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
按系统计各系平均成绩在80分以上的人数,结果按降序排列;
select sdept,count(*)sum
from student,V_AVG_S_G
where student.sno=V_AVG_S_G.sno and avg>80
group by sdept
order by sum desc;
Part3 修改视图
1. 通过视图V_Computer,将学号为“201215121”的学生姓名进行修改,并查询结果;
update V_Computer
set Sname='John'
where Sno='201215121';
select *
from V_Computer
;
Select * from student;
2. 通过视图V_Computer,新增加一个学生记录 ('201215201','YAN XI',19,'IS'),并查询结果
insert into V_Computer(Sno,Sname,Sage,Sdept) values('201215201','YAN XI',19,'CS');
select *
from V_Computer ;
Select * from student;
3. 通过视图V_Computer,新增加一个学生记录 ('201215301','Yangyang',20,'MA'),并查询结果
insert into V_Computer(Sno,Sname,Sage,Sdept)
values('201215301','Yangyang',20,'MA');
要通过视图V_S_C_G,将学号为“201215127”的姓名改为“Weiwen”,是否可以实现?并说明原因
不可以
5. 要通过视图V_AVG_S_G,将学号为“201215121”的平均成绩改为90分,是否可以实现?并说明原因
不可以