View the Exhibit (no exhibit was provided) and examine a sample of the data existing in the STORES table.
You need to generate a report that shows the following details:
1) The total QTY_SOLD of each product in each region of each country.
2) The total QTY_SOLD of all products in each region of each country.
3) The total QTY_SOLD of all products in each country.
Which SQL statement gives the required output?
A. SELECT country_id, region, prod_no, SUM(qty_sold)
FROM stores
GROUP BY CUBE(country_id,region,prod_no);
B. SELECT country_id, region, prod_no, SUM(qty_sold)
FROM stores
GROUP BY ROLLUP(country_id,region,prod_no);
C. SELECT country, region, prod_no, SUM(qty_sold)
FROM stores
GROUP BY GROUPING SETS(country_id,region),prod_no);
D. SELECT country, region, prod_no, SUM(qty_sold), GROUPING(country_id) G1, GROUPING(region) G2, GROUPING(prod_no)
FROM stores
GROUP BY CUBE (country_id,region,prod_no);
Theory:
Since the question contains SELECT queries with GROUP BY clause and functions GROUPING, GROUPING SETS, CUBE and ROLLUP, so the information required to answer the question can be found as follows: SELECT, GROUP BY, CUBE, ROLLUP and GROUPING SETS and GROUPING.
You specify the
GROUP BY clause if you want the database to group the selected rows based on the value of expr(s) for each row and return a single row of summary information for each group. If this clause contains
CUBE or
ROLLUP extensions, then the database produces superaggregate groupings in addition to the regular groupings. Expressions in the GROUP BY clause can contain any columns of the tables, views, or materialized views in the FROM clause, regardless of whether the columns appear in the select list. The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause. The
ROLLUP operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the GROUP BY specification, and returns a single row of summary for each group. You can use the
ROLLUP operation to produce subtotal values by using it with the SUM function. When used with SUM,
ROLLUP generates subtotals from the most detailed level to the grand total. Aggregate functions such as COUNT can be used to produce other kinds of superaggregates. For example, given three expressions (n=3) in the
ROLLUP clause of the simple_grouping_clause, the operation results in n+1 = 3+1 = 4 groupings. Rows grouped on the values of the first n expressions are called regular rows, and the others are called superaggregate rows. The
CUBE operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the specification. It returns a single row of summary information for each group. You can use the
CUBE operation to produce cross-tabulation values. For example, given three expressions (n=3) in the
CUBE clause of the simple_grouping_clause, the operation results in 2n = 23 = 8 groupings. Rows grouped on the values of n expressions are called regular rows, and the rest are called superaggregate rows.
GROUPING SETS are a further extension of the GROUP BY clause that let you specify multiple groupings of data. Doing so facilitates efficient aggregation by pruning the aggregates you do not need. You specify just the desired groups, and the database does not need to perform the full set of aggregations generated by
CUBE or
ROLLUP. Oracle Database computes all groupings specified in the
GROUPING SETS clause and combines the results of individual groupings with a UNION ALL operation. The UNION ALL means that the result set can include duplicate rows. A blank set ( ) in the
GROUPING SETS clause calculates the overall aggregate.
GROUPING distinguishes superaggregate rows from regular grouped rows. GROUP BY extensions such as
ROLLUP and
CUBE produce superaggregate rows where the set of all values is represented by null. Using the
GROUPING function, you can distinguish a null representing the set of all values in a superaggregate row from a null in a regular row. The expr in the
GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 1 if the value of expr in the row is a null representing the set of all values. Otherwise, it returns zero. The datatype of the value returned by the
GROUPING function is Oracle NUMBER. It might be also worth mentioning here that
GROUP BY CUBE( a, b, c) is equivalent to
GROUP BY GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )), whereas
ROLLUP (a, b, c) is equivalent to
GROUPING SETS ( (a, b, c), (a, b), (a), ( )).
Answer:
A - Wrong - this query would display more than is needed – all combinations of region, country, product, so it would output for example sum in each region or all products without country, which is not requested in the question
B - Correct - this gives 4 aggregation levels with sums as needed as ROLLUP takes as its argument an ordered list of grouping column, and first it calculates the standard aggregate values specified in the GROUP BY clause, and next it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns, and finally it creates a grand total.
C - Wrong - the query doesn't display data from requirement number 1 and 3 as it displays total of all products in each region of each country and sum per each product, but it does not display sum of each product in each region of each country and sum of all products in each country
D - Wrong - this query would work like the query from answer A but with extra information regarding if a row that is being displayed is a data row or an aggregate, which is not required, however with DECODE function that could be used to filter unnecessary rows from the output but this query doesn't do it
