SQL 문제 풀이 (16번~20번)

문제 풀이 DB : https://thinking-it.tistory.com/12

 

[16번 문제] 월별 매출액을 구하라

SELECT DECODE(idx, 'list', item_name, '총합계') 상품명
     , SUM(mm_01) "1월"
     , SUM(mm_02) "2월"
     , SUM(mm_03) "3월"
     , SUM(mm_04) "4월"
     , SUM(mm_05) "5월"
     , SUM(mm_06) "6월"
     , SUM(mm_07) "7월"
     , SUM(mm_08) "8월"
     , SUM(mm_09) "9월"
     , SUM(mm_10) "10월"
     , SUM(mm_11) "11월"
     , SUM(mm_12) "12월"
     , SUM(amount) "합계"
  FROM (
       SELECT d.item_name
            , SUM(CASE WHEN d.mm = '01' THEN amount ELSE 0 END) mm_01
            , SUM(CASE WHEN d.mm = '02' THEN amount ELSE 0 END) mm_02
            , SUM(CASE WHEN d.mm = '03' THEN amount ELSE 0 END) mm_03
            , SUM(CASE WHEN d.mm = '04' THEN amount ELSE 0 END) mm_04
            , SUM(CASE WHEN d.mm = '05' THEN amount ELSE 0 END) mm_05
            , SUM(CASE WHEN d.mm = '06' THEN amount ELSE 0 END) mm_06
            , SUM(CASE WHEN d.mm = '07' THEN amount ELSE 0 END) mm_07
            , SUM(CASE WHEN d.mm = '08' THEN amount ELSE 0 END) mm_08
            , SUM(CASE WHEN d.mm = '09' THEN amount ELSE 0 END) mm_09
            , SUM(CASE WHEN d.mm = '10' THEN amount ELSE 0 END) mm_10
            , SUM(CASE WHEN d.mm = '11' THEN amount ELSE 0 END) mm_11
            , SUM(CASE WHEN d.mm = '12' THEN amount ELSE 0 END) mm_12
            , SUM(amount) amount
         FROM (
              SELECT SUBSTR(a.ord_date, 5, 2) mm
                   , c.item_name
                   , b.amount
                FROM burger_ord a
                   , burger_ord_item b
                   , burger_item c
               WHERE a.store_code = b.store_code
                 AND a.ord_code = b.ord_code
                 AND a.ord_date LIKE ('2017%')
                 AND b.item_code = c.item_code
               ) d
               GROUP BY d.item_name
       ) a
       , (SELECT 'list' idx FROM dual
 UNION ALL
 SELECT 'sum' idx FROM dual) b
 GROUP BY idx, DECODE(idx, 'list', item_name, '총합계')
 ORDER BY idx;

※결과값※

 

[17번 문제]  시간대별 매출을 구하라

SELECT CASE WHEN b.idx = 1 THEN a.store_code
            WHEN b.idx = 2 THEN store_code END 지점
     , CASE WHEN b.idx = 1 AND store_code = 'S01' THEN '성수점'
            WHEN b.idx = 2 AND store_code = 'S01' THEN '성수점 합계'
            WHEN b.idx = 1 AND store_code = 'S02' THEN '강북점'
            WHEN b.idx = 2 AND store_code = 'S02' THEN '강북점 합계'
            WHEN b.idx = 1 AND store_code = 'S03' THEN '강남점' 
            WHEN b.idx = 2 AND store_code = 'S03' THEN '강남점 합계'
            WHEN b.idx = 1 AND store_code = 'S04' THEN '수원점'
            WHEN b.idx = 2 AND store_code = 'S04' THEN '수원점 합계' END 주소
     , CASE WHEN b.idx = 1 THEN a.emp_name
            WHEN b.idx = 2 THEN '' END 사원
     , SUM(a.h_00_06) "00~03(05:59)"
     , SUM(a.h_06_13) "06~13(12:59)"
     , SUM(a.h_13_20) "13~20(19:59)"
     , SUM(a.h_20_24) "20~24"
  FROM (
       SELECT d.store_code
            , d.emp_name
            , SUM(CASE WHEN d.ord_time BETWEEN '0000' AND '0559' THEN amount END) h_00_06
            , SUM(CASE WHEN d.ord_time BETWEEN '0600' AND '1259' THEN amount END) h_06_13
            , SUM(CASE WHEN d.ord_time BETWEEN '1300' AND '1959' THEN amount END) h_13_20
            , SUM(CASE WHEN d.ord_time BETWEEN '2000' AND '2400' THEN amount END) h_20_24
         FROM (
              SELECT b.store_code
                   , a.emp_name
                   , b.ord_time
                   , c.amount
                FROM burger_emp a
                   , burger_ord b
                   , burger_ord_item c
               WHERE a.store_code = b.store_code
                 AND b.store_code = c.store_code
                 AND b.ord_code = c.ord_code
                 AND a.emp_code = b.emp_code
              ) d
          GROUP BY d.store_code
              , d.emp_name
              ) a
              , (
                SELECT 1 idx FROM dual
                UNION ALL
                SELECT 2 idx FROM dual
                ) b
          GROUP BY idx
              , a.store_code
              , CASE WHEN b.idx = 1 THEN emp_name WHEN b.idx = 2 THEN '' END
          ORDER BY a.store_code;

※결과값※

※전체 합계까지 구하는 SQL※

SELECT store_code
     , emp_name
     , hh_00
     , hh_06
     , hh_13
     , hh_20
  FROM (
SELECT CASE WHEN idx IN (1, 2) THEN store_code ELSE '합계' END store_code
     , CASE WHEN idx IN (1) THEN emp_name ELSE '지점합계' END emp_name
     , CASE WHEN idx IN (1, 2) THEN '00' || store_code ELSE '99합계' END store_ord
     , CASE WHEN idx IN (1) THEN '00' || emp_name ELSE '99지점합계' END emp_ord
     , SUM(hh_00) hh_00
     , SUM(hh_06) hh_06
     , SUM(hh_13) hh_13
     , SUM(hh_20) hh_20
  FROM (
       SELECT b.store_code
            , a.emp_name
            , SUM(CASE WHEN substr(b.ord_time, 1, 2) BETWEEN 00 AND 05 THEN c.amount ELSE 0 END) HH_00
            , SUM(CASE WHEN substr(b.ord_time, 1, 2) BETWEEN 06 AND 12 THEN c.amount ELSE 0 END) HH_06
            , SUM(CASE WHEN substr(b.ord_time, 1, 2) BETWEEN 13 AND 19 THEN c.amount ELSE 0 END) HH_13
            , SUM(CASE WHEN substr(b.ord_time, 1, 2) BETWEEN 20 AND 23 THEN c.amount ELSE 0 END) HH_20
         FROM burger_emp a
            , burger_ord b
            , burger_ord_item c
        WHERE a.store_code = b.store_code
          AND b.store_code = c.store_code
          AND b.ord_code = c.ord_code
          AND a.emp_code = b.emp_code
        GROUP BY b.store_code, a.emp_name
        ) A
        , (
          SELECT 1 idx FROM dual
          UNION ALL
          SELECT 2 idx FROM dual
          UNION ALL
          SELECT 3 idx FROM dual
          )
      GROUP BY idx
          , CASE WHEN idx IN (1, 2) THEN store_code ELSE '합계' END 
          , CASE WHEN idx IN (1) THEN emp_name ELSE '지점합계' END 
          , CASE WHEN idx IN (1, 2) THEN '00' || store_code ELSE '99합계' END 
          , CASE WHEN idx IN (1) THEN '00' || emp_name ELSE '99지점합계' END 
      ORDER BY store_ord, emp_ord
      )

※결과값※

 

 

[18번 문제] 2018년 한 해 동안 오후 시간대 기준으로 매출이 가장 높았던 시간대와 매출을 구하기 (1시간 간격 기준, top3까지)

SELECT time 시간대
     , amt 매출
 FROM (
      SELECT SUBSTR(a.ord_time, 1, 2) time
           , SUM(b.amount) amt
        FROM burger_ord a
        FULL OUTER JOIN burger_ord_item b
          ON a.ord_code = b.ord_code
       WHERE SUBSTR(a.ord_time, 1, 2) BETWEEN 13 AND 23
         AND SUBSTR(a.ord_date, 1, 4) = 2018
       GROUP BY SUBSTR(a.ord_time, 1, 2)
       ORDER BY amt DESC
       ) c
WHERE ROWNUM <= 3;

※결과값※

 

[19번 문제] 2017년 품목별 매출합계 구하기

SELECT SUM(DECODE(a.item_name, '콜라', a.amount, 0)) 콜라
     , SUM(DECODE(a.item_name, '사이다', a.amount, 0)) 사이다
     , SUM(DECODE(a.item_name, '환타', a.amount, 0)) 환타
     , SUM(DECODE(a.item_name, '마운틴듀', a.amount, 0)) 마운틴듀
     , SUM(DECODE(a.item_name, '불고기버거', a.amount, 0)) 불고기버거
     , SUM(DECODE(a.item_name, '새우버거', a.amount, 0)) 새우버거
     , SUM(DECODE(a.item_name, '빅맥', a.amount, 0)) 빅맥
     , SUM(DECODE(a.item_name, '치킨버거', a.amount, 0)) 치킨버거
     , SUM(DECODE(a.item_name, '치즈버거', a.amount, 0)) 치즈버거
     , SUM(DECODE(a.item_name, '비프버거', a.amount, 0)) 비프버거
     , SUM(DECODE(a.item_name, '버섯버거', a.amount, 0)) 버섯버거
     , SUM(DECODE(a.item_name, '아이스크림', a.amount, 0)) 아이스크림
     , SUM(DECODE(a.item_name, '감자튀김', a.amount, 0)) 감자튀김
     , SUM(DECODE(a.item_name, '치즈스틱', a.amount, 0)) 치즈스틱
     , SUM(DECODE(a.item_name, '치킨텐더', a.amount, 0)) 치킨텐더
     , SUM(DECODE(a.item_name, '콘슬로', a.amount, 0)) 콘슬로
     , SUM(DECODE(a.item_name, '오징어링', a.amount, 0)) 오징어링
     , SUM(DECODE(a.item_name, 'A 세트', a.amount, 0)) a세트
     , SUM(DECODE(a.item_name, 'B 세트', a.amount, 0)) b세트
     , SUM(DECODE(a.item_name, '연인세트', a.amount, 0)) 연인세트
     , SUM(a.amount) 총합계
  FROM (
       SELECT c.item_name
            , SUM(b.amount) amount
         FROM burger_ord a
         LEFT OUTER JOIN burger_ord_item b
           ON a.ord_code = b.ord_code
         LEFT OUTER JOIN burger_item c
           ON b.item_code = c.item_code
        WHERE a.ord_date LIKE '2017%'
        GROUP BY c.item_name
        ) a;

※결과값※

 

[20번 문제] 각 상품당 세트메뉴에 포함되어 있는 횟수

  SELECT c.item_name 상품명
       , COUNT(c.item_name) 포함횟수
    FROM burger_item a
       , burger_set_item b
       , burger_item c
   WHERE a.item_code = b.set_item_code
     AND c.item_code = b.unit_item_code
     AND a.set_yn = 'Y'
   GROUP BY c.item_name
   ORDER BY 포함횟수 DESC;

※결과값※