C2090-730 Online Practice Questions and Answers

Which of the following commands is used to retrieve database names from the local catalog for DRDA host databases on System i and System z?

A. LIST DB DIRECTORY

B. LIST DCS DIRECTORY

C. LIST NODE DIRECTORY

D. LIST ACTIVE DATABASES

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 two tables:

TAB1 C1 C2

1 Antarctica 2 Africa 3 Asia 4 Australia TAB2 CX CY

5 Europe 6 North America 7 South America

Which of the following SQL statements will insert all rows found in table TAB2 into table TAB1?

A. INSERT INTO tab1 SELECTcx, cy FROM tab2

B. INSERT INTO tab1 VALUES (tab2.cx, tab2.cy)

C. INSERT INTO tab1 VALUES (SELECTcx, cy FROM tab2)

D. INSERT INTO tab1 (c1, c2) VALUES (SELECTcx, cy FROM tab2)

Given the following UPDATE statement:

UPDATE employees SET workdept =

(SELECT deptno FROM department WHERE deptno = 'A01') WHERE workdept IS NULL

Which of the following describes the result if this statement is executed?

A. The statement will fail because an UPDATE statement cannot contain asubquery.

B. The statement will only succeed if the data retrieved by thesubquery does not contain multiple records.

C. The statement will succeed; if the data retrieved by thesubquery contains multiple records, only the first record will be used to perform the update.

D. The statement will only succeed if every record in the EMPLOYEES table has a null value in the WORKDEPT column.

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

A. 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)

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 specified contains more than one NULL value

D. A unique index can only be created for a non-nullable column

Given the following requirements:

Create a table named TESTTAB, which has an identity column named ACTIVITYNO. Define the identity column to generate the values for the column by default. Start the values at 10 and increment by 10. Make the identity column unique.

Which of the following CREATE statements will successfully create this table?

A. CREATE TABLE TESTTAB (ACTIVITYNO SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 10), ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, UNIQUE(ACTIVITYNO))

B. CREATE TABLE TESTTAB (ACTIVITYNO SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 10), ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, UNIQUE(ACTNO))

C. CREATE TABLE TESTTAB (ACTIVITYNO SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 1), ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, UNIQUE(ACTIVITYNO))

D. CREATE TABLE TESTTAB (ACTIVITYNO SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10), ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, UNIQUE(ACTIVITYNO))

How does DB2 protect the integrity of indexes when data is updated?

A. Locks are acquired on the data.

B. Locks are acquired on index keys.

C. Locks are acquired on index pages.

D. Locks are acquired on index pointers.

To which of the following resources can a lock be applied?

A. Row

B. Alias

C. Bitmap

D. Column

The EMPLOYEE table contains the following information:

EMPNO NAME WORKDEPT

101 SAM A11 102 JOHN C12 103 JANE 104 PAT Remote 105 ANNE

106 BOB A11 The MANAGER table contains the following information: MGRID NAME DEPTNO EMPCOUNT 1 WU B01

2 JONES A11 3 CHEN - 4 SMITH - 5 THOMAS C12

After this statement is executed:

UPDATE manager m SET empcount = (SELECT COUNT(workdept) FROM employee e WHERE

workdept=m.deptno)

What is the result of the following query?

SELECT mgrid, empcount FROM MANAGER WHERE empcount IS NOT NULL ORDER BY mgrid

A. MGRID EMPCOUNT ----- -------- 1 0 22 5 1

B. MGRID EMPCOUNT ----- -------- 1 0 22 3 0 4 0 5 1

C. MGRID EMPCOUNT ----- -------- 1 3 2 33 3 4 3 5 3

D. MGRID EMPCOUNT ----- -------- 1 0 22 3 2 4 2 5 1

Given the following two tables:

TAB1 R1

A A A B B C C D E TAB2 R2

A A B B C C D Which of the following queries returns the following result set?

RETVAL

E

A. SELECT r1 ASretval FROM tab1 INTERSECT SELECT r2 AS retval FROM tab2

B. SELECT r1 ASretval FROM tab1 EXCEPT SELECT r2 AS retval FROM tab2

C. SELECT DISTINCT r1 ASretval FROM tab1, tab2 WHERE r1 <> r2

D. SELECT r1 ASretval FROM tab1 UNION SELECT r2 AS retval FROM tab2