Basic Tools of SQL 2

Operators

addition ( + )
subtraction ( - )
multiplication ( * )
division ( / )
sum(variable_name)
avg(variable_name)

The example code below, finds to the sum of all food prepation time of all orders and the average delivery of all orders.

select sum(food_preparation_time) total_food_prep_time, avg(delivery_time) avg_delivery_time
from food_orders

Count

Count counts the number of data points, below are some examples:

select count(*) customer_id,
       count(distinct customer_id) count_of_customers
from food_orders

select count(1) as total_payment_count
from payments

count(1) or count(*) means just count all without any restrictions

Max/Min

select min(price) mini, max(price) maxi
from food_orders

Examples

Example 1: Getting the number of orders that cost 30,000 KRW or more.

select count(1) expensive_orders
from food_orders
where price>=30000

Example 2: Getting the average price of korean food.

• filter out korean food from cuisine_type
• take the average

select avg(price) avg_kr_price
from food_orders
where cuisine_type='Korean'

Group by

Groups calculation by specified category.

Examples

Example 1: Getting the average price of each cuisine (grouping by cuisine type)

select cuisine_type, avg(price) avg_price
from food_orders fo
group by cuisine_type

Output

Example 2:

select restaurant_name, max(price) max_price
from food_orders fo
group by restaurant_name

Order by

Orders data by ascending (default) or descending order

Removing 'desc' from the code below will order by ascending order.

select cuisine_type,
       sum(price) sum_of_price
from food_orders
group by cuisine_type
order by sum(price) desc

Also, you can set more than one ordering constraints.
In the example below the data is first categorized by gender then within each gender we categorize each by name (alphabetically).

select *
from customers
order by gender, name

Complex code example

The below code uses all of the knowledge we learned of SQL so far.

select cuisine_type, avg(delivery_time) average_del_time
from food_orders
where day_of_the_week ='Weekend'
group by cuisine_type 
order by avg(delivery_time) desc

For each cuisine type, find the average deliverty time for orders only placed on weekends. Order this data in descending time order (slowest to fastest).