[sqld] 노랭이책(개정판) - 57번 문제(p.70)

1. 예제 테이블

예제쿼리.txt

0.00MB

 

2. 문제 보기

-- 1번
SELECT b.grade, a.job, SUM(a.sal) AS SUM_SAL, COUNT(*) AS CNT
FROM emp a, salgrade b
WHERE a.sal BETWEEN b.losal AND b.hisal 
GROUP BY GROUPING SETS (grade, (job, grade));

     GRADE JOB                                                   SUM_SAL        CNT
---------- -------------------------------------------------- ---------- ----------
         2 CLERK                                                    1300          1
         2 SALESMAN                                                 2500          2
         2                                                          3800          3
         3 SALESMAN                                                 1500          1
         3                                                          1500          1
         4 ANALYST                                                  6000          2
         4 MANAGER                                                  5425          2
         4                                                         11425          4

-- 2번
SELECT b.grade, a.job, SUM(a.sal) AS SUM_SAL, COUNT(*) AS CNT
FROM emp a, salgrade b
WHERE a.sal BETWEEN b.losal AND b.hisal 
GROUP BY ROLLUP (b.grade, a.job);

---------- -------------------------------------------------- ---------- ----------
         2 CLERK                                                    1300          1
         2 SALESMAN                                                 2500          2
         2                                                          3800          3
         3 SALESMAN                                                 1500          1
         3                                                          1500          1
         4 ANALYST                                                  6000          2
         4 MANAGER                                                  5425          2
         4                                                         11425          4
                                                                   16725          8

-- 3번
SELECT b.grade, a.job, SUM(a.sal) AS SUM_SAL, COUNT(*) AS CNT
FROM emp a, salgrade b
WHERE a.sal BETWEEN b.losal AND b.hisal 
GROUP BY grade, ROLLUP (job);

     GRADE JOB                                                   SUM_SAL        CNT
---------- -------------------------------------------------- ---------- ----------
         2 CLERK                                                    1300          1
         2 SALESMAN                                                 2500          2
         2                                                          3800          3
         3 SALESMAN                                                 1500          1
         3                                                          1500          1
         4 ANALYST                                                  6000          2
         4 MANAGER                                                  5425          2
         4                                                         11425          4

-- 4번
SELECT b.grade, a.job, SUM(a.sal) AS SUM_SAL, COUNT(*) AS CNT
FROM emp a, salgrade b
WHERE a.sal BETWEEN b.losal AND b.hisal 
GROUP BY grade, CUBE (job);

     GRADE JOB                                                   SUM_SAL        CNT
---------- -------------------------------------------------- ---------- ----------
         2                                                          3800          3
         2 CLERK                                                    1300          1
         2 SALESMAN                                                 2500          2
         3                                                          1500          1
         3 SALESMAN                                                 1500          1
         4                                                         11425          4
         4 ANALYST                                                  6000          2
         4 MANAGER                                                  5425          2