Basic Tools of SQL 4

Summary Key Words

• Subquery
• Inner join
• Left join

Subquery

Basic Structure

Can be written in the two forms below. As you can see the code inside the brackets are a query of their own hence the name subquery! After you finish writing a subquery, you should title it something, in this case is 'a'.

select column1, special_column
from
    (
    select column1, column2 special_column
    from table1
    ) a

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

Examples

Simple Example

We are using the variable over_time calculated from the subquery in our outer query.

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

More Complex Examples

Example1

• step1: calculate the average price per plate
• step2: calculate the additional fee(%) based on average price per plate value range.
• step3: calculate the fees (actual amount) for each plate.

select restaurant_name,
       price_per_plate*ratio_of_add fees
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

Example2

Classify the average delivery time of each restaurant into different segments: less than 20min, between 20 to 30, and more than 30 minutes.
'sido' refers to the state/city of where the restaurant is located.

select restaurant_name, sido ,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'
            when avg_delivery_time>30 then '>30' end time_segment
from
(
select restaurant_name, 
	   substr(addr,1,2) sido,
	   avg(delivery_time) avg_delivery_time
from food_orders
group by 1,2
) a

Example3

Based on the total quantity and price of each restaurant's order we calculate the discount_rate that will be given.

select restaurant_name, total_quantity, total_price,
	case when total_quantity <= 5 then 0.1
		when total_quantity > 15 and total_price >= 300000 then 0.005
		else 0.01 end "discount_rate"
from
(
select restaurant_name, sum(quantity) total_quantity, sum(price) total_price
from food_orders fo 
group by 1
) a

Join

Left Join

select column_to_search
from table_1 a left join table_2 b on a.common_column1=b.common_column2

Inner Join

select column_to_search
from table_1 a inner join table_2 b on a.common_column1=b.common_column2

Example

First the subquery calculates the discount rate according to customers age (people above 50). Since we need the price of the order and the age of the customer, we also need to use 'inner join' combine two data tables. Then, calculate the per cuisine category the total amount discount and the total price of all orders after the discount. Lastly, the prices are sorted in descending order to check which cuisines had the most discount.

select cuisine_type,
       sum(price) original_price,
       sum(price)-sum(discount_amount) price_after_discount,
       sum(discount_amount) total_discount_amount 
from 
(
select a.cuisine_type,
       price,
       price*((b.age-50)*0.005) discount_amount
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age>=50
) t
group by 1
order by 4 desc