ING

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

SQL 고득점 Kit의 JOIN 답안 공유드립니다~

1. 없어진 기록 찾기

SELECT outs.animal_id
     , outs.name
  FROM animal_ins ins
 right outer join animal_outs outs
    on ins.animal_id = outs.animal_id
 where ins.animal_id is null
 ORDER BY outs.animal_id, outs.name

2. 있었는데요 없었습니다

select ins.animal_id
     , ins.name
  from animal_ins ins
     , animal_outs outs
 where ins.animal_id = outs.animal_id
   and (ins.datetime - outs.datetime) > 0
 order by ins.datetime

3. 오랜 기간 보호한 동물(1)

select *
  from (
       SELECT ins.name
            , ins.datetime
         from animal_ins ins
         left outer join animal_outs outs
           on ins.animal_id = outs.animal_id
        where outs.animal_id is null
        order by ins.datetime
    )
  where rownum <4

4. 보호소에서 중성화한 동물

SELECT ins.animal_id
     , ins.animal_type
     , ins.name
  from animal_ins ins
     , animal_outs outs
 where ins.animal_id = outs.animal_id
   and ins.sex_upon_intake <> outs.sex_upon_outcome