操作题答案

1月7日对存储过程做了补充

1月4日下午16：17做了修改

12月29日下午5点程序填空题的第21、22题已经修改

数据库辅导

特别说明：为避免大多数同学问相同的问题，本文给出的参考答案为本老师的个

人见解，与考试答案无关，考试中的程序和语句完全可能与本文中给出的参考解法不同，并且每道题目可能还有其它解决方法, 考题中出现什么样的解法及填空位置老师也不清楚，故文中给出的解法只起答疑作用，仅供同学参考，如有错误请指正。

一、上机操作题（40分）：

请下载“学生信息.bak”文件。在企业管理器中建立一个数据库“学生信息”，并用刚才的文件恢复数据库“学生信息”。在D盘建立一个以自己学号+姓名的文件夹。

还原时特别注意：(1)若是先创建数据库再还原的，一定要选中图中所示的“在现有数据库上强制还原”；（2）注意修改图中红线框中的路径，例如图中所示的“F:\数据库原理及应用\”目录即为下载后“学生信息.bak”文件所在目录，这应根据你将其放在什么路径而定本。

《数据库原理及应用》上机操作

1、从网上下载指定的数据库备份文件，还原到考生所用的系统中，数据库名为“学生信息”。

2、在数据库“学生信息”中，新建一名为“个人信息”表，表结构如下：

个人信息（编码char(10) NOT NULL

姓名char(8) NOT NULL

性别char(2) NOT NULL

生日datetime

身份证号char(18)

家庭住址varchar(50))

3、将“个人信息”表“编码”列设置为主键。

4、为“个人信息”表“身份证号”列建立唯一索引，索引名为“IX_个人信息_身份证号”。

5、将“个人信息”表“性别”列的默认值设为“男”，并增加约束，该列只能输入“男”或“女”，约束名为“CK_个人信息_性别”。

6、创建一名为“学生总评成绩”的视图，显示学号、姓名、课程名称、总评成绩。（此题已经修改了，现在只需学生，成绩，课程三个表了）

以上5道题可参考上机实验或教材或课件，完全可通过SQL企业管理器图形界面完成操作。

7、将课程表中至今没有学生选学的课程记录行删除。

delete from 课程where 课程代码not in (select 课程代码from 成绩)

8、将“学生”表所有名字只有两个字的学生信息复制到表“两字学生”中，再将表“两字学生”的姓名中间加入一全角的空格，例如姓名“张三”则更改为“张三”。

select * into 两字学生from 学生where len(姓名)=2

update 两字学生set 姓名=left(姓名,1)+'' +right(rtrim(姓名),1)

9、将网上下载的Excel文件“电子注册信息.xls”与学生信息数据库中的“学生”表信息进行对比，以学号为基准，找出姓名不一致的学生，并将这些信息存到表“学生注册信息对比”中，该表包括：学号、学生表姓名、电子注册姓名。

可以通过两种方法来完成：建议掌握方法一

方法一：A 、先将电子表格文件内容导入为SQL server 数据表，表名取为注册信息，在导入过程中的下面位置中输入表名即可，

B 、再将“学生”表与“注册信息”表内容按照要求比较，并将结果存入“学生注册信息对比”表中。这易步只需在查询分析器中输入并执行下面的参考语即可：

select 学生.学号,学生.姓名 学生表姓名,a.姓名 电子注册姓名

into 学生注册信息对比

from 学生,注册信息$ a where 学生.学号=a.学号 and 学生.姓名<>a.姓名

方法二：通过链接服务器来完成。链接服务器设置如下图：

设置完成

后，再将“学生”表与链

接服务器表内容按照要求比较，并将结

导入时在此选择表名

果存入“学生注册信息对比”表中。参考语句如下：

select 学生.学号,学生.姓名学生表姓名,a.姓名注册表姓名

into 学生注册信息对比

from 学生,excel...注册信息$ a

where 学生.学号=a.学号and 学生.姓名<>a.姓名

提示：查询连接服务器数据表内容时，一定要将电子表格文件关闭。

10、编写存储过程

编写存储过程“学生成绩统计分析”，带一输入参数“@学号”，其开始代码严格如下：

CREATE PROCEDURE 学生成绩统计分析

@学号 Char(10)

AS

Declare @平均总评成绩as int

要求此存储过程实现的功能是：根据输入参数“@学号”指定的学生，先计算该生的平均总评成绩(保留2位小数)，即在程序中要考虑好变量@平均总评成绩的数据类型，然后：

(1)将该生的学习状况添加到表“学生学习状况”中(其xID列为自动赋值)，“评价”列的取值为：优秀、一般或较差，优秀的标准是平均总评成绩>=90，较差的标准是平均总评成绩<60，其余情况则为一般。往该表中添加记录的程序示例：假定@学号中指定学生的@平均总评成绩为95.73

INSERT INTO 学生学习状况 (学号, 姓名, 平均总评成绩, 评价)

SELECT @学号, X.姓名, @平均总评成绩, '优秀'

FROM 学生 X

WHERE X.学号 = @学号

(2)若平均总评成绩在90分以上，则查询出该学生总评成绩>=90的课程，并添加到表“优秀课程”表中(其kID列为自动赋值)。往该表中添加记录的程序示例：假定@学号中指定学生的@平均总评成绩为95.73，现需将该生总评成绩在70～80间的课程添加到该表中

INSERT INTO 优秀课程 (学号, 课程名称, 总评成绩)

SELECT C.学号, K.课程名称, C.总评成绩

FROM 成绩 C, 课程 K

WHERE C.学号=@学号 and C.总评成绩 between 70 and 80 and K.课程代码=C.课程代码

(3)若平均总评成绩在60分以下，则查询出该学生总评成绩<60的课程，并添加到表“不及格课程”表中(其kID列为自动赋值)。

11、完全备份数据库“学生信息”，备份文件命名为“学生信息.bak”，将文件“学生信息.bak”

上传到指定的服务器上

注：存储过程，这里没有给出

二、程序填空题（3个SQL填空+3个程序设计，每题两空，两空全对才能得分，共30分）：

1、查询2008级学生中的留级（或复读）学生信息

select * from 学生where left(学号,4) <> 年级and 年级=2008

2、查询龙泉和航空港校区的班级个数。

select 所在地点,count(*) AS 班数from 班Group by 所在地点