[오라클로 배우는 데이터베이스 개론과 실습] 실무 실습 A.9 다차원 그룹 데이터 검색

A.9 다차원 그룹 데이터 검색 : ROLLUP, CUBE

 

샘플 문제

SELECT DEPARTMENT_ID, JOB_ID, TO_CHAR(SUM(SALARY), '$999,999,00') AS "Salary SUM"
                ,COUNT(EMPLOYEE_ID) AS "Count EMPs"
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY DEPARTMENT_ID;

 

샘플 문제 - 계속

SELECT DEPARTMENT_ID, JOB_ID, TO_CHAR(SUM(SALARY), '$999,999,00') AS "Salary SUM"
                ,COUNT(EMPLOYEE_ID) AS "Count EMPs"
FROM EMPLOYEES
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID;

 

문제 1

SELECT DEPARTMENT_ID, JOB_ID, TO_CHAR(SUM(SALARY), '$999,999,00') AS "Salary SUM"
                ,COUNT(EMPLOYEE_ID) AS "Count EMPs"
FROM EMPLOYEES
GROUP BY CUBE(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID;

 

 

문제 2

SELECT DEPARTMENT_ID, JOB_ID, TO_CHAR(SUM(SALARY), '$999,999.00') AS "Salary SUM"
                ,COUNT(EMPLOYEE_ID) AS "Count EMPs", grouping(department_id) as "Dept#", 
                grouping(job_id) "Job ID"
FROM EMPLOYEES
GROUP BY CUBE(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID;

 

 

 

문제 3

SELECT DECODE(GROUPING(DEPARTMENT_ID), 1, 'ALL_DEPTS',
                                       DEPARTMENT_ID) 
                                       AS "Dept#", 
       DECODE(GROUPING(Job_id), 1, 'All-JOBS',
                                JOB_ID) "Jobs",
       TO_CHAR(AVG(SALARY*12), '$999,999') AS "AVG Ann_sal",
       COUNT(EMPLOYEE_ID) AS "Count EMPs"
FROM EMPLOYEES
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID;