5일차

주요내용 : 데이터 삭제, 데이터 입력, 데이터 업데이트, CASE WHEN

/* 0730 실습1 */
-- 1. 주문번호가 20005, 20007인 주문된 제품중에 // 총 주문수량이 100개가 넘은 제품의 제품이름
SELECT TRIM(prod_name)
FROM Products
WHERE prod_id IN(
    SELECT prod_id
    FROM Orderitems
    WHERE order_num IN (20005, 20007)
    GROUP BY prod_id
    HAVING SUM(quantity) >100
    )
;

-- 2. DDL01 나 BRS01 인 제조업체에서 만든 제품 중 // 가격이 10불이상 인 제품의 총 판매수량
-- 결과: 제품명, 총판매수량
SELECT prod_id, TRIM(prod_name),
    (
    SELECT SUM(quantity)
    FROM Orderitems
    WHERE prod_id = a.prod_id
    AND item_price >= 10
    ) sum_quantity
FROM Products a
WHERE vend_id IN ('DDL01', 'BRS01')
AND EXISTS ( --exist는 1개만 존재해도 작동함. exist나 join 둘다 풀수있는 방법이면 exists로 해라.(효율이 더 좋음)
    SELECT * -- exist는 row 만 상관,
    FROM Orderitems
    WHERE prod_id = a.prod_id
    AND item_price >=10
    )
;

-- 3. 다음의 조건에 따라 고객의 고객번호, 고객이름, 고객주소를 추출하시오
-- 조건: 주문한 총 상품의 개수가 2개이상인 고객
SELECT cust_id, cust_name, cust_address
FROM Customers k
WHERE EXISTS(
    SELECT *
    FROM Orderitems OI, Orders O
    WHERE OI.order_num = O.order_num
    AND O.cust_id = k.cust_id
    GROUP BY OI.prod_id
    HAVING COUNT(*) >= 2
    )
;

-- 4. 현재 주문된 상품을 공급한 공급업체의 국가별 공급업체수
-- 결과: 국가,총공급업체수, 주문된 상품을 공급한 공급업체 수
SELECT
    vend_country, vend_cnt,
    (SELECT COUNT(*)
     FROM   orderitems aa, products bb, vendors cc 
     WHERE  aa.prod_id = bb.prod_id
     AND    bb.vend_id = cc.vend_id
     AND    cc.vend_country = t.vend_country
    ) order_vend_cnt
FROM
(
    SELECT d.vend_country, COUNT(d.vend_country) as vend_cnt
    FROM   orderitems a, orders b, products c, vendors d
    WHERE  a.order_num = b.order_num
    AND    a.prod_id = c.prod_id
    AND    c.vend_id = d.vend_id
    GROUP BY d.vend_country
) t;

/* CASE WHEN */
/*  형식
SELECT CASE WHEN 조건1 THEN 참일때 결과
            WHEN 조건2 THEN 참일때 결과
            ELSE 조건모두 불만족 시 결과
        END
    FROM   ..
--  조건절 사용*/

-- 우편번호로 지역 Type을 구분
SELECT vend_id,
    CASE WHEN SUBSTR(vend_zip, 1, 2) ='44' THEN 'A'
         WHEN SUBSTR(vend_zip, 1, 2) ='99' THEN 'B'
         ELSE 'C'
    END AS resion_type
FROM Vendors;

-- 기본제품가격보다 주문 시 낮은 단가로 주문되는 상품 중에 가격을 5$를 기준으로 고가/저가/일치로 구분
SELECT order_num, prod_id,
    CASE WHEN item_price > 5 THEN '고가'
         WHEN item_price < 5 THEN '저가'
         ELSE '일치'
    END AS price_std
FROM Orderitems a
WHERE item_price >= (
            SELECT MIN(prod_price)
            FROM Products
            WHERE prod_id = a.prod_id
            )
;

--  그룹함수와 조건절 함께 사용
-- 공급업체의 주별 수를 컬럼으로 분리하여 추출
SELECT SUM(CASE WHEN vend_state = 'MI' THEN 1 END) MI_CNT,
       SUM(CASE WHEN vend_state = 'OH' THEN 1 END) OH_CNT,
       SUM(CASE WHEN vend_state = 'CA' THEN 1 END) CA_CNT,
       SUM(CASE WHEN vend_state = 'NY' THEN 1 END) NY_CNT
FROM Vendors;

/* 실습 */
-- 1. 주문의 주문번호, 고객번호, 주문시기를 추출하시오
-- 주문시기: 일이 1~15일 사이면 ‘상일’, 16~31일 사이면 ‘하일’
SELECT order_num, cust_id, 
    CASE WHEN TO_CHAR(order_date, 'DD') <= 15 THEN '상일'
    ELSE '하일'
    END AS "주문시기"
FROM Orders;

SELECT order_num, cust_id,
         CASE WHEN TO_CHAR(order_date,'DD') >= '01' AND TO_CHAR(order_date,'DD') <= '15' THEN '상일'
              WHEN TO_CHAR(order_date,'DD') >= '16' AND TO_CHAR(order_date,'DD') <= '31' THEN '하일'
         END order_period
FROM orders;

-- 2. 주문에서 주문번호와 주문가능날자를 추출하시오
-- 주문가능날자: 주문날자가 1~15일이면 현재날자, 16~31일이면 다음달 1일
SELECT order_num, 
    CASE WHEN TO_CHAR(order_date, 'DD') <= 15 THEN '현재날짜'
    ELSE '다음달 1일'
    END AS "주문가능날짜"
FROM Orders;

SELECT  order_num,
          CASE WHEN TO_CHAR(order_date,'DD') >= '01' AND TO_CHAR(order_date,'DD') <= '15' THEN TO_CHAR(SYSDATE,'YYYY-MM-DD')
               WHEN TO_CHAR(order_date,'DD') >= '16' AND TO_CHAR(order_date,'DD') <= '31' THEN TO_CHAR(SYSDATE+1, 'YYYY-MM-DD')
          END orderCanDt
FROM orders;

-- 3. 주문에서 주문번호와 주문가능날자(YYYY-MM-DD)를 추출하시오
-- 주문가능날자: 일자의 10자리가 0이면 1일, 1이면 10일, 2이면 20일, 30이면 30일
SELECT order_num, 
    CASE WHEN SUBSTR(TO_CHAR(order_date, 'DD'), 2) = 0 THEN '1일'
         WHEN SUBSTR(TO_CHAR(order_date, 'DD'), 2) = 1 THEN '10일'
         WHEN SUBSTR(TO_CHAR(order_date, 'DD'), 2) = 2 THEN '20일'
         ELSE '30일'
    END AS "주문가능날짜"
FROM Orders;

SELECT order_num,
          TO_CHAR(
              CASE WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '0' THEN TO_DATE(TO_CHAR(order_date,'YYYYMM')||'01')
                   WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '1' THEN TO_DATE(TO_CHAR(order_date,'YYYYMM')||'10')
                   WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '2' THEN TO_DATE(TO_CHAR(order_date,'YYYYMM')||'20')
                   WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '3' THEN TO_DATE(TO_CHAR(order_date,'YYYYMM')||'30')
              END, 'YYYY-MM-DD') orderCanDt     
FROM orders;

/* 데이터 입력*/
--  형식
-- INSERT INTO 테이블명 VALUES(전체컬럼값…)
-- INSERT INTO 테이블명(컬럼명..) VALUES(컬럼값…): 컬럼명과 컬럼값의 개수는 일치해야 한다

-- Customers에 모든 컬럼에 대해 Data 추가
INSERT INTO Customers
VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);

-- Customers에 특정 컬럼에 대해 Data 추가
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000017', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL);


-- 초기값을 포함하여 테이블 생성
CREATE TABLE custnew AS
SELECT * FROM Customers;

DELETE FROM custnew;

-- SELECT 쿼리의 결과로 데이터 입력
-- 각 컬럼 간 데이터 타입이 일치해야 한다
INSERT INTO custnew(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country
FROM  customers;

/*실습*/
-- 1. 고객 테이블에 다음의 정보를 추가하시오
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000007', '홍길동', '서울시 서초구 강남대로 459', 'Seoul', '51243', 'KOR', 'Park', 'aaa@naver.com');

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000008', '박길동', '서울시 서초구 강남대로 459', 'Seoul', '51472', 'KOR', 'Kim', 'bbb@daum.net');

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000009', '박홍준', '서울시 서초구 강남대로 459', 'Seoul', '53782', 'KOR', 'Kim', 'ccc@google.co.kr');

--2. 제품 테이블에 다음의 정보를 추가하시오
INSERT INTO Products
VALUES('KRO1', 'BRS01', '뽀로로인형', '10', 'TV프로그램으로 유명한 뽀로로를 인형으로 만듦');

INSERT INTO Products
VALUES('KRO2', 'BRE02', '곰인형', '15', '요즘 유행하는 곰모양의 인형');

INSERT INTO Products
VALUES('KR03', 'DLL01', '태권V로보트', '100', '향수를 자극하는 어른을 위한 태권V로보트');

-- 3. 주문 테이블에 다음의 정보를 추가하시오
INSERT INTO Orders
VALUES('20010', '2019-02-01', '1000000006');

INSERT INTO Orders
VALUES('20011', '2019-05-04', '1000000007');

-- 4. 주문상품 테이블에 다음의 정보를 추가하시오
INSERT INTO Orderitems
VALUES('20010', '1', 'KR01', '2', '9');

INSERT INTO Orderitems
VALUES('20011', '1', 'KR02', '2', '16');

INSERT INTO Orderitems
VALUES('20011', '2', 'KR03', '3', '95');

/*데이터 업데이트*/
--  형식 - 하위쿼리를 사용할 수도 있다
-- 값에 쿼리를 넣을 수도 있다.
UPDATE 테이블명
SET    컬럼명 = 값, 컬럼값 = 값
WHERE  컬럼명 = 값;

-- ID가 1000000005인 고객의 전자메일주소를 변경
UPDATE Customers
SET cust_email = 'kim@naver.com',
    cust_contact = 'Sam Roberts'
WHERE  cust_id = '1000000005';

-- 1. 고객테이블에서 고객번호가 ‘1000000006’인 고객의 담당자를 ‘Jin’으로 변경하시오
UPDATE CUSTOMERS
SET cust_contact = 'Jin'
WHERE cust_id = '1000000006';

-- 2. 주문테이블에서 고객국가가 ‘KOR’인 고객이 주문한 항목수량에 +1씩을 더하시오
UPDATE Orderitems
SET quantity = quantity + 1
WHERE Order_num IN(
    SELECT order_num
    FROM Customers C, Orders O
    WHERE O.cust_id = C.cust_id
    AND C.cust_country = 'KOR'
    )
;

-- 3. 주문날자가 2019년도에 발생한 주문 중에 주문상품의 항목가격이 90$를 넘는 항목가격에 대해 10%씩을 DC하여 변경하시오
UPDATE ORDERITEMS
SET item_price = item_price * 0.9
WHERE order_num IN(
    SELECT order_num
    FROM Orders
    WHERE TO_CHAR(order_date, 'YYYY') = '2019'
    )
AND item_price >= 90;

-- 4. 주문상품중에 (제품의 제품가격보다 주문상품의 항목가격이 비싼) 항목가격을 제품의 제품가격으로 변경하시오
UPDATE ORDERITEMS k
SET item_price = (
    SELECT prod_price
    FROM Products
    WHERE prod_id = k.prod_id
    )
WHERE order_num IN (
    SELECT DISTINCT order_num
    FROM Orderitems OI, Products P
    WHERE OI.item_price > P.prod_price
    )
;

/* 데이터 삭제*/
--  형식 - 하위쿼리를 사용할 수도 있다
-- 삭제하기 전에 select으로 확인해보고 할것.
-- 부모부터 삭제할수 없음. 자식 테이블에서 우선 not exists 를 이용해서 확인후 부모 테이블에서 삭제해야함.
DELETE FROM 테이블명
WHERE  컬럼명 = 값;

-- ID가 1000000008인 고객삭제
DELETE FROM Customers
WHERE cust_id = ‘1000000008’;

-- 실습
-- 1. 제품번호가 KR03인 제품을 제품테이블에서 삭제하고 주문상품테이블에서도 삭제하시오
DELETE FROM Products
WHERE prod_id = 'KR03';

DELETE FROM Orderitems
WHERE prod_id = 'KR03';

SELECT *
FROM Products
WHERE prod_id = 'KR03';

-- 2. 공급업체가 ‘BRE02’인 공급업체가 생산한 제품중 ‘곰인형’ 제품을 주문한 주문정보를 삭제하시오
DELETE FROM Orderitems
WHERE prod_id IN (
    SELECT prod_id 
    FROM Products
    WHERE vend_id = 'BRE02'
    AND prod_name LIKE '%곰인형%'
    )
;

DELETE FROM Orders a
WHERE NOT EXISTS (
    SELECT order_num
    FROM Orderitems
    WHERE order_num = a.order_num
    )
;

-- 3. 고객 ‘1000000006’가 주문한 주문정보를 삭제하시오
DELETE FROM Orderitems
WHERE order_num IN(
    SELECT order_num
    FROM Orders
    WHERE cust_id = '1000000006'
    )
;

DELETE FROM Orders a
WHERE NOT EXISTS (
    SELECT order_num
    FROM Orderitems
    WHERE order_num = a.order_num
    )
;