目录
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