SQL练习：查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

-- 8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#各部门平均工资 
SELECT AVG(salary) avgs, department_id 
FROM employees 
GROUP BY department_id 
#最高的平均工资 
SELECT MAX(avgs)
FROM (SELECT AVG(salary) avgs, department_id FROM employees GROUP BY department_id ) a 
#拥有最高平均工资的部门 
SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING AVG(salary) = (SELECT MAX(avgs)FROM (SELECT AVG(salary) avgs, department_id FROM employees GROUP BY department_id ) a )
#部门为XXX的manager 的详细信息 
SELECT last_name, department_id, email, salary
FROM employees 
WHERE department_id = (SELECT department_id 
FROM employees 
GROUP BY department_id 
HAVING AVG(salary) = (SELECT MAX(avgs)FROM (SELECT AVG(salary) avgs, department_id FROM employees GROUP BY department_id ) a ));