[SQL] 프로그래머스 SQL문제풀이, 동물보호소

프로그래머스 SQL문제 중 동물보호소 자료를 대상으로 한 SQL문제를 풀이한 포스팅이다.

LEVEL 1, 2 문제

아래와 같은 ANIMAL_INS 테이블을 대상으로 쿼리를 수행한다. (마지막 문제는 제외)

-- 널값이 아닌 고유한 이름을 대상으로 개수 출력, distinct count & not null
SELECT COUNT(DISTINCT NAME) FROM ANIMAL_INS WHERE NAME IS NOT NULL

--가장 옛날 날짜 구하기, minimum date
SELECT MIN(DATETIME) AS 시간 FROM ANIMAL_INS

-- counting rows
SELECT COUNT(*) FROM animal_ins

-- multi column order by with asc, desc
SELECT ANIMAL_ID, NAME, DATETIME 
FROM ANIMAL_INS 
ORDER BY NAME ASC, DATETIME DESC

-- select with where and order by
SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS 
WHERE INTAKE_CONDITION="Sick" 
ORDER BY ANIMAL_ID

-- select order by with limit
SELECT NAME FROM ANIMAL_INS ORDER BY DATETIME ASC LIMIT 1

-- select with negative where
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION != 'Aged'

-- IS NOT NULL, ORDER BY ASC
SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL ORDER BY ANIMAL_ID ASC

-- 동명 동물 수 찾기, GROUP BY, HAVING, ORDER BY
SELECT NAME, COUNT(NAME) 
FROM ANIMAL_INS 
GROUP BY NAME HAVING COUNT(NAME) >= 2 
ORDER BY NAME

-- 이름에 el이 들어가는 멍멍이 찾기, LIKE, %
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS 
WHERE ANIMAL_TYPE = 'Dog' AND NAME LIKE '%el%' 
ORDER BY NAME ASC

-- 중성화 여부 파악하기, SELECT에서 조건에 맞게 컬럼에 출력하기 CASE
SELECT ANIMAL_ID, NAME,
CASE
	WHEN SEX_UPON_INTAKE LIKE '%Neutered%' THEN 'O'
	WHEN SEX_UPON_INTAKE LIKE '%Spayed%' THEN 'O'
    ELSE 'X'
END as 중성화
FROM ANIMAL_INS

-- 아래와 같이 IF문을 사용하는 것도 가능하다.
SELECT ANIMAL_ID, NAME,
	IF(SEX_UPON_INTAKE LIKE 'Intact%', 'X', 'O') as '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

-- 고양이와 개는 몇 마리 있을까?, IN 연산자, ORDER BY FIELD,
SELECT 
    ANIMAL_TYPE,
    COUNT(ANIMAL_ID) AS count
FROM ANIMAL_INS
WHERE ANIMAL_TYPE IN ('Cat', 'Dog')
GROUP BY ANIMAL_TYPE
ORDER BY FIELD(ANIMAL_TYPE, 'Cat', 'Dog');

-- 이름이 NULL인 동물 ID, IS NULL
SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL

-- 특정 조건을 만족하는 컬럼을 찾아서 정렬하기, WHERE IN ()
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID

-- 특정시간안에 입양된 동물의 구간합 구하기, '시' 추출하기 HOUR(DATETIME)
-- GROUP BY는 HAVING으로 필터하는거 까먹지 않기.
SELECT HOUR(DATETIME) as HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 and 19
ORDER BY HOUR

LEVEL 3, 4 문제

위의 ANIMAL_INS, ANIMAL_OUT 두 테이블을 대상으로 쿼리를 수행한다.

1. 조인, DATETIME '-' 연산자, 정렬

-- 입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 
-- SQL문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME
FROM ANIMAL_INS INNER JOIN ANIMAL_OUTS 
ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
ORDER BY ANIMAL_OUTS.DATETIME - ANIMAL_INS.DATETIME DESC
LIMIT 2

2. 조인, 서브쿼리, LIKE 연산자

-- 보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 
-- 보호소에 들어올 당시에는 중성화1되지 않았지만, 보호소를 나갈 당시에는 
-- 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.

SELECT A_IN.ANIMAL_ID, A_IN.ANIMAL_TYPE, A_IN.NAME
FROM ANIMAL_INS A_IN INNER JOIN ANIMAL_OUTS A_OUT ON A_IN.ANIMAL_ID = A_OUT.ANIMAL_ID
WHERE A_IN.SEX_UPON_INTAKE LIKE '%INTACT%' 
AND (A_OUT.SEX_UPON_OUTCOME LIKE '%SPAYED%' OR A_OUT.SEX_UPON_OUTCOME LIKE '%NEUTERED%')
ORDER BY A_IN.ANIMAL_ID

-- 다른사람의 아래 서브쿼리를 활용한 풀이가 더 깔끔하다. 성능도 좋아보인다.
SELECT ANIMAL_ID, ANIMAL_TYPE, NAME FROM ANIMAL_OUTS
WHERE ANIMAL_ID IN (SELECT ANIMAL_ID FROM ANIMAL_INS
WHERE SEX_UPON_INTAKE LIKE "%Intact%") && 
(SEX_UPON_OUTCOME LIKE "%Spayed%" || SEX_UPON_OUTCOME LIKE "%Neutered%")

3. 서브쿼리, NOT IN 연산자, 정렬

-- 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 
-- 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.
SELECT NAME, DATETIME 
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN
(SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY DATETIME
LIMIT 3

4. DATETIME 비교, JOIN WITH WHERE CLAUSE

--관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 
-- 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 
-- 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.
SELECT I.ANIMAL_ID, I.NAME 
FROM ANIMAL_INS I INNER JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.DATETIME > O.DATETIME
ORDER BY I.DATETIME 

5. 서브쿼리, NOT IN

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

6. GROUP BY WITH ZERO, SET 변수 선언,

처음에는 이렇게 풀이했다. 이렇게 풀이하면 카운트가 0인 튜플은 출력이 아예 안된다.
감을 전혀 못잡겠어서 풀이를 찾아봤다.

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

SELECT HOUR(DATETIME) AS HOUR, COUNT(*)
FROM ANIMAL_OUTS
GROUP BY HOUR WITH NULL
ORDER BY HOUR

이 글에 상세한 풀이가 나와있어 참고했다. 감사합니다.
블로그 설명을 복붙하기는 좀 그래서 GPT 형님께 자문을 구한 결과를 기록해본다.

이 SQL 쿼리는 다음과 같은 두 부분으로 구성되어 있습니다.

1. 변수 선언:

SET @HOUR := -1; # 변수선언

이 부분에서는 @HOUR이라는 사용자 정의 변수를 선언하고 초기값으로 -1을 할당합니다.

2. 주 쿼리:

SELECT (@HOUR := @HOUR +1) AS HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @HOUR) AS COUNT 
FROM ANIMAL_OUTS
WHERE @HOUR < 23

이 쿼리의 주요 목적은 ANIMAL_OUTS 테이블에서 각 시간별로 동물이 나간 횟수를 계산하는 것입니다. 쿼리는 다음과 같은 방식으로 작동합니다.

1. 첫 번째 SELECT 절에서 (@HOUR := @HOUR +1) AS HOUR을 사용하여 @HOUR 변수의 값을 1씩 증가시키면서 결과에 HOUR 열이 포함되도록 합니다. 이렇게 하면 쿼리의 결과에서 각 시간대를 표현할 수 있습니다.
2. 두 번째 SELECT 절에서는 COUNT(*)를 사용하여 ANIMAL_OUTS 테이블에서 해당 시간대에 발생한 동물 출입 횟수를 계산합니다. 이 부분의 쿼리는 각 시간대별로 ANIMAL_OUTS 테이블의 DATETIME 열에서 시간 정보를 추출하여(HOUR(DATETIME)) @HOUR 값과 일치하는지 비교합니다.
   마지막으로 WHERE 절에서 @HOUR < 23 조건을 사용하여 24시간 (0~23시)의 결과만 계산하도록 제한합니다.
3. 결과적으로 이 쿼리는 ANIMAL_OUTS 테이블에서 시간대별 동물 출입 횟수를 반환합니다. 반환되는 결과는 각 시간대(HOUR)와 해당 시간대의 동물 출입 횟수(COUNT)로 구성됩니다.