IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 11:

  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%

  Correct Answer: B

• Question 12:

  Which of the following will begin a new unit of work?

  A. The CONNECT statement

  B. The first FETCH of a cursor

  C. The BEGIN TRANSACTION statement

  D. The first executable SQL statement

  Correct Answer: D

• Question 13:

  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

  Correct Answer: B

• Question 14:

  Which kind of non-sourced UDF can be created so that it only returns a single value?

  A. Row

  B. Table

  C. Scalar

  D. Column

  Correct Answer: C

• Question 15:

  Given that tables T1 and T2 contain the following rows: Table T1: C1 C2

  1 4 1 3 1 2 Table T2: C1 C2

  1 1 1 2 1 3

  Which of the following queries will return only those rows that exist in both T1 and T2?

  A. SELECT * FROM t1 UNION SELECT * FROM t2

  B. SELECT * FROM t1 UNION DISTINCT SELECT * FROM t2

  C. SELECT * FROM t1 INTERSECT SELECT * FROM t2

  D. SELECT * FROM t1 WHERE (c1,c2)= (SELECT c1,c2 FROM t2)

  Correct Answer: C

• Question 16:

  Application A holds an Update lock on a single row and application B is trying to read that row. If both applications are using isolation level UR, which of the following will occur?

  A. Application B will read the row.

  B. Applications A and B will cause a deadlock situation.

  C. Application B will wait until applicationA releases the Update lock.

  D. Application A will be terminated so that application B can read the row.

  Correct Answer: A

• Question 17:

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

  A. Row

  B. Alias

  C. Bitmap

  D. Column

  Correct Answer: A

• Question 18:

  Given the following stored procedure:

  CREATE PROCEDURE increase_salary ( IN p_workdept CHAR(6), OUT p_sum DECIMAL(9,2) )

  SET p_sum = (SELECT SUM(salary) FROM employee WHERE workdept=p_workdept);

  How can this stored procedure be called from the Command Line Processor?

  A. CALLincrease_salary('A00')

  B. VALUESincrease_salary('A00')

  C. CALLincrease_salary('A00', ?)

  D. VALUESincrease_salary('A00', ?)

  Correct Answer: A

• Question 19:

  Which of the following best describes the lock protection provided by DB2 for the current row of a cursor?

  A. The cursor is only protected from updates and deletes by concurrent applications.

  B. The row is only protected from positioned updates and deletes that reference another cursor of the same application.

  C. The cursor is protected from positioned updates and deletes that reference another cursor of a different application.

  D. The row is protected from updates and deletes by the current application and from positioned updates and deletes that reference another cursor of the same application

  Correct Answer: A

• Question 20:

  Which of the following is TRUE for the DB2 isolation level Cursor Stability (CS)?

  A. An application process acquires at least a share lock on the current row of every cursor.

  B. Any row that is read during a unit of work cannot be changed by other application processes until the unit of work is complete.

  C. Any row changed by another application process can be read, even if the change has not been committed by that application process.

  D. An application process that issues the same query more than once in a unit of work will not see additional rows caused by other application processes appending new information to the database.

  Correct Answer: A