Oracle command CUBE

Oracle basic command: group by cube (Field List );
The CUBE command generates different permutation and Combination Based on the field list, and generates statistical summary based on each combination result.
For example: group by cube (city, job, age); shows the result of the permutation and combination:

1) city, job, age
2) city, job
3) city
4) job, age
5) age
6) city, age
7) job

So there are seven types of permutation and combination.

2) generate different statistical summaries based on each sort and combination, as shown below:
1) group by cube (city, job, age );
2) group by cube (city, job );
3) group by cube (city );
4) group by cube (job, age );
5) group by cube (age );
6) group by cube (city, age );
7) group by cube (job );

Example:
1) First, let's look at the tables we will use ):

1. SQL>SELECT*FROMEMP;
3. EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
4. ------------------------------------------------------------
5. 7369 smith clerk 7902 800.00 20
6. 7499 allen salesman 7698 1600.00 30
7. 7521 ward salesman 7698 1250.00 30
8. 7566 jones manager 7839 2975.00 20
9. 7654 martin salesman 7698 1250.00 30
10. 7698 blake manager 7839 2850.00 30
11. 7782 clark manager 7839 2450.00 10
12. 7788 scott analyst 7566 1987/4/19 3000.00 20
13. 7839 king president 1981/11/17 5000.00 10
14. 7844 turner salesman 7698 1500.00 30
15. 7876 adams clerk 7788 1987/5/23 1100.00 20
16. 7900 james clerk 7698 950.00 30
17. 7902 ford analyst 7566 3000.00 20
18. 7934 miller clerk 7782 1982/1/23 1300.00 10

2) calculate the average salary (SAL) of different occupations based on the JOB ):

1. SQL>SELECTJOB,AVG(SAL)FROMEMPGROUP BYJOBORDER BYJOBASC;
3. JOBAVG(SAL)
4. -------------------
5. Analytic 3000
6. CLERK 1037.5
7. Management 2758.33333
8. PRESIDENT 5000
9. SALESMAN 1400

3) calculate the average salary (SAL) based on different departments (DEPTNO) and jobs ):

1. SQL>SELECTJOB, DEPTNO,AVG(SAL)FROMEMPGROUP BYJOB, DEPTNOORDER BYJOB;
3. JOB DEPTNOAVG(SAL)
4. -------------------------
5. ANALYST 20 3000
6. CLERK 10 1300
7. CLERK 20 950
8. CLERK 30 950
9. MANAGER 10 2450
10. MANAGER 20 2975
11. MANAGER 30 2850
12. PRESIDENT 10 5000
13. SALESMAN 30 1400

-- We can see that the average annual salary AVG (SAL) of different departments (DEPTNO: 10, 20, 30) is different.

1 2 3 Next Page