GROUPING SETS — Concept Check
Questions on GROUPING SETS. GROUPING SETS lets you define multiple grouping combinations in a single query, avoiding verbose UNION ALL rewrites.
Questions in this quiz
7 questions on GROUPING SETS — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A database query needs to aggregate total sales by city, by product category, and by both city and category. Which SQL feature allows this in a single query without multiple scans?
- AROLLUP
- BUNION ALL
- CGROUPING SETS
- DCUBE
2.When using GROUPING SETS, what does the GROUPING() function return when a column is part of the current grouping and its value is NULL?
- A1
- BThe actual value
- CNULL
- D0
3.A report requires subtotals by city and category, but not a grand total. Which SQL clause achieves this?
- AGROUP BY GROUPING SETS ((city, category), (city), (category))
- BGROUP BY ROLLUP(city, category)
- CGROUP BY CUBE(city, category)
- DGROUP BY GROUPING SETS ((city, category), ())
4.In a query using GROUPING SETS, you see a NULL value in the 'city' column. What could this indicate?
- AThe 'city' column has NULL values in the data.
- BThe database does not support GROUPING SETS.
- CThe 'city' column is not part of the current grouping.
- DThe query is incorrectly written.
5.A developer uses UNION ALL for multiple GROUP BY queries. What is a potential downside compared to GROUPING SETS?
- AInability to handle NULLs
- BIncreased query complexity
- CMultiple scans of the data
- DLack of support in SQL:1999
6.How does ROLLUP(city, category) differ from GROUPING SETS((city, category), (city), ())?
- AROLLUP requires more complex syntax.
- BROLLUP includes a grand total row.
- CROLLUP is not supported in SQL:1999.
- DROLLUP does not allow custom subsets.
7.What is the primary advantage of using GROUPING SETS in SQL queries?
- AIncreases database compatibility.
- BReduces data scans by combining multiple GROUP BY operations.
- CSimplifies syntax by using fewer keywords.
- DEliminates the need for JOIN operations.