IBM Assessment: DB2 9 Family Fundamentals Sample Questions:

1. Given the following statements:
CREATE TABLE table1 (col1 INTEGER, col2 CHAR(3)); CREATE VIEW view1 AS SELECT col1, col2 FROM table1 WHERE col1 < 100 WITH LOCAL CHECK OPTION;
Which of the following INSERT statements will execute successfully?

A) INSERT INTO view1VALUES(100, abc)
B) INSERT INTO view1VALUES(50, 'abc')
C) INSERT INTO view1 VALUES (50,abc)
D) INSERT INTO view1VALUES(100, 'abc')

2. Which of the following strings can NOT be inserted into an XML column using XMLPARSE()?

A) "<p></p>"
B) "<employee />"
C) "<name>John Doe</name>"
D) "<?xml version='1.0' encoding='UTF-8' ?>"

3. If the following SQL statements are executed in the order shown:
CREATE TABLE orders (order_num INTEGER NOT NULL, buyer_name VARCHAR(35), amount NUMERIC(5,2));
CREATE UNIQUE INDEX idx_orderno ON orders (order_num);
Which of the following describes the resulting behavior?

A) Every ORDER_NUM value entered must be unique; no other index can be created for the ORDERS table that reference the ORDER_NUM column
B) Every ORDER_NUM value entered must be unique; whenever the ORDERS table is queried rows should be displayed in order of increasing ORDER_NUM values
C) Duplicate ORDER_NUM values are allowed; no other index can be created for the ORDERS table that reference the ORDER_NUM column
D) Every ORDER_NUM value entered must be unique; whenever the ORDERS table is queried rows will be displayed in no particular order

4. A DBA has been asked to create a table which will contain a substantial amount of detailed sales information for each calendar month and maintain it to contain only the last 12 months. Which of the following methods will facilitate the online removal of the oldest month's data?

A) Create a single table, extract the data to be retained using UNLOAD with a SELECT statement, drop and re-create the table then load only the data to be retained.
B) Create an MQT that selects the oldest month of data with the REFRESH IMMEDIATE option.
C) Create 12 separate tables, create a view based on all 12, drop the table with the oldest month's data then drop and re-create the view.
D) Create a range partitioned table, partitioned by month, and use the ALTER TABLE statement to detach the oldest month and attach storage for new data.

5. 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
DEPARTMENT
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 RIGHT OUTER JOIN department d ON e.deptid = d.id
WHERE d.id = '20'
C) SELECT e.name,d.deptname FROM employee e LEFT OUTER JOIN department d ON e.deptid = d.id WHERE d.id = '20'
D) SELECT e.name,d.deptname FROM employee e FULL 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'

Solutions: