数据库原理实验报告

四 嵌套、相关及其他

1 查询平均分不及格的学生人数

select COUNT(*) from student where Sno in(

select Sno from SC group by Sno having AVG(Grade)<60 )

2 查询没有选修1002 课程的学生的学生姓名

41

select Sname from student where Sno not in( select Sno from SC where Cno = '3001' )

3 查询平均分最高的学生学号及平均分 （2种方法 TOP , any , all）

a: select top 1 Sno,avg(Grade) from SC group by Sno order by avg(Grade) desc B: select Sno,avg(Grade) from SC group by Sno

having avg(Grade) = (select top 1 avg(Grade) from SC group by Sno order by avg(Grade) desc ) c: select Sno,avg(Grade) from SC group by Sno

having avg(Grade) >=all ( select avg(Grade) from SC group by Sno )

42

*4 查询没有选修1001，1002课程的学生姓名。

Select Sname from student where not exists (

Select * from course where Cno in ('3001','3002') and

Not exists ( select * from SC where Sno=student.Sno and Cno=course.Cno ) )

5 查询1002课程第一名的学生学号（2种方法）

select top 3 Sno from SC group by Sno order by avg(Grade) desc

6 查询平均分前三名的学生学号

select top 3 sno from sc group by sno order by avg(grade) desc

43

7 查询 JSJ 系的学生与年龄不大于19岁的学生的差集

a: select * from student where Sdept='JSJ' and Sage>19 b: select * from student where sdept='JSJ' except select * from student where sage<19

8 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名 select student.Sno,Sname from student,SC where Cno='1081' and Grade>90 union select Sno,Sname from student where Sno in( select Sno from SC group by Sno having AVG(Grade)>85 )

44