SQL

Data warehousing is a focus in your organization. Management needs more reports in order to truly understand their data. You have been assigned to write simple SQL statement using the �ROLLUP� operator to create salary subtotals for each type of job per year. Also, you should use the CUBE operator to enable a simple statement to calculate all possible combinations of the types of jobs and year for the research department only.

Notice: A select statement using the ROLLUP operator returns both �regular rows� and �super-aggregate rows.� Super-aggregate rows are rows that contain a sub-total value.

A select statement using the CUBE operator returns cross-tabulation values, thus produces totals in all possible dimensions, and is used for warehousing aggregated data reports.

The report layout printout:

The ROLLUP layout output:

Year JOB SUM(SAL) COUNT(*)

---- --------- ---------- ----------

80 CLERK 1050 1

80 1050 1

81 ANALYST 3150 1

81 CLERK 997.5 1

81 MANAGER 9160.45 3

81 PRESIDENT 5250 1

81 SALESMAN 11609.82 4

81 30167.77 10

82 CLERK 1365 1

82 1365 1

87 ANALYST 2000 1

87 CLERK 1337.06 1

87 3337.06 2

35919.83 14

Oracle added the ROLLUP and CUBE functions in Oracle9i to the GROUP BY clause of the SELECT statement. The ROLLUP function creates subtotals at any level of aggregation from the most detailed up to a grand total. The CUBE function is similar to the ROLLUP function, but it calculates all possible combinations of subtotals. Look at the following example to have a better understanding of these two powerful functions. We use these two functions a lot when we have a warehousing application or needs.

Connect to SQL*PLUS as the oracle user.
SQL> CONNECT oracle/learning
 

Set the pagesize to 55 and the linesize to 100.
SQL> SET PAGESIZE 55
SQL> SET LINESIZE 100
 

Alter the EMP table, and add a column to store the year that employees were hired.
SQL> ALTER TABLE emp ADD (year VARCHAR2(2))
SQL> /
 

Set the column year heading.
SQL> COLUMN year FORMAT a4 HEADING "Year"
 

Update the EMP table to set the year each employee was hired, into the newly created YEAR column.
SQL> UPDATE emp
SET year = TO_CHAR(hiredate,'YY')
SQL> /
SQL> commit;
 

Query an aggregation sum report for each job, in each year, using the ROLLUP grouping option.
SQL> SELECT year, job, SUM(sal), COUNT(*)
FROM emp
GROUP BY ROLLUP (year, job)
SQL> /
This is an example of a GROUP BY ROLLUP option.
 

Query an aggregation sum report for each job, in each year using the CUBE option group for the research department only.
SQL> SELECT year, job, SUM(sal), COUNT(*)
FROM emp
WHERE deptno = 20
GROUP BY CUBE (year, job)
SQL> /
This is an example of a GROUP BY CUBE option.
 

Drop the year column
SQL> ALTER TABLE emp DROP (year)
SQL> /
You have dropped the year column so that you can perform this exercise over and over again.

Q: What does the ROLLUP operator?

Q: What does the CUBE operator?

Q: What are the differences between the CUBE and ROLLUP functions?

Q: What environments may use the CUBE and ROLLUP functions most?

Q: Write a query to list an aggregation sum report for each job, in each year, using the ROLLUP grouping option.

Q: Write a query to list an aggregation sum report for each job, in each year, using the CUBE grouping option.