QL-Server-2016-数据库应用实战练习题题库及答案(动手实践).docx

QL Server 2016 数据库应用实战练习题题库及答案（动手实践）

动手实践与综合实训答案

任务1-1动手实践

请绘出购物车表（Carts）的E-R图。

任务1-2动手实践
利用完整格式创建“天意购物2”数据库，要求数据库有两个数据文件和两个日志文件。具体要求见表1-8 。
表1-8 “天意购物3”数据库参数说明
参 数
参 数 值
参 数
参 数 值
数据库名称
天意购物2
日志文件逻辑名
天意购物2_log1
主数据文件逻辑名
天意购物2_data1
日志文件物理名
C:\SQL\天意购物2_LOG1.LDF
主数据文件物理名
C:\SQL\天意购物2_DATA1.MDF
日志文件初始大小
50MB
主数据文件初始大小
100MB
日志文件最大值
100MB
主数据文件最大值
200 MB
日志文件增长值
10MB
主数据文件增长值
20 MB
事务日志文件逻辑名
天意购物2_log2
数据文件逻辑名
天意购物2_data2
日志文件物理名
C:\SQL\天意购物2_LOG2.LDF
数据文件物理名
C:\SQL\天意购物2_DATA2.NDF
日志文件初始大小
50MB
数据文件初始大小
20MB
日志文件最大值
100MB
数据文件最大值
UNLIMITED
日志文件增长值
10MB
数据文件增长值
10%

在查询窗口中输入：
CREATE DATABASE 天意购物2
ON
(NAME='天意购物2_DATA1', --主数据文件属性设置
FILENAME='C:\SQL\天意购物2_DATA1.MDF',
SIZE=100MB,
MAXSIZE=200MB,
FILEGROWTH=20MB
),
(NAME='天意购物2_DATA2', --次要数据文件属性设置
FILENAME='C:\SQL\天意购物2_DATA2.NDF',
SIZE=20MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=10%
)
LOG ON
(NAME='天意购物2_LOG1', --日志文件属性设置
FILENAME='C:\SQL\天意购物2_LOG1.LDF',
SIZE=50MB,
MAXSIZE=100MB,
FILEGROWTH=10MB
),
(NAME='天意购物2_LOG2', --日志文件属性设置
FILENAME='C:\SQL\天意购物2_LOG2.LDF',
SIZE=50MB,
MAXSIZE=100MB,
FILEGROWTH=10MB)

图1-30 创建两个数据文件和日志文件的运行窗口

任务1-3动手实践
1、 将天意购物数据库名称改写为TYGW。
ALTER DATABASE 天意购物 MODIFY NAME=TYGW
2、 给TYGW数据库添加一个日志文件：
名称：TYGW_log日志文件，存放在D盘根目录下，初始大小5MB，文件最大值为无限制，文件增长值为5%。
ALTER DATABASE TYGW
ADD LOG FILE
(NAME=TYGW_log,
FILENAME='D:\TYGW_log.LDF',
SIZE=5 MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=5%)
3、 修改上面添加日志文件TYGW_log，初始大小10MB，文件最大值为100MB，文件增长值为2MB。
ALTER DATABASE TYGW
MODIFY FILE
(NAME=TYGW_log,
SIZE=10MB,
MAXSIZE=100MB,
FILEGROWTH=2MB)

项目二
任务一
动手实践
1、请使用SSMS方式和T-SQL方式完成
Orders（订购信息）表、Carts（购物车信息）的表结构创建。表结构信息如表2-4 和表2-5。
表2-4 Orders（订购信息）表
字段名
描述
数据类型
是否为空
是否为主键
CustomerID
客户编号
char(9)
否

ProductID
商品编号
Varchar(9)
否

OrderID
订单编号
Varchar(10)
否

OrderDate
订单日期
datatime
否

PaidDate
付款日期
datatime
否

Oquantity
订购数量
Int
否

表2-5 Carts（购物车信息）表
字段名
描述
数据类型
是否为空
是否为主键
CustomerID
客户编号
char(9)
否

ProductID
商品编号
Varchar(9)
否

CartID
购物车编号
Varchar(10)
否

Cquantity
商品数量
Int
否

方法一：使用ssms方式：
（1）Orders（订购信息）表结构的创建
步骤一：启动SQL Server 2016中的SQL Server Management Studio工具，以Windows身份验证或SQL Server身份验证登录。
步骤二：在“对象资源管理器”中，展开“数据库”列表，展开创建的天意购物数据库，右单击“表”，在弹出的快捷菜单中选择“新建表”命令。
步骤三：打开“表设计器”窗口，定义表的结构。显示表基本属性——列名、数据类型和允许Null值。输入完毕。
步骤四：保存创建的表：单击“保存”按钮，弹出“选择名称”对话框，输入新建的表名称：Orders，即完成了数据表的创建。
方法二：使用T-SQL方式：
CREATE TABLE Orders
(
CustomerId CHAR(9) NOT NULL,
ProductId VARCHAR(9) NOT NULL,
OrderId VARCHAR(10) NOT NULL,
OrderDate DATETIME NOT NULL,
PaidDate DATETIME NOT NULL,
Oquantity INT NOT NULL
)
任务二
动手实践
1、要求向“天意购物”数据库中的Orders（订购信息）表添加记录。数据如表2-8所示。（请使用SSMS方式完成）
表2-8 订购信息表（Orders）
客 户 编 号
商 品 编 号
订 单 编 号
订 单 日 期
付 款 日 期
商 品 数 量
202000198
293269110
22567890
2016-1-26
2016-1-30
1
578102356
173889123
33123456
2016-1-28
2016-1-28
1
301119782
121882122
55234562
2016-2-28
2016-3-2
3
678123456
118041512
11659247
2016-1-7
2016-1-8
20
212345678
121882122
47298451
2016-3-2
2016-3-2
5
142258847
169669624
67148061
2019-4-26
2019-4-26
3
387487455
173889123
42822620
2020-6-18
2020-6-18
1
387487455
286966166
12985430
2019-2-25
2019-2-26
2
649471323
195117281
78768801
2018-11-11
2018-11-11
3
212345678
351174235
31789665
2017-9-4
2017-9-8
1
步骤一：选择“开始”→“所有程序”→“Microsoft SQL Server 2016”→SQL Server Management Studio 命令，使用“Windows 身份验证”建立连接，进入SQL Server Management Studio窗口（简称SSMS窗口）。
步骤二：在“对象资源管理器”窗格中依次展开数据库“天意购物”，选择Orders表，右击，在弹出的快捷菜单中选择“编辑前200行”命令。
步骤三：在相应列分别输入表中信息，然后按【Enter】键即可。
步骤四：添加每条记录都重复步骤三。
步骤五：全部数据输入完毕后，直接关闭编辑窗口即可。
2、要求向“天意购物”数据库中的Carts（购物车）表添加记录。数据如表2-9 所示。（请使用T-SQL方式完成）
表2-9 购物车信息表（Carts）
客 户 编 号
商 品 编 号
购物车编号
商 品 数 量
202000198
293269110
1000112
2
202000198
173889025
1000112
1
578102356
173889025
2001234
3
301119782
121882122
1234501
5
678123456
118041512
2312348
2
212345678
121882122
1122345
6
673833825
169669624
1456784
8
673833825
164524525
1588272
5
388630932
286966166
2599502
2
142258847
169669624
1393888
9
387487455
215964973
2885070
4
929957069
262214804
1596448
7
INSERT Carts VALUES('202000198','293269110','1000112',2)
INSERT Carts VALUES('202000198','173889025','1000112',1)
INSERT Carts VALUES('578102356','173889025','2001234',3)
INSERT Carts VALUES('301119782','121882122','1234501',5)
INSERT Carts VALUES('678123456','118041512','2312348',2)
INSERT Carts VALUES('212345678','121882122','1122345',6)
INSERT Carts VALUES('673833825','169669624','1456784',8)
INSERT Carts VALUES('673833825','164524525','1588272',5)
INSERT Carts VALUES('388630932','286966166','2599502',2)
INSERT Carts VALUES('142258847','169669624','1393888',9)
INSERT Carts VALUES('387487455','215964973','2885070',4)
INSERT Carts VALUES('929957069','262214804','1596448',7)
任务三
动手实践
使用T-SQL语句将“天意购物”数据库中的订购信息表（Orders）表更名为Orders1。
在查询分析器中运行以下命令：
SP_RENAME Prod,Products

项目三任务实践标准答案
任务一动手实践
1、使用三种修改列标题的方式显示‘Products’表中的”ProductId ”,”Type ”, ”ProductName ”的信息。
USE 天意购物
SELECT ProductId '商品编号',
Type AS 类型, 商品名称= ProductName
FROM Products
2、查询在数据库天意购物中商品信息表 Products中查找’食品’类的商品，价格在100元以内的商品信息。查询窗口中输入命令如下：
USE 天意购物
SELECT * FROM Products
WHERE Type='食品' AND Price>0 AND Price<100
3、查询数据库天意购物的商品信息表Products中图书价格在10元到50元的商品信息。查询窗口中输入命令如下：
方法一:
USE 天意购物
SELECT * FROM Products
WHERE Type='图书' AND Price>=10 AND Price<=50
方法二:
USE 天意购物
SELECT * FROM Products
WHERE Type='图书' AND Price BETWEEN 10 AND 50
4、查询数据库天意购物的商品信息表Products中咖啡价格不在100元到200元的商品信息。查询窗口中输入命令如下：
USE 天意购物
SELECT * FROM Products
WHERE ProductName='咖啡'
AND Price NOT BETWEEN 100 AND 200

任务二动手实践
查询显示天意购物数据库商品信息表Products的商品类型，商品名称，商品价格，订单表Orders付款时间等信息,要求商品类型包含“电”字，付款时间在2020-1-1以前的信息。
USE 天意购物
SELECT P.Type '商品类型',P.ProductName '商品名称',
P.Price '商品价格',O.PaidDate '付款时间'
FROM Products P INNER JOIN Orders O ON P.ProductId=O.ProductId
WHERE Type LIKE '%电%' AND PaidDate<='2020-1-1'
项目4 视图与索引
动手实践：

1、要求使用T-SQL语言创建视图，名称为“View_OP”,包含Orders和Products表，要求在该视图中输出以下字段： CustomerId， ProductId ，ProductName，Price。
CREATE VIEW View_OP
AS
SELECT O.CustomerId, O.ProductId, P.ProductName ,P.Price
from Orders O INNER JOIN Products P
ON O.ProductId=P.ProductId

2、要求使用T-SQL语言修改视图“View_OP”，输出以下字段:CustomerId，ProductId，ProductName，Price，要求输出电视机商品信息。
USE 天意购物
GO
ALTER VIEW View_OP
AS
SELECT O.CustomerId, O.ProductId, P.ProductName ,P.Price
from Orders O INNER JOIN Products P
ON O.ProductId=P.ProductId
WHERE ProductName='电视机'

3、要求使用T-SQL语言为视图“View_OP”进行重命名，新的视图名称为“View_OrdPro”。
USE 天意购物
GO
SP_RENAME View_OP,View_OrdPro

4、要求使用T-SQL语言查询视图“View_OrdPro”。

USE 天意购物
GO
SELECT * FROM View_OrdPro

5、要求使用T-SQL语言删除视图“View_OrdPro”。
USE 天意购物
GO
DROP VIEW View_OrdPro
GO

知识巩固：
1、C 2、C 3、C 4、A

1、要求在 “Customers” 表中的“Address” 列上创建名为”unique_Address”的唯一非聚集索引。
USE 天意购物
CREATE UNIQUE NONCLUSTERED
INDEX Unique_Address
ON Customers(Address)

2、要求使用T-SQL命令查看“天意购物”数据库中Customers表所建立的索引。
USE 天意购物
GO
SP_HELPINDEX Customers

3、要求使用T-SQL命令将“天意购物”数据库中Customers表所建立的索引unique_Address重命名为unique_Add。
USE 天意购物
GO
SP_RENAME ‘Customers.unique_Address’, ‘unique_Add’

4、要求使用T-SQL命令重建Customers表所建立的索引unique_Add。
USE 天意购物
GO
ALTER INDEX unique_Add
ON Customers
REBUILD
GO
5、要求使用T-SQL命令删除Customers表所建立的索引unique_Add。
USE 天意购物
GO
DROP INDEX Customers.unique_Add
GO

知识巩固：
1、D 2、D 3、B 4、聚集索引 非聚集索引

项目五 动手实践答案
任务一
动手实践
在“天意购物”数据库carts表中添加一个折扣字段（discount int），要求将客户号为'202000198'的客户所购商品的折扣信息通过以下方式写入：商品数量3件的可以打7折，3件以上可以打5折，5件以上打4折（使用case语句完成）。

DECLARE @QUA INT
SET @QUA= ( SELECT SUM(Cquantity) FROM Carts WHERE CustomerId='202000198')
UPDATE Carts SET Discount=
CASE
WHEN @QUA>5 THEN 4
WHEN @QUA>3 THEN 5
WHEN @QUA=3 THEN 7
END
WHERE CustomerId='202000198'

执行结果如图5-10所示。

图5-10 执行结果

任务二
动手实践
（1）要求创建一个存储过程，在“天意购物”数据库carts表中添加一个折扣字段（discount int），要求将客户号为'202000198'的客户所购商品的折扣信息通过以下方式写入：
商品数量3件的可以打7折，3件以上可以打5折，5件以上打4折（使用if 语句完成）。
create procedure pro_carts(@card varchar(15))
as
DECLARE @QUA INT
SET @QUA= ( SELECT SUM(Cquantity) FROM Carts WHERE CustomerId=@card)
IF @QUA>5
UPDATE Carts SET Discount=4 WHERE CustomerId=@card
else
IF @QUA>3
UPDATE Carts SET Discount=5 WHERE CustomerId=@card
ELSE
IF @QUA=3
UPDATE Carts SET Discount=7 WHERE CustomerId=@card
执行结果如图5-29所示。

图5-29 执行结果

（2）要求创建一个存储过程，在“天意购物”数据库carts表中添加一个折扣字段（discount int），要求将客户号为'202000198'的客户所购商品的折扣信息通过以下方式写入：
商品数量3件的可以打7折，3件以上可以打5折，5件以上打4折（使用case语句完成）。

ALTER PROCEDURE pro_carts(@card varchar(15))
AS
DECLARE @QUA INT
SET @QUA= ( SELECT SUM(Cquantity) FROM Carts WHERE CustomerId=@card)
UPDATE Carts SET Discount=
CASE
WHEN @QUA>5 THEN 4
WHEN @QUA>3 THEN 5
WHEN @QUA=3 THEN 7
END
WHERE CustomerId=@card
执行结果如图5-30所示。

图5-30 执行结果

任务三
动手实践
使用T_SQL语言为表 customers 创建DML触发器名为trig_禁止插入，使得当向表中插入数据时，显示该事务不能被处理，不能插入数据！'。
方法一：
1、创建触发器：
CREATE TRIGGER trig_禁止插入1
ON Customers
FOR INSERT
AS
BEGIN
PRINT '该事务不能被处理，不能插入数据！'
ROLLBACK TRANSACTION
END

执行上述代码结果如图5-48所示。

图5-48 创建触发器trig_禁止插入1
2、触发操作
INSERT Customers VALUES('578102352','李红','131015','1310156789','天津')
SELECT * FROM Customers
执行上述代码结果和进行插入操作结果，如图5-49所示。

图5-49 执行结果
方法二：
1、创建触发器：
CREATE TRIGGER trig_禁止插入2
ON Customers
INSTEAD OF INSERT
AS
BEGIN
PRINT '该事务不能被处理，不能插入数据！'
END
执行上述代码结果如图5-50所示。

图5-50 创建触发器trig_禁止插入2
2、触发操作
INSERT Customers VALUES('578102352','李红','131015','1310156789','天津')
SELECT * FROM Customers
执行上述代码结果和进行插入操作结果，如图5-51所示。

图5-51 执行结果

任务一
动手实践
1、要求使用T-SQL语言创建SQL Server身份验证的登录账户lisa，密码为123，默认数据库为master，默认语言为简体中文。之后再使用T-SQL语言删除该用户。
EXEC SP_ADDLOGIN 'lisa','123','master','Simplified Chinese'
EXEC SP_DROPLOGIN 'lisa'
命令执行结果如图6-40所示

图 6-40 创建和删除SQL Server身份验证的登录账户命令执行结果
2、要求使用T-SQL语言给登录账户lisa添加一个天意购物据库的同名数据库用户账户lisa，然后用T-SQL语言从数据库“天意购物”中查看用户lisa，最后从当前数据库“天意购物”中删除用户lisa。
创建并查看用户的命令如下，执行结果如图6-41所示
EXEC SP_ADDLOGIN 'lisa','123','天意购物','Simplified Chinese'
USE 天意购物
EXEC SP_GRANTDBACCESS 'lisa','lisa'
EXEC SP_HELPUSER 'lisa'

图6-41 创建和查看数据库用户
删除用户的命令如下，执行结果如图6-42所示
USE 天意购物
EXEC SP_REVOKEDBACCESS 'lisa'

图6-42 删除数据库用户
3、要求使用T-SQL语言将lisa用户添加到“天意购物”数据库的db_accessadmin角色中，然后删除该角色。
创建lisa用户，将其添加到db_accessadmin角色的命令如下，执行结果如图6-43所示
USE 天意购物
EXEC SP_GRANTDBACCESS 'lisa','lisa'
EXEC SP_ADDROLEMEMBER 'db_accessadmin','lisa'

图6-43 数据库用户删除角色

将lisa用户从db_accessadmin删除角色的命令如下，执行结果如图6-44所示
USE 天意购物
EXEC SP_DROPROLEMEMBER 'db_accessadmin','lisa'

图6-44 数据库用户删除角色
任务二
动手实践
1、要求使用T-SQL语言为数据库“天意购物”创建名称为“天意购物_测试备份”备份设备。然后查看所有备份设备的信息。
备份设备使用的命令如下：
EXEC SP_ADDUMPDEVICE 'disk' , '天意购物_测试备份' ,
'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\天意购物_测试备份.bak'
命令执行结果如图6-67所示

图6-67 “天意购物”备份设备
查看备份使用的命令如下：
EXEC SP_HELPDEVICE
命令执行结果如图6-68所示

图6-68 查看“天意购物”备份设备
2、要求使用T-SQL语言，使用完整备份方式备份数据库“天意购物”。指定的备份设备为“天意购物_测试备份”，备份名称为“测试天意购物完整备份”。
采用完整备份方式备份数据库的命令如下：
BACKUP DATABASE 天意购物
TO 天意购物_测试备份 WITH INIT, NAME='测试天意购物完整备份' ,
DESCRIPTION='测试采用完整备份方式'
命令执行结果如图6-69所示。

图6-69 完整备份方式备份“天意购物”
3、要求使用T-SQL语言还原天意购物数据库，设备备份为“天意购物_测试备份”。
使用的命令如下：
RESTORE DATABASE 天意购物
FROM 天意购物_测试备份
WITH REPLACE
命令执行的结果如图6-70所示。

图6-70 还原数据库