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

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

- Name, Department, Salary
- Max(Salary), GROUP BY Department, Name - Top_Earner
- Max(Salary), GROUP BY Department - Top_Salary
- ORDER BY Department DESC LIMIT - Top_Salary, Top_Earner

내 답 : 

SELECT Name,
       Department,
       Salary,
       Top_Earner,
       Top_Salary
FROM Employees
(
SELECT Name Top_Earner,
       Department,
       Salary Top_Salary
FROM Employees
GROUP BY Department
ORDER BY DESC
LIMIT 1
) 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
    );

---

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

- 부서별로 평균 월급 : AVG(Salary), GROUP BY Department, ORDER BY AVG(Salary) DESC, LIMIT 1
-  Avg_Salary

내 답 : 

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