14일차 SQL 스터디: 가장 높은 월급을 받는 직원은?

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

 

요구사항:

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

// 내가 작성한 쿼리: 완전 틀림! 꽝
SELECT Name, 
	Department, 
    Salary, 
    Top_Earner,
    Top_salary
FROM Employees e
OUTER JOIN a ON e.Department = a.Department
(
SELECT Department,
	Name Top_Earner, 
	Top_salary
FROM 
(
SELECT MAX(Salary) Top_salary
FROM Employees
GROUP BY Department
)) a;

// 정답
SELECT 
    e1.Name,
    e1.Department,
    e1.Salary,
    e2.Name AS Top_Earner,
    e2.Salary AS Top_Salary
FROM 
    Employees e1
JOIN 
    Employees e2 ON e1.Department = e2.Department
WHERE 
    e2.Salary = (
        SELECT MAX(Salary)
        FROM Employees e3
        WHERE e3.Department = e1.Department
    ); 
    // e2는 MAX(Salary)를 가진 직원만 포함된 데이터
// e1, e2, e3 구분 필수

 

- 기대결과    

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 쿼리를 작성해주세요

// 내가 작성한 쿼리: 현재 테이블에서는 동일한 결과 출력 가능
SELECT Department, 
	AVG(Salary) Avg_Salary
FROM Employees
GROUP BY Department
ORDER BY Avg_Salary DESC
LIMIT 1;
// 최고 평균 급여를 가진 부서가 여러 개일 경우, 이 쿼리는 하나의 부서만 반환

// 정답
SELECT Department,
    AVG(Salary) AS Avg_Salary
FROM Employees
GROUP BY Department
HAVING 
    AVG(Salary) = (
        SELECT MAX(Avg_Salary)
        FROM 
            (SELECT AVG(Salary) AS Avg_Salary
             FROM Employees
             GROUP BY Department) AS subquery
    );
    // 부서별 AVG(Salary) 중 MAX값 SELECT -> 이 값을 AVG(Salary)로 가진 부서명과 AVG(Salary)값 출력
// 동점 처리 가능, 다른 조건 추가나 더 복잡한 로직 적용 용이
// 쿼리가 길어지고 이해하기 어려움, 데이터 크기가 커지면 성능이 떨어질 수 있음

 

- 기대결과 

Department	Avg_Salary
IT	87500

제약사항:

• 두 쿼리 모두 서브쿼리, JOIN, GROUP BY, HAVING 등의 기능을 활용해주세요.