|
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 oracle user.
--
pause
CONNECT oracle/learning
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 |
