⑨查询“钱军”教师任课的课程号，以及选修其课程学生的学号和成绩； 法1:select cno from course
where tno=(select tno from teacher
where tname='钱军'); select sno,grade from sc
where cno in(select cno from course where tno in (select tno from teacher
where tname='钱军'));
法2:select teacher.tname as 教师姓名,course.cno as课程号,student.sname as 学生姓名,student.sno as学号,sc.grade as成绩
from teacher inner join (course inner join (sc inner join student on student.sno=sc.sno) on course.sno=sc.sno) on course.tno=teacher.tno where teacher.tname='钱军';（出错）
⑩查询选修某课程的学生人数多于20人的教师姓名； 法1:select tname as 教师姓名 from teacher where tno in (select tno from course where cno in (select cno from sc
group by cno having count(*) > 20));
6
法2:select tname as 教师姓名
from teacher inner join (course inner join sc on course.cno=sc.cno) on teacher.tno=course.tno group by sc.cno having count(*) > 20;（出错）
⑾查询选修编号为“8105”课程且成绩至少高于其选修编号为“8245”课程的同学的SNO及“8105”课程成绩，并按成绩从高到低依次排列； select * from sc
where cno='8105'
and grade > any (select grade from sc
where cno='8245') order by grade desc;
⑿查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩的同学的 CNO,SNO,GRADE； select * from sc
where cno='8105'
and grade > all (select grade from sc
where cno='8245') order by grade desc;
⒀列出所有教师和同学的姓名、SEX、AGE；
select student.sname as 学生姓名,student.sex as 学生性别,student.age as 学生年龄, teacher.tname as 教师姓名,teacher.sex as 教师性别,teacher.age as 教师年龄
from student inner join (sc inner join (course inner join teacher on course.tno=teacher.tno) on sc.cno=course.cno) on student.sno=sc.sno;
7
⒁查询成绩比该课平均成绩高的学生的成绩表； select * from sc
where grade > any (select avg(grade) from sc group by cno) order by cno asc;
⒂列出所有任课教师的TNAME和DEPT；
select teacher.tname as 教师姓名, teacher.dept as专业 from teacher where tno in (select tno from course where cno in (select cno from sc
8
group by cno));
⒃列出所有未讲课教师的TNAME和DEPT；
select teacher.tname as 教师姓名, teacher.dept as 专业 from teacher where tno not in (select tno from course
where cno in (select cno from sc group by cno));
⒄列出至少有4名男生的班号； select class as 班号 from student where sex='男'
group by class having count(*) >= 4;
⒅查询不姓“张”的学生记录； 法1:select * from student
where sname not like '张%'; 法2:select * from student
where sname not in (select sname from student
where sname like '张%');
⒆查询每门课最高分的学生的SNO、CNO、GRADE； select *
9
from sc
where grade in (select max(grade) from sc
group by cno);
⒇查询与“李华”同性别并同班的同学SNAME；
select sname from student
where sex=(select sex from student
where sname='李华') and class=(select class from student
where sname='李华');
（21）查询“女”教师及其所上的课程； select tname as 教师名,cname as 课程
from teacher inner join course on teacher.tno=course.tno where sex='女';
（22）查询选修“数据库系统”课程的“男”同学的成绩表； 法1:select * from sc
where cno=(select cno from course
where cname='数据库系统') and sno in (select sno from student
10
where sex='男');
法2:select sc.sno,student.sname as 学生姓名,
student.sex as 学生性别,sc.cno,sc.grade,course.cname
from student inner join (sc inner join course on sc.cno=course.cno) on student.sno=sc.sno where sex='男' and cname='数据库系统';
（23）查询所有比刘涛年龄大的教师姓名、年龄和刘涛的年龄； Select tname,age From teacher
Where age>(select age From teacher
Where tname='刘涛') Union
Select tname,age From teacher
Where tname='刘涛';
/*Select teacher.tname as 教师姓名，teacher.age as 年龄，*/
（24）查询不讲授“8104”号课程的教师姓名。 select teacher.tname from teacher where not exists ( select *
from course
where cno='8104' and tno=teacher.tno);
11