240701(+25) | SQL 4주차 강의 (2)

JOIN

필요한 데이터가 서로 다른 테이블에 있을 때 조회하기
(EX) 주문테이블-주문 가격, 결제테이블-결제 수단

공통 컬럼을 기준으로 묶은 형태는 아래와 같이 됩니다.
즉, 공통 컬럼을 기준으로 두 테이블을 합쳐서, 각각 테이블에서 필요한 데이터를 조회할 수 있도록 만들어주는 것입니다.

기초 작성법

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
    -- f 테이블의 정보 모두 가져오기, 연결 할 c 테이블
    -- 공동되는 컬럼 테이블명(별칭).공동컬럼명

left join
a table의 정보는 b table에 없더라도 전부 가져온다. (b테이블 컬럼은 공백으로)

inner join
공동된 정보만 출력

실습(1) 결제 수단별 수수료

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

• 조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율
• 결제 정보가 없는 경우도 포함하여 조회

SELECT f.order_id,
	   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'

실습(2) 주문 식당 조회

고객의 주문 식당 조회하기

• 조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당
• 고객명으로 정렬, 중복 없도록 조회

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
where c.name is not null --null값 제거, 항상 위치 잘 생각하기 order by 뒤에 가면 오류
order by c.name

두 테이블의 값을 연산하기

실습(1)

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

• 조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)
• 수수료율이 있는 경우만 조회 (inner join)

SELECT f.order_id,
	   f.restaurant_name,
	   f.price,
	   p.vat '수수료',
	   floor(f.price*p.vat) '수수로율'
from food_orders f
inner join payments p
on f.order_id = p.order_id

실습(2)

50세 이상 고객의 연령에 따라 경로 할인율을 적용하고(subquery), 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기

• 조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격
• 할인율 : (나이-50)*0.005
• 고객 정보가 없는 경우도 포함하여 조회(left 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
) sub1
group by 1 -- 음식타입별로
order by discounted_price

4주차 과제

식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 세분화 하기

• 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
• 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
• 두 테이블 모두에 데이터가 있는 경우만 조회(inner join), 식당 이름 순으로 오름차순 정렬

SELECT restaurant_name,
	   case
	   	when avg_price <= 5000 then '(1) 소식가'
	   	when avg_price > 5000 and avg_price <= 10000 then '(2) 1인 가구'
	   	when avg_price > 10000 and avg_price <= 30000 then '(3) 2인 가구'
	   	when avg_price > 30000 then '(4) 대식가'
	   	else '신원 미상'
	   end price_group,
	   case
	   	when avg_age <30 then '(1) 10대~20대'
	   	when avg_age >= 30 and avg_age < 40 then '(2) 30대'
	   	when avg_age >= 40 and avg_age < 50 then '(3) 40대'
	   	else '(4) 중년'
	   end age_group
from 
(
select f.restaurant_name,
	   floor(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 --식당 별
order by 1 --식당 이름 오름차순
) sub1