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

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

动手实践与综合实训答案

任务1-1动手实践

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

任务1-2动手实践

利用完整格式创建"天意购物2"数据库，要求数据库有两个数据文件和两个日志文件。

具体要求见表1-8。

表1-8“天意购物3"数据库参数说明

参数参数值参数参数值

数据库名称天意购物2日志文件逻辑名天意购物2,logl

C:\SQL\天意购物

主数据文件逻辑名天意购物2,datal日志文件物理名

2_LOG1.LDF

主数据文件物理名C:\SQL\天意购物2.DATA1.MDF日志文件初始大小50MB

主数据文件初始大小100MB日志文件最大值100MB

主数据文件最大值200MB日志文件增长值10MB

事务日志文件逻辑

主数据文件增长值20MB天意购物2,log2

名

C:\SQL\天意购物

数据文件逻辑名天意购物2_data2日志文件物理名

2_LOG2.LDF

数据文件物理名C:\SQL\天意购物2_DATA2.NDF日志文件初始大小50MB

数据文件初始大小20MB日志文件最大值100MB

数据文件最大值UNLIMITED日志文件增长值10MB

数据文件增长值10%

在查询窗口中输入：

CREATEDATABASE天意购物2

ON

(NAME=，天意购物2_DATA1',--主数据文件属性设置

FILENAME=t:\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%

)

LOGON

(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)

32SQLQueiy2.sql-PC-20210120ZVSU.^SI9«1(PC-20210120ZVSU\Admini$tr...快速足动(Ctrk®_P-

文件但费田(©视圉QD项目(E)IS®eC(W)Sfe(H)

O•J3•­eia广是MSiS®医Iwa血®&■-0-户二

-:，抗行区：0I'':，」,-I一，Y

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

任务1-3动手实践

1、将天意购物数据库名称改写为TYGW。

ALTERDATABASE天意购物MODIFYNAMETYGW

2、给TYGW数据库添加一个日志文件：

名称：TYGWJog日志文件，存放在D盘根目录下，初始大小5MB,文件最大值为

无限制，文件增长值为5%。

ALTERDATABASETYGW

ADDLOGFILE

（NAME=TYGW_log,

FILENAME」D:\TYGW_log.LDF',

SIZE=5MB.

MAXSIZEUNLIMITED,

FILEGR0WTH=5%）

3、修改上面添加日志文件TYGWJog,初始大小10MB,文件最大值为100MB,

文件增长值为2MB。

ALTERDATABASETYGW

MODIFYFILE

（NAMETYGW_log,

SIZE10MB,

MAXSIZE100MB,

FILEGROWTH2^fB）

项目二

任务一

_动手实践

L请使用SSMS方式和T-SQL方式完成

Orders（订购信息）表、Carts（购物车信息）的表结构创建。表结构信息如表2-4和表

2-5。

表2-4Orders（订购信息）表

表2-5Carts（购物车信息）表

方法一：使用ssms方式：

（1）Orders（订购信息）表结构的创建

步骤一启动SQLServer2016中的SQLServerManagementStudio工具以Windows

身份验证或SQLServer身份验证登录。

字段名描述数据类型是否为空是否为主键

CustomerlD客户编号char(9)否

ProductID商品编号Varchar(9)否

OrderlD订单编号Varchar(lO)否

OrderDate订单日期datatime否

PaidDate付款日期datatime否

Oquantity订购数量Int否

步骤二：在"对象资源管理器"中，展开"数据库”列表，展开创建的天意购物数据库，

字段名描述数据类型是否为空是否为主键

CustomerlD客户编号char(9)否

ProductID商品编号Varchar(9)否

CartID购物车编号Varchar(lO)否

Cquantity商品数量Int否

右单击"表"，在弹出的快捷菜单中选择“新建表"命令。

步骤三：打开"表设计器"窗口，定义表的结构。显示表基本属性一一列名、数据

类型和允许Null值。输入完毕。

步骤四：保存创建的表：单击“保存"按钮，弹出"选择名称"对话框，输入新建的表名

称：Orders,即完成了数据表的创建。

方法二：使用T-SQL方式：

CREATETABLEOrders

CustomerldCHAR(9)NOTNULL,

ProductldVARCHAR(9)NOTNULL,

OrderIdVARCIIAR(IO)NOTNULL,

OrderDateDATETIMENOTNULL,

PaidDateDATETIMENOTNULL,

OquantityINTNOTNULL

)

任务二

动手实践

____________

1、要求向"天意购物”数据库中的。rders(订购信息)表添加记录。数据如表2-8所示。

(请使用SSMS方式完成)

表2-8订购信息表（Orders）

客户编号商品编号订单编号订单日期付款日期商品数量

202000198293269110225678902016-1-262016-1-301

578102356173889123331234562016-1-282016-1-281

301119782121882122552345622016-2-282016-3-23

678123456118041512116592472016-1-72016-1-820

212345678121882122472984512016-3-22016-3-25

142258847169669624671480612019-4-262019-4-263

387487455173889123428226202020-6-182020-6-181

387487455286966166129854302019-2-252019-2-262

649471323195117281787688012018-11-112018-11-113

212345678351174235317896652017-9-42017-9-81

步骤一：选择"开始"一"所有程序"一"MicrosoftSQLServer2016”-SQLServer

ManagementStudio命令,使用"Windows身份验证”建立连接，进入SQLServer

ManagementStudio窗口（简称SSMS窗口）。

步骤二：在“对象资源管理器”窗格中依次展开数据库“天意购物”，选择Orders表,

右击，在弹出的快捷菜单中选择“编辑前200行”命令。

步骤三：在相应列分别输入表中信息，然后按【Enter】键即可。

步骤四：添加每条记录都重复步骤三。

步骤五：全部数据输入完毕后，直接关闭编辑窗口即可。

2、要求向“天意购物"数据库中的Carts（购物车）表添加记录。数据如表2-9所示。

（请使用T-SQL方式完成）

表2-9购物车信息表（Carts）

客户编号商品编号购物车编号商品数量

20200019829326911010001122

20200019817388902510001121

57810235617388902520012343

30111978212188212212345015

67812345611804151223123482

21234567812188212211223456

67383382516966962414567848

67383382516452452515882725

38863093228696616625995022

14225884716966962413938889

38748745521596497328850704

92995706926221480415964487

INSERTCartsVALUESC2020001987293269110'.,1000112'.2)

INSERTCartsVALUES('202000198'.,173889025','1000112',1)

INSERTCartsVALUES(：578102356','173889025'：2001234'.3)

INSERTCartsVALUESC301119782'.,121882122','1234501'.5)

INSERTCartsVALUES('678123456'；118041512','2312348',2)

INSERTCartsVALUESC212345678'.,121882122','1122345'.6)

INSERTCartsVALUESC673833825','169669624','1456784',8)

INSERTCartsVALUESC673833825',,164524525'.,1588272'.5)

INSERTCartsVALUES(,388630932'：286966166','2599502,,2)

INSERTCartsVALUES('142258847',,169669624,,,1393888'.9)

INSERTCartsVALUES(,387487455','215964973\'2885070'.4)

INSERTCartsVALUES('929957069',,262214804'.,1596448',7)

任务三

动手实践

使用T-SQL语句将“天意购物”数据库中的订购信息表（Orders）表更名为Orders'

在查询分析器中运行以下命令:

SP_RENAMEProd,Products

项目三任务实践标准答案

任务一动手实践

1、使用三种修改列标题的方式显示‘Products'表中的"ProductldType

ProductName”的信息。

USE天意购物

SELECTProductld'商品编号'，

TypeAS类型，商品名称=ProductName

FROMProducts

2、查询在数据库天意购物中商品信息表Products中查找，食品，类的商品，价格在100

元以内的商品信息。查询窗口中输入命令如下：

USE天意购物

SELECT*FROMProducts

WHEREType='食品'ANDPrice>0ANDPrice<100

3、查询数据库天意购物的商品信息表Products中图书价格在10元到50元的商品信息。

查询窗口中输入命令如下：

方法一：

USE天意购物

SELECT*FROMProducts

WHEREType='图书，ANDPrice>=10ANDPrice<=50

方法二：

USE天意购物

SELECT*FROMProducts

WHEREType=，图书，ANDPriceBETWEEN10AND50

4、查询数据库天意购物的商品信息表Products中咖啡价格不在100元到200元的商品

信息。查询窗口中输入命令如下：

USE天意购物

SELECT*FROMProducts

WHEREProductName='咖啡'

ANDPriceNOTBETWEEN100AND200

任务二动手实践

查询显示天意购物数据库商品信息表Products的商品类型，商品名称，商品价格，订单

表。rders付款时间等信息，要求商品类型包含"电"字，付款时间在2020-1-1以前的信息。

USE天意购物

SELECTP.Type'商品类型'，P.ProductName'商品名称

P.Price'商品价格'，0.PaidDate'付款时间'

FROMProductsPINNERJOINOrders0ONP.ProductId=0.Productld

WHERETypeLIKE'%电%'ANDPaidDate<=,2020-1-1,

项目4视图与索引

动手实践：

1、要求使用T-SQL语言创建视图，名称为"ViewJDP",包含Orders和Products表，要求

在该视图中输出以下字段：

Customerld,Productld,ProductName,Priceo

CREATEVIEWView_OP

AS

SELECTO.Customerld,0.Productld,P.ProductName,P.Price

fromOrdersOINNERJOINProductsP

ON0.ProductId=P.Productld

2、要求使用T-SQL语言修改视图“ViewJDP"，输出以下字段:Customerld,Productld,

ProductName,Price,要求输出电视机商品信息。

USE天意购物

GO

ALTERVIEWView_0P

AS

SELECT0・Customer工d,0.Productld,P.ProductName,P.Price

fromOrders0INNERJOINProductsP

ON0.ProductId=P.Productld

WHEREProductName=,电视机,

3、要求使用T-SQL语言为视图“ViewjDP”进行重命名，新的视图名称为“View_OrdPro”。

USE天意购物

GO

SP_RENAMEView_0PzView_OrdPro

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

USE天意购物

GO

SELECT*FROMView_0rdPro

5、要求使用T-SQL语言删除视图"View_OrdPro\

USE天意购物

GO

DROPVIEWView_0rdPro

GO

知识巩固：

1、C2、C3、C4、A

1、要求在"Customers"表中的"Address”列上创建名为〃unique_Address〃

的唯一非聚集索引。

USE天意购物

CREATEUNIQUENONCLUSTERED

INDEXUnique_Address

ONCustomers(Address)

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

USE天意购物

GO

SP_HELPINDEXCustomers

3、要求使用T-SQL命令将〃天意购物〃数据库中Customers表所建立的索引

unique_Address重命名为unique_Addo

USE天意购物

GO

SP_RENAME'Customers.unique_Addressz,'unique_Addz

4、要求使用T・SQL命令重建Customers表所建立的索引unique_Addo

USE天意购物

GO

ALTERINDEXunique_Add

ONCustomers

REBUILD

GO

5、要求使用T-SQL命令删除Customers表所建立的索引unique_Add0

USE天意购物

GO

DROPINDEXCustomers.unique_Add

GO

知识巩固：

1>D2、D3>B4、聚集索引非聚集索引

项目五动手实践答案

任务一

动手实践

在''天意购物”数据库carts表中添加一个折扣字段(discountint),要求将客户号为'202000198'

的客户所购商品的折扣信息通过以下方式写入：商品数量3件的可以打7折，3件以上可以打5折，5件

以上打4折(使用case语句完成)。

DECLARE©QUAINT

SET@QUA=(SELECTSUM(Cquantity)FROMCartsWHERECustomerld=*2020001981)

UPDATECartsSETDiscount=

CASE

WHEN@QUA>5THEN4

WHEN@QUA>3THEN5

WHEN@QUA=3THEN7

END

WHERECustomerld='202000198,

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

jiSQLQueryl.tql-WIN-20210113；NX.7^S»»(WIN-20210113JNX\Adminittr«tcw(52))•-Mic2V..俣3劭(Ctrl+Q)

文件(F)"(E)®H(V)«9(0)或目⑻工B(T)BQ(W)

O-a••"mjawttiiWN)强国&而后乂用图9•0

耳天盒盟利•I>RfrOO✓益自回|尸跪好品园。，飞-♦♦-»:

图5-10执行结果

任务二

禺动手实践

(1)要求创建一个存储过程，在"天意购物"数据库carts表中添加一个折扣字段(discount

int),要求将客户号为‘202000198'的客户所购商品的折扣信息通过以下方式写入：

商品数量3件的可以打7折，3件以上可以打5折，5件以上打4折(使用if语句完成).

createprocedurepro_carts(Qcardvarchar(15))

as

DECLARE@QUAINT

SET@QUA=(SELECTSUM(Cquantity)FROMCartsWHERECustomerId=@card)

IF@QUA>5

UPDATECartsSETDiscount=4WHERECustomerId=@card

else

IF@QUA>3

UPDATECartsSETDiscount=5WHERECustomerId=@card

ELSE

IF@QUA=3

UPDATECartsSETDiscount=7WHERECustomerId=@card

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

SQLQuery7.sql-W...ministrator(53))*

createprocedurepro_carts©cardvarchar(15))

as

DECLARE©3UAINT

SET咙UA=(SELECTSUM(CQUANTITY)FROMCartsWHERECUSTOKERID=@card)

-IF®3UA5

UPDATECARTSSETDISCOUNT4WHERECUSTOMERIDScard

else

IF©QUA?3

UPDATECARTSSETDISCOUNT=5WHERECUSTOMERID=@card

ELSE

BIFCQUA-3

UPDATECARTSSETDISCOUNT=7WHERECUSTOMERID-@card

100%▼.：

南消息

命令已成功完成.

完成时闾:2021-01-14T19:24:30.4183798+08:00

100%

QWIN-20210113JNX(13.0RTM)WIN-20210113JNX\Admini...00:00:00。行

图5-29执行结果

(2)要求创建一个存储过程，在“天意购物"数据库carts表中添加一个折扣字段(discount

int),要求将客户号为'202000198'的客户所购商品的折扣信息通过以下方式写入：

商品数量3件的可以打7折，3件以上可以打5折，5件以上打4折(使用case语句完

成)。

ALTERPROCEDUREpro_carts(@cardvarchar(15))

AS

DECLARE@QUAINT

SETmQUA=(SELECTSUM(Cquantity)FROMCartsWHERECustomerId=0card)

UPDATECartsSETDiscount=

CASE

WHEN@QUA>5THEN4

WHEN@QUA>3THEN5

WHEN@QUA=3THEN7

END

WHERECustomer工d=@card

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

QSQLQuery7.sql-WIN-2O21O113JNXJ^S19WS(WIN-20210113)NX\Administrator(53))*-Micros...关速启司(Ctri+Q)P

文林㈤百⑻视图(V)查询＜Q)项目(P)8fD(W)WgD(H)

o•G­-HJ-ja新星查询(N)㈤w国帛0X印由2二比1a

7号贼J•|»M(x)y器同回|萨跪即同画勘M冬-♦♦-•通；

图5-30执行结果

任务三

.动手实践

使用T_SQL语言为表customers创建DML触发器名为trig_禁止插入，使得当向表中插入

数据时，显示该事务不能被处理，不能插入数据！1

方法一：

1、创建触发器：

CREATETRIGGERtrig_禁止插入1

ONCustomers

FORINSERT

AS

BEGIN

PRINT，该事务不能被处理，不能插入数据！，

ROLLBACKTRANSACTION

END

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

SQLQueryl^ql-W1N-2O21OH3JNX^B99V(WIN-202101ISJNXVMiministratoc(52»*-Microi...云子，。小6Q-口X

文件(F)g(E)»RB(V)面8(Q)凄目(P)Ifl(n®D(W)»RJ(H)

076♦；e94国Wr3ffiS(N)国苏'髭日畲区印曲*?•附-P

〉执行伽彩总圆片鸵呼用画0

石侬awr理舞SQLQueryl.sql-W...ministrator(52))**X

createTRIGGERtng_堂止插入I

ONCUStOIMCS

-i£W1N-20210113JNX(SQLServer13.0,1601.5人FORinsert

sI.数gAS

BEGIN

GBI装统数病片

PRIK7或事务不就襁处遵•不就住入砂枢！’

lHIJ5®S«5EROLLBACKTRANSA3TION

Ul■ReportServerEND

.•iaReportServerTempDB

m6租*

StJ53K岸关案网

国・表

田・极里

S■:外部费港

S一同义词

ffi.可装程也100%-*c=

.消息

:•:«ServiceBroker

出,存储"e0%板

S«安全性

完的用：2021-01-16109:46:10.0324877*08:00

SM

ffiI

aa

S■PolyBase

&IAlwaysOn耐用性

田0曾耍100%

OWIN-20210113JNX(13.0RTM)WIN-20210113JNX\Adrnini...00KXHX)Of?

om知1

图5-48创建触发器trig_禁止插入1

2、触发操作

INSERTCustomersVALUES('578102352李红'，'1310151310156789天津')

SELECT*FROMCustomers

执行上述代码结果和进行插入操作结果，如图5-49所示。

3eSQLQMMW-WIN-202101UJNX.^SStt(WIN-20210113/NX\Ad»wro«rato«(54))e-Micw.„3超5)(CM-QP

文件&)«H(E)«!B(v)mi(p)ixnBa(w)3fHi

o•H,-HJ-值新i由(N)⑨q国0却&S-i

In'"X'>'：«•I•s

图5-49执行结果

方法二:

1、创建触发器：

CREATETRIGGERtrig_禁止插入2

ONCustomers

INSTEADOFINSERT

AS

BEGIN

PRINT，该事务不能被处理，不能插入数据!

END

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

3?SQLQuerylAql-WIN-20210113)KXJtBIWB<WIN-20210113JNX\AdminijtritOf(S2)r-Micros...'Q-UX

文WF)«fi(E)®H(V)gQ)«@(P)Iflm«D(W)

o•y--H3»»««KN)Aiq&wm晶-0P

”由《弗•AMOOy器且回IrKtf3用四。m左-•♦-他；

图5-50创建触发器trig一禁止插入2

2、触发操作

INSERTCustomersVALUES('578102352李红'，'1310151310156789'天津')

SELECT*FROMCustomers

执行上述代码结果和进行插入操作结果，如图5-51所示。

SQ4.Qucry1.sql-WIN-20210113JNX.^gtW»(WlN-20210113JNX\Administrotor(5Z)),-Micros-礴后劲(Clri+Q)_P

文用F)郦WE)税曲明好(Q)项目(P)Iftm®C(W)

o•!3••eia声星I新建直甸(N)金会◎念，介品印由•的H-

7天E♦IA蛇