[SQL문제풀기] 입양 시각 구하기(1)

문제

MYSQL

내가 작성한 정답

--1
SELECT HOUR(DATETIME) HOUR, COUNT(*) COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN '09' AND '19'
GROUP BY HOUR(DATETIME)
ORDER BY 1;

--2
SELECT DATE_FORMAT(DATETIME,'%H') HOUR, COUNT(*) COUNT
FROM ANIMAL_OUTS
WHERE DATE_FORMAT(DATETIME,'%H') BETWEEN '09' AND '19'
GROUP BY DATE_FORMAT(DATETIME,'%H')
ORDER BY 1;

이전에 작성한 정답

SELECT  EXTRACT(HOUR FROM DATETIME) HOUR, COUNT(ANIMAL_ID) COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME)>= 9 AND HOUR(DATETIME) < 20
GROUP BY  EXTRACT(HOUR FROM DATETIME)
ORDER BY HOUR ASC

ORACLE

내가 작성한 정답

#1 이 문제 ;쓰면 오답처리 됨
SELECT TO_CHAR(DATETIME,'HH24')+0 HOUR, COUNT(*) COUNT
FROM ANIMAL_OUTS
WHERE TO_CHAR(DATETIME,'HH24') BETWEEN '09' AND '19'
GROUP BY TO_CHAR(DATETIME,'HH24')
ORDER BY 

#2 숫자로 변경 시 TO_NUMBER으로 명시적으로 변경, where절에서 명확한 비교를 위해 숫자로 변경
SELECT TO_NUMBER(TO_CHAR(DATETIME,'HH24')) HOUR, COUNT(*) COUNT
FROM ANIMAL_OUTS
WHERE TO_NUMBER(TO_CHAR(DATETIME,'HH24')) BETWEEN '09' AND '19'
GROUP BY TO_CHAR(DATETIME,'HH24')
ORDER BY 1

이전에 작성한 정답

SELECT CAST(TO_CHAR(DATETIME,'HH24') AS NUMBER) HOUR, COUNT(ANIMAL_ID) COUNT
FROM ANIMAL_OUTS
WHERE CAST(TO_CHAR(DATETIME,'HH24') AS NUMBER) >= 9 
    AND CAST(TO_CHAR(DATETIME,'HH24') AS NUMBER) < 20
GROUP BY CAST(TO_CHAR(DATETIME,'HH24') AS NUMBER)
ORDER BY HOUR ASC