Programmers: MySQL Lv. 2

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

#1 고양이와 개는 몇 마리 있을까

동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.

풀이

SELECT ANIMAL_TYPE, count(*)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

ANIMAL_TYPE으로 그룹화 및 정렬해준 뒤 count(*)를 이용하여 각 ANIMAL_TYPE에 해당하는 모든 행의 개수를 구해주었다.

#2 루시와 엘라 찾기

동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요.

풀이

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID;

IN 구문을 이용해 문제에서 언급한 이름을 가진 모든 동물의 정보를 추출하였다. OR을 사용해서도 쿼리를 작성할 수 있지만, IN을 사용해 작성하면 훨씬 직관적이고 간단하게 작성할 수 있다.

#3 최솟값 구하기

동물 보호소에 가장 먼저 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.

풀이

SELECT MIN(DATETIME) FROM ANIMAL_INS

해당 테이블에서 DATETIME의 최솟값을 구해주면 된다.

#4 동명 동물 수 찾기

동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.

풀이

SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME;

문제에서 제시한 조건에 맞는 데이터를 추출하기 위해, 중요한 조건을 두가지를 살펴보도록 하자.

1. 두 번 이상 쓰인 이름을 구하라

   두 번 이상 쓰인 이름을 찾기 위해 NAME으로 그룹화 해준 뒤, 두 번 이상 카운트 되는 이름만을 선택하도록 조건문을 작성하였다.

2. 이름이 없는 동물은 집계에서 제외하라

   이름이 없는 동물, 즉 NAME에 NULL 값을 가지는 동물들을 제외하기 위해 IS NOT NULL 구문을 이용하였다.

#5 이름에 el이 들어가는 동물 찾기

보호소에 돌아가신 할머니가 기르던 개를 찾는 사람이 찾아왔습니다. 이 사람이 말하길 할머니가 기르던 개는 이름에 'el'이 들어간다고 합니다. 동물 보호소에 들어온 동물 이름 중, 이름에 "EL"이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 이름 순으로 조회해주세요. 단, 이름의 대소문자는 구분하지 않습니다.

풀이

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog' and NAME LIKE '%EL%'
ORDER BY NAME;

SQL에서는 LIKE 절과 와일드 문자를 이용하여 특정 문자열을 포함하는지 확인할 수 있다. 따라서 위와 같이 NAME LIKE '%EL%라고 작성해주면, EL을 포함하는 NAME을 선택하라는 것과 같다.

#6 동물 수 구하기

동물 보호소에 동물이 몇 마리 들어왔는지 조회하는 SQL 문을 작성해주세요.

풀이

SELECT COUNT(ANIMAL_ID) FROM ANIMAL_INS

동물 별로 고유한 ANIMAL_ID를 가지고 있기 때문에 동물 보호소에 들어온 동물의 수를 구하고 싶을 땐 ANIMAL_ID의 개수를 세어 보면 된다.

#7 입양 시각 구하기 (1)

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

풀이

SELECT HOUR(DATETIME) HOUR, COUNT(DATETIME) COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
HAVING HOUR BETWEEN 9 AND 19
ORDER BY HOUR;

날짜에서 시간만 확인하기 위해서는 HOUR() 함수를 이용해주면 된다. 시간대별로 입양 건수를 비교하기 위해 GROUP BY 함수를 사용했으며, 그룹에 대한 조건으로 9시와 19시 사이를 설정해 주었다.

#8 NULL 처리하기

입양 게시판에 동물 정보를 게시하려 합니다. 동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 "No name"으로 표시해 주세요.

풀이

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'), SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

이름이 없는 동물의 경우 NAME이 NULL 값인데, 이러한 동물들을 No name으로 표시하기 위해 IFNULL 함수를 사용하였다. IFNULL은 변수로 열 이름과 NULL일 경우 대신 출력할 값이 들어간다.

따라서 IFNULL(NAME, 'No name')는 NAME 열이 NULL일 시에 No name을 반환하라는 의미이다.

#9 중성화 여부 파악하기

보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

풀이

SELECT ANIMAL_ID, NAME, IF(SEX_UPON_INTAKE LIKE '%NEUTERED%' OR SEX_UPON_INTAKE LIKE '%SPAYED%','O','X') '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

문제에서 요구한 중성화 여부를 나타내는 열을 출력하기 위해 IF 구문을 사용했다. IF 구문은 변수로 조건문, 참일 때 값, 거짓일 때 값 총 3개를 필요로 한다.

따라서 위 코드에서 IF(SEX_UPON_INTAKE LIKE '%NEUTERED%' OR SEX_UPON_INTAKE LIKE '%SPAYED%','O','X')는 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있을 때, 즉 중성화가 된 동물이라면 O를 출력하고, 그렇지 않다면 X를 출력하라는 의미이다.

#10 중복 제거하기

동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL 문을 작성해주세요. 이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 칩니다.

풀이

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL;

중복을 제거한 고유 값만을 출력하기 위해서는 DISTINCT를 이용할 수 있다. 따라서 COUNT(DISTINCT column)은 열의 고유한 값의 개수를 출력해준다.

#11 DATETIME에서 DATE로 형 변환

ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜를 조회하는 SQL문을 작성해주세요. 이때 결과는 아이디 순으로 조회해야 합니다.

풀이

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

DATETIME 열은 2018-01-22 14:32:00와 같이 날짜뿐만 아니라 시간까지 포함하는 값이다. 따라서 해당 열에서 날짜만을 출력하기 위해서는 DATE_FORMAT이라는 함수를 이용할 수 있고, 구분기호를 활용하여 원하는 형식으로 날짜를 출력할 수 있다.

문제에서 요구한 대로 2018-01-22 형식으로 날짜를 출력하기 위해 구분기호는 %Y-%m-%d와 같이 작성해 주었다.

---

Reference

https://school.programmers.co.kr/learn/challenges