My Dev Blog

<MySQL>

SELECT A.AUTHOR_ID
     , B.AUTHOR_NAME
     , A.CATEGORY
     , SUM(A.PRICE * C.SALES) AS TOTAL_SALES
  FROM BOOK A
     , AUTHOR B
     , BOOK_SALES C
 WHERE A.AUTHOR_ID = B.AUTHOR_ID
   AND A.BOOK_ID = C.BOOK_ID
   AND DATE_FORMAT(C.SALES_DATE, '%Y-%m') = '2022-01'
 GROUP BY A.AUTHOR_ID, A.CATEGORY, B.AUTHOR_NAME
 ORDER BY A.AUTHOR_ID, A.CATEGORY DESC

<Oracle>

SELECT A.AUTHOR_ID
     , B.AUTHOR_NAME
     , A.CATEGORY
     , SUM(A.PRICE * C.SALES) AS TOTAL_SALES
  FROM BOOK A
     , AUTHOR B
     , BOOK_SALES C
 WHERE A.AUTHOR_ID = B.AUTHOR_ID
   AND A.BOOK_ID = C.BOOK_ID
   AND TO_CHAR(C.SALES_DATE, 'YYYY-MM') = '2022-01'
 GROUP BY A.AUTHOR_ID, A.CATEGORY, B.AUTHOR_NAME
 ORDER BY A.AUTHOR_ID, A.CATEGORY DESC

프로그래머스 저자 별 카테고리 별 매출액 집계하기 SQL

<MySQL>

SELECT MST.YEAR
     , MST.MONTH
     , MST.GENDER
     , COUNT(MST.GENDER) AS USERS
  FROM (
         SELECT YEAR(A.SALES_DATE) AS YEAR
              , MONTH(A.SALES_DATE) AS MONTH
              , B.GENDER
           FROM ONLINE_SALE A
              , USER_INFO B
          WHERE A.USER_ID = B.USER_ID
            AND B.GENDER IN (0, 1)
       GROUP BY YEAR(A.SALES_DATE), MONTH(A.SALES_DATE), B.USER_ID
       ) MST
GROUP BY MST.YEAR, MST.MONTH, MST.GENDER
ORDER BY MST.YEAR, MST.MONTH, MST.GENDER

<Oracle>

SELECT MST.YEAR
     , MST.MONTH
     , MST.GENDER
     , COUNT(MST.GENDER) AS USERS
  FROM (
         SELECT EXTRACT(YEAR FROM A.SALES_DATE) AS YEAR
              , EXTRACT(MONTH FROM A.SALES_DATE) AS MONTH
              , B.GENDER
           FROM ONLINE_SALE A
              , USER_INFO B
          WHERE A.USER_ID = B.USER_ID
            AND B.GENDER IN (0, 1)
       GROUP BY EXTRACT(YEAR FROM A.SALES_DATE), EXTRACT(MONTH FROM A.SALES_DATE), B.USER_ID, B.GENDER
       ) MST
GROUP BY MST.YEAR, MST.MONTH, MST.GENDER
ORDER BY MST.YEAR, MST.MONTH, MST.GENDER

MySQL에서 MONTH(날짜), DATE_FORMAT(날짜, '%c') 차이

Oracle에서 EXTRACT(MONTH FROM 날짜), TO_CHAR(날짜, 'FMMM') 차이

정렬 기준의 차이를 확인할 수 있다.

프로그래머스 년, 월, 성별 별 상품 구매 회원 수 구하기 SQL

<MySQL>

SELECT A.PRODUCT_ID
     , B.PRODUCT_NAME
     , SUM(A.AMOUNT) * B.PRICE AS TOTAL_SALES
  FROM FOOD_ORDER A
     , FOOD_PRODUCT B
 WHERE A.PRODUCT_ID = B.PRODUCT_ID
   AND DATE_FORMAT(A.PRODUCE_DATE, '%Y-%m') = '2022-05'
 GROUP BY A.PRODUCT_ID
 ORDER BY SUM(A.AMOUNT) * B.PRICE DESC, A.PRODUCT_ID

<Oracle>

SELECT A.PRODUCT_ID
     , A.PRODUCT_NAME
     , A.PRICE * B.AMOUNT_SUM AS TOTAL_SALES
  FROM FOOD_PRODUCT A
     , (
         SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT_SUM
           FROM FOOD_ORDER
          WHERE TO_CHAR(PRODUCE_DATE, 'YYYY-MM') = '2022-05'
       GROUP BY PRODUCT_ID
       ) B
 WHERE A.PRODUCT_ID = B.PRODUCT_ID
 ORDER BY A.PRICE * B.AMOUNT_SUM DESC, A.PRODUCT_ID

프로그래머스 5월 식품들의 총매출 조회하기 SQL

<MySQL>

SELECT A.MEMBER_NAME
     , B.REVIEW_TEXT
     , DATE_FORMAT(B.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
  FROM MEMBER_PROFILE A
     , REST_REVIEW B
 WHERE A.MEMBER_ID = B.MEMBER_ID
   AND B.MEMBER_ID IN (
                        SELECT MEMBER_ID
                          FROM REST_REVIEW
                      GROUP BY MEMBER_ID
                        HAVING COUNT(MEMBER_ID) = (
                                                    SELECT COUNT(MEMBER_ID) AS CNT
                                                      FROM REST_REVIEW
                                                  GROUP BY MEMBER_ID
                                                  ORDER BY COUNT(MEMBER_ID) DESC
                                                     LIMIT 1                      
                                                  )
                      )
 ORDER BY B.REVIEW_DATE, B.REVIEW_TEXT

<Oracle>

SELECT A.MEMBER_NAME
     , B.REVIEW_TEXT
     , TO_CHAR(B.REVIEW_DATE, 'YYYY-MM-DD') AS REVIEW_DATE
  FROM MEMBER_PROFILE A
     , REST_REVIEW B
 WHERE A.MEMBER_ID = B.MEMBER_ID
   AND B.MEMBER_ID IN (
                        SELECT MEMBER_ID
                          FROM REST_REVIEW
                      GROUP BY MEMBER_ID
                        HAVING COUNT(MEMBER_ID) = (
                                                    SELECT MAX(COUNT(MEMBER_ID)) AS CNT
                                                      FROM REST_REVIEW
                                                  GROUP BY MEMBER_ID                 
                                                  )
                      )
 ORDER BY B.REVIEW_DATE, B.REVIEW_TEXT

프로그래머스 그룹별 조건에 맞는 식당 목록 출력하기 SQL

<MySQL & Oracle>

SELECT A.CATEGORY
     , A.PRICE AS MAX_PRICE
     , A.PRODUCT_NAME
  FROM (
         SELECT CATEGORY
              , PRICE
              , PRODUCT_NAME
              , RANK() OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC) AS PRICE_RANK
           FROM FOOD_PRODUCT
          WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
       ) A
 WHERE A.PRICE_RANK = 1
 ORDER BY A.PRICE DESC

프로그래머스 식품분류별 가장 비싼 식품의 정보 조회하기 SQL

<MySQL>

SELECT A.REST_ID
     , A.REST_NAME
     , A.FOOD_TYPE
     , A.FAVORITES
     , A.ADDRESS
     , ROUND(AVG(IFNULL(NULLIF(B.REVIEW_SCORE, ''), 0)), 2) AS SCORE
  FROM REST_INFO A
     , REST_REVIEW B
 WHERE A.REST_ID = B.REST_ID
   AND A.ADDRESS LIKE '서울%'
 GROUP BY A.REST_ID
 ORDER BY ROUND(AVG(IFNULL(NULLIF(B.REVIEW_SCORE, ''), 0)), 2) DESC, A.FAVORITES DESC

<Oracle>

SELECT A.REST_ID
     , A.REST_NAME
     , A.FOOD_TYPE
     , A.FAVORITES
     , A.ADDRESS
     , ROUND(AVG(NVL(B.REVIEW_SCORE, 0)), 2) AS SCORE
  FROM REST_INFO A
     , REST_REVIEW B
 WHERE A.REST_ID = B.REST_ID
   AND A.ADDRESS LIKE '서울%'
 GROUP BY A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS
 ORDER BY ROUND(AVG(NVL(B.REVIEW_SCORE, 0)), 2) DESC, A.FAVORITES DESC

평균 계산 함수 AVG 사용 시 주의할 점

SCORE 값이 NULL이거나 빈 값일 경우 0으로 평균 계산에 포함시키려면

<MySQL> : IFNULL(NULLIF(컬럼, ''), 컬럼 값이 NULL이거나 빈 값일 경우 보여줄 값)

=> AVG(IFNULL(NULLIF(SCORE, ''), 0))

<Oracle> : NVL(컬럼, 컬럼 값이 NULL이거나 빈 값일 경우 보여줄 값)

=> AVG(NVL(SCORE, 0))

프로그래머스 서울에 위치한 식당 목록 출력하기 SQL

<MySQL>

SELECT A.SALES_DATE
     , A.PRODUCT_ID
     , A.USER_ID
     , A.SALES_AMOUNT
  FROM (
         SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE
              , PRODUCT_ID
              , USER_ID
              , SALES_AMOUNT
           FROM ONLINE_SALE
          WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03'
      UNION ALL 
         SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE
              , PRODUCT_ID
              , NULL AS USER_ID
              , SALES_AMOUNT
           FROM OFFLINE_SALE
          WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03'
       ) A
ORDER BY A.SALES_DATE, A.PRODUCT_ID, A.USER_ID

<Oracle>

SELECT A.SALES_DATE
     , A.PRODUCT_ID
     , A.USER_ID
     , A.SALES_AMOUNT
  FROM (
         SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE
              , PRODUCT_ID
              , USER_ID
              , SALES_AMOUNT
           FROM ONLINE_SALE
          WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-03'
      UNION ALL 
         SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE
              , PRODUCT_ID
              , NULL AS USER_ID
              , SALES_AMOUNT
           FROM OFFLINE_SALE
          WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-03'
       ) A
ORDER BY A.SALES_DATE, A.PRODUCT_ID, A.USER_ID

<NULL 값을 먼저 또는 나중에 출력되도록 정렬 방법>

MySQL : ORDER BY 컬럼 IS NULL (ASC / DESC)

Oracle : ORDER BY 컬럼 (ASC / DESC) NULLS (FIRST / LAST)

프로그래머스 오프라인/온라인 판매 데이터 통합하기 SQL

<MySQL>

SELECT A.APNT_NO
     , B.PT_NAME
     , B.PT_NO
     , A.MCDP_CD
     , C.DR_NAME
     , A.APNT_YMD
  FROM APPOINTMENT A
     , PATIENT B
     , DOCTOR C
 WHERE A.PT_NO = B.PT_NO
   AND A.MDDR_ID = C.DR_ID
   AND DATE_FORMAT(A.APNT_YMD, '%Y-%m-%d') = '2022-04-13'
   AND A.APNT_CNCL_YN = 'N'
   AND A.MCDP_CD = 'CS'
 ORDER BY A.APNT_YMD

<Oracle>

SELECT A.APNT_NO
     , B.PT_NAME
     , B.PT_NO
     , A.MCDP_CD
     , C.DR_NAME
     , A.APNT_YMD
  FROM APPOINTMENT A
     , PATIENT B
     , DOCTOR C
 WHERE A.PT_NO = B.PT_NO
   AND A.MDDR_ID = C.DR_ID
   AND TO_CHAR(A.APNT_YMD, 'YYYY-MM-DD') = '2022-04-13'
   AND A.APNT_CNCL_YN = 'N'
   AND A.MCDP_CD = 'CS'
 ORDER BY A.APNT_YMD

프로그래머스 취소되지 않은 진료 예약 조회하기 SQL