创建和管理其他数据库对象题目及答案

实验：创建与管理其他数据库对象

一、实验目的

掌握创建管理索引、视图、存储过程和触发器对象的使用方法； 二、实验环境

已安装SQL Server 2005 企业版的计算机； 具有局域网环境,有固定IP; 三、实验学时

4学时 四、实验要求

1. 掌握索引的创建和管理方法； 2. 掌握视图的创建和管理方法； 3. 掌握存储过程的创建和管理方法； 4. 掌握触发器的创建和管理方法； 5. 完成实验报告； 五、实验内容及步骤

请使用T-SQL 语句实现进行以下操作：

1.在student表的sno列上创建唯一性聚集索引index_sno

USE stu GO

CREATE UNIQUE CLUSTERED INDEX index_sno ON student(sno)

2.在student表的sname列上创建唯一性非聚集索引index_sname

USE stu GO

CREATE UNIQUE NONCLUSTERED INDEX index_sname ON student(sname)

3.在student表的sage列上创建非聚集索引index_sage

USE stu GO

CREATE NONCLUSTERED INDEX index_sage ON student(sage)

4.在sc表的sno列和cno列上创建复合非聚集索引index_sno_cno

USE stu GO

CREATE NONCLUSTERED INDEX index_sno_cno ON sc(sno,cno) GO

5.将索引index_sno_cno更名为index_snocno

USE stu GO

EXEC sp_rename 'sc.index_sno_cno','index_snocno','index'

6.删除上面的索引index_snocno

USE stu GO

DROP index index_snocno ON sc

7.查看student表的索引基本信息

USE stu GO

EXEC sp_helpindex student GO

8.创建一个只选修8号课程的学生情况的视图信息

USE stu GO

CREATE VIEW stu_8 AS

SELECT student.sno,student.sname,sage,ssex from student,sc

Where student.sno=sc.sno AND cno=8

9.创建一个视图view_stu，只包含学生的学号，姓名，年龄信息

USE stu GO

CREATE VIEW view_stu AS

SELECT sno,sname,sage From student

10.在上一题视图的基础上创建年龄在20岁以上的的学生的视图

USE stu GO

SELECT sno,sname,sage From view_stu where sage>20

11.创建一个包含不及格同学及90分以上同学的学号、姓名、课程名称及成绩的视图

USE stu GO

SELECT student.sno,student.sname,cname,grade From student,sc,course

where student.sno=sc.sno AND sc.cno=course.cno AND(grade>90 OR grade<60)

12.选择一个上面创建的视图，查看视图的信息

USE stu GO

SELECT sno,sname,sage FROM view_stu

13.选择一个上面创建的视图，修改删除其中的一列

USE stu GO

EXEC sp_rename 'view_stu.sno','view_stu.newsno','COLUMN'

14. 选择一个上面创建的视图进行删除操作

USE stu GO

DROP VIEW stu_8

15.从视图view_stu中查询19岁的男生记录

USE stu GO USE stu GO

SELECT 'view_stu.newsno' AS 'sno',student.sname,student.sage FROM view_stu,student

WHERE 'view_stu.newsno'=student.sno AND student.sage=19 AND ssex='男'

16.通过视图view_stu中插入一行数据。学号：2006010010，姓名：赵青，年龄：

USE stu GO

Insert into view_stu

Values('200601010' ,'赵青', ' ')

17.将视图view_stu中姓名为“李蕾”的年龄改为“22”

USE stu GO

UPDATE view_stu SET sage='22' WHERE sname='李蕾'

18.通过视图 view_stu将姓名为“李贵”的记录删除

USE stu GO DELETE

FROM view_stu WHERE sname='李贵'

19.自行选择上面创建的一个视图插入一个记录。

USE stu GO

Insert into view_stu

Values('200601011' ,'赵贵', '20 ')

20.自行选择一个视图根据一定的条件进行查询。

USE stu GO

SELECT * From view_stu WHERE sage>=20

21.自行选择联合一张表和某个视图进行复合查询。

USE stu GO

SELECT * From view_stu,sc

WHERE 'view_stu.newsno'=sno

22.创建存储过程返回全部男生选课及成绩等相关信息

USE stu GO

CREATE PROCEDURE stu_info AS

SELECT DISTINCT a.sno,sname,cno,grade FROM student a,sc

WHERE a.sno=sc.sno AND ssex='男' EXEC stu_info

23.创建一个带输入参数的存储过程，能够根据参数向学生表中插入一条记录

USE stu GO

CREATE PROCEDURE student_insert (@sno char(9) ,@sname char(8),@ssex char(2),@sage int,@sdept char(4)) AS

INSERT INTO student(sno, sname, ssex, sage, sdept ) VALUES(@sno,@sname,@ssex,@sage,@sdept )

EXEC student_insert ('200666666','萧萧','男','20','CS')

24.创建一个带输入参数的存储过程，根据不同的学生名参数，返回该学生的所有课程成绩。

USE stu GO

CREATE PROCEDURE stu_sc1(@yx char(6) ) AS SELECT DISTINCT a.sno,sname,cno,grade FROM student a ,sc

WHERE a. sno =sc.sno and sname=@yx GO

Exec stu_sc1'张力'

25.创建一个带输入输出参数的存储过程，根据输入的系部名，统计该系学生的

平均成绩并输出。

USE stu GO

CREATE PROCEDURE AVERAGE2

@stu_sdept varchar(8), @average int OUTPUT AS

SELECT @average=avg(grade) FROM student,sc

WHERE student.sno=sc.sno AND sdept=@stu_sdept GO

DECLARE @G int

EXEC AVERAGE2'CS', @G OUTPUT SELECT @G

26.查看第22和23题的存储过程的有关信息，并查看它们的定义。修改第22题的存储过程，改为查询全部女生的资料。删除第23题的存储过程。

USE stu GO

ALTER PROCEDURE stu_info AS

SELECT DISTINCT a.sno,sname,cno,grade FROM student a,sc

WHERE a.sno=sc.sno AND ssex='女' EXEC stu_info GO

DROP PROCEDURE student_insert

27.创建student表的DELETE触发器，当一个学生退学后，信息从student表中删除，触发在sc表中删除该生选课情况。

USE stu GO

28.创建一个触发器，检查在sc表中输入一门课程成绩，必须符合在0到100分范围内。

USE stu

GO

本文来源：https://www.bwwdw.com/article/0vcr.html