Explain the difference between the output produced by SQL ROLLUP and CUBE queries.
What will be an ideal response?
The ROLLUP and CUBE
extensions to GROUP BY generate OLAP-type summaries of the data with subtotals and totals. The
columns to be defined are defined similarly to how grouping sets can define GROUP BY columns.
ROLLUP generates subtotal and total rows for the GROUP BY columns. CUBE extends the
capabilities by generated subtotal rows for every combination of GROUP BY columns. ROLLUP and
CUBE also generate a grand total row.
A simple example of a ROLLUP query is shown below:
SELECT a, b, c, sum(x)
FROM t
GROUP BY ROLLUP (a,b,c)
Produces aggregates of x for (a,b,c), (a,b), a, (grand total)
A simple example of a CUBE query is shown below:
SELECT a, b, c, sum(x)
FROM t
GROUP BY CUBE (a,b,c)
Produces aggregates of x for (a,b,c), (a,b), (a,c), a, (b,c), b, c, (grand total). The rows that are produced
by ROLLUP and CUBE are shown in bold.
You might also like to view...
A(n) ________ is a place on a computer where you attach a device to exchange data with the computer
Fill in the blank(s) with correct word
When writing a business letter, use the colon character to follow the complimentary closing
Indicate whether the statement is true or false