7. 부서별 평균 급여보다 적게 받는 사원

부서별 평균 급여보다 적게 받는 사원은 누구?

1.부서별 평균급여

SELECT department_name, AVG(salary)
FROM departments d JOIN employees e
ON d.department_id = e.department_id
GROUP BY department_name

2. 1번을 테이블로 생각하여 접근

SELECT *
FROM
 (SELECT department_name, AVG(salary)
 FROM departments d JOIN employees e
 ON d.department_id = e.department_id
 GROUP BY department_name) dept_avgsal

 

SELECT employee_id, last_name, department_name, salary
FROM departments d JOIN employees e
ON d.department_id = e.department_id
JOIN  (SELECT department_name, AVG(salary) avgsal
 FROM departments d JOIN employees e
 ON d.department_id = e.department_id
 GROUP BY department_name) dept_avgsal
ON d.department_name = dept_avgsal.department_name
WHERE dept_avgsal.avgsal > salary