SQL - JOIN 실습예제

6강. [실습] JOIN 으로 두 테이블의 데이터 조회하기

실습예제1) 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
    
(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
*결제 정보가 없는 경우도 포함하여 조회

1. 결제 정보가 없는 경우도 포함하여 조회
이 내용은, 두개의 테이블 중 한 쪽 테이블에서 없는 내용이 있을수 있다라는 의미이기 때문에 left join 을 사용한다.

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) 고객의 주문 식당 조회하기

 

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

1. 우선, 이름, 연령, 성별, 주문 식당, 고객명으로 정렬 까지 한다.

select 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

 

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
order by c.name

 

distinct 를 사용하면 중복된 내용이 제거된다.

---

7강. [실습] JOIN으로 두 테이블의 값을 연산하기

실습예제1) 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

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

1. 수수료율이 있는 경우만 조회
이 내용은 두 테이블에 공통된 값이 있을 경우에만 조회하라는 내용이라서 INNER JOIN 을 사용한다.

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

 

 

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

 

(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)
*할인 : 나이-50*0.005
*고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

1. 할인율을 적용하고, 할인 적용가격 합을 구한다
위의 내용은, 두가지의 연산이 동시에 일어나기 때문에 subquery 문을 사용하면 효율적이다.

select f.cuisine_type,
        f.price,
        c.age
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.age>=50

 

이렇게 조회하면 위의 조건에 맞게 일단은 50세 이상의 customer 들에 대한 내용이 조회된다.

 

2. 할인율 적용하기

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

 

3. subquery 문 사용

//아래의 a.cuisine_type 에서 a. 부분은 생략해도 된다.

select a.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 sum(price*discount_rate) discounted_price desc

 

마지막에 group by 1 로, select 부분의 1번째 것을 기준으로 묶어서 조회해주고, order by 3 또는 order by sum(price*discount_rate) 라고 하여, 할인된 가격의 합을 내림차순으로 정렬한다.

---

4주차 숙제

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

 

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

1. SQL 기본구조 적어보기
2. 각 테이블에서 필요한 컬럼 정리하기
3. Join 문으로 데이터 조회문 적어보기
4. 데이터를 연산해보기
5. 연산한 데이터를 segmentation 에 활용하기

 

 

나의 풀이

 

1. 각각이 가지고 있는 컬럼을 먼저 조회한다.

select *
from food_orders

select *
from customers

 

 

2. 두 테이블 모두에 데이터가 있는 경우만 조회. 라는 조건에서 inner join을 사용한다는 것을 파악한다.

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

 

 

3. 필요한 컬럼을 호출하면서 식당별 평균 나이와 평균 가격을 계산하고 기준을 따라 group by 로 묶는다.

 

select f.restaurant_name,
        avg(f.price) avg_order_price,
        avg(c.age) avg_customer_age
from food_orders f inner join customers c on f.customer_id=c.customer_id
group by f.restaurant_name

 

 

4. 이 결과를 서브쿼리로 이용하고, case when 으로 상황별 조건을 메인쿼리에 작성한다.

select f.restaurant_name,
        t.avg_order_price,
        t.avg_customer_age,
        case when t.avg_order_price <= 5000 then 'price_group1'
	  when t.avg_order_price <= 10000 then 'price_group2'
	  when t.avg_order_price <= 30000 then 'price_group3'
	  else '30000>' as 'price_group4' end as price_group,
        case when t.avg_customer_age <30 then 'age_group1'
	  when t.avg_customer_age <40 then 'age_group2'
	  when t.avg_customer_age <50 then 'age_group3'
	  else 'age_group4' end as age_group

from
(
select f.restaurant_name,
        avg(f.price) avg_order_price,
        avg(c.age) avg_customer_age
from food_orders f inner join customers c on f.customer_id=c.customer_id
group by f.restaurant_name
) t
order by t.restaurant_name

 

5. 마지막으로 출력 예시에 맞게 필요없는 컬럼은 지워준다

        t.avg_order_price,
        t.avg_customer_age, 을 지우면

최종 코드

select t.restaurant_name,
        case when t.avg_order_price <= 5000 then 'price_group1'
	  when t.avg_order_price <= 10000 then 'price_group2'
	  when t.avg_order_price <= 30000 then 'price_group3'
	  else 'price_group4' end as price_group,
        case when t.avg_customer_age <30 then 'age_group1'
	  when t.avg_customer_age <40 then 'age_group2'
	  when t.avg_customer_age <50 then 'age_group3'
	  else 'age_group4' end as age_group
from
(
select f.restaurant_name,
        avg(f.price) avg_order_price,
        avg(c.age) avg_customer_age
from food_orders f inner join customers c on f.customer_id=c.customer_id
group by f.restaurant_name
) t
order by t.restaurant_name

 

 이로서 SQL 4주차 강의의 내용은 모두 끝났다. 다음 주도 5주차 강의를 모두 완성하여 SQL 알고리즘도 공부를 시작하여야 겠다.