[프로그래머스] 부서별 평균 연봉 조회하기

□ 문제 URL

부서별 평균 연봉 조회하기

□ 문제 풀이

• 2개 테이블 LEFT JOIN
• GROUP BY 로 평균 연봉 출력

-- MySQL

WITH join_table AS (
    SELECT
        he.DEPT_ID, hd.DEPT_NAME_EN, he.SAL 
    FROM
        HR_EMPLOYEES AS he
    LEFT JOIN
        HR_DEPARTMENT AS hd
    ON he.DEPT_ID = hd.DEPT_ID
)
SELECT 
    DEPT_ID,  DEPT_NAME_EN,
    ROUND(AVG(SAL)) AS AVG_SAL
FROM join_table
GROUP BY DEPT_ID, DEPT_NAME_EN
ORDER BY AVG_SAL DESC
;