------------------------------------------------------------------------
-- BOOSTER QUIZ 3-3-1
------------------------------------------------------------------------

답안)
    SELECT  *
    FROM    startdbmy.ms_item
    WHERE   item_id = 'AMB';

------------------------------------------------------------------------
-- BOOSTER QUIZ 3-3-2
------------------------------------------------------------------------   

답안)
    SELECT  item_id ,item_nm ,item_cat
    FROM    startdbmy.ms_item
    WHERE   item_cat = 'BEV';
    

------------------------------------------------------------------------
-- BOOSTER QUIZ 3-4-1
------------------------------------------------------------------------

답안)
    SELECT  item_id ,item_nm ,item_cat ,item_size_cd ,hot_cold_cd
    FROM    startdbmy.ms_item
    WHERE   item_cat = 'COF'
    AND     item_size_cd = 'BIG'
    AND     hot_cold_cd = 'HOT';

------------------------------------------------------------------------
-- BOOSTER QUIZ 3-4-2
------------------------------------------------------------------------

    SELECT  item_id ,item_nm ,item_cat ,item_size_cd ,hot_cold_cd
    FROM    startdbmy.ms_item
    WHERE   item_size_cd = 'REG'
    AND     (item_cat = 'BEV' OR item_cat = 'BKR');

------------------------------------------------------------------------
-- BOOSTER QUIZ 3-6-1
------------------------------------------------------------------------

    SELECT  item_id ,item_nm ,item_cat
    FROM    startdbmy.ms_item
    WHERE   item_id BETWEEN 'BMFR' AND 'CLR';

------------------------------------------------------------------------
-- BOOSTER QUIZ 3-6-2
------------------------------------------------------------------------

    SELECT  item_id ,item_nm ,item_cat
    FROM    startdbmy.ms_item
    WHERE   hot_cold_cd = 'HOT'
    AND     item_nm LIKE '%(R)';

------------------------------------------------------------------------
-- BOOSTER QUIZ 3-6-3
------------------------------------------------------------------------

    SELECT  item_id ,item_nm ,item_cat ,item_size_cd
    FROM    startdbmy.ms_item
    WHERE   item_size_cd = 'BIG'
    AND     item_cat IN ('COF','BEV')
    AND     item_nm NOT LIKE 'Iced%';

------------------------------------------------------------------------
-- BOOSTER QUIZ 3-7-1
------------------------------------------------------------------------

    SELECT  item_id ,item_nm ,item_cat ,hot_cold_cd
    FROM    startdbmy.ms_item
    WHERE   item_cat = 'COF'
    AND     hot_cold_cd = 'HOT'
    ORDER BY item_nm DESC;

------------------------------------------------------------------------
-- BOOSTER QUIZ 3-7-2
------------------------------------------------------------------------

    SELECT  item_id ,item_nm ,item_cat ,hot_cold_cd
    FROM    startdbmy.ms_item
    WHERE   item_id BETWEEN 'A' AND 'C'
    ORDER BY hot_cold_cd DESC ,item_id DESC;

------------------------------------------------------------------------
-- BOOSTER QUIZ 3-9-1
------------------------------------------------------------------------

    SELECT  t1.item_id 상품ID ,t1.item_nm 상품명 ,t1.item_cat 상품종류 ,t1.hot_cold_cd `냉/온구분`
    FROM    startdbmy.ms_item t1
    WHERE   t1.item_cat = 'BKR'
    ORDER BY t1.item_nm DESC;

------------------------------------------------------------------------
-- BOOSTER QUIZ 3-10-1
------------------------------------------------------------------------

  ㅁ CHAR_LENGTH: 입력된 문자열의 문자수를 반환합니다.
  ㅁ REPEAT: 입력된 문자열을 주어진 횟수만큼 반복합니다.
  ㅁ TRIM: 입력된 문자열의 앞뒤 공백을 제거합니다.

------------------------------------------------------------------------
-- BOOSTER QUIZ 3-10-2
------------------------------------------------------------------------

    SELECT  t1.item_id ,LPAD(t1.item_id,10,'0') new_item_id ,t1.item_nm
    FROM    startdbmy.ms_item t1;

------------------------------------------------------------------------
-- BOOSTER QUIZ 3-11-1
------------------------------------------------------------------------

    SELECT  t1.ord_no ,t1.shop_id ,t1.mbr_id ,t1.ord_dtm ,DATE_FORMAT(t1.ord_dtm,'%Y%m') ord_ym
    FROM    startdbmy.tr_ord t1
    WHERE   t1.shop_id = 'S003'
    AND     t1.mbr_id = 'M2942'
    AND     t1.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20230101','%Y%m%d')
    ORDER BY t1.ord_dtm ASC;
    
    
    
------------------------------------------------------------------------
-- BOOSTER QUIZ 3-11-2
------------------------------------------------------------------------

    SELECT  t1.ord_no ,t1.ord_dtm ,t1.pkup_dtm
            ,TIMESTAMPDIFF(MINUTE, t1.ord_dtm, t1.pkup_dtm)  `픽업까지걸린시간(분)`
    FROM    startdbmy.tr_ord t1
    WHERE   t1.ord_dtm >= STR_TO_DATE('20220601','%Y%m%d')
    AND     t1.ord_dtm <  STR_TO_DATE('20220602','%Y%m%d')
    AND     t1.shop_id = 'S023'
    ORDER BY `픽업까지걸린시간(분)`
    
    
------------------------------------------------------------------------
-- BOOSTER QUIZ 3-11-3
------------------------------------------------------------------------

    SELECT  t1.mbr_id ,t1.join_dtm ,t1.leave_dtm 
            ,DATE_FORMAT(t1.join_dtm,'%Y%m') 가입월 ,DATE_FORMAT(t1.leave_dtm,'%Y%m') 탈퇴월
            ,DATEDIFF(t1.leave_dtm,t1.join_dtm) 회원유지일수
    FROM    startdbmy.ms_mbr t1
    WHERE   t1.leave_dtm >= STR_TO_DATE('20210801','%Y%m%d')
    AND     t1.leave_dtm <  STR_TO_DATE('20210901','%Y%m%d')
    ORDER BY 회원유지일수 ASC;
    
    
    
    
------------------------------------------------------------------------
-- BOOSTER QUIZ 3-11-4
------------------------------------------------------------------------

    SELECT  t1.ord_no ,t1.shop_id ,t1.ord_dtm
    FROM    startdbmy.tr_ord t1
    WHERE   t1.ord_dtm >= STR_TO_DATE('2021-01-03','%Y-%m-%d')
    AND     t1.ord_dtm <  DATE_ADD(STR_TO_DATE('2021-01-03','%Y-%m-%d'), interval 1 day)
    AND     t1.shop_id = 'S001'
    ORDER BY t1.ord_no;