Search

7. JOIN - BOOSTER QUIZ 참고 답안

------------------------------------------------------------------------ -- BOOSTER QUIZ 7-1-1 ------------------------------------------------------------------------(주문) 테이블은 매장 테이블을 참조합니다. ㅁ 바꿔 말하면, 매장 테이블은 (주문) 테이블에 참조된다는 뜻입니다 ㅁ 그러므로 (주문) 테이블에 발생되는 데이터의 매장ID는 매장 테이블에 존재해야 합니다. ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-1-2 ------------------------------------------------------------------------(tr_ord) 테이블은 ms_mbr를 참조합니다. ㅁ 이는 ms_mbr 테이블의 (PK)인 mbr_id를 (tr_ord) 테이블에서 FK로 사용하고 있다는 뜻입니다. ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-1-3 ------------------------------------------------------------------------ ㅁ 논리 ERD의 주문상세, 상품, 상품카테고리를 참고해서 답하세요.(주문상세) 테이블은 주문 테이블을 참조합니다.(주문상세) 테이블의 PK는 주문번호와 (주문상세번호)입니다. ㅇ 하나의 주문에 여러 개의 상세 내역이 있을 수 있다는 뜻입니다.(주문상세) 테이블은 주문 테이블과 함께 (상품) 테이블도 참조하고 있습니다.(상품) 테이블의 PK 인 상품ID가 (주문상세) 테이블에 FK 컬럼으로 존재합니다. ㅁ 정리하면, 주문에 대해 어떤 상품이 주문 되었는지 알려면 (주문상세) 테이블을 확인해야 합니다. ㅇ 그리고 하나의 주문에는 여러 (상품)의 주문이 가능합니다. ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-1-3 ------------------------------------------------------------------------ ㅁ tr_ord 테이블은 tr_ord_det 테이블에 참조(된다) ㅇ 이는 tr_ord 테이블의 PK 인 ord_no가 tr_ord_det 테이블에서 (사용) 되고 있다는 뜻입니다. ㅁ 만약에 ord_no가 100 인 신규 주문이 저장되려면, (tr_ord)에 먼저 저장되어야 합니다. ㅇ 그래야만 (tr_ord_det) 에도 ord_no가 100인 데이터를 생성할 수 있습니다. ㅁ tr_ord_det 테이블의 PK는 ord_no와 ord_det_no로 구성되어 있습니다. ㅇ 이는 ord_no별로 여러 건의 상세 데이터를 저장할 수 (있다)는 뜻입니다. ㅇ 그리고 tr_ord_det 테이블은 ms_item 테이블의 item_id를 참조 (합니다) ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-2-1 ------------------------------------------------------------------------ ㅁ 아래 SQL에서 조인 조건에 해당하는 번호를 모두 적으세요. (3) ㅁ 아래 SQL에서 ms_mbr 테이블의 필터 조건에 해당하는 번호를 모두 적으세요. (4) ㅁ 아래 SQL에서 tr_ord 테이블의 필터 조건에 해당하는 번호를 모두 적으세요. (1),(2),(5) ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-2-2 ------------------------------------------------------------------------(SQL-3)을 실행하면 nick_nm이 Air인 데이터는 몇 건인가요? 2건 ㅁ (SQL-3)을 실행하면 nick_nm이 Water1인 데이터는 몇 건인가요? 3------------------------------------------------------------------------ -- BOOSTER QUIZ 7-2-3 ------------------------------------------------------------------------(SQL-3)의 실행 결과에 포함되지 않는 ms_mbr 테이블의 mbr_id는? M3017, M3196 ㅁ (SQL-3)의 실행 결과에 포함되지 않는 tr_ord 테이블의 ord_no는? 254529, 254829, 254830 ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-2-4 ------------------------------------------------------------------------ SELECT t1.mbr_id ,t1.nick_nm ,t1.mbr_gd ,t2.ord_no ,t2.ord_dtm ,t2.shop_id ,t2.ord_amt FROM startdbmy.ms_mbr t1 ,startdbmy.tr_ord t2 WHERE t1.mbr_gd = 'GOLD' AND t2.shop_id = 'S002' AND t2.ord_dtm >= STR_TO_DATE('20221001','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20221002','%Y%m%d') AND t2.mbr_id = t1.mbr_id ORDER BY t1.mbr_id; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-3-1 ------------------------------------------------------------------------ SELECT t1.ord_no ,t1.ord_dtm ,t1.shop_id ,t2.ord_det_no ,t2.item_id ,t2.ord_qty ,t2.sale_prc FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.tr_ord_det t2 ON (t2.ord_no = t1.ord_no) WHERE t1.shop_id = 'S062' AND t1.ord_dtm >= STR_TO_DATE('20230331:07','%Y%m%d:%H') AND t1.ord_dtm < STR_TO_DATE('20230331:12','%Y%m%d:%H') ORDER BY t1.ord_no, t1.ord_det_no; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-3-2 ------------------------------------------------------------------------ SELECT t1.ord_no, t1.ord_dtm, t1.shop_id ,t2.ord_det_no, t2.item_id, t2.ord_qty, t2.sale_prc FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.tr_ord_det t2 ON (t2.ord_no = t1.ord_no) WHERE t1.ord_dtm >= STR_TO_DATE('20230101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230102','%Y%m%d') AND t1.shop_id = 'S230' AND t2.item_id = 'AMB' ORDER BY t1.ord_no; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-3-3 ------------------------------------------------------------------------ SELECT t1.ord_no ,t1.ord_det_no ,t1.ord_qty ,t1.sale_prc ,t1.item_id ,t2.item_nm FROM startdbmy.tr_ord_det t1 INNER JOIN startdbmy.ms_item t2 ON (t2.item_id = t1.item_id) WHERE t1.ord_no = 91810 ORDER BY t1.ord_det_no; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-5-1 ------------------------------------------------------------------------ SELECT t1.shop_oper_tp ,SUM(t2.ord_amt) ord_amt_sum FROM startdbmy.ms_shop t1 INNER JOIN startdbmy.tr_ord t2 ON (t2.shop_id = t1.shop_id) WHERE t2.ord_dtm >= STR_TO_DATE('20220101','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20220201','%Y%m%d') GROUP BY t1.shop_oper_tp ORDER BY t1.shop_oper_tp; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-5-2 ------------------------------------------------------------------------ SELECT t1.shop_oper_tp ,DATE_FORMAT(t2.ord_dtm,'%Y') ord_year ,COUNT(*) ord_cnt FROM startdbmy.ms_shop t1 INNER JOIN startdbmy.tr_ord t2 ON (t2.shop_id = t1.shop_id) WHERE t1.shop_st = 'CLSD' GROUP BY t1.shop_oper_tp ,DATE_FORMAT(t2.ord_dtm,'%Y') ORDER BY t1.shop_oper_tp ,DATE_FORMAT(t2.ord_dtm,'%Y'); ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-5-3 ------------------------------------------------------------------------ SELECT t1.shop_id ,MAX(t1.shop_nm) shop_nm ,MAX(t1.shop_size) shop_size ,SUM(t2.ord_amt) ord_amt_sum ,SUM(t2.ord_amt) / MAX(t1.shop_size) ord_amt_per_size FROM startdbmy.ms_shop t1 INNER JOIN startdbmy.tr_ord t2 ON (t2.shop_id = t1.shop_id) WHERE t1.shop_start_ymd = '20180405' AND t2.ord_dtm >= STR_TO_DATE('20191223','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20191224','%Y%m%d') GROUP BY t1.shop_id ORDER BY MAX(t1.shop_size) DESC; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-7-1 ------------------------------------------------------------------------ SELECT t1.ord_no ,t1.ord_dtm ,t2.shop_nm ,t3.nick_nm ,t4.ord_det_no ,t4.ord_qty ,t5.item_nm FROM startdbmy.tr_ord t1 INNER JOIN startdbmy.ms_shop t2 ON (t2.shop_id = t1.shop_id) INNER JOIN startdbmy.ms_mbr t3 ON (t3.mbr_id = t1.mbr_id) INNER JOIN startdbmy.tr_ord_det t4 ON (t4.ord_no = t1.ord_no) INNER JOIN startdbmy.ms_item t5 ON (t5.item_id = t4.item_id) WHERE t1.ord_no = 1 ORDER BY t4.ord_det_no; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-7-2 ------------------------------------------------------------------------ SELECT t2.ord_no ,t2.ord_dtm ,t1.shop_nm ,t1.shop_size ,t4.item_id ,t5.item_nm ,t4.ord_qty FROM startdbmy.ms_shop t1 INNER JOIN startdbmy.tr_ord t2 ON (t2.shop_id = t1.shop_id) INNER JOIN startdbmy.ms_mbr t3 ON (t3.mbr_id = t2.mbr_id) INNER JOIN startdbmy.tr_ord_det t4 ON (t4.ord_no = t2.ord_no) INNER JOIN startdbmy.ms_item t5 ON (t5.item_id = t4.item_id) WHERE t1.shop_oper_tp = 'DRCT' AND t1.shop_size <= 100 AND t2.ord_dtm >= STR_TO_DATE('20221224','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20221225','%Y%m%d') AND t3.mbr_gd = 'PLAT' ORDER BY t2.ord_no ,t4.ord_det_no; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-7-3 ------------------------------------------------------------------------ SELECT t4.item_id ,MAX(t5.item_nm) item_nm ,SUM(t4.ord_qty) ord_qty_sum FROM startdbmy.ms_shop t1 INNER JOIN startdbmy.tr_ord t2 ON (t2.shop_id = t1.shop_id) INNER JOIN startdbmy.ms_mbr t3 ON (t3.mbr_id = t2.mbr_id) INNER JOIN startdbmy.tr_ord_det t4 ON (t4.ord_no = t2.ord_no) INNER JOIN startdbmy.ms_item t5 ON (t5.item_id = t4.item_id) WHERE t1.shop_oper_tp = 'DRCT' AND t1.shop_size <= 100 AND t2.ord_dtm >= STR_TO_DATE('20221224','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20221225','%Y%m%d') AND t3.mbr_gd = 'PLAT' GROUP BY t4.item_id ORDER BY ord_qty_sum DESC; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-8-1 ------------------------------------------------------------------------ SELECT t1.mbr_id ,t1.nick_nm ,t1.join_dtm ,t2.ord_dtm ,t4.item_id ,t4.prc_start_dt ,t4.sale_prc FROM startdbmy.ms_mbr t1 INNER JOIN startdbmy.tr_ord t2 ON (t2.mbr_id = t1.mbr_id) INNER JOIN startdbmy.tr_ord_det t3 ON (T3.ord_no = t2.ord_no) INNER JOIN startdbmy.ms_item_prc_hist t4 ON (t4.item_id = t3.item_id AND t4.prc_start_dt <= DATE(t2.ord_dtm) AND t4.prc_end_dt >= DATE(t2.ord_dtm) ) WHERE t1.mbr_id = 'M4547' AND t2.ord_dtm >= STR_TO_DATE('20240105','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20240106','%Y%m%d') ORDER BY t2.ord_dtm ,t4.item_id; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-8-2 ------------------------------------------------------------------------ SELECT t1.mbr_id ,t1.nick_nm ,t1.join_dtm ,t2.ord_dtm ,t4.item_id ,t4.prc_start_dt ,t4.sale_prc ,t5.sale_prc prc_join FROM startdbmy.ms_mbr t1 INNER JOIN startdbmy.tr_ord t2 ON (t2.mbr_id = t1.mbr_id) INNER JOIN startdbmy.tr_ord_det t3 ON (T3.ord_no = t2.ord_no) INNER JOIN startdbmy.ms_item_prc_hist t4 ON (t4.item_id = t3.item_id AND t4.prc_start_dt <= DATE(t2.ord_dtm) AND t4.prc_end_dt >= DATE(t2.ord_dtm) ) INNER JOIN startdbmy.ms_item_prc_hist t5 ON (t5.item_id = t3.item_id AND t5.prc_start_dt <= DATE(t1.join_dtm) AND t5.prc_end_dt >= DATE(t1.join_dtm) ) WHERE t1.mbr_id = 'M4547' AND t2.ord_dtm >= STR_TO_DATE('20240105','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20240106','%Y%m%d') ORDER BY t2.ord_dtm ,t4.item_id; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-9-1 ------------------------------------------------------------------------ SELECT t1.mbr_id ,t1.nick_nm ,t2.ord_no ,t2.ord_dtm FROM startdbmy.ms_mbr t1 LEFT OUTER JOIN startdbmy.tr_ord t2 ON (t2.mbr_id = t1.mbr_id AND t2.ord_dtm >= STR_TO_DATE('20200101','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20200104','%Y%m%d')) WHERE t1.nick_nm IN ('Air','Sky','Space') ORDER BY t1.mbr_id ,t2.ord_no; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-9-2 ------------------------------------------------------------------------ SELECT t1.mbr_id ,t1.nick_nm ,t2.ord_no ,t2.ord_dtm ,t3.ord_det_no ,t3.item_id ,t4.item_nm FROM startdbmy.ms_mbr t1 LEFT OUTER JOIN startdbmy.tr_ord t2 ON (t2.mbr_id = t1.mbr_id AND t2.ord_dtm >= STR_TO_DATE('20200101','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20200104','%Y%m%d')) LEFT OUTER JOIN startdbmy.tr_ord_det t3 ON (t3.ord_no = t2.ord_no) LEFT OUTER JOIN startdbmy.ms_item t4 ON (t4.item_id = t3.item_id) WHERE t1.nick_nm IN ('Air','Sky','Space') ORDER BY t1.mbr_id ,t2.ord_no; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-10-1 ------------------------------------------------------------------------ SELECT t1.mbr_id ,MAX(t1.nick_nm) nick_nm ,COUNT(t2.ord_no) ord_cnt ,IFNULL(SUM(t2.ord_amt),0) ord_amt_sum FROM startdbmy.ms_mbr t1 LEFT OUTER JOIN startdbmy.tr_ord t2 ON (t2.mbr_id = t1.mbr_id AND t2.ord_dtm >= STR_TO_DATE('20200101','%Y%m%d') AND t2.ord_dtm < STR_TO_DATE('20200104','%Y%m%d')) WHERE t1.nick_nm IN ('Air','Sky','Space') GROUP BY t1.mbr_id ORDER BY t1.mbr_id; ------------------------------------------------------------------------ -- BOOSTER QUIZ 7-12-1 ------------------------------------------------------------------------ SELECT DATE_FORMAT(t1.ord_dtm,'%Y') ord_yy ,'주문건수' 값구분 ,COUNT(*) val FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20200101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20240101','%Y%m%d') GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y') UNION ALL SELECT DATE_FORMAT(t1.ord_dtm,'%Y') ord_yy ,'주문존재회원수' 값구분 ,COUNT(DISTINCT t1.mbr_id) val FROM startdbmy.tr_ord t1 WHERE t1.ord_dtm >= STR_TO_DATE('20200101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20240101','%Y%m%d') GROUP BY DATE_FORMAT(t1.ord_dtm,'%Y') ORDER BY ord_yy ,값구분;
SQL
복사