개발 학습 블로그

SELECT CITY
  FROM STATION
 WHERE MOD(ID, 2) = 0
 GROUP BY CITY

해커랭크 Weather Observation Station SQL

<MySQL>

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
                  )

<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>

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

<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>

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

<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>

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

<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>

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

<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

<MySQL>

SELECT ANIMAL_ID
     , NAME
  FROM ANIMAL_OUTS
 WHERE ANIMAL_ID NOT IN (SELECT B.ANIMAL_ID
                           FROM ANIMAL_OUTS B
                     INNER JOIN ANIMAL_INS A
                             ON B.ANIMAL_ID = A.ANIMAL_ID)
ORDER BY ANIMAL_ID

<Oracle>

SELECT ANIMAL_ID
     , NAME
  FROM ANIMAL_OUTS
 WHERE ANIMAL_ID NOT IN (SELECT B.ANIMAL_ID
                           FROM ANIMAL_OUTS B
                     INNER JOIN ANIMAL_INS A
                             ON B.ANIMAL_ID = A.ANIMAL_ID)
ORDER BY ANIMAL_ID

프로그래머스 없어진 기록 찾기 SQL

<MySQL>

SELECT ANIMAL_ID
  FROM ANIMAL_INS
 WHERE NULLIF(NAME, '') IS NULL
 ORDER BY ANIMAL_ID

<Oracle>

SELECT ANIMAL_ID
  FROM ANIMAL_INS
 WHERE NULLIF(NAME, '') IS NULL
 ORDER BY ANIMAL_ID

프로그래머스 이름이 없는 동물의 아이디 SQL