数据库系统概论实验报告(全)

2、查询考试成绩不及格的学生的学号, 课程号，成绩。

select sno,cno,grade from SC

where Grade<60

3、计算选修了1号课程的学生平均成绩,要求显示学号和平均成绩。

select sno,avg(grade)as avggrade from SC

where sno in (select sno from SC

where Cno='1') group by sno

4、查询每个系年龄最大的学生的学号，年龄。

select Sno,sage

from(select sdept,max(sage)as xsage from Student

group by sdept)as x,student

where x.Sdept=Student.Sdept and x.xsage=student.sage order by sno

5、查询没有选修过“李明”老师讲授课程的所有学生姓名。

select sname from student

where sno not in(select sno from sc

where cno=(select cno from course

where cteacher='李明'))

6、查询有二门以上(含两门)不及格课程的学生姓名

select sname

from student, (select Sno,COUNT(sno) as x from SCc

where Grade<60 group by sno) as y

where y.sno=Student.Sno and y.x>=2 (因所建表中并没有这种情况,故不截图)

7、查询既学过“1”号课程，又学过“2”号课程的所有学生姓名

select sname

from Student,SC

where student.sno =sc.sno and Cno='1' and Cno='2'

8、查询选修的课程的成绩大于该课程的平均成绩的所有学生姓名

select sname from student

where sno in (select sno

from SC,(select cno,AVG(grade) as x from SC

group by cno) as y where SC.Cno=y.Cno and SC.Grade>x group by sno)

四、 主要仪器设备及耗材

PC机一台

五、 思考讨论题或体会或对改进实验的建议

(1)在调试时,遇到什么问题?是怎样解决的? (2)通过实验,获得哪些收获、体会? 六、 参考资料

卫琳 《SQL SERVER 2008数据库应用与开发教程》清华大学出版社 2011.6