스파르타 AI-8기 TIL(9/24)

...............................

Let's start

1. Replace -> used when to change the name of the table

ex. 

select restaurant_name "original",
       replace(restaurant_name, 'Blue', 'Pink') "changed name"
from food_orders
where restaurant_name like '%Blue Ribbon%'

replace (name of the table, original name, changed name)

Another ex.

select cuisine_type "original cuisine", replace (cuisine_type, 'Korean', '한국') "changed name"

from food_orders fo

where cuisine_type like '%Korean%'

 

substr -> used when to get specific text

 

select addr "원래 주소",
       substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'

(

1, 2 -> gets from 1st and gets 2 texts

If we do something here, like

select addr "원래 주소",
       substr(addr, 1, 3) "시도"
from food_orders

As I did, It will get from 1st and gets 3 texts from all data

Frame -> substr(column, start text, number of text)

 

Concat-> Connect all the data I want

select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

[(substr -> from the first text, take two texts)], name of restaurant

 

Let's try like this

select cuisine_type '원래 음식 타입', price "원래 가격", concat ('[', substr(cuisine_type,

1, 10),']', ' ', price) '바뀐 이름'

from food_orders fo

 

 

frame -> concat (value I want to connect 1, value I want to connect 2, value I want to connect 3......)

 

If statement

frame 

if(condition, condition = true, condition = false)

Let's make 

select restaurant_name "레스토랑", day_of_the_week 'Original DOTW', if (day_of_the_week like '%Weekend%', replace(day_of_the_week, 'Weekend', '주말')

, replace(day_of_the_week, 'Weekday', '주일')) "Weekend or Weekday"

from food_orders fo

 

But If there are too many conditions,

must use a case statement

Frame -

case when condition 1 then value1

         when condition 2 then value2

         else value3

end

I made it this

select restaurant_name, price,

case when price < 20000 then '파산'

when price < 30000 then 'Not bad'

else '유명' end 'Comparison'

from food_orders

group by 1

 

 

Tips!
1. Consider the types of the data(check the name of the table(123^price or A-Z^Comparison -> 123 = int, A-Z = string)

Therefore, when using both int and string in one function, one must change the data type

--into int type

cast(if(rating='Not given', '1', rating) as decimal)  -> use as decimal

--into string type

concat(restaurant_name, '-', cast(order_id as char)

 

 

H.W

1. Create a value that determines whether the delivery time is late based on the following conditions.
Weekdays: 25 minutes or more
Weekends: 30 minutes or more

Since I need to make 2 functions on each condition like:

if it took 25 minutes less or more

case when day_of_the_week = 'Weekday' then if(delivery_time >= 25, 'late', 'Successfully delivered')

when day_of_the_week = 'Weekday' then if(delivery_time <= 25, 'Successfully delivered', 'late')

 

and the same as another condition

when day_of_the_week = 'Weekend' then if(delivery_time >= 30, 'late', 'Successfully delivered')

when day_of_the_week = 'Weekend' then if(delivery_time <= 30, 'Successfully delivered', 'late')

 

and lastly make it shows the tables

select

restaurant_name,

day_of_the_week,

delivery_time,

 

If I add all here, then

select

restaurant_name,

day_of_the_week,

delivery_time,

case when day_of_the_week = 'Weekday' then if(delivery_time >= 25, 'late', 'Successfully delivered')

when day_of_the_week = 'Weekday' then if(delivery_time <= 25, 'Successfully delivered', 'late')

when day_of_the_week = 'Weekend' then if(delivery_time >= 30, 'late', 'Successfully delivered')

when day_of_the_week = 'Weekend' then if(delivery_time <= 30, 'Successfully delivered', 'late')

end 'delivery_result'

from food_orders fo

 

and here is the result

 

And done. Have a nice day