My Dev Blog

<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

<MySQL>

SELECT A.FLAVOR
  FROM FIRST_HALF A
 INNER JOIN (
              SELECT FLAVOR
                   , SUM(TOTAL_ORDER) AS JULY_ORDER
                FROM JULY
            GROUP BY FLAVOR
            ) B
    ON A.FLAVOR = B.FLAVOR
 ORDER BY (A.TOTAL_ORDER + B.JULY_ORDER) DESC
 LIMIT 3

<Oracle>

SELECT FLAVOR
  FROM (
         SELECT A.FLAVOR
           FROM FIRST_HALF A
     INNER JOIN (
                  SELECT FLAVOR
                       , SUM(TOTAL_ORDER) AS JULY_ORDER
                    FROM JULY
                GROUP BY FLAVOR
                ) B
             ON A.FLAVOR = B.FLAVOR
       ORDER BY (A.TOTAL_ORDER + B.JULY_ORDER) DESC
       )
 WHERE ROWNUM <= 3

프로그래머스 주문량이 많은 아이스크림들 조회하기 SQL

<MySQL>

SELECT DATE_FORMAT(A.SALES_DATE, '%Y') AS YEAR
     , DATE_FORMAT(A.SALES_DATE, '%c') AS MONTH
     , COUNT(DISTINCT A.USER_ID) AS PUCHASED_USERS
     , ROUND(COUNT(DISTINCT A.USER_ID) / (SELECT COUNT(*) FROM USER_INFO WHERE DATE_FORMAT(JOINED, '%Y') = 2021), 1) AS PUCHASED_RATIO
  FROM ONLINE_SALE A
 INNER JOIN USER_INFO B
    ON A.USER_ID = B.USER_ID
   AND DATE_FORMAT(B.JOINED, '%Y') = 2021
 GROUP BY YEAR, MONTH
 ORDER BY CAST(YEAR AS SIGNED), CAST(MONTH AS SIGNED)

<Oracle>

SELECT EXTRACT(YEAR FROM A.SALES_DATE) AS YEAR
     , EXTRACT(MONTH FROM A.SALES_DATE) AS MONTH
     , COUNT(DISTINCT A.USER_ID) AS PUCHASED_USERS
     , ROUND(COUNT(DISTINCT A.USER_ID) / (SELECT COUNT(*) FROM USER_INFO WHERE TO_CHAR(JOINED, 'YYYY') = 2021), 1) AS PUCHASED_RATIO
  FROM ONLINE_SALE A
 INNER JOIN USER_INFO B
    ON A.USER_ID = B.USER_ID
   AND TO_CHAR(B.JOINED, 'YYYY') = 2021
 GROUP BY EXTRACT(YEAR FROM A.SALES_DATE), EXTRACT(MONTH FROM A.SALES_DATE)
 ORDER BY EXTRACT(YEAR FROM A.SALES_DATE), EXTRACT(MONTH FROM A.SALES_DATE)

프로그래머스 상품을 구매한 회원 비율 구하기 SQL

<MySQL & Oracle>

SELECT A.FOOD_TYPE
     , A.REST_ID
     , A.REST_NAME
     , A.FAVORITES
  FROM REST_INFO A
 INNER JOIN (
              SELECT FOOD_TYPE
                   , MAX(FAVORITES) AS FAVORITES
                FROM REST_INFO
            GROUP BY FOOD_TYPE
            ) B
    ON A.FOOD_TYPE = B.FOOD_TYPE
   AND A.FAVORITES = B.FAVORITES
 ORDER BY A.FOOD_TYPE DESC

프로그래머스 즐겨찾기가 가장 많은 식당 정보 출력하기 SQL

<MySQL & Oracle>

SELECT ID
     , NAME
     , HOST_ID
  FROM PLACES
 WHERE HOST_ID IN (
                    SELECT HOST_ID
                      FROM PLACES
                  GROUP BY HOST_ID
                    HAVING COUNT(HOST_ID) > 1
                  )
ORDER BY ID

집계함수 COUNT를 조건으로 사용하므로 서브쿼리에서

WHERE절이 아닌 HAVING절에 COUNT(HOST_ID) > 1

프로그래머스 헤비 유저가 소유한 저장소 SQL

<MySQL & Oracle>

SELECT A.ANIMAL_ID
     , A.NAME
  FROM ANIMAL_OUTS A
 INNER JOIN ANIMAL_INS B
    ON A.ANIMAL_ID = B.ANIMAL_ID
   AND A.DATETIME < B.DATETIME
 ORDER BY B.DATETIME

프로그래머스 있었는데요 없었습니다 SQL

<MySQL & Oracle>

SELECT A.NAME
     , A.COUNT
  FROM (
         SELECT NAME, COUNT(NAME) AS COUNT
           FROM ANIMAL_INS
       GROUP BY NAME
       ) A
WHERE COUNT >= 2
ORDER BY NAME

프로그래머스 동명 동물 수 찾기 SQL