SQLP CBT 기출문제 예상문제 467번

다음 문제를 풀어보세요.

다음 중 CTE(Common Table Expression)를 사용하여 복잡한 쿼리를 재사용 가능한 형태로 구성한 것으로 가장 적절한 것을 고르시오.

[테이블 구조]
CREATE TABLE EMPLOYEES (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(100),
    DEPT_ID NUMBER,
    SALARY NUMBER,
    HIRE_DATE DATE
);

CREATE TABLE DEPARTMENTS (
    DEPT_ID NUMBER PRIMARY KEY,
    DEPT_NAME VARCHAR2(100)
);

[요구사항]

각 부서별 최고 연봉자를 찾는다.
그 결과를 바탕으로, 해당 부서의 평균 연봉보다 20% 이상 높은 연봉을 받는 직원들을 모두 조회한다.

SELECT E.EMP_NAME, D.DEPT_NAME, E.SALARY
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
WHERE E.SALARY > (SELECT AVG(SALARY) * 1.2 FROM EMPLOYEES WHERE DEPT_ID = E.DEPT_ID);

WITH DeptAvgSalary AS (
    SELECT DEPT_ID, AVG(SALARY) AS AvgSal
    FROM EMPLOYEES
    GROUP BY DEPT_ID
),
HighEarners AS (
    SELECT E.EMP_ID, E.EMP_NAME, E.DEPT_ID, E.SALARY
    FROM EMPLOYEES E
    JOIN DeptAvgSalary DAS ON E.DEPT_ID = DAS.DEPT_ID
    WHERE E.SALARY > DAS.AvgSal * 1.2
)
SELECT HE.EMP_NAME, D.DEPT_NAME, HE.SALARY
FROM HighEarners HE
JOIN DEPARTMENTS D ON HE.DEPT_ID = D.DEPT_ID
WHERE (HE.DEPT_ID, HE.SALARY) IN (
    SELECT DEPT_ID, MAX(SALARY) FROM HighEarners GROUP BY DEPT_ID
);

SELECT E.EMP_NAME, D.DEPT_NAME, E.SALARY
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
WHERE (E.DEPT_ID, E.SALARY) IN (
    SELECT DEPT_ID, MAX(SALARY)
    FROM EMPLOYEES
    GROUP BY DEPT_ID
)
AND E.SALARY > (SELECT AVG(SALARY) * 1.2 FROM EMPLOYEES WHERE DEPT_ID = E.DEPT_ID);

WITH MaxSalaryPerDept AS (
    SELECT DEPT_ID, MAX(SALARY) AS MaxSal
    FROM EMPLOYEES
    GROUP BY DEPT_ID
),
AvgSalaryPerDept AS (
    SELECT DEPT_ID, AVG(SALARY) AS AvgSal
    FROM EMPLOYEES
    GROUP BY DEPT_ID
)
SELECT E.EMP_NAME, D.DEPT_NAME, E.SALARY
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
JOIN MaxSalaryPerDept MSPD ON E.DEPT_ID = MSPD.DEPT_ID AND E.SALARY = MSPD.MaxSal
JOIN AvgSalaryPerDept ASPD ON E.DEPT_ID = ASPD.DEPT_ID
WHERE E.SALARY > ASPD.AvgSal * 1.2;