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

Oracle Academy My Learning

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

Test: Section 1 Quiz Group By and Having Clauses
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 1 Quiz
(Answer all questions in this section)

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

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