GROUP BY & Aggregation — Concept Check
Questions on GROUP BY & Aggregation. GROUP BY collapses rows by distinct values; aggregate functions (COUNT, SUM, AVG, MIN, MAX) operate per group; ROLLUP and CUBE extend GROUP BY for hierarchical totals.
Questions in this quiz
8 questions on GROUP BY & Aggregation — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A query uses GROUP BY to summarize orders by status. It returns an error because a column in SELECT is not in GROUP BY or an aggregate function. What is the likely cause?
- AThe query uses HAVING incorrectly.
- BThe query is running in ONLY_FULL_GROUP_BY mode.
- CThe query lacks an ORDER BY clause.
- DThe query is missing a WHERE clause.
2.A report shows revenue by region and category using GROUP BY WITH ROLLUP. Why might some rows have NULL for both region and category?
- AThey represent the grand total across all groups.
- BThey show incomplete groupings due to a query error.
- CThey indicate missing data in the database.
- DThey are filtered out by a WHERE clause.
3.A developer wants to filter orders with revenue greater than 1000 after grouping by customer_id. Which clause should they use?
- AGROUP BY
- BWHERE
- CHAVING
- DORDER BY
4.A query groups orders by month and category but needs to include the customer's email in the result. What is a correct way to fix a SELECT error?
- AAdd email to the HAVING clause.
- BRemove email from the SELECT clause.
- CInclude email in GROUP BY or use ANY_VALUE(email).
- DUse email in the ORDER BY clause.
5.A report uses COUNT(DISTINCT customer_id) to find unique customers per day. What does this aggregation do?
- ACounts total orders for each day.
- BCounts all customer IDs, including duplicates.
- CCounts unique customer IDs for each day.
- DCounts unique orders for each customer.
6.A developer uses GROUP BY to group orders by status. They mistakenly use WHERE instead of HAVING to filter groups. What is the likely outcome?
- AThe query filters rows before grouping, not groups.
- BThe query will have no effect on the output.
- CThe query will return an error.
- DThe query will filter groups correctly.
7.A query groups orders by region and category with ROLLUP. How can you distinguish ROLLUP-generated NULLs from actual NULL values?
- ACheck the ORDER BY clause.
- BUse COALESCE to replace NULLs.
- CCheck for NULL in the WHERE clause.
- DUse the GROUPING() function.
8.A query uses GROUP_CONCAT(id ORDER BY id LIMIT 5) in a GROUP BY clause. What is the purpose of this function?
- ACounts the number of IDs per group.
- BLimits the number of groups to 5.
- COrders groups by ID.
- DConcatenates up to 5 IDs per group.