Day 7 Today I Learned

오늘 배운 내용

엑셀보다 쉽고 빠른 SQL

3-5 SQL로 간단한 User segmentation 해보기

select case when (age between 10 and 19) and gender='male' then '10대 남성'
		when (age between 10 and 19) and gender='female' then '10대 여성'
		when (age between 20 and 29) and gender='male' then '20대 남성'
		when (age between 20 and 29) and gender='female' then '20대 여성'  end "고객 분류",
		name,
		age,
		gender
from customers
where age between 10 and 29

3-6 조건문으로 서로 다른 식을 적용한 수수료 구해보기

select case when delivery_time > 30 then price*0.1*if(addr like '%서울%', 1.1, 1)
		when delivery_time between 26 and 30 then price*0.05*if(addr like '%서울%', 1.1, 1)
		else 0 end "수수료",
		restaurant_name,
		order_id,
		price,
		delivery_time,
		addr
from food_orders

3-7 SQL문에 문제가 없는 것 같은데 왜 오류가 나나요?

data type 오류

숙제

select order_id,
		restaurant_name,
		day_of_the_week,
		delivery_time,
		case when day_of_the_week = 'Weekday' then if(delivery_time < 25, 'On-time', 'Late')
			when day_of_the_week = 'Weekend' then if(delivery_time < 30, 'On-time', 'Late') end "지연여부"
from food_orders

4-1 포맷 변경과 조건문 복습하고, 이번 수업 내용 맛보기

4-2 여러번의 연산을 한 번의 SQL 문으로 수행하기

select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time
from
(
select order_id, restaurant_name, food_preparation_time-25 over_time
from food_orders
) a

4-3 User segmentation 와 조건별 수수료를 subquery로 결합해 보기

select restaurant_name,
		price_per_plate*ratio_of_add "수수료"
from 
(
select restaurant_name,
		case when price_per_plate<5000 then 0.005
			 when price_per_plate between 5000 and 19999 then 0.01
			 when price_per_plate between 20000 and 29999 then 0.02
			 else 0.03 end ratio_of_add,
		price_per_plate
from 
(
select restaurant_name, avg(price/quantity) price_per_plate
from food_orders
group by 1
) a 
) b

4-4 복잡한 연산을 Subquery로 수행하기

select restaurant_name,
		sum_price,
		sum_quantity,
		case when sum_quantity<=5 then 0.1
			when sum_quantity>15 and sum_price>=300000 then 0.005
			else 0.01 end discount_rate
from 
(
select restaurant_name,
		sum(price) sum_price,
		sum(quantity) sum_quantity
from food_orders
group by 1
) a

4-5 필요한 데이터가 서로 다른 테이블에 있을 때 조회하기

JOIN : 테이블 합치기

select *
from food_orders left join payments on food_orders.order_id=payments.order_id

4-6 JOIN으로 두 테이블의 데이터 조회하기

SELECT distinct c.name,
		c.age,
		c.gender,
		f.restaurant_name
from food_orders f left join customers c on f.customer_id = c.customer_id 
order by c.name

4-7 JOIN으로 두 테이블의 값을 연산하기

select cuisine_type,
		sum(price) price,
		sum(price*discount_rate) discounted_price
from 
(
select f.cuisine_type,
		f.price,
		c.age,
		(c.age-50)*0.005 discount_rate
from food_orders f left join customers c on f.customer_id = c.customer_id 
where c.age >= 50
) a
group by 1
order by 3 desc