뉴딜 일자리 사업 6일차

1. ERD (Entity Relationship Diagram) 실제로 작성해보기

요구사항

• 고객 관리
• 거래처 관리
• 거래처 별 취급 상품 (납품가격)
• 반품 관리
• 상품 관리
• 회원별 거래내역
• 고객별 마일리지 (구매금액 10%)
• 10번 구매시마다 쿠폰 발행
• 기준이 되는 데이터를 잘 설정해야 한다.
• DATETIME 은 PK 로 잡지 말아야 할 column중의 하나임. (유일한 값이 아님)

2. 1번에서 만든 ERD를 가지고서 SQL query문 연습

문제 1 : 특정기간 동안 구매 제일 많은 5명

JINSU SON

SELECT ci.custum_name
    FROM tb_custom_info ci
             INNER JOIN (
                                SELECT custum_cd, DATE_FORMAT(buy_date, '%Y.%m.%d') AS buy_date
                              FROM tb_sell
                            ) ll ON ci.custum_cd = ll.custum_cd
WHERE ll.buy_date BETWEEN '2018.09.11' AND '2018.10.11'
ORDER BY ci.buy_cnt DESC
LIMIT 5;

Kim Hyuk

-- 횟수
SELECT ci.cust_code, ci.cust_name, SUM(ci.buy_cnt) AS '총 구매횟수' LIMIT 5
    FROM tb_cust_info AS ci
        LEFT JOIN tb_self AS ts ON ci.cust_code = tb.cust_code
    WHERE ts.sell_date BETWEEN s_date and e_date + 1
    ORDER BY ci.buy_cnt DESC;

-- -----------------------------------------------------------------------------

-- 금액기준
SELECT ci.cust_code, ci.cust_name, SUM(ts.amt) AS 구매금액 LIMIT 5
    FROM tb_cust_info AS ci
        LEFT JOIN tb_self AS ts ON ci.cust_code = tb.cust_code
    WHERE ts.sell_date BETWEEN s_date and e_date + 1
    ORDER BY ts.amt DESC;

-- ------------------------------------------------------------

SELECT ts.cust_code, ci.cust_name, SUM(ts.amt)
    FROM tb_sell AS ts
        INNER JOIN (
                                SELECT cust_code, cust_name
                                  FROM tb_cust_info
                            ) AS ci ON ts.cust_code = ci.cust_code
    WHERE ts.sell_date BETWEEN s_date and e_date + 1
    GROUP BY ts.cust_code
    ORDER BY SUM(ts.amt) DESC LIMIT 5;

팀장님 query

select chain_cd, cust_cd, amt
  from (  
        select chain_cd, cust_cd, sum(amt) amt
          from tb_sell
         where bye_date between '20200101' and '20201231'
         group by chain_cd, cust_cd 
       ) ll
 order by amt desc
 limit 0,5

문제 2 : 특정기간 동안 총 매출액과 총 발주액을 뽑아서 손익 금액 구하기

JINSU SON

SELECT s.tot_amt
            ,o.tot_order_amt
            ,(s.tot_amt - o.tot_order_amt) as profit_and_loss
FROM (
            SELECT goods_cd, SUM(amt) as tot_amt 
              FROM tb_sell
             WHERE buy_date BETWEEN '20180911' AND '20181111'
         ) s
INNER JOIN (
                        SELECT goods_cd, SUM(order_amt) as tot_order_amt 
                  FROM tb_order
                 WHERE order_date BETWEEN '20180911' AND '20181111'
           ) o ON s.goods_cd = o.goods_cd

Kim Hyuk

SELECT SUM(ts.amt) AS '총 매출액', SUM(tb_o.order_amt) AS '총 발주액', SUM(ts.amt) - SUM(tb_o.order_amt)
    FROM tb_sell AS ts
        LEFT JOIN tb_order tb_o ON ts.goods_code = tb_o.goods_code
    WHERE ts.sell_date BETWEEN s_date and e_date AND tb_o.order_date BETWEEN s_date and e_date;

팀장님 query

select order.order_amt, sell.amt, (sell.amt - order.order_amt) as betamt 
  from 
      (
        select sum(order_amt) as order_amt
         from tb_order
        where order_date between '20200101' and '20201231'       
      ) order
     ,(
       select sum(amt) as amt
         from tb_sell
        where bye_date between '20200101' and '20201231'
      ) sell

-- -----------------------------------------------------------------------------

-- 지점에 따른 손익

select order.chain_cd, order.order_amt, sell.amt, (sell.amt - order.order_amt) as betamt 
  from 
      (
        select chain_cd, sum(order_amt) as order_amt
         from tb_order
        where order_date between '20200101' and '20201231'       
        group by chain_cd
      ) order 
     ,(
       select chain_cd, sum(amt) as amt
         from tb_sell
        where bye_date between '20200101' and '20201231'
        group by chain_cd
      ) sell 
where order.chain_cd = sell.chain_cd

문제 3 : 제일 많이 팔린 상품 Top 3

JINSU SON

SELECT gi.goods_name, COUNT(gi.goods_name) as cnt
  FROM tb_sell s
             INNER JOIN tb_goods_info gi ON s.goods_cd = gi.goods_cd
GROUP BY gi.goods_name
ORDER BY cnt DESC
LIMIT 3;

Kim Hyuk

SELECT ts.goods_code, gi.goods_name
    FROM tb_sell AS ts
        LEFT JOIN (
                            SELECT goods_code, goods_name
                              FROM tb_goods_info
                        ) AS gi ON ts.goods_code = gi.goods_code
    GROUP BY ts.goods_code
    ORDER BY COUNT(ts.goods_code) DESC LIMIT 3;

팀장님 query

select  ll.goods_cd
      , gi.goods_name
      , ll.cnt
  from (
         select goods_cd, count(goods_cd) as cnt
           from tb_sell
       ) ll
       inner join tb_goodsinfo gi on gi.goods_cd = ll.goods_cd
 order by ll.cnt desc
 limit 0,3

문제 4 : 매출 순위 제일 높은 Top 3 지점명

JINSU SON

SELECT s.amt
FROM (
                SELECT chain_cd, SUM(amt) as amt
              FROM tb_sell
                         GROUP BY chain_cd
         ) s,
         (
             SELECT chain_cd, chain_name
            FROM tb_chain
         ) c
WHERE s.chain_cd = c.chain_cd
ORDER BY s.amt DESC
LIMIT 3;

Kim Hyuk

팀장님 query

문제 5 : 쿠폰 발행을 많이 받은 회원 Top 5

JINSU SON

SELECT ci.custom_name
FROM tb_custom_info ci, (
                          SELECT custum_cd, COUNT(custum_cd) AS cnt 
                          FROM tb_coupon_history 
                          GROUP BY custum_cd
                       ) ll
WHERE ci.custum_id = ll.custum_id
ORDER BY cnt DESC
LIMIT 5;

Kim Hyuk

SELECT ch.cust_code, ci.cust_name
    FROM tb_coupon_his AS ch 
        LEFT JOIN tb_cust_info AS ci
            ON ch.cust_code = ci.cust_code 
    GROUP BY ch.cust_code 
    ORDER BY COUNT(ch.cust_code) DESC LIMIT 5;

-- -----------------------------------------------------------------------------

-- 서브쿼리
SELECT ch.cust_code, ci.cust_name
    FROM tb_coupon_his AS ch
        INNER JOIN (
                                SELECT cust_code, cust_name
                                  FROM tb_cust_info
                            ) ci ON ch.cust_code = ci.cust_code
  GROUP BY ch.cust_code
  ORDER BY COUNT(ch.cust_code) DESC LIMIT 5;

팀장님 query

select ll.chain_cd
     , ch.chain_name
     , ll.cust_cd
     , ci.cust_name
     , ll.cust_cnt
 from 
      (
         select chain_cd, cust_cd, count(cust_cd) as cust_cnt
           from tb_coupon_his
          group by chain_cd, cust_cd
      ) ll
      inner join tb_chain ch on ch.chain_cd = ll.chain_cd
      inner join tb_custinfo ci on ci.cust_cd = ll.cust_cd
order by cust_cnt desc
limit 0,5

3. 커피숍 ERD 만들어보기