DDSQL-DP9-P10

MINUS will give you rows from the first query that are not present in the second query. (True or False?)

• True

• False

INTERSECT will give you the common rows found in both queries. (True or False?)

• True

• False

When using SET operators, the names of the matching columns must be identical in all of the SELECT statements used in the query. True or False?

• True

• False

You use GROUPING functions to ______ database rows from tabulated rows.

• COUNT

• COMPUTE

• DISTINGUISH

• CREATE

Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary) FROM employees GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))

What data will this query generate?

• Sum of salaries for (department_id, job_id) and (department_id, manager_id)

• Sum of salaries for (department_id, job_id, manager_id)

• Subtotals for (job_id, manager_id)

• The statement will fail.

CUBE will cross-reference the columns listed in the ______ clause to create a superset of groups.

• WHERE

• SELECT

• GROUP BY

Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary) FROM employees GROUP BY GROUPING SETS(.......);

Select the correct GROUP BY GROUPING SETS clause from the following list:

• GROUP BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id), (department_id, manager_id)

• GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id))

• GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary), (manager_id, job_id))

• GROUP BY GROUPING SETS (department_id, salary), (department_id, job_id), (department_id, manager_id)

You use ROLLUP to:

• produce a single result set

• produce subtotal values

• cross-tabulate values

If you want to include subtotals and grand totals for all columns mentioned in a GROUP BY clause, you should use which of the following extensions to the GROUP BY clause?

• GROUP BY ALL COLUMNS

• CUBE

• ROLLUP

• HAVING

Evaluate this statement:

SELECT department_id, AVG(salary) FROM employees WHERE job_id <> 69879 GROUP BY job_id, department_id HAVING AVG(salary) > 35000 ORDER BY department_id;

Which clauses restricts the result? Choose two.

(Choose all correct answers)

• WHERE job_id <> 69879

• GROUP BY job_id, department_id

• HAVING AVG(salary) > 35000

• SELECT department_id, AVG(salary)