IBM DB2 9 Family Fundamentals Set 3

Given the following table definition:
EMPLOYESS
--------------------------
EMP ID INTEGER
NAME CHAR(20)
DEPT CHAR(10)
SALARY DECIMAL (10, 2)
COMMISSION DECIMAL (8, 2)
Assuming the DEPT column contains the values `ADMIN', `PRODUCTION', and `SALES', which of the following statements will produce a result data set in which all ADMIN department employees are grouped together, all PRODUCTION department employees are grouped together, and all SALES department employees are grouped together?

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 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