数据库 实验四 视图、索引和数据库关系图实验

–1、视图创建
–在选修数据库中，以Student(sno,sname,ssex,sage,sdept)，Course(cno,cname,cpno,credit)，SC(sno,cno,grade)表为基础完成下列视图的创建

–（1）创建计算机系学生的基本情况视图V_CS。

drop view V_CS;
create view V_CS AS SELECT * FROM student where sdept='CS';
select * from V_CS;

–（2）将Student,Course,SC表中学生的sno,sname,cname,grade创建为视图V_SC_G。

select * from V_SC_G; 
create view V_SC_G AS SELECT s.sno,sname,cname,grade FROM student s,sc,course c where s.sno=sc.sno and c.cno=sc.cno;

–（3）将各系学生人数、平均年龄创建视图V_Num_Avg。

create view V_Num_Avg(sdept,count,avg) as select sdept,COUNT(sno),AVG(sage) from student group by sdept; 
select * from  V_Num_Avg;

–（4）创建一个反映学生出生年份的视图V_Year。
drop view V_Year;

create view V_Year(sno,sname,sbirth) as select sno,sname,2020-sage from student ;
select * from V_Year;

–（5）将各位学生选修课程的门数及平均成绩创建视图V_AVG_S。

create view V_AVG_S(sno,sdept,count,avg_grade) as select s.sno,sdept,COUNT(sc.sno),AVG(grade) from sc , student s where sc.sno=s.sno group by s.sno,sdept;  

select * from V_AVG_S;
–（6）将各门课程选修的人数及平均成绩创建视图V_AVG_C。

create view V_AVG_C(cname,count,avg_grade) as select cname,COUNT(sc.cno),AVG(grade) from sc,course c where c.cno=sc.cno group by sc.cno,cname; 
select * from V_AVG_C;

–2、视图查询
–（1）查询平均成绩为90分以上的学生的学号、姓名和成绩。

select distinct v1.sno,cname,sname,grade from V_SC_G v1,V_AVG_S v2 where v1.sno=v2.sno and avg_grade>90;

–（2）查询各门课程成绩均大于平均成绩的学生的学号、姓名、课程和成绩。

select sno,sname,cname,grade from V_SC_G v1
where v1.grade> (
	select avg_grade from V_AVG_C v2 where v1.cname=v2.cname
);

–（3）按系统计各系平均成绩在80分以上的人数，结果按降序排列。

select sdept,count(sno) from V_AVG_S where avg_grade>80 GROUP BY sdept  ;

–3、索引创建
–（1）对学生表Student创建学号Sno创建聚簇索引，并按降序排列。

create clustered index S_sno on student(sno desc);

–（2）对选修表SC创建学号Cno创建升序，成绩降序索引。

create index SC_cno_grade on sc(cno asc,grade desc);