230731 SQL 강의

--2020년도 판매금액이 8천만 이상인 거래처의 담당자 컬럼에
--    '우수거래처'로 갱신 (  WHERE절에 EXISTS문 사용  )

--1. 거래처테이블의 담당자 조회
SELECT BUYER_ID      AS "거래처코드"
     , BUYER_NAME    AS "거래처명"
     , BUYER_CHARGER AS "담당자"
  FROM BUYER;

--2. 판매금액 8천만 이상 우수거래처로 지정(조회) – EXISTS를 통해 INTERSACT (교집합)를 구현
--    * BUYER테이블과 CART테이블은 직접적인 관계가 없으므로 PROD테이블을 통해 JOIN 실현
SELECT BUYER_ID      AS "거래처코드"
     , BUYER_NAME    AS "거래처명"
     , BUYER_CHARGER AS "담당자"
     , '우수거래처'  AS "우수거래처"   
  FROM BUYER
 WHERE EXISTS (SELECT SUM(CART.CART_QTY * PROD.PROD_SALE)
               FROM   PROD
                  ,   CART
               WHERE  CART.CART_NO LIKE '2020%'
               AND    CART.CART_PROD  = PROD.PROD_ID
               AND    PROD.PROD_BUYER = BUYER.BUYER_ID
               HAVING SUM(CART.CART_QTY * PROD.PROD_SALE) > 80000000);

--2020년도 판매금액이 8천만 이상인 거래처의 담당자 컬럼에
--    '우수거래처'로 갱신 (  WHERE절에 EXISTS문 사용  )
--
--(갱신)
ALTER TABLE BUYER MODIFY (BUYER_CHARGER varchar2(20));


UPDATE        BUYER
SET           BUYER_CHARGER = '우수거래처'
WHERE EXISTS (SELECT SUM(CART.CART_QTY * PROD.PROD_SALE)
              FROM   PROD
                 ,   CART
              WHERE  CART.CART_NO LIKE '2020%'
              AND    CART.CART_PROD  = PROD.PROD_ID
              AND    PROD.PROD_BUYER = BUYER.BUYER_ID
              HAVING SUM(CART.CART_QTY * PROD.PROD_SALE) > 80000000);

SELECT * FROM BUYER;

---

-- 2020년도 구매금액이 3천만 이상인 회원의 마일리지 점수를 
-- 20만으로 하여 수정하시오 ? 

--1. 회원테이블의 마일리지 조회
SELECT MEM_ID       AS "회원ID"
     , MEM_NAME     AS "성명"
     , MEM_MILEAGE  AS "마일리지"
  FROM MEMBER;

--2. 구매금액 3천만 이상 마일리지 조회
SELECT MEM_ID       AS "회원ID"
     , MEM_NAME     AS "성명"
     , MEM_MILEAGE  AS "마일리지"
  FROM MEMBER
 WHERE EXISTS (SELECT SUM(CART.CART_QTY * PROD.PROD_SALE) 
                 FROM PROD
                    , CART
                WHERE CART.CART_NO LIKE '2020%' 
                  AND CART.CART_PROD   = PROD.PROD_ID
                  AND CART.CART_MEMBER = MEMBER.MEM_ID
              HAVING SUM(CART.CART_QTY * PROD.PROD_SALE)  > 30000000);

--3. 구매금액 3천만 이상 마일리지 20만으로 갱신
UPDATE MEMBER
   SET MEM_MILEAGE = 200000
 WHERE EXISTS (SELECT SUM(CART.CART_QTY * PROD.PROD_SALE) 
                 FROM PROD
                    , CART
                WHERE CART.CART_NO LIKE '2020%' 
                  AND CART.CART_PROD   = PROD.PROD_ID
                  AND CART.CART_MEMBER = MEMBER.MEM_ID
               HAVING SUM(CART.CART_QTY * PROD.PROD_SALE)  > 30000000);

SELECT * FROM MEMBER;

---

--SubQuery 로 Table 생성 구문
--CREATE TABLE table_name [column_list…]
--    AS Subquery

--재고 수불 테이블을 복사하여 remain2 테이블을 생성하시오
CREATE TABLE REMAIN2 
AS 
SELECT * FROM REMAIN; 

SELECT * FROM REMAIN2;

--상품분류테이블의 내용을 모두 삭제

--  1) 전체 RECORD 삭제
--    - DELETE FROM table_name;
--      (ROLLBACK 가능, 느림)
--    
--    - TRUNCATE TABLE table_name; ( 자동 COMMIT (복구 불가), 빠름 )

--  2) TABLE  제거
--    -자료가 수십만 건 이상일 경우 또는 빠른 시간 내에 자료전체를
--     조건 없이 삭제해야 하는 경우 사용가능
--    -단 TABLE의 모든 이력이 사라지므로 TABLE CREATE, 
--      PRIMARY KEY, INDEX등을 다시 생성하여 주어야 한다.
--
--      DROP TABLE table_name;

--재고수불2  테이블의 내용을 모두 삭제

--  1) DELETE로 전체 레코드 삭제
       DELETE FROM remain2;
       SELECT * FROM REMAIN2;
       
--  2) 작업 취소
       ROLLBACK;
       SELECT * FROM REMAIN2;
       
--  3) TRUNCATE 로 전체 레코드 삭제
       TRUNCATE TABLE remain2;
       SELECT * FROM REMAIN2;

--  4) 작업 취소
       ROLLBACK;
       SELECT * FROM REMAIN2;

--  5) TABLE 제거
       DROP TABLE remain2;




--재고수불 테이블에서 2003년도 자료 중  입고수량 +출고수량이 
--   20개 이상인 자료를 삭제 하시오 ?
--   (조건이 있으므로 WHERE조건에 AND 사용 )

--1. 조회 ( Alias는 연도, 상품, 입고량, 출고량 ,합계수량 )
SELECT REMAIN_YEAR         AS "연도"
     , REMAIN_PROD         AS "상품"
     , REMAIN_I            AS "입고량"
     , REMAIN_O            AS "출고량"
     , REMAIN_I + REMAIN_O AS "합계수량"
  FROM REMAIN
 WHERE REMAIN_YEAR = '2003'
   AND (NVL(REMAIN_I,0) + NVL(REMAIN_O,0)) >= 20;

--2. 삭제
DELETE 
  FROM REMAIN
 WHERE REMAIN_YEAR = '2003'
   AND (NVL(REMAIN_I,0) + NVL(REMAIN_O,0)) >= 20;

---

---

--상품 분류테이블에 'P103', 'USB 제품'을 등록하시오?
--(단, lprod_id 컬럼의 값은 최대값을 구하여 1을 더한 서브쿼리를 작성하여 입력한다.)
SELECT * FROM lprod;
SELECT NVL(MAX(lprod_id), 0) FROM lprod;

INSERT INTO LPROD(LPROD_ID, LPROD_GU, LPROD_NM)
     VALUES ((SELECT NVL(MAX(LPROD_ID),0)+1 FROM LPROD),'P103','USB제품');
SELECT * FROM lprod;

--Sequence 구문 
--   CREATE SEQUENCE sequence_name
--	   [ START WITH  n ]
--       [ INCREMENT BY  n ]
--       [ MAXVALUE n | NOMAXVALUE ]
--       [ MINVALUE  n | NOMINVALUE ]
--       [ CYCLE | NOCYCLE ]
--       [ CACHE n | NOCACHE ]
--       [ ORDER | NOORDER ]

--lprod_seq 시퀀스 생성
CREATE SEQUENCE LPROD_SEQ
   INCREMENT BY 1
     START WITH 11;

--상품 분류테이블에  'P203' , '아동복' 을 등록하시오?
INSERT INTO LPROD (LPROD_ID, LPROD_GU, LPROD_NM) 
     VALUES (LPROD_SEQ.NEXTVAL, 'P203', '아동복' );
     
SELECT * FROM lprod;

--다음 번호로 증가
SELECT LPROD_SEQ.NEXTVAL FROM DUAL;

--현재 번호
SELECT LPROD_SEQ.CURRVAL FROM DUAL;

--Sequence 변경
--  - START WITH는 변경할 수 없다.
 
ALTER SEQUENCE LPROD_SEQ
INCREMENT BY   2
MAXVALUE       999;

SELECT LPROD_SEQ.NEXTVAL FROM DUAL;

SELECT LPROD_SEQ.CURRVAL FROM DUAL;

--Sequence 제거(* ROLLBACK되지 않음!!)
DROP SEQUENCE LPROD_SEQ;

--다음 요건을 만족하는 시퀀스를 생성하시오? (P.221 참고)
--객체명 : cart_seq, 증감값 : 1, 최소값 : 10000, 최대값 : 99999, 순환가능
CREATE SEQUENCE CART_SEQ
   INCREMENT BY 1
       MINVALUE 10000
       MAXVALUE 99999
          CYCLE;

SELECT CART_SEQ.NEXTVAL FROM DUAL;

--다음 요건을 만족하는 시퀀스를 생성하시오? (P.221 참고)
--객체명 : cart_seq, 증감값 : 1, 최소값 : 10000, 최대값 : 99999, 순환가능
CREATE SEQUENCE CART_SEQ
   INCREMENT BY 4000
       MINVALUE 10000
       MAXVALUE 100000
          CYCLE;

DROP SEQUENCE CART_SEQ;

SELECT CART_SEQ.NEXTVAL FROM DUAL;

---

--[Synonym은 동의어, 별칭의 의미.]
-- Synonym은 객체에 대한 다른 이름으로 대체한다.
-- Synonym을 이용하는 경우 
-- - 다른 소유자의 객체를 접근하는 경우 "스키마명.객체명"으로 접근하는데 이를 편하게 한다.
-- - 긴 이름의 객체명을 쉬운 이름으로 대체하고자 할 때

--Sequence 구문 
--   CREATE [ or REPLACE ]  [ PUBLIC ] SYNONYM synonym_name
--	  FOR  object_name

CREATE SYNONYM MEM
   FOR MEMBER;

SELECT * FROM MEM;

CREATE SYNONYM MYDUAL
   FOR SYS.DUAL;

SELECT 'Hello World'  FROM MYDUAL;

DROP SYNONYM MYDUAL;

---

--회원 생일이 조건절에 자주 사용되어 Index를 생성 
CREATE INDEX IDX_MEMBER_BIR 
          ON MEMBER(MEM_BIR);

SELECT * FROM MEMBER;

SELECT  MEM_ID
     ,  MEM_NAME
     ,  MEM_JOB
     ,  MEM_BIR
  FROM  MEMBER
 WHERE  TO_CHAR(MEM_BIR, 'YYYY') = '2000';

--회원생일에서 년도만 분리하여 인덱스를 생성(Function-based Index)
CREATE INDEX IDX_MEMBER_BIR_YEAR 
    ON MEMBER(TO_CHAR(MEM_BIR, 'YYYY'));

SELECT  MEM_ID
     ,  MEM_NAME
     ,  MEM_JOB
     ,  MEM_BIR
  FROM  MEMBER
 WHERE  TO_CHAR(MEM_BIR, 'YYYY') = '2000';

--idx_member_bir 인덱스는 삭제하고, idx_member_bir_year 인덱스는 rebuild 하시오?

DROP INDEX IDX_MEMBER_BIR;

ALTER INDEX IDX_MEMBER_BIR_YEAR REBUILD;

---

--ALL_OBJECTS의 모든 컬럼 상세
DESC all_objects;

--Dictionary 뷰에서 'ALL_'로 시작 하는 모든 테이블 조회

SELECT TABLE_NAME
     , COMMENTS
  FROM DICTIONARY 
 WHERE TABLE_NAME LIKE 'ALL_%';

--현재 로그인한 사용자가 만든 모든 객체 정보를 출력

SELECT OBJECT_NAME
     , OBJECT_TYPE
     , CREATED
  FROM ALL_OBJECTS
 WHERE OWNER = 'PC_21' -- 대문자, 소문자 구분함.
 ORDER BY OBJECT_TYPE ASC;

--USER_TABLES의 컬럼 상세를 확인하고 각 테이블 전체 레코드 개수를 출력.  
--( 테이블명, 레코드 수 )

DESC    USER_TABLES; 

SELECT  TABLE_NAME
     ,  ROWNUM 
FROM    USER_TABLES;

--USER_CONSTRAINTS, USER_CONS_COLUMNS의 컬럼 상세를 확인하고 상품 테이블의 제약조건을 출력하시오?
--(컬럼명, 제약명, 타입, 제약내용)

SELECT * 
  FROM ALL_CONSTRAINTS
 WHERE TABLE_NAME = 'PROD';

---

-- 출력
 SET SERVEROUTPUT ON;  -- 출력옵션으로 ON
 
 DECLARE 
    v_i    NUMBER(9,2) := 1234567.12345 ;
    v_name VARCHAR2(20);
    c_pi   CONSTANT NUMBER(8,6) := 3.141592;
    v_flag BOOLEAN NOT NULL := true;
    v_date VARCHAR2(10) := TO_CHAR(SYSDATE, 'YYYY-MM-DD');
 BEGIN
    v_name := '홍길동';
    DBMS_OUTPUT.ENABLE;
    DBMS_OUTPUT.PUT_LINE( 'v_i : ' || v_i );           
    DBMS_OUTPUT.PUT_LINE( 'v_name : ' || v_name);    
    DBMS_OUTPUT.PUT_LINE( 'c_pi : ' || c_pi );
    DBMS_OUTPUT.PUT_LINE( 'v_date : ' || v_date);
 END;
 /

---

--IF 문
--조건이 true이면 이하 문장을 실행하고, 조건이 false이면 관련된 
--    문장을 통과한다.
--  - ELSIF절은 여러 개가 가능하나, ELSE절은 한 개만 가능하다

DECLARE 
  v_num   NUMBER := 37;
BEGIN
  DBMS_OUTPUT.ENABLE; 

  IF MOD(v_num, 2) = 0 THEN
     DBMS_OUTPUT.PUT_LINE( v_num ||  ' 은(는) 짝수');
  ELSE 
     DBMS_OUTPUT.PUT_LINE( v_num ||  ' 은(는) 홀수');
  END IF;
END;
/