SQL 61-65.

61. 서울에 위치한 식당 목록 출력하기

문제 REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬

select i.rest_id,
       i.rest_name,
       i.food_type,
       i.favorites,
       i.address,
       round(avg(r.review_score), 2) as score
from rest_info i join rest_review r on i.rest_id = r.rest_id
where i.address like ('서울%')
group by 1,2,3,4,5
order by score desc, i.favorites desc

오답이유: where address like ('%서울%') 로 하면, '경기도 서울리' 같은 값도 포함됨!

따라서 서울시/서울특별시 주소만 정확히 찾기 위해 like ('서울%')로 해야 함

 

62. 자동차 대여기록에서 장기/단기 대여 구분하기

문제  CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬

select history_id, car_id,
       date_format(start_date, '%Y-%m-%d') as start_date, 
       date_format(end_date, '%Y-%m-%d') as end_date,
    case
        when (datediff(end_date, start_date) + 1) >= 30 then '장기 대여'
        else '단기 대여' end as 'rent_type'
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where date_format(start_date, '%Y-%m') = '2022-09'
order by history_id desc

오답이유: '장기 대여' 와 '단기 대여' 띄어쓰기 안 해줌. 이런건 그냥 복붙 필요

중요: 대여한 당일도 1일로 카운트해야 하므로 +1 필요

 

63. 자동차 평균 대여 기간 구하기

문제  CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬

select car_id,
       round(avg(datediff(end_date, start_date)+1), 1) as average_duration
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by 1
having average_duration >= 7
order by average_duration desc, car_id desc

한 번에 못 맞춘 이유: 1) having을 써야 한다, 2) group by > having > order by 순서임

중요: 대여한 당일도 1일로 카운트해야 하므로 +1 필요

 

64. 해비 유저가 소유한 장소

문제 이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다. 헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성

select id, name, host_id
from places
where host_id in (
select host_id
from places
group by host_id
having count(host_id) >= 2)
order by id

오답이유:

1) host_id 서브쿼리에 컬럼을 여러 개 불러옴 (where절에 넣을거면 필요한 컬럼만 가져와서 in으로 찾아야 했음

2) 서브쿼리 자체: select에 count() 를 쓸 필요 없이, group by 이후 having만으로도 조건 부여 및 추출 가능

 

65. 우유와 요거트가 담긴 장바구니

문제 데이터 분석 팀에서는 우유(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) having count를 cart_id로 했는데, 그러면 우유/ 요구르트 두 번 산 사람도 추출됨. 문제에서 요구한건 우유와 요거트가 둘 다 들어있는거였음! 따라서 count(name)으로 해야함

2) 게다가 distinct name을 해서 우유/요구르트 구분해야 함

3) 서브쿼리는 필요 없었음