数据库原理实验报告

9 查询每门课程成绩都高于该门课程平均分的学生学号 select Sno from student where Sno not in ( select Sno from SC X where Grade < (

select avg(Grade) from SC where Cno=X.Cno ) )

select sno from student where sno not in (

select sno from sc X where grade < (

select avg(grade) from sc where cno=X.cno ) )

10 查询大于本系科平均年龄的学生姓名 select Sname from student X where Sage > ( select avg(Sage) from student y where Sdept=x.Sdept )

45

46

实验八 视图

建立学生学号、姓名、性别、课程号、成绩的视图v_sc，并且查看v_sc中的数据。

create view v_sc(sno,sname,ssex,cno,grade) as(select student.sno,sname,ssex,cno,grade from student,sc

where student.sno=sc.sno );

创建视图成功。

select * from v_sc;

47

查询成功,共有14条记录.

建立学生学号、姓名、出生年月的视图v_age，并且查看v_age中的数据。

create view v_age(sno,sname,sbirth)

as select sno,sname,2008-sage from student;

创建成功。

select * from v_age;

48