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

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

Sample Questions
Question 1

Which of the following are NOT stored in the system catalog tables?:

A. SQL statements used to create tables
B. SQL statements used to create triggers
C. SQL statements used to create constraints
D. Table names

Get answer

Question 2

Assuming table TAB1 contains 100 rows, which of the following queries will return only half of the rows available?

A. SELECT * FROM tab1 FIND FIRST 50 ROWS
B. SELECT * FROM tab1 FETCH FIRST 50 ROWS ONLY
C. SELECT * FROM tab1 WHILE ROW_NUM < 50
D. SELECT * FROM tab1 MAXROWS 50

Get answer

Question 3

If the following statement is executed:

CREATE TABLE employee
    (empid     INT NOT NULL GENERATED BY DEFAULT 
         AS IDENTITY (START WITH 1, INCREMENT BY 5),
     name      VARCHAR(20),
     dept      INT CHECK (dept BETWEEN 1 AND 20),
     hiredate  DATE WITH DEFAULT CURRENT DATE,
     salary    DECIMAL(7,2),
     PRIMARY KEY(empid),
  CONSTRAINT cst1 CHECK (YEAR(hiredate) > 2006 OR
         Salary > 60500));

Which of the following INSERT statements will fail?

A. INSERT INTO employee VALUES (15, 'Smith', 5, '01/22/2004', 92500.00)
B. INSERT INTO employee VALUES (DEFAULT, 'Smith', 2, '10/07/2002', 80250.00)
C. INSERT INTO employee VALUES (20, 'Smith', 5, NULL, 65000.00)
D. INSERT INTO employee VALUES (DEFAULT, 'Smith', 10, '11/18/2004', 60250.00)

Get answer

Question 4

Which of the following modes, when used with the LOCK TABLE statement, will cause the DB2 Database Manager to acquire a table-level lock that prevents other concurrent transac-tions from accessing data stored in the table while the owning transaction is active?

A. SHARE MODE
B. ISOLATED MODE
C. EXCLUSIVE MODE
D. RESTRICT MODE

Get answer

Question 5

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

Get answer

Question 6

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

Get answer

Question 7

Which of the following is NOT a characteristic of a unique index?

A. Each column in a base table can only participate in one unique index, regard-less of how the columns are grouped (the same column can not be used in mul-tiple unique indexes)
B. In order for an index to be used to support a unique constraint, it must have been defined with the UNIQUE attribute
C. A unique index cannot be created for a populated table if the key column speci-fied contains more than one NULL value
D. A unique index can only be created for a non-nullable column

Get answer

Question 8

Which of the following causes a lock that is being held by an application using the Cursor Stability isolation level to be released?

A. The cursor is moved to another row
B. The row the cursor is on is deleted by the application
C. The row the cursor is on is deleted by another application
D. The row the cursor is on needs to be updated by another application

Get answer

Question 9

If the following SQL statements are executed in the order shown:

CREATE TABLE table1 (c1 INTEGER, c2 INTEGER);
INSERT INTO table1 VALUES (123, 456);
UPDATE table1 SET c1 = NULL;

What will be the result of the following statement?

SELECT * FROM table1;

A. C1   C2
   –––  –––
   123   456
   1 record(s) selected.

B. C1   C2
   –––  –––
   NULL  456
   1 record(s) selected.

C. C1   C2
   –––  –––
     -     456
   1 record(s) selected.

D. C1   C2
   –––  –––
     0     456
   1 record(s) selected.

Get answer

Question 10

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.

Get answer

ANSWER KEY

Question 1

The correct answer is A -- SQL statements used to create tables

The system catalog tables are used to keep track of information like database object definitions, database object dependencies, database object privileges, column data types, and table constraints. In most cases, the complete characteristics of a database object are stored in one or more system catalog tables when the object is created. However in some cases, such as when triggers and constraints are defined, the actual SQL used to create the object is stored instead. Characteristics of tables, not the SQL used to create them, are stored in the system catalog tables.

Question 2

The correct answer is B -- SELECT * FROM tab1 FETCH FIRST 50 ROWS ONLY

The FETCH FIRST clause is used to limit the number of rows that are returned to the result data set produced in response to a query. When used, the FETCH FIRST clause is followed by a positive integer value and the words ROWS ONLY. This tells the DB2 Database Manager that the user/application executing the query does not want to see more than n number of rows, regardless of how many rows might exist in the result data set that would be produced were the FETCH FIRST clause not specified

Question 3

The correct answer is D -- INSERT INTO employee VALUES (DEFAULT, 'Smith', 10, '11/18/2004', 60250.00)

In this example, the statement "INSERT INTO employee VALUES (DEFAULT, 'Smith', 10, '11/18/2004', 60250.00)" will fail because the hire date and the salary specified violates check constraint CST1 – YEAR(hiredate) > 2006 OR salary > 60500)

Question 4

The correct answer is C -- EXCLUSIVE MODE

The LOCK TABLE statement allows a transaction to explicitly acquire a table-level lock on a particular table in one of two modes: SHARE and EXCLUSIVE. If a table is locked using the SHARE mode, a table-level Share (S) lock is acquired on behalf of the transaction, and other concurrent transactions are allowed to read, but not change, the data stored in the locked table. If a table is locked using the EXCLUSIVE mode, a table-level Exclusive (X) lock is acquired, and other concurrent transactions can neither access nor modify data stored in the locked table.

Question 5

The correct answer is B -- SELECT DISTINCT * FROM tab1

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 corre-sponding column of the second row.

Question 6

The correct answer is C -- 10

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.

Question 7

The correct answer is D -- A unique index can only be created for a non-nullable column

A unique index allows one and only one NULL value; the value "NULL" means a column's value is undefined and distinct from any other value. The remaining characteristics are true for unique indexes.

Question 8

The correct answer is B -- In order for an index to be used to support a unique constraint, it must have been defined with the UNIQUE attribute

If a row level lock is held by a application using the Cursor Stability isolation level, that lock remains in effect until either the cursor is moved to a new row (at which time the lock for the old row is released – if possible, and a new lock for the current row is acquired) or the transaction holding the lock is terminated.

Question 9

The correct answer is C -- NULL

The UPDATE statement assigns a NULL value to column C1 and NULL values are displayed as a dash (-).

Question 10

The correct answer is D -- The rows are not sorted in any particular order.

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.