2021-08-22

USE Sql_store;

SELECT *
FROM orders
where year(order_date) = year(now())-4

select date_add(now(),interval 1 year),
	date_sub(now(),interval -1 year)

use sql_store;
select order_id,
	-- ifnull(shipper_id,'NOT')
    coalesce(shipper_id,comments,'NOT') as shipper
from orders

select 
	concat(first_name,' ',last_name) as customer,
    coalesce(phone,'Unknown')
from customers

select order_id, order_date,
	if(year(now())-2=year(order_date),'活跃','不活跃') as category
from orders

select p.product_id, p.name, count(oi.order_id),if(count(oi.order_id)=1, 'once','many times')
from products p
join order_items oi using(product_id)
group by product_id

select concat(first_name,' ',last_name) as customer,points,
	case
		when points > 3000 then 'gold'
        when points between 2000 and 3000 then 'silver' 
        else 'bronze'
    end as category
from customers
order by points desc