[프로그래머스] 4주차 MySQL 스터디 2

1. SELECT - [과일로 만든 아이스크림 고르기] _ LEVEL 1

다음은 아이스크림 가게의 상반기 주문 정보를 담은 FIRST_HALF 테이블과

아이스크림 성분에 대한 정보를 담은 ICECREAM_INFO 테이블입니다. 

상반기 아이스크림 총주문량이 3,000보다 높으면서 아이스크림의 주 성분이 과일인 아이스크림의 맛을 총주문량이 큰 순서대로 조회하는 SQL 문을 작성해주세요.

 

[ 정답 쿼리 ]

SELECT flavor
FROM first_half
WHERE 
    total_order > 3000
    AND flavor IN 
    (
        SELECT flavor 
        FROM icecream_info 
        WHERE ingredient_type = 'fruit_based'
    )
ORDER BY total_order DESC;

 

[ 풀이 ]

• 출력해야 하는 정보는 FIRST_HALF 테이블에, 조건의 일부 정보는 ICECREAM_INFO 테이블에 있으므로 서브쿼리 활용!

---

2. SUM, MAX, MIN - [조건에 맞는 아이템들의 가격의 총합 구하기] _ LEVEL 2

다음은 어느 한 게임에서 사용되는 아이템들의 아이템 정보를 담은 ITEM_INFO 테이블입니다.

ITEM_INFO 테이블에서 희귀도가 'LEGEND'인 아이템들의 가격의 총합을 구하는 SQL문을 작성해 주세요. 이때 컬럼명은 'TOTAL_PRICE'로 지정해 주세요.

 

[ 정답 쿼리 ]

SELECT SUM(price) AS total_price
FROM item_info
WHERE rarity = 'LEGEND';

---

3. GROUP BY - [카테고리 별 도서 판매량 집계하기] _ LEVEL 3

다음은 어느 한 서점에서 판매중인 도서들의 도서 정보(BOOK), 판매 정보(BOOK_SALES) 테이블입니다.

2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리(CATEGORY), 총 판매량(TOTAL_SALES) 리스트를 출력하는 SQL문을 작성해주세요.

결과는 카테고리명을 기준으로 오름차순 정렬해주세요.

 

[ 정답 쿼리 ]

SELECT 
    category
    , SUM(sales) AS total_sales
FROM book B
JOIN book_sales S ON B.book_id = S.book_id
WHERE sales_date LIKE '2022-01%'
GROUP BY category
ORDER BY category;

 

[ 풀이 ]

• 출력해야 하는 두 field가 각각의 테이블에 분산되어 존재하기 때문에, JOIN을 사용하여 푸는 것이 가장 간단한 방법!

---

4. IS NULL - [업그레이드 할 수 없는 아이템 구하기] _ LEVEL 3

다음은 해당 게임에서 사용되는 아이템 정보를 담은 ITEM_INFO 테이블과 아이템 관계를 나타낸 ITEM_TREE 테이블입니다.

각 아이템들은 오직 하나의 PARENT 아이템 ID 를 가지며, ROOT 아이템의 PARENT 아이템 ID 는 NULL 입니다. ROOT 아이템이 없는 경우는 존재하지 않습니다.

더 이상 업그레이드할 수 없는 아이템의 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME), 아이템의 희귀도(RARITY)를 출력하는 SQL 문을 작성해 주세요. 이때 결과는 아이템 ID를 기준으로 내림차순 정렬해 주세요.

 

[ 정답 쿼리 ]

SELECT item_id, item_name, rarity
FROM item_info
WHERE 
    item_id NOT IN 
    (
        SELECT DISTINCT parent_item_id 
        FROM item_tree 
        WHERE parent_item_id IS NOT NULL
    )
ORDER BY item_id DESC;

 

[ 풀이 ]

• 더 이상 업그레이드할 수 없는 아이템 = ITEM_TREE 테이블에서 parent_item_id 필드에 나타나지 않는 아이템
• 단, ITEM_TREE 테이블에서 NOT IN 사용 시 비교대상에 NULL인 경우가 존재하면, 해당 쿼리가 오류나기 때문에 해당 경우는 제외하고 비교 → NOT IN의 비교에 관해 자세히 정리한 글을 따로 첨부한다

https://ddunddan.tistory.com/36

NOT IN, NOT EXISTS와 NULL

 

---

5. JOIN - [있었는데요 없었습니다] _ LEVEL 3

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

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

 

[ 정답 쿼리 ]

SELECT I.animal_id, I.name
FROM animal_ins I
JOIN animal_outs O
ON I.animal_id = O.animal_id
WHERE O.datetime < I.datetime 
ORDER BY I.datetime;

 

[ 풀이 ]

1. JOIN 방식 : 해당 기관의 보호 시작일과 입양일을 비교한다는 것은, 두 테이블에 공통적으로 존재하는 동물개체에 대해서만 살피겠다 → INNER JOIN
2. 조건(보호 시작일보다 입양일이 더 빠른 동물) : 입양일 < 보호 시작일(OUTS.datetime < INS.datetime)

---

6. String, Date - [대여 기록이 존재하는 자동차 리스트 구하기] _ LEVEL 3

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 자동차 종류가 '세단'인 자동차들 중 10월에 대여를 시작한 기록이 있는 자동차 ID 리스트를 출력하는 SQL문을 작성해주세요. 자동차 ID 리스트는 중복이 없어야 하며, 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

[ 정답 쿼리 ]

SELECT DISTINCT car_id
FROM car_rental_company_rental_history
WHERE car_id IN 
(
    SELECT car_id 
    FROM car_rental_company_car 
    WHERE car_type = '세단'
)
    AND MONTH(start_date) = 10
ORDER BY 1 DESC;

 

[ 풀이 ]

• 조건 하나는 CAR_RENTAL_COMPANY_CAR 테이블에, 하나는  CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에 존재하므로, 굳이 JOIN을 사용하지 않고 서브쿼리 만으로 해결 가능