Search

3. SELECT 기초 - BOOSTER QUIZ 참고 답안

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