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 天意购物2ON(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=TYGW2、给TYGW数据库添加一个日志文件：名称：TYGW_log日志文件，存放在D盘根目录下，初始大小5MB，文件最大值为无限制，文件增长值为5%。ALTER DATABASE TYGWADD 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 TYGWMODIFY 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）客 户 编 号商 品 编 号订 单 编 号订 单 日 期付 款 日 期商 品 数 量202000198293269110225678902016-1-262016-1-301578102356173889123331234562016-1-282016-1-281301119782121882122552345622016-2-282016-3-23678123456118041512116592472016-1-72016-1-820212345678121882122472984512016-3-22016-3-25142258847169669624671480612019-4-262019-4-263387487455173889123428226202020-6-182020-6-181387487455286966166129854302019-2-252019-2-262649471323195117281787688012018-11-112018-11-113212345678351174235317896652017-9-42017-9-81步骤一：选择“开始”“所有程序”“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）客 户 编 号商 品 编 号购物车编号商 品 数 量202000198293269110100011222020001981738890251000112157810235617388902520012343301119782121882122123450156781234561180415122312348221234567812188212211223456673833825169669624145678486738338251645245251588272538863093228696616625995022142258847169669624139388893874874552159649732885070492995706926221480415964487INSERT 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 Products2、查询在数据库天意购物中商品信息表 Products中查找食品类的商品，价格在100元以内的商品信息。查询窗口中输入命令如下：USE 天意购物SELECT * FROM Products WHERE Type=食品 AND Price0 AND Price=10 AND Price=50方法二:USE 天意购物SELECT * FROM Products WHERE Type=图书 AND Price BETWEEN 10 AND 504、查询数据库天意购物的商品信息表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 PaidDate5 THEN 4WHEN QUA3 THEN 5WHEN QUA=3 THEN 7ENDWHERE 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)asDECLARE QUA INTSET QUA= ( SELECT SUM(Cquantity) FROM Carts WHERE CustomerId=card)IF QUA5UPDATE Carts SET Discount=4 WHERE CustomerId=card elseIF QUA3 UPDATE Carts SET Discount=5 WHERE CustomerId=cardELSE IF QUA=3UPDATE 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)ASDECLARE QUA INTSET QUA= ( SELECT SUM(Cquantity) FROM Carts WHERE CustomerId=card)UPDATE Carts SET Discount=CASE WHEN QUA5 THEN 4 WHEN QUA3 THEN 5 WHEN QUA=3 THEN 7ENDWHERE CustomerId=card执行结果如图5-30所示。图5-30 执行结果任务三动手实践使用T_SQL语言为表 customers 创建DML触发器名为trig_禁止插入，使得当向表中插入数据时，显示该事务不能被处理，不能插入数据！。方法一：1、创建触发器：CREATE TRIGGER trig_禁止插入1ON CustomersFOR INSERTASBEGIN PRINT 该事务不能被处理，不能插入数据！ ROLLBACK TRANSACTIONEND执行上述代码结果如图5-48所示。图5-48 创建触发器trig_禁止插入12、触发操作INSERT Customers VALUES(578102352,李红,131015,1310156789,天津)SELECT * FROM Customers执行上述代码结果和进行插入操作结果，如图5-49所示。图5-49 执行结果方法二：1、创建触发器：CREATE TRIGGER trig_禁止插入2ON CustomersINSTEAD OF INSERTASBEGIN PRINT 该事务不能被处理，不能插入数据！END执行上述代码结果如图5-50所示。图5-50 创建触发器trig_禁止插入22、触发操作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 ChineseEXEC 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 ChineseUSE 天意购物 EXEC SP_GRANTDBACCESS lisa,lisaEXEC 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,lisaEXEC 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 FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLBackup天意购物_测试备份.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 还原数据库