SELECT 연습(1)

1. 평균 일일 대여 요금 구하기

 

 

평균 일일 대여 요금 구하기

• 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림 -> ROUND(123.567, 0) //소수점 1번째 자리를 반올림 해서 0번째까지 만든다

SELECT round(avg(DAILY_FEE), 0) as AVERAGE_FEE
from CAR_RENTAL_COMPANY_CAR
where CAR_TYPE = 'SUV'

 

 

재구매가 일어난 상품과 회원 리스트 구하기

select USER_ID, PRODUCT_ID
from 
(SELECT user_id, product_id, count(*) c
from ONLINE_SALE
group by 1, 2) a
where a.c > 1
order by USER_ID asc, PRODUCT_ID desc

 

3월에 태어난 여성 회원 목록 출력하기

• 날짜 포멧 맞추기 -> date_format(DATE_OF_BIRTH, '%Y-%m-%d')

SELECT MEMBER_ID, MEMBER_NAME, GENDER, date_format(DATE_OF_BIRTH, '%Y-%m-%d') DATE_OF_BIRTH
from MEMBER_PROFILE
where GENDER = 'W' 
    and month(DATE_OF_BIRTH) = 3
    and TLNO is not null
order by 1 asc

 

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

(레벨 4짜리다 💁😆)

select r.REST_ID, r.REST_NAME, r.FOOD_TYPE, r.FAVORITES, r.ADDRESS, a.food_avg as SCORE
from REST_INFO r
inner join 
(
select i.REST_ID, round(avg(r.REVIEW_SCORE), 2) food_avg
from REST_INFO i
left join REST_REVIEW r on i.REST_ID = r.REST_ID
group by i.REST_ID
) a on r.REST_ID = a.REST_ID
where substring(r.ADDRESS, 1, 2) = '서울' 
        and a.food_avg is not null
order by SCORE desc, r.FAVORITES desc