多表连接
- 检索"0001"课程分数小于60,按分数降序排列的学生信息:
select a.学号,a.姓名,b.成绩
from student as a inner join score as b on a.学号=b.学号
where b.课程号='0001' and b.成绩<60
order by b.成绩 desc
- 查询不同老师所教不同课程平均分从高到低显示
with tmp as (select course.*,teacher.教师姓名
from course inner join teacher on course.教师号=teacher.教师号)
select tmp.*,score.成绩,avg(成绩) as 平均成绩
from tmp inner join score on score.课程号=tmp.课程号
group by tmp.教师号
order by 平均成绩 desc--
这个题问的有点问题,看题意以为再group by 后还要加个课程号呢
这是原答案
select a.教师号,a.教师姓名,avg(c.成绩)
from teacher as a inner join course as b
on a.教师号= b.教师号
inner join score c on b.课程号= c.课程号
group by a.教师姓名
order by avg(c.成绩) desc;
- 查询课程名称为"数学",且分数低于60的学生姓名和分数
select student.姓名,score.成绩
from score inner join course on score.课程号=course.课程号
inner join student on student.学号=score.学号
where score.成绩<60 and course.课程名称='数学'
- 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select student.姓名,course.课程名称,score.成绩
from score inner join course on score.课程号=course.课程号
inner join student on student.学号=score.学号
where score.成绩>70
- 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
- 翻译成大白话:计算每个学号不及格分数个数,筛选出大于2个的学号并找出姓名,平均成绩
select student.姓名,avg(score.成绩) as 平均成绩
from student inner join score on student.学号=score.学号
where score.成绩<60
group by student.学号
having count(student.学号)>=2
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select distinct a.学号,a.课程号,a.成绩
from score as a join score as b on a.学号=b.学号
where a.成绩=b.成绩 and a.课程号!=b.课程号