QL Server 2016 數(shù)據(jù)庫應(yīng)用實(shí)戰(zhàn)練習(xí)題題庫及答案（動(dòng)手實(shí)踐）

QLServer2016數(shù)據(jù)庫應(yīng)用實(shí)戰(zhàn)練習(xí)題題庫及答案（動(dòng)手實(shí)踐）

動(dòng)手實(shí)踐與綜合實(shí)訓(xùn)答案

任務(wù)1-1動(dòng)手實(shí)踐

請繪出購物車表（Carts）的E-R圖。

任務(wù)1-2動(dòng)手實(shí)踐

利用完整格式創(chuàng)建"天意購物2"數(shù)據(jù)庫，要求數(shù)據(jù)庫有兩個(gè)數(shù)據(jù)文件和兩個(gè)日志文件。

具體要求見表1-8。

表1-8“天意購物3"數(shù)據(jù)庫參數(shù)說明

參數(shù)參數(shù)值參數(shù)參數(shù)值

數(shù)據(jù)庫名稱天意購物2日志文件邏輯名天意購物2,logl

C:\SQL\天意購物

主數(shù)據(jù)文件邏輯名天意購物2,datal日志文件物理名

2_LOG1.LDF

主數(shù)據(jù)文件物理名C:\SQL\天意購物2.DATA1.MDF日志文件初始大小50MB

主數(shù)據(jù)文件初始大小100MB日志文件最大值100MB

主數(shù)據(jù)文件最大值200MB日志文件增長值10MB

事務(wù)日志文件邏輯

主數(shù)據(jù)文件增長值20MB天意購物2,log2

名

C:\SQL\天意購物

數(shù)據(jù)文件邏輯名天意購物2_data2日志文件物理名

2_LOG2.LDF

數(shù)據(jù)文件物理名C:\SQL\天意購物2_DATA2.NDF日志文件初始大小50MB

數(shù)據(jù)文件初始大小20MB日志文件最大值100MB

數(shù)據(jù)文件最大值UNLIMITED日志文件增長值10MB

數(shù)據(jù)文件增長值10%

在查詢窗口中輸入：

CREATEDATABASE天意購物2

ON

(NAME=，天意購物2_DATA1',--主數(shù)據(jù)文件屬性設(shè)置

FILENAME=t:\SQL\天意購物2_DATA1.MDF,,

SIZE=100MB,

MAXSIZE=200MB,

FILEGROWTH=20MB

),

(NAME=，天意購物2_DATA2',一次要數(shù)據(jù)文件屬性設(shè)置

FILENAME=，C:\SQL\天意購物2_DATA2.NDF',

SIZE=20MB,

MAXSIZE=UNLIMITED,

FILEGROWTH=10%

)

LOGON

(NAME='天意購物2_LOG1',-日志文件屬性設(shè)置

FILENAME=，C:\SQL\天意購物2_LOG1.LDF',

SIZE=50MB,

MAXSIZE=100MB,

FILEGROWTH=10MB

),

(NAME=，天意購物2_LOG2',?日志文件屬性設(shè)置

FILENAME=，C:\SQL\天意購物2_LOG2.LDF',

SIZE=50MB,

MAXSIZE=100MB,

FILEGROWTH=10MB)

32SQLQueiy2.sql-PC-20210120ZVSU.^SI9?1(PC-20210120ZVSU\Admini$tr...快速足動(dòng)(Ctrk?_P-

文件但費(fèi)田(?視圉QD項(xiàng)目(E)IS?eC(W)Sfe(H)

O?J3?-eia廣是MSiS?醫(yī)Iwa血?&■-0-戶二

-:，抗行區(qū)：0I'':，」,-I一，Y

圖1-30創(chuàng)建兩個(gè)數(shù)據(jù)文件和日志文件的運(yùn)行窗口

任務(wù)1-3動(dòng)手實(shí)踐

1、將天意購物數(shù)據(jù)庫名稱改寫為TYGW。

ALTERDATABASE天意購物MODIFYNAMETYGW

2、給TYGW數(shù)據(jù)庫添加一個(gè)日志文件：

名稱：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）

項(xiàng)目二

任務(wù)一

_動(dòng)手實(shí)踐

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

Orders（訂購信息）表、Carts（購物車信息）的表結(jié)構(gòu)創(chuàng)建。表結(jié)構(gòu)信息如表2-4和表

2-5。

表2-4Orders（訂購信息）表

表2-5Carts（購物車信息）表

方法一：使用ssms方式：

（1）Orders（訂購信息）表結(jié)構(gòu)的創(chuàng)建

步驟一啟動(dòng)SQLServer2016中的SQLServerManagementStudio工具以Windows

身份驗(yàn)證或SQLServer身份驗(yàn)證登錄。

字段名描述數(shù)據(jù)類型是否為空是否為主鍵

CustomerlD客戶編號(hào)char(9)否

ProductID商品編號(hào)Varchar(9)否

OrderlD訂單編號(hào)Varchar(lO)否

OrderDate訂單日期datatime否

PaidDate付款日期datatime否

Oquantity訂購數(shù)量Int否

步驟二：在"對象資源管理器"中，展開"數(shù)據(jù)庫”列表，展開創(chuàng)建的天意購物數(shù)據(jù)庫，

字段名描述數(shù)據(jù)類型是否為空是否為主鍵

CustomerlD客戶編號(hào)char(9)否

ProductID商品編號(hào)Varchar(9)否

CartID購物車編號(hào)Varchar(lO)否

Cquantity商品數(shù)量Int否

右單擊"表"，在彈出的快捷菜單中選擇“新建表"命令。

步驟三：打開"表設(shè)計(jì)器"窗口，定義表的結(jié)構(gòu)。顯示表基本屬性一一列名、數(shù)據(jù)

類型和允許Null值。輸入完畢。

步驟四：保存創(chuàng)建的表：單擊“保存"按鈕，彈出"選擇名稱"對話框，輸入新建的表名

稱：Orders,即完成了數(shù)據(jù)表的創(chuàng)建。

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

CREATETABLEOrders

CustomerldCHAR(9)NOTNULL,

ProductldVARCHAR(9)NOTNULL,

OrderIdVARCIIAR(IO)NOTNULL,

OrderDateDATETIMENOTNULL,

PaidDateDATETIMENOTNULL,

OquantityINTNOTNULL

)

任務(wù)二

動(dòng)手實(shí)踐

____________

1、要求向"天意購物”數(shù)據(jù)庫中的。rders(訂購信息)表添加記錄。數(shù)據(jù)如表2-8所示。

(請使用SSMS方式完成)

表2-8訂購信息表（Orders）

客戶編號(hào)商品編號(hào)訂單編號(hào)訂單日期付款日期商品數(shù)量

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身份驗(yàn)證”建立連接，進(jìn)入SQLServer

ManagementStudio窗口（簡稱SSMS窗口）。

步驟二：在“對象資源管理器”窗格中依次展開數(shù)據(jù)庫“天意購物”，選擇Orders表,

右擊，在彈出的快捷菜單中選擇“編輯前200行”命令。

步驟三：在相應(yīng)列分別輸入表中信息，然后按【Enter】鍵即可。

步驟四：添加每條記錄都重復(fù)步驟三。

步驟五：全部數(shù)據(jù)輸入完畢后，直接關(guān)閉編輯窗口即可。

2、要求向“天意購物"數(shù)據(jù)庫中的Carts（購物車）表添加記錄。數(shù)據(jù)如表2-9所示。

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

表2-9購物車信息表（Carts）

客戶編號(hào)商品編號(hào)購物車編號(hào)商品數(shù)量

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)

任務(wù)三

動(dòng)手實(shí)踐

使用T-SQL語句將“天意購物”數(shù)據(jù)庫中的訂購信息表（Orders）表更名為Orders'

在查詢分析器中運(yùn)行以下命令:

SP_RENAMEProd,Products

項(xiàng)目三任務(wù)實(shí)踐標(biāo)準(zhǔn)答案

任務(wù)一動(dòng)手實(shí)踐

1、使用三種修改列標(biāo)題的方式顯示‘Products'表中的"ProductldType

ProductName”的信息。

USE天意購物

SELECTProductld'商品編號(hào)'，

TypeAS類型，商品名稱=ProductName

FROMProducts

2、查詢在數(shù)據(jù)庫天意購物中商品信息表Products中查找，食品，類的商品，價(jià)格在100

元以內(nèi)的商品信息。查詢窗口中輸入命令如下：

USE天意購物

SELECT*FROMProducts

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

3、查詢數(shù)據(jù)庫天意購物的商品信息表Products中圖書價(jià)格在10元到50元的商品信息。

查詢窗口中輸入命令如下：

方法一：

USE天意購物

SELECT*FROMProducts

WHEREType='圖書，ANDPrice>=10ANDPrice<=50

方法二：

USE天意購物

SELECT*FROMProducts

WHEREType=，圖書，ANDPriceBETWEEN10AND50

4、查詢數(shù)據(jù)庫天意購物的商品信息表Products中咖啡價(jià)格不在100元到200元的商品

信息。查詢窗口中輸入命令如下：

USE天意購物

SELECT*FROMProducts

WHEREProductName='咖啡'

ANDPriceNOTBETWEEN100AND200

任務(wù)二動(dòng)手實(shí)踐

查詢顯示天意購物數(shù)據(jù)庫商品信息表Products的商品類型，商品名稱，商品價(jià)格，訂單

表。rders付款時(shí)間等信息，要求商品類型包含"電"字，付款時(shí)間在2020-1-1以前的信息。

USE天意購物

SELECTP.Type'商品類型'，P.ProductName'商品名稱

P.Price'商品價(jià)格'，0.PaidDate'付款時(shí)間'

FROMProductsPINNERJOINOrders0ONP.ProductId=0.Productld

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

項(xiàng)目4視圖與索引

動(dòng)手實(shí)踐：

1、要求使用T-SQL語言創(chuàng)建視圖，名稱為"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,要求輸出電視機(jī)商品信息。

USE天意購物

GO

ALTERVIEWView_0P

AS

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

fromOrders0INNERJOINProductsP

ON0.ProductId=P.Productld

WHEREProductName=,電視機(jī),

3、要求使用T-SQL語言為視圖“ViewjDP”進(jìn)行重命名，新的視圖名稱為“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

知識(shí)鞏固：

1、C2、C3、C4、A

1、要求在"Customers"表中的"Address”列上創(chuàng)建名為〃unique_Address〃

的唯一非聚集索引。

USE天意購物

CREATEUNIQUENONCLUSTERED

INDEXUnique_Address

ONCustomers(Address)

2、要求使用T-SQL命令查看〃天意購物”數(shù)據(jù)庫中Customers表所建立的索引。

USE天意購物

GO

SP_HELPINDEXCustomers

3、要求使用T-SQL命令將〃天意購物〃數(shù)據(jù)庫中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

知識(shí)鞏固：

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

項(xiàng)目五動(dòng)手實(shí)踐答案

任務(wù)一

動(dòng)手實(shí)踐

在''天意購物”數(shù)據(jù)庫carts表中添加一個(gè)折扣字段(discountint),要求將客戶號(hào)為'202000198'

的客戶所購商品的折扣信息通過以下方式寫入：商品數(shù)量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,

執(zhí)行結(jié)果如圖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)強(qiáng)國&而后乂用圖9?0

耳天盒盟利?I>RfrOO?益自回|尸跪好品園。，飛-??-?:

圖5-10執(zhí)行結(jié)果

任務(wù)二

禺動(dòng)手實(shí)踐

(1)要求創(chuàng)建一個(gè)存儲(chǔ)過程，在"天意購物"數(shù)據(jù)庫carts表中添加一個(gè)折扣字段(discount

int),要求將客戶號(hào)為‘202000198'的客戶所購商品的折扣信息通過以下方式寫入：

商品數(shù)量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

執(zhí)行結(jié)果如圖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%▼.：

南消息

命令已成功完成.

完成時(shí)閭:2021-01-14T19:24:30.4183798+08:00

100%

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

圖5-29執(zhí)行結(jié)果

(2)要求創(chuàng)建一個(gè)存儲(chǔ)過程，在“天意購物"數(shù)據(jù)庫carts表中添加一個(gè)折扣字段(discount

int),要求將客戶號(hào)為'202000198'的客戶所購商品的折扣信息通過以下方式寫入：

商品數(shù)量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

執(zhí)行結(jié)果如圖5-30所示。

QSQLQuery7.sql-WIN-2O21O113JNXJ^S19WS(WIN-20210113)NX\Administrator(53))*-Micros...關(guān)速啟司(Ctri+Q)P

文林㈤百⑻視圖(V)查詢＜Q)項(xiàng)目(P)8fD(W)WgD(H)

o?G--HJ-ja新星查詢(N)㈤w國帛0X印由2二比1a

7號(hào)賊J?|?M(x)y器同回|薩跪即同畫勘M冬-??-?通；

圖5-30執(zhí)行結(jié)果

任務(wù)三

.動(dòng)手實(shí)踐

使用T_SQL語言為表customers創(chuàng)建DML觸發(fā)器名為trig_禁止插入，使得當(dāng)向表中插入

數(shù)據(jù)時(shí)，顯示該事務(wù)不能被處理，不能插入數(shù)據(jù)！1

方法一：

1、創(chuàng)建觸發(fā)器：

CREATETRIGGERtrig_禁止插入1

ONCustomers

FORINSERT

AS

BEGIN

PRINT，該事務(wù)不能被處理，不能插入數(shù)據(jù)！，

ROLLBACKTRANSACTION

END

執(zhí)行上述代碼結(jié)果如圖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)國蘇'髭日畬區(qū)印曲*??附-P

〉執(zhí)行伽彩總圓片鴕呼用畫0

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

createTRIGGERtng_堂止插入I

ONCUStOIMCS

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

sI.數(shù)gAS

BEGIN

GBI裝統(tǒng)數(shù)病片

PRIK7或事務(wù)不就襁處遵?不就住入砂樞！’

lHIJ5?S?5EROLLBACKTRANSA3TION

Ul■ReportServerEND

.?iaReportServerTempDB

m6租*

StJ53K岸關(guān)案網(wǎng)

國?表

田?極里

S■:外部費(fèi)港

S一同義詞

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

.消息

:?:?ServiceBroker

出,存儲(chǔ)"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創(chuàng)建觸發(fā)器trig_禁止插入1

2、觸發(fā)操作

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

SELECT*FROMCustomers

執(zhí)行上述代碼結(jié)果和進(jìn)行插入操作結(jié)果，如圖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執(zhí)行結(jié)果

方法二:

1、創(chuàng)建觸發(fā)器：

CREATETRIGGERtrig_禁止插入2

ONCustomers

INSTEADOFINSERT

AS

BEGIN

PRINT，該事務(wù)不能被處理，不能插入數(shù)據(jù)!

END

執(zhí)行上述代碼結(jié)果如圖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創(chuàng)建觸發(fā)器trig一禁止插入2

2、觸發(fā)操作

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

SELECT*FROMCustomers

執(zhí)行上述代碼結(jié)果和進(jìn)行插入操作結(jié)果，如圖5-51所示。

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

文用F)酈WE)稅曲明好(Q)項(xiàng)目(P)Iftm?C(W)

o?!3??eia聲星I新建直甸(N)金會(huì)◎念，介品印由?的H-

7天E?IA蛇