[Oracle] 중성화 여부 파악하기 (Lv.2) / 오랜 기간 보호한 동물(2) (Lv.3) / 입양 시각 구하기(1) (Lv.2)

* 중성화 여부 파악하기 (Lv.2) 

• 문제 : https://school.programmers.co.kr/learn/courses/30/lessons/59409

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

 

 

* 오랜 기간 보호한 동물(2) (Lv.3)

• 문제 : https://school.programmers.co.kr/learn/courses/30/lessons/59411

SELECT ANIMAL_ID
     , NAME
  FROM (
    SELECT AI.ANIMAL_ID
         , AI.NAME
         , AO.DATETIME - AI.DATETIME AS PERIOD
      FROM ANIMAL_INS AI
     INNER JOIN ANIMAL_OUTS AO
        ON AI.ANIMAL_ID = AO.ANIMAL_ID
     WHERE 1=1
     ORDER BY PERIOD DESC
  )
WHERE ROWNUM <= 2

 

 

* 입양 시각 구하기(1) (Lv.2)

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

SELECT TO_NUMBER(TO_CHAR(DATETIME, 'FMHH24')) AS HOUR
     , COUNT(ANIMAL_ID)
  FROM ANIMAL_OUTS
 GROUP BY TO_CHAR(DATETIME, 'FMHH24')
HAVING TO_CHAR(DATETIME, 'FMHH24') >= 9
   AND TO_CHAR(DATETIME, 'FMHH24') < 20
 ORDER BY HOUR ASC
;

-- FM 은 생략 가능

SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) AS HOUR
     , COUNT(ANIMAL_ID)
  FROM ANIMAL_OUTS
 WHERE TO_CHAR(DATETIME, 'HH24') BETWEEN 09 AND 19
 GROUP BY TO_CHAR(DATETIME, 'HH24')
 ORDER BY HOUR ASC