SQL 실전!(5)

Employees 테이블:

EmployeeID	Name	Department	Salary	ManagerID
1	Alice	HR	70000	NULL
2	Bob	IT	90000	1
3	Charlie	IT	80000	2
4	David	IT	85000	2
5	Eve	HR	75000	1
6	Frank	Finance	95000	NULL
7	Grace	Finance	80000	6
8	Heidi	IT	95000	2

 

요구사항:

각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하는 SQL 쿼리를 작성해주세요.

Name	Department	Salary	Top_Earner	Top_Salary
Alice	HR	70000	Eve	75000
Bob	IT	90000	Heidi	95000
Charlie	IT	80000	Heidi	95000
David	IT	85000	Heidi	95000
Eve	HR	75000	Eve	75000
Frank	Finance	95000	Frank	95000
Grace	Finance	80000	Frank	95000
Heidi	IT	95000	Heidi	95000

 

2. 부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회하는 SQL 쿼리를 작성해주세요.
   • 기대결과

Department	Avg_Salary
IT	87500

 

문제

1번

각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하는 SQL 쿼리를 작성해주세요.

 

문제풀이

왼쪽 테이블 e1
행 : employees 테이블의 직원 이름을 오름차순으로 정렬한 것
열 : employees 테이블의 Name, Department, Salary

SELECT Name
    , Department
    , Salary
FROM employees
ORDER BY Name
;

오른쪽 테이블 max_salaries
열 : 왼쪽 테이블의 부서를 기준으로 월급을 가장 많이 받는 사람의 이름, 그 사람의 연봉

SELECT Name
    , Department
    , Salary
FROM employees
WHERE Salary = 
(
SELECT MAX(Salary) 
FROM employees e2 
WHERE e2.Department = employees.Department
)
;

이 두 테이블을 LEFT JOIN

정답:

SELECT e1.Name
    , e1.Department
    , e1.Salary
    , max_salaries.Name AS Top_Earner
    , max_salaries.Salary AS Top_Salary
FROM
(
SELECT Name
    , Department
    , Salary
FROM employees
ORDER BY Name
) e1
LEFT JOIN
(
SELECT Name
    , Department
    , Salary
FROM employees
WHERE Salary = 
(
SELECT MAX(Salary) 
FROM employees e2 
WHERE e2.Department = employees.Department
)
) max_salaries
ON e1.Department = max_salaries.Department
;

결과 확인:

문제

2번

부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회하는 SQL 쿼리를 작성해주세요.

 

문제풀이

행에 필요한 조건식
: HAVING Avg_Salary = (SELECT MAX(Avg_Salary) … )

HAVING Avg_Salary = 
(
SELECT MAX(Avg_Salary)
FROM employees
)
;

 

열에 필요한 테이블 avg_Salaries
: Department, Avg_Salary

SELECT Department
    , AVG(Salary) AS Avg_Salary
FROM employees
GROUP BY Department
;

둘을 합치기

정답:

SELECT Department
    , Avg_Salary
FROM
(
SELECT Department
    , AVG(Salary) AS Avg_Salary
FROM employees
GROUP BY Department
) avg_Salaries /* 평균 월급 테이블 조회 */
HAVING Avg_Salary = 
(
SELECT MAX(Avg_Salary) /* 평균 월급 테이블에서 가장 큰값 조회 */
FROM /* 앞서 만든 평균 월급 테이블을 다시 활용 */
(
SELECT Department
    , AVG(Salary) AS Avg_Salary
FROM employees
GROUP BY Department
) avg_Salaries2
)
LIMIT 1
;

결과 확인: