[프로그래머스] SQL - JOIN 4문제

한지원·2021년 3월 1일

릴레이션 스키마

4문제 모두에서 같은 테이블 구조를 사용한다.

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다.

NAME	Type	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
INTAKE_CONDITION	VARCHAR(N)	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_INTAKE	VARCHAR(N)	FALSE

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다.

ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다.

NAME	Type	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_OUTCOME	VARCHAR(N)	FALSE

없어진 기록 찾기

천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

문제에서 보여주는 예시를 토대로 DDL과 DML을 작성해보았다.

해당 문제의 예시대로 출력해보고 싶다면 아래의 sql문을 실행시킨 후 sql을 작성해보면 될 것이다.
(외래키를 따로 적용시켜주지 않았다.)

#DROP TABLE animal_ins;
#DROP TABLE animal_outs;

CREATE TABLE animal_ins(
  animal_id varchar(20) NOT null,
  animal_type varchar(20) not null,
  datetime datetime not null,
  intake_condition varchar(20) not null,
  name varchar(20),
  sex_upon_intake varchar(20) not null);

CREATE TABLE animal_outs(
  animal_id varchar(20) not null,
  animal_type varchar(20) NOT null,
  datetime datetime not null,
  name varchar(20),
  sex_upon_outcome varchar(20) not null);

  
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) values ('A352713', 'Cat', '2017-04-13 16:29:00', 'Normal', 'Gia', 'Spayed female');
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) values ('A350375', 'cat', '2017-03-06 15:01:00	', 'Normal', 'Meo', 'Neutered Male');

insert into animal_outs(animal_id, animal_type, datetime, name, sex_upon_outcome) values ('A349733', 'Dog', '2017-09-27 19:09:00', 'Allie', 'Spayed female');
insert into animal_outs(animal_id, animal_type, datetime, name, sex_upon_outcome) values ('A352713', 'Cat', '2017-04-25 12:25:00', 'Gia', 'Spayed female'); 
insert into animal_outs(animal_id, animal_type, datetime, name, sex_upon_outcome) values ('A349990', 'Cat', '2018-02-02 14:18:00', 'Spice', 'Spayed female');

solution

SELECT animal_id, name
from animal_outs
where animal_id not in (select animal_id from animal_ins);

where문에서 animal_ins의 animal_id만 뽑아주는 서브쿼리를 작성하고 not in 키워드를 이용해 그 중 animal_out의 animal_id에 들어있지 않은 튜플의 animal_id, name 칼럼만 출력시켜주었다.

있었는데요 없었습니다

관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

문제 예시 테이블 생성

#DROP TABLE animal_ins;
#DROP TABLE animal_outs;

CREATE TABLE animal_ins(
  animal_id varchar(20) NOT null,
  animal_type varchar(20) not null,
  datetime datetime not null,
  intake_condition varchar(20) not null,
  name varchar(20),
  sex_upon_intake varchar(20) not null);

CREATE TABLE animal_outs(
  animal_id varchar(20) not null,
  animal_type varchar(20) NOT null,
  datetime datetime not null,
  name varchar(20),
  sex_upon_outcome varchar(20) not null);
  
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) VALUES ('A350276', 'Cat', '2014-05-02 12:16:00', 'Normal', 'Jewel', 'Spayed Female');
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) VALUES ('A381217', 'Dog', '2017-07-08 09:41:00', 'Sick', 'Cherokee', 'Neutered Male');

insert into animal_outs(animal_id, animal_type, datetime, name, sex_upon_outcome) values ('A350276', 'Cat', '2018-01-28 17:51:00', 'Jewel', 'Spayed Female');
insert into animal_outs(animal_id, animal_type, datetime, name, sex_upon_outcome) values ('A381217', 'Dog', '2017-06-09 18:51:00', 'Cherokee', 'Neutered Male');

solution

select ai.animal_id, ai.name
from animal_ins as ai, animal_outs as ao
where ai.animal_id = ao.animal_id and date_format(ai.datetime, '%Y-%m-%d %H:%i:%s') > date_format(ao.datetime, '%Y-%m-%d %H:%i:%s')
order by ai.datetime;

도메인타입 중 datetime의 비교를 위해 date_format(,)을 이용했다.

date_formay()의 파라미터

parameter info
%a 3글자 요일 영문자 (Sun..Sat)
%b 3글자 월 영문자 (Jan..Dec)
%c 숫자 월 (0..12)
%D 영어 서수로 날짜 표시 (0th, 1st, 2nd, 3rd, …)
%d 숫자 날짜 2자리(0채움) (00..31)
%e 숫자 날짜 (0..31)
%f 마이크로세컨드 (000000..999999)
%H 24시간 시(0채움) (00..23)
%h 12시간 시(0채움) (01..12)
%I 12시간 시(0채움) (01..12)
%i 2자리 분(0채움) (00..59)
%j 1년의 몇번째날(0채움) (001..366)
%k 24시간 시 (0..23)
%l 12시간 시 (1..12)
%M 영문 월 전체길이 (January..December)
%m 숫자 월(0채움) (00..12)
%p AM 또는 PM
%r 12시간 단위 AM/PM 시:분:초 (AM 또는 PM 뒤에 hh:mm:ss)
%S 초(0채움) (00..59)
%s 초(0채움)(00..59)
%T Time, 24-hour (hh:mm:ss)
%U 올해의 몇번째주(2자리 0채움) (00..53) - 일요일이 주의 첫요일임. week() 모드 1
%u 올해의 몇번째주(2자리 0채움) (00..53), 월요일이 주의 첫요일임. week() 모드 2
%V 올해의 몇번째주(0채움) (01..53), 일요일이 주의 첫번째 날임. WEEK() 모드 2; %X 와 함께 사용
%v 올해의 몇번째주(0채움) (01..53), 월요일이 주의 첫번째 날임. WEEK() 모드 3; %x 와 함께 사용
%W 전체 길이 영문 요일 (Sunday..Saturday)
%w 요일 순서 숫자 (0=일요일..6=토요일)
%X 일요일이 첫요일인 주 표시를 위한 4자리 숫자 년도, %V 와 함께 사용(일요일부터 시작하는 2019년도 첫째주)
%x 월요일이 첫요일인 주 표시를 위한 4자리 숫자 년도, %v 와 함께 사용(월요일부터 시작하는 2019년도 첫째주)
%Y 4자리 년도
%y 2자리 년도
%% %문자 표시

출처: https://blogpack.tistory.com/483

parameter	info
%a	3글자 요일 영문자 (Sun..Sat)
%b	3글자 월 영문자 (Jan..Dec)
%c	숫자 월 (0..12)
%D	영어 서수로 날짜 표시 (0th, 1st, 2nd, 3rd, …)
%d	숫자 날짜 2자리(0채움) (00..31)
%e	숫자 날짜 (0..31)
%f	마이크로세컨드 (000000..999999)
%H	24시간 시(0채움) (00..23)
%h	12시간 시(0채움) (01..12)
%I	12시간 시(0채움) (01..12)
%i	2자리 분(0채움) (00..59)
%j	1년의 몇번째날(0채움) (001..366)
%k	24시간 시 (0..23)
%l	12시간 시 (1..12)
%M	영문 월 전체길이 (January..December)
%m	숫자 월(0채움) (00..12)
%p	AM 또는 PM
%r	12시간 단위 AM/PM 시:분:초 (AM 또는 PM 뒤에 hh:mm:ss)
%S	초(0채움) (00..59)
%s	초(0채움)(00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	올해의 몇번째주(2자리 0채움) (00..53) - 일요일이 주의 첫요일임. week() 모드 1
%u	올해의 몇번째주(2자리 0채움) (00..53), 월요일이 주의 첫요일임. week() 모드 2
%V	올해의 몇번째주(0채움) (01..53), 일요일이 주의 첫번째 날임. WEEK() 모드 2; %X 와 함께 사용
%v	올해의 몇번째주(0채움) (01..53), 월요일이 주의 첫번째 날임. WEEK() 모드 3; %x 와 함께 사용
%W	전체 길이 영문 요일 (Sunday..Saturday)
%w	요일 순서 숫자 (0=일요일..6=토요일)
%X	일요일이 첫요일인 주 표시를 위한 4자리 숫자 년도, %V 와 함께 사용(일요일부터 시작하는 2019년도 첫째주)
%x	월요일이 첫요일인 주 표시를 위한 4자리 숫자 년도, %v 와 함께 사용(월요일부터 시작하는 2019년도 첫째주)
%Y	4자리 년도
%y	2자리 년도
%%	%문자 표시

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

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

문제 예시 테이블 생성

#drop table animal_ins;
#DROP TABLE animal_outs;

CREATE TABLE animal_ins(
  animal_id varchar(20) NOT null,
  animal_type varchar(20) not null,
  datetime datetime not null,
  intake_condition varchar(20) not null,
  name varchar(20),
  sex_upon_intake varchar(20) not null);

CREATE TABLE animal_outs(
  animal_id varchar(20) not null,
  animal_type varchar(20) NOT null,
  datetime datetime not null,
  name varchar(20),
  sex_upon_outcome varchar(20) not null);
  
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) VALUES ('A354597', 'Cat', '2014-05-02 12:16:00', 'Normal', 'Ariel', 'Spayed Female');
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) VALUES ('A373687', 'Dog', '2014-03-20 12:31:00', 'Normal', 'Rosie', 'Spayed Female');
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) VALUES ('A412697', 'Dog', '2016-01-03 16:25:00', 'Normal', 'Jackie', 'Spayed Female');
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) VALUES ('A413789', 'Dog', '2016-04-19 13:28:00', 'Normal', 'Benji', 'Spayed Female');
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) VALUES ('A414198', 'Dog', '2015-01-29 15:01:00', 'Normal', 'Shelly', 'Spayed Female');
insert into animal_ins(animal_id, animal_type, datetime, intake_condition,  sex_upon_intake) VALUES ('A368930', 'Dog', '2014-06-08 13:20:00', 'Normal', 'Neutered Male');

insert into animal_outs(animal_id, animal_type, datetime, name, sex_upon_outcome) values ('A354597', 'Cat', '2014-05-02 12:16:00', 'Ariel', 'Spayed Female');
insert into animal_outs(animal_id, animal_type, datetime, name, sex_upon_outcome) values ('A373687', 'Dog', '2014-03-20 12:31:00', 'Rosie', 'Spayed Female');
insert into animal_outs(animal_id, animal_type, datetime, sex_upon_outcome) values ('A368930', 'Dog', '2014-06-13 15:52:00', 'Spayed Female');

solution

select name, datetime
from animal_ins
where animal_ins.animal_id not in (select animal_id from animal_outs)
order by datetime
LIMIT 3;

아직 입양을 못간 동물을 출력하는 것이기 때문에 where절에서 보호소에 들어온 모든 동물의 정보가 담긴 animal_ins테이블에는 있지만 입양간 동물의 정보인 animal_outs 테이블에는 없는 동물을 뽑아내고 order by를 이용해 datetime의 오름차순으로 정렬한 뒤 limit을 이용해 튜플의 수를 제한하여 출력한다.

보호소에서 중성화한 동물

보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.
(중성화를 거치지 않은 동물은 성별 및 중성화 여부에 Intact, 중성화를 거친 동물은 Spayed 또는 Neutered라고 표시되어있습니다. )

문제 예시 테이블 생성

#drop table animal_ins;
#DROP TABLE animal_outs;

CREATE TABLE animal_ins(
  animal_id varchar(20) NOT null,
  animal_type varchar(20) not null,
  datetime datetime not null,
  intake_condition varchar(20) not null,
  name varchar(20),
  sex_upon_intake varchar(20) not null);


CREATE TABLE animal_outs(
  animal_id varchar(20) not null,
  animal_type varchar(20) NOT null,
  datetime datetime not null,
  name varchar(20),
  sex_upon_outcome varchar(20) not null);
  
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) VALUES ('A367438', 'Dog', '2015-09-10 16:01:00', 'Normal', 'Cookie', 'Spayed Female');
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) VALUES ('A382192', 'Dog', '2015-03-13 13:14:00', 'Normal', 'Maxwell 2	', 'Intact Male');
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) VALUES ('A405494', 'Dog', '2014-05-16 14:17:00', 'Normal', 'Kaila', 'Spayed Female');
insert into animal_ins(animal_id, animal_type, datetime, intake_condition, name, sex_upon_intake) VALUES ('A410330', 'Dog', '2016-09-11 14:09:00', 'Sick', 'Chewy', 'Intact Female');

insert into animal_outs(animal_id, animal_type, datetime, name, sex_upon_outcome) values ('A367438', 'Dog', '2015-09-12 13:30:00', 'Cookie', 'Spayed Female');
insert into animal_outs(animal_id, animal_type, datetime, name, sex_upon_outcome) values ('A382192', 'Dog', '2015-03-16 13:46:00', 'Maxwell 2', 'Neutered Male');
insert into animal_outs(animal_id, animal_type, datetime, name, sex_upon_outcome) values ('A405494', 'Dog', '2014-05-20 11:44:00', 'Kaila', 'Spayed Female');
insert into animal_outs(animal_id, animal_type, datetime, name, sex_upon_outcome) values ('A410330', 'Dog', '2016-09-13 13:46:00', 'Chewy', 'Spayed Female');

solution

select ai.animal_id, ai.animal_type, ai.name
from (SELECT * from animal_ins where sex_upon_intake LIKE 'Intact%') as ai, animal_outs as ao
where ai.animal_id = ao.animal_id and (ao.sex_upon_outcome LIKE 'Spayed%' or ao.sex_upon_outcome LIKE 'Neutered%')
order by ai.animal_id;

중성화를 나타내는 키워드가 존재하는지 여부를 like와 와일드카드 문자로 찾아낼 수 있는지 확인하는 문제였다.

한지원

이전 포스트

[Python] 문자열 리스트 정렬

다음 포스트

[프로그래머스] SQL - JOIN 4문제

릴레이션 스키마

없어진 기록 찾기

있었는데요 없었습니다

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

보호소에서 중성화한 동물

[Python] 문자열 리스트 정렬

[프로그래머스] 스킬 체크 LEVEL 1

0개의 댓글