IBM IBM Certified Database Associate C2090-730 Questions & Answers

• Question 151:

  Which two of the following statements are true about the HAVING clause?

  A. The HAVING clause is used in place of the WHERE clause.

  B. The HAVING clause uses the same syntax as the WHERE clause.

  C. The HAVING clause can only be used with the GROUP BY clause.

  D. The HAVING clause accepts wildcards.

  E. The HAVING clause uses the same syntax as the IN clause.

  Correct Answer: BC

• Question 152:

  Given the following statement:

  SELECT hyear, AVG(salary)

  FROM (SELECT YEAR(hiredate) AS hyear, salary

  FROM employee WHERE salary > 30000)

  GROUP BY hyear

  Which of the following describes the result if this statement is executed?

  A. The statement will return the year and average salary for all employees that have a salary greater than $30,000, sorted by year.

  B. The statement will return the year and average salary for all employees hired within a given year that have a salary greater than $30,000.

  C. The statement will return the year and average salary for all years that every employee hired had a salary greater than $30,000.

  D. The statement will return the year and average salary for all years that any employee had a salary greater than $30,000.

  Correct Answer: B

• Question 153:

  Given the following table definition:

  SALES

  INVOICE_NO CHAR(20) NOT NULL

  SALES_DATE DATE

  SALES_PERSON VARCHAR(25)

  REGION CHAR(20)

  SALES_AMT DECIMAL(9,2)

  Which of the following queries will return SALES information, sorted by SALES_PERSON, from A to Z, and

  SALES_DATE, from most recent to earliest?

  A. SELECTinvoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person, sales_date DESC

  B. SELECTinvoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person DESC, sales_date

  C. SELECTinvoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person, sales_date DESC

  D. SELECTinvoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person DESC, sales_date

  Correct Answer: D

• Question 154:

  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 Watts, Charlie 6 Stewart, Ian

  If the following SQL statement is executed, how many rows will be returned?

  SELECT name FROM year_2007 UNION ALL SELECT name FROM year_1962

  A. 6

  B. 9

  C. 10

  D. 12

  Correct Answer: D

• Question 155:

  Which of the following is a valid wildcard character in a LIKE clause of a SELECT statement?

  A. %

  B. *

  C. ?

  D. \

  Correct Answer: A

• Question 156:

  Given the following two tables:

  NAMES

  NAME NUMBER

  Wayne Gretzky 99 Jaromir Jagr 68 Bobby Orr 4 Bobby Hull 23 Brett Hull 16 Mario Lemieux 66 Mark Messier 11

  POINTS

  NAME POINTS

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

  Which of the following statements will display the player name, number, and points for all players that have scored points?

  A. SELECT p.name,n.number, p.points FROM names n INNER JOIN points p ON n.name =

  B. name

  C. SELECT p.name,n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name

  D. SELECT p.name,n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name

  E. SELECT p.name,n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name

  Correct Answer: C

• Question 157:

  Given the following queries:

  SELECT c1 FROM tab1; SELECT c1 FROM tab2;

  Which of the following set operators can be used to produce a result data set that contains only records that are not found in the result data set produced by each query after duplicate rows have been eliminated?

  A. UNION

  B. INTERSECT

  C. EXCEPT

  D. MERGE

  Correct Answer: D

• Question 158:

  Given the following two tables:

  EMPLOYEE

  ID NAME DEPTID

  01 Mick Jagger 10 02 Keith Richards 20 03 Ronnie Wood 20 04 Charlie Watts 20 05 Bill Wyman 30 06 Brian Jones

  DEPARTMENT

  ID DEPTNAME

  10 Executive Staff 20 Sales 30 Marketing 40 Engineering 50 Human Resources

  Which two of the following queries will display the employee name and department name for all employees that are in Sales?

  A. SELECT e.name,d.deptname FROM employee e, department d WHERE e.deptid = d.id AND d.id = '20'

  B. SELECT e.name,d.deptname FROM employee e FULL OUTER JOIN department d ON e.deptid = d.id WHERE d.id = '20'

  C. SELECT e.name,d.deptname FROM employee e RIGHT OUTER JOIN department d ON e.deptid = d.id WHERE d.id = '20'

  D. SELECT e.name,d.deptname FROM employee e LEFT OUTER JOIN department d ON e.deptid = d.id WHERE d.id = '20'

  E. SELECT e.name,d.deptname FROM employee e INNER JOIN department d ON e.deptid = d.id WHERE d.id = '20'

  Correct Answer: DE

• Question 159:

  Assuming table TAB1 contains 100 rows, which of the following queries will return only half of the rows available?

  A. SELECT * FROM tab1 FIND FIRST 50 ROWS

  B. SELECT * FROM tab1 FETCH FIRST 50 ROWS ONLY

  C. SELECT * FROM tab1 WHILE ROW_NUM < 50

  D. SELECT * FROM tab1 MAXROWS 50

  Correct Answer: B

• Question 160:

  Which of the following queries will correctly return the manager information sorted by the manager's last name, department and project name?

  A. SELECTlastname, dept, projname, manager, startdate FROM (SELECT name, dept, proj AS projname, manager, startdate FROM employee, project WHERE empno = empno) AS empproj ORDER BY name, dept, projname

  B. SELECTlastname, dept, projname, manager, startdate FROM (SELECT name AS lastname, dept, proj AS projname, manager, startdate FROM employee, project WHERE empno = mgrno) AS empproj SORT BY lastname, firstname, dept, projname

  C. SELECTlastname, dept, projname, manager, startdate FROM (SELECT name AS lastname, dept, proj AS projname, manager, startdate FROM employee, project WHERE empno = mgrno) AS empproj ORDER BY lastname ASC SORT BY dept, projname DESC

  D. SELECTlastname, dept, projname, manager, startdate FROM (SELECT name AS lastname, dept, proj AS projname, manager, startdate FROM employee, project WHERE empno = mgrno) AS empproj ORDER BY lastname, dept, projname

  Correct Answer: D