SQL 4주차

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

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

4-3 [실습] User Segmentation 와 조건별 수수료를 Subquery로 결합해보기

4-4 [실습] 복잡한 연산을 Subquery로 수행하기

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

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

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

4-8 4주차 끝 & 숙제 안내

숙제 Segmentation 하기

 

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

Subquery를 활용하여 복잡한 연산을 수행한다

Join을 활용하여 여러개의 테이블에 있는 데이터를 한 번에 조회하고 연산한다

 

연산을 여러번 해야하는데, 쿼리문에 길게 쓰는 것밖에는 방법이 없을까?

연산한 결과를 다른 연산이나 조건문에 사용하고 싶은데, 계속 반복해서 적어줘야할까?

필요한 데이터가 여러 테이블에 나누어져 있는데 한 번에 조회해서 사용할 수는 없을까?

 

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

 

4-3 [실습] User Segmentation 와 조건별 수수료를 Subquery로 결합해보기

1) [실습] 음식점의 평균 단가별 segmentation을 진행하고, 그룹에 따라 수수료 연산하기

(수수료 구간 -

~5000원 미만 0.05%

~20000원 미만 1%

~30000원 미만 2%

30000원 초과 3%)

 

 

 

 

2) [실습] 음식점의 지역과 평균 배달시간으로 segmentation 하기

 

My solution

 

컬럼명을 "한글"로 표기했더니 결과가 뭔가 좀 이상한 것 같다. 컬럼명 변경해줄 때 조건 다시 확인해보자

컬럼명을 영어로 수정했더니 정상적인 결과로 나옴

 

4-4 [실습] 복잡한 연산을 Subquery로 수행하기

1) [실습] 음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율 산정하기

(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05%

음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.08%

음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%

음식점수 5개 미만, 주문수 30개 미만 → 수수료 2%)

 

My solution

 

Solution

2) [실습] 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

1 > 음식점, 총주문수량, 총주문금액

2> 수수료할인율(주문수량)

 

(할인조건 수량이 5개 이하 → 10%

수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%

이 외에는 일괄 1%)

 

My solution

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

 

 

[실습] JOIN을 이용하여 두 개의 테이블에서 데이터를 조회해보기

1. 주문 테이블과 고객 테이블을 customer_id를 기준으로 left join으로 묶어보기

(조회 컬럼: order_id, customer_id, restaurant_name, price, name, age, gender)

 

 

 

 

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

1) [실습] 한국 음식의 주문별 결제 수단과 수수료율을 조회하기

(조회 컬럼 : 주문 번호f.order_id, 식당 이름f.restaurant_name, 주문 가격f.price, 결제 수단p.pay_type, 수수료율p.vat)

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

My solution

 

Solution

2) [실습] 고객의 주문 식당 조회하기

customer_id

(조회 컬럼 : 고객 이름c.name, 연령c.age, 성별c.gender, 주문 식당f.restaurant_name)

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

 

 

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

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

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

food_orders / customers on customer_id

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

계산에 필요한 컬럼: c.age

*할인 : (나이-50)*0.005 

*고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

 

My solution

 

Solution

4-8 4주차 끝 & 숙제 안내

f.restaurant_name, f.price, c.age

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

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

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

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

숙제 Segmentation 하기