이 스크립트는 {*}김강환{*}님께서 제공해주셨습니다.
{column:width=50%}
spool 2-48.log
set timing off
set autotrace off
set linesize 1000
set pagesize 1000
prompt 0. 기존 테이블 삭제
drop table SALE_SUMM2 cascade constraints purge;
drop table SALE_WONGA cascade constraints purge;
drop table SLIP_SUMM cascade constraints purge;
prompt 1. 테이블스키마
prompt 1) 매출집계 테이블
CREATE TABLE SALE_SUMM2 (
YYMM VARCHAR2 (6),
AMT NUMBER (9) );
INSERT INTO SALE_SUMM2 VALUES ('199701', 46300);
INSERT INTO SALE_SUMM2 VALUES ('199702', 32820);
INSERT INTO SALE_SUMM2 VALUES ('199703', 54620);
INSERT INTO SALE_SUMM2 VALUES ('199704', 48580);
INSERT INTO SALE_SUMM2 VALUES ('199705', 63720);
INSERT INTO SALE_SUMM2 VALUES ('199706', 57560);
INSERT INTO SALE_SUMM2 VALUES ('199707', 46300);
INSERT INTO SALE_SUMM2 VALUES ('199708', 32820);
INSERT INTO SALE_SUMM2 VALUES ('199709', 54620);
INSERT INTO SALE_SUMM2 VALUES ('199710', 48580);
INSERT INTO SALE_SUMM2 VALUES ('199711', 82430);
INSERT INTO SALE_SUMM2 VALUES ('199712', 157560);
prompt 2) 매출원가 테이블
CREATE TABLE SALE_WONGA (
YYMM VARCHAR2 (6),
AMT NUMBER (9));
INSERT INTO SALE_WONGA VALUES ('199701', 38172);
INSERT INTO SALE_WONGA VALUES ('199702', 23814);
INSERT INTO SALE_WONGA VALUES ('199703', 43802);
INSERT INTO SALE_WONGA VALUES ('199704', 36510);
INSERT INTO SALE_WONGA VALUES ('199705', 50694);
INSERT INTO SALE_WONGA VALUES ('199706', 42552);
INSERT INTO SALE_WONGA VALUES ('199707', 38172);
INSERT INTO SALE_WONGA VALUES ('199708', 23814);
INSERT INTO SALE_WONGA VALUES ('199709', 43802);
INSERT INTO SALE_WONGA VALUES ('199710', 36510);
INSERT INTO SALE_WONGA VALUES ('199711', 65156);
INSERT INTO SALE_WONGA VALUES ('199712', 142552);
prompt 3)계정별집계 테이블
CREATE TABLE SLIP_SUMM (
YYMM VARCHAR2 (6),
ACCOUNT VARCHAR2 (3),
AMT NUMBER (9) );
INSERT INTO SLIP_SUMM VALUES ('199701', '211', 969) ;
INSERT INTO SLIP_SUMM VALUES ('199701', '212', 872) ;
INSERT INTO SLIP_SUMM VALUES ('199701', '213', 415) ;
INSERT INTO SLIP_SUMM VALUES ('199701', '214', 160) ;
INSERT INTO SLIP_SUMM VALUES ('199701', '215', 289) ;
INSERT INTO SLIP_SUMM VALUES ('199701', '221', 358) ;
INSERT INTO SLIP_SUMM VALUES ('199701', '222', 97) ;
INSERT INTO SLIP_SUMM VALUES ('199701', '229', 265) ;
INSERT INTO SLIP_SUMM VALUES ('199702', '211', 1150) ;
INSERT INTO SLIP_SUMM VALUES ('199702', '212', 786) ;
INSERT INTO SLIP_SUMM VALUES ('199702', '213', 545) ;
INSERT INTO SLIP_SUMM VALUES ('199702', '214', 80) ;
INSERT INTO SLIP_SUMM VALUES ('199702', '215', 142) ;
INSERT INTO SLIP_SUMM VALUES ('199702', '221', 280) ;
INSERT INTO SLIP_SUMM VALUES ('199702', '222', 34) ;
INSERT INTO SLIP_SUMM VALUES ('199702', '229', 182) ;
INSERT INTO SLIP_SUMM VALUES ('199703', '211', 1280) ;
INSERT INTO SLIP_SUMM VALUES ('199703', '212', 1045) ;
INSERT INTO SLIP_SUMM VALUES ('199703', '213', 674) ;
INSERT INTO SLIP_SUMM VALUES ('199703', '214', 120) ;
INSERT INTO SLIP_SUMM VALUES ('199703', '215', 381) ;
INSERT INTO SLIP_SUMM VALUES ('199703', '221', 492) ;
INSERT INTO SLIP_SUMM VALUES ('199703', '222', 68) ;
INSERT INTO SLIP_SUMM VALUES ('199703', '229', 416) ;
INSERT INTO SLIP_SUMM VALUES ('199704', '211', 1060) ;
INSERT INTO SLIP_SUMM VALUES ('199704', '212', 840) ;
INSERT INTO SLIP_SUMM VALUES ('199704', '213', 715) ;
INSERT INTO SLIP_SUMM VALUES ('199704', '214', 280) ;
INSERT INTO SLIP_SUMM VALUES ('199704', '215', 478) ;
INSERT INTO SLIP_SUMM VALUES ('199704', '221', 586) ;
INSERT INTO SLIP_SUMM VALUES ('199704', '222', 35) ;
INSERT INTO SLIP_SUMM VALUES ('199704', '229', 523) ;
INSERT INTO SLIP_SUMM VALUES ('199705', '211', 1230) ;
INSERT INTO SLIP_SUMM VALUES ('199705', '212', 922) ;
INSERT INTO SLIP_SUMM VALUES ('199705', '213', 508) ;
INSERT INTO SLIP_SUMM VALUES ('199705', '214', 220) ;
INSERT INTO SLIP_SUMM VALUES ('199705', '215', 512) ;
INSERT INTO SLIP_SUMM VALUES ('199705', '221', 664) ;
INSERT INTO SLIP_SUMM VALUES ('199705', '222', 52) ;
INSERT INTO SLIP_SUMM VALUES ('199705', '229', 712) ;
INSERT INTO SLIP_SUMM VALUES ('199706', '211', 1149) ;
INSERT INTO SLIP_SUMM VALUES ('199706', '212', 1260) ;
INSERT INTO SLIP_SUMM VALUES ('199706', '213', 620) ;
INSERT INTO SLIP_SUMM VALUES ('199706', '214', 110) ;
INSERT INTO SLIP_SUMM VALUES ('199706', '215', 259) ;
INSERT INTO SLIP_SUMM VALUES ('199706', '221', 433) ;
INSERT INTO SLIP_SUMM VALUES ('199706', '222', 80) ;
INSERT INTO SLIP_SUMM VALUES ('199706', '229', 849) ;
INSERT INTO SLIP_SUMM VALUES ('199707', '211', 969) ;
INSERT INTO SLIP_SUMM VALUES ('199707', '212', 872) ;
INSERT INTO SLIP_SUMM VALUES ('199707', '213', 215) ;
INSERT INTO SLIP_SUMM VALUES ('199707', '214', 60) ;
INSERT INTO SLIP_SUMM VALUES ('199707', '215', 189) ;
INSERT INTO SLIP_SUMM VALUES ('199707', '221', 358) ;
INSERT INTO SLIP_SUMM VALUES ('199707', '222', 70) ;
INSERT INTO SLIP_SUMM VALUES ('199707', '229', 2265) ;
INSERT INTO SLIP_SUMM VALUES ('199708', '211', 1150) ;
INSERT INTO SLIP_SUMM VALUES ('199708', '212', 786) ;
INSERT INTO SLIP_SUMM VALUES ('199708', '213', 345) ;
INSERT INTO SLIP_SUMM VALUES ('199708', '214', 80) ;
INSERT INTO SLIP_SUMM VALUES ('199708', '215', 42) ;
INSERT INTO SLIP_SUMM VALUES ('199708', '221', 280) ;
INSERT INTO SLIP_SUMM VALUES ('199708', '222', 24) ;
INSERT INTO SLIP_SUMM VALUES ('199708', '229', 2282) ;
INSERT INTO SLIP_SUMM VALUES ('199709', '211', 1280) ;
INSERT INTO SLIP_SUMM VALUES ('199709', '212', 1045) ;
INSERT INTO SLIP_SUMM VALUES ('199709', '213', 574) ;
INSERT INTO SLIP_SUMM VALUES ('199709', '214', 120) ;
INSERT INTO SLIP_SUMM VALUES ('199709', '215', 281) ;
INSERT INTO SLIP_SUMM VALUES ('199709', '221', 492) ;
INSERT INTO SLIP_SUMM VALUES ('199709', '222', 48) ;
INSERT INTO SLIP_SUMM VALUES ('199709', '229', 1542) ;
INSERT INTO SLIP_SUMM VALUES ('199710', '211', 1060) ;
INSERT INTO SLIP_SUMM VALUES ('199710', '212', 840) ;
INSERT INTO SLIP_SUMM VALUES ('199710', '213', 615) ;
INSERT INTO SLIP_SUMM VALUES ('199710', '214', 180) ;
INSERT INTO SLIP_SUMM VALUES ('199710', '215', 378) ;
INSERT INTO SLIP_SUMM VALUES ('199710', '221', 586) ;
INSERT INTO SLIP_SUMM VALUES ('199710', '222', 35) ;
INSERT INTO SLIP_SUMM VALUES ('199710', '229', 1523) ;
INSERT INTO SLIP_SUMM VALUES ('199711', '211', 2004) ;
INSERT INTO SLIP_SUMM VALUES ('199711', '212', 452) ;
INSERT INTO SLIP_SUMM VALUES ('199711', '213', 401) ;
INSERT INTO SLIP_SUMM VALUES ('199711', '214', 172) ;
INSERT INTO SLIP_SUMM VALUES ('199711', '215', 312) ;
INSERT INTO SLIP_SUMM VALUES ('199711', '221', 733) ;
INSERT INTO SLIP_SUMM VALUES ('199711', '222', 45) ;
INSERT INTO SLIP_SUMM VALUES ('199711', '229', 1712) ;
INSERT INTO SLIP_SUMM VALUES ('199712', '211', 1149) ;
INSERT INTO SLIP_SUMM VALUES ('199712', '212', 1260) ;
INSERT INTO SLIP_SUMM VALUES ('199712', '213', 520) ;
INSERT INTO SLIP_SUMM VALUES ('199712', '214', 110) ;
INSERT INTO SLIP_SUMM VALUES ('199712', '215', 157) ;
INSERT INTO SLIP_SUMM VALUES ('199712', '221', 433) ;
INSERT INTO SLIP_SUMM VALUES ('199712', '222', 68) ;
INSERT INTO SLIP_SUMM VALUES ('199712', '229', 11849) ;
prompt 3.쿼리문
prompt 교재의 쿼리
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 NO
,SUM(TOT*DECODE(NO-LINE,1,-1,3,-1,1) ) TOT
,SUM(W01*DECODE(NO-LINE,1,-1,3,-1,1)) "1 월"
,SUM(W02*DECODE(NO-LINE,1,-1,3,-1,1)) "2 월"
,SUM(W03*DECODE(NO-LINE,1,-1,3,-1,1)) "3 월"
,SUM(W04*DECODE(NO-LINE,1,-1,3,-1,1)) "4 월"
,SUM(W05*DECODE(NO-LINE,1,-1,3,-1,1)) "5 월"
,SUM(W06*DECODE(NO-LINE,1,-1,3,-1,1)) "6 월"
,SUM(W01*DECODE(NO-LINE,1,-1,3,-1,1)) "7 월"
,SUM(W02*DECODE(NO-LINE,1,-1,3,-1,1)) "8 월"
,SUM(W03*DECODE(NO-LINE,1,-1,3,-1,1)) "9 월"
,SUM(W04*DECODE(NO-LINE,1,-1,3,-1,1)) "10 월"
,SUM(W05*DECODE(NO-LINE,1,-1,3,-1,1)) "11 월"
,SUM(W06*DECODE(NO-LINE,1,-1,3,-1,1)) "12 월"
FROM (SELECT LINE, SUM(AMT) TOT,
SUM(DECODE(MM,'01',AMT)) W01, SUM(DECODE(MM,'02',AMT)) W02,
SUM(DECODE(MM,'03',AMT)) W03, SUM(DECODE(MM,'04',AMT)) W04,
SUM(DECODE(MM,'05',AMT)) W05, SUM(DECODE(MM,'06',AMT)) W06,
SUM(DECODE(MM,'07',AMT)) W07, SUM(DECODE(MM,'08',AMT)) W08,
SUM(DECODE(MM,'09',AMT)) W09, SUM(DECODE(MM,'04',AMT)) W10,
SUM(DECODE(MM,'11',AMT)) W11, SUM(DECODE(MM,'06',AMT)) W12
FROM (SELECT Y.NO LINE, MM, SUM(AMT*DECODE(Y.NO*LINE,6,-1,1) ) AMT
FROM (SELECT '1' LINE, SUBSTR(S2.YYMM ,5,2) MM, SUM(AMT) AMT
FROM SALE_SUMM2 S2
WHERE S2.YYMM LIKE '1997%'
GROUP BY SUBSTR(S2.YYMM,5,2)
UNION ALL
SELECT '2' LINE, SUBSTR(SW.YYMM ,5,2) MM, SUM(AMT) AMT
FROM SALE_WONGA SW
WHERE SW.YYMM LIKE '1997%'
GROUP BY SUBSTR(SW.YYMM ,5,2)) X, COPY_T Y
WHERE Y.NO IN (LINE, 3)
AND Y.NO <= 14
GROUP BY Y.NO, MM
UNION ALL
SELECT Y.NO LINE, MM, SUM(AMT*DECODE(Y.NO*LINE,88,-1,1) ) AMT
FROM (SELECT DECODE(SUBSTR(SL.ACCOUNT ,1,3),'211',4, '212',5,
'213',6, '214',7, '215',8, '221',9, '222',10, 13) LINE,
SUBSTR(SL.YYMM,5,2) MM, SUM(AMT) AMT
FROM SLIP_SUMM SL
WHERE SL.YYMM LIKE '1997%'
AND SL.ACCOUNT BETWEEN '211' AND '229'
GROUP BY DECODE(SUBSTR(SL.ACCOUNT,1,3),'211',4, '212',5,
'213',6, '214',7, '215',8, '221',9, '222',10, 13),
SUBSTR(SL.YYMM,5,2)) X, COPY_T Y
WHERE Y.NO IN ( LINE,DECODE(LINE,13,NULL,11) ) AND Y.NO <=14
GROUP BY Y.NO, MM)
GROUP BY LINE) X, COPY_T Y
WHERE Y.NO IN (LINE, DECODE(LINE,3,12, 11,12), DECODE(LINE,3,14,11,14,13,14))
AND Y.NO <= 14
GROUP BY Y.NO;
spool off
{column}