SQL 4

• 查询课程编号为“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'