이전 포스트에 이어서 프로그래머스의 SQL 문제를 풀어보도록 한다.
데이터는 다음과 같다.
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의 외래 키입니다.
가장 많이 출제되는 JOIN과 관련된 파트이다.
(결과 데이터는 동일하지 않을 수도 있다.)
1. 없어진 기록 찾기
천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요. 다음과 같이 출력되어야 합니다.
이 문제는 입양을 간 기록의 데이터인 ANIMAL_OUTS as A 와 보호소에 들어온 기록의 데이터인 ANIMAL_INS as B 를 비교해서 A에는 있지만, B에는 없는 동물들을 찾아내는 쿼리이다.
먼저 JOIN은 상당히 헷갈리는 부분이 많기 때문에 다음 그림을 첨부했다.
여기서 이 문제와 관련있는 부분은 왼쪽위에서 두번째, 즉
이 부분이다.
그냥 LEFT JOIN과 다른 이유는,
LEFT JOIN을 사용했을 때 두 테이블에 모두 포함되는 경우도 출력하기 때문에 반드시 where A.key is null 이라는 부분을 넣어줘야 하는 것이다.
그래서 짜게된 쿼리는 다음과 같다.
SELECT A.ANIMAL_ID, A.NAME from ANIMAL_OUTS as A LEFT JOIN ANIMAL_INS as B ON A.ANIMAL_ID = B.ANIMAL_ID WHERE B.ANIMAL_ID is null order by A.ANIMAL_ID;2.있었는데요 없었습니다.
관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다. 다음과 같이 출력되어야 합니다.
이 문제도 1번과 비슷하지만 두 테이블을 함께 놓고 비교해야 한다.
먼저 두 테이블의 ANIMAL_ID가 같으면서도 보호시작일보다 입양일이 더 빠르다는 조건을 넣어줘야하기 때문에 INNER JOIN을 통해 ANIMAL_ID가 같으면서도 ANIMAL_INS의 DATETIME이 ANIMAL_OUTS의 DATETIME보다 클 경우를 생각하면 된다. + 보호 시작일이 빠른 순서대로 order by를 걸어줘야 한다.
SELECT A.ANIMAL_ID, A.NAME FROM ANIMAL_INS as A INNER JOIN ANIMAL_OUTS as B on A.ANIMAL_ID = B.ANIMAL_ID and A.DATETIME > B.DATETIME order by A.DATETIME;3. 오랜기간 보호한 동물(1)
아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다. 다음과 같이 출력되어야 합니다.
이 문제는 1번과 비슷하지만 반대로 ANIMAL_INS 에는 있지만, ANIMAL_OUTS 에는 존재하지 않는 데이터를 먼저 찾아야 한다. 그래서 중복된 데이터를 제거하는 방식의 LEFT JOIN 을 사용하면 된다. 또한 가장 오래 보호소에 있던 순서이기 때문에 DATETIME로 정렬을 해주며 3마리이기 때문에 limit 3을 걸어주게 된다.
SELECT A.NAME, A.DATETIME FROM ANIMAL_INS as A LEFT JOIN ANIMAL_OUTS as B on A.ANIMAL_ID = B.ANIMAL_ID where B.ANIMAL_ID is null order by A.DATETIME limit 3;4. 보호소에서 중성화한 동물
보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요. 다음과 같이 출력되어야 합니다.
이 문제는 두 테이블을 연결해야 하기 때문에 INNER JOIN 을 사용해야 한다. 먼저 보호소에 들어온 동물의 데이터, 입양 간 동물의 데이터가 서로 일치해야 하므로 ID 가 일치하는 조건 아래에서 시작한다.
그리고 중성화 수술을 했는지 여부는 SEX_UPON_INTAKE 라는 컬럼에서 Intact 라는 단어가 포함되어있는지 파악하면 된다. 그렇기 때문에 LIKE 를 활용하여 ANIMAL_INS 에 Intact 단어가 포함되어있으면서도 ANIMAL_OUTS 에 Intact 단어가 포함되어있지 않은 데이터를 조회하면 된다.
SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME FROM ANIMAL_INS AS A INNER JOIN ANIMAL_OUTS AS B ON A.ANIMAL_ID = B.ANIMAL_ID AND A.SEX_UPON_INTAKE LIKE '%Intact%' AND B.SEX_UPON_OUTCOME NOT LIKE '%Intact%';