IBM IBM Certified Advanced Database Administrator C2090-610 Questions & Answers

• Question 31:

  A table named DEPARTMENT contains the following data:

  

  What will happen?

  A. The statement will fail because a subquery cannot be used in an UPDATE statement.

  B. The statement will fail because the result set produced by the subquery will contain more than one row.

  C. The statement will succeed; the last record retrieved by the subquery will be used to provide a WORKDEPT value for the update operation.

  D. The statement will succeed; the first record retrieved by the subquery will be used to provide a WORKDEPT value for the update operation.

  Correct Answer: B

• Question 32:

  Which SQL statement would be used to retrieve only salaries that are greater than $100,000.00 from a table named EMPLOYEE?

  A. SELECT * FROM employee WHERE salary < 100000

  B. SELECT salary FROM employee WHERE salary > 100000

  C. SELECT salary FROM employee WHERE salary > 100,000.00

  D. SELECT * FROM employee WHERE salary GREATER THAN 100000

  Correct Answer: B

• Question 33:

  Which SQL statement should be used to retrieve the minimum and maximum salaries (SALARY) for each job code (JOB), sorted by job code, from a table named EMPLOYEE?

  A. SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY job ORDER BY job;

  B. SELECT job, MIN(salary), MAX(salary) FROM employee ORDER BY job;

  C. SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY SALARY ORDER BY job;

  D. SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY MIN(salary), MAX(salary) ORDER BY job;

  Correct Answer: A

• Question 34:

  If a table named MY_TAB contains 100 rows and the following statement is executed:

  

  How many rows will be updated?

  A. 0

  B. 1

  C. 10

  D. 99

  Correct Answer: C

• Question 35:

  An SQL function named DEPT_INFO was created as follows:

  

  What is the correct way to use this function in a query?

  A. SELECT dept_info(dept_id, dept_name)

  B. SELECT dept_id, dept_name FROM dept_info()

  C. SELECT TABLE dept_id, dept_name FROM dept_info()

  D. SELECT dept_id, dept_name FROM TABLE(dept_info()) AS results

  Correct Answer: D

• Question 36:

  A table named SALES contains a record for every sales transaction a company processes. A user wishes to see the number of transactions that are made by each salesman. Which SQL statement will produce the desired results?

  A. SELECT name, COUNT(*) AS transactions FROM sales

  B. SELECT name, COUNT(DISTINCT name) AS transactions FROM sales

  C. SELECT name, COUNT(*) AS transactions FROM sales GROUP BY name

  D. SELECT DISTINCT name, COUNT(*) AS transactions FROM sales GROUP BY transactions

  Correct Answer: C

• Question 37:

  Which statement about roll back operations is correct?

  A. When a ROLLBACK statement is executed, all locks held by the terminating transaction are released.

  B. When a ROLLBACK TO SAVEPOINT statement is executed, all locks acquired up to the savepoint are released.

  C. When a ROLLBACK TO SAVEPOINT statement is executed, all locks acquired after the savepoint are released.

  D. When a ROLLBACK statement is executed, all locks acquired for open cursors that were declared WITH HOLD, are held.

  Correct Answer: A

• Question 38:

  When a COMMIT statement is executed, what happens?

  A. All locks held on the database are automatically released.

  B. Data stored in global temporary tables is automatically deleted.

  C. Open cursors defined WITH HOLD are closed, but their data is retained.

  D. The current transaction is terminated and a new transaction boundary is started.

  Correct Answer: D

• Question 39:

  Which statement about large object (LOB) locators is true?

  A. A LOB locator is a data type that is used to store LOB data in binary files.

  B. A LOB locator is a data type that is used to store LOB data in a database.

  C. A LOB locator represents a value for a LOB resource that is stored in a database.

  D. A LOB locator represents a value for a LOB resource that is stored in a binary file.

  Correct Answer: C

• Question 40:

  Which DB2 product is a database cluster solution for non-mainframe platforms and is best suited for Online Transaction Processing (OLTP) workloads?

  A. DB2 Connect

  B. DB2 pureScale

  C. Workload Manager

  D. High Availability Disaster Recovery

  Correct Answer: B