{column:width=50%}

spool 2-40.lst

set timing off
set autotrace off
set linesize 1000
set pagesize 1000

prompt I. 특정 부서(AA)의 모든 입출금 내역 검색

prompt 0. 기존 테이블 삭제
drop table ACCOUNT cascade constraints purge;
drop table INPUT cascade constraints purge;
drop table OUTPUT cascade constraints purge;
drop table MONTH_DUAL cascade constraints purge;

prompt 1. 예적금원장 테이블 생성

CREATE TABLE ACCOUNT(
MANAGE_NUM VARCHAR2(10),
DEPT_COD VARCHAR2(10));

prompt 2. 예적금원장 테이블에 값 입력

INSERT INTO ACCOUNT VALUES('111','AA');
INSERT INTO ACCOUNT VALUES('222','AA');
INSERT INTO ACCOUNT VALUES('333','AA');
INSERT INTO ACCOUNT VALUES('444','BB');
INSERT INTO ACCOUNT VALUES('555','CC');

prompt 3. 입금내역 테이블 생성

CREATE TABLE INPUT(
MANAGE_NUM VARCHAR2(10),
INPUT_DATE VARCHAR2(8),
AMT NUMBER);

prompt 4. 입금내역 테이블에 값 입력

INSERT INTO INPUT VALUES('111','20060301',1000);
INSERT INTO INPUT VALUES('111','20060302',1000);
INSERT INTO INPUT VALUES('222','20060303',1000);
INSERT INTO INPUT VALUES('333','20060304',1000);
INSERT INTO INPUT VALUES('333','20060304',1000);
INSERT INTO INPUT VALUES('555','20060301',1000);
INSERT INTO INPUT VALUES('111','20060301',1000);
INSERT INTO INPUT VALUES('222','20060405',1000);
INSERT INTO INPUT VALUES('333','20060402',1000);

prompt 5. 출금내역 테이블 생성

CREATE TABLE OUTPUT(
MANAGE_NUM VARCHAR2(10),
OUTPUT_DATE VARCHAR2(8),
AMT NUMBER);

prompt 6. 출금내역 테이블 생성

INSERT INTO OUTPUT VALUES('111','20060301',1000);
INSERT INTO OUTPUT VALUES('111','20060302',1000);
INSERT INTO OUTPUT VALUES('111','20060303',1000);
INSERT INTO OUTPUT VALUES('222','20060301',1000);
INSERT INTO OUTPUT VALUES('444','20060301',1000);
INSERT INTO OUTPUT VALUES('555','20060301',1000);
INSERT INTO OUTPUT VALUES('111','20060401',1000);
INSERT INTO OUTPUT VALUES('222','20060305',1000);
INSERT INTO OUTPUT VALUES('333','20060402',1000);

prompt 7. 조인 이용한 입출금내역 조회

conn sys/loveora77 as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

conn scott/loveora77
set timing on
set autotrace on

select inp_date, sum(inp_amt), sum(out_amt)
from ACCOUNT a,
   (select MANAGE_NUM, substr(INPUT_DATE,1,6) inp_date,
           sum(AMT) inp_amt
    from INPUT
    where INPUT_DATE between '20060301' and '20060430'
    group by MANAGE_NUM, substr(INPUT_DATE,1,6)) b,
   (select MANAGE_NUM, substr(OUTPUT_DATE,1,6) out_date,
           sum(AMT) out_amt
    from OUTPUT
    where OUTPUT_DATE between '20060301' and '20060430'
    group by MANAGE_NUM, substr(OUTPUT_DATE,1,6)) c
where b.MANAGE_NUM = a.MANAGE_NUM
  and c.MANAGE_NUM(+) = b.MANAGE_NUM
  and c.out_date(+) = b.inp_date
  and a.DEPT_COD = 'AA'
group by inp_date;


prompt 2) 정상적인 결과를 얻기 위해서 양쪽 outer join을 수행 : 임시 집합 추가해서 해결

set timing off
set autotrace off

CREATE TABLE month_dual(
COL1 date,
yyyymm varchar2(8));

INSERT INTO month_dual VALUES(systimestamp,'20060301');
INSERT INTO month_dual VALUES(systimestamp,'20060302');
INSERT INTO month_dual VALUES(systimestamp,'20060303');
INSERT INTO month_dual VALUES(systimestamp,'20060304');
INSERT INTO month_dual VALUES(systimestamp,'20060305');
INSERT INTO month_dual VALUES(systimestamp,'20060306');
INSERT INTO month_dual VALUES(systimestamp,'20060307');
INSERT INTO month_dual VALUES(systimestamp,'20060401');
INSERT INTO month_dual VALUES(systimestamp,'20060402');
INSERT INTO month_dual VALUES(systimestamp,'20060403');
INSERT INTO month_dual VALUES(systimestamp,'20060404');
INSERT INTO month_dual VALUES(systimestamp,'20060405');


prompt ② 임시 집합(x) 추가

conn sys/loveora77 as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

conn scott/loveora77
set timing on
set autotrace on

select yyyymmdd, sum(inp_amt), sum(out_amt)
from (select MANAGE_NUM, substr(yyyymm,1,6) yyyymmdd
      from ACCOUNT a, MONTH_DUAL d
      where a.DEPT_COD = 'AA'
        and d.yyyymm between '20060301' and '20060430') x,
     (select MANAGE_NUM, substr(INPUT_DATE,1,6) inp_date, sum(AMT) inp_amt
      from INPUT
      where INPUT_DATE between '20060301' and '20060430'
      group by MANAGE_NUM, substr(INPUT_DATE,1,6)) b,
     (select MANAGE_NUM, substr(OUTPUT_DATE,1,6) out_date, sum(AMT) out_amt
      from OUTPUT
      where OUTPUT_DATE between '20060301' and '20060430'
      group by MANAGE_NUM, substr(OUTPUT_DATE,1,6)) c
where b.MANAGE_NUM(+) = x.MANAGE_NUM
  and b.inp_date(+) = x.yyyymmdd
  and c.MANAGE_NUM(+) = x.MANAGE_NUM
  and c.out_date(+) = x.yyyymmdd
group by yyyymmdd;


prompt 8. union과 group by 이용한 입출금내역 조회

conn sys/loveora77 as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

conn scott/loveora77
set timing on
set autotrace on

select yyyymm, nvl(sum(inp_amt),0), nvl(sum(out_amt),0)
from (select MANAGE_NUM, substr(INPUT_DATE,1,6) yyyymm,
             AMT inp_amt, to_number(null) out_amt
      from INPUT
      where INPUT_DATE between '20060301' and '20060430'
     union all
      select MANAGE_NUM, substr(OUTPUT_DATE,1,6) yyyymm,
             to_number(null) inp_amt, AMT out_amt
      from OUTPUT
      where OUTPUT_DATE between '20060301' and '20060430')
group by yyyymm;


prompt 5) 잘못된 사례1. 쿼리문을 단순하게 하기 위해서 where절의 공통조건을 인라인뷰 밖으로 보낸 경우

conn sys/loveora77 as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

conn scott/loveora77
set timing on
set autotrace on

select yyyymm, nvl(sum(inp_amt),0), nvl(sum(out_amt),0)
from (select MANAGE_NUM, substr(INPUT_DATE,1,6) yyyymm,
             AMT inp_amt, to_number(null) out_amt
    from INPUT
    union all
    select MANAGE_NUM, substr(OUTPUT_DATE,1,6) yyyymm,
           to_number(null) inp_amt, AMT out_amt
    from OUTPUT)
where yyyymm between substr('20060301',1,6) and substr('20060430',1,6)
group by yyyymm;


prompt 6) 잘못된 사례2. 형태가 다른 컬럼을 공통화시키기 위해 가공화

conn sys/loveora77 as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

conn scott/loveora77
set timing on
set autotrace on

select substr(yyyymm,1,6), nvl(sum(inp_amt),0), nvl(sum(out_amt),0)
from (select MANAGE_NUM, INPUT_DATE yyyymm,
             AMT inp_amt, to_number(null) out_amt
      from INPUT
      union all
      select MANAGE_NUM, OUTPUT_DATE yyyymm,
             to_number(null) inp_amt, AMT out_amt
      from OUTPUT)
where yyyymm between substr('20060301',1,6) and substr('20060430',1,6)
group by substr(yyyymm,1,6);



spool off

{column}