|
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 |
