<TIL> 2023-12-07

내일배움캠프 사전학습캠프 넷째 날

 

스파르타코딩클럽 / 엑셀보다 쉽고 빠른 SQL 4주차 수강완료

슬슬 SQL도 난이도가 높아짐

 

배운 것 : 서브쿼리와 조인문

 

이제 서브쿼리에 조인문 써가면서 1~3주차 때 배웠던 내용들 응용해가면서 복잡한 SQL를 적으니 

공부할 맛이 더 나는 것 같다.

---

서브쿼리(Subquery)

• 서브쿼리를 사용할 때
  1. 여러 번의 연산을 수행해야할 때 
  2. 조건문에 연산의 결과를 사용하고 싶을 때 
  3. 조건문에 쿼리를 사용하고 싶을 때 

SELECT restaurant_name, 
       sum_quantity,
       sum_price,
       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;

 

• 쿼리 해석 
  1.  레스토랑에 따른 가격과 주문량의 합계를 서브쿼리로 하여
  2.  그 값을 기준으로 CASE구문을 사용하여 할인율을 정하는 쿼리

---

SELECT restaurant_name,
       price_per_plate*ratio_of_add "수수료"
FROM 
(
SELECT restaurant_name,
       CASE WHEN price_per_plate<5000 THEN 0.005
            WHEN price_per_plate between 5000 AND 19999 THEN 0.01
            WHEN price_per_plate between 20000 AND 29999 THEN 0.02
            ELSE 0.03 
            END ratio_of_add,
       price_per_plate
FROM 
(
SELECT restaurant_name, AVG(price/quantity) price_per_plate
FROM food_orders
GROUP BY 1
) a
) b;

 

• 쿼리 해석(서브쿼리를 통해 3중으로 구성)
  1. 레스토랑에 따른 평균 메뉴 가격을 계산하고
  2. 계산한 평균메뉴가격에 따른 수수료율을  CASE문으로 나누어주고
  3. 레스토랑별 메뉴 하나 당 평균 수수료를 계산함

---

조인문(Join)

• 두 테이블의 데이터를 합쳐서 사용하고 싶을 때 조인문을 사용
• LEFT JOIN, RIGHT JOIN, INNER JOIN이 있는데 LEFT와 RIGHT는 사실 기능적으로 같기 때문에 RIGHT는 생략

LEFT JOIN

• 공통 컬럼 (키값) 을 기준으로, 조인문 우측의 테이블에 값이 없더라도 모두 조회되는 경우

SELECT a.cuisine_type, 
       SUM(a.price), 
       SUM(a.price*discount_rate) discounted_price
FROM 
(
SELECT f.price, 
       f.cuisine_type,
       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 3 DESC;

 

서브쿼리로 실행한 경우 서브쿼리의 alias인 a를 SELECT문에 이용하거나 생략할 수도 있다.

• 쿼리 해석
  1. 음식 주문테이블에 고객테이블을  LEFT JOIN해서
  2. 50살이 넘는 고객을 대상으로 나이가 더 높을수록 높아지는 할인율(discount_rate)를 계산
  3. 음식 종류(cuisine_type)에 따른 음식가격 합계와, 2에서 계산한 할인율을 적용한 음식가격의 합계를 계산
  4. 그리고 이를 할인된 음식 가격합계가 높은 순으로 출력

---

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;

• 쿼리 해석
  1. 음식 주문테이블에 결제테이블을  INNER JOIN해서 연결하는 것으로
  2. 주문번호, 레스토랑명, 가격, 수수료율, 가격*수수료율(수수료) 를 출력함