SQL Aggregate Functions and Grouping 3

Question 1

What does GROUP BY ROLLUP(region, product) add to the result set?

Accepted Answer

Subtotal and grand total rows

Answer

ROLLUP generates subtotals for each level plus a grand total row.

Question 2

Which query correctly counts employees per department having more than 5 members?

Accepted Answer

SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*)>5

Answer

Aggregate filtering on groups requires HAVING after GROUP BY.

Question 3

What value does SUM return for a group where every row's column is NULL?

Accepted Answer

NULL

Answer

SUM over all-NULL values returns NULL, not 0.

Question 4

Which statement about combining aggregate and non-aggregate columns is true in standard SQL?

Accepted Answer

Non-aggregated columns must appear in GROUP BY

Answer

Every non-aggregated SELECT column must be in the GROUP BY clause.

Question 5

What does COUNT return when applied to an empty table?

Accepted Answer

0

Answer

COUNT returns 0 on an empty set, unlike SUM or AVG which return NULL.

SQL Practice Test