数据库原理实验四：视图与索引（17题全部代码）

2020-12-08 11:30:43

数据库原理：实验四：视图与索引（17题全部代码）

-------工程大数据库原理实验课
-------来源：计算机系0211181蓝多多、0211181琛哥
2020年12月5日最新版

–1. 使用Transact-SQL语句创建“计算机系”学生的视图Stu_CS1。

``````create view Stu_CS1 (sno,sname,sex,age)as select sno,sname,sex,age from s where sdept='计算机'
``````

–2. 使用SQL Server Management Studio的图形工具在视图Stu_CS1的基础上创建“计算机系”选修了“c2”课程的学生视图Stu_CS2。

``````create view Stu_CS2(sno,sname,sex,age,cno)as select sno,sname,sex,age,cno from Stu_CS1,c where cno='c2'
``````

–3. 在视图Stu_CS2的基础上创建“计算机系”选修了“c2”课程且成绩在“90”分以上的学生视图Stu_CS3。

``````create view Stu_CS3(sno,sname,sex,age,grade)as select Stu_CS2.sno,sname,sex,age, grade from Stu_CS2,sc where grade>90
``````

``````create view S_GRADE(sno,num,avggrade)as select sno,count(cno),avg(grade)from sc group by sno
``````

``````select *from S_GRADE
``````

``````select *from S_GRADE where num >(select num from S_GRADE where sno='S4')
``````

–7.定义“计算机”系学生视图STUDENT_COMPUTER，包括学生学号，姓名，性别，和年龄。

``````create view STUDENT_COMPUTER as select sno,sname,sex,age from s where sdept='计算机'
``````

–8.基于“计算机”系学生视图STUDENT_COMPUTER，插入一个学生的信息，学号为S99，姓名为王敏，性别为男，年龄为22，观察学生表s中数据的变化。

``````insert into STUDENT_COMPUTER values('s99','王敏','男',22)
``````

–9.基于“计算机”系学生视图STUDENT_COMPUTER，删除姓名为“王敏”的学生记录。

``````delete from STUDENT_COMPUTER where sname='王敏'
``````

–10.创建视图view1，要求查询学号为‘s1’的学生选修课程和成绩情况

``````create view view1(sno,cno,grade)as select sno,cno,grade from sc where sno='s1'
``````

–11.更改视图view1，要求查询学号为‘s1’和‘s3’的学生选修课程和成绩情况

``````alter view view1(sno,cno,grade)as select sno,cno,grade from sc where sno='s1'or sno='s3'
``````

–12.通过视图view1更新学号为‘s1’的学生‘c1’课程的成绩为98分

``````update view1 set grade=98 where cno='c1'and sno='s1'
``````

–13.在基本表SC上，建立一个学生学习成绩等级视图SC_等级，该视图中有学生学号，姓名，选修课程的课号，课名，成绩等级。

``````create view SC_等级(sno,sname,cno,cname,gradelevel)as select sc.sno,sname,sc.cno,cname,(case when grade>90 then 'A' when grade>80 and grade<90 then'B' when grade>70 and grade<80 then'C' when grade>60 and grade<70 then'D'
when grade<60 then'E' end)from sc,c,s where s.sno=sc.sno and c.cno=sc.cno
``````

–14. 使用Transact-SQL语句在sc表中创建一个聚集唯一索引，索引名为IDX_SNO_CNO, 索引以SNO升序排列，SNO相同时以CNO降序排列

``````create unique clustered index IDX_SNO_CNO on sc(sno asc,cno desc)
``````

–15. 使用SQL Server Management Studio的图形工具在S表的SDEPT（所在系）列上建立一个普通索引IDX_S

``````create index IDX_S on s (sdept)
``````

–16.在S表建立一个复合索引IDX_SDEPT_AGE，索引以所在系升序排列，系相同时以年龄降序排列

``````create index IDX_SDEPT_AGE on s(sdept asc,age desc)
``````

–17.删除索引IDX_SDEPT_AGE

``````drop index s.IDX_SDEPT_AGE
``````

https://my.oschina.net/u/4417586/blog/4780772