SQL 05

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

기본 형태

1. food_orders 가 가진 order_id 기준으로 조회 (payments 는 가지지 않은 id의 값도 조회됨)

select *

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

 

2. food_orders 와 payments 둘 다 가지고 있는 order_id 기준으로 조회

select *

from food_orders inner join payments on food_orders.order_id = payments.order_id

 

테이블에 이름 부여하여 활용한 left join

select f.order_id ,

           f.customer_id ,

            f.restaurant_name,

            f.price,

            c.name,

            c.age,

            c.gender

from food_orders f left join customers c on f.customer_id = c.customer_id

 

한국 음식의 주문별 결제 수단과 수수료율을 조회하기

(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)

*결제 정보가 없는 경우도 포함하여 조회  

select f.cuisine_type,

           f.restaurant_name,

            f.price,

            p.pay_type,

            p.vat

from food_orders f left join payments p on f.order_id = p.order_id

where cuisine_type = 'Korean'

 

고객의 주문 식당 조회하기

(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당)

*고객명으로 정렬, 중복 없도록 조회

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 //또는 1로

 

주문 가 격과 수수료율을 곱하여 주문별 수수료 구하기

(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)

*수수료율이 있는 경우만 조회

select f.order_id,

            f.restaurant_name,

            f.price,

            p.vat,

            f.price * p.vat vat2

from food_orders f inner join payments p on f.order_id = p.order_id

group by order_id

 

50세 이상 고객의 연령에 따라 경로 할인율을 적용하고,

음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기

(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)

할인 : 나이 -50 * 0.005

* 고객 정보가 없는 경우도 포함하여 조회,

할인 금액이 큰 순서대로 정렬

select cuisine_type,

            sum(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 //50세 이상만 조회함

) a

group by 1

order by sum(price*discount_rate) desc

 

1. 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기

평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과

평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상

두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬

select restaurant_name,

           case when avg_price <= 5000 then 'price_group1'

            when avg_price > 5000 and avg_price <= 10000 then 'price_group2'

            when avg_price >10000 and avg_price <= 30000 then 'price_group3'

            when avg_price > 30000 then 'price_group4'

            end price_group,

            case when avg_age < 30 then 'age_group1'

            when avg_age between 31 and 39 then 'age_group2'

            when avg_age between 41 and 49 then 'age_group3'

            else 'age_group4' end age_group

from

(

select f.restaurant_name,

            avg(f.price) avg_price,

            avg(c.age) avg_age

from food_orders f inner join customers c on f.customer_id = c.customer_id

group by 1

) a

order by 1