Practice Questions

1.	Given the following two tables: NAMES ------------------------ NAME NUMBER ---------- ------- Wayne Gretzky 99 Jaromir Jagr 68 Bobby Orr 4 Bobby Hull 23 Mario Lemieux 66 POINTS ------------------------ NAME POINTS ---------- ------- Wayne Gretzky 244 Bobby Orr 129 Brett Hull 121 Mario Lemieux 189 Joe Sakic 94 How many rows would be returned using the following statement? SELECT name FROM names, points A. 0 B. 5 C. 10 D. 25
2.	Given the following two tables: TAB1 ------------------- COL_1 COL_2 ----- ----- A 10 B 12 C 14 TAB2 ------------------- COL_A COL_B ----- ----- A 21 C 23 D 25 Assuming the following results are desired: COL_1 COL_2 COL_A COL_B A 10 A 21 B 12 - - C 14 C 23 - - D 25 Which of the following joins will produce the desired results? A. SELECT * FROM tab1 INNER JOIN tab2 ON col_1 = col_a B. SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON col_1 = col_a C. SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON col_1 = col_a D. SELECT * FROM tab1 FULL OUTER JOIN tab2 ON col_1 = col_a
3.	Given the following table: TAB1 ------------------- COL1 COL2 ----- ----- A 10 B 20 C 30 A 10 D 40 C 30 Assuming the following results are desired: TAB1 ------------------- COL1 COL2 ----- ----- A 10 B 20 C 30 D 40 Which of the following statements will produce the desired results? A. SELECT UNIQUE * FROM tab1 B. SELECT DISTINCT * FROM tab1 C. SELECT UNIQUE(*) FROM tab1 D. SELECT DISTINCT(*) FROM tab1
4.	Given the following two tables: EMPLOYEE ID NAME DEPTID -- -------------- ------- 01 Mick Jagger 10 02 Keith Richards 20 03 Ronnie Wood 20 04 Charlie Watts 20 05 Bill Wyman 30 06 Brian Jones - DEPARTMENT ID DEPTNAME -- --------------- 10 Executive Staff 20 Sales 30 Marketing 40 Engineering 50 Human Resources Which two of the following queries will display the employee name and department name for all employees that are in Sales? A. SELECT e.name, d.deptname FROM employee e, department d WHERE e.deptid = d.id AND d.id = '20' B. SELECT e.name, d.deptname FROM employee e FULL OUTER JOIN department d ON e.deptid = d.id WHERE d.id = '20' C. SELECT e.name, d.deptname FROM employee e RIGHT OUTER JOIN department d ON e.deptid = d.id WHERE d.id = '20' D. SELECT e.name, d.deptname FROM employee e LEFT OUTER JOIN department d ON e.deptid = d.id WHERE d.id = '20' E. SELECT e.name, d.deptname FROM employee e INNER JOIN department d ON e.deptid = d.id WHERE d.id = '20'
5.	Given the following queries: SELECT c1 FROM tab1; SELECT c1 FROM tab2; Which of the following set operators can be used to produce a result data set that contains only records that are not found in the result data set produced by each query after duplicate rows have been eliminated? A. UNION B. INTERSECT C. EXCEPT D. MERGE
6.	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? A. SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name B. SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name C. SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name D. SELECT p.name, n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name
7.	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 Watts, Charlie 6 Stewart, Ian If the following SQL statement is executed, how many rows will be returned? SELECT name FROM year_2007 UNION ALL SELECT name FROM year_1962 A. 6 B. 9 C. 10 D. 12
8.	Given the following table: EMPLOYEE EMPID NAME INSTRUMENT --- --------- ----- 1 Jagger, Mick 01 2 Richards, Keith 02 3 Wood, Ronnie 02 4 Watts, Charlie 03 5 Jones, Darryl 04 6 Leavell, Chuck 05 If the following query is executed: SELECT name, CASE WHEN instrument = '01' THEN 'HARMONICA' WHEN instrument = '02' THEN 'GUITAR' WHEN instrument = '03' THEN 'DRUMS' ELSE 'UNKNOWN' END AS instrument FROM employee What will be the results? • A. NAME INSTRUMENT ---------------- -------------- Jagger, Mick HARMONICA Richards, Keith GUITAR Wood, Ronnie GUITAR Watts, Charlie DRUMS Jones, Darryl ERROR Leavell, Chuck ERROR. • B. NAME INSTRUMENT ---------------- ------------- Jagger, Mick HARMONICA Richards, Keith GUITAR Wood, Ronnie GUITAR Watts, Charlie DRUMS Jones, Darryl 04 Leavell, Chuck 05 • C. NAME INSTRUMENT ---------------- ------------- Jagger, Mick HARMONICA Richards, Keith GUITAR Wood, Ronnie GUITAR Watts, Charlie DRUMS Jones, Darryl UNKNOWN Leavell, Chuck UNKNOWN • D. NAME INSTRUMENT ---------------- ------------- Jagger, Mick HARMONICA Richards, Keith GUITAR Wood, Ronnie GUITAR Watts, Charlie DRUMS Jones, Darryl - Leavell, Chuck -
9.	Given the following table definition: SALES --------------------------------------- INVOICE_NO CHAR(20) NOT NULL SALES_DATE DATE SALES_PERSON VARCHAR(25) REGION CHAR(20) SALES_AMT DECIMAL(9,2) Which of the following queries will return SALES information, sorted by SALES_PERSON, from A to Z, and SALES_DATE, from most recent to earliest? A. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person, sales_date DESC B. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person DESC, sales_date C. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person, sales_date DESC D. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person DESC, sales_date
10.	Given the following statement: SELECT hyear, AVG(salary) FROM SELECT YEAR(hiredate) AS hyear, salary FROM employee WHERE salary > 30000) GROUP BY hyear Which of the following describes the result if this statement is executed? A. The statement will return the year and average salary for all employees that have a salary greater than $30,000, sorted by year. B. The statement will return the year and average salary for all employees hired within a given year that have a salary greater than $30,000. C. The statement will return the year and average salary for all years that every employee hired had a salary greater than $30,000. D. The statement will return the year and average salary for all years that any employee had a salary greater than $30,000.
11.	Which two of the following statements are true about the HAVING clause? A. The HAVING clause is used in place of the WHERE clause. B. The HAVING clause uses the same syntax as the WHERE clause. C. The HAVING clause can only be used with the GROUP BY clause. D. The HAVING clause accepts wildcards. E. The HAVING clause uses the same syntax as the IN clause.
12.	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? A. INSERT INTO current_employees (empid) VALUES (10) B. INSERT INTO current_employees VALUES (10, 'JAGGER', 85000.00) C. INSERT INTO current_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20 D. INSERT INTO current_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)
13.	Given the following UPDATE statement: UPDATE employees SET workdept = (SELECT deptno FROM department WHERE deptno = 'A01') WHERE workdept IS NULL Which of the following describes the result if this statement is executed? A. The statement will fail because an UPDATE statement cannot contain a subquery. B. The statement will only succeed if the data retrieved by the subquery does not contain multiple records. C. The statement will succeed; if the data retrieved by the subquery contains multiple records, only the first record will be used to perform the update. D. The statement will only succeed if every record in the EMPLOYEES table has a null value in the WORKDEPT column.
14.	Given the following table definition: SALES -------------------------- SALES_DATE DATE SALES_PERSON CHAR(20) REGION CHAR(20) SALES INTEGER Which of the following SQL statements will remove all rows that had a SALES_DATE in the year 1995? A. DELETE * FROM sales WHERE YEAR(sales_date) = 1995 B. DELETE FROM sales WHERE YEAR(sales_date) = 1995 C. DROP * FROM sales WHERE YEAR(sales_date) = 1995 D. DROP FROM sales WHERE YEAR(sales_date) = 1995
15.	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? A. SELECT name, dept FROM employees ORDER BY dept B. SELECT name, dept FROM employees GROUP BY dept C. SELECT name, dept FROM employees GROUP BY ROLLUP (dept) D. SELECT name, dept FROM employees GROUP BY CUBE (dept)
16.	The following SQL statement: DELETE FROM tab1 WHERE CURRENT OF csr1 WITH RR Is used to perform which type of delete operation? A. Positioned B. Searched C. Embedded D. Dynamic
17.	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? • A. COL2 COL1_SUM ---- -------- abc 200 abc 250 def 175 ghi 300 4 record(s) selected. • B. COL2 COL1_SUM ---- -------- abc 450 def 175 ghi 300 3 record(s) selected. • C. COL2 COL1_SUM ---- -------- abc 450 def 325 ghi 300 3 record(s) selected. • D. COL2 COL1_SUM ---- -------- abc 450 abc 450 def 175 def 175 ghi 300 5 record(s) selected.
18.	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? A. DELETE FROM table1 WHERE id IN (SELECT id FROM table2) B. DELETE FROM table1 WHERE id IN (SELECT person FROM table2) C. DELETE FROM table1 WHERE person IN (SELECT id FROM table2) D. DELETE FROM table1 WHERE person IN (SELECT person FROM table2)
19.	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? A. SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name B. SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name C. SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name D. SELECT p.name, n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name
20.	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 A. SELECT *, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC B. SELECT deptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC C. SELECT deptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 ASC D. SELECT deptname, COUNT(*) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2
21.	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? A. INSERT INTO current_employees (empid) VALUES (10) B. INSERT INTO current_employees VALUES (10, 'JAGGER', 85000.00) C. INSERT INTO current_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20 D. INSERT INTO current_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)
22.	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? A. UPDATE stock SET description = 'NULL', quantity = 0, price = 0 WHERE category 'S' B. UPDATE stock SET description = NULL, SET quantity = 0, SET price = 0 WHERE category 'S' C. UPDATE stock SET (description, quantity, price) = ('null', 0, 0) WHERE category 'S' D. UPDATE stock SET (description, quantity, price) = (NULL, 0, 0) WHERE category 'S'
23.	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) • A. COL1 ---- 1 1 record(s) selected. • B. COL1 ---- NULL 1 2 record(s) selected. • C. COL1 ---- - 1 2 record(s) selected. • D. COL1 ---- - 1 record(s) selected.
24.	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 A. The rows are sorted by INVOICE_NO in ascending order. B. The rows are sorted by INVOICE_NO in descending order. C. The rows are ordered based on when they were inserted into the table. D. The rows are not sorted in any particular order.
25.	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 A. 0 B. 6 C. 10 D. 12
26.	Which of the following best describes a unit of work? A. It is a recoverable sequence of operations whose point of consistency is established when a connection to a database has been established or when a mechanism known as a savepoint is created. B. It is a recoverable sequence of operations whose current point of consistency can be determined by querying the system catalog tables. C. It is a recoverable sequence of operations whose point of consistency is established when an executable SQL statement is processed after a connection to a database has been established or a previous transaction has been terminated. D. It is a recoverable sequence of operations whose point of consistency is only established if a mechanism known as a savepoint is created.
27.	Given the following set of statements: CREATE TABLE tab1 (col1 INTEGER, col2 CHAR(20)); COMMIT; INSERT INTO tab1 VALUES (123, 'Red'); INSERT INTO tab1 VALUES (456, 'Yellow'); SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS; DELETE FROM tab1 WHERE col1 = 123; INSERT INTO tab1 VALUES (789, 'Blue'); ROLLBACK TO SAVEPOINT s1; INSERT INTO tab1 VALUES (789, 'Green'); UPDATE tab1 SET col2 = NULL WHERE col1 = 789; COMMIT; Which of the following records would be returned by the following statement? SELECT * FROM tab1 • A. COL1 COL2 ---- ------- 123 Red 456 Yellow 2 record(s) selected. • B. COL1 COL2 ---- ------ 456 Yellow 1 record(s) selected. • C. COL1 COL2 ---- ----- 123 Red 456 Yellow 789 - 3 record(s) selected. • D. COL1 COL2 ---- ------ 123 Red 456 Yellow 789 Green 3 record(s) selected.
28.	Given the following table: TAB1 COL1 COL2 ----- ----- A 10 B 20 C 30 D 40 E 50 And the following SQL statements: DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM tab1 ORDER BY col_1; OPEN c1; FETCH c1; FETCH c1; FETCH c1; COMMIT; FETCH c1; CLOSE c1; FETCH c1; Which of the following is the last value obtained for COL_2? A. 20 B. 30 C. 40 D. 50
29.	A stored procedure has been created with the following statement: CREATE PROCEDURE proc1 (IN var1 VARCHAR(10), OUT rc INTEGER) SPECIFIC myproc LANGUAGE SQL … What is the correct way to invoke this procedure from the command line processor (CLP)? A. CALL proc1 ('SALES', ?) B. CALL myproc ('SALES', ?) C. CALL proc1 (SALES, ?) D. RUN proc1 (SALES, ?)
30.	Given the following table: TEMP_DATA TEMP DATE ----- ----- 45 12/25/2006 51 12/26/2006 67 12/27/2006 72 12/28/2006 34 12/29/2006 42 12/30/2006 And the following SQL statement: CREATE FUNCTION degf_to_c (temp INTEGER) RETURNS INTEGER LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC BEGIN ATOMIC DECLARE newtemp INTEGER; SET newtemp = temp - 32; SET newtemp = newtemp * 5; RETURN newtemp / 9; END Which two of the following SQL statements illustrate the proper way to invoke the scalar function DEGF_TO_C? A. VALUES degf_to_c(32) B. SELECT date, degf_to_c(temp) AS temp_c FROM temp_data C. CALL degf_to_c(32) D. SELECT * FROM TABLE(degf_to_c(temp)) AS temp_c E. VALUES degf_to_c(32) AS temp_c
31.	Given the following CREATE TABLE statement: CREATE TABLE customer(custid INTEGER, info XML) And the following INSERT statements: INSERT INTO customer VALUES (1000, '<customerinfo xmlns="http://custrecord.dat" cust> <name>John Doe</name> <addr country="United States"> <street>25 East Creek Drive</street> <city>Raleigh</city> <state-prov>North Carolina</state-prov> <zip-pcode>27603</zip-pcode> </addr> <phone type="work">919-555-1212</phone> <email>[e-mail]</email> </customerinfo>'); INSERT INTO customer VALUES (1000, '<customerinfo xmlns="http://custrecord.dat" cust> <name>Paul Smith</name> <addr country="Canada"> <street>412 Stewart Drive</street> <city>Toronto</city> <state-prov>Ontario</state-prov> <zip-pcode>M8X-3T6</zip-pcode> </addr> <phone type="work">919-555-4444</phone> <email>[e-mail]</email> </customerinfo>'); What is the result of the following XQuery expression? XQUERY declare default element namespace "http://custrecord.dat"; for $info in db2-fn:xmlcolumn('CUSTOMER.INFO')/customerinfo where $info/addr/state-prov="Ontario" return $info/name/text(); A. Paul Smith B. <name xmlns="http://custrecord.dat">Paul Smith</name> C. <customerinfo xmlns="http://custrecord.dat" cust><name xmlns="http://custrecord.dat">Paul Smith</name> D. <customerinfo xmlns="http://custrecord.dat" cust>Paul Smith</customerinfo>

Answers

1. 

The correct answer is D. When a SELECT statement such as the one shown is executed, the result data set produced will contain all possible combinations of the rows found in each table specified (otherwise known as the Cartesian product). Every row in the result data set produced is a row from the first table referenced concatenated with a row from the second table referenced, concatenated in turn with a row from the third table referenced, and so on. The total number of rows found in the result data set produced is the product of the number of rows in all the individual table-references; in this case, 5 x 5 = 25.

2. 

The correct answer is D. When a full outer join operation is performed, rows that would have been returned by an inner join operation, together with all rows stored in both tables of the join operation that would have been eliminated by the inner join operation are returned in the result data set produced. An inner join can be thought of as the cross product of two tables, in which every row in one table that has a corresponding row in another table is combined with that row to produce a new record. When a left outer join operation is performed, rows that would have been returned by an inner join operation, together with all rows stored in the leftmost table of the join operation (i.e., the table listed first in the OUTER JOIN clause) that would have been eliminated by the inner join operation, are returned in the result data set produced. When a right outer join operation is performed, rows that would have been returned by an inner join operation, together with all rows stored in the rightmost table of the join operation (i.e., the table listed last in the OUTER JOIN clause) that would have been eliminated by the inner join operation, are returned in the result data set produced.

3. 

The correct answer is B. If the DISTINCT clause is specified with a SELECT statement, duplicate rows are removed from the final result data set returned. Two rows are considered to be duplicates of one another if the value of every column of the first row is identical to the value of the corresponding column of the second row.

4. 

The correct answers are A and E. An inner join can be thought of as the cross product of two tables, in which every row in one table that has a corresponding row in another table is combined with that row to produce a new record. The syntax for a SELECT statement that performs an inner join operation is:

 SELECT [*|[Expression] <<AS> [NewColumnName]> ,...] FROM [[TableName] <<AS> [CorrelationName]> ,...] [JoinCondition] 

where:

Expression	Identifies one or more columns whose values are to be returned when the SELECT statement is executed. The value specified for this option can be any valid SQL language element; however, corresponding table or view column names are commonly used.
NewColumnName	Identifies a new column name that is to be used in place of the corresponding table or view column name specified in the result data set returned by the SELECT statement.
TableName	Identifies the name(s) assigned to one or more tables that data is to be retrieved from.
CorrelationName	Identifies a shorthand name that can be used when referencing the table name specified in the TableName parameter.
JoinCondition	Identifies the condition to be used to join the tables specified. Typically, this is a WHERE clause in which the values of a column in one table are compared with the values of a similar column in another table.

The following syntax can also be used to create a SELECT statement that performs an inner join operation:

 SELECT [*|[Expression] <<AS> [NewColumnName]> ,...] FROM [[TableName1] <<AS> [CorrelationName1]>] <INNER> JOIN [[TableName2] <<AS> [CorrelationName2]>] ON [JoinCondition] 

where:

Expression	Identifies one or more columns whose values are to be returned when the SELECT statement is executed. The value specified for this option can be any valid SQL language element; however, corresponding table or view column names are commonly used.
NewColumnName	Identifies a new column name to be used in place of the corresponding table or view column name specified in the result data set returned by the SELECT statement.
TableName1	Identifies the name assigned to the first table data is to be retrieved from.
CorrelationName1	Identifies a shorthand name that can be used when referencing the leftmost table of the join operation.
TableName2	Identifies the name assigned to the second table data is to be retrieved from.
CorrelationName2	Identifies a shorthand name that can be used when referencing the rightmost table of the join operation.
JoinCondition	Identifies the condition to be used to join the two tables specified.

5. 

The correct answer is C. When the EXCEPT set operator is used, the result data sets produced by each individual query are combined, all duplicate rows found are eliminated, and all records found in the first result data set that have a corresponding record in the second result data set are eliminated, leaving just the records that are not found in both result data sets. When the UNION set operator is used, the result data sets produced by each individual query are combined and all duplicate rows are eliminated; when the INTERSECT set operator is used, the result data sets produced by each individual query are combined, all duplicate rows found are eliminated, and all records found in the first result data set that do not have a corresponding record in the second result data set are eliminated, leaving just the records that are found in both result data sets; MERGE is not a set operator.

6. 

The correct answer is C. When a right outer join operation is performed, rows that would have been returned by an inner join operation, together with all rows stored in the rightmost table of the join operation (i.e., the table listed last in the OUTER JOIN clause) that would have been eliminated by the inner join operation are returned in the result data set produced. In this case, we want to see all records found in the POINTS table, along with any corresponding records found in the NAMES table, so a right outer join is the appropriate join operation to use.

7. 

The correct answer is D. When the UNION ALL set operator is used, the result data sets produced by each individual query are combined; all duplicate rows found are retained. Thus with this example, the results of both tables are combined (6 rows + 6 rows = 12 rows) and the duplicate rows for "Jagger, Mick", "Richards, Keith", and "Watts, Charlie" are retained. Had the UNION set operator been used instead, the result data sets produced by each individual query would have been combined and all duplicate rows would have been eliminated and the answer would have been 9 (12 - 3 = 9 rows).

8. 

The correct answer is C. One efficient and concise way to display coded values in a readable format is to use one or more CASE expressions in the selection list of a query. Each CASE operation evaluates a specified expression and supplies a different value, depending on whether a certain condition is met. A CASE expression can take one of two forms: simple or searched. The CASE statement presented in the question is a searched CASE expression; in this example, if the INSTRUMENT column contains the value '01', the word 'HARMONICA' is returned, if the INSTRUMENT column contains the value '02', the word 'GUITAR' is returned, if the INSTRUMENT column contains the value '03', the word 'DRUMS' is returned, and if the INSTRUMENT column contains any other value, the word 'UNKNOWN' is returned.

9. 

The correct answer is C. Data is stored in a table in no particular order, and unless otherwise specified, a query only returns data in the order in which it is found. The ORDER BY clause is used to tell the DB2 Database Manager how to sort and order the rows that are to be returned in a result data set produced in response to a query. When specified, the ORDER BY clause is followed by the name of one or more column(s) whose data values are to be sorted and a keyword that indicates the desired sort order. If the keyword ASC follows the column's name, ascending order is used, and if the keyword DESC follows the column name, descending order is used. If no keyword is specified, ascending order is used by default.

10. 

The correct answer is B. The subselect produces a result data set that contains hire year and salary information for each employee whose salary is greater than $30,000.00. The GROUP BY clause is used to tell the DB2 Database Manager how to organize rows of data returned in the result data set produced in response to a query. (The GROUP BY clause specifies an intermediate result table consisting of a group of rows.) In this example, the GROUP BY clause tells the outer SELECT to calculate and group average salary information by hire year.

11. 

The correct answers are B and C. The HAVING clause is used to apply further selection criteria to columns that are referenced in a GROUP BY clause. This clause behaves like the WHERE clause, except that it refers to data that has already been grouped by a GROUP BY clause (the HAVING clause is used to tell the DB2 Database Manager how to select the rows that are to be returned in a result data set from rows that have already been grouped). And like the WHERE clause, the HAVING clause is followed by a search condition that acts as a simple test that, when applied to a row of data, will evaluate to TRUE, FALSE, or Unknown.

12. 

The correct answer is D. Because the EMPID column in each table was defined in such a way that it does not allow null values, a non-null value must be provided for this column any time data is inserted into either table. The INSERT statement shown in answer D does not provide a value for the EMPID column of the CURRENT_EMPLOYEES table, so the statement will fail.

13. 

The correct answer is B . When the results of a query, or subselect, are used to provide values for one or more columns identified in the column name list provided for an UPDATE statement, the values retrieved from one base table or view are used to modify values stored in another. The number of values returned by the subselect must match the number of columns provided in the column name list specified, and only one record can be returned.

14. 

The correct answer is B. The DELETE statement is used to remove specific records from a table (the DROP statement completely destroys the table object), and the correct syntax for the DELETE statement is DELETE FROM [TableName] …

15. 

The correct answer is A. The ORDER BY clause is used to tell the DB2 Database Manager how to sort and order the rows that are to be returned in a result data set produced in response to a query. In this example, all rows containing the value "ADMIN" in the DEPT column would be listed first, followed by all rows containing the value "PRODUCTION", followed by all rows containing the value "SALES".

16. 

The correct answer is A. Delete operations can be conducted in one of two ways: as searched delete operations or as positioned delete operations. To perform a positioned delete, a cursor must first be created, opened, and positioned on the row to be deleted. Then, the DELETE statement used to remove the row must contain a WHERE CURRENT OF [CursorName] clause (CursorName identifies the cursor being used). Because of their added complexity, positioned delete operations are typically performed by embedded SQL applications.

17. 

The correct answer is B. Common table expressions are mechanisms that are used to construct local temporary tables that reside in memory and only exist for the life of the SQL statement that defines them. The syntax used to construct a common table expression is:

 WITH [TableName]<([ColumnName] ,...])> AS ([SELECTStatement]) 

where:

TableName	Specifies the name that is to be assigned to the temporary table to be created.
ColumnName	Specifies the name(s) to be assigned to one or more columns that are to be included in the temporary table to be created. Each column name specified must be unique and unqualified; if no column names are specified, the names derived from the result data set produced by the SELECT statement specified will be used. If a list of column names is specified, the number of column names provided must match the number of columns that will be returned by the SELECT statement used to create the temporary table. If a common table expression is recursive, or if the result data set produced by the SELECT statement specified contains duplicate column names, column names must be specified.
SELECTStatement	Identifies a SELECT SQL statement that, when executed, will produce the data values to be added to the column(s) in the temporary table to be created.

So in this example, all of the data stored in table TAB1, with the exception of the record "150 - def" is copied to a common table named SUBSET, and then a query is run against this common table.

18. 

The correct answer is C. Since we are looking for values in the PERSON column of TABLE1 that have a matching value in the ID column of TABLE2, the statement shown in Answer C is the only statement that is correct. (The SQL statements shown in Answers B and D are incorrect because there is no PERSON column in TABLE2; the statement shown in Answer A is incorrect because it is looking for values that match those in the ID column in TABLE1, not the PERSON column.)

19. 

The correct answer is C. When a right outer join operation is performed, rows that would have been returned by an inner join operation, together with all rows stored in the rightmost table of the join operation (i.e., the table listed last in the OUTER JOIN clause) that would have been eliminated by the inner join operation are returned in the result data set produced. In this case, we want to see all records found in the POINTS table, along with any corresponding records found in the NAMES table, so a right outer join is the appropriate join operation to use.

20. 

The correct answer is B. COUNT(empno) together with GROUP BY deptname displays the total number of employees in each department; SELECT deptname displays the corresponding department name for each department ID, and ORDER BY 2 DESC sorts the data by employee count (which is column 2) from greatest to least.

21. 

The correct answer is D. Because the EMPID column was defined in such a way that it does not allow null values, a non-null value must be provided for this column anytime data is inserted into either table. The INSERT statement shown in Answer D does not provide a value for the EMPID column of the CURRENT_EMPLOYEES table, so the statement will fail.

22. 

The correct answer is D. Because 'NULL' is treated as a string instead of a NULL value, the SQL statements shown in Answers A and C would not set the STATUS to NULL; the statement shown in Answer B is invalid because the SET keyword is only used once in the UPDATE statement. Therefore, statement D is the only UPDATE statement shown that will accomplish the desired task.

23. 

The correct answer is A. The IN predicate is used to define a comparison relationship in which a value is checked to see whether or not it matches a value in a finite set of values. This finite set of values can consist of one or more literal values coded directly in the SELECT statement, or it can be composed of the non-null values found in the result data set generated by a subquery. So in this example, the non-null values that appear in the result data set produced by the subquery are the values 1 and 2, and the only row in TAB1 that has a matching value in COL1 is the row with the value 1 in it.

24. 

The correct answer is D. Data is stored in a table in no particular order, and unless otherwise specified (with an ORDER BY clause), a query only returns data in the order in which it is found.

25. 

The correct answer is C. When the UNION set operator is used, the result data sets produced by each individual query are combined and all duplicate rows are eliminated. Thus with this example, the results of both tables are combined (6 rows + 6 rows = 12 rows) and the duplicate rows for Jagger, Mick and Richards, Keith are removed (12 - 2 = 10 rows). So 10 rows are returned.

26. 

The correct answer is C. A transaction (also known as a unit of work) is a sequence of one or more SQL operations grouped together as a single unit, usually within an application process. A given transaction can perform any number of SQL operations-from a single operation to many hundreds or even thousands, depending on what is considered a "single step" within your business logic. The initiation and termination of a single transaction defines points of data consistency within a database; either the effects of all operations performed within a transaction are applied to the database and made permanent (committed), or the effects of all operations performed are backed out (rolled back) and the database is returned to the state it was in before the transaction was initiated. In most cases, transactions are initiated the first time an executable SQL statement is executed after a connection to a database has been made or immediately after a preexisting transaction has been terminated. Once initiated, transactions can be implicitly terminated using a feature known as "automatic commit" (in this case, each executable SQL statement is treated as a single transaction, and any changes made by that statement are applied to the database if the statement executes successfully or discarded if the statement fails) or they can be explicitly terminated by executing the COMMIT or the ROLLBACK SQL statement.

27. 

The correct answer is C. DB2 uses a mechanism known as a savepoint to allow an application to break the work being performed by a single large transaction into one or more subsets. Once created, a savepoint can be used in conjunction with a special form of the ROLLBACK SQL statement to return a database to the state it was in at the point in time a particular savepoint was created. The syntax for this form of the ROLLBACK statement is:

 ROLLBACK <WORK> TO SAVEPOINT <[ SavepointName]> 

where:

SavepointName

Identifies the name assigned to the savepoint that indicates the point in time that operations performed against the database are to be rolled back (backed out) to.

So, in this example, every operation performed between the time savepoint S1 was created and the ROLLBACK TO SAVEPOINT statement was executed was undone.

28. 

The correct answer is C. When a cursor that has been declared with the WITH HOLD option specified (as in the example shown) is opened, it will remain open across transaction boundaries until it is explicitly closed; otherwise, it will be implicitly closed when the transaction that opens it is terminated. In this example, the cursor is opened, the first three rows are fetched from it, the transaction is committed (but the cursor is not closed), another row is fetched from it, and then the cursor is closed. Thus, the last value obtained will be:

      TAB1 --------------- COL1     COL2 ----     --- D        40 

29. 

The correct answer is A. The CALL statement is used to invoke a stored procedure, so answer D is wrong; because a stored procedure cannot be invoked using its specific name, answer B is wrong; and since SALES is a character string value that is being passed to the procedure, it must be enclosed in single quotes. Therefore, answer C is wrong.

30. 

The correct answers are A and B. How a user-defined function is invoked depends a lot on what it has been designed to do; scalar user-defined functions can be invoked as an expression in the select list of a query while table and row functions must be referenced by the FROM clause. In because the user-defined function used in this example is a scalar function that only returns a single value, answer B is the correct way to call it. A scalar function can also be invoked by executing a VALUES statement that looks something like the one shown in answer A.

31. 

The correct answer is A. In XQuery, expressions are the main building blocks of a query. Expressions can be nested and form the body of a query. A query can also have a prolog that contains a series of declarations that define the processing environment for the query. Thus, if you wanted to retrieve customer names for all customers who reside in North Carolina from XML documents stored in the CUSTINFO column of a table named CUSTOMER (assuming this table has been populated with the INSERT statement we looked at earlier), you could do so by executing an XQuery expression that looks something like this:

 XQUERY declare default element namespace "http://custrecord.dat"; for $info in db2-fn:xmlcolumn('CUSTOMER.CUSTINFO')/customerinfo where $info/addr/state-prov="North Carolina" return $info/name 

And when this XQuery expression is executed from the Command Line Processor, it should return information that looks like this (again, assuming this table has been populated with the INSERT statement we looked at earlier):

 1 ------------------------------ <name xmlns="http://custrecord.dat">John Doe</name> 

If you wanted to remove the XML tags and just return the customer name, you could do so by executing an XQuery expression that looks like this instead:

 XQUERY declare default element namespace "http://custrecord.dat";  for $info in db2-fn:xmlcolumn('CUSTOMER.CUSTINFO')/customerinfo where $info/addr/state-prov="North Carolina" return $info/name/text() 

Now, when the XQuery expression is executed from the Command Line Processor, it should return information that looks like this:

 1 ------- John Doe