Given the following data:
TAB1
C1 C2
— —
200 abc
250 abc
150 def
300 ghi
175 def
If the following query is executed:
WITH subset (col1, col2) AS
(SELECT c1, c2 FROM tab1 WHERE c1 > 150)
SELECT col2, SUM(col1) AS col1_sum
FROM subset
GROUP BY col2
ORDER BY col2
Which of the following result data sets will be produced?

Given the following table definitions:
TABLE1
———————————–
ID INT
NAME CHAR(30)
PERSON INT
CITIES INT
TABLE2
———————————–
ID INT
LASTNAME CHAR(30)
Which of the following statements will remove all rows in table TABLE1 that have matching
PERSONs in table TABLE2?

Given the following two tables:
NAMES
NAME NUMBER
———– ————–
Wayne Gretzky 99
Jaromir Jagr 68
Bobby Orr 4
Bobby Hull 23
Brett Hull 16
Mario Lemieux 66
Mark Messier 11
POINTS
NAME POINTS
———– ————–
Wayne Gretzky 244
Jaromir Jagr 168
Bobby Orr 129
Brett Hull 121
Mario Lemieux 189
Joe Sakic 94
Which of the following statements will display the player name, number, and points for all players
that have scored points?

Given the following table definitions:
EMPLOYEES
————————————————
EMPID INTEGER
NAME CHAR(20)
DEPTID CHAR(3)
SALARY DECIMAL(10,2)
COMMISSION DECIMAL(8,2)
DEPARTMENTS
————————————————
DEPTNO INTEGER
DEPTNAME CHAR(20)
Which of the following statements will produce a result data set that satisfies all of these
conditions:
> Displays the total number of employees in each department
>> Displays the corresponding department name for each department ID
>> Sorted by department employee count, from greatest to least

Given the following table:
CURRENT_EMPLOYEES
————————————–
EMPID INTEGER NOT NULL
NAME CHAR(20)

SALARY DECIMAL(10,2)
PAST_EMPLOYEES
————————————–
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)
Assuming both tables contain data, which of the following statements will NOT successfully add
data to table CURRENT_EMPLOYEES?

Given the following table:
STOCK
————————–
CATEGORY CHAR(1)
PARTNO CHAR(12)
DESCRIPTION VARCHAR(40)
QUANTITY INTEGER
PRICE DEC(7,2)
If items are indicated to be out of stock by setting DESCRIPTION to NULL and QUANTITY and
PRICE to zero, which of the following statements updates the STOCK table to indicate that all
items except those with CATEGORY of ‘S’ are temporarily out of stock?

Given the following SQL statements:
CREATE TABLE tab1 (col1 INTEGER)
INSERT INTO tab1 VALUES (NULL)
INSERT INTO tab1 VALUES (1)
CREATE TABLE tab2 (col2 INTEGER)
INSERT INTO tab2 VALUES (NULL)
INSERT INTO tab2 VALUES (1)
INSERT INTO tab2 VALUES (2)
What will be the result when the following statement is executed?
SELECT * FROM tab1 WHERE col1 IN (SELECT col2 FROM tab2)

Given the following table definition:
SALES

———————————————
INVOICE_NO CHAR(20) NOT NULL
SALES_DATE DATE
SALES_PERSON CHAR(20)
REGION CHAR(20)
SALES INTEGER
If the following SELECT statement is executed, which of the following describes the order of the
rows in the result data set produced?
SELECT * FROM sales

Given the following tables:
YEAR_2006
EMPID NAME
———————————
1 Jagger, Mick
2 Richards, Keith
3 Wood, Ronnie
4 Watts, Charlie
5 Jones, Darryl
6 Leavell, Chuck
YEAR_1962
EMPID NAME
———————————
1 Jagger, Mick
2 Richards, Keith
3 Jones, Brian
4 Wyman, Bill
5 Chapman, Tony
6 Stewart, Ian
If the following SQL statement is executed, how many rows will be returned?

SELECT name FROM year_2006
UNION
SELECT name FROM year_1962

Which of the following best describes a unit of work?