[5] DBeaver - DBeaver 활용한 SQL 4주차 강의

※  8월에 들었던 SQL강의 관련 포스팅

ㄴ 4주차 쿼리 ( 길어서 일부분만 )

 

1.  서브쿼리 실습1

# ---- 4-2) 서브쿼리 ----# 

SELECT price/quantity FROM
(SELECT price, quantity FROM food_orders) a; 



select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time
from 
(
select order_id, restaurant_name, food_preparation_time-25 over_time
from food_orders
) a;

# food_preparation_time을 기준으로 over_time을 구하는 쿼리이다.
# 먼저 서브쿼리에서 food_preparation_time -25를 해준다.
# 0이나 음수의 값이 나오는 데이터들은 over_time이 아닌것으로 귀결된다.
# 여기서 over_time이 아닌 곳들은 0으로 데이터를 넣어줘야하기 때문에 따로 where문에서 진행하지않고
# 메인쿼리에서 if문으로 over_time에 해당되는 조건을 넣고 조회해준다.

ㄴ 서브쿼리를 활용하여 만든 데이터 음수 데이터들은 0으로 표시된것을 볼 수 있다.

ㄴ 아래 데이터는 괄호안에 있는 서브쿼리문만 메인으로 실행했을때 나오는 결과이다.

ㄴ -시간도 다 출력되는것을 볼 수 있다.

 

2.  서브쿼리 실습2

# ---- 4-3) 서브쿼리 실습----# 

# 음식점의 평균단가별 segmentation을 진행하고, 그룹에 따라 수수료 연산하기.
# 5000원미만 0.05%
# 5000~20000원 미만 1%
# 20000~30000원 미만 2%
# 30000원 초과 3%
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

# 서브쿼리가 2개가 들어가는 쿼리문이다.
# 가장 하위 쿼리에서 음식점명과 평균단가를 먼저 계산한다.
# 그리고 평균단가별 그룹화를 해야하기때문에 두번째 서브쿼리에서 진행한다.
# 음식점명을 불러오고, 문제에 4개의 수수료조건에 따라 case when을 활용해서 조건을 나눈다.
# 메인쿼리에서 첫 서브쿼리에서 구한 평균금액과 그룹화한 수수료를 계산하여 '수수료'로 컬럼지정해서 보여준다.
# 음식점별로 평균단가가 보여야하기 때문에 group by로 음식점명을 묶어준다.
# 마지막으로 서브쿼리를 구분하기 위한 식별자로 a,b를 넣어서 묶어준다.

 

3.  조인문 실습1

SELECT 
	a.order_id,
	a.customer_id,
	a.restaurant_name,
	a.price,
	b.name,
	b.age,
	b.gender
FROM
food_orders a left join customers b ON b.customer_id = a.customer_id;

# [고객의 주문번호, 고객번호, 식당이름, 주문금액, 고객이름, 나이, 성별을 조회해주세요.]
# 위 문제중 4가지와 3가지는 각각 다른테이블에 정보가 들어있다.
# 이때 서로 다른테이블끼리 연결되는 컬럼이 있다면 그 컬럼을 기준으로 조인문을 진행한다면 양쪽 테이블에서 원하는 정보를 불러올 수 있다.
# 고객정보테이블의 고객아이디와 음식주문정보테이블의 고객아이디를 매칭시켜서 조인을 진행한다.
# a는 음식주문정보테이블로 주문번호,고객번호,식당이름,주문금액을 가져온다.
# b는 고객정보테이블로 고객이름, 나이, 성별을 가져온다
# left join을 사용해서 주문정보테이블의 고객아이디와 고객정보의 고객아이디를 서로 매칭시켜서 쿼리를 완성한다.

 

4.  조인문 실습2

# 50세이상 고객의 연령에 따라 경로할인율을 적용하고, 음식타입별로 원래 가격과 할인 적용 가격 합을 구하기
# 음식타입, 원래가격, 할인적용가격, 할인가격 조회
# 할인 ㄴ 나이-50*000.5


SELECT
	cuisine_type,
	sum(price) price,
	sum(price*dis_price1) dis_price
FROM
(
 SELECT 
	a.cuisine_type,
	a.price,
	b.age,
	(b.age-50) * 0.005 dis_price1
FROM food_orders a left join customers b ON a.customer_id = b.customer_id
WHERE b.age >= 50
) c
group by 1 order by 3 DESC;

# 음식타입과 고객연령 등 고객정보,음식주문정보테이블 2개가 필요하기 때문에 join이 필요하다.
# 또한 경로할인율을 적용하고 마지막에 적용된 할인가격까지 구해와야하기 때문에 서브쿼리도 필요하다.
# 우선 양쪽 테이블에서 필요한 정보들을 식별자로 표시하여 가져온다. 
# 이때 경로할인율을 계산하여 적용한 가격을 임의의 지정된 이름으로 만들어둔다.
# 그리고 양쪽 테이블에서 매칭될 수 있는 고객아이디로 매칭시킨다.
# 나이 50세이상의 조건을 where문에 넣어서 값을 구해온다.
# 서브쿼리에서 계산한 값을 가지고 메인쿼리에서 최종 할인가격을 구한다.
# 마지막으로 음식타입을 group by하고 할인가격 금액이 높은 금액부터 조회해준다.