SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager')
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DATE END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- ----------- ----------- ---------- ---------- ---------------
01 Jason Martin 1996-JUL-25 2006-JUL-25 1234.56 Toronto Programmer
02 Alison Mathews 1976-MAR-21 1986-FEB-21 6661.78 Vancouver Tester
03 James Smith 1978-DEC-12 1990-MAR-15 6544.78 Vancouver Tester
04 Celia Rice 1982-OCT-24 1999-APR-21 2344.78 Vancouver Manager
05 Robert Black 1984-JAN-15 1998-AUG-08 2334.78 Vancouver Tester
06 Linda Green 1987-JUL-30 1996-JAN-04 4322.78 New York Tester
07 David Larry 1990-DEC-31 1998-FEB-12 7897.78 New York Manager
08 James Cat 1996-SEP-17 2002-APR-15 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> --CUBE clause: return rows containing a subtotal for all combinations of columns included in the CUBE clause along with a total.
SQL>
SQL> SELECT city, description, SUM(salary)
2 FROM employee
3 GROUP BY CUBE(city, description);
CITY DESCRIPTION SUM(SALARY)
---------- --------------- -----------
32574.02
Tester 21096.9
Manager 10242.56
Programmer 1234.56
Toronto 1234.56
Toronto Programmer 1234.56
New York 12220.56
New York Tester 4322.78
New York Manager 7897.78
Vancouver 19118.9
Vancouver Tester 16774.12
Vancouver Manager 2344.78
12 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>