My Dev Blog

<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

<MySQL & Oracle>

SELECT A.CART_ID
  FROM (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Milk') A
 INNER JOIN (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Yogurt') B
    ON A.CART_ID = B.CART_ID

프로그래머스 우유와 요거트가 담긴 바구니 SQL

<MySQL & Oracle>

SELECT ANIMAL_ID
     , NAME
     , SEX_UPON_INTAKE
  FROM ANIMAL_INS
 WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
 ORDER BY ANIMAL_ID

프로그래머스 루시와 엘라 찾기 SQL