IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 161:

  Given the following expression:

  SELECT QUANTITY,

  CASE WHEN ITEMCODE = '099' THEN "SILVER"

  WHEN ITEMCODE = '788' THEN "GOLD"

  WHEN ITEMCODE = '899' THEN "PLATINUM"

  ELSE ERROR

  END

  FROM SUPPLIER

  What will be the result of the query if the following data is evaluated by the CASE expression?

  QUANTITY ITEMCODE

  3 099

  4 099

  1 788

  1 899

  5 009

  3 788

  1 899

  A. 3, SILVER 4, GOLD 1, SILVER 1, GOLD 5, PLATINUM 3, SILVER 1, GOLD

  B. 3, SILVER 4, SILVER 1, GOLD 1, PLATINUM 5, ERROR 3, GOLD 1, PLATINUM

  C. C. C. 3, SILVER 4, SILVER 1, PLATINUM 1, ERROR 5, SILVER 3, GOLD 1, PLATINUM

  D.

  E.

  F. 3, SILVER 4, SILVER 1, GOLD 1, GOLD 5, SILVER 1, PLATINUM

  Correct Answer: B

• Question 162:

  Given the following query:

  SELECT quantity,

  CASE WHEN itemcode = '099' THEN 'SILVER'

  WHEN itemcode = '788' THEN 'GOLD'

  WHEN itemcode = '899' THEN 'PLATINUM'

  ELSE 'ERROR'

  END

  FROM supplier

  What will be the result of the query if the following data is evaluated by the CASE expression?

  SUPPLIER

  QUANTITY ITEMCODE 3 099 4 099 1 788 1 899 5 009 3 788 1 899

  A. 3,SILVER 4,GOLD 1,SILVER 1,GOLD 5,PLATINUM 3,SILVER 1,GOLD

  B. 3,SILVER 4,SILVER 1,GOLD 1,PLATINUM 5,ERROR 3,GOLD 1,PLATINUM

  C. 3,SILVER 4,SILVER 1,PLATINUM 1,ERROR 5,SILVER 3,GOLD 1,PLATINUM

  D. 3,SILVER 4,SILVER 1,GOLD 1,GOLD 5,SILVER 3,ERROR 1,PLATINUM

  Correct Answer: B

• Question 163:

  The opening of cursor CSR01 produces the following result set:

  STUDENT LASTNM FIRSTNM CLASSNO 123 Brown John T100 213 Bailey James T100 312 Carter Arlene T210 465 Chas Devon T305 546 Davis Steven T405

  If this Fetch statement is executed:

  FETCH csr01 INTO :studnum, :firstname, :lastname, :class

  Which of the following DELETE statements will cause this row to be deleted?

  A. DELETE ALL FROM tab01 FOR CURRENT OF csr01

  B. DELETE FROM tab01 FOR CURRENT csr01 WITH RS

  C. DELETE * FROM tab01 WHERE CURRENT csr01 WITH CS

  D. DELETE FROM tab01 WHERE CURRENT OF csr01 WITH RR

  Correct Answer: D

• Question 164:

  Given the following table: TAB1

  COL1 COL2

  A 10 B 20 C 30 A 10 D 40 C 30

  Assuming the following results are desired:

  TAB1

  COL1 COL2

  A 10 B 20 C 30 D 40

  Which of the following statements will produce the desired results?

  A. SELECT UNIQUE * FROM tab1

  B. SELECT DISTINCT * FROM tab1

  C. SELECTUNIQUE(*) FROM tab1

  D. SELECTDISTINCT(*) FROM tab1

  Correct Answer: B

• Question 165:

  If the following SQL statements are executed in the order shown:

  CREATE TABLE table1 (c1 INTEGER, c2 INTEGER);

  INSERT INTO table1 VALUES (123, 456);

  UPDATE table1 SET c1 = NULL;

  What will be the result of the following statement?

  SELECT * FROM table1;

  A. C1 C2

  123 456

  1 record(s) selected.

  B. C1 C2

  NULL 456

  1 record(s) selected.

  C. C1 C2 1 record(s) selected.

  D. C1 C2 0 456 1 record(s) selected.

  Correct Answer: C

• Question 166:

  Given the following two tables: TAB1 COL_1 COL_2 A 10

  B 12 C 14 TAB2

  COL_A COL_B A 21 C 23 D 25

  Assuming the following results are desired:

  COL_1 COL_2 COL_A COL_B A 10 A 21 B 12 - C 14 C 23 - - D 25

  Which of the following joins will produce the desired results?

  A. SELECT * FROM tab1 INNER JOIN tab2 ON col_1 =col_a

  B. SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON col_1 =col_a

  C. SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON col_1 =col_a

  D. SELECT * FROM tab1 FULL OUTER JOIN tab2 ON col_1 =col_a

  Correct Answer: D

• Question 167:

  Given the following two tables:

  NAMES

  NAME NUMBER

  Wayne Gretzky 99 Jaromir Jagr 68 Bobby Orr 4 Bobby Hull 23 Mario Lemieux 66

  POINTS

  NAME POINTS

  Wayne Gretzky 244 Bobby Orr 129 Brett Hull 121 Mario Lemieux 189 Joe Sakic 94

  How many rows would be returned using the following statement?

  SELECT name FROM names, points

  A. 0

  B. 5

  C. 10

  D. 25

  Correct Answer: D

• Question 168:

  Given the following CREATE TABLE statement:

  CREATE TABLE EMPLOYEE

  (EMPNO CHAR(3) NOT NULL,

  FIRSTNAME CHAR(20) NOT NULL,

  MIDINIT CHAR(1),

  LASTNAME CHAR(20) NOT NULL,

  SALARY DECIMAL(10, 2))

  Which of the following will retrieve the rows that have a missing value in the MIDINIT column?

  A. SELECT * FROM employee WHEREmidinit = ' '

  B. SELECT * FROM employee WHEREmidinit = NULL

  C. SELECT * FROM employee WHEREmidinit = " "

  D. SELECT * FROM employee WHEREmidinit IS NULL

  Correct Answer: D

• Question 169:

  57 Given table T1 has column I1 containing the following data: I1

  If the following sequence of SQL statements is applied within a single unit of work:

  UPDATE t1 SET i1 = 3 WHERE i1 = 2; S

  AVEPOINT s1 ON ROLLBACK RETAIN CURSORS;

  UPDATE t1 SET i1 = 5 WHERE i1 = 3;

  SAVEPOINT s2 ON ROLLBACK RETAIN CURSORS;

  INSERT INTO t1 (i1) VALUES (6);

  ROLLBACK TO SAVEPOINT s1;

  UPDATE t1 SET i1 = 2 WHERE i1 = 4;

  COMMIT;

  What is the expected sequence of values returned from?

  SELECT i1 FROM t1 ORDER BY i1

  A. 1, 2, 3, 3

  B. 1, 2, 2, 4

  C. 1, 2, 3, 3, 6

  D. 1, 2, 2, 5, 6

  Correct Answer: A

• Question 170:

  Given the following table and XML data stored in the CONTACTINFO column:

  CREATE TABLE clients(

  id INT PRIMARY KEY NOT NULL,

  name VARCHAR(50),

  status VARCHAR(10),

  contactinfo XML)

  25 EastCreek

  Toronto

  Ontario

  M8X-3T6

  4165551358

  9051112222

  9051112222

  by fax

  What is the result of the following XQuery expression?

  for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client//fax return $y

  A. 9051112222

  B. 9051112222

  C. No results because there is no child element of

  D. No results because theXQuery expression is not a valid FLWOR expression

  Correct Answer: B