C2090-730 Online Practice Questions and Answers

A declared temporary table is used for which of the following purposes?

A. Backup purposes

B. Storing intermediate results

C. Staging area for load operations

D. Sharing result data sets between applications

A stored procedure object is created into which DB2 object?

A. Alias

B. Schema

C. Package

D. Routine Space

Given the following function: CREATE FUNCTION emplist () RETURNS TABLE ( id CHAR(6) , firstname VARCHAR(12) , lastname VARCHAR(15) ) LANGUAGE SQL BEGIN ATOMIC RETURN SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE WHERE WORKDEPT IN ('A00', 'B00'); END

How can this function be used in an SQL statement?

A. SELECTTABLE(EMPLIST()) FROM EMPLOYEE

B. SELECTTABLE(EMPLIST()) AS t FROM EMPLOYEE

C. SELECTEMPLIST(id, firstname, lastname) FROM EMPLOYEE

D. SELECT id,firstname, lastname FROM TABLE(EMPLIST()) AS t

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, department d WHERE e.id > 4

B. SELECT e.id,d.deptname FROM employee e INNER JOIN department d ON e.deptid = d.id WHERE e.id > 4

C. SELECT e.id,d.deptname FROM employee e FULL OUTER JOIN department d ON e.id = d.id WHERE e.id > 4

D. 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

In which of the following situations should correlation names be used?

A. A table referenced in the FROM clause has no indexed column.

B. The table referenced in the FROM clause has more than 200 columns.

C. Two or more tables in the FROM clause have identical column names.

D. The FROM clause contains two or more tables in the SELECT statement.

Which of the following is a valid wildcard character in a LIKE clause of a SELECT statement?

A. %

B. *

C. ?

D. \

Given the following CREATE TABLE statement:

CREATE TABLE customer(custid INTEGER, info XML)

And the following INSERT statements:

INSERT INTO customer VALUES (1000,

' John Doe

25 East Creek Drive

Raleigh

North Carolina

27603

919-555-1212

[email protected]

');

INSERT INTO customer VALUES (1000,

' Paul Smith

412 Stewart Drive

Toronto

Ontario

M8X-3T6

919-555-4444

[email protected]

');

What is the result of the following XQuery expression?

XQUERY declare default element namespace "http://custrecord.dat"; for $info in db2-fn:xmlcolumn ('CUSTOMER.INFO')/customerinfo where $info/addr/state-prov="Ontario" return $info/name/text();

A. Paul Smith

B. Paul Smith

C. Paul Smith

D. Paul Smith

Which of the following are all valid DB2 data types?

A. LONG VARCHAR, SMALLINT, NUMBER, BLOB

B. DECIMAL, DATE, DBCLOB, INTERVAL

C. NUMERIC, TIMESTAMP, BYTE, FLOAT

D. NUM, TIME, XML, DOUBLE

Which of the following isolation levels will lock all rows scanned to build a result data set?

A. Uncommitted Read

B. Cursor Stability

C. Read Stability D. Repeatable Read

An application needs a table for each connection that tracks the ID and Name of all items previously ordered and committed within the connection. The table also needs to be cleaned up and automatically removed each time a connection is ended. Assuming the ITEMS table was created with the following SQL statement:

CREATE TABLE items item_no INT, item_name CHAR(5), item_qty INT) Which of the following SQL statements will provide the table definition that meets the specified requirements?

A. DECLARE GLOBAL TEMPORARY TABLE tracker AS (SELECT item_no, item_name FROM items) WITH NO DATA ON COMMIT PRESERVE ROWS ON DISCONNECT DROP TABLE

B. DECLARE GLOBAL TEMPORARY TABLE tracker AS (SELECT item_no, item_name FROM items) WITH NO DATA ON COMMIT PRESERVE ROWS

C. CREATE TABLEsystmp.tracker AS (SELECT item_num, item_name FROM items) WITH NO DATA ON COMMIT PRESERVE ROWS

D. CREATE TABLE tracker AS (SELECT item_num, item_name FROM items) ON COMMIT PRESERVE ROWS ON DISCONNECT DROP TABLE