SQLD 기출문제 예상문제 21번

다음 문제를 풀어보세요.

다음 중 부서별 급여 총액을 기준으로 상위 3개 부서를 추출하고, 각 부서별로 급여가 높은 순으로 순위를 매긴 후, 각 부서의 급여 1위 사원의 이름과 급여를 출력하는 SQL 쿼리로 가장 적절한 것을 고르시오.

-- EMP 테이블 구조
CREATE TABLE EMP (
    EMPNO NUMBER(4) PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2)
);

-- DEPT 테이블 구조
CREATE TABLE DEPT (
    DEPTNO NUMBER(2) PRIMARY KEY,
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13)
);

-- 샘플 데이터
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-11-1981', 'DD-MM-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('01-05-1981', 'DD-MM-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('09-06-1981', 'DD-MM-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('02-04-1981', 'DD-MM-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('03-12-1981', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-12-1980', 'DD-MM-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-02-1981', 'DD-MM-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-02-1981', 'DD-MM-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-09-1981', 'DD-MM-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('08-09-1981', 'DD-MM-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('12-01-1983', 'DD-MM-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('03-12-1981', 'DD-MM-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('23-01-1982', 'DD-MM-YYYY'), 1300, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('09-12-1982', 'DD-MM-YYYY'), 3000, NULL, 20);

WITH DEPT_SUM AS (
    SELECT DEPTNO, SUM(SAL) AS TOTAL_SAL
    FROM EMP
    GROUP BY DEPTNO
    ORDER BY TOTAL_SAL DESC
    FETCH FIRST 3 ROWS ONLY
),
EMP_RANK AS (
    SELECT DEPTNO, ENAME, SAL, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RN
    FROM EMP
    WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT_SUM)
)
SELECT ER.ENAME, ER.SAL
FROM EMP_RANK ER
WHERE RN = 1;

SELECT ENAME, SAL
FROM (
    SELECT ENAME, SAL, DEPTNO, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANK_NUM,
           DENSE_RANK() OVER (ORDER BY SUM(SAL) DESC) AS DEPT_RANK
    FROM EMP
    GROUP BY ENAME, SAL, DEPTNO
) AS SUBQUERY
WHERE RANK_NUM = 1 AND DEPT_RANK <= 3;

WITH RankedDepts AS (
    SELECT DEPTNO, SUM(SAL) AS TotalSal, RANK() OVER (ORDER BY SUM(SAL) DESC) AS DeptRank
    FROM EMP
    GROUP BY DEPTNO
),
RankedEmps AS (
    SELECT DEPTNO, ENAME, SAL, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS EmpRank
    FROM EMP
)
SELECT re.ENAME, re.SAL
FROM RankedEmps re
JOIN RankedDepts rd ON re.DEPTNO = rd.DEPTNO
WHERE rd.DeptRank <= 3 AND re.EmpRank = 1;

SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT GROUP BY DEPTNO ORDER BY SUM(SAL) DESC LIMIT 3)
AND ROWNUM IN (SELECT ROWNUM FROM EMP GROUP BY DEPTNO ORDER BY SAL DESC LIMIT 1);