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

silver

Jan 10, 2025

Contents

문제 MYSQL ORACLE

문제

school.programmers.co.kr

https://school.programmers.co.kr/learn/courses/30/lessons/59412

MYSQL


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

ORACLE

내가 작성한 오답1

: 프로그래머스 ORACLE의 EXTRACT에서는 HOUR를 지원을 해주지 않는 것 같다. → MYSQL에서는 EXTRACT(HOUR FROM DATETIME)이 적용이 되었다.


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

내가 작성한 오답2

: HH에서 시간을 24시간으로 변환을 하지 않았다.


SELECT TO_CHAR(DATETIME,'HH') HOUR, COUNT(ANIMAL_ID) COUNT
FROM ANIMAL_OUTS
WHERE TO_CHAR(DATETIME,'HH') >= '09' AND TO_CHAR(DATETIME,'HH') < '20'
GROUP BY TO_CHAR(DATETIME,'HH')
ORDER BY HOUR ASC

내가 작성한 오답3

: DATETIME을 TO_CHAR을 이용하여 VARCHAR형식으로 형 변환을 했는데 부등호로 문자를 비교했다.


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

내가 작성한 오답4

: 비교해야하는 WHERE절의 시간만 숫자로 변환하여 비교하고 결과로 출력되는 SELECT, GROUP BY 절은 TO_CHAR그대로 두었다. 결과값에 ‘09’가 아닌 숫자 ‘9’가 출력됐어야했던 것 같다


SELECT TO_CHAR(DATETIME,'HH24') 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 TO_CHAR(DATETIME,'HH24')
ORDER BY HOUR ASC

내가 작성한 정답


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

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

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

SELECT TO_CHAR(DATETIME,'HH24') 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 TO_CHAR(DATETIME,'HH24') ORDER BY HOUR ASC

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