Oracle 선택적 조인, 윈도우 함수 활용 어떤값이 모든 행에 +하기, 부등호조인 포인트별 상품 갯수 구하기 WINDOW 함수

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