DEFAULT 0 : 입력을 하지 않으면 자동으로 0이 입력되게 하는 것
숫자의 컬럼들이 사칙연산에 동원되는 컬럼들이면 테이블 생성시 DEFAULT 0를 기술해주는 것이 좋음.
일괄적인 INSERT문을 쓸 때 : 일일이 쓰면 안 됨.
** 재고수불테이블을 생성하시오
. 테이블명 : REMAIN
-----------------------------------------------------------------
컬럼명 데이터타입 NULLABLE PK/FK
-----------------------------------------------------------------
REMAIN_YEAR CHAR(4) N.N PK
PROD_ID VARCHAR2(10) N.N PK & FK --기본키가 두 개 : 여러 연도에 걸쳐서 재고수불처리
REMAIN_J_00 NUMBER(5) -- J_00 : 영업이 개시되기 이전의 재고량
REMAIN_I NUMBER(5) -- I : 입고수량
REMAIN_O NUMBER(5) -- O : 출고수량
REMAIN_J_99 NUMBER(5) -- J_99 : 기말재고
REMAIN_DATE DATE -- 날짜기억하게 만드는 컬럼
--------------------------------------------------------------------REMAIN테이블과 PROD테이블은 서로 식별자관계가 된다.
CREATE TABLE REMAIN(
REMAIN_YEAR CHAR(4),
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)
SELECT '2005',PROD_ID,PROD_PROPERSTOCK,PROD_PROPERSTOCK,TO_DATE('20050101')
FROM PROD;
--서브쿼리의 ()가 생략되는 오직 한 경우 : INSERT문을 사용할 때.
서브쿼리를 사용하면 VALUES를 생략한다.
SELECT * FROM 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
SET (A.REMAIN_I,A.REMAIN_J_99,A.REMAIN_DATE)=
(SELECT B.CNT, B.CNT,TO_DATE('20010430')
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) --관련성있는 서브쿼리
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;
ROLLBACK;사용예)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
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')
-- B.CMT : 판매수량 / 출고수량에는 +판매수량, 재고수량에는 -판매수량
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) --관련성있는 서브쿼리
WHERE REMAIN_YEAR='2005'
AND PROD_ID IN(SELECT DISTINCT CART_PROD
FROM CART
WHERE CART_NO LIKE '200504%');
2)Cartesian JOIN)
- 모든 가능한 행들의 조합을 결과로 반환
- 조인조건이 없거나 잘못 정의된 경우
- 반드시 필요한 경우가 아니면 사용하지 말 것
- ANSI의 CROSS JOIN과 같은 JOIN
- ex)A 테이블(100행 20열), B 테이블(30행 10열)을 카타시안 조인하면 결과는(3000행 30열) --행들은 곱하고 열들은 더하기
사용예)
SELECT 'CART' AS 테이블명, COUNT(*) AS 자료수
FROM CART
UNION
SELECT 'BUYPROD',COUNT(*)
FROM BUYPROD
UNION
SELECT 'PROD',COUNT(*)
FROM PROD;
SELECT COUNT(*)
FROM BUYPROD,CART,PROD; --전체의 행의 수 도출(최악의 카타시안)
SELECT COUNT(*)
FROM BUYPROD
CROSS JOIN CART
CROSS JOIN PROD;
3)SELF JOIN
- 하나의 테이블에 2개 이상의 별칭을 부여하여 자신의 테이블사이에 발생되는 조인
사용예)사원테이블에서 사원번호 120번 사원의 급여보다 더 많은 급여를 받는 사원의
사원번호,사원명,부서번호,부서명,급여를 조회하시오
SELECT C.EMPLOYEE_ID AS 사원번호,
--모두 C로 설정한 이유 : 120번사원보다 더 많은 급여를 받는 사원들을 추출하려고
C.EMP_NAME AS 사원명,
C.DEPARTMENT_ID AS 부서번호,
B.DEPARTMENT_NAME AS 부서명,
C.SALARY AS 급여
FROM HR.EMP A, HR.DEPT B, HR.EMP C -- A: 120번사원의 급여를 꺼내옴,
C: 120번사원보다 더 많은 급여를 받는 사원을 꺼내옴
WHERE A.EMPLOYEE_ID=120 -- A테이블을 120번사원 한 명으로 제한함
AND A.SALARY<=C.SALARY -- A테이블의 급여를 C테이블의 사람들과 비교 /
조인조건('='이 사용되지 않은 조인조건)
AND C.DEPARTMENT_ID=B.DEPARTMENT_ID --조인조건
ORDER BY 3,5 DESC;
