mssqlserer2000--数据库操作题02-答题参考

– 1单击此处下载数据库备份文件“src.rar”(winRar压缩包)，还原到考生所用的系统中，数据库名为“学生信息”。
use master
go
restore filelistonly from disk=‘d:\操作题02\src\1.bak’
go
xp_cmdshell ‘md d:\data\studentdb’
go
restore database 学生信息 from disk=‘d:\操作题02\src\1.bak’
go
use 学生信息
go
– 2在数据库“学生信息”中，新建一名为“ 个人信息 ”表，表结构如下：
create table 个人信息(
编码 char(10) NOT NULL
,姓名 char(8) NOT NULL
,性别 char(2) NOT NULL
,生日 datetime
,身份证号 char(18)
,家庭住址 varchar(50)
)
go
– 3将“个人信息”表“编码”列设置为主键。
alter table 个人信息
add constraint PK_个人信息_编码 primary key (编码)
go
– 4为“个人信息”表“身份证号”列建立 唯一索引 ，索引名为“IX_个人信息_身份证号”。
create unique index IX_个人信息_身份证号 on 个人信息(身份证号)
go
– 5将“个人信息”表“性别”列的默认值设为“男”，并增加约束，该列只能输入“男”或“女”，约束名为“CK_个人信息_性别”。
alter table 个人信息
add constraint DF_个人信息_性别 default(‘男’) for 性别
go
alter table 个人信息
add constraint CK_个人信息_性别 check(性别=‘男’ or 性别=‘女’)
go
– 6创建一个名为“ 学生总评成绩 ”的视图，显示学生的学号、学生的姓名、学生所学课程的名称及总评成绩。
create view 学生总评成绩
as
select 学生.学号,姓名,课程名称,总评成绩
from 学生,成绩,课程
where
学生.学号=成绩.学号
and 成绩.课程代码=课程.课程代码
go
–或者
– create view 学生总评成绩
– as
– select 学生.学号,姓名,课程名称,总评成绩
– from
– 学生
– join 成绩 on 学生.学号=成绩.学号
– join 课程 on 成绩.课程代码=课程.课程代码
– go
– 7将课程表中至今没有学生选学的课程记录行删除。
delete from 课程
where
课程代码 not in(select 课程代码 from 成绩)
go
– 8将“学生”表所有名字只有 两个字 的学生信息复制到表“ 两字学生 ”中，
– 再将表“两字学生”的姓名中间加入一全角的空格，例如姓名“张三”则更改为“张　三”。
select *
into 两字学生
from 学生
where
len(姓名)=2
go
update 两字学生
set 姓名=left(姓名,1)+’　'+right(rtrim(姓名),1)
go
–select * from 两字学生
– 9单击此处下载Excel文件“电子注册信息.xls”，并按以下要求进行操作：
– 　　将Excel文件“ 电子注册信息.xls ”与学生信息数据库中的“ 学生 ”表信息进行对比，
– 以学号为基准，找出姓名不一致的学生，并将这些信息存到名为“ 学生注册信息对比 ”的表中，该表包括三列、列名分别为：学号 、 学生表姓名 、 电子注册姓名。
–第01步：导入数据到sql
–打开企业管理器，刷新数据库服务器，到表节点，右键，完成导入数据。把数据从excel文件导入到"注册信息$"表中--查看--select\ast from注册信息$
– select * from 学生
–第02步：查询对比，写入数据到新表
select
学生.学号
,学生.姓名 as 学生表姓名
,注册信息$.姓名as电子注册姓名into学生注册信息对比from学生join注册信息$ on 学生.学号=注册信息$.学号where学生.姓名<>注册信息$.姓名
–第03步：核验数据
–select * from 学生注册信息对比
–第04步：清理不用的数据表，以免造成备份文件数据过多。
–drop table 注册信息$
go
– 10编写存储过程“ 学生成绩统计分析 ”，要求如下：
– (1)带一输入参数“@学号”，其开始代码严格如下：
CREATE PROCEDURE 学生成绩统计分析
@学号 Char(10)
AS
– (2)实现的功能是：根据输入参数“ @学号 ”指定的学生，先计算该生的 平均总评成绩 ( 保留2位小数 )，然后：
declare @平均总评成绩 decimal(5,2)
select @平均总评成绩=convert(decimal(5,2),avg(convert(decimal(6,3),总评成绩))) from 成绩 where 学号=@学号
– 　　①将该生的学习状况添加到表“ 学生学习状况 ”中(其xID列为自动赋值)，
– “评价”列的取值为：“优秀”、“一般”或“较差”，
– “优秀”的标准是平均总评成绩>=90，
– “较差”的标准是平均总评成绩<60，
– 其余情况则为“一般”。
– 往该表中添加记录的程序示例：假定@学号中指定学生的@平均总评成绩为95.73，则代码为：
INSERT INTO 学生学习状况 (学号, 姓名, 平均总评成绩, 评价)
SELECT @学号, X.姓名, @平均总评成绩,case when @平均总评成绩>=90 then ‘优秀’ when @平均总评成绩<60 then ‘较差’ else ‘一般’ end as 评价
FROM 学生 X
WHERE X.学号 = @学号
– 　　②若 平均总评成绩在90分以上，则查询出该学生总评成绩>=90的课程，并添加到表“ 优秀课程 ”表中(其kID列为自动赋值)。
–往该表中添加记录的程序示例：假定@学号中指定学生的@平均总评成绩为95.73，现需将该生总评成绩在70～80间的课程添加到该表中，则代码为：
if @平均总评成绩>=90
INSERT INTO 优秀课程 (学号, 课程名称, 总评成绩)
SELECT C.学号, K.课程名称, C.总评成绩
FROM 成绩 C, 课程 K
WHERE C.学号=@学号 and C.总评成绩 >=90 and K.课程代码=C.课程代码
– 　　③若平均总评成绩在60分以下，则查询出该学生总评成绩<60的课程，并添加到表“ 不及格课程 ”表中(其kID列为自动赋值)。
if @平均总评成绩<60
INSERT INTO 不及格课程 (学号, 课程名称, 总评成绩)
SELECT C.学号, K.课程名称, C.总评成绩
FROM 成绩 C, 课程 K
WHERE C.学号=@学号 and C.总评成绩 <60 and K.课程代码=C.课程代码
go

– 11完全备份数据库“学生信息”，备份文件命名为“学生信息.bak”，将文件 “学生信息.bak”用WinRar压缩为“学生信息.rar”，上传到指定的服务器上。
– 　　单击此处上传“学生信息.rar”
use master
go
backup database 学生信息 to disk=‘d:\操作题02\学生信息.bak’
go
drop database 学生信息
go
xp_cmdshell ‘“C:\Program Files\WinRAR\RAR.exe” a -y d:\操作题02\学生信息.rar d:\操作题02\学生信息.bak >NULL’
go
–清理系统
xp_cmdshell ‘rd /s/q d:\data\ > NULL’
go

–最后，在网页界面，点击鼠标，完成压缩文件的上传。
–提示：
– 1 如果不习惯使用命令方式来解压文件和创建压缩文件，可以自行用鼠标右键的方式去解压缩和创建压缩文件包。
– 2 如果是多次反复备分和打包，则建议事先一定清理已经存在了的数据包或是文件，否则有可能会对最新生成的备份或是数据包造成影响。