Subquery

 

 1) When a Subquery is needed

     When multiple operations need to be performed

        → Determine the time value to charge the fees
        → Add extra fee based on order amounts
        → Calculate the final estimated delivery fee based on the applied weights  

  When the results of operations are needed in conditional statements

        → When wanting to divide food expenses into high/medium/low based on the average order amount of                 each food type       

  When wanting to use Query results in conditions

      → When wanting to only retrieve results where orders are made by people aged 30 or older

 

2) Basic structure of Subquery

select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) a

select column1, column2
from table1
where column1 = (select col1 from table2)

 

 

Practice 1

Conduct segmentation by average unit price of restaurants and calculate fees according to groups.

Fee range:

- Less than 5,000 won: 0.05%
- 5,000 won to less than 20,000 won: 1%
- 20,000 won to less than 30,000 won: 2%
- More than 30,000 won: 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

 

 

Practice 2

Segmentation of restaurants based on location and average delivery time

SELECT restaurant_name,
		Location,
		avg_delivery_time,
		case when avg_delivery_time<=20 then '<=20'
			when avg_delivery_time >20 and avg_delivery_time<=30 then '20<x<=30'
			else '>30' end delivery__time_segment
FROM 
(
SELECT restaurant_name,
	SUBSTR(addr, 1, 2) Location,
	AVG(delivery_time) avg_delivery_time 
from food_orders fo 
group by 1, 2
) a

 

Practice3

Calculate the total quantity and order amount of a restaurant, and determine the discount rate based on the order quantity.

 

Discount Conditions:

 

Quantity 5 or less → 10%

Quantity exceeds 15, total order amount is 300,000 or more → 0.5%

Otherwise → 1%

 

select Restaurant_name,
	   Total_order,
	   Total_price,
	   case when total_order<=5 then 0.1
	   		when Total_order>15 and total_price>=300000 then 0.005 else 0.01 end as 'Discount'
from
(
SELECT restaurant_name,
		sum(quantity) 'Total_order',
		sum(price) 'Total_price'
from food_orders fo 
group by 1
) a