[大数据]Mysql必练50题练习（第八天）

第三十六题
查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数

select s.s_name,c.c_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
group by sc.s_id having sc.s_score>70;

第三十七题
查询课程不及格的学生

select  s.s_name,c.c_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 sc.s_score<60;

第三十八题
查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

select s.s_id ,s.s_name
from student s inner join (select * from score where c_id = 01) sc
on s.s_id = sc.s_id where sc.s_score>=80;

第三十九题

select c_id,count(s_id) as num from score 
group by c_id;

第四十题
查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select s.*,max(sc.s_score) as max_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 inner join teacher t on 
c.t_id = t.t_id where t.t_name = "张三";