实验四 视图（数据库实验）part1

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

create view V_computer
as
select *
from student,sc,course
where sdept='CS'

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

create view V_S_C_G
as
select student.sno,sc.cno,grade,course.cname,cpno,ccredit
from student,sc,course
where sc.cno=course.cno and student.sno=sc.sno

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

create view V_NUM_AVG(sno,sums,asage)
as
select sno,count(*),avg(sage)
from student
group by sno

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

create view V_AVG_S_G(sname,sums,grade)
as
select sname,count(*),avg(grade)
from student,sc
where student.sno=sc.sno
group by sname

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

create view V_AVG_C_G(cname,sums,grade)
as
select cname,count(*),avg(grade)
from sc,course
where sc.cno=course.cno
group by cname