1. 다음과 같은 결과가 나오도록 SQL문을 작성해보세요. 각 부서의 입사 연도별 사원 수, 최고 급여, 급여 합, 평균 급여를 출력하고 각 부서별 소계와 총계를 출력하세요. 특히 AVG_SAL은 소수점 셋째 자리에서 반올림되어 두 자리만 출력되도록 하세요.
SELECT DEPTNO, TO_CHAR(HIREDATE, 'YYYY ') AS HIRE_YEAR, COUNT(*) AS CNT, MAX(SAL) AS MAX_SAL, SUM(SAL) AS SUM_SAL, ROUND(AVG(SAL), 2) AS AVG_SAL FROM EMP GROUP BY ROLLUP(DEPTNO, HIREDATE) ORDER BY DEPTNO;
요렇게 짰는데 그림과는 달리 HIREDATE에서 그룹화?가 안되는거 같더라구요ㅠ 수정 부분이 혹시 어떻게 되는지 여쭙고 싶습니다ㅜㅠ
2. 다음과 같이 부서 정보, 사원정보, 급여 등급 정보, 각 사원의 직속 상관의 정보를 부서번호, 사원번호 순서로 정렬하여 출력해 보세요. 특히 EMPNO는 뒤에 두 자리를 생략해서 **로 표시되도록 하세요.
이거는 아예 시작부터 엄두가 안나서..ㅠ
-- 원하시는게 맞는지 모르겠네요... 1. SELECT deptno , hiredate , COUNT(empno) AS cnt , MAX(sal) AS max_sal , SUM(sal) AS sum_sal , ROUND(AVG(sal), 2) AS avg_sal FROM ( SELECT deptno , TO_CHAR(hiredate, 'YYYY') AS hiredate , empno , sal FROM emp ) GROUP BY ROLLUP(deptno, hiredate) ; 2. LOSAL, HISAL, GRADE 기준을 모르겠네요... -- 불 필요한 데이터는 제거하였습니다. SELECT d.deptno , d.dname , NVL2(e.empno, SUBSTR(e.empno, 1, 2) || '**', NULL) AS empno , e.ename , e.mgr , e.sal , PRIOR ename AS mgr_name FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr ORDER BY deptno ;
-- 1. SELECT deptno , TO_CHAR(hiredate, 'yyyy') hire_year , COUNT(*) cnt , MAX(sal) max_sal , SUM(sal) sum_sal , ROUND(AVG(sal), 2) avg_sal FROM emp GROUP BY ROLLUP(deptno, TO_CHAR(hiredate, 'yyyy')) ;
-- 2.1. Oracle 아우터 조인(+) -- SELECT d.deptno , d.dname , FLOOR(e.empno / 100) || '**' empno , e.ename , e.mgr , e.sal , e.deptno deptno_1 -- 이건 빼는게 좋을 듯 , s.losal , s.hisal , s.grade , m.empno mgr_empno -- 여기도 ** 붙여야 할 듯? , m.ename mgr_ename FROM dept d , emp e , emp m , salgrade s WHERE d.deptno = e.deptno(+) AND e.mgr = m.empno(+) AND e.sal BETWEEN s.losal(+) AND s.hisal(+) ORDER BY d.deptno, e.empno ;
-- 2.2. ANSI 아우터 조인 -- SELECT d.deptno , d.dname , FLOOR(e.empno / 100) || '**' empno , e.ename , e.mgr , e.sal , e.deptno deptno_1 -- 이건 빼는게 좋을 듯 , s.losal , s.hisal , s.grade , m.empno mgr_empno -- 여기도 ** 붙여야 할 듯? , m.ename mgr_ename FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno LEFT OUTER JOIN emp m ON e.mgr = m.empno LEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal ORDER BY d.deptno, e.empno ;