IBM Assessment: DB2 9 Family Fundamentals Sample Questions:

1. Which of the following CREATE TABLE statements will NOT be successful?

A) CREATE TABLE t1 (c1 XML NOT NULL UNIQUE, c2 INT)
B) CREATE TABLE t1 (c1 XML CHECK (c1 IS VALIDATED), c2 INT)
C) CREATE TABLE t1 (c1 XML, c2 XML)
D) CREATE TABLE t1 (c1 XML NOT NULL, c2CHAR(20))

2. Given the following table definitions:
EMPLOYEE ID NAME DEPTID
01 Smith 10 02 Bossy 20 03 Peterson 20 04 Goss 30 05 Pape 40 06 Avery 50 07 O'Neal 60 08 Carter 50 DEPARTMENT ID DEPTNAME
05 Hardware 10 Kitchen 20 Shoes 30 Toys 40 Electronics 50 Automotive and the following query:
SELECT e.id, d.deptname FROM employee e, department d WHERE e.deptid = d.id AND e.id > 4
Which of the following queries will produce the same result set as the query above?

A) SELECT e.id,d.deptname FROM employee e FULL OUTER JOIN department d ON e.id = d.id WHERE e.id > 4
B) SELECT e.id,d.deptname FROM employee e, department d WHERE e.id > 4
C) SELECT e.id,d.deptname FROM employee e LEFT OUTER JOIN department d ON e.deptid = d.id WHERE e.id > 4 UNION ALL SELECT e.id, d.deptname FROM employee e RIGHT OUTER JOIN department d ON e.deptid = d.id WHERE e.id > 4
D) SELECT e.id,d.deptname FROM employee e INNER JOIN department d ON e.deptid = d.id WHERE e.id > 4

3. 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 descending order.
B) The rows are not sorted in any particular order.
C) The rows are sorted by INVOICE_NO in ascending order.
D) The rows are ordered based on when they were inserted into the table.

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

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

5. Given the following DDL for the PARTS table:
CREATE TABLE parts (part_no INT(9) NOT NULL, part_name VARCHAR(24), part_remain INT(9));
All part numbers entered will be different and all rows should be displayed in order of increasing part numbers whenever the table is queried. Which of the following create index statements will meet this criteria and require the least amount of storage for the index object?

A) CREATE UNIQUE INDEXidx_partno ON parts(part_no, part_name ASC)
B) CREATE UNIQUE INDEXidx_partno ON parts(part_no)
C) CREATE UNIQUE INDEXidx_partno ON parts(part_name, part_no ASC)
D) CREATE UNIQUE INDEXidx_partno ON parts(part_name ASC)

Solutions: