Test your knowledge of DB2 9 by answering the questions below, which are from a real, previous DB2 9 Fundamentals certification exam (Exam 730). When you make your answer selection, click on "Get Answer" to check your answers and read the explanations. If you haven't done so already, be sure to take part 1 of this DB2 9 quiz series about tables.
Sample Questions
Question 1
Given the following two tables:
NAMES
NAME NUMBER
–––––––––– ––––––––
Wayne Gretsky 99
Jaromir Jagr 68
Bobby Orr 4
Bobby Hull 23
Brett Hull 16
Mario Lemieux 66
Mark Messier 11
POINTS
NAME POINTS
–––––––––– ––––––––
Wayne Gretsky 244
Jaromir Jagr 168
Bobby Orr 128
Brett Hull 121
Mario Lemieux 189
Joe Sacik 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
Get answer
Question 2
Given the following statement:
CREATE TABLE t1
col1 SMALLINT NOT NULL PRIMARY KEY,
col2 VARCHAR(200) NOT NULL WITH DEFAULT NONE,
col3 DECIMAL(5,2) CHECK (col3 >= 100.00),
col4 DATE NOT NULL WITH DEFAULT)
Which of the following definitions will cause the CREATE TABLE statement to fail?
A. COL1
B. COL2
C. COL3
D. COL4
Get answer
Question 3
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
Get answer
Question 4
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?
Get answer
Question 5
Given the following scenario:
Table TABLE1 needs to hold specific numeric values up to 9999999.999 in column COL1. Once TABLE1 is populated, arithmetic operations will be performed on data stored in col-umn COL1.
Which of the following would be the most appropriate DB2 data type to use for column COL1?
A. INTEGER
B. REAL
C. NUMERIC(7,3)
D. DECIMAL(10,3)
Get answer
Question 6
Application A wants to read a subset of rows from table TAB1 multiple times. Which of the following isolation levels should Application A use to prevent other users from making modifications and additions to table TAB1 that will affect the subset of rows read?
A. Repeatable Read
B. Read Stability
C. Cursor Stability
D. Uncommitted Read
Get answer
Question 7
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 -
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'
Get answer
Question 8
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
Get answer
Question 9
Which of the following is used to indicate a column will not accept NULL values and can be referenced in another table's foreign key specification?
A. Check constraint
B. Unique constraint
C. Default constraint
D. Informational constraint
Get answer
Question 10
An application has acquired a Share lock on a row in a table and now wishes to update the row. Which of the following statements is true?:
A. The application must release the row-level Share lock it holds and acquire an Update lock on the row
B. The application must release the row-level Share lock it holds and acquire an Update lock on the table
C. The row-level Share lock will automatically be converted to a row-level Up-date lock
D. The row-level Share lock will automatically be escalated to a table-level Up-date lock
Get answer
