SQLD 기출문제 예상문제 76번

다음 문제를 풀어보세요.

다음 중 부서별 급여 총액을 계산하고, 급여 총액이 평균 급여 총액보다 큰 부서의 부서명과 급여 총액을 내림차순으로 정렬하여 출력하는 SQL 쿼리로 가장 적절한 것을 고르시오.

-- 테이블 구조: EMPLOYEES (부서ID: DEPARTMENT_ID, 급여: SALARY, 이름: EMP_NAME)
-- 테이블 구조: DEPARTMENTS (부서ID: DEPARTMENT_ID, 부서명: DEPARTMENT_NAME)

SELECT D.DEPARTMENT_NAME, SUM(E.SALARY) AS TOTAL_SALARY
FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
HAVING SUM(E.SALARY) > AVG(SUM(E.SALARY))
ORDER BY TOTAL_SALARY DESC;

SELECT D.DEPARTMENT_NAME, SUM(E.SALARY) AS TOTAL_SALARY
FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
HAVING SUM(E.SALARY) > (SELECT AVG(TOTAL_SALARY) FROM (SELECT SUM(SALARY) AS TOTAL_SALARY FROM EMPLOYEES GROUP BY DEPARTMENT_ID) AS AVG_TABLE)
ORDER BY TOTAL_SALARY DESC;

SELECT D.DEPARTMENT_NAME, SUM(E.SALARY) AS TOTAL_SALARY
FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE SUM(E.SALARY) > (SELECT AVG(SALARY) FROM EMPLOYEES)
GROUP BY D.DEPARTMENT_NAME
ORDER BY TOTAL_SALARY DESC;

SELECT D.DEPARTMENT_NAME, SUM(E.SALARY) AS TOTAL_SALARY
FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
WHERE TOTAL_SALARY > (SELECT AVG(TOTAL_SALARY) FROM (SELECT SUM(SALARY) AS TOTAL_SALARY FROM EMPLOYEES GROUP BY DEPARTMENT_ID) AS AVG_TABLE)
ORDER BY TOTAL_SALARY DESC;