JOIN 예제

1.부서의 부서번호, 부서가 속한 지역의 도시명(city), 국가명(country_name)을 출력하시오

SELECT department_id, city, country_name
FROM departments d JOIN locations l ON (d.location_id = l.location_id)
		  JOIN countries c ON (l.country_id = c.country_id);

2. 사원의 사번, 부서번호, 부서명, 직무번호, 직무명을 출력하시오
   직무명에 'Manager'를 포함한 사원들만 출력하시오
   직무번호순, 부서명으로 오름차순하시오

SELECT employee_id, e.department_id, d.department_name
	  ,e.job_id, job_title
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
				 JOIN jobs j ON (e.job_id = j.job_id)
WHERE INSTR(job_title, 'Manager') > 0
ORDER BY job_id, department_name;

3. 부서별 부서번호, 부서명, 사원수, 평균급여를 출력하시오

SELECT e.department_id, department_name, COUNT(*)
	  ,TRUNC(AVG(salary), 2) "평균급여"
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY e.department_id, department_name;

4. 부서별 사원수가 10명이상인 부서들의 부서별 부서번호, 부서명, 사원수, 평균급여를 출력하시오

SELECT e.department_id, department_name, COUNT(*)
	  ,TRUNC(AVG(salary), 2) "평균급여"
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY e.department_id, department_name
HAVING COUNT(*) >= 10;

5. 사원의 부서번호와 관리자의 부서번호가 일치하지않는 사원들 사번, 부서번호를 출력하시오 
   사번순으로 오름차순 정렬하시오

SELECT e.employee_id, e.department_id
FROM employees e JOIN employees m ON (e.manager_id = m.employee_id)
WHERE e.department_id <> m.department_id
ORDER BY e.employee_id;

6. 각 도시에 있는 부서수를 출력하시오

SELECT COUNT(department_id)
FROM departments d, locations l
WHERE d.location_id = l.location_id
GROUP BY city;

7. 각 도시에 있는 부서수를 출력하시오.  단, 부서가 없는 도시도 모두 출력하시오

SELECT COUNT(department_id)
FROM locations l LEFT JOIN departments d ON(d.location_id = l.location_id)  
GROUP BY city;
--COUNT(*) → 1
--COUNT(department_id) → 0

SELECT
FROM JOIN
WHERE
GROUP BY
HAVING
ORDER BY
--작성 먼저 하면 작성하기 쉽다