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.

-- Hands-On 06 (Data Warehousing - rollup and cube function) 

CLEAR SCR 
SET ECHO ON 
-- Connect to SQL*PLUS as the iself user.
--
pause

CONNECT iself/schooling
pause

CLEAR SCR
-- Set the pagesize to 55 and the linesize to 100.
--
pause

SET PAGESIZE 55
SET LINESIZE 100
pause

CLEAR SCR
-- Alter the EMP table, and add a column
-- to store the year that employees were
-- hired.
-- 
pause

ALTER TABLE emp ADD (year VARCHAR2(2))
/
pause

CLEAR SCR
-- Set the column year heading.
--
pause

COLUMN year FORMAT a4 HEADING "Year"
pause

CLEAR SCR
-- Update the EMP table to set the year each employee
-- was hired, into the newly created YEAR column.
--
pause

UPDATE emp 
SET year = TO_CHAR(hiredate,'YY') 
/ 
commit;
pause

CLEAR SCR
-- Query an aggregation sum report for each job, 
-- in each year, using the ROLLUP grouping option.
pause

SELECT year, job, SUM(sal), COUNT(*) 
FROM emp 
GROUP BY ROLLUP (year, job)
/
-- This is an example of a GROUP BY ROLLUP option.
pause 

CLEAR SCR
-- Query an aggregation sum report for 
-- each job, in each year using the CUBE option group
-- for the research department only.
pause

SELECT year, job, SUM(sal), COUNT(*) 
FROM emp 
WHERE deptno = 20
GROUP BY CUBE (year, job)
/
-- This is an example of a GROUP BY CUBE option.
pause

CLEAR SCR
-- Drop the year column

pause

ALTER TABLE emp DROP (year)
/
-- You have dropped the year column so that you can
-- perform this excercise over and over again.
pause

CLEAR SCR
-- Now, practice this Hands-On over and over
-- until you become a master at it.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck!
pause