My Dev Blog

<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

<MySQL>

SET @HOUR = -1;
SELECT (@HOUR := @HOUR + 1) AS HOUR
     , (SELECT COUNT(HOUR(DATETIME))
          FROM ANIMAL_OUTS
         WHERE HOUR(DATETIME) = @HOUR) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23

<Oracle>

SELECT HOUR
     , COUNT(B.DATETIME) AS COUNT
  FROM (
         SELECT LEVEL-1 AS HOUR
           FROM DUAL
        CONNECT BY LEVEL <= 24
       ) A
LEFT OUTER JOIN ANIMAL_OUTS B
ON A.HOUR = TO_CHAR(B.DATETIME, 'HH24')
GROUP BY HOUR
ORDER BY HOUR

프로그래머스 입양시각구하기 SQL

<MySQL>

SELECT A.HOUR
     , A.COUNT
  FROM (
         SELECT DATE_FORMAT(DATETIME, '%Y-%m-%d %H:%i:%s') AS TOTAL_DATE
              , DATE_FORMAT(DATETIME, '%H') AS HOUR
              , COUNT(SUBSTR(DATETIME, 12, 2)) AS COUNT
           FROM ANIMAL_OUTS
       GROUP BY SUBSTR(DATETIME, 12, 2)
       ) A
 WHERE A.HOUR BETWEEN 9 AND 20
ORDER BY HOUR

<Oracle>

SELECT A.HOUR
     , A.COUNT
  FROM (
         SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR
              , COUNT(TO_CHAR(DATETIME, 'HH24')) AS COUNT
           FROM ANIMAL_OUTS
       GROUP BY TO_CHAR(DATETIME, 'HH24')
       ) A
 WHERE A.HOUR BETWEEN 9 AND 20
ORDER BY HOUR

프로그래머스 입양시각구하기 SQL