------------------------------------------------------------------------
-- 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;
SQL
복사