오라클 재고수불테이블의 생성&

** 재고수불 테이블을 생성하시오
.테이블명: REMAIN

     컬럼명     데이터타입           NULLTABLE           PK/FK
     ----------------------------------------------------------------
     REMAIN_YEAR      CHAR(4)             N.N                PK
  --연도와 재품코드(제품 테이블의 제품코드)를 합쳐 기본코드로 만듬
  PROD_ID          VARCHAR2(10)           N.N            PK&FK
  REMAIN_J_00      NUMBER(5)
  --기초재고
  REMAIN_I         NUMBER(5)  
  --입고수량
  REMAIN_O         NUMBER(5)
  --출고수량
  REMAIN_J_99      NUMBER(5)
  --기말재고
  REMAIN_DATE       DATE
  --날짜기록 하는 재고수불 테이블
  -------------------------------------------------------------------
  
  CREATE TABLE REMAIN(
  REMAIN_YEAR      CHAR(4) NOT NULL, -- 기본적으로  NOT NULL 이기 때문에 생략가능 
  PROD_ID          VARCHAR2(10),          
  REMAIN_J_00      NUMBER(5) DEFAULT 0,
  REMAIN_I         NUMBER(5) DEFAULT 0, 
  REMAIN_O         NUMBER(5) DEFAULT 0,
  REMAIN_J_99      NUMBER(5) DEFAULT 0,
  REMAIN_DATE       DATE,
  CONSTRAINT pk_remain PRIMARY KEY(REMAIN_YEAR,PROD_ID)
  CONSTRAINT fk_remain_prod FOREIGN KEY(PROD_ID) --중복불가
  REFERENCES PROD(PROD_ID));

** 생성된 재고수불테이블에서 다음 자료를 입력하시오
1)년도 : 2005
2)상품코드 : PROD테이블의 상품코드
3)기초재고수량 : PROD테이블의 적정재고(PROD_PROPERSTOCK)
4)기말재고(창고에 쌓여있는 재고) : 기초재고
5)갱신일짜 : 2005년 1월 1일

INSERT INTO REMAIN(REMAIN_YEAR,PROD_ID,REMAIN_J_00,REMAIN_J_99,REMAIN_DATE) 
--INSERT INTO가 사용되는 서브쿼리은 ()사용 안함
    SELECT '2005',PROD_ID,PROD_PROPERSTOCK,PROD_PROPERSTOCK,TO_DATE('20050101')
    FROM PROD;

SELECT * FROM REMAIN; -- REMAIN테이블을 보여줘~

UPDATE REMAIN
SET REMAIN_J_00=0,
    REMAIN_J_99=0;

COMMIT;

사용예) 2005년 1-3월 제품별 매입현황을 조회하시오
Alias는 상품코드,매입수량이다. --재고 테이블을 만들어야함

          SELECT BUY_PROD AS BID,
                 SUM(BUY_QTY) AS CNT
            FROM BUYPROD
           WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050331')
        GROUP BY BUY_PROD;
        

사용예) 위 예에서 조회된 자료를 이용해 재고수불태이블을 갱신하시오
--재고수량 테이블에 입고수량이 있어서 입고수량과 기말재고, 날짜를 변경해 주면 됨다.

UPDATE REMAIN A --갱신의 대상은 REMAIN테이블
   SET (A.REMAIN_I, A.REMAIN_J_99,A.REMAIN_DATE)=  --갱신할 컬럼
      (SELECT B.CNT, B.CNT,TO_DATE('20050430')
         FROM(SELECT BUY_PROD AS BID,
                 SUM(BUY_QTY) AS CNT
            FROM BUYPROD
           WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050430')
        GROUP BY BUY_PROD) B
    WHERE A.PROD_ID = B.BID) --조인(메인커리 테이블과 서브 쿼리 테이블을 조인: 관련성 있는 서브 쿼리)
--UPDATE에 걸리는 WHERE
WHERE REMAIN_YEAR = '2005'
  AND PROD_ID IN(SELECT DISTINCT BUY_PROD
                    FROM BUYPROD
                    WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050430')); 
COMMIT;
SELECT *FROM REMAIN; --재고수불테이블

사용예) 2005년도 4월 제품별 매출현황을 조회하시오
Alias는 상품코드, 매출수량이다.

SELECT CART_PROD AS CID,
       SUM(CART_QTY) AS CMT
  FROM CART
 WHERE CART_NO LIKE '200504%'
 GROUP BY CART_PROD 

(재고수불테이블 UPDATE)

UPDATE REMAIN A --갱신의 대상은 REMAIN테이블
   SET (A.REMAIN_O, A.REMAIN_J_99,A.REMAIN_DATE)=  --갱신할 컬럼
      (SELECT A.REMAIN_O+B.CMT,A.REMAIN_J_99-B.CMT,TO_DATE('20050430')
      --출고 수량은 더해주고, 전체 재고에서는 뺀것
         FROM(SELECT CART_PROD AS CID,
                     SUM(CART_QTY) AS CMT
                FROM CART
               WHERE CART_NO LIKE '200504%'
              GROUP BY CART_PROD ) B
     WHERE A.PROD_ID = B.CID) --조인(메인커리 테이블과 서브 쿼리 테이블을 조인: 관련성 있는 서브 쿼리)
--UPDATE에 걸리는 WHERE 생성
WHERE REMAIN_YEAR = '2005'
  AND PROD_ID IN(SELECT DISTINCT CART_PROD --PROD_ID에서 IN괄호 안에 있는 것들 추출
                    FROM CART
                    WHERE CART_NO LIKE '200504%');
                 
                 SELECT *FROM REMAIN;