MySQL多表查询试题（1）
–1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–3.教师表 Teacher(t_id,t_name) –教师编号,教师姓名
–4.成绩表 Score(s_id,c_id,s_score) –学生编号,课程编号,分数
1、横向显示学生所学课程的成绩（例如：学生姓名，学生课程1的分数，学生课程2的分数）提示：子查询
select student.s_name, s.s_score as “课程1—英语”, v.s_score as “课程2-卖货”
from student
left join scores as s on student.s_id = s.s_id and s.c_id=1
left join scores as v on student.s_id = v.s_id and v.c_id=2;
2、找出直播带货课程的成绩，比培训英语成绩高的学生。提示：子查询
select s1.s_id, student.s_name, s1.s_score, v1.s_score
from (select s.s_id,s.s_score from scores as s where s.c_id=1 ) as s1
left join (select v.s_id,v.s_score from scores as v where v.c_id=2) as v1
on s1.s_id=v1.s_id
left join student on student.s_id=s1.s_id
where s1.s_score < v1.s_score;
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select student.s_id,student.s_name,avg(scores.s_score) from student left join scores on student.s_id=scores.s_id group by scores.s_id
having avg(scores.s_score)>60;
4、查询在教师-今日头条老板上过课的学生的个人信息
select * from student s
left join scores v on s.s_id=v.s_id
left join course c on v.c_id=c.c_id
left join teacher t on c.t_id=t.t_id
where t.t_name=“今日头条老板”;
5、查询比学生-罗永浩在课程直播卖货的成绩高的学生信息
select * from student where s_id in
(SELECT s_id from scores where c_id=2 and s_score>
(select DISTINCT v.s_score from student s,scores v,course c
where s.s_id=v.s_id and s.s_name=‘罗永浩’ and
v.c_id=(select c_id from course where c_name=‘直播卖货’)))
6、查询不同教师教授的课程的平均分。（例如：教师，课程1的平均分，课程2的平均分）
select t.t_name,c.c_name, avg(s.s_score) from teacher t
left join course c on t.t_id=c.t_id
left join scores s on c.c_id=s.c_id group by s.c_id ;