coursera_advanced_sql

[woneuy01]

select format(unit_price/100, 2, "en_IN") --인도스타일로 표기 as unit_price_decimal from merchandise_item;

[woneuy01]

select concat(CHAR_LENGTH(description), " chars") --description의 글자수가 몇개인지 from merchandise_item;

[woneuy01]

select concat(" $ ",format(avg(unit_price)/100,2,"en_IN")) from merchandise_item

' $ 461.12'

[woneuy01]

-- Stored Functions

USE world_peace;

DROP FUNCTION IF EXISTS check_credit; 기존에 function을 만들었으면 지우기

DELIMITER $$ -- change delimiter from ; to $$ because there is query ends with ; incide. 그래서 여기서 끝나면 안되므로

CREATE FUNCTION check_credit (
requesting_customer_id CHAR(10), request_amount INT ) -- function에서 변수 2개 사용 RETURNS BOOLEAN

DETERMINISTIC -- 1개의 확정적 값만 가짐

BEGIN

RETURN

(
-- TODO --
-- write an SQL to select the credit_limit
-- from table customer
-- for customer_id matching requesting_customer_id 변수로 넣은 id가 request_amount 보다 credit_limit가 큰지
select credit_limit from customer
where customer_id = requesting_customer_id
) >= request_amount;

END $$

DELIMITER ; -- delimiter 다시 ;로 바꿈

-- check to see if the function works SET @approved = check_credit("C000000001", 4000000); -- once run this 아이디와 금액 변수 지정 SELECT @approved; -- find out the value of @approved

---

1 ( return 값)

[woneuy01]

DROP FUNCTION IF EXISTS get_qoh_ftn; 기존에 같은 function이 만들어져 있으면 지우기

DELIMITER $$

CREATE FUNCTION get_qoh_ftn ( request_item_id CHAR(10) ) -- 변수 하나 RETURNS INT -- 숫자 리턴

DETERMINISTIC

BEGIN

RETURN 
(
-- TODO --
-- write an SQL to select the qoh (quantity on hand)
-- from table merchandise_item
-- for merchandise_item_id matching request_item_id
 select qoh from merchandise_item
 where merchandise_item_id = request_item_id
);

END $$

DELIMITER ;

-- check to see if the function works SET @qty = get_qoh_ftn("KYOTOCBOOK");-- 이 아이디의 값을 변수로 해라 SELECT @qty;

---

123

[woneuy01]

USE world_peace;

DROP PROCEDURE IF EXISTS customer_roster_stp;

DELIMITER $$

CREATE PROCEDURE customer_roster_stp() -- parameter의 input이 없는 procedure

BEGIN

-- TODO --
-- write an SQL to select all the customer
-- from table customer
-- sort it by customer_name
select * from customer
order by customer_name;

END $$

DELIMITER ;

-- check to see if it works CALL customer_roster_stp(); -- 프로시져는 콜로 부름

---

[woneuy01]

DROP PROCEDURE IF EXISTS get_qoh_stp;

DELIMITER $$

CREATE PROCEDURE get_qoh_stp( IN request_item_id CHAR(10), -- input parameter OUT qoh_to_return INT) -- output

BEGIN

-- TODO --
-- write an SQL to select the qoh (quantity on hand)
-- from table merchandise_item
-- for merchandise_item_id matching request_item_id
-- note that your statement will not run yet
select qoh INTO qoh_to_return -- pass to output parameter (update into 처럼 사용됨)
from merchandise_item
where merchandise_item_id=request_item_id;

END$$

DELIMITER ;

SET @qty = 0; -- 처음에 값을 0으로 설정하고 CALL get_qoh_stp("ITALYPASTA", @qty); -- 해당아이디와 결과값 (@qty)으로 procedure 를 call SELECT @qty; -- 결과값 조회

---

10 -- 해당 아이디의 수량은 10

[woneuy01]

USE world_peace;

WITH order_line_item_cte (new_name, new_order_id, new_description, order_qty, new_unit_price, line_subtotal ) AS --칼럼들

(

SELECT customer.customer_name, customer_order_line_item.customer_order_id, merchandise_item.description, customer_order_line_item.quantity, merchandise_item.unit_price / 100 AS "unit_price_decimal", customer_order_line_item.quantity * merchandise_item.unit_price / 100 AS "line_total" FROM customer_order_line_item, customer_order, customer, merchandise_item WHERE customer_order_line_item.merchandise_item_id = merchandise_item.merchandise_item_id AND customer_order.customer_id = customer.customer_id AND customer_order_line_item.customer_order_id = customer_order.customer_order_id ORDER BY customer_name, customer_order_line_item.customer_order_id, merchandise_item.description

---

SELECT * from order_line_item_cte; --CTE 의 모든 항목 조회

[woneuy01]

With customer_cte (customer_id, customer_name) as ( select customer_id, customer_name from customer order by customer_name )

---

select * from customer_cte; -- customer_cte 테이블 조회

[woneuy01]

----trigger

USE world_peace;

DROP TRIGGER IF EXISTS decrease_inventory_tgr; --기존의 trigger를 지우고 싶을때

-- decrease qoh (quantity on hand) after inserting a new line item 새로 오더가 오면 inventory를 줄인다 -- into the table customer_order_line_item

DELIMITER $$ create TRIGGER decrease_inventory_tgr AFTER INSERT ON customer_order_line_item

    FOR EACH ROW
    BEGIN
         UPDATE merchandise_item
         set qoh=qoh -NEW.quantity
         when merchandiase_item_id=NEW.merchandise_item_id;

END$$

DELIMITER ;

-- into the table customer_order_line_item DROP TRIGGER IF EXISTS inventory_check_tgr;

DELIMITER $$

CREATE TRIGGER inventory_check_tgr BEFORE INSERT ON customer_order_line_item FOR EACH ROW

BEGIN DECLARE inventory INT;

CALL get_goh_stp(NEW.merchandise_item_id, inventory);

IF (inventory < NEW.quantity) THEN
    SIGNAL SQLSTATE="45000"
    SET MESSAGE_TEXT="Insufficient inventory";
END IF;

----------check to see if it works

INSERT INTO customer_order_line_item SET custo,er_order_id = "D0000000003", merchandise_item_id="ITALYYPASTA" quantity=20; --남아있는 수량이 20개 보다 적으면 에러 메시지가 뜬다

[woneuy01]

USE world_peace;

WITH RECURSIVE merchandise_cte(merchandise_item_id, depth, description, unit_price_decimal, alpha_sort........짤림 --defining the common table expression as ( -- top level items SELECT merchandise_item_id, 1, --depth CAST(description as CHAR(500)), CAST(unit_price/100 AS DECIMAL(8,2)), CAST(desctiption as CHAR(700)), bundle_id FROM merchandise_item

 UNION ALL

  -- these are the contents of the bundles

   SELECT
          D.merchandise_item_id,
          depth +1,
          CAST(CONCAT (repeat("I_", depth), D.description) AS CHAR(500)),
          CAST(NULL AS DECIMAL(8,2)), --unit_price_decimal
          CAST(CONCAT(C.alpha_sort, " ", D.description) AS CHAR(700)),
          D.bundle_id
     FROM merchandise_cte AS C, merchandise_item AS D
     WHERE C.merchandise_item_id=D.bundle_id

)

--- using the common table expression SELECT * FROM merchandise_cte ORDER BY alpha_sort