1Z0-071 Aggregating Data with Group Functions 3

Question 1

Which is the correct order of clauses in a SELECT statement using GROUP BY and HAVING?

Accepted Answer

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

Answer

SELECT, FROM, HAVING, WHERE, GROUP BY, ORDER BY

Answer

SELECT, FROM, GROUP BY, WHERE, HAVING, ORDER BY

Answer

SELECT, FROM, WHERE, HAVING, GROUP BY, ORDER BY

Question 2

What is the output of SELECT MAX(salary) - MIN(salary) FROM employees?

Accepted Answer

The salary range across all employees

Answer

An error because you cannot subtract aggregate functions

Answer

The difference between the first and last employee salary

Answer

NULL if any salary is NULL

Question 3

A query uses GROUP BY department_id. Which column can appear in the SELECT list WITHOUT being inside an aggregate function?

Accepted Answer

department_id

Answer

employee_id

Answer

salary

Answer

hire_date

Question 4

Which aggregate function is used to find the average salary per department?

Accepted Answer

AVG()

Answer

MEDIAN()

Answer

MEAN()

Answer

SUM()/COUNT(*)

Question 5

What does GROUP BY ROLLUP(department_id, job_id) produce in addition to normal groups?

Accepted Answer

Subtotals for each department_id and a grand total

Answer

Only grand total row

Answer

Only subtotals for each job_id

Answer

Nothing different from regular GROUP BY

Question 6

Which statement correctly nests group functions?

Accepted Answer

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

Answer

SELECT SUM(AVG(salary)) FROM employees

Answer

SELECT AVG(MAX(salary)) FROM employees

Answer

SELECT COUNT(SUM(salary)) FROM employees