SQL Aggregate Functions and Grouping 3 — Questions and Answers
Question 1: What does GROUP BY ROLLUP(region, product) add to the result set?
- Nothing extra
- Subtotal and grand total rows (Correct answer)
- Sorted output only
- Distinct rows only
Correct answer: Subtotal and grand total rows
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?
- SELECT dept, COUNT(*) FROM emp WHERE COUNT(*)>5 GROUP BY dept
- SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*)>5 (Correct answer)
- SELECT dept, COUNT(*) FROM emp GROUP BY dept WHERE COUNT(*)>5
- SELECT dept, COUNT(*) FROM emp HAVING COUNT(*)>5
Correct answer: SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*)>5
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?
- 0
- NULL (Correct answer)
- An error
- The row count
Correct answer: NULL
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?
- Any column may be mixed freely
- Non-aggregated columns must appear in GROUP BY (Correct answer)
- Aggregates cannot be used with GROUP BY
- Only one aggregate is allowed
Correct answer: Non-aggregated columns must appear in GROUP BY
Every non-aggregated SELECT column must be in the GROUP BY clause.
Question 5: What does COUNT return when applied to an empty table?
- NULL
- 0 (Correct answer)
- An error
- 1
Correct answer: 0
COUNT returns 0 on an empty set, unlike SUM or AVG which return NULL.
Question 6: Which aggregate would you use to find the total revenue across all orders?
- COUNT(amount)
- SUM(amount) (Correct answer)
- AVG(amount)
- MAX(amount)
Correct answer: SUM(amount)
SUM adds all values together to give the total revenue.
Question 7: Can you nest aggregate functions like SUM(MAX(x)) directly in a single GROUP BY query?
- Yes, always
- No, it is generally not allowed (Correct answer)
- Only with COUNT
- Only in subqueries with WHERE
Correct answer: No, it is generally not allowed
Nesting aggregates directly is not permitted; you must use a subquery instead.
SQL Aggregate Functions and Grouping 3
What does GROUP BY ROLLUP(region, product) add to the result set?