Mysql必练50题（第七天）

第三十一题

select * from student where year(s_birth) = 1990;

第三十二题
查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select sc.c_id,round(avg(sc.s_score),2) as avg_score,c.c_name from score sc 
left join course c on sc.c_id = c.c_id group by sc.c_id
order by avg_score desc,sc.c_id;

第三十三题
查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select s.s_id,s.s_name,round(avg(sc.s_score),2) as avg_score
from student s inner join score sc on s.s_id = sc.s_id
group by sc.s_id having avg_score>=85;

第三十四题
查询课程名称为"数学"，且分数低于60的学生姓名和分数

select s.s_name,sc.s_score from student s inner join score sc 
on s.s_id = sc.s_id inner join course c on sc.c_id = c.c_id
where c.c_name = "数学" and sc.s_score<60;

第三十五题
查询所有学生的课程及分数情况

select s.s_id,s.s_name,
sum(case when c_id = 1 then s_score else 0 end) as "语文",
sum(case when c_id = 2 then s_score else 0 end) as "数学",
sum(case when c_id = 3 then s_score else 0 end) as "英语"
from student s left join score sc on s.s_id = sc.s_id group by sc.s_id;