SQLD(SQL 개발자) 기출 · 그룹 함수

이 문제 풀어보기

1. 1.SELECT DNAME, JOB, COUNT(*) AS CNT, SUM(SAL) AS TOTAL_SAL
   FROM SCOTT.EMP A, SCOTT.DEPT B
   WHERE A.DEPTNO = B.DEPTNO
   GROUP BY DNAME, JOB
   UNION ALL
   SELECT DNAME, '' AS JOB, COUNT(*) AS CNT, SUM(SAL) AS TOTAL_SAL
   FROM SCOTT.EMP A, SCOTT.DEPT B
   WHERE A.DEPTNO = B.DEPTNO
   GROUP BY DNAME
   UNION ALL
   SELECT '' AS DNAME, '' AS JOB, COUNT(*) AS CNT, SUM(SAL) AS TOTAL_SAL
   FROM SCOTT.EMP A, SCOTT.DEPT B
   WHERE A.DEPTNO = B.DEPTNO
   ORDER BY 1, 2;
2. 2.SELECT DNAME, JOB, COUNT(*) AS CNT, SUM(SAL) AS TOTAL_SAL
   FROM SCOTT.EMP A, SCOTT.DEPT B
   WHERE A.DEPTNO = B.DEPTNO
   GROUP BY CUBE(DNAME, JOB)
   ORDER BY 1, 2;
3. 3.SELECT DNAME, JOB, COUNT(*) AS CNT, SUM(SAL) AS TOTAL_SAL
   FROM SCOTT.EMP A, SCOTT.DEPT B
   WHERE A.DEPTNO = B.DEPTNO
   GROUP BY GROUPING SETS((DNAME,JOB), DNAME, NULL)
   ORDER BY 1, 2;
4. 4.SELECT DNAME, JOB, COUNT(*) AS CNT, SUM(SAL) AS TOTAL_SAL
   FROM SCOTT.EMP A, SCOTT.DEPT B
   WHERE A.DEPTNO = B.DEPTNO
   GROUP BY ROLLUP(DNAME,JOB)
   ORDER BY DNAME, JOB;