SQL多表查询练习题

搜集自网上资料

查询所有学生的学号、姓名、选课数、总成绩
select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩from student as a left join score as bon a.学号 = b.学号group by a.学号;

查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select a.学号,a.姓名, avg(b.成绩) as 平均成绩from student as a left join score as bon a.学号 = b.学号group by a.学号having avg(b.成绩)>85;

查询学生的选课情况：学号，姓名，课程号，课程名称
select a.学号, a.姓名, c.课程号,c.课程名称
from student a inner join score b on a.学号=b.学号
inner join course c on b.课程号=c.课程号;

查询出每门课程的及格人数和不及格人数
-考察case表达式
select 课程号,sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,sum(case when 成绩 < 60 then 1 else 0 end) as 不及格人数from scoregroup by 课程号;

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
考察case表达式
select a.课程号,b.课程名称,sum(case when 成绩 between 85 and 100 then 1 else 0 end) as '[100-85]',sum(case when 成绩 =70 and 成绩<85 then 1 else 0 end) as '[85-70]',sum(case when 成绩>=60 and 成绩<70 then 1 else 0 end) as '[70-60]',sum(case when 成绩<60 then 1 else 0 end) as '[<60]'from score as a right join course as b on a.课程号=b.课程号group by a.课程号,b.课程名称;