8일차

=================== @@@@0812 SQL 실습

-- • 0812실습 - 다음의 SQL을 작성하시오
-- 1.고객에서 고객번호, 고객이름, 고객주소, (주문한 총 금액), (주문한 제품 중에 가장 최근에 주문한 상품명)을 추출하시오
-- (주문한 총 금액): 해당고객이 주문상품에서 주문한 상품의 항목가격의 SUM
-- (주문한 제품 중에 가장 최근에주문한 상품명) :주문상품에서 해당고객이 주문한 상품중 가장 최근에 주문한 상품의 상품명 
                                           -- (상품이 여러개일 경우 가나다순으로 가장 큰 상품명을 출력)      
select C.cust_id, TRIM(C.cust_name), TRIM(C.cust_address),
    (select sum(OI.item_price)
    from orders O, orderitems OI
    WHERE O.order_num = OI.order_num
    AND O.cust_id = C.cust_id) as sum_price, 
        (select max(P.prod_name)
        from Orders O, orderitems OI, products P
        WHERE O.order_num = OI.order_num
        AND OI.prod_id = P.prod_id
        AND O.order_date = (
                            select MAX(order_date)
                            from orderitems
                            where cust_id = C.cust_id)) as recent_prod_name
from customers C;

-- 2. 상품을주문한 고객 중에 고객국가,(고객담당자 별 주문수)를 추출하시오
-- 결과: 고객국가,고객담당자, (고객담당자 별 주문수)
-- 조건: 주문수가 2건이상인 경우만 조회

select C.cust_country, C.cust_contact, COUNT(DISTINCT OI.order_num) as order_cnt
from customers C, orderitems OI, orders O
WHERE C.cust_id = O.cust_id
AND O.order_num = OI.order_num
GROUP BY C.cust_country, C.cust_contact
HAVING COUNT(DISTINCT OI.order_num) >= 2;

-- 3. 다음의 조건에 따라 제품의 제품번호, 제품이름, (제품가격수준), 제품설명을 추출하시오
-- (제품가격수준): 제품가격이 3$대이면 ‘적당’ 11$대이면 ‘비쌈’나머지는 ‘보통’
-- 조건: 공급업체의 우편번호 앞의 2자리가 ‘44’,‘45’, ’99’ 인 공급업체에서 만들거나 주문된 날자가 5월이 아닌 제품

select P.prod_id, TRIM(P.prod_name), 
    case when P.prod_price like '3%' then '적당'
          when P.prod_price like '11%' then '비쌈'
          else '보통'
    END price_level , P.prod_desc
from Products P
WHERE EXISTS(
    select *
    from vendors V, orders O
    WHERE V.vend_id = P.vend_id
    AND (SUBSTR(V.vend_zip, 0, 2) IN ('44', '45', '99')) OR (TO_CHAR(O.order_date, 'MM') <> '05')
    )
;

-- 강사님 풀이
SELECT prod_id, TRIM(prod_name) as prod_name,
       DECODE(TRUNC(prod_price), 3, '적당', 11, '비쌈', '보통') as prod_level
FROM   products
WHERE  vend_id IN (
         SELECT vend_id
         FROM   vendors
         WHERE  SUBSTR(vend_zip,1,2) IN ('44','45','99')
       )
OR     prod_id IN (
         SELECT b.prod_id
         FROM   orders a, orderitems b
         WHERE  a.order_num = b.order_num
         AND    TO_CHAR(a.order_date, 'MM') <> '05'
);       

-- 4. 주문상품중에 // 제품의 제품가격(prod_price)보다 주문상품의 항목가격(item_price)이 비싼 항목가격을 // 제품의 제품가격(prod_price)으로 변경하시오

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
    )
;