# 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.

FROM STUDENTS

GROUP_BY ?????;

Mark for Review

(1) Points

last_name

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

*OBS: New Question :

12. 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
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.

Solution for Test: Quiz: Introduction to The Oracle Academy
Solution for Test: Quiz: Data vs Information
Top