[ JOIN과 Subquery ]
3. 집합(Set) 연산자
1) Union : 두 질의 결과값의 합에서 중복을 제거 → 합집합
-- 부서번호가 50이거나 90인 사원과
-- 급여가 10000 이상인 사원의
-- 사번, 이름, 급여, 부서번호
select employee_id, first_name, salary, department_id
from employees
where department_id in (50, 90)
union
select employee_id, first_name, salary, department_id
from employees
where salary >= 10000;
2) Union all : 두 질의 결과값의 합에서 중복을 포함 → 합집합 + 교집합
select employee_id, first_name, salary, department_id
from employees
where department_id in (50, 90)
union all
select employee_id, first_name, salary, department_id
from employees
where salary >= 10000;
3) Intersect : 교집합
select employee_id, first_name, salary, department_id
from employees
where department_id in (50, 90)
intersect
select employee_id, first_name, salary, department_id
from employees
where salary >= 10000;
4) Minus : 차집합
select employee_id, first_name, salary, department_id
from employees
where department_id in (50, 90)
minus
select employee_id, first_name, salary, department_id
from employees
where salary >= 10000;
-- 차이 확인!
select employee_id, first_name, salary, department_id
from employees
where salary >= 10000
minus
select employee_id, first_name, salary, department_id
from employees
where department_id in (50, 90);
[ Group by ]
1. 단일행을 이용한 Group by절
1) 기본 문법
-- 부서별 급여총합, 평균급여, 사원수, 최대급여, 최소급여
select department_id 부서번호, sum(salary) 급여총합, avg(salary) 평균급여,
count(employee_id) 사원수, max(salary) 최대급여, min(salary) 최소급여
from employees
group by department_id;
- select 절에는 Groupping 한 컬럼과 Group 함수만 쓸 수 있음
2) group 함수 : avg, count, max, min, sum
* 그룹 함수는 where 조건절에 사용할 수 없음.
3) Having 절 : group by의 조건절
* 일반 조건은 where 절에, group by의 조건은 having 절에!
-- 부서별 급여총합, 평균급여, 사원수, 최대급여, 최소급여
-- 평균급여가 5000 이하인 부서
select department_id 부서번호, sum(salary) 급여총합, avg(salary) 평균급여,
count(employee_id) 사원수, max(salary) 최대급여, min(salary) 최소급여
from employees
group by department_id
having avg(salary) <= 5000;
4) 응용
--1. 모든 부서의 평균 급여(각 부서별 평균)보다 많이 받는 사원의
-- 사번, 이름, 급여
select employee_id, first_name, salary
from employees
where salary > all ((select avg(salary)
from employees
group by department_id
)
);
--2. 부서별 최고 급여를 받는 사원의
-- 부서이름, 사번, 이름, 급여 (단, 부서가 없는 사원은 제외한다)
select (select department_name from departments where e.department_id = department_id) 부서이름,
e.employee_id 사번, e.first_name 이름, e.salary 급여
from employees e, (select department_id, max(salary) 급여
from employees
group by department_id
having department_id is not null) m
where e.salary = m."급여"
and e.department_id = m.department_id;
--3-1 부서가 없는 사원을 포함하려면?
select (select department_name from departments where e.department_id = department_id) 부서이름,
e.employee_id 사번, e.first_name 이름, e.salary 급여
from employees e, (select department_id, max(salary) 급여
from employees
group by department_id
having department_id is not null) m
where e.salary = m."급여"
and e.department_id = m.department_id
union
select (select department_name from departments where e.department_id = department_id) 부서이름,
e.employee_id 사번, e.first_name 이름, e.salary 급여
from employees e, (select department_id, max(salary) 급여
from employees
group by department_id
having department_id is null) m
where e.salary = m."급여";
--3-2. where 절에서 is null을 쓰면?
select (select department_name from departments where e.department_id = department_id) 부서이름,
e.employee_id 사번, e.first_name 이름, e.salary 급여
from employees e, (select department_id, max(salary) 급여
from employees
group by department_id) m
where e.salary = m."급여"
or e.department_id is null -- 지금은 값이 같지만, null인 사람이 여러명인 경우라면 max 자체가 제대로 안나올 것임
and e.department_id = m.department_id;
5) Top N Query
(1) 예제
-- 사번, 이름, 급여, 입사연대, 부서이름
-- 급여순 순위,
-- 한 페이지당 5명씩 출력된다고 가정할 때, 2쪽을 출력할 것
-- 1980년대, 1990년대, 2000년대
select b.rn 급여순위, b.employee_id 사번, b.first_name 이름, b.salary 급여,
case
when to_char(b.hire_date, 'yyyy') < '1990' -- 범위비교를 하는 것이므로, 의미상 decode 보다는 case가 적합함
then '1980년대'
when to_char(b.hire_date, 'yyyy') < '2000'
then '1990년대'
else '2000년대'
end 입사연대,
d.department_name 부서이름
from (select rownum rn, a.*
from (select employee_id, first_name, salary, hire_date, department_id
from employees
order by salary desc) a
where rownum <= &page * 5) b, departments d
where b.department_id = d.department_id(+)
and b.rn > &page * 5 - 5 -- 변수로 지정할 경우도 고려해야 하므로 숫자는 통일되게 계산하는 것이 좋음
order by 급여순위; --? 이게 갑자기 왜 순서가 바뀌었을까??
-- 오답체크
select e.employee_id 사번, e.first_name 이름, e.salary 급여,
decode( substr( to_char(e.hire_date, 'yyyy') , 3, 2),
'1980', '1980년대',
'1990', '1990년대',
'2000년대') "입사 연대",
(select department_name from departments where e.department_id = department_id) "부서이름" --이 부분 확인!!!
from (select rownum 순번, employee_id
from (select employee_id
from employees
order by salary desc)) s, employees e
where s.employee_id = e.employee_id
and s.순번 between ((&page*5)-4) and (&page * 5);
(2) 관련 함수
▷ Rank | Dense_rank | Row_number () OVER ( [ PARTITION BY colum_name ] ORDER BY colum [ ASC | DESC ] )
- prtition by 뒤에 컬럼을 입력하여 그룹별로 순번을 부여할 수 있음
- 정렬 기본값은 asc
- 값이 중복되더라도 중복순위 없이 유일값을 부여함
▷ &page : 변수 값 받아오기
select *
from employees
where salary > &page;
▷ rownum : 행번호
- order by로 지정하기 이전의 행번호를 가상의 컬럼에 보여주는 것. (roww_number 함수와 달리 아래와 같은 제약조건이 있음)
- 작다 비교는 가능 (rownum의 최소값이 1이므로)
- 크다 비교는 불가 (rownum의 최대값은 무한대이므로)
select rownum, employee_id, salary
from employees
where rownum < 10;
-- 크다비교 >> 에러
select rownum, employee_id, salary
from employees
where rownum > 5;
- end.
.png)
