SQL 46-50.

46. 대여 기록이 존재하는 자동차 리스트 구하기

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

46-1. inner join, distinct

select distinct c.car_id
from car_rental_company_car c join car_rental_company_rental_history h on c.car_id = h.car_id
where c.car_type = '세단' and month(h.start_date) = 10
order by car_id desc

★ 출제포인트

두 테이블 inner join조건 2가지 필터 (세단, 10월)DISTINCT로 중복 제거내림차순 정렬

 

46-2. IN과 서브쿼리 활용

select car_id
from car_rental_company_car
where car_type = '세단' and car_id in
(
select car_id
from car_rental_company_rental_history
where month(start_date) = 10
) 
order by car_id desc

★ 출제포인트

where 조건1 and car_id in (서브쿼리)

 

47. 모든 레코드 조회하기

동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회하는 SQL문을 작성. SQL을 실행하면 다음과 같이 출력

select *
from animal_ins
order by animal_id

 

48. 즐겨찾기가 가장 많은 식당 정보 출력하기

 

REST_INFO 테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬

48-1. where (컬럼1, 컬럼2) in 서브쿼리

select food_type,
       rest_id,
       rest_name,
       favorites
from rest_info
where (food_type, favorites) in (
select food_type, max(favorites)
from rest_info
group by food_type
)
order by food_type desc

where절에서 (food_type, favorites) 쌍이 아래 서브쿼리 결과에 포함된 행만 필터링

즉 음식 종류별로 즐겨찾기 수 가장 많은(최대값) 행만 남김

 

서브쿼리 : 음식 종류별로 그룹화, 각 그룹(음식종류) 마다 즐겨찾기 수의 최대값(max(favorites)를 구함

★중요: 이렇게 해서 결과는 (food_type, 최대즐겨찾기수) 쌍의 집합이 됨

48-2. rank() over

select food_type,
       rest_id,
       rest_name,
       favorites
from (
select *,
       rank() over (partition by food_type order by favorites desc) as rnk
from rest_info
) t
where rnk = 1
order by food_type desc;

rank() over (partition by food_type order by favorites desc) as rnk

음식 종류별로 그룹을 나눔(partition by) / 즐겨찾기 수가 많은 순서로 정렬(order by ... desc)

각 음식 종류 그룹 내에서 즐겨찾기수가 가장 많은 행은 rnk = 1 부여됨

 

메인쿼리 : 필요한 컬러만 select해서 결국 where rnk = 1만 추출, 음식 종류별 내림차순 정렬

 

★ 오답노트

max(favorites) 만 하면, 최대값만 구할 뿐, 해당 식당 정보와 연결 안 됨!!!

게다가 group by에는 select에 들어간 모든 컬럼(비집계 컬럼 포함) 동시 사용해야 하는데 그럼 오류 뜸.

결국, 서브쿼리에서 음식 종류별 최대 즐겨찾기수를 구하고, 메인쿼리에서 그 최대값을 가진 행(식당 정보)를 출력

 

49. 식품분류별 가장 비싼 식품의 정보 조회하기

FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬

49-1. where (컬럼1, 컬럼2) in 서브쿼리

select category,
       price as max_price,
       product_name
from food_product
where (category, price) in (
select category, max(price)
from food_product
where category in ('과자', '국', '김치', '식용유')
group by category )
and category in ('과자', '국', '김치', '식용유')
order by price desc;

49-2. rank() over

select category, 
       price as max_price,
       product_name
from (
select *,
       rank() over (partition by category order by price desc) as rnk
from food_product ) a
where rnk = 1 and category in ('과자', '국', '김치', '식용유')
order by max_price desc

★ 오답노트 (앞의 문제랑 동일한 문제 but where 조건 하나 더 추가됐을 뿐

max(price) 만 하면, 최대값만 구할 뿐, 해당 카테고리, 식품 이름과 연결 안 됨!!!

게다가 group by에는 select에 들어간 모든 컬럼(비집계 컬럼 포함) 동시 사용해야 하는데 그럼 오류 뜸.

결국, 서브쿼리에서 카테고리별 최대 가격을 구하고, 메인쿼리에서 그 최대값을 가진 행(식당 정보)를 출력

 

50. 5월 식품들의 총매출 조회하기

FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬

select p.product_id, 
       p.product_name,
       sum(p.price * o.amount) as total_sales
from food_product p inner join food_order o on p.product_id = o.product_id
where date_format(produce_date, '%Y-%m') = '2022-05'
group by 1, 2
order by 3 desc, 1 asc

★ 오답노트

총매출 = sum(p.price * o.amount) -> sum을 추가하지 않으면 동일 상품의 모든 주문량을 합산하지 못 함!

group by 1, 2 집계함수를 제외한 select에 있는 모든 값들로 group by 를 해야 결과가 안잔하게 나옴!

728x90