IBM DB2 9 Family Fundamentals Sample Questions:

1. 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 person IN (SELECT person FROM table2)
B) DELETE FROM table1 WHERE person IN (SELECT id FROM table2)
C) DELETE FROM table1 WHERE id IN (SELECT id FROM table2)
D) DELETE FROM table1 WHERE id IN (SELECT person FROM table2)

2. A view is created with the following statement:
CREATE VIEW v1 AS SELECT col1, col2, col3, col4 FROM t1 WHERE col4 > 1000 WITH CHECK OPTION
What is the effect of the CHECK OPTION clause?

A) Any row inserted or updated through view V1 must meet the condition that col4 > 1000 and no row in table T1 can be updated such that col4 <= 1000, but new rows in the table can be inserted with col4 <= 1000.
B) Any row inserted or updated through view V1 must meet the condition that col4 > 1000.
C) From now on, any row inserted or updated in table T1 must meet the condition that col4 > 1000, but existing rows in the table are not checked.
D) At view creation, DB2 will check the data in table T1, and if in any row doesn't meet the condition col4 > 1000, the view creation will be rejected.

3. 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, SET quantity = 0, SET price = 0 WHERE category 'S'
B) UPDATE stock SET (description, quantity, price) = ('null', 0, 0) WHERE category 'S'
C) UPDATE stock SET (description, quantity, price) = (NULL, 0, 0) WHERE category 'S'
D) UPDATE stock SET description = 'NULL', quantity = 0, price = 0 WHERE category 'S'

4. Which object may be used during query optimization to improve the performance for a subset of SELECT queries?

A) Read Only Cursor
B) MQT
C) Trigger
D) Stored Procedure

5. 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) SELECTinvoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person, sales_date DESC
B) SELECTinvoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person, sales_date DESC
C) SELECTinvoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person DESC, sales_date
D) SELECTinvoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person DESC, sales_date

Solutions: