C2090-730 Online Practice Questions and Answers

Which of the following will allow user USER1 to change the comment associated with a table named TABLE1?

A. GRANT UPDATE ON TABLE table1 TO user1

B. GRANT CONTROL ON TABLE table1 TO user1

C. GRANT ALTER ON TABLE table1 TO user1

D. GRANT REFERENCES ON TABLE table1 TO user1

Which of the following statements is used to revoke all DML privileges on table EMPLOYEE from user TOM?

A. REVOKE ALL PRIVILEGES FROM USER tom

B. REVOKE ALL ON EMPLOYEE FROM USER tom

C. REVOKE EXECUTE ON EMPLOYEE FROM USER tom

D. REVOKE PRIVILEGES ON EMPLOYEE FROM USER tom

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 FULL OUTER JOIN department d ON e.deptid = d.id WHERE d.id = '20'

C. SELECT e.name,d.deptname FROM employee e RIGHT OUTER JOIN department d ON e.deptid = d.id WHERE d.id = '20'

D. SELECT e.name,d.deptname FROM employee e LEFT 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'

A stored procedure has been created with the following statement:

CREATE PROCEDURE proc1 (IN var1 VARCHAR(10), OUT rc INTEGER) SPECIFIC myproc LANGUAGE SQL ...

What is the correct way to invoke this procedure from the command line processor (CLP)?

A. CALL proc1 ('SALES', ?)

B. CALLmyproc ('SALES', ?)

C. CALL proc1 (SALES, ?)

D. RUN proc1 (SALES, ?)

Given the following table:

TEMP_DATA

TEMP DATE

45 12/25/2006 51 12/26/2006 67 12/27/2006 72 12/28/2006 34 12/29/2006 42 12/30/2006

And the following SQL statement:

CREATE FUNCTION degf_to_c (temp INTEGER) RETURNS INTEGER LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC BEGIN ATOMIC DECLARE newtemp INTEGER; SET newtemp = temp - 32; SET newtemp = newtemp * 5; RETURN newtemp / 9; END

Which two of the following SQL statements illustrate the proper way to invoke the scalar function DEGF_TO_C?

A. VALUESdegf_to_c(32)

B. SELECT date,degf_to_c(temp) AS temp_c FROM temp_data

C. CALLdegf_to_c(32)

D. SELECT * FROMTABLE(degf_to_c(temp)) AS temp_c

E. VALUESdegf_to_c(32) AS temp_c

Which of the following can be used to ensure that once a row has been inserted in table TABLEX, the column MAINID in that row cannot be updated?

A. Define the column MAINID as NOT UPDATABLE.

B. Define the column MAINID as a PRIMARY KEY.

C. Define the column MAINID as a FOREIGN KEY.

D. Define an UPDATE trigger on table TABLEX.

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

A. ""

B. "John Doe"

C. ""

D. ""

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

Application A is running under the Repeatable Read isolation level and holds an Update lock on table TAB1. Application B wants to query table TAB1 and cannot wait for Application A to release its lock. Which isolation level should Application B run under to achieve this objective?

A. Repeatable Read

B. Read Stability

C. Cursor Stability

D. Uncommitted Read

In which of the following situations would DB2 retain resources associated with a transaction at COMMIT time?

A. A cursor is defined as WITH HOLD.

B. Another user executes the same transaction.

C. The application program amends during COMMIT.

D. The transaction terminates abnormally during COMMIT.