------------------------------------------------------------------------
-- 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 ,값구분;