[SQL]Lv4(2). 가장 높은 월급을 받는 직원은?

아래에 나오는 데이터는 서버에 없는 데이터 이기때문에 따로 CSV 파일을 만들어 LOCALHOST(MY SQL) 서버에 데이터를 넣어주어야한다.

방법:https://computerreport.tistory.com/16

CSV 파일을 이용해 데이터 가져오기(MY 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

주의사항

- 모두 서브쿼리 join,group by,Having 등을 사용하여야한다.

요구사항

1.

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

 

ex)

 

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

select e1.Name,
       e1.Department,
       e1.Salary, 
       e3.top_Earner, 
       e3.top_salary 
from employee e1 inner join 
                          (select Department,
                                  Name Top_Earner,
                                  Salary Top_Salary
                           from(select Department,
                                       Name,
                                       Salary,
                                       rank()over(partition by Department order by Salary desc) rn
                                from employee)e2
                           where rn=1)e3
 on e1.Department = e3.Department

 

Lv.4 이전에 했던 알고리즘과 비슷한 맥락으로 풀어보았다.

 

코드 해석

• 부서별로 월급이 많은 순서대로 순위를 매기기  -> 부서별로 rank가 1등인 인스턴스만 추출  -> 모든 인스턴스와 그 부서의 가장 높은 월급과 직원의 이름이 나오는 컬럼을 만들기  ->  부서 컬럼을 inner join하여 일치하는 값 출력

 

 

 

2.

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

 

ex)

Department	Avg_Salary
IT	87500

 

 

방법 1

 

햇갈려서 정답을 참고 하였는데 다양한 방법이 있어 처음 본 함수에 대해 알아보았다.

LIMIT 라는 함수인데

 

LIMIT 숫자1,숫자2

쿼리 인덱스컬럼 숫자부터 숫자 2까지 출력한다.

Order by 랑 쓰면 유용하게 쓰일거 같다.

 

그래서 limit 함수를 활용하면 쉽게 풀 수 있다.

select Department,Avg(Salary) Avg_Salary
from employee
group by 1
order by 2 desc
limit 1

 

평균이 같더라도 젤위의 하나의 값만 출력하게 된다.

 

 

방법2

select Department,Avg_Salary
from(
select Department,Avg_Salary,rank() over(order by avg_salary desc) rn
from (select department,avg(salary) Avg_Salary
      from employee
      group by 1)a
      )b
where rn=1

 

코드 해석

• 부서별로 Salary 평균 구하기 -> 가장 salary의 평균이 높은 부서별로 rank 매기기 -> rank가 1인것만 출력 

 

이 코드를 사용하면 평균이 같다면 2개의 결과가 출력한다.