Programmers: MySQL Lv. 4

#1 우유와 요거트가 담긴 장바구니

CART_PRODUCTS 테이블은 장바구니에 담긴 상품 정보를 담은 테이블입니다. CART_PRODUCTS 테이블의 구조는 다음과 같으며, ID, CART_ID, NAME, PRICE는 각각 테이블의 아이디, 장바구니의 아이디, 상품 종류, 가격을 나타냅니다.

데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요. 이때 결과는 장바구니의 아이디 순으로 나와야 합니다.

풀이

SELECT cart_id FROM cart_products
WHERE name in ("Milk","Yogurt")
GROUP BY cart_id
HAVING COUNT(distinct name) = 2
ORDER BY cart_id;

우유와 요거트를 동시에 구입한 장바구니를 찾기 위한 과정은 다음과 같았다.

1. NAME이 Milk 또는 Yogurt인 것만 추출하도록 조건을 준다.
2. CART_ID로 그룹화를 해준다.
3. HAVING을 이용해 CART_ID별로 중복되지 않은 NAME이 2개인 것을 조회하라는 조건을 준다.
   (1번에서 NAME이 Milk 또는 Yogurt인 것을 조회하라는 조건을 줬으므로, 중복되지 않은 NAME이 2개라는 것은 Milk와 Yogurt를 모두 가지고 있다는 것과 같은 의미이다.)

#2 입양 시각 구하기 (2)

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

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

풀이

SET @hour := -1;

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

해당 문제를 풀기 위해서는 변수 설정이 필요했다. 변수를 설정하지 않고 풀면 0시부터 23시까지 모든 값을 조회하지 않고, COUNT가 0인 시간대는 제외시킨 결과가 도출되므로 유의해야 한다.

먼저 SET @hour := -1을 통해 hour이라는 변수에 -1이라는 값을 대입해준다. SELECT 구문의 @hour := @hour + 1과 WHERE @hour < 23의 WHERE 절을 통해 hour이 23보다 작은 동안은 계속 +1을 하며 반복하게 만들어 주었다. 따라서 0부터 23까지의 hour이 출력될 수 있는 것이다.

또한 서브쿼리를 이용해 해당 시간대(hour)에 입양을 간 동물의 수를 조회할 수 있도록 쿼리를 작성하였다.

다른 풀이

-- 1
SELECT TIME as HOUR, COUNT(ANIMAL_ID) as COUNT
FROM (SELECT 0 AS TIME
UNION ALL
SELECT 1 AS TIME
UNION ALL
SELECT 2 AS TIME
UNION ALL
SELECT 3 AS TIME
UNION ALL
SELECT 4 AS TIME
UNION ALL
SELECT 5 AS TIME
UNION ALL
SELECT 6 AS TIME
UNION ALL
SELECT 7 AS TIME
UNION ALL
SELECT 8 AS TIME
UNION ALL
SELECT 9 AS TIME
UNION ALL
SELECT 10 AS TIME
UNION ALL
SELECT 11 AS TIME
UNION ALL
SELECT 12 AS TIME
UNION ALL
SELECT 13 AS TIME
UNION ALL
SELECT 14 AS TIME
UNION ALL
SELECT 15 AS TIME
UNION ALL
SELECT 16 AS TIME
UNION ALL
SELECT 17 AS TIME
UNION ALL
SELECT 18 AS TIME
UNION ALL
SELECT 19 AS TIME
UNION ALL
SELECT 20 AS TIME
UNION ALL
SELECT 21 AS TIME
UNION ALL
SELECT 22 AS TIME
UNION ALL
SELECT 23 AS TIME) as A LEFT JOIN ANIMAL_OUTS as B ON A.TIME = HOUR(B.DATETIME)
GROUP BY TIME

-- 2
WITH RECURSIVE TEMP_TABLE AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR+1 FROM TEMP_TABLE WHERE HOUR < 23
)

SELECT HOUR, count(ANIMAL_ID) as COUNT
FROM TEMP_TABLE
    LEFT OUTER JOIN ANIMAL_OUTS
    ON HOUR(DATETIME) = TEMP_TABLE.HOUR
group by HOUR
order by HOUR

#3 보호소에서 중성화한 동물

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는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

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

풀이

SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
FROM ANIMAL_INS A JOIN ANIMAL_OUTS B
WHERE A.ANIMAL_ID = B.ANIMAL_ID
AND A.SEX_UPON_INTAKE <> B.SEX_UPON_OUTCOME
ORDER BY A.ANIMAL_ID;

일단 해당 문제는 테이블 두개를 모두 고려하여 결과를 도출해야 하는 문제이므로, 문제에서 제시한 두 테이블을 조인하여 쿼리를 작성했다.

문제는 생각보다 간단하게 풀렸는데, 두 테이블(동물 보호소에 들어온 동물의 정보, 입양 보낸 동물의 정보를 담은 테이블)에서 ANIMAL_ID가 같으면서, 중성화 여부인 SEX_UPON_INTAKE과 SEX_UPON_OUTCOME이 다른 동물을 조회해주면 된다.

• 중성화 O → 중성화 X : 불가능
• 중성화 X → 중성화 O : 가능

이므로 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물을 확인하기 위해서는, 들어올 당시와 나갈 당시의 중성화 상태가 다른 동물을 찾으면 된다.

다른 풀이

-- 1
SELECT outs.ANIMAL_ID, outs.ANIMAL_TYPE, outs.NAME
FROM ANIMAL_INS as ins right join ANIMAL_OUTS as outs
ON ins.ANIMAL_ID = outs.ANIMAL_ID
WHERE ins.SEX_UPON_INTAKE IN ('Intact Male', 'Intact Female') 
AND
outs.SEX_UPON_OUTCOME IN ('Neutered Male', 'Neutered Female', 'Spayed Male','Spayed Female')

-- 2
SELECT AO.ANIMAL_ID, AO.ANIMAL_TYPE, AO.NAME
FROM ANIMAL_INS AI INNER JOIN ANIMAL_OUTS AO ON AO.ANIMAL_ID = AI.ANIMAL_ID
WHERE AI.SEX_UPON_INTAKE LIKE '%INTACT%' AND (AO.SEX_UPON_OUTCOME LIKE '%SPAYED%' OR
                                              AO.SEX_UPON_OUTCOME LIKE '%NEUTERED%')

---

Reference

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