[ SQL 고득점 Kit ] String, Date 답안

안녕하세요 Jin's 입니다.

 

String, Date 답안 공유 드립니다!

 

1. 루시와 엘라 찾기

SELECT animal_id
     , name
     , sex_upon_intake
  FROM animal_ins
 where name in ('Lucy','Ella','Pickle','Rogan','Sabrina','Mitty')
 order by animal_id;

 

2. 이름에 el이 들어가는 동물 찾기

SELECT animal_id
     , name
  from animal_ins
 where upper(name) like '%EL%'
   and animal_type = 'Dog'
 order by name;

 

3. 중성화여부 파악하기

SELECT animal_id
     , name
     , (case when instr(sex_upon_intake,'Intact') > 0
             then 'X'
             else 'O'
         end
        ) as 중성화
  from animal_ins
 order by animal_id

 

4. 오랜기간 보호한 동물(2)

select animal_id
     , name
from (SELECT ins.animal_id
            , ins.name
            , outs.datetime - ins.datetime as datetime
         FROM animal_ins ins
            , animal_outs outs
        WHERE ins.animal_id = outs.animal_id    
      order by datetime desc
      ) 
where rownum < 3

 

5. DATETIME에서 DATE로 형 변환

SELECT animal_id
     , name
     , to_char(datetime,'yyyy-mm-dd') as datetime
  from animal_ins
 order by animal_id