Using the Extended GROUP BY Clauses

In this section, you ll learn about:

ROLLUP , which extends the GROUP BY clause to return rows containing a subtotal for each group.
CUBE , which extends the GROUP BY clause to return rows containing a subtotal for all combinations of columns along with a total at the end.

First, let s look at the example tables you ll see in this section.

The Example Tables

You ll see the use of three new tables that refine the representation of employees in the company that runs the store.

divisions , which stores the divisions within the company
jobs , which stores the jobs within the company
employees2 , which stores the employees

These tables are created by the store_schema.sql script. You ll learn the details of the divisions , jobs , and employees2 tables in the following sections.

The divisions Table

The divisions table is created using the following statement:

 CREATE TABLE divisions (division_id CHAR(3)  CONSTRAINT divisions_pk PRIMARY KEY,  name VARCHAR2(15) NOT NULL);

The divisions table contains the following rows:

 DIV NAME --- ---------- SAL Sales OPE Operations SUP Support BUS Business

The jobs Table

The jobs table is created using the following statement:

 CREATE TABLE jobs (job_id CHAR(3)  CONSTRAINT jobs_pk PRIMARY KEY,  name VARCHAR2(20) NOT NULL);

The jobs table contains the following rows:

 JOB NAME --- ------------ WOR Worker MGR Manager ENG Engineer TEC Technologist PRE President

The employees2 Table

The employees2 table is created using the following statement:

 CREATE TABLE employees2 (employee_id INTEGER  CONSTRAINT employees2_pk PRIMARY KEY,  division_id CHAR(3)  CONSTRAINT employees2_fk_divisions  REFERENCES divisions(division_id),  job_id CHAR(3) REFERENCES jobs(job_id),  first_name VARCHAR2(10) NOT NULL,  last_name VARCHAR2(10) NOT NULL,  salary NUMBER(6, 0));

The following output shows the first five rows in the employees2 table:

 EMPLOYEE_ID DIV JOB FIRST_NAME LAST_NAME SALARY ----------- --- --- ---------- ---------- ----------  1 BUS PRE James Smith 800000  2 SAL MGR Ron Johnson 350000  3 SAL WOR Fred Hobbs 140000  4 SUP MGR Susan Jones 200000  5 SAL WOR Rob Green 350000

Note	The employees2 table actually contains a lot more rows than this, but for brevity I ve omitted listing them all here.