iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |

 

Lesson 15

"Some cause happiness wherever they go; others, whenever they go." - Oscar Wilde (1854-1900)

 

Read first then play the video:

   SQL-VIDEO -Data Warehousing and use of ROLLUP and CUBE functions
 
 

The ROLLUP and CUBE function

 

Hands-On

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

 

The CUBE layout output:

Year JOB SUM(SAL) COUNT(*)

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

80               CLERK 1050 1

80               1050 1

 

81               ANALYST 3150 1

81               MANAGER 3123.75 1

81               6273.75 2

 

87               ANALYST 2000 1

87               CLERK 1337.06 1

87               3337.06 2

 

ANALYST 5150 2

CLERK 2387.06 2

MANAGER 3123.75 1

 

                 10660.81 5

 

Introduction

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 iself user.
SQL> CONNECT iself/schooling


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;


ROLLUP grouping option

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.


CUBE grouping 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 TABLE … DROP column

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.

 

"I am ready to meet my Maker. Whether my Maker is prepared for the great ordeal of meeting me is another matter." - Sir Winston Churchill (1874-1965)

 

Questions:

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.