Sql - server2005实训

数据库应用基础A实训

一、实训目的

1、进一步明确数据库管理的主要功能。

2、进一步掌握SQL Server 2005数据库及其组成。 3、进一步熟悉主要数据库对象的操作。

4、提高应用SQL Server 2005数据库的能力。 5、提高T-SQL语句的编写能力。

二、实训内容

（一）关系运算练习

对于学生选课关系，其关系模式如下： 学生（学号，姓名，年龄，所在系） 课程（课程名，课程号，先行课） 选课（学号，课程号，成绩） 试用关系代数完成下列查询： ① 求成绩不及格的学生学号和姓名

Π学号，姓名（δ成绩<60（学生∞选课））

② 求学过数据库课程的学生学号和姓名

Π学号，姓名（δ课程名=‘数据库’（学生∞选课∞课程））

③ 求数据库成绩不及格的学生学号和姓名

Π学号，姓名（δ课程名=‘数据库’∧成绩<60（学生∞选课∞课程））

④ 求学过数据库和数据结构课程的学生学号和姓名

Π学号，姓名（δ课程名=‘数据库’（学生∞选课∞课程））∩Π学号，姓名（δ课程名=‘数据结构’（学生∞选课∞课程））

⑤ 求学过数据库或数据结构课程的学生学号和姓名

Π学号，姓名（δ课程名=‘数据库’（学生∞选课∞课程））∪Π学号，姓名（δ课程名=‘数据结构’（学生∞选课∞课程）） ⑥ 求没学过数据库课程的学生学号

Π学号（学生）-Π学号（δ课程名=‘数据库’（选课∞课程））

（二）E-R图设计

1、

有以下学校选课管理的信息系统开发需求：（1）学生查看成绩、查看课程和开课教师信息。（2）学生选课。（3）教师查看所开课的选课学生基本信息。给出E-R图，然后给出关系模式。

1

专业号专业名称学号姓名性别1专业属于学生nn考试成绩修学课程号包含系编号1课程名称系课程系名称nn学分构成学院编号1属于教授上课时间n学院名称学院教师工号姓名性别

2

专业号专业名称学号姓名性别1专业属于n学生nn考试成绩修学课程号包含系编号1m课程名称系系名称n1属于课程n学分构成学院编号1教授上课时间mn教师学院名称学院工号姓名性别

2）关系模式

学生（*学号，姓名，性别，&专业号） 教师（*工号，姓名，性别，&系编号）

课程（*课程号，课程名，课程性质（公共、专业），学分） 学院（*学院编号，名称）

系（*系编号，名称，&学院编号） 专业（*编号，名称，&系编号）

学生－课程（*&学号，*&课程号，考试成绩） 教师－课程（*&工号，*&课程号，上课时间） 注：*表示主码，&表示外码

2、请设计一个连锁商店数据库，保存商店的信息，包括：商店编号，商店名称，电话。保存商品的信息，包括：商品编号，商品名称，价格。保存员工的信息，包括：工号，姓名，年龄，性别，住址。同时要保存每种商品在每家商店的销售数量。其中一个商品可以销售多种商品，一种商品也可以在不同的连锁商店销售，一名员工只能在一家商店工作。要求：给出该数据库的E-R图，再将其转换为关系模型。同时标明关系模式的候选码和外码。

3

关系模式：

商店（商店编号，商店名称，电话）候选码：商店编号 商品（商品编号，商品名称，价格）候选码：商品编号

员工（工号，姓名，年龄，性别，住址，商店编号）候选码：工号 外码：商店编号 销售（商店编号，商品编号，销售数量）候选码：（商店编号，商品编号）外码：商店编号，商品编号

（三）使用PD建立数据库概念模型

1、一对一CDM

下图描述了 一个系统用户对应一个扩展信息，也可以没有扩展信息。扩展信息依赖用户信息的存在。并且一个扩展信息只能有一个用户信息。

4

5

FROM T_Grade

WHERE StudentCode='05101101') IF @cn>=3 BEGIN

SET @text='你选了'+CAST(@cn AS char(2))

/* CAST函数将@cn的值转换为长度为2的字符数据*/

SET @text=@text+'门课。很好，你完成了任务！' END ELSE

BEGIN

SET @text='你选了'+CAST(@cn AS char(2)) SET @text=@text+'门课。选课太少，加油！' END

SELECT @text AS 选课提示 【例4-57】计算10！。执行结果见图4-54。

DECLARE @p int, @i smallint, @text varchar(100) SET @p=1

图4-54计算10！的结果

SET @i=1

WHILE @i<=10 BEGIN

SET @p= @p*@i SET @i=@i+1 END

SET @text='1×2×3×……×10='+CAST(@p AS char(10)) SELECT @text AS 计算结果

【例4-58】查询07级女同学的住校情况。执行结果见图4-55。 SELECT StudentName AS '姓名', CASE LiveInDorm

WHEN 0 THEN '未住校' WHEN 1 THEN '住校' 图4-55 07级女生住校情况 END AS '是否住校' FROM T_Student

WHERE Sex='女' AND LEFT(StudentCode,2)='07'

【例4-59】统计每个学生平均成绩并划分等级。执行结果如图4-56所示。 SELECT StudentCode AS '学号', STR(AVG(Grade),5,2) AS '平均成绩', CASE

WHEN AVG(Grade)>=90 THEN 'A' WHEN AVG(Grade)>=80 THEN 'B' WHEN AVG(Grade)>=70 THEN 'C' WHEN AVG(Grade)>=60 THEN 'D' WHEN AVG(Grade)<60 THEN 'E' END AS '等级'

图4-56 成绩等级部分结果 FROM T_Grade GROUP BY StudentCode

【例4-65】在School中创建一个存储过程proc_SearchStudent，查询指定学生的选课情

16

况。

CREATE PROC proc_SearchStudent @stcode char(8) AS

SELECT T_Student.StudentName, T_Course.CourseName FROM T_Student JOIN T_Grade JOIN T_Course

ON T_Grade.CourseCode=T_Course.CourseCode ON T_Student.StudentCode=T_Grade.StudentCode WHERE T_Student.StudentCode=@scode

调用例4-64创建的存储过程proc_Course的语句为： EXEC proc_Course

调用例4-65创建的存储过程proc_SearchStudent，查询'05101101'学生的选课情况 EXEC proc_SearchStudent '05101101' 或

EXEC proc_ SearchStudent @scode='05101101' 【例4-66】在School中创建一个的存储过程

图4-65 proc_SearchStudent执行结果

proc_SearchStudentAvgGrade，查询某个学生的选课数目和平均成绩。

本例带一个输入参数和二个输出参数的存储过程，在定义输出参数时要用OUTPUT说明。

CREATE PROC proc_SearchStudentAvgGrade

@stcode char(8), @stcount int OUTPUT, @stavg int OUTPUT AS

SELECT @stcount=COUNT(T_Grade.StudentCode), @stavg=AVG(T_Grade.Grade) FROM T_Grade

WHERE T_Grade.StudentCode=@stcode GROUP BY T_Grade.StudentCode

调用存储过程proc_SearchStudentAvgGrade，查询'05101101'学生的选课数目和平均成绩，执行结果如图4-66所示。

DECLARE @scount int, @savg int

EXEC proc_SearchStudentAvgGrade '05101101', @scount OUTPUT, @savg OUTPUT PRINT '学生' + '05101101'+的选课数目为'+CAST(@scount AS char(2))+'门' PRINT '学生'+ '05101101'+的平均成绩为'+STR(@savg,5,2)+'分'

【例4-66】在School中创建一个的存储过程proc_SearchStudentAvgGrade，查询某个学生的选课数目和平均成绩。

本例带一个输入参数和二个输出参数的存储过程，在定义输出参数时要用OUTPUT说明。

CREATE PROC proc_SearchStudentAvgGrade

@stcode char(8), @stcount int OUTPUT, @stavg int OUTPUT AS

SELECT @stcount=COUNT(T_Grade.StudentCode), @stavg=AVG(T_Grade.Grade) FROM T_Grade

WHERE T_Grade.StudentCode=@stcode

17

GROUP BY T_Grade.StudentCode

调用存储过程proc_SearchStudentAvgGrade，查询'05101101'学生的选课数目和平均成绩。

DECLARE @scount int, @savg int

EXEC proc_SearchStudentAvgGrade '05101101', @scount OUTPUT, @savg OUTPUT PRINT '学生' + '05101101'+的选课数目为'+CAST(@scount AS char(2))+'门' PRINT '学生'+ '05101101'+的平均成绩为'+STR(@savg,5,2)+'分'

【例4-69】在School数据库中创建一个简单的触发器tri_StudentInsDel，当用户插入或删除T_Student表中学生记录时，能自动显示表中的内容”。

CREATE TRIGGER tri_StudentInsDel ON [dbo].[T_Student] FOR INSERT, DELETE AS

SELECT * FROM T_Student

【例4-70】 在School数据库的T_Student表上创建一个触发器tri_StudentCodeUpdate，当对学号列进行修改时，给出提示信息并取消修改操作。

CREATE TRIGGER tri_StudentCodeUpdate ON T_Student FOR UPDATE AS

DECLARE @text varchar(50) IF UPDATE(StudentCode) BEGIN

SET @text='学生数据被修改!!!' RAISERROR(@text,16,1) ROLLBACK TRANSACTION END 【例4-71】在School数据库的T_Grade表上创建一个触发器tri_Grade，当向T_Grade表中插入一条记录时，检查该记录的学号是否在T_Student表中，如果不存在则取消插入操作,否则显示“插入操作成功完成”。

CREATE TRIGGER tri_Grade ON T_Grade FOR INSERT AS

DECLARE @text varchar(50)

IF EXISTS(SELECT * FROM inserted

WHERE inserted.StudentCode NOT IN (SELECT StudentCode FROM T_Student))

BEGIN

SET @text='学生的学号不存在，将取消该插入操作 ' RAISERROR(@text,16,1) ROLLBACK TRANSACTION END Else

SET @text='插入操作成功完成'

18

1、创建一个数据库，其具体属性如下表 参数 数据库名 逻辑数据文件名 物理数据文件名 数据文件的初始大小 数据文件的最大大小 数据文件增长帐度 日志逻辑文件名 操作系统日志文件名 日志文件初始大小 日志文件增长幅度 参数值 student student_dat D:\\student_dat.mdf 3MB 10MB 15% student_log D:\\student_log.ldf 1MB 10% 2、使用T-SQL语句，在数据库student中创建如下student表，表结构如下：（注：要求表字段取英文名） 列名（英文名） S_no S_name S_sex 列文（中文名） 学号 姓名 性别 数据类型 char char char 长度 11 8 2 允许空值 ? ? ? 主键 默认值：男；约束：只能填“男”或“女” S_birth S_address S_class 出生日期 datetime 家庭地址 varchar 班级 varchar 30 10 ? ? ? 默认值：系统日期 说明 其表记录如下：

3、使用T-SQL语句，在数据库student中创建如下course表，表结构如下：（注：要求表字段取英文名）（10分）

19

其表记录如下：

4、使用T-SQL语句，在数据库student中创建如下score表，表结构如下：（注：要求表字段取英文名）（10分）

其表记录如下：

5． 要在student 表中添加一个长为 20 个字符，名为S_major的类型为CHAR的列： 6、修改学生“周天”的家由“广东广州”搬到“湖南株洲”。 7． 将班级为“20021001”课程号为“1003c#_w”，的成绩统一设置为75。 8． 查询全体学生的姓名、学号、所在班级。 9． 查询全体学生的姓名及其年龄。

10．查询全体学生的学号、姓名和年龄，同时以汉字标题来表示学号、姓名和年龄。

11、查询学号为’20021003010’考试成绩80分以上的学生学号、课程号、学期和成绩并显示汉字标题。

12．查询年龄在18至22岁之间的学生的S_name（姓名）、S_class（班级）、和Nl（年龄不是基本表中的字段，是计算出来的字段）。

13．查询家庭地址为“湖南株洲”和“湖南长沙”班学生的详细信息。 14．查询缺少成绩的学生的学号和相应的课程号。 15．查询所有选修过课程的学生的学号。

16．查询课程号为“1003c#_w”的成绩为前三名的学生的学号和成绩。

17．查询选修了“1003c#_w”课程的学生的学号及其成绩，查询结果按分数的降序排列。 18．查询’ 20021003’班各门课程最高成绩，并显示最高成绩大于80的课程号和最高成绩。 19、查询每个学生的S_no（学号）、S_name（姓名）、S_class（班级）及其所选修课程的成绩情况。

20．查询比“王玉梅”年龄大或同龄的学生的学号、姓名和出生年份，结果按出生年月升序排列。

21、查询与“刘晶晶”在同一个班学习的学生。

22．查询其他班级中比“信息021 ”班任一学生年龄小的学生信息。

23．查询所有选修了“1003c#_w”课程的学生S_no（学号）和S_name（姓名）。 24、查询选修了课程名为“数据库原理与应用”的学生学号和姓名。 25、建立关于information表的s_no列的聚集索引。 26．建立关于course表的c_no列的惟一非聚集索引。

20

27．建立关于score表的s_no列和c_no列的复合非聚集索引。

28．创建一个关于学生成绩的视图，要求含有学号，姓名，课程号，课程名和成绩列。 29．创建关于学生信息的视图stu_info_view，使之仅包含学生的学号、姓名和性别等基本信息。

30．从视图grade_view中查询达到80分以上的成绩。

31．通过视图stu_info_view向表information中插入一条记录。 32、更新s_no为“20031001002”的学生的姓名为“王勇”，成绩增加2分。 33、创建一存储过程，检索信息021班学生的记录。

34、创建一存储过程GetCredit，通过用户输入课程号，输出学分。 35、创建关于性别的默认，默认值为‘男’，并将其绑定到数据表 information的s_sex列上。

36、为数据表score的grade列设置CHECK约束，使grade列的值在0~100之间。

37、为数据表score时，为s_no和c_no设置PRIMARY KEY约束和FOREIGN KEY约束。 38、为数据表score创建一个UPDATE触发器。当试图修改数据表score中的记录时，检查修改后记录中的s_no（学号）是否在数据表information存在，同时c_no（课程号）是否在数据表course中存在，若不是同时存在，则撤消UPDATE操作，并返回一条错误消息。 39、利用游标逐行显示对表course的查询结果,并按课程号排序，显示课程号和课程名称。 40、在myBackup磁盘设备上对student数据库作全库备份后，现在对student数据库出现误操作，需要从备份中恢复。

21

27．建立关于score表的s_no列和c_no列的复合非聚集索引。

28．创建一个关于学生成绩的视图，要求含有学号，姓名，课程号，课程名和成绩列。 29．创建关于学生信息的视图stu_info_view，使之仅包含学生的学号、姓名和性别等基本信息。

30．从视图grade_view中查询达到80分以上的成绩。

31．通过视图stu_info_view向表information中插入一条记录。 32、更新s_no为“20031001002”的学生的姓名为“王勇”，成绩增加2分。 33、创建一存储过程，检索信息021班学生的记录。

34、创建一存储过程GetCredit，通过用户输入课程号，输出学分。 35、创建关于性别的默认，默认值为‘男’，并将其绑定到数据表 information的s_sex列上。

36、为数据表score的grade列设置CHECK约束，使grade列的值在0~100之间。

37、为数据表score时，为s_no和c_no设置PRIMARY KEY约束和FOREIGN KEY约束。 38、为数据表score创建一个UPDATE触发器。当试图修改数据表score中的记录时，检查修改后记录中的s_no（学号）是否在数据表information存在，同时c_no（课程号）是否在数据表course中存在，若不是同时存在，则撤消UPDATE操作，并返回一条错误消息。 39、利用游标逐行显示对表course的查询结果,并按课程号排序，显示课程号和课程名称。 40、在myBackup磁盘设备上对student数据库作全库备份后，现在对student数据库出现误操作，需要从备份中恢复。

21

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