【SQL50】day 1

目录

1.可回收且低脂的产品

2.寻找用户推荐人

 3.使用唯一标识码替换员工ID

 4.产品销售分析 I

5.有趣的电影

6.平均售价

7.每位教师所教授的科目种类的数量

8.平均售价

1.可回收且低脂的产品

# Write your MySQL query statement below
select product_id
from Products
where low_fats='Y' and recyclable='Y'

2.寻找用户推荐人

# Write your MySQL query statement below
select name
from Customer
where referee_id!=2

使用不等于符号无法筛选null情况

# Write your MySQL query statement below
select name
from Customer
where id not in 
(select id from Customer where referee_id=2)

 3.使用唯一标识码替换员工ID

# Write your MySQL query statement below
#左连接
select e1.unique_id,e2.name
from Employees as e2
left join EmployeeUNI as e1
on e1.id=e2.id

 4.产品销售分析 I

# Write your MySQL query statement below
select p.product_name,s.year,s.price
from Sales as s
left join Product as p
on s.product_id=p.product_id

5.有趣的电影

# Write your MySQL query statement below
select *
from cinema
where id%2=1 and description!='boring'
order by rating desc

6.平均售价

# Write your MySQL query statement below
#先完成正常的，再完成为0的情况
select p.product_id,round(sum(p.price*u.units)/sum(u.units),2) as average_price
from Prices as p join UnitsSold as u
on p.product_id=u.product_id
and u.purchase_date between p.start_date and p.end_date
group by product_id
#处理为0的情况
union all 
select product_id,0 as average_price
from Prices
where product_id not in (select distinct product_id from UnitsSold)

7.每位教师所教授的科目种类的数量

# Write your MySQL query statement below
select teacher_id,count(distinct subject_id) as cnt
from teacher
group by teacher_id

8.平均售价

# Write your MySQL query statement below
select activity_date as day,count(distinct user_id) as active_users
from Activity
where datediff('2019-07-27',activity_date) between 0 and 29
group by activity_date