(다 푼 후 사진 추가 예정)
너무 간단하거나 이전 문제 풀이와 비슷하다고 생각하는 문제는 작성하지 않았다.
카테고리 별 도서 판매량 집계하기
판매 중인 도서 정보 테이블(BOOK)과 판매 정보 테이블(BOOK_SALES)이 주어진다.
2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리와 총 판매량을 구하여라.
결과는 카테고리의 오름차순으로 나타낸다.
select CATEGORY, sum(SALES) TOTAL_SALES
from BOOK natural join BOOK_SALES
where YEAR(SALES_DATE) = 2022 and MONTH(SALES_DATE) = 1
group by CATEGORY
order by CATEGORY- 문제가 시키는 대로 2022년 1월의 도서 판매 목록을 구하고, 이를 도서 정보와 JOIN한 다음 GROUP BY를 이용해 카테고리 별로 묶은 다음 판매량의 총합을 구하였다.
- 옛날에 푼 문제라 그런지 NATURAL JOIN을 사용했는데, JOIN USING을 사용하는 것이 더 직관적인 것 같다.
- 정렬도 오름차순이 기본인 점을 이용해서 작성하지 않았는데, 뒤에 ASC을 추가해야 직관적으로 보인다.
저자 별 카테고리 별 매출액 집계하기
판매 중인 도서 정보 테이블(BOOK), 저자 정보 테이블(AUTHOR), 판매 정보 테이블(BOOK_SALES)이 주어진다.
2022년 1월의 저자 및 카테고리 별 매출액과 각 매출액의 저자 정보, 카테고리를 구하여라.
결과는 저자 ID의 오름차순, 카테고리의 내림차순으로 나타낸다.
나의 풀이
select AUTHOR_ID, AUTHOR_NAME, CATEGORY, sum(EACH_BOOK_SALES) as TOTAL_SALES
from (select BOOK_ID, CATEGORY, AUTHOR_ID, (PRICE * SALES) as EACH_BOOK_SALES
from (select BOOK_ID, SALES
from BOOK_SALES
where SALES_DATE like "2022-01%") A join BOOK B using(BOOK_ID)) A
join AUTHOR B using(AUTHOR_ID)
group by AUTHOR_ID, CATEGORY
order by AUTHOR_ID asc, CATEGORY desc- 가장 먼저 2022년 1월의 도서 판매 정보를 구한다. 이후 각 정보에 대해 도서 정보를 붙여줌과 동시에 매출액도 계산한다. 이후 저자 ID와 카테고리로 그룹화를 하여 각 그룹의 총 매출액을 구했다.
GPT의 풀이
SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(S.SALES * B.PRICE) AS TOTAL_SALES
FROM BOOK_SALES S
JOIN BOOK B ON S.BOOK_ID = B.BOOK_ID
JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE S.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY A.AUTHOR_ID, B.CATEGORY
ORDER BY A.AUTHOR_ID ASC, B.CATEGORY DESC;- 놀랍게도 서브쿼리를 사용하지 않고 단순 조인만으로도 문제를 해결할 수 있었다. 이 방식이 깔끔하긴 한데, 이렇게 한 번에 생각하는 것보다 단계적으로 문제를 해결하는 것도 괜찮다고 생각한다.
식품분류별 가장 비싼 식품의 정보 조회하기
식품 정보 테이블(FOOD_PRODUCT) 테이블이 주어진다.
이때 과자, 국, 김치, 식용유 각 식품분류 별로 가격이 가장 비싼 식품의 정보를 구하여라.
결과는 식품 가격의 내림차순으로 나타낸다.
select CATEGORY, PRICE MAX_PRICE, PRODUCT_NAME
from FOOD_PRODUCT
where (CATEGORY, PRICE) in (select CATEGORY, max(PRICE) MAX_PRICE
from FOOD_PRODUCT
where CATEGORY
in ("과자", "국", "김치", "식용유")
group by CATEGORY)
order by MAX_PRICE desc- 먼저 각 식품 분류 별로 가격이 가장 비싼 식품을 찾는다. 이를 이용해 각 식품의 정보를 얻는다.
- 서브쿼리를 사용하지 않고 가장 비싼 식품을 찾을 때 PRODUCT_NAME을 함께 구하려고 하면 가장 비싼 식품의 이름이 아니라 (아마도) 가장 위에 있는 식품의 이름이 얻어지기 때문에 이렇게 서브쿼리를 사용해야 한다.
자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기
자동차 대여 기록 정보(CAR_RENTAL_COMPANY_RENTAL_HISTORY) 테이블이 주어진다.
이때 2022년 10월 16일의 대여 여부를 나타내는 컬럼(AVAILABILITY)을 추가해 자동차 ID와 AVAILABILITY를 구하여라.
결과는 자동차 ID의 내림차순으로 나타낸다.
select distinct CAR_ID, ifnull(AVAILABILITY, "대여 가능")
from CAR_RENTAL_COMPANY_RENTAL_HISTORY A left join
(select distinct CAR_ID, "대여중" as AVAILABILITY
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where START_DATE <= '2022-10-16' and END_DATE >= '2022-10-16') B using(CAR_ID)
order by CAR_ID desc- 먼저 2022년 10월 16일이 포함되는 대여 기록을 찾아 "대여중"인 자동차 ID를 얻는다. 이를 대여 기록과 OUTER JOIN을 해서 "대여중"이 아닌 경우에는 NULL을 얻게 하여 IFNULL 함수를 사용해 "대여 가능"을 나타내도록 하였다.
- GPT한테도 물어봤는데, 틀린 답을 알려줘서 적지는 않았다. 서브쿼리를 사용하지 않고 CASE문을 사용하는 접근인데, 대여 기록에 CASE를 적용해버리면 대여 가능과 대여중이 함께 나타날 수도 있어서 틀리게 된다.
대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
자동차 대여 기록 정보(CAR_RENTAL_COMPANY_RENTAL_HISTORY) 테이블이 주어진다.
이때 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차에 대해 월 및 자동차 별 총 대여 횟수를 나타내는 컬럼(RECORDS)를 추가해 대여 월, 자동차 ID, RECORDS를 구하여라.
결과는 월의 오름차순, 자동차 ID의 내림차순으로 나타낸다.
select MONTH(START_DATE) as MONTH, CAR_ID, count(*) as RECORDS
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where (CAR_ID in (select CAR_ID
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where START_DATE between '2022-08-01' and '2022-10-31'
group by CAR_ID
having count(*) >= 5))
and (START_DATE between '2022-08-01' and '2022-10-31')
group by MONTH, CAR_ID
order by MONTH asc, CAR_ID desc- 먼저 2022년 8월과 10월 사이에 총 대여횟수가 5회 이상인 자동차를 찾는다. 이후 대여 월과 자동차 ID로 그룹화를 해서 각 그룹의 대여 횟수를 얻는다.
- 바깥쪽 쿼리에도 대여 일자 범위 조건을 추가해야 한다는 점을 미처 생각하지 못해 많이 틀렸다. 이를 추가하지 않으면 다른 년이나 월의 값이 포함될 수 있기 때문에 반드시 추가해야 한다.
(추가 예정)
