Basic Tools of SQL 3

Let's learn some new commands on sql and do some sample questions!

Summary Key Words

• Replace
• Substring
• Concat
• If
• Case

Replace

• replace (column_to_modify, current_value, new_value)

select restaurant_name "Original_name",
       replace(restaurant_name, 'Blue', 'Pink') "Modified_name"
from food_orders
where restaurant_name like '%Blue Ribbon%'

Substring/Substr

• substr(column_to_search, starting_index, number_of_characters)
  The starting_index starts from 1 and the number_of_characters is the length of the substring.

select addr "Original Address",
       substr(addr, 1, 2) "City"
from food_orders
where addr like '%서울특별시%'

concatenation

• concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)
• usually used in the ‘select’ line

select addr "Original Address",
       concat('The ', substr(addr, 1, 2)) "City"
from food_orders
where addr like '%서울특별시%'

If

• if(condition, if-satisfied, if-not-satisfied)
• mostly used when there is a binary outcome

Case

Syntax:

case when Condition1 then Result1
     when Condition2 then Result2
     else Result3
end as "title(optional)"

• Used when there is multiple cases of outcom
• This is very similar to a if-condition block with elif conditions in python.

Examples

Example_A

Create a colunm with restaurant name and its address according to the format below and get the total number of orders and the total number of quantity ordered from that restaurant

Answer

SELECT concat('[', substr(addr, 1,2), '] ', restaurant_name, ' (', cuisine_type ,')') "new_string",
count(*) "total nbr of orders", sum(quantity) "total nbr of items ordered"
FROM food_orders fo
group by restaurant_name

Example_B

For each food order we want to calculate the delivery fee. The calculation for delivery fee has the following conditions:

• For deliveries take less than 25 minutes: free delivery
• For deliveries that take more than 25 minutes: 5% x price
• For deliveries that take more than 30 minutes: 10% x price
• Orders withint Seoul(서울) have extra 10% charge to its calculated delivery fee

My code

select restaurant_name, order_id, delivery_time,
case when substr(addr, 1, 2)='서울' and delivery_time >30 then price*0.1*1.1
     when substr(addr, 1, 2)='서울' and delivery_time >25 then price*0.05*1.1
     when substr(addr, 1, 2)<>'서울' and delivery_time >30 then price*0.1
     when substr(addr, 1, 2)<>'서울' and delivery_time >25 then price*0.05
     else 0
end as 'delivery fee'
from food_orders fo 

Textbook code

select restaurant_name,
       order_id,
       delivery_time,
       price,
       addr,
       case when delivery_time>25 and delivery_time<=30 then price*0.05*(if(addr like '%서울%', 1.1, 1))
            when delivery_time>30 then price*1.1*(if(addr like '%서울%', 1.1, 1))
            else 0 end "수수료"
from food_orders

Example_C (mixing if-condition with case-conditions)

The delivery fee for weekend is 3500 KRW and for week days it is 3000 KRW. When the number of items order (quantity) is greater than 3, there is an additional 20% charge in delivery fee.

My code

select restaurant_name, price, quantity, day_of_the_week, 
case when day_of_the_week='Weekend' then 3500*(if (quantity>3,1.2,1))
else 3000*(if (quantity>3,1.2,1))	
end as 'additional delivery fee'
from food_orders

Textbook code

select order_id,
       price,
       quantity,
       day_of_the_week,
       if(day_of_the_week='Weekday', 3000, 3500)*(if(quantity<=3, 1, 1.2)) "additional delivery fee"
from food_orders