[교육] SQL초보에서실전전문가까지 ORACLE - 5

• 서브쿼리

-- 자신이 자신을 참조하는 형태일 경우

-- 이거시 더 실행계획이 좋음

select ename, sal

from emp a

where sal > (select avg(sal) from emp where deptno=a.deptno group by deptno);

-- 집합적으로 만들어봤으나 효율은 그렇게 좋지 않음.

select ename, sal

from emp a

where exists     (

        select 1 

        from (select deptno, avg(sal) avgsal from emp group by deptno) z

        where a.deptno=z.deptno and a.sal > z.avgsal

        );

-- 검증용 쿼리

select deptno, avg(sal) avgsal from emp group by deptno;

• INSERT ALL

-- INSERT ALL SAMPLES.......

create table sal (

ename   varchar2(10)

,sal     number(7,2)

);

create table deptno (

ename   varchar2(10)

,deptno number(2)

);

select * from sal;

select * from deptno;

-- no rows

insert ALL

into sal values ( ename, sal )

into deptno values ( ename, deptno )

select ename, sal, deptno -- '*' 로 해도 상관없음 

from emp

where deptno = 10;

-- 8 rows created.

select * from sal;

--4 rows selected.

select * from deptno;

--4 rows selected.

• Conditional INSERT ALL

-- 일단 테이블 삭제해주고,

delete from sal;

delete from deptno;

emp 기준으로 sal 이나 deptno  둘 다   when 조건에 따라 필터를 더해준다고 생각하면 됨.

insert ALL

when sal > 2000 then -- 조건 추가

    into sal values ( ename, sal )

when comm is not null then  -- 조건 추가

    into deptno values ( ename, deptno )

select ename, sal, deptno, comm -- '*' 로 해도 상관없음 

from emp

where deptno = 10;

-- 4 rows created.

select * from sal;

ENAME             SAL

---------- ----------

CLARK            2450

KING             5000

ktMINname        9000

3 rows selected.

select * from deptno;

ENAME          DEPTNO

---------- ----------

ktMINname          10

1 row selected.

• Conditional INSERT FIRST

delete from sal;

delete from deptno;

첫 번째 조건에 먼저 넣은 것은 두 번째 조건에는 적용하지 않는 다는 점이 차이점임.

insert FIRST

when sal > 2000 then

    into sal values ( ename, sal )

when comm is not null then 

    into deptno values ( ename, deptno )

select ename, sal, deptno, comm -- '*' 로 해도 상관없음 

from emp

where deptno = 10;

-- 3 rows created.

select * from sal;

select * from deptno;

• 다중행 입력 - PIVOTING INSERT

-- PIVOT Samples

create table sales (

sales_no    number(4),

week_no     number(2),

sales_mon   number(7,2),

sales_tue   number(7,2),

sales_wed   number(7,2),

sales_thu   number(7,2),

sales_fri   number(7,2)

);

insert into sales values (1101, 4, 100, 150, 80, 60, 120);

insert into sales values (1101, 5, 300, 300, 230, 120, 150);

create table sales_data (

sale_no number(4),

week_no number(2),

day_no  number(2),

sales   number(7,2)

);

insert ALL

into sales_data values ( sales_no, week_no, '1', sales_mon)

into sales_data values ( sales_no, week_no, '2', sales_tue)

into sales_data values ( sales_no, week_no, '3', sales_wed)

into sales_data values ( sales_no, week_no, '4', sales_thu)

into sales_data values ( sales_no, week_no, '5', sales_fri)

select sales_no, week_no, sales_mon, sales_tue, sales_wed, sales_thu, sales_fri

from sales;

select * from sales_data;

-- UPDATE Samples..

update emp

set ename = 'BOND'

where empno = 1144;

select * from emp;