SQL server 实验四 视图的定义、使用

/* 1 */
create view V_IS
as
select *
from Student
where Sdept='IS';

/* 2 */
create view V_S_C_G
as
select Student.Sno,Student.Sname,Course.Cno,Cname,Grade
from Student,Course,SC
where Student.Sno=SC.Sno and Course.Cno=SC.Cno;

/* 3 */
create view V_NUM_AVG
as
select	Sdept,COUNT(*) NUM,AVG(Sage) S_AVG
from Student
group by Sdept;


/* 4 */
create view V_YEAR
as
select Sno,Sname,Ssex,Sdept,2018-Sage BIRTHDAY
from Student;

/* 5 */
create view V_AVG_S_G
as
select Student.Sno,Sname,COUNT(Cno) CLASSES,AVG(Grade) G_AVG
from Student,SC
where Student.Sno=SC.Sno
group by Student.Sno,Sname;


/* 6 */
create view V_AVG_C_G
as
select Course.Cno,Cname,COUNT(Sno) PEOPLE,AVG(Grade) G_AVG
from Course,SC
where Course.Cno=SC.Cno
group by Course.Cno,Cname;

/* 7 */
create view AVG_G_90
as
select Student.Sno,Sname,Cno,Grade
from Student,SC
where Student.Sno=SC.Sno and Student.Sno=(	select Sno
											from SC
											group by Sno
											having AVG(Grade)>=90);

/* 8 */
update V_IS
set Sname='S1_MMM'
where Sno='20161513';

/* 9 */
insert
into V_IS
values('S12','YAN XI','男',19,'IS');

select *
from V_IS
where Sno='S12';

/* 10 */
insert
into V_IS(Sno,Sdept)
values('S13','IS');

delete 
from V_IS
where Sno='S12'
delete
from V_IS
where Sno='S13';

select *
from V_IS;