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

• Question 31:

  Which SQL statement will retrieve the department number (WORKDEPT) and average departmental salary (SALARY) for all departments in a table named EMPLOYEE and arrange the result set in ascending order by average departmental salary?

  A. SELECT workdept, AVG(salary)FROM employeeORDER BY workdeptGROUP BY 2

  B. SELECT workdept, AVG(salary)FROM employeeORDER BY workdeptARRANGE BY 2

  C. SELECT workdept, AVERAGE(salary)FROM employeeGROUP BY workdeptORDER BY 2

  D. SELECT workdept, AVG(salary)FROM employeeGROUP BY workdeptORDER BY 2

  Correct Answer: D

• Question 32:

  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 33:

  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 34:

  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 employeeGROUP BY jobORDER BY job;

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

  C. SELECT job, MIN(salary), MAX(salary)FROM employeeGROUP BY SALARYORDER BY job;

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

  Correct Answer: A

• Question 35:

  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 employeeWHERE salary < 100000

  B. SELECT salary FROM employeeWHERE salary > 100000

  C. SELECT salary FROM employeeWHERE salary > 100,000.00

  D. SELECT * FROM employeeWHERE salary GREATER THAN 100000

  Correct Answer: B

• Question 36:

  Which product replaced the Control Center used in versions prior to DB2 v10?

  A. IBM Data Studio

  B. IBM Administrator

  C. IBM Data Manager

  D. IBM Command Center

  Correct Answer: A

• Question 37:

  Which DB2 product is NOT suitable for very large data warehouse applications?

  A. DB2 for i

  B. DB2 for AIX

  C. DB2 for z/OS

  D. DB2 for Linux

  Correct Answer: A

• Question 38:

  Which platform supports DB2 pureScale environments?

  A. AIX

  B. iOS

  C. z/OS

  D. Windows

  Correct Answer: A

• Question 39:

  Which tool allows users to connect to a DB2 database using a wizard?

  A. Control Center

  B. IBM Data Studio

  C. Universal Connection Expert

  D. DB2 Connection Pool Manager

  Correct Answer: B

• Question 40:

  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 transactionsFROM sales

  B. SELECT name, COUNT(DISTINCT name) AS transactionsFROM sales

  C. SELECT name, COUNT(*) AS transactionsFROM salesGROUP BY name

  D. SELECT DISTINCT name, COUNT(*) AS transactionsFROM salesGROUP BY transactions

  Correct Answer: C