My Dev Blog

<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

<MySQL>

SELECT ANIMAL_ID
     , NAME
     , DATE_FORMAT(DATETIME, '%Y-%m-%d') AS '날짜'
#      %Y %m %d %H %i %s
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

<Oracle>

SELECT ANIMAL_ID
     , NAME
     , TO_CHAR(DATETIME, 'YYYY-MM-DD') AS 날짜
--      YYYY MM DD HH24 MI SS
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

프로그래머스 DATETIME에서 DATE로 형 변환 SQL

<MySQL>

SELECT NAME
     , DATETIME
  FROM ANIMAL_INS
 WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY DATETIME
 LIMIT 3

<Oracle>

SELECT T.NAME
     , T.DATETIME
  FROM (
         SELECT NAME
              , DATETIME
           FROM ANIMAL_INS
          WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
       ORDER BY DATETIME
       ) T
WHERE ROWNUM <= 3

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