C2090-730 Online Practice Questions and Answers

A user needs to create a trigger that will update table T2 whenever a row is added to table T1. Assuming the user has all appropriate privileges for table T2, which privilege is required on table T1 to create the trigger?

A. REFERENCES

B. SELECT

C. UPDATE

D. ALTER

A user wishing to invoke an SQL stored procedure that queries a table must have which of the following privileges?

A. CALL privilege on the procedure; SELECT privilege on the table

B. CALL privilege on the procedure; REFERENCES privilege on the table

C. EXECUTE privilege on the procedure; SELECT privilege on the table

D. EXECUTE privilege on the procedure; REFERENCES privilege on the table

User USER1 has the privileges needed to invoke a stored procedure named GEN_RESUME. User USER2 needs to be able to call the procedure - user USER1 and all members of the group PUBLIC should no longer be allowed to call the procedure. Which of the following statement(s) can be used to accomplish this?

A. GRANT EXECUTE ON ROUTINEgen_resume TO user2 EXCLUDE user1, PUBLIC

B. GRANT EXECUTE ON PROCEDUREgen_resume TO user2; REVOKE EXECUTE ON PROCEDURE gen_resume FROM user1, PUBLIC;

C. GRANT CALL ON ROUTINEgen_resume TO user2 EXCLUDE user1 PUBLIC

D. GRANT CALL ON PROCEDUREgen_resume TO user2; REVOKE CALL ON PROCEDURE gen_resume FROM user1, PUBLIC;

The following SQL statements were executed in sequence:

CREATE DISTINCT TYPE salary AS decimal(7,2) WITH COMPARISONS; CREATE TABLE staffsalary(empid INT, empsalary salary); INSERT INTO staffsalary VALUES (10, 50000), (20, 50000.00); UPDATE staffsalary SET empsalary = 60000 What is the current content of the staffsalary table?

A. ID | EMPSALARY ----------------- 10 | 60000 20 | 50000.00 ----------------

B. ID | EMPSALARY ----------------- 10 | 50000.00 20 | 50000.00 ----------------

C. ID | EMPSALARY ----------------- 10 | 60000.00 20 | 60000.00 ----------------

D. ID | EMPSALARY ----------------- 10 | 60000.00 20 | 50000.00 ----------------

The following SQL statement:

DELETE FROM tab1 WHERE CURRENT OF csr1 WITH RR Is used to perform which type of delete operation?

A. Positioned

B. Searched

C. Embedded

D. Dynamic

Given the following tables:

YEAR_2006 EMPID NAME

1 Jagger, Mick 2 Richards, Keith 3 Wood, Ronnie 4 Watts, Charlie 5 Jones, Darryl 6 Leavell, Chuck YEAR_1962 EMPID NAME

1 Jagger, Mick 2 Richards, Keith 3 Jones, Brian 4 Wyman, Bill 5 Chapman, Tony 6 Stewart, Ian

If the following SQL statement is executed, how many rows will be returned? SELECT name FROM year_2006 UNION SELECT name FROM year_1962

B. 6

C. 10

D. 12

When does a view get populated?

A. When it is created

B. When it is referenced in an INSERT statement

C. The first time any executable SQL statement references it

D. Any time an executable SQL statement references it

What is the maximum size that can be specified when creating an XML column in a DB2 table?

A. No size is specified

B. The buffer pool size

C. The page size for the table space

D. The extent size for the table space

Which type of key is defined on the child table to implement a referential constraint?

A. Unique key

B. Primary key

C. Foreign key

D. Composite key

Given the following table definition:

STOCK:

item VARCHAR(30) status CHAR(1) quantity INT price DEC(7,2)

If items are indicated to be out of stock by setting STATUS to NULL and QUANTITY and PRICE to zero, which of the following statements would be used to update the STOCK table to indicate that all the items whose description begins with the letter "S" are out of stock?

A. UPDATE stock SET (status = NULL; quantity, price = 0) WHERE item LIKE S%

B. UPDATE stock SET (status, quantity,price) = (NULL, 0, 0) WHERE item LIKE S%

C. UPDATE stock SET status = NULL, SET quantity = 0, SET price = 0 WHERE item LIKE 'S%'

D. UPDATE stock SET (status = NULL), (quantity = 0), (price = 0) WHERE item LIKE S%