数据库视图应用

一．定义视图
    在EDUC数据库中，已Student Course 和Student_course 表为基础完成一下视图定义
  １.定义计算机系学生基本情况视图V_Computer；
     create view V_Computer as
     select student.sno,sname,sex,student.spno,birthday,score,student_course.tcid,teacher_course.tno,course.cno,cname
     from student,student_course,teacher_course,course
     where student.spno='computer' and student.sno=student_course.sno and student_course.tcid=teacher_course.tcid and teacher_course.cno=course.cno
     
  ２.将Student Course 和Student_course表中学生的学号，姓名，课程号，课程名，成   绩定义为视图V_S_C_G
     create view V_S_C_G as
     select student.sno,sname,student_course.tcid,cname,score
     from student,course,student_course,teacher_course
     where student.sno=student_course.sno and student_course.tcid=teacher_course.tcid and teacher_course.cno=course.cno
  ３.将各系学生人数，平均年龄定义为视图V_NUM_AVG
     create view V_NUM_AVG as
     select spno,count(sno) as tatolnum,avg(year(getdate()-birthday)) as avgage
     from student
     group by spno
  ４．定义一个反映学生出生年份的视图V_YEAR
      create view V_YEAR as
      select sno,birthday
      from student
  ５．将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G
      create view V_AVG_S_G as 
      select sno,count(tcid) as tatolcourse,avg(score) as avgscore
      from student_course
      group by sno
  ６．将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G
      create view V_AVG_C_G as 
      select tcid,count(sno) as tatolnum,avg(score) as avgscore