Oracle Database 10g SQL Fundamentals II - Practice 4

 

Oracle Database 10g SQL Fundamentals II - Practice 4
4. Generating Reports by Grouping Related Data

1)
SELECT MANAGER_ID
, JOB_ID
, SUM(SALARY)
FROM EMPLOYEES
WHERE MANAGER_ID < 120
GROUP BY ROLLUP(MANAGER_ID, JOB_ID)
;

2)
SELECT MANAGER_ID
, JOB_ID
, SUM(SALARY)
, GROUPING(MANAGER_ID)
, GROUPING(JOB_ID)
FROM EMPLOYEES
WHERE MANAGER_ID < 120
GROUP BY ROLLUP(MANAGER_ID, JOB_ID)
;

3)
SELECT MANAGER_ID
, JOB_ID
, SUM(SALARY)
FROM EMPLOYEES
WHERE MANAGER_ID < 120
GROUP BY CUBE(MANAGER_ID, JOB_ID)
;

4)
SELECT MANAGER_ID AS MGR
, JOB_ID AS JOB
, SUM(SALARY)
, GROUPING(MANAGER_ID)
, GROUPING(JOB_ID)
FROM EMPLOYEES
WHERE MANAGER_ID < 120
GROUP BY CUBE(MANAGER_ID, JOB_ID)
;

5)
SELECT DEPARTMENT_ID
, MANAGER_ID
, JOB_ID
, SUM(SALARY)
FROM EMPLOYEES
GROUP BY
GROUPING SETS
((DEPARTMENT_ID, MANAGER_ID, JOB_ID)
, (DEPARTMENT_ID, JOB_ID)
, (MANAGER_ID, JOB_ID)
)
;