SQL 서브쿼리, 고급 쿼리문, 집합연산자

• 서브쿼리 종류

1. 단일행 서브쿼리 : 서브쿼리에서 메인쿼리로 하나의 값이 리턴되는 경우
2. 다중행 서브쿼리 : 서브쿼리에서 메인쿼리로 여러개의 값이 리턴되는 경우
3. 다중 컬럼 서브쿼리

---

• 다중 컬럼 서브쿼리 (multiple column subquery)

1.  Non pairwise 방식

   -> select ename, sal, comm, deptno

       from emp

       where sal in (select sal from emp where deptno = 30) and

       where comm in (select comm from emp);

   

   2. Pairwise 방식

   -> select ename, sal, comm, deptno

       from emp

       where (sal, comm) in (select sal, comm from emp where deptno = 30); 

 

Non-pairwise와 pairwise의 결과는 같으나 데이터 정렬이 다른데 그 이유는 실행계획이 다르기 때문이다.

 

emp 테이블의 king의 데이터를 변경해보고, 위의 두 가지 방식을 비교해보자.

• king의 월급을 1250으로,  comm 을 500으로 변경

      ->  Update emp set sal = 1250, comm =300 Where ename = 'KING';

 

Non pair

Pair

---

• 집합 연산자

   : 조인은 서로 다른 테이블의 컬럼을 양 옆으로 출력하는 문법

  집합 연산자는 서로 다른 테이블의 컬럼을 위아래로 출력하는 문법

1. 합집합 연산자 : union, union all
2. 교집합 연산자 : intersect
3. 차집합 연산자 : minus

   ex) select job, sum(sal)

       from emp

       group by job

       union all

       select '토탈월급:', sum(sal) from emp;

 

집합 연산자 사용시 주의사항

1. 집합 연산자 위 아래의 쿼리의 컬럼의 갯수가 동일해야 한다.

   위의 쿼리의 컬럼의 갯수가 2개면 아래도 2개여야한다.

 

2. 집합 연산자 위 아래 쿼리의 컬럼의 데이터 타입이 동일해야한다.

   위의 쿼리의 컬럼의 데이터 타입이 문자형이면 아래 같은 순서의 컬럼의 데이터 타입도 문자형이어야한다.

 

3.컬럼별칭은 맨 위의 쿼리에 있는 별칭만 화면에 표시된다.

  Order by 절은 맨 아래의 쿼리에만 사용할 수 있다.

 

 ex) select job as 직업, sum(sal) as 토탈

      from emp

      group by job

      union all

      select '토탈월급:' as 직업, sum(sal) from emp

      order by 토탈 desc;

 

 

• union과 union all의 차이

      union은 union all과 같은 합집합 연산자이지만,

      차이점은 중복데이터를 제거하고, 정렬된 상태로 출력을 한다.

 

     비교를 위해 아래의 데이터를 삽입하자.

   

     create table emp01 (empno number(10));

     create table emp02 (empno number(10));

 

     insert into emp01 values(1);

     insert into emp01 values(2);

     insert into emp01 values(3);

     insert into emp01 values(4);

 

     insert into emp02 values(3);

     insert into emp02 values(4);

     insert into emp02 values(5);

     insert into emp02 values(6);

     commit;

 

     Ex) select empno from emp01

          union all

          select empno from emp02;

   

   

     Ex) select empno from emp01

         union

         select empno from emp02;

       

 

 

• Intersect 연산자

: 두 집합의 교집합을 출력하는 함수

 

Ex) select empno from emp01

    intersect

    select empno from emp02;

 

 

• Minus 연산자

: 두집합 간의 차이를 출력하는 집합 연산자

 

Ex) select empno from emp01

     minus

     select empno

     from emp02;

 

---

• 고급 쿼리문 함수들

• Reportin 함수

1. Rollup
2. Cube
3. Grouping sets
4. Grouping

 

• 레포팅 함수(reporting 함수)

1. Rollup
2. Cube

union은 테이블을 합집합시킨다는 개념으로 이해하면 좋다.

그러나 같은 테이블도 두 번 참조해야하기때문에 좋은 명령어는 아니다.

이를 튜닝할 때 사용할 수 있는 Rollup에 대해 알아보자

Ex) Select nvl(to_char(hiredate,'RRRR'),'전체') as 년도, count(*) as 인원수

     From emp

    Group by rollup (to_char(hiredate,'RRRR'));

   

   이전 sql에서 살펴본 union 사용 sql의 길이와 확연하게 차이를 나는 것을 볼 수가 있으며

   테이블 참조도 한 번으로 할 수가 있다.

 

• Grouping sets

: 집계할 컬럼을 직접 선택해서 집계할 수 있게 해주는 레포팅 함수

 

select deptno, sum(sal) from emp
group by grouping sets((deptno),());

 

(deptno) -> 부서번호 별로 집계

  () -> 전체데이터를 가지고 집계해라

---

- 서브쿼리 문제

 

문제 231. 30번 부서번호인 사원들과 월급도 같고 커미션도 같은

             사원들의 이름과 월급과 커미션과 부서번호를 출력

-> select ename, sal, comm, deptno

    from emp

    where sal in (select sal from emp where deptno = 30) and

    comm in (select comm from emp);

 

문제 232. 직업이 salesman인 사원들과 월급이 같고,

              커미션이 같은 사원들의 이름과 월급과 커미션을 출력하는데 pairwise방식으로 수행하세요

-> select ename, sal, comm from emp

    where (sal, comm) in (select sal, comm from emp where job='SALESMAN');

 

문제 233. 직업이 salesman인 사원들과 월급과 커미션이 같지 않은 사원들의

             이름과 월급과 커미션 출력

-> select ename, job, comm from emp

    where (sal, nvl(comm,-1)) not in (select sal,nvl(comm,-1) from emp where job = 'SALESMAN');

 

문제 238. 부서번호, 부서번호별 토탈 월급 출력

             맨 아래에 전체 토탈월급 출력

-> select deptno as 부서번호, sum(sal) as 월급

    from emp

    group by deptno

    union all

    select '토탈월급:' as 부서번호, sum(sal) from emp

    order by 월급 asc;

문제 239. 입사한년도 (4자리), 입사한 년도별 인원수 출력

-> select to_char(hiredate,'RRRR'), count(empno)

    from emp

    group by to_char(hiredate,'RRRR');

문제 240. 년도별 인원수와 총인원수를 출력하세요

-> select to_char('hiredate','RRRR') 전체인원, count(*) 인원수

    from emp

    group by to_char(hiredate,'RRRR')

    union all

    select '총인원:' 전체인원, count(*) 인원수

    from emp;

문제 242. 부서번호, 부서번호 별 토탈월급을 출력하는데 맨 아래쪽에 전체 토탈 월급을 출력하세요

-> select nvl(to_char(deptno),'총월급'), sum(sal)

    from emp

    group by rollup(deptno); 

 

문제 244. union all을 사용해서 전체 월급 구하기

-> select job, sum(sal)

    from emp

    group by job

    union all

    select job, sum(sal)

    from emp;

 

   

   Select '토탈월급:' 직업, sum(sal)

   From emp

   Union all

   Select job 직업, sum(sal) 월급

   From emp

   Group by job;

문제 245. 위의 문제를 cube를 사용해서 출력

Select job, sum(sal)

From emp

Group by cube(job);

문제 246. 아래의 결과를 cube를 이용하지 말고 union이나 union all을 이용해서

             출력하세요

 

Select deptno, sum(sal)

From emp

Group by cube(deptno);

 

-> select null as deptno, sum(sal)

    from emp

    union all

    select deptno, sum(sal)

    from emp

    group by deptno

    order by deptno asc nulls first;

 

@ order by 사용할 때 null의 위치를 제어하려면

1. Nulls first
2. Nulls last

 

문제 247. union all 을 rollup으로 구현해보시오

select to_char(deptno) 부서번호 ,round(avg(sal)) 평균월급

from emp

group by deptno

union all

Select '전체평균:' 부서번호 ,round(avg(sal)) 평균월급

From emp

order by 부서번호;

 

-> select nvl(to_char(deptno),'전체평균') 부서번호, round(avg(sal))
    from emp
    group by rollup(deptno);

 

 

 

문제 249. 아래의 sql을 grouping sets로 구현하세요

-> select null as deptno, job, sum(sal)

    from emp

    group by job

    union all

    select deptno, null as job, sum(sal)

    from emp

    group by deptno;

 

-> select deptno, job, sum(sal)

    from emp

    group by grouping sets((deptno),(job));

 

문제 250. 아래의 union 절을 grouping sets로 구현하세요

select null as deptno, job, sum(sal)

from emp

group by job

union all

select deptno, null as job, sum(sal)

from emp

group by deptno

union all

select null as dpetno, null as job, sum(sal)

from emp;

-> select deptno, job, sum(sal)
    from emp
    group by grouping sets ((deptno),(job),(null));

 

Null ->

select null as deptno, null as job, sum(sal)

from emp

group by null;

 

 

문제 251. 부서번호, 직업, 부서번호별 직업별 토탈월급 출력

Select deptno, job, sum(sal)

From emp

Group by deptno, job

Order by deptno, job;

@ select 절에 그룹함수와 함께 일반 컬럼을 2개를 사용하였으면

   group by 절에 반드시 명시해서 grouping을 해줘야 수행된다.

 

문제 252. 위의 문제를 grouping sets를 통해 구현하세요

-> select deptno, job, sum(sal)
    from emp
    group by grouping sets ((deptno, job))
    order by deptno, job;

 

 

문제 253. 아래의 sql을 grouping sets로 수행하세요

Select deptno, job, sum(sal) from emp

Group by deptno, job

Union

Select deptno, null as job, sum(sal)

From emp

Group by deptno;

 

-> select deptno, job,sum(sal) from emp

    group by grouping sets ((deptno, job),(deptno))

    order by deptno, job;

 

문제 254. 아래의 결과를 grouping sets로 구현하세요

Select to_char(hiredate,'RRRR'), deptno, count(*)

From emp

Group by to_char(hiredate,'RRRR"), deptno

Union

Select to_char(hiredate,'RRRR'), null as deptno, count(*)

From emp

Group by to_char(hiredate,'RRRR")

 

-> Select to_char(hiredate,'RRRR'), deptno, count(*)

    From emp

    Group by grouping sets((to_char(hiredate,'RRRR'),deptno),(to_char(hiredate,'RRRR')))