GROUP BY / HAVING/ JOIN/ UNION

 

GROUP BY 처리 순서 중요

FROM -> WHERE-> GROUP BY-> HAVING

GROUP BY절에서 사용한 컬럼만 SELECT절에서 여러행 함수와 함께 사용 가능

일반 조건 : WHERE

그룹 조건 : HAVING

 

급여가 5000이상인 사원의 부서별 사번, 급여, 부서번호를 출력하시오

SELECT employee_id, salary, department_id

FROM employees

WHERE salary >= 5000;

 

급여가 5000이상인 사원의 부서별 부서번호, 총급여를 출력하시오

SELECT department_id, SUM( salary )

FROM employees

WHERE salary >= 5000

GROUP BY department_id;

 

급여가 5000이상인 사원의 부서별 부서번호, 총급여를 출력하시오

단, 부서별 총급여가 20000이하인 부서들만 출력한다

SELECT department_id, SUM( salary )

FROM employees

WHERE salary >= 5000 AND SUM( salary )  <= 20000;

GROUP BY department_id; (X)

 

 

SELECT department_id, SUM( salary )

FROM employees

WHERE salary >= 5000

GROUP BY department_id

HAVING SUM(salary) <= 20000;

 

급여가 5000이상인 사원의 부서별 부서번호, 총급여를 출력하시오

단, 부서별 총급여가 20000이하인 부서들만 출력한다

부서배치를 받지 못한 사원은 제외한다 

SELECT department_id, SUM( salary )

FROM employees

WHERE salary >= 5000 AND department_id IS NOT NULL

GROUP BY department_id

HAVING SUM(salary) <= 20000;

 부서배치를 받지 못한 사원은 제외한다 => HAVING 절도 가능하나 WHERE절에 권장

 

 

부서별, 직무별 부서번호, 직무번호, 사원수를 출력하시오

작은 부서번호부터, 사원수 많은 직무부터 출력한다

SELECT department_id, job_id, COUNT(*)

FROM employees

GROUP BY department_id, job_id

ORDER BY department_id, COUNT(*) DESC;

oracle에서는 오름차순 정렬인경우, 마지막 행에 null이 보임. 

소계, 총계 구하려면 ROLL UP, CUBE 

 

부서별 최대급여를 출력하시오

SELECT department_id, MAX(salary)

FROM employees
GROUP BY department_id;

 

 

부서별 최대급여자의 사번, 이름, 급여를 출력하시오 -- SUBQUERY로 해결

SELECT empoloyee_id, first_name, salary
FROM employees
GROUP BY department_id; (x)

 

지역별 지역번호(location_id), 부서수를 출력하시오
SELECT location_id, COUNT(*)
FROM departments
GROUP BY  location_id;

 

 

SUBQUERY 

한 테이블의 컬럼을 출력해야 하는 경우 사용

 

UNION

여러 테이블의 행을 겹합

 

JOIN

여러 테이블의 여러 컬럼들을 결합해주는 기능.

사원의 사번, 이름, 부서번호, 부서명을 출력하시오

SELECT employee_id, department_id

FROM employees; --107건

SELECT department_id, department_name

FROM departments; --27건

 

카디션 프로덕트 : 조건 없는 조인

SELECT employee_id, department_name

FROM employees, departments;  --107*27 

 

표준(ANSI) 조인

JOIN 앞에 (INNER)가 생략되어 있음.

SELECT

FROM 테이블1 JOIN 테이블2 ON(테이블1. 컬럼 = 테이블2. 컬럼)

ON절은 직접 관계 없는 테이블은 연결 불가..

 

SELECT

FROM 테이블1 NATURAL JOIN 테이블2

테이블 별칭주기 사용 불가

 

SELECT

FROM 테이블1 JOIN 테이블2 USING 컬럼

테이블 별칭주기 사용 불가

 

 

=> 조건에 해당되는 행만 나옴 (NULL  x) 

 

1) JOIN ON

사원의 사번, 이름, 부서번호, 부서명을 출력하시오

SELECT employee_id, employees.department_id, department_name 

FROM employees

JOIN departments ON ( employees.department_id   = departments.department_id );

 

SELECT employee_id, e.department_id, department_name

FROM employees e

JOIN departments d ON ( e.department_id   = d.department_id );

별칭을 주면 별칭 명만 사용해야 함

 

 

사원의 사번, 이름, 부서번호, 부서명, 직무번호, 직무명을 출력하시오

SELECT employee_id, e.department_id,

                department_name,

                j.job_id,

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

 

테이명.을 사용할 때는 중복되는 내용이 있을때 어느 컬럼을 쓰는지 명시하기 위함.

ex) employees.department_id

 

 

국가의 국가번호 (country_id), 국가이름(country_name), 도시명(city)을 출력하시오

SELECT * FROM countries; -- 25건

SELECT * FROM locations; -- 23건

국가 이름(country_name), 도시명(city)을 출력하시오

SELECT l.country_id,

               c.country_name,

               l.city

FROM locations l

JOIN countries c ON (l.country_id = c.country_id) ;

 

2) NATURAL JOIN

사원의 사번, 직무번호, 직무명을 출력하시오

SELECT employee_id, jobs.job_id, jobs.job_title

FROM employees JOIN jobs ON (employees.job_id = jobs.job_id);

 

SELECT employee_id, job_id, job_title

FROM employees NATURAL JOIN jobs;
한 테이블의 컬럼명과 다른 테이블 컬럼명이 일치 하는 경우 NATURAL JOIN

 

 

 

사원의 사번, 부서번호, 부서명을 출력하시오 106건

SELECT employee_id, departments.department_id, departments.department_name

FROM employees JOIN departments ON (employees.department_id = departments.department_id);

 

SELECT employee_id, department_id, department_name

FROM employees NATURAL JOIN departments; (X)

 

실행은 되나 결과가 32건 나옴. 

이유: employees테이블과 departments테이블에 동일한 컬럼 아래 2건에 모두 만족하는 결과만 출력

부서장만 뽑은 결과

 

SELECT employee_id,employees.department_id, department_name

FROM employees JOIN departments

ON (employees.department_id = departments.department_id

AND

employees.manager_id = deparments.manager_id

); --위의 NATURAL JOIN과 같음

 

3) JOIN USING

사원의 사번, 직무번호, 직무명을 출력하시오

SELECT employee_id, jobs.job_id, jobs.job_title

FROM employees JOIN jobs ON (employees.job_id = jobs.job_id);

 

SELECT employee_id, jobs.job_id, jobs.job_title

FROM employees JOIN jobs USING (job_id);

 

 

사원의 사번, 부서번호, 부서명을 출력하시오 106건

SELECT employee_id, departments.department_id, departments.department_name

FROM employees JOIN departments ON (employees.department_id = departments.department_id);

 

SELECT employee_id, department_id, department_name

FROM employees JOIN departments USING(department_id);

 

 

부서가 없는 직원도 출력하고 싶은 경우 INNER JOIN은 사용 불가하고, OUTER JOIN을 사용해야 함

기준 테이블을 정해야한다

 

OUTER JOIN

기준 테이블은 다  출력 됨

전체 사원의 사번, 이름, 부서번호, 부서명을 출력하시오 107건

SELECT employee_id, employees.department_id, department_name

FROM employees LEFT OUTER JOIN departments   --LEFT, RIGTH있는 경우 OUTER생략가능

ON (employees.department_id = departments.department_id) ;

 

 

모든 국가의 국가번호 (country_id), 국가이름(country_name), 도시명(city)을 출력하시오

도시가 등록 안 된 국가도 출력한다

SELECT * FROM countries; -- 25건

SELECT * FROM locations; -- 23건

 

SELECT country_id, country_name, city

FROM countries LEFT JOIN locations USING (country_id);

 

SELECT country_id, country_name, city

FROM locations RIGHT JOIN countries USING  (country_id);

 

 

모든 사원의 사번, 부서번호, 부서명을 출력하시오

부서없는 사원도 출력하고, 사원이 없는 부서도 한다

 

SELECT employee_id, e.department_id, department_name

FROM employees e LEFT JOIN departments d

ON (e.department_id = d.department_id); --부서 없는 사원도 출력 107건

 

SELECT employee_id, e.department_id, department_name

FROM employees e RIGHT JOIN departments d

ON (e.department_id = d.department_id); --사원이 없는 부서도 출력 122건

 

SELECT employee_id, e.department_id, department_name

FROM employees e FULL JOIN departments d

ON (e.department_id = d.department_id); --부서 없는 사원도 출력 +사원없는 부서 : 123건

 

SELF JOIN

참조 관계를 봐야 한다.

 

사원의 사번, 이름, 관리자번호, 관리자 이름을 출력하시오 (106건)

SELECT e.employee_id, e.first_name, e.manager_id, m.first_name
FROM employees e JOIN employees m ON ( e.manager_id = m.employee_id );

 

사원의 사번, 이름, 사원소속부서, 관리자번호, 관리자이름, 관리자 소속부서를 출력하시오

사원부서와 관리자부서가 다른 사원들만 출력한다

SELECT e.employee_id 사번, e.first_name 이름, e.department_id 사원부서, 

               m.employee_id 관리자번호, m.first_name 관리자이름, m.department_id 관리자소속부서
FROM employees e JOIN employees m ON (e.manager_id = m.employee_id ) 
WHERE e.department_id <> m.department_id;

 

 

사원의 사번, 이름, 사원급여, 관리자번호, 관리자이름, 관리자급여를 출력하시오

사원 급여가 관리자 급여보다 많은 사원들만 출력한다

SELECT e.employee_id 사번, e.first_name 이름, e.salary 급여, m.employee_id 관리자번호,

               m.first_name 관리자이름, m.salary 관리자급여

FROM employees e JOIN employees m ON (e.manager_id = m.employee_id ) 

WHERE e.salary > m.salary ;

 

 

집합: UNION(합) -중복제거, UNION ALL(합), MINUS(차), INTERSECT(교) -  여러 테이블의 행을 결합

 

사원의 사번, 직무번호를 출력하시오 -107건

SELECT employee_id, job_id

FROM employees;

 

사원 경력 정보(사번, 이전직무번호)를 출력하시오 - 10건

SELECT employee_id, job_id

FROM job_history;

 

이전 경력정보와 현재 사원의 사번, 직무번호를 출력하시오 -117건

SELECT employee_id, job_id

FROM employees

UNION ALL

SELECT employee_id, job_id

FROM job_history

ORDER BY employee_id;

 

이전 직무와 다른 직무를 담당하는 사원의 사번, 직무번호를 출력하시오 -105건

SELECT employee_id, job_id

FROM employees

MINUS

SELECT employee_id, job_id

FROM job_history

ORDER BY employee_id;

 

이전 직무와 현재 직무를 담당하는 사원의 사번, 직무번호를 출력하시오 -- 2건

SELECT employee_id, job_id

FROM employees

INTERSECT

SELECT employee_id, job_id

FROM job_history

ORDER BY employee_id;