IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 131:

  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, ?)

  Correct Answer: A

• Question 132:

  Given the following table:

  TAB1

  COL1 COL2

  A 10

  B 20

  C 30

  D 40

  E 50

  And the following SQL statements:

  DECLARE c1 CURSOR WITH HOLD FOR

  SELECT * FROM tab1 ORDER BY col_1;

  OPEN c1;

  FETCH c1;

  FETCH c1;

  FETCH c1;

  COMMIT;

  FETCH c1;

  CLOSE c1;

  FETCH c1;

  Which of the following is the last value obtained for COL_2?

  A. 20

  B. 30

  C. 40

  D. 50

  Correct Answer: C

• Question 133:

  Given the following set of statements:

  CREATE TABLE tab1 (col1 INTEGER, col2 CHAR(20));

  COMMIT;

  INSERT INTO tab1 VALUES (123, 'Red');

  INSERT INTO tab1 VALUES (456, 'Yellow');

  SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS;

  DELETE FROM tab1 WHERE col1 = 123;

  INSERT INTO tab1 VALUES (789, 'Blue');

  ROLLBACK TO SAVEPOINT s1;

  INSERT INTO tab1 VALUES (789, 'Green');

  UPDATE tab1 SET col2 = NULL WHERE col1 = 789;

  COMMIT;

  Which of the following records would be returned by the following statement?

  SELECT * FROM tab1

  A. COL1 COL2

  123 Red

  456 Yellow

  2 record(s) selected.

  B. COL1 COL2

  456 Yellow 1 record(s) selected.

  C. COL1 COL2

  123 Red

  456 Yellow

  3 record(s) selected.

  D. COL1 COL2

  123 Red

  456 Yellow

  789 Green

  3 record(s) selected.

  Correct Answer: C

• Question 134:

  Which of the following best describes a unit of work?

  A. It is a recoverable sequence of operations whose point of consistency is established when a connection to a database has been established or when a mechanism known as asavepoint is created.

  B. It is a recoverable sequence of operations whose current point of consistency can be determined by querying the system catalog tables.

  C. It is a recoverable sequence of operations whose point of consistency is established when an executable SQL statement is processed after a connection to a database has been established or a previous transaction has been terminated.

  D. It is a recoverable sequence of operations whose point of consistency is only established if a mechanism known as asavepoint is created.

  Correct Answer: C

• Question 135:

  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

  Correct Answer: C

• Question 136:

  Given the following table definition:

  SALES

  INVOICE_NO CHAR(20) NOT NULL SALES_DATE DATE SALES_PERSON CHAR(20) REGION CHAR(20) SALES INTEGER

  If the following SELECT statement is executed, which of the following describes the order of the rows in the result data set produced? SELECT * FROM sales

  A. The rows are sorted by INVOICE_NO in ascending order.

  B. The rows are sorted by INVOICE_NO in descending order.

  C. The rows are ordered based on when they were inserted into the table.

  D. The rows are not sorted in any particular order.

  Correct Answer: D

• Question 137:

  Given the following SQL statements:

  CREATE TABLE tab1 (col1 INTEGER) INSERT INTO tab1 VALUES (NULL) INSERT INTO tab1 VALUES (1) CREATE TABLE tab2 (col2 INTEGER) INSERT INTO tab2 VALUES (NULL) INSERT INTO tab2 VALUES (1) INSERT INTO tab2 VALUES (2)

  What will be the result when the following statement is executed?

  SELECT * FROM tab1 WHERE col1 IN (SELECT col2 FROM tab2)

  A. COL1

  1 record(s) selected.

  B. COL1

  NULL

  2 record(s) selected.

  C. COL1

  -

  2 record(s) selected.

  D. COL1

  -

  1 record(s) selected.

  Correct Answer: A

• Question 138:

  Given the following table:

  STOCK

  CATEGORY CHAR(1)

  PARTNO CHAR(12)

  DESCRIPTION VARCHAR(40)

  QUANTITY INTEGER

  PRICE DEC(7,2)

  If items are indicated to be out of stock by setting DESCRIPTION to NULL and QUANTITY and PRICE to zero,

  which of the following statements updates the STOCK table to indicate that all items except those with

  CATEGORY of 'S' are temporarily out of stock?

  A. UPDATE stock SET description = 'NULL', quantity = 0, price = 0 WHERE category 'S'

  B. UPDATE stock SET description = NULL, SET quantity = 0, SET price = 0 WHERE category 'S'

  C. UPDATE stock SET (description, quantity, price) = ('null', 0, 0) WHERE category 'S'

  D. UPDATE stock SET (description, quantity, price) = (NULL, 0, 0) WHERE category 'S'

  Correct Answer: D

• Question 139:

  Given the following table:

  CURRENT_EMPLOYEES

  EMPID INTEGER NOT NULL

  NAME CHAR(20)

  SALARY DECIMAL(10,2)

  PAST_EMPLOYEES

  EMPID INTEGER NOT NULL

  NAME CHAR(20)

  SALARY DECIMAL(10,2)

  Assuming both tables contain data, which of the following statements will NOT successfully add data to table

  CURRENT_EMPLOYEES?

  A. INSERT INTOcurrent_employees (empid) VALUES (10)

  B. INSERT INTOcurrent_employees VALUES (10, 'JAGGER', 85000.00)

  C. INSERT INTOcurrent_employees SELECT empid, name, salary FROM past_employees WHERE empid =

  D. INSERT INTOcurrent_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)

  Correct Answer: D

• Question 140:

  Given the following table definitions:

  EMPLOYEES

  EMPID INTEGER

  NAME CHAR(20)

  DEPTID CHAR(3)

  SALARY DECIMAL(10,2)

  COMMISSION DECIMAL(8,2)

  DEPARTMENTS

  DEPTNO INTEGER DEPTNAME CHAR(20)

  Which of the following statements will produce a result data set that satisfies all of these conditions:

  > Displays the total number of employees in each department >> Displays the corresponding department name for each department ID >> Sorted by department employee count, from greatest to least

  A. SELECT *,COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC

  B. SELECTdeptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC

  C. SELECTdeptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 ASC

  D. SELECTdeptname, COUNT(*) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2

  Correct Answer: B