귤귤의 대모험

1. SALE_HIST의 자료로 일자별 품목별로 ‘01’, ‘02’ 사업장 판매 금액합계, ‘03’, ‘04’ 사업장 판매 금액 합을 구하시오

SELECT SAEL_DATE,

                 SUM(CASE WHEN SALE_SITE BETWEEN ‘01’ AND ‘02’ THEN SALE_AMT END) AS S01

               , SUM(CASE WHEN SALE_SITE BETWEEN ‘03’ AND ‘04’ THEN SALE_AMT END) AS S02

FROM SALE_HIST

GROUP BY SALE_DATE

2. TEMP의 자료를 이용해 한 행에 5명의 사번과 성명을 보여주는 QUERY를 작성해 보라.

SELECT CEIL(ROWNUM/5) C0

,MAX(DECODE(MOD(ROWNUM, 5), 1, EMP_ID, NULL)) C1

,MAX(DECODE(MOD(ROWNUM, 5), 1, EMP_NAME, NULL)) C2

,MAX(DECODE(MOD(ROWNUM, 5), 2, EMP_ID, NULL)) C3

,MAX(DECODE(MOD(ROWNUM, 5), 2, EMP_NAME, NULL)) C4

,MAX(DECODE(MOD(ROWNUM, 5), 3, EMP_ID, NULL)) C5

,MAX(DECODE(MOD(ROWNUM, 5), 3, EMP_NAME, NULL)) C6

,MAX(DECODE(MOD(ROWNUM, 5), 4, EMP_ID, NULL)) C7

,MAX(DECODE(MOD(ROWNUM, 5), 4, EMP_NAME, NULL)) C8

,MAX(DECODE(MOD(ROWNUM, 5), 0, EMP_ID, NULL)) C9

,MAX(DECODE(MOD(ROWNUM, 5), 0, EMP_NAME, NULL)) C10

FROM TEMP

GROUP BY CEIL(ROWNUM/5)

3. TEST14와 TEST13을 이용하여 Cartesian Product을 이용하여 테이블을 조인하시오

SELECT *

FROM TEST14 , TEST13

4. 고객이 어떤 해당 상품을 받아야 하는지 TEST14와 TEST13을 이용하여 고객과 상품LIST를 출력하시오

두 테이블은 POINT에 관련이 있다

SELECT A.CUST, B.GIFT

FROM TEST14 A, TEST13 B

WHERE A.POINT > 0

AND A.POINT BETWEEN B.FPOINT AND B.TPOINT

5. TEST14와 TEST13을 이용하여 상품별로 준비되어야 할 개수를 알아보는 문장을 작성 하시오

SELECT B.GIFT, COUNT(A.CUST)

FROM TEST14 A, TEST13 B

WHERE A.POINT > 0

AND A.POINT BETWEEN B.FPOINT AND B.TPOINT

GROUP BY B.GIFT

6.고객이 가진 포인트보다 하한금액이 낮은 모든 등급의 상품을 선택할 수 있다고 할 때 갈비세트를 상품으로 받을 수 있는 고객의 고객번호, 포인트, 선물을 보여주는 QUERY를 만들어보라

SELECT *

FROM TEST14 A, TEST13 B

WHERE B.FPOINT <=  A.POINT

AND B.GIFT ='갈비세트’

CREATE TABLE LOGTABLE(USERID VARCHAR(10), STIME VARCHAR(5), ETIME VARCHAR(5));

INSERT INTO LOGTABLE

SELECT '사용자1', '07:00', '11:00'

SELECT '사용자2', '10:00', '15:00'

SELECT '사용자3', '11:00', '12:00';

CREATE TABLE SETIME(STIME VARCHAR(5), ETIME VARCHAR(5));

INSERT INTO SETIME VALUES('00:00', '01:00');

INSERT INTO SETIME VALUES('01:00', '02:00');

INSERT INTO SETIME VALUES('02:00', '03:00');

INSERT INTO SETIME VALUES('03:00', '04:00');

INSERT INTO SETIME VALUES('04:00', '05:00');

INSERT INTO SETIME VALUES('05:00', '06:00');

INSERT INTO SETIME VALUES('06:00', '07:00');

INSERT INTO SETIME VALUES('07:00', '08:00');

INSERT INTO SETIME VALUES('08:00', '09:00');

INSERT INTO SETIME VALUES('09:00', '10:00');

INSERT INTO SETIME VALUES('10:00', '11:00');

INSERT INTO SETIME VALUES('11:00', '12:00');

INSERT INTO SETIME VALUES('12:00', '13:00');

INSERT INTO SETIME VALUES('13:00', '14:00');

INSERT INTO SETIME VALUES('14:00', '15:00');

INSERT INTO SETIME VALUES('15:00', '16:00');

INSERT INTO SETIME VALUES('16:00', '17:00');

INSERT INTO SETIME VALUES('17:00', '18:00');

INSERT INTO SETIME VALUES('18:00', '19:00');

INSERT INTO SETIME VALUES('19:00', '20:00');

INSERT INTO SETIME VALUES('20:00', '21:00');

INSERT INTO SETIME VALUES('21:00', '22:00');

INSERT INTO SETIME VALUES('22:00', '23:00');

INSERT INTO SETIME VALUES('23:00', '24:00');

7. 위의 구문을 INSERT하고 출퇴근 시간대별로 몇 사람이 출퇴근했는지 사람 수와 출퇴근 시간을 출력하여라

SELECT S.STIME || '-' || S.ETIME, COUNT(*)

FROM LOGTABLE L, SETIME S

WHERE L.STIME < S.ETIME AND L.ETIME >= S.STIME

GROUP BY S.STIME || '-' || S.ETIME

ORDER BY S.STIME || '-' || S.ETIME

TEST34 활용 문제 8, 9

INSERT INTO TEST34 VALUE ('A10',4,100);

INSERT INTO TEST34 VALUE ('A11',4,110);

COMMIT;

8. 위 구문을 인설트하고 KEY_TYPE이 ‘4’인 경우에 ‘1’, ‘2’, ‘3’ 에 모두 더하고 ‘1’의 합과 ‘2’, ‘3’의 합을 구해라

선택적 조인문과 DECODE를 활용하시오

SELECT T2.R_CNT, SUM(AMT) AMT

FROM TEST34 T1, (SELECT ROWNUM R_CNT

                                       FROM TEST34

                                       WHERE ROWNUM<4) T2

WHERE T1.KEY_TYPE = DECODE(T1.KEY_TYPE, 4, T1.KEY_TYPE, T2.R_CNT)

GROUP BY T2.R_CNT;

key type amt rcnt

A01 1 10 1 true

A03 1 30 1 tur

A07 1 70 1

A09 1 90 1

A10 4 100 1 true

A11 4 110 1

410

A02 2 20 2

A04 2 40 2

A08 2 80 2

A10 4 100 2

A11 4 110 2

350

A05 3 50 3

A06 3 60 3

A10 4 100 3

A11 4 110 3

320

9. 위 구문을 인설트하고 KEY_TYPE이 ‘4’인 경우에 ‘1’, ‘2’, ‘3’ 에 모두 더하고 ‘1’, ‘2’, ‘3’의 합을 구해라

선택적 조인문과 윈도우 함수를 활용하시오 LAG()

SELECT *

FROM (SELECT DECODE(KEY_TYPE,1,'A',2,'B',3,'C') AS KEY

               , SUM(AMT) 

                 + LAG(SUM(AMT), DECODE(KEY_TYPE,1,1,2,2,3,3)) OVER (ORDER BY DECODE(KEY_TYPE,1,'A',2,'B',3,'C')

                                                                                                                                      NULLS FIRST) AS AMT

               FROM TEST34

              GROUP BY KEY_TYPE

              )

WHERE KEY IS NOT NULL

27 사번, 이름, 부서번호, 급여를 출력하고 분석함수를 사용한 컬럼에 해당 사원과 이전 사원 급여액 합계를 구하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

          ,SUM(SALARY) OVER (ORDER BY SALARY

                                       ROWS 1 PRECEDING ) AS 이전사원급여합계

FROM TEMP

28, 사번, 이름, 부서번호, 급여를 출력하고 분석함수를 사용한 컬럼에 해당 부서별 사원과 이전 사원 급여액 합계를 구하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

          ,SUM(SALARY) OVER (PARTITION BY DEPT_CODE

                                       ORDER BY SALARY

                                       ROWS 1 PRECEDING ) AS 부서별이전사원급여합계

FROM TEMP

29 사번, 이름, 부서번호, 급여를 출력하고 분석함수를 사용한 컬럼 3개를 생성하여 첫번째는 사원들의 급여의 총액

두 번째는 사원들의 누적급여총액 그리고 또 다른 하나의 세 번째는 두 번째를 역순으로 표시하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

          ,SUM(SALARY) OVER (ORDER BY SALARY

                                       ROWS BETWEEN UNBOUNDED PRECEDING

                                       AND UNBOUNDED FOLLOWING ) AS 부서사원급여합계

         ,SUM(SALARY) OVER (ORDER BY SALARY

                                      ROWS UNBOUNDED PRECEDING ) AS 부서사원누적급여합계

         ,SUM(SALARY) OVER (ORDER BY SALARY DESC

                                      ROWS BETWEEN CURRENT ROW

                                      AND UNBOUNDED FOLLOWING ) AS 부서사원누적역순급여합계

FROM TEMP

30 29번 결과 값을 참고하여 사원이 아닌 부서별로 사원들의 총액, 부서별 사원들의 누적급여액 등을 구하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

                ,SUM(SALARY) OVER ( PARTITION BY DEPT_CODE

                                                            ORDER BY SALARY

                                                            ROWS BETWEEN UNBOUNDED PRECEDING

                                                            AND UNBOUNDED FOLLOWING ) AS 부서사원급여합계

               ,SUM(SALARY) OVER ( PARTITION BY DEPT_CODE

                                                            ORDER BY SALARY

                                                            ROWS UNBOUNDED PRECEDING ) AS 부서사원누적급여합계

                ,SUM(SALARY) OVER ( PARTITION BY DEPT_CODE

                                                            ORDER BY SALARY DESC

                                                            ROWS BETWEEN CURRENT ROW

                                                            AND UNBOUNDED FOLLOWING ) AS 부서사원누적역순급여합계

FROM TEMP

31 일자별 사업장별 매출액과 사업장별 매출액의 3일 이동평균 금액을 구하라 (SALE_HIST)

31번 문제는 PARTITION, GROUP, RANGE 활용 문제이다

SELECT SALE_DATE, SALE_SITE, SUM(SALE_AMT),

                 ROUND(AVG(SUM(SALE_AMT)) OVER( PARTITION BY SALE_SITE

                                                                                              ORDER BY SALE_DATE

                                                                                              RANGE INTERVAL '2' DAY PRECEDING)) AS 이동평균

FROM SALE_HIST

GROUP BY SALE_DATE, SALE_SITE

32 각 ROW의 판매액, 동일일자/ 동일품목의 최대판매액, 최대판매액사업장, 해당 사업장 최소판매액, 최소판매액 사업장을 구하여라

SELECT SALE_DATE, SALE_ITEM, SALE_AMT, SALE_SITE

          ,FIRST_VALUE(SALE_AMT) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                    ORDER BY SALE_AMT DESC

                                                    ROWS BETWEEN UNBOUNDED PRECEDING

                                                    AND UNBOUNDED FOLLOWING ) AS 최고판매액

         ,FIRST_VALUE(SALE_SITE) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                   ORDER BY SALE_AMT DESC

                                                   ROWS BETWEEN UNBOUNDED PRECEDING

                                                   AND UNBOUNDED FOLLOWING ) AS 최고판매사업장

        ,LAST_VALUE(SALE_AMT) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                 ORDER BY SALE_AMT DESC

                                                 ROWS BETWEEN UNBOUNDED PRECEDING

                                                 AND UNBOUNDED FOLLOWING ) AS 최저판매액

        ,LAST_VALUE(SALE_SITE) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                 ORDER BY SALE_AMT DESC

                                                 ROWS BETWEEN UNBOUNDED PRECEDING

                                                 AND UNBOUNDED FOLLOWING ) AS 최저판매사업장

FROM SALE_HIST;

33 SALE_HIST의 자료를 이용하여‘01’ 사업장‘ PENCIL’ 품목의 일자별 누적 판매금액을 구하여라

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT,

          SUM(SALE_AMT) OVER ( ORDER BY SALE_DATE

                                          RANGE INTERVAL '5' DAY PRECEDING) AS 누적합계

FROM SALE_HIST

WHERE SALE_ITEM = 'PENCIL'

AND SALE_SITE = '01'

34 품목별/일자별 과거 판매매액을 모두 이용하는 이동평균값을 구하라

SELECT SALE_ITEM , SALE_DATE, SUM(SALE_AMT)

          ,AVG(SUM(SALE_AMT)) OVER( PARTITION BY SALE_ITEM

                                                 ORDER BY SALE_DATE

                                                 ROWS UNBOUNDED PRECEDING ) AS 품목일자별이동평균

FROM SALE_HIST

GROUP BY SALE_ITEM , SALE_DATE

35 SALE_HIST의 자료를 이용하여 “01”사업장 PENCIL 품목에 대해 일자별 매출액과 전일 매출, 당일과 전일의 매출액 차이를 구하시오

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT

          , LAG(SALE_AMT,1) OVER ( ORDER BY SALE_DATE) AS 전일매출

          , SALE_AMT - LAG(SALE_AMT,1) OVER ( ORDER BY SALE_DATE) AS 전일매출차액

FROM SALE_HIST

WHERE SALE_ITEM = 'PENCIL'

AND SALE_SITE = '01’

WINDOW 함수

WINDOW 함수는 AVG, COUNT, SUM, MAX, MIN만 사용 가능

윈도우 함수( ) OVER (

             PARTITION BY 절                              PARTITION BY 절에 명시된 그룹을 좀 더 세부적으로 그룹핑 가능

             ORDER BY 절 [ASC|DESC]             

             ROWS | RANGE

             BETWEEN UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW

             AND UNBOUNDED FOLLOWING | n FOLLOWING | CURRENT ROW

OVER - 쿼리 RESULT SET을 이용해 동작하는 함수라는 구분

PARTITION BY - RESULT SET을 VALUE EXPRESS 에 지정된 값에 근거하여 분활하는 역할 수행

( ) - 안에 기술된 ORDER BY는 RESULT SET에 순위를 부여할 때 정렬기준을 표시하는 부분

ORDER BY – 각 PARTITION 내에서 DATA 각 어떤 값을 기준으로 정렬될 것인가를 지정

[NULLIS FIRST | NULLS LAST] - NULL이 포함된 ROW가 순서상 제일 앞이나 제일 뒤에 위치할 것인지 지정

ROWS | RANGE - 자료의 물리적 순서를 이용(ROWS)할 것인지 논리적 순서(RANGE)를 이용할 것인지 결정

BETWEEN ~ AND ~ - 자료의 범위를 결정한다.

- UNBOUNDED PRECEDING PARTITION의 첫 번째 로우에서 윈도우가 시작한다.

- UNBOUNDED FOLLOWING PARTITION의 마지막 로우에서 윈도우가 시작한다

- CURRENT ROW 현재 ROW를 시작 값

- ROWS 1 PRECEDING 1행 이전 행을 말한다

- ROWS 2 FOLLOWING 2행 이후 행을 말한다

- RANGE는 INTERVAL을 꼭 붙여서 숫자 ‘ ’ 안에 표시해야 한다

- RANGE INTERVAL ‘2’ DAY PRECEDING 3일 이동 합계

윈도우함수의 종류

FIRST_VALUE() - 윈도우 함수의 정렬이 끝이나고 제일 앞에 위치하는 ROW의 값들을 읽어올 때 사용.

LAST_VALUE() - 윈도우 함수의 정렬이 끝이나고 제일 뒤에 위치하는 ROW의 값들을 읽어올 때 사용.

RATIO_TO_REPORT - 함수가 전체대비 해당 ROW의 값이 차지하는 비율을 구해주고 있다.

LAG(컬럼, 원하는 행) - 해당 행의 앞에 몇 번째 행을 참조할 것인지 지정 가능

LEAD(컬럼, 원하는 행) - 해당 행의 뒤에 몇 번째 행을 참조할 것인지 지정 가능