day05 mysql查询语句

请显示用户性别、电影名称、评分

select b.gender, c.title, a.ratings
from ratings a
inner join users b
on a.user_id = b.user_id
inner join movies c
on a.movie_id = c.movie_id
limit 5;

请显示用户职业、用户年龄段、电影名称、评分
select b.gender, e.occup_title, d.age_min, d.age_max as age_scope, c.title, a.ratings
from ratings a
inner join users b
on a.user_id = b.user_id
inner join movies c
on a.movie_id = c.movie_id
inner join age_dict d
on b.age = d.age_id
inner join occup_dict e
on b.occupation = e.occup_id
limit 5;

求每部电影平均得分  （电影名称，平均得分）
select c.title, avg(a.ratings) as avg_rating
from ratings a
inner join movies c
on a.movie_id = c.movie_id
group by c.title
limit 10;

求每部电影男的平均分，女的平均分
select c.title, b.gender, avg(a.ratings) as avg_rating
from ratings a
inner join users b
on a.user_id = b.user_id
inner join movies c
on a.movie_id = c.movie_id
group by c.title, b.gender
limit 10;

显示平均分大于4分的电影名称，用子查询

select * from movies 
where movie_id in
(select movie_id
from ratings
group by movie_id
having avg(ratings) > 4);