SQL - Subquery & JOIN

SQL 4주차

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

실습 예제1
음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 
수수료율을 산정하기
(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.5%
 음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.8%
 음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
 음식점수 5개 미만, 주문수 30개 미만 → 수수료 2%)

1. sum, count, distinct 을 기본적으로 사용하여 조건에 맞는 내용을 작성한다

select cuisine_type,
sum(quantity) total_quantity,
count(distinct restaurant_name) count_res
from food_orders
group by 1

2. 위의 내용을 subquery 문으로 사용한다

select cuisine_type,
        total_quantity,
        count_res
case when count_res>=5 and total_quantity>=30 then 0.005
      when count_res>=5 and total_quantity<30 then 0.008
      when count_res<5 and total_quantity>=30 then 0.01
      when count_res<5 and total_quantity<30 then 0.02 end rate
from
(
select cuisine_type,
sum(quantity) total_quantity,
count(distinct restaurant_name) count_res
from food_orders
group by 1
)a

실습 예제2
음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
(할인조건
  수량이 5개 이하 → 10%
  수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
   이 외에는 일괄 1%)

1. 총 주문수량, 총 주문 금액

select restaurant_name,
sum(price) sum_price,
sum(quantity) sum_quantity
from food_orders
group by 1

2. 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

 

5강. 필요한 데이터가 서로 다른 테이블에 있을 때 조회하기(JOIN)

LEFT JOIN
공통컬럼(키값)을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미한다.

INNER JOIN
공통컬럼(키값)을 기준으로, 두 테이블 모두에 있는 값만 조회한다.

예시)

SELECT f.customer_id,
f.order_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

테이블을 각각 f 와 c 로 명시하면 호출할때도 별명으로 간단하게 호출할 수 있다.