[Oracle] 집합연산자

집합 연산자

-select 절의 컬럼의 개수가 일치해야한다.

-첫번째 select절에 대응되는 두번째 select절 컬럼의 데이터 타입이 일치해야한다.

-union, intersect, minus 연산자는 중복을 제거하기 위해서 정렬이 수행된다

-order by절은 첫번째 select절의 컬럼이름, 별칭, 위치 표기법을 사용한다.(가장 마지막줄에)

주의) 컬럼의 별칭을 사용하면 무조건 별칭, 위치표기법만 사용해야 한다.

-성능↓

select employee_id, job_id, salary
from employees
union
select employee_id, job_id, 0
from job_history;

 

합집합

-union : 중복제거

-union all : 중복포함

 

교집합

intersect : 두개의 쿼리에서 똑같은 값만 출력. 부서이동을 한 직원

 

차집합

minus : job_id를 한번도 바꾸지 않은 직원

 

rollup

: group by절에 지정된 열 리스트를 오른쪽에서 왼쪽으로 이동하면서 그룹화를 만드는 연산자

select a ,b, c, sum(sal)
from test
group by rollup(a, b, c);

① sum(sal) = {a,b,c}
② sum(sal) = {a,b}
③ sum(sal) = {a}
④ sum(sal) = { }

cube

: rollup 연산자를 포함하고 모든 그룹화 할 수 있는 걸 만드는 연산자

select a ,b, c, sum(sal)
from test
group by cube(a, b, c);

① sum(sal) = {a,b,c}
② sum(sal) = {a,b}
③ sum(sal) = {a,c}
④ sum(sal) = {b,c}
⑤ sum(sal) = {a}
⑥ sum(sal) = {b}
⑦ sum(sal) = {c}
⑧ sum(sal) = { }

grouping sets

: 내가 원하는 그룹을 만드는 연산자

select a ,b, c, sum(sal)
from test
group by grouping sets( (a, b), (a,c), () );

① sum(sal) = {a,b}
② sum(sal) = {a,c}
③ sum(sal) = { }

---

[문제82] job_id를 한 번이라도 바꾼 사원들의 정보를 출력해주세요.

1. 집합연산자
select *
from employees
where employee_id in (select employee_id
                      from employees
                      intersect --문제점 : 정렬로 인한 불필요한 work 발생
                      select employee_id
                      from job_history);
2. exists --개선
select *
from employees e
where exists (select 1
              from job_history
              where employee_id = e.employee_id) ;

[문제83] job_id를 바꾸지 않은 사원들의 정보를 출력해주세요.

1. 집합연산자
select *
from employees
where employee_id in (select employee_id
                      from employees
                      minus
                      select employee_id
                      from job_history);
2. not exists --개선
select *
from employees e
where not exists (select 'x'
                  from job_history
                  where employee_id = e.employee_id) ;

[문제 84] 부서가 소재하지 않은 국가의 country_id, country_name을 출력해주세요.

1. 집합연산자
select country_id, country_name
from countries
minus
select c.country_id, c.country_name
from departments d, locations l, countries c
where d.location_id = l.location_id
and l.country_id = c.country_id;

2. not exists
select country_id, country_name
from countries c
where not exists (select 'x'
                  from departments d, locations l
                  where d.location_id = l.location_id
                  and l.country_id = c.country_id);

[문제87] 년도 분기별 총액을 구하세요. 행의 합과 열의 합도 구하세요.

YEAR      1분기      2분기      3분기      4분기         합
---- ---------- ---------- ---------- ---------- ----------
2001      17000                                       17000
2002                 36808      21008      11000      68816
2003                 35000       8000       3500      46500
2004      40700      14300      17000      14000      86000
2005      86900      16800      60800      33400     197900
2006      69400      20400      14200      17100     121100
2007      36600      20200       2500      35600      94900
2008      46900      12300                            59200
         297500     155808     123508     114600     691416

select year, max(decode(quarter,1,sum_sal)) "1분기",
             max(decode(quarter,2,sum_sal)) "2분기",
             max(decode(quarter,3,sum_sal)) "3분기",
             max(decode(quarter,4,sum_sal)) "4분기",
             max(decode(quarter,null,sum_sal)) "합계"
from (select to_char(hire_date,'yyyy') year, to_char(hire_date,'q') quarter, sum(salary) sum_sal
      from employees
      group by cube(to_char(hire_date,'yyyy'), to_char(hire_date,'q')))
group by year
order by 1;

select *
from(select year, nvl(quarter,0) quarter, sum_sal
     from (select to_char(hire_date,'yyyy') year, to_char(hire_date,'q') quarter, sum(salary) sum_sal
           from employees
           group by cube(to_char(hire_date,'yyyy'), to_char(hire_date,'q'))))
pivot(max(sum_sal) for quarter in (1 "1분기",2 "2분기",3 "3분기",4 "4분기",0 "합"))
order by 1;

[문제88] pivot을 이용해서 아래 화면과 같이 출력해주세요.
년도                   SA_REP               SH_CLERK             ST_CLERK             행의합
2003                     ￦0원                ￦0원            ￦7,100원            ￦7,100원
2004                ￦39,500원            ￦8,200원            ￦3,300원           ￦51,000원
2005                ￦74,800원           ￦15,400원           ￦18,100원          ￦108,300원
2006                ￦59,100원           ￦21,900원           ￦15,900원           ￦96,900원
2007                ￦38,200원           ￦13,400원            ￦6,900원           ￦58,500원
2008                ￦38,900원            ￦5,400원            ￦4,400원           ￦48,700원
열의합              ￦250,500원           ￦64,300원           ￦55,700원           ￦370,500원

select nvl(년도,'열의합') 년도, to_char(nvl(SA_REP,0),'l999,999')||'원' "SA_REP",
                              to_char(nvl(SH_CLERK,0),'l999,999')||'원' "SH_CLERK",
                              to_char(nvl(ST_CLERK,0),'l999,999')||'원' "ST_CLERK",
                              to_char(행의합,'l999,999')||'원' "행의합"
from(select 년도, nvl(job_id,'x') job, sum_sal
     from (select to_char(hire_date,'yyyy') 년도, job_id, sum(salary) sum_sal
           from employees
           where job_id in ('SA_REP','SH_CLERK', 'ST_CLERK')
           group by cube(to_char(hire_date,'yyyy'), job_id)))
pivot (max(sum_sal) for job in('SA_REP' "SA_REP",
                               'SH_CLERK' "SH_CLERK", 'ST_CLERK' "ST_CLERK", 'x' "행의합"))
order by 1;