数据库-------视图

   Part 1 定义视图

在数据库中，以Student Course 和sc 表为基础完成以下视图定义:

1. 定义计算机系学生基本情况视图V_Computer；

将Student Course 和sc表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G

create view V_S_C_G

as

select student.sno,sname,course.cno,cname,grade

from student,sc,course

where student.sno=sc.sno and course.cno=sc.cno

3. 将各系学生人数，平均年龄定义为视图V_NUM_AVG

create view V_NUM_AVG(sdetp,sum,avg)

as

select sdept,count(*),avg(sage)

from student

group by sdept

将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G

create view V_AVG_S_G(sno,sum,avg)

as

select sno,count(cno),avg(grade)

from sc

group by sno

5. 各门课程的选修人数及平均成绩定义为视图V_AVG_C_G

    create view V_AVG_C_G(cno,sum,avg)

as

select cno,count(sno),avg(grade)

from sc

group by cno

Part 2 使用视图

查询以上所建的视图结果。

Select *

from V_Computer  

           

  （其它视图V_S_C_G, V_NUM_AVG, V_YEAR, V_AVG_S_G, V_AVG_C_G的查询方法类似）

查询平均成绩为90分以上的学生学号、姓名和成绩；

参考SQL语句：

Select distinct V_AVG_S_G .sno, V_S_C_G .sname,ascore

From V_AVG_S_G, V_S_C_G

Where V_S_C_G.sno = V_AVG_S_G .sno and ascore > 90;

查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩；

    select distinct Student.Sno,Sname,sc.cno,course.cname,Grade

from Student,SC,Course,V_AVG_C_G

   where Student.Sno=SC.Sno and sc.Cno=V_AVG_C_G.Cno and Grade >V_AVG_C_G.avg and sc.cno=course.cno