SQL GROUPING SETS, ROLLUP, and CUBE Essentials Quiz

Explore fundamental concepts and differences of GROUPING SETS, ROLLUP, and CUBE in SQL for advanced multi-level aggregations, using practical reporting scenarios and clear examples for effective query design.

1. GROUPING SETS Functionality

   Which SQL clause allows you to perform multiple groupings in a single query, such as totaling sales per city and per product at once?

   1. GROUPING SETS
   2. ORDER GROUPS
   3. GROUP FILTER
   4. GROUP NODES

   Explanation: GROUPING SETS enables you to specify multiple groupings within the same query efficiently. ORDER GROUPS, GROUP FILTER, and GROUP NODES are not valid SQL clauses, making them incorrect options. Only GROUPING SETS provides this enhanced grouping functionality.

2. ROLLUP Purpose

   What is the main purpose of the ROLLUP extension in SQL aggregation queries?

   1. To produce subtotals and a grand total in a hierarchical manner
   2. To sort data in ascending order
   3. To eliminate duplicate records
   4. To filter rows based on a condition

   Explanation: ROLLUP is designed to generate subtotals at each level of a hierarchy, culminating in a grand total, which is especially helpful for multi-level reports. Sorting data, removing duplicates, and filtering rows are not the functions of the ROLLUP extension; those refer to ORDER BY, DISTINCT, and WHERE clauses.

3. CUBE Output

   When using the CUBE keyword in a GROUP BY clause, what kind of groupings do you get?

   1. All combinations of specified columns
   2. Only totals for single columns
   3. No aggregation at all
   4. A filtered subset of the data

   Explanation: CUBE computes all possible combinations of the specified columns, including all subtotals and the grand total. Only totals for single columns would be a partial grouping, not a complete cube. 'No aggregation at all' and 'a filtered subset' don't relate to CUBE's functionality.

4. Typical GROUP BY Limitation

   What is a common limitation of using the standard GROUP BY clause when generating multiple summarized reports in SQL?

   1. You must write separate queries for each grouping requirement
   2. It prevents joining multiple tables
   3. It does not allow for sorting results
   4. It only works with numeric columns

   Explanation: Using GROUP BY alone only supports a single grouping per query, requiring multiple queries for different aggregations. JOINs and sorting are still allowed with GROUP BY, and it can work with non-numeric columns for grouping purposes.

5. GROUPING SETS Syntax Example

   Which of the following shows a correct use of GROUPING SETS to total sales by city or product?

   1. GROUP BY GROUPING SETS ((city), (product))
   2. GROUP SETS BY (city, product)
   3. GROUP BY (city OR product)
   4. GROUPING BY SET (city, product)

   Explanation: The proper syntax for GROUPING SETS specifies the groupings inside double parentheses following GROUP BY. The other options misplace keywords or do not use the syntax that SQL requires.

6. ROLLUP Example Scenario

   In a sales dataset with columns city, product, and quarter, what result does ROLLUP(city, product) provide?

   1. It creates totals for each city and city-product pair, plus a grand total
   2. It only totals by product
   3. It removes all duplicate product entries
   4. It ignores the city column in the results

   Explanation: ROLLUP(city, product) generates groupings for each city-product pair, then for each city, and finally a grand total. The second option only totals by product, not by city. The other two do not describe aggregation or summarize results as ROLLUP does.

7. When to Use CUBE

   Suppose you want to report total sales by product, by city, by quarter, and by every combination of these. Which SQL feature best achieves this?

   1. CUBE
   2. ORDER BY
   3. HAVING
   4. DISTINCT

   Explanation: CUBE automatically computes all possible combinations of the specified grouping columns, ideal for multidimensional analysis. ORDER BY arranges the output, HAVING filters groups, and DISTINCT removes duplicates, none of which provide multi-level totals like CUBE.

8. Grand Total Output

   Which grouping extension(s) automatically produce a grand total row in the query result?

   1. Both ROLLUP and CUBE
   2. Only CUBE
   3. Neither GROUPING SETS nor ROLLUP
   4. Only GROUP BY

   Explanation: Both ROLLUP and CUBE, by design, create a final aggregate row representing the grand total. While GROUPING SETS can also produce a grand total if specified, it's not automatic. Standard GROUP BY does not create a grand total unless explicitly coded.

9. Hierarchical Report Example

   A hierarchical sales report requires subtotals by city and an overall total for all cities. Which SQL feature should you use?

   1. ROLLUP
   2. JOIN
   3. UNION
   4. FILTER

   Explanation: ROLLUP automatically summarizes data in a hierarchical fashion, ideal for subtotals by city and grand totals. JOIN combines tables, UNION merges result sets, and FILTER is not a standard SQL clause.

10. GROUPING SETS vs ROLLUP

    What is one advantage of GROUPING SETS over ROLLUP?

    1. It allows non-hierarchical and custom combinations of groupings
    2. It always produces a grand total row
    3. It disables sorting in the output
    4. It is faster for single groupings

    Explanation: GROUPING SETS gives you explicit control, letting you define any combination of groupings, not just hierarchical totals. The grand total row is not automatic; you must specify it. The other statements are either incorrect or unrelated to its core advantage.

11. Basic GROUP BY Operation

    Which aggregation clause is used in SQL to summarize data for one or more specified columns?

    1. GROUP BY
    2. SELECT DISTINCT
    3. MERGE
    4. LIMIT

    Explanation: GROUP BY groups the rows based on one or more columns for summarization. SELECT DISTINCT filters duplicates, MERGE is not a standard aggregation clause, and LIMIT restricts row counts.

12. Syntax Error Identification

    Which of the following is NOT valid SQL syntax for GROUPING SETS, ROLLUP, or CUBE?

    1. GROUP BY ROLLUP (city, product)
    2. GROUP BY CUBE (product, quarter)
    3. GROUP BY GROUPING SETS ((city), (product), ())
    4. GROUP BY GROUP SET (city, product)

    Explanation: GROUP BY GROUP SET (city, product) is not valid SQL; the correct keyword is GROUPING SETS. The other three options correctly use ROLLUP, CUBE, and GROUPING SETS syntax.

13. Use Case Understanding

    You are asked to generate totals per city, per product, both city and product, and an overall total. Which SQL clause can do all of these in a single query?

    1. GROUPING SETS
    2. SELECT UNIQUE
    3. WHERE ALL
    4. UNION

    Explanation: GROUPING SETS lets you specify all required groupings (city, product, both, grand total) in one query. SELECT UNIQUE is not SQL standard, WHERE ALL is not valid here, and UNION combines different result sets, not groupings.

14. Handling NULL in Aggregation

    In results using ROLLUP or CUBE, what does a NULL value in a grouping column typically indicate?

    1. That row represents an aggregate total at a higher level
    2. A data entry error
    3. A missing join condition
    4. A deleted record

    Explanation: A NULL in a grouping column created by ROLLUP or CUBE usually signifies that the row is a subtotal or grand total. It is not necessarily a data error, join issue, or deleted record. The NULLs are used intentionally for aggregated rows.

15. Limiting GROUPINGS

    How do GROUPING SETS, ROLLUP, and CUBE relate to query performance compared to traditional GROUP BY for many aggregations?

    1. They are generally faster because they consolidate multiple queries into one
    2. They always slow down queries due to complexity
    3. They cannot be used with joins
    4. They only work with date columns

    Explanation: GROUPING SETS, ROLLUP, and CUBE can improve performance by eliminating the need for separate queries, thus reducing overhead. While complex groupings can impact query time, the ability to combine aggregations in one query is typically more efficient. They work with more than just date columns and support joins.

16. Multiple Levels of Aggregation

    Which result do you expect when applying GROUPING SETS ((city), (product)) to a sales table?

    1. Separate total sales per city and per product
    2. A single grand total only
    3. Summarized data by city-product pairs
    4. No aggregation is produced

    Explanation: GROUPING SETS ((city), (product)) produces two levels of aggregation: one for cities and one for products, but not their combinations. Summarizing by city-product pairs would require a different grouping set. The single grand total or no aggregation does not match this query.