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

The correct order is SELECT, FROM, WHERE, GROUP BY, HAVING, 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

Oracle allows arithmetic between aggregate functions in the SELECT clause, returning the salary range.

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

Only department_id can appear unaggregated in SELECT because it is in the GROUP BY clause.

Question 4

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

Accepted Answer

AVG()

Answer

AVG() is the standard Oracle group function that computes the arithmetic mean of a numeric column.

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

ROLLUP creates subtotal rows for each level of the grouping hierarchy plus a grand total row.

1Z0-071 - Oracle Database SQL Certified Associate Practice Test