数据库实验四答案

--实验四--
--1． 定义“IS”系学生基本情况视图V_IS 并查询结果；
create view V_IS
as
select *
from S
where Sdept='IS'
with check option;

select *
from V_IS;

--2． 将S，C，SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G并查询结果;
create view V_S_C_G
as
select S.Sno,Sname,https://www.wendangku.net/doc/d49523033.html,o,Cname
from S,C,SC
where S.Sno=SC.Sno and https://www.wendangku.net/doc/d49523033.html,o=https://www.wendangku.net/doc/d49523033.html,o;

select *
from V_S_C_G;

--3． 将各系学生人数，平均年龄定义为视图V_NUM_AVG并查询结果;
create view V_NUM_AVG
as
select count(Sno) DeptNum ,avg(Sage) DeptAvgAge,Sdept
from S
group by Sdept;

select *
from V_NUM_AVG;

--4． 定义一个反映学生出生年份的视图V_YEAR并查询结果;
create view V_YEAR
as
select Sno,2012-Sage birthday
from S;

select *
from V_YEAR;

--5． 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;
create view V_AVG_S_G
as
select SC.Sno,count(https://www.wendangku.net/doc/d49523033.html,o) CountCno,avg(Grade) AvgGrade
from S,C,SC
where S.Sno=SC.Sno and https://www.wendangku.net/doc/d49523033.html,o=https://www.wendangku.net/doc/d49523033.html,o
group by SC.Sno;

select *
from V_AVG_S_G;


--6． 将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G并查询结果;
create view V_AVG_C_G
as
select count(SC.Sno) Cnum,avg(Grade) AvgGrade
from S,C,SC
where S.Sno=SC.Sno and https://www.wendangku.net/doc/d49523033.html,o=https://www.wendangku.net/doc/d49523033.html,o
group by https://www.wendangku.net/doc/d49523033.html,o;

select *
from V_AVG_C_G;

--7． 查询平均成绩为90分以上的学生学号、姓名和成绩；
select SC.Sno,Sname,avg(Grade) AvgGrade
from S,SC
where S.Sno=SC.Sno
group by SC.Sno,Sname
having avg(Grade) >90;

--8． 查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩；
create view V_AVG
as
select https://www.wendangku.net/doc/d49523033.html,o,avg(Grade) AvgGrade
from C,SC
where https://www.wendangku.net/doc/d49523033.html,o=https://www.wendangku.net/doc/d49523033.html,o
group by https://www.wendangku.net/doc/d49523033.html,o;

select
distinct S.Sno,Sname,https://www.wendangku.net/doc/d49523033.html,o,Grade
from S,SC,C,V_AVG
where S.Sno=SC.Sno and https://www.wendangku.net/doc/d49523033.html,o=V_https://www.wendangku.net/doc/d49523033.html,o and Grade >V_AVG.AvgGrade ;

--9． 按系统计各系平均成绩在80分以上的人数，结果按降序排列；
select Sdept,count(AvgGrade) Num
from S,V_AVG_S_G
where S.Sno=V_AVG_S_G.Sno and AvgGrade > 80
group by Sdept
order by Num desc;

--10 通过视图V_IS，分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,”S4_MMM” 并查询结果;
insert into S(Sno,Sname,Ssex,Sage,Sdept) values('200215121','三毛','男',21,'IS');
insert into S(Sno,Sname,Ssex,Sage,Sdept) values('200215124','紫薇','女',19,'IS');

update V_IS set Sno='S1_MMM' where Sno='200215121';
update V_IS set Sno='S4_MMM' where Sno='200215124';

select *
from V_IS;
--11． 通过视图V_IS，新增加一个学生记录 ('S12','YAN XI',19,'IS')，并查询结果；
insert into V_IS(Sno,Sname,Sage,Sdept) values('S12','YAN XI',19,'IS');

sel

ect *
from V_IS
where Sno='S12';

--11． 通过视图V_IS，新增加一个学生记录 ('S13','YAN XI',19,'MA')，并查询结果；
insert into V_IS(Sno,Sname,Sage,Sdept) values('S13','Y
AN XI',19,'MA');

-- Tip:由于该学生是MA系的,当有check约束时将会产生更新错误。

--13． 通过视图V_IS，删除学号为“S12”和“S3”的学生信息，并查询结果；
insert into V_IS(Sno,Sname,Sage,Sdept) values('S3','李杨',21,'IS');

select *
from V_IS;

delete
from V_IS
where Sno='S12';

delete
from V_IS
where Sno='S3';

select *
from V_IS;

--14． 要通过视图V_S_C_G，将学号为“S12”的姓名改为“S12_MMM”，是否可以实现？
update V_S_C_G
set Sname='S12_MMM'
where Sno='S12';

--15． 要通过视图V_AVG_S_G，将学号为“S1”的平均成绩改为90分，是否可以实现？
-- 不能实现,因为视图不是真正存在的,对视图的修改实际上是对基本表的修改,
-- 而平均分在基本表中不存在,涉及到多个成绩,所以不能修改。