Test: Quiz: Group By and Having Clauses, ROLLUP and CUBE Operations, and Grouping Sets

Section 1

1. Which of the following SQL statements could display the number of people with the same last name: Mark for Review
(1) Points


SELECT first_name, last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;



SELECT employee_id, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;



SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;

(*)


SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name;







2. The use of GROUP BY GROUPING SETS(....) can speed up the execution of complex report statements? (True or False) Mark for Review
(1) Points


True (*)


False






3. The following is a valid statement:

SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;

True or False?
Mark for Review
(1) Points


True (*)


False






4. Read the following SELECT statement. Choose the column or columns that must be included in the GROUP BY clause.

SELECT COUNT(last_name), grade, gender
FROM STUDENTS
GROUP_BY ?????;
Mark for Review
(1) Points


last_name


last_name, grade


grade, gender (*)


last_name, gender






5. 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?

Mark for Review
(1) Points


Total salaries for (department_id, job_id) and (department_id, manager_id) (*)


Total salaries for (department_id, job_id, manager_id)


Total for (job_id, manager_id)


The statement will fail.






6. If you want to include subtotals and grant totals for all columns mentioned in a GROUP BY clause you should use which of the following extensions to the GROUP BY clause? Mark for Review
(1) Points


ROLLUP


CUBE (*)


GROUP BY ALL COLUMNS


HAVING






7. Is the following statement correct?

SELECT department_id, AVG(salary)
FROM employees;

Mark for Review
(1) Points


No, because a GROUP BY department_id clause is needed (*)


No, because the SELECT clause cannot contain both individual columns and group functions


No, because the AVG function cannot be used on the salary column


Yes






8. 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:

Mark for Review
(1) Points


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


GROUP BY GROUPING SETS (department_id, 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))






9. How would you alter the following query to list only employees where more than one employee exists with the same last_name:

SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;

Mark for Review
(1) Points


SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
WHERE COUNT(*) > 1
GROUP BY last_name



SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING COUNT(last_name) > 1;

(*)


SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
EXISTS COUNT(last_name) > 1;



SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING last_name > 1;







10. Is the following statement correct:

SELECT first_name, last_name, salary, department_id, COUNT(employee_id)
FROM employees
WHERE department_id = 50
GROUP BY last_name, first_name, department_id;

Mark for Review
(1) Points


Yes


No, beause you cannot have a WHERE-clause when you use group functions.


No, because the statement is missing salary in the GROUP BY clause (*)


Yes, because Oracle will correct any mistakes in the statement itself






11. 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?

Mark for Review
(1) Points


Subtotals for department_id, and grand totals for salary.


Subtotals for department_id, job_id and grand totals for salary.


Subtotals for department_id, job_id, manager_id and grand totals for salary.


The statement will fail. (*)






( Vrei sa traduci ceva?Want to translate something? http://translate.google.ro/# )