-1.없어진 기록 찾기 문제
- 문제 설명
ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME,INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.
ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다. ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다.
천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.
내 답 )
|
1
2
3
4
|
#OUTS 테이블에만 존재하는 것을 뽑아 내면 된다.
SELECT outs.ANIMAL_ID,outs.NAME from ANIMAL_OUTS outs left join
ANIMAL_INS ins on outs.ANIMAL_ID=ins.ANIMAL_ID
where ins.ANIMAL_ID is null order by outs.ANIMAL_ID;
|
cs |
jeong-in 님의 답 )
|
1
|
select animal_id, name from animal_outs where animal_id not in (select animal_id from animal_ins);
|
cs |
bigMango 님의 답 )
|
1
2
3
4
5
|
select o.animal_id, o.name
from animal_outs o
left join animal_ins i using(animal_id)
where i.animal_id is null
order by o.animal_id;
|
cs |
mysql에서 join을 사용할때 using 이나 on이나 결과는 동일하다 .
다만 , using 은 두 테이블간 필드이름이 같은 경우에만 사용하고 ,
on 은 두 테이블간 필드이름이 같던 다르던 사용가능하다.
상황에 따라서 using을 사용하면 조금이나마 코드가 짧아지겠다..
-2. 있었는데요 없었습니다
- 문제설명
기본 문제형식은 1번과 동일합니다.
관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다.
보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요.
이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.
내답 )
|
1
2
3
4
|
#INNER JOIN 이용후 조건 만족하는 데이터 찾아서 출력
SELECT INS.ANIMAL_ID,OUTS.NAME FROM ANIMAL_INS INS INNER JOIN
ANIMAL_OUTS OUTS USING(ANIMAL_ID) WHERE INS.DATETIME>OUTS.DATETIME
ORDER BY INS.DATETIME ASC;
|
cs |
herjh0405 님의 답 )
|
1
2
3
4
5
6
|
SELECT AI.ANIMAL_ID, AI.NAME FROM ANIMAL_INS AS AI
JOIN
ANIMAL_OUTS AS AO
ON AI.ANIMAL_ID = AO.ANIMAL_ID
WHERE AI.DATETIME > AO.DATETIME
ORDER BY AI.DATETIME
|
cs |
2: INNER JOIN 에서 INNER는 생략이 가능하다.
박현수 님의 답 )
|
1
2
3
|
SELECT a.animal_id, a.name from animal_ins a, animal_outs b
where a.animal_id = b.animal_id and a.datetime > b.datetime
order by a.datetime
|
cs |
-3. 오랜 기간 보호한 동물(1)
- 문제설명
기본 문제형식은 1번과 동일합니다.
아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.
내답 )
|
1
2
3
4
5
6
|
SELECT INS.NAME,INS.DATETIME FROM ANIMAL_INS INS LEFT JOIN
ANIMAL_OUTS OUTS USING(ANIMAL_ID) WHERE OUTS.ANIMAL_ID IS NULL
ORDER BY INS.DATETIME LIMIT 0,3;
#INS 에만 있는 기록 중 , 가장 오래 보호소에 있었던 동물 3마리를
#이름,보호시작일 조회 (ORDER BY ANIMAL_INS.DATETIME ASC)
# => INS만의 차집합을 뽑아내면 해결.
|
cs |
이필환님의 답)
|
1
2
3
4
5
6
|
SELECT NAME, DATETIME
from ANIMAL_INS
where ANIMAL_ID not in (select ANIMAL_ID from ANIMAL_OUTS)
order by DATETIME
limit 3
;
|
cs |
join 없이 서브쿼리로 해결하셨다.
-4. 보호소에서 중성화한 동물
- 문제설명
기본 문제형식은 1번과 동일합니다.
보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다.
보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.
중성화를 거치지 않은 동물은 성별 및 중성화 여부에 Intact, 중성화를 거친 동물은 Spayed 또는 Neutered라고 표시되어있습니다.
내답 )
|
1
2
3
4
5
6
7
8
9
10
|
SELECT INS.ANIMAL_ID,INS.ANIMAL_TYPE,INS.NAME FROM ANIMAL_INS INS
INNER JOIN ANIMAL_OUTS OUTS USING(ANIMAL_ID)
WHERE INS.SEX_UPON_INTAKE LIKE 'Intact%' and
OUTS.SEX_UPON_OUTCOME NOT LIKE 'Intact%' ORDER BY INS.ANIMAL_ID;
#중성화를 거치지 않은 동물은 Intact 문자열이 들어가있음.
#먼저 INS와 OUTS에 동일한 동물이 존재해야하므로 ,
#INS와 OUTS가 ANIMAL_ID로 inner join 후
#INS.SEX_UPON_INTAKE 가 Intact 를 포함하며 ,
#OUTS.SEX_UPON_OUTCOME 가 Intact를 포함하지 않는 경우의
# 아이디와 생물 종, 이름을 조회 (ORDER BY INS.ANIMAL_ID ASC)
|
cs |
가뜩이나 인덱스가 적용안된 SEX_UPON_INTAKE(SEX_UPON_OUTCOME) 에
WHERE 조건으로 Spayed , Neutered 를 두가지 모두 사용하는 것은 부담스러워서 Intact만을 사용하였다.
lazy-sky 님의 답 )
|
1
2
3
4
5
6
|
SELECT O.ANIMAL_ID, O.ANIMAL_TYPE, O.NAME
FROM ANIMAL_OUTS AS O
LEFT OUTER JOIN ANIMAL_INS AS I
ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE O.SEX_UPON_OUTCOME != I.SEX_UPON_INTAKE
ORDER BY ANIMAL_ID;
|
cs |
실로 놀랍다.
먼저 ,
1. 범위성 측면에서 ANIMAL_OUTS 테이블은 ANIMAL_INS 테이블보다 작고,
ANIMAL_OUTS 테이블에 있는 동물은 ANIMAL_INS 테이블에 기본적으로 모두 포함되어있다.
=> 'A' LEFT OUTER JOIN 'B' 의 'A'부분에 ANIMAL_OUTS를 놓음 으로써 INNER JOIN 과 동일한 결과가 나온다.
2. INS테이블의 성별과 OUTS테이블이 성별이 다르다는 것은 , 의미하는 바가 한가지이다.
(중성 -> 성별을갖는것 은 될수가 없다.)성별을갖는것 ->중성O 이 만족한다, 즉 중성화가 된다..
=> WHERE O.SEX_UPON_OUTCOME != I.SEX_UPON_INTAKE 으로 표현이 된다.
'21년이전 > 국비-sql' 카테고리의 다른 글
| SQL - JOIN (0) | 2021.06.22 |
|---|---|
| SQL - DDL (0) | 2021.05.12 |
| DB - 개념모델링 (0) | 2021.05.10 |
| 국비 SQL - 명령어 모음 (0) | 2021.02.22 |
