My Dev Blog

<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

<MySQL & 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 & Oracle>

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

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

<MySQL & Oracle>

SELECT ANIMAL_TYPE
     , COUNT(ANIMAL_TYPE) AS COUNT
  FROM ANIMAL_INS
 WHERE ANIMAL_TYPE IN ('Cat', 'Dog')
 GROUP BY ANIMAL_TYPE
 ORDER BY ANIMAL_TYPE

프로그래머스 고양이와 개는 몇 마리 있을까 SQL

<MySQL>

SELECT ANIMAL_ID
     , NAME
     , CASE WHEN (SEX_UPON_INTAKE LIKE 'Neutered%' OR SEX_UPON_INTAKE LIKE 'Spayed%') THEN 'O' ELSE 'X' END AS '중성화'
  FROM ANIMAL_INS
ORDER BY ANIMAL_ID

<Oracle>

SELECT ANIMAL_ID
     , NAME
     -- , DECODE (SEX_UPON_INTAKE, 'Neutered%', 'O', 'Spayed%', 'O', 'X') AS 중성화
     , CASE WHEN (SEX_UPON_INTAKE LIKE 'Neutered%' OR SEX_UPON_INTAKE LIKE 'Spayed%') THEN 'O' ELSE 'X' END AS 중성화
  FROM ANIMAL_INS
ORDER BY ANIMAL_ID

프로그래머스 중성화 여부 파악하기 SQL

<MySQL>

SELECT A.ANIMAL_ID
     , A.NAME
  FROM ANIMAL_OUTS A
 INNER JOIN ANIMAL_INS B
    ON A.ANIMAL_ID = B.ANIMAL_ID
 ORDER BY DATEDIFF(A.DATETIME, B.DATETIME) DESC
 LIMIT 2

<Oracle>

SELECT T.ANIMAL_ID
     , T.NAME
  FROM (
         SELECT A.ANIMAL_ID
              , A.NAME
           FROM ANIMAL_OUTS A
     INNER JOIN ANIMAL_INS B
             ON A.ANIMAL_ID = B.ANIMAL_ID
       ORDER BY TO_DATE(A.DATETIME) - TO_DATE(B.DATETIME) DESC
       ) T
WHERE ROWNUM <= 2

프로그래머스 오랜기간보호한동물 SQL

<MySQL & Oracle>

SELECT A.ANIMAL_ID
     , A.ANIMAL_TYPE
     , A.NAME
  FROM ANIMAL_OUTS A
 INNER JOIN ANIMAL_INS B
    ON A.ANIMAL_ID = B.ANIMAL_ID
   AND A.SEX_UPON_OUTCOME != B.SEX_UPON_INTAKE
ORDER BY A.ANIMAL_ID

프로그래머스 보호소에서 중성화한 동물 SQL