IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 4

The following sample exam questions are taken from DB2 9 Fundamentals Certification Study Guide, by Roger E. Sanders (copyright 2007) and are reprinted here with permission from MC Press.

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, part 2 and part 3 of this DB2 9 quiz series about tables.

Sample Questions
Question 1

Which of the following deletion rules on CREATE TABLE will allow parent table rows to be deleted if a dependent row exists?

A. ON DELETE RESTRICT
B. ON DELETE NO ACTION
C. ON DELETE SET NO VALUE
D. ON DELETE CASCADE

Get answer

Question 2

To which of the following resources can a lock NOT be applied?

A. Table spaces
B. Buffer pools
C. Tables
D. Rows

Get answer

Question 3

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
   1 record(s) selected
D. COL1
   ––––––
   -
   1 record(s) selected

Get answer

Question 4

Given the statement:

CREATE TABLE tablea    ( col1   INTEGER NOT NULL,
     CONSTRAINT const1 CHECK (col1 in (100, 200, 300))

Which of the following can be inserted into TABLEA?

A. 0
B. NULL
C. 100
D. '100'

Get answer

Question 5

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                24               -            21
    C                14               C           21
    -                  -                 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

Get answer

Question 6

Application A holds a lock on a row in table TAB1. If lock timeout is set to 20, what will happen when Application B attempts to acquire a compatible lock on the same row?

A. Application B will acquire the lock it needs
B. Application A will be rolled back if it still holds its lock after 20 seconds have elapsed
C. Application B will be rolled back if Application A still holds its lock after 20 seconds have elapsed
D. Both applications will be rolled back if Application A still holds its lock after 20 seconds have elapsed

Get answer

Question 7

A view named V.VIEW1 is based on a table named T.TABLE1. A user with DBADM authority issues the following statement:

GRANT INSERT ON v.view1 TO user1 WITH GRANT OPTION

Which of the following statements is USER1 authorized to execute?

A. GRANT INSERT ON t.table1 TO user2
B. GRANT CONTROL ON v.view1 TO user2
C. GRANT ALL PRIVILEGES ON v.view1 TO user2
D. GRANT INSERT ON v.view1 TO user2

Get answer

Question 8

Given the following CREATE TABLE statement

CREATE TABLE EMPLOYEE
       (EMPNO          CHAR(3) NOT NULL,
       FIRSTNAME   CHAR(20) NOT NULL,
       MIDINIT           CHAR(1),
       LASTNAME    CHAR(20) NOT NULL,
       SALARY          DECIMAL(10, 2))

Which of the following will retrieve the rows that have a missing value in the MIDINIT column?

A. SELECT * FROM employee WHERE midinit = ' '
B. SELECT * FROM employee WHERE midinit = NULL
C. SELECT * FROM employee WHERE midinit = " "
D. SELECT * FROM employee WHERE midinit IS NULL

Get answer

Question 9

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'

Get answer

Question 10

Which of the following DB2 UDB isolation levels will only lock rows during read process-ing if another transaction tries to drop the table the rows are being read from?

A. Repeatable Read
B. Read Stability
C. Cursor Stability
D. Uncommitted Read

Get answer

ANSWER KEY

Question 1

The correct answer is D -- ON DELETE CASCADE

The ON DELETE RESTRICT delete rule and the ON DELETE NO ACTION delete rule prevent the deletion of parent rows in a parent table if dependent rows that reference the primary row being deleted exist in the corresponding child table, and the ON DELETE SET NO VALUE delete rule is an invalid rule. On the other hand, the ON DELETE CASCADE delete rule will allow rows in the parent table to be deleted; if dependent rows that reference the primary row being deleted exist in the corresponding child table, they will be deleted as well.

Question 2

The correct answer is B -- Buffer pools

Locks can only be acquired for table spaces, tables, and rows.

Question 3

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.

Question 4

The correct answer is C -- 100

The check constraint (CONST1) for TABLEA will only allow the values 1, 2, or 3 to be entered into column COL1. The NOT NULL constraint prohibits null values, the value 0 is not a valid value, and the value '1' is a character value (the column COL1 was defined using a numeric data type).

Question 5

The correct answer is D -- SELECT * FROM tab1 FULL OUTER JOIN tab2 ON col_1 = col_a

When a full outer join operation is performed, rows that would have been returned by an inner join operation, together with and 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.

Question 6

The correct answer is A -- Application B will acquire the lock it needs

Anytime one transaction holds a lock on a data resource and another transaction attempts to acquire a lock on the same resource, the DB2 Database Manager will examine each lock's state and determine whether they are compatible. If the state of a lock placed on a data resource by one transaction is such that another lock can be placed on the same resource by another transaction before the first lock acquired is released, the locks are said to be compatible and the second lock will be acquired. However, if the locks are not compatible, the transaction requesting the incompatible lock must wait until the transaction holding the first lock is terminated before it can acquire the lock it needs. If the requested lock is not acquired before the time interval specified in the locktimeout configuration parameter has elapsed, the waiting transaction receives an error message and is rolled back.

Question 7

The correct answer is D -- GRANT INSERT ON v.view1 TO user2

The first GRANT statement (Answer A), when executed, would attempt to give user USER2 INSERT privilege on table T.TABLE1—since user USER1 does not have the authority needed to grant this privilege, this statement would fail; the second GRANT statement (Answer B) is not valid because only users with System Administrator (SYSADM) authority or Database Administrator (DBADM) authority are allowed to explicitly grant CONTROL privilege on any object—again, user USER1 does not have the authority needed to grant this privilege; and the third GRANT statement (Answer C), when executed, would attempt to give user USER2 every view privilege available (except the CONTROL privilege) on view V.VIEW1—since user USER1 does not have the authority needed to grant these privileges, this statement would also fail.

Question 8

The correct answer is D -- SELECT * FROM employee WHERE midinit IS NULL

The proper way to test for a missing value (or null) is by using the NULL predicate with a WHERE clause, and answer D shows the correct way to construct such a WHERE clause. Keep in mind that NULL, zero (0), and blank (" ") are not the same value. NULL is a special marker used to represent missing information, while zero and blank (empty string) are actual values that can be stored in a column to indicate a specific value (or lack thereof).

Question 9

The correct answer is D -- UPDATE stock SET (description, quantity, price) = (NULL, 0, 0) WHERE category <> 'S'

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.

Question 10

The correct answer is D -- Uncommitted Read

Usually locks are not acquired during processing when the Uncommitted Read isolation level is used. However, rows that are retrieved by a transaction using the Uncommitted Read isolation level will be locked if another transaction attempts to drop or alter the table from which the rows were retrieved.