3/26 Homework

숙제1]
2020년 2~7월 제품별 매입/매출 수량을 조회하여 재고수불테이블을 갱신하시오
단, 갱신일은 2020/07/31 이다

풀이 1)

UPDATE REMAIN A
      SET (A.REMAIN_I, A.REMAIN_O, A.REMAIN_J_99, A.REMAIN_DATE) =
             (SELECT A.REMAIN_I + B.BQTY,
                     A.REMAIN_O + C.CQTY,
                     A.REMAIN_J_99 + B.BQTY - C.CQTY,
                     TO_DATE('20200731')
               FROM (SELECT BUY_PROD,
                            SUM(BUY_QTY) AS BQTY
                       FROM BUYPROD
                      WHERE BUY_DATE BETWEEN TO_DATE('20200201') AND TO_DATE('20200731')
                      GROUP BY BUY_PROD) B,
                     (SELECT CART_PROD,
                             SUM(CART_QTY) AS CQTY
                        FROM CART
                       WHERE SUBSTR(CART_NO,1,6) BETWEEN '202002' AND '202007'
                       GROUP BY CART_PROD) C
                      WHERE B.BUY_PROD=A.PROD_ID
                        AND C.CART_PROD=A.PROD_ID)
 WHERE A.PROD_ID IN (SELECT BUY_PROD          
                       FROM BUYPROD
                      WHERE BUY_DATE BETWEEN TO_DATE('20200201') AND TO_DATE('20200731'))
 AND A.PROD_ID IN (SELECT CART_PROD
                     FROM CART
                    WHERE SUBSTR(CART_NO,1,6) BETWEEN '202002' AND '202007') ;

풀이 2 ) WHERE절 없이 외부조인으로 푸는법 

서브쿼리를 하나 더 써서 외부조인을 한 후에 넣어줘야함

UPDATE REMAIN R
      SET (R.REMAIN_I, R.REMAIN_O, R.REMAIN_J_99, R.REMAIN_DATE) =
             (SELECT R.REMAIN_I + P.BSUM,
                     R.REMAIN_O + P.CSUM,
                     R.REMAIN_J_99 + P.BSUM - P.CSUM,
                     TO_DATE('20200731')
                FROM (SELECT A.PROD_ID AS PID,
                             NVL(B.BQTY,0) AS BSUM,
                             NVL(C.CQTY,0) AS CSUM
                        FROM PROD A,
                             (SELECT BUY_PROD,
                                     SUM(BUY_QTY) AS BQTY
                                FROM BUYPROD
                               WHERE BUY_DATE BETWEEN TO_DATE('20200201')
                                                  AND TO_DATE('20200731')
                               GROUP BY BUY_PROD) B,
                              (SELECT CART_PROD,
                                      SUM(CART_QTY) AS CQTY
                                  FROM CART
                                 WHERE SUBSTR(CART_NO,1,6) BETWEEN '202002' AND '202007'
                                 GROUP BY CART_PROD) C
                       WHERE A.PROD_ID=B.BUY_PROD(+)
                         AND A.PROD_ID=C.CART_PROD(+) ) P
             WHERE R.PROD_ID=P.PID);

사용 예제 1)
상품테이블에서 상품별 마일리지가 NULL인 상품을 찾아 '0'으로 치환하시오

UPDATE PROD 
    SET PROD_MILEAGE=0
WHERE PROD_ID IN (SELECT PROD_ID
                    FROM PROD 
                   WHERE PROD_MILEAGE IS NULL);

사용 예제 1-1)
상품테이블에서 판매가에서 매입가를 뺀 값의 0.1% 값을 구하여 상품의 마일리지로 갱신

UPDATE PROD A
   SET A.PROD_MILEAGE = (SELECT ROUND((B.PROD_PRICE - B.PROD_COST)*0.001,-1)
                           FROM PROD B
                          WHERE A.PROD_ID=B.PROD_ID );

사용 예제 2)
회원테이블의 마일리지를 모두 0으로 갱신하시오.

UPDATE MEMBER
   SET MEM_MILEAGE = 0;

사용 예제 3)
2020년 회원별 상품별 구매수량을 조회하여 마일리지를 새로 부여하시오.

풀이 1) 서브쿼리 1개

UPDATE MEMBER A
       SET (A.MEM_MILEAGE) = (SELECT SUM(C.CART_QTY*B.PROD_MILEAGE)
                                FROM PROD B, CART C
                               WHERE C.CART_PROD=B.PROD_ID
                                 AND SUBSTR(CART_NO,1,4)='2020'
                                 AND A.MEM_ID = C.CART_MEMBER
                               GROUP BY C.CART_MEMBER)
WHERE A.MEM_ID IN (SELECT DISTINCT CART_MEMBER
                     FROM CART
                    WHERE SUBSTR(CART_NO,1,4)='2020');

풀이 2) 서브쿼리 2개

UPDATE MEMBER A
       SET A.MEM_MILEAGE = ( SELECT BSUM
                               FROM  (SELECT B.CART_MEMBER AS CID,
                                             SUM (B.CSUM*A.PROD_MILEAGE) AS BSUM
                                        FROM PROD A,
                                             (SELECT CART_MEMBER,
                                                     CART_PROD,
                                                     SUM(C.CART_QTY) AS CSUM
                                                FROM CART C
                                               WHERE SUBSTR(CART_NO,1,4)='2020'
                                               GROUP BY CART_MEMBER, CART_PROD) B     
                                         WHERE A.PROD_ID=B.CART_PROD
                                         GROUP BY B.CART_MEMBER)
                               WHERE CID=A.MEM_ID)
 WHERE A.MEM_ID IN (SELECT DISTINCT CART_MEMBER
                      FROM CART
                     WHERE SUBSTR(CART_NO,1,4)='2020') ;