sql쿼리 각 절의 우선 순위
from >>> where >>> select >>> order by >>> group by >>> having >>> limit(이절은 order by 이후 실행)
1. 오프라인/온라인 판매 데이터 통합하기
ONLINE_SALE 테이블 : 의류 쇼핑몰 온라인 상품 판매 정보
OFFLINE_SALE 테이블 : 오프라인 상품 판매 정보
문제 : 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요. 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬
나의 쿼리
(SELECT date_format(sales_date, '%Y-%m-%d') as sales_date, product_id, user_id, sales_amount
FROM ONLINE_SALE
WHERE date_format(sales_date, '%Y-%m')='2022-03') # 온라인 판매 2022년 3월 데이터
UNION ALL # SELECT 문의 결과를 결합하지만 중복된 행을 제거하지 않습니다.
(SELECT date_format(sales_date, '%Y-%m-%d') as sales_date, product_id, null as user_id, sales_amount
FROM OFFLINE_SALE
WHERE date_format(sales_date, '%Y-%m')='2022-03') # 오프라인 판매 2022년 3월 데이터, user_id는 널 값으로 채움
ORDER BY sales_date, product_id, user_id # 판매일, 제품id, 유저id로 오름차순 정렬
2. 입양 시각 구하기(2)
ANIMAL_OUT : 동물 보호소에 입양 보낸 동물의 정보
문제 : 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬
나의 쿼리
-- 코드를 입력하세요
SELECT a.hour, # 컬럼 선택
CASE # 입양 시간이 없는 것은 0으로 나머지는 카운트
WHEN b.count is null then 0
ELSE b.count
END as count
FROM
(SELECT 0 as hour # 시간 데이터
union
SELECT 1 as hour
union
SELECT 2 as hour
union
SELECT 3 as hour
union
SELECT 4 as hour
union
SELECT 5 as hour
union
SELECT 6 as hour
union
SELECT 7 as hour
union
SELECT 8 as hour
union
SELECT 9 as hour
union
SELECT 10 as hour
union
SELECT 11 as hour
union
SELECT 12 as hour
union
SELECT 13 as hour
union
SELECT 14 as hour
union
SELECT 15 as hour
union
SELECT 16 as hour
union
SELECT 17 as hour
union
SELECT 18 as hour
union
SELECT 19 as hour
union
SELECT 20 as hour
union
SELECT 21 as hour
union
SELECT 22 as hour
union
SELECT 23 as hour) a
LEFT JOIN # 왼쪽 테이블 기준 조인
(SELECT date_format(datetime, '%H') as hour, count(*) as count # 시간 별 카운트 데이터
FROM ANIMAL_OUTS
GROUP BY date_format(datetime, '%H')) b
ON a.hour=b.hour # 시간 기준 조인
ORDER BY hour # 시간 기준 오름차순 정렬
3. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기
CAR_RENTAL_COMPANY_CAR : 자동차 대여 회사에서 대여 중인 자동차들 정보
CAR_RENTAL_COMPANY_RENTAL_HISTORY : 자동차 대여 기록 정보
CAR_RENTAL_COMPANY_DISCOUNT_PLAN : 자동차 종류 별 대여 기간 종류별 할인 정책 정보
문제 : 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬
나의 쿼리
SELECT a.car_id, a.car_type, round((a.daily_fee*((100-b.discount_rate)/100))*30,0) as fee # 컬럼 선택
FROM
(SELECT car_id, car_type, daily_fee # suv, 세단 목록
FROM CAR_RENTAL_COMPANY_CAR
WHERE (car_type='SUV' or car_type='세단')
AND car_id not in ( # 2022년 11월1일부터 11월 30일까지 대여가능한 차량 id
select car_id
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where (date_format(START_DATE, '%Y-%m-%d')<'2022-11-01' and date_format(END_DATE,'%Y-%m-%d')>'2022-12-01')
or (date_format(START_DATE, '%Y-%m-%d')>='2022-11-01' and date_format(START_DATE,'%Y-%m-%d')<='2022-12-01')
or (date_format(END_DATE, '%Y-%m-%d')>='2022-11-01' and date_format(END_DATE,'%Y-%m-%d')<='2022-12-01'))
group by car_id) a
LEFT JOIN
(SELECT car_type, discount_rate # 할인 데이터
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE (car_type='SUV' or car_type='세단')
AND duration_type like '30%') b
ON a.car_type=b.car_type # 차량 타입기준 조인
where round((a.daily_fee*((100-b.discount_rate)/100))*30,0)>=500000 and round((a.daily_fee*((100-b.discount_rate)/100))*30,0)<2000000 # 최종 대여 가격 50만이상 200만 미만 자동차 필터
ORDER BY fee desc, car_type, car_id desc # 가격 내림차순, 차량타입 오름차순, 차량id 내림차순 정렬
4. 자동차 대여 기록 별 대여 금액 구하기
CAR_RENTAL_COMPANY_CAR : 자동차 대여 회사에서 대여 중인 자동차들 정보
CAR_RENTAL_COMPANY_RENTAL_HISTORY : 자동차 대여 기록 정보
CAR_RENTAL_COMPANY_DISCOUNT_PLAN : 자동차 종류 별 대여 기간 종류별 할인 정책 정보
문제 : 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬
나의 쿼리
SELECT a.history_id, # 컬럼 선택
ROUND(b.DAILY_FEE * (100 - IFNULL(c.DISCOUNT_RATE, 0)) / 100 * (DATEDIFF(a.END_DATE, a.START_DATE) + 1)) FEE # 최종 요금 계산
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY a
JOIN
(SELECT car_id, car_type, daily_fee # 트럭인 데이터
FROM CAR_RENTAL_COMPANY_CAR
WHERE car_type='트럭') b
ON a.car_id=b.car_id
LEFT JOIN
(SELECT car_type, CAST(duration_type AS UNSIGNED) as duration_type, discount_rate # 차량 가격 데이터
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN) c
ON (b.car_type=c.car_type) and datediff(a.end_date, a.start_date)+1>=c.duration_type
GROUP BY a.history_id
ORDER BY FEE desc, history_id desc # 요금, 차량id 내림차순 정렬
5. 상품을 구매한 회원 비율 구하기
USER_INFO : 의류 쇼핑몰 가입한 회원 정보
ONLINE_SALE : 온라인 상품 판매 정보
문제 : 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬
나의 쿼리
SELECT # 컬럼 선택
date_format(sales_date, '%Y') as year,
date_format(sales_date, '%m') as month,
count(distinct(user_id)) as puchased_users,
round(count(distinct(user_id))/(SELECT count(*) FROM USER_INFO WHERE date_format(joined,'%Y')='2021'),1) as puchased_ratio
FROM ONLINE_SALE
WHERE user_id in (SELECT user_id FROM USER_INFO WHERE date_format(joined, '%Y')='2021') # 21년 가입한 회원의 구매 데이터
GROUP BY year, month
ORDER BY year, month
