[SQL 달리기반] Lv4. 가장 높은 월급을 받는 직원은?

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Department VARCHAR(50),
    Salary INT,
    ManagerID INT,
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

INSERT INTO Employees (EmployeeID, Name, Department, Salary, ManagerID) VALUES
(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);

 

 

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

• 기대결과

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

 

 

✅제약사항:

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

 

 

✅문제풀이:

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

select e.Name, e.Department, e.Salary, e2.Name as Top_Earner, e2.Salary as Top_Salary
from Employees e
join Employees e2 on e.Department=e2.Department
where e2.Salary = (
	select max(e3.Salary)
	from Employees e3
	where e.Department=e3.Department
);

• e1과 e2 테이블을 JOIN해서 같은 부서의 직원들끼리 매칭한다.
• e2.Salary는 서브쿼리에서 해당 부서의 최고 월급을 가져온다.
• 서브쿼리

 (
     SELECT MAX(Salary) 
     FROM Employees e3 
     WHERE e3.Department = e1.Department
 )

• 는 e1과 같은 부서에 속한 직원들 중 가장 높은 월급을 반환한다.
• 최종적으로 각 직원의 이름, 부서, 월급과 함께 해당 부서에서 가장 높은 월급을 받는 직원의 이름과 월급을 출력한다.

 

 

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

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 sub
);

• GROUP BY를 통해 부서별로 직원들의 평균 월급을 계산한다.
• 서브쿼리

(
    SELECT MAX(Avg_Salary) 
    FROM (
        SELECT AVG(Salary) AS Avg_Salary 
        FROM Employees 
        GROUP BY Department
    ) AS subquery
)

• 는 모든 부서의 평균 월급 중 가장 높은 값을 찾는다.
• HAVING 절을 사용해 이 최고 평균 월급을 가진 부서만을 필터링하여 결과를 반환한다.