Database programming section 9

1. The difference between UNION and UNION ALL is
UNION will remove duplicates; UNION ALL returns all rows from all queries including the duplicates. (*)

2. To control the order of rows returned using SET operators, the ORDER BY clause is used ______ and is placed in the _____ SELECT statement of the query.
ONCE; LAST (*)

3. When using SET operators, the number of columns and the data types of the columns must be identical in all of the SELECT statements used in the query. True or False.
True(*)

4. Evaluate this SELECT statement:
SELECT COUNT(emp_id), mgr_id, dept_id
FROM employees
WHERE status = 'I'
GROUP BY dept_id
HAVING salary > 30000
ORDER BY 2;
Why does this statement return a syntax error?
MGR_ID must be included in the GROUP BY clause. (*)

5. Evaluate this SELECT statement:
SELECT MIN(hire_date), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?

The earliest hire date in each department (*)

6. Which statement about the GROUP BY clause is true?
To exclude rows before dividing them into groups using the GROUP BY clause, you should use a WHERE clause. (*)

7. Evaluate this SELECT statement:
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
The highest salary in each department (*)

8. Group functions can be nested to a depth of?
Two(*)

9. Evaluate this SELECT statement:
SELECT SUM(salary), department_id, department_name
FROM employees
WHERE department_id = 1
GROUP BY department;
Which clause of the SELECT statement contains a syntax error?
GROUP BY (*)

10. You use GROUPING functions to ______ database rows from tabulated rows.
DISTINGUISH(*)

11. 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, manager_id), (department_id, job_id), (manager_id, job_id)) (*)

12. GROUPING SETS is another extension to the GROUP BY clause and is used to specify multiple groupings of data but provide a single result set. True or False?
Trus (*)

13. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, manager_id)
What extra data will this query generate?
The statement will fail. (*)

14. You use GROUPING functions to:
Identify the extra row values created by either a ROLLUP or CUBE operation (*)

15. 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?
CUBE (*)