- 查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号
select a.学号
from (select 学号,成绩 from score where 课程号='0001') as a inner join
(select 学号,成绩 from score where 课程号='0002') as b on a.学号=b.学号
where a.成绩=b.成绩
原答案：
select a.学号
from (select 学号,成绩 from score where 课程号='0001') as a inner join
(select 学号,成绩 from score where 课程号='0002') as b on a.学号=b.学号
inner join student as c on c.学号=a.学号
where a.成绩=b.成绩
原答案修改后的：
select a.学号
from (select 学号,成绩 from score where 课程号='0001') as a inner join
(select 学号,成绩 from score where 课程号='0002') as b on a.学号=b.学号
where a.成绩=b.成绩
- 查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名
select b.学号,b.姓名
from (select 学号,课程号 from score where 课程号='0001') as a
inner join student as b on a.学号=b.学号
where a.学号 in (select 学号 from score where 课程号='0002')
- 查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名
select 学号,姓名
from student
where 学号 in (
select 学号 from score where 课程号 in (
select 课程号 from course where 教师号 in (
select 教师号 from teacher where 教师姓名='孟扎扎')
)
)
或
select a.学号,a.姓名
from student as a inner join score as b on a.学号=b.学号
inner join course as c on b.课程号=c.课程号
inner join teacher as d on c.教师号=d.教师号
where d.教师姓名='孟扎扎'
- 查询没学过"孟扎扎"老师讲授的任一门课程的学生姓名
select distinct 姓名
from student
where 学号 not in (
select a.学号
from student as a inner join score as b on a.学号=b.学号
inner join course as c on b.课程号=c.课程号
inner join teacher as d on c.教师号=d.教师号
where d.教师姓名='孟扎扎')
- 查询选修“孟扎扎”老师所授课程的学生中成绩最高的学生姓名及其成绩
select a.姓名,b.成绩
from student as a inner join score as b on a.学号=b.学号
inner join course as c on b.课程号=c.课程号
inner join teacher as d on c.教师号=d.教师号
where d.教师姓名='孟扎扎'
order by b.成绩 desc
limit 1
- 查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名
select distinct a.学号,a.姓名
from student as a inner join score as b on a.学号=b.学号
where b.课程号 in (
select 课程号 from score
where 学号='0001')
having 学号!='0001'