IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 171:

  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)

  Correct Answer: A

• Question 172:

  Given that tables T1 and T2 contain the following rows:

  Table T1: C1 C2

  5 4 5 2 5 5 Table T2: C1 C2

  5 1 5 2 5 3 Which of the following queries will return only those rows that exist in T1 and not in T2 ?

  A. SELECT * FROM T1 MINUS SELECT * FROM T2

  B. SELECT * FROM T1 EXCEPT SELECT * FROM T2

  C. SELECT * FROM T2 UNION EXCEPT SELECT * FROM T1

  D. SELECT * FROM T1 NOT EXISTS SELECT * FROM T2

  Correct Answer: B

• Question 173:

  How should the following UDF be invoked in order to convert US currency values stored in the EXPENSES table into Canadian currency?

  CREATE FUNCTION getratews11 ( country1 VARCHAR(100), country2 VARCHAR(100) ) RETURNS DOUBLE LANGUAGE SQL CONTAINS SQL EXTERNAL ACTION NOT DETERMINISTIC BEGIN ... END

  A. CALLgetratews11('USA','CANADA')

  B. CALLexpenses.getratews11('USA','CANADA')

  C. SELECTgetratews11('USA','CANADA') FROM expenses

  D. SELECT * FROMTABLE(getratews11('USA','CANADA')) AS convert_currency

  Correct Answer: C

• Question 174:

  Given the following tables:

  CONTINENTS ID NAME COUNTRIES 1 Antarctica 0 2 Africa 53 3 Asia 47 4 Australia 14 6 North America 23 7 South America 12 REGION ID LOCATION 1 East 2West

  How many rows would be returned using the following statement?

  SELECT location FROM continents, region

  A. 2

  B. 7

  C. 9

  D. 14

  Correct Answer: D

• Question 175:

  Which of the following statements eliminates all but one of each set of duplicate rows in the DEPT column in the STAFF table?

  A. SELECT UNIQUE dept FROM staff

  B. SELECT DISTINCT dept FROM staff

  C. SELECT (dept) UNIQUE FROM staff

  D. SELECT (dept) DISTINCT FROM staff

  Correct Answer: B

• Question 176:

  In which of the following situations should correlation names be used?

  A. A table referenced in the FROM clause has no indexed column.

  B. The table referenced in the FROM clause has more than 200 columns.

  C. Two or more tables in the FROM clause have identical column names.

  D. The FROM clause contains two or more tables in the SELECT statement.

  Correct Answer: C

• Question 177:

  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 1,GOLD 1,GOLD 5,SILVER 3,ERROR 1,PLATINUM

  Correct Answer: B

• Question 178:

  Which of the following is a feature of a unit of work?

  A. It applies to a single data server.

  B. It is a recoverable sequence of operations.

  C. Its value can be queried from the system catalog tables.

  D. It begins when the application connects to the data server.

  Correct Answer: B

• Question 179:

  Consider the following table called EMPLOYEES:

  ID FIRSTNAME LASTNAME JOB LEVEL ------------------------------------------------------------ 1 Paul Jones DBA 2 2 George Baker MGR 1 3 Roger Melvin CLERK 3 4 Jim Smith MGR 1 5 Kevin Street CLERK 3 6 Chris Eaton MGR 1

  If the following SQL statement is executed, how many rows will be deleted? DELETE FROM employees WHERE 1 = 1

  A. 0

  B. 1

  C. 3

  D. 6

  Correct Answer: D

• Question 180:

  Given the following table definitions:

  EMPLOYEE ID NAME DEPTID

  01 Smith 10 02 Bossy 20 03 Peterson 20 04 Goss 30 05 Pape 40 06 Avery 50 07 O'Neal 60 08 Carter 50 DEPARTMENT ID DEPTNAME

  05 Hardware 10 Kitchen 20 Shoes 30 Toys 40 Electronics 50 Automotive and the following query:

  SELECT e.id, d.deptname FROM employee e, department d WHERE e.deptid = d.id AND e.id > 4

  Which of the following queries will produce the same result set as the query above?

  A. SELECT e.id,d.deptname FROM employee e, department d WHERE e.id > 4

  B. SELECT e.id,d.deptname FROM employee e INNER JOIN department d ON e.deptid = d.id WHERE e.id > 4

  C. SELECT e.id,d.deptname FROM employee e FULL OUTER JOIN department d ON e.id = d.id WHERE e.id > 4

  D. SELECT e.id,d.deptname FROM employee e LEFT OUTER JOIN department d ON e.deptid = d.id WHERE e.id > 4 UNION ALL SELECT e.id, d.deptname FROM employee e RIGHT OUTER JOIN

  department d

  ON e.deptid = d.id

  WHERE e.id > 4

  Correct Answer: B