QL-Server-2016-數(shù)據(jù)庫應用實戰(zhàn)練習題題庫及答案(動手實踐)

1、QL Server 2016 數(shù)據(jù)庫應用實戰(zhàn)練習題題庫及答案（動手實踐）動手實踐與綜合實訓答案任務1-1動手實踐請繪出購物車表（Carts）的E-R圖。任務1-2動手實踐利用完整格式創(chuàng)建“天意購物2”數(shù)據(jù)庫，要求數(shù)據(jù)庫有兩個數(shù)據(jù)文件和兩個日志文件。具體要求見表1-8 。表1-8 “天意購物3”數(shù)據(jù)庫參數(shù)說明參 數(shù)參 數(shù) 值參 數(shù)參 數(shù) 值數(shù)據(jù)庫名稱天意購物2日志文件邏輯名天意購物2_log1主數(shù)據(jù)文件邏輯名天意購物2_data1日志文件物理名C:SQL天意購物2_LOG1.LDF主數(shù)據(jù)文件物理名C:SQL天意購物2_DATA1.MDF日志文件初始大小50MB主數(shù)據(jù)文件初始大小100MB日志文件

2、最大值100MB主數(shù)據(jù)文件最大值200 MB日志文件增長值10MB主數(shù)據(jù)文件增長值20 MB事務日志文件邏輯名天意購物2_log2數(shù)據(jù)文件邏輯名天意購物2_data2日志文件物理名C:SQL天意購物2_LOG2.LDF數(shù)據(jù)文件物理名C:SQL天意購物2_DATA2.NDF日志文件初始大小50MB數(shù)據(jù)文件初始大小20MB日志文件最大值100MB數(shù)據(jù)文件最大值UNLIMITED日志文件增長值10MB數(shù)據(jù)文件增長值10%在查詢窗口中輸入：CREATE DATABASE 天意購物2ON(NAME=天意購物2_DATA1, -主數(shù)據(jù)文件屬性設置FILENAME=C:SQL天意購物2_DATA1.MDF,

3、SIZE=100MB,MAXSIZE=200MB,FILEGROWTH=20MB),(NAME=天意購物2_DATA2, -次要數(shù)據(jù)文件屬性設置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.

4、LDF,SIZE=50MB,MAXSIZE=100MB,FILEGROWTH=10MB)圖1-30 創(chuàng)建兩個數(shù)據(jù)文件和日志文件的運行窗口任務1-3動手實踐將天意購物數(shù)據(jù)庫名稱改寫為TYGW。ALTER DATABASE 天意購物 MODIFY NAME=TYGW2、給TYGW數(shù)據(jù)庫添加一個日志文件：名稱：TYGW_log日志文件，存放在D盤根目錄下，初始大小5MB，文件最大值為無限制，文件增長值為5%。ALTER DATABASE TYGWADD LOG FILE(NAME=TYGW_log,FILENAME=D:TYGW_log.LDF,SIZE=5 MB,MAXSIZE=UNLIMITED

5、,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（購物車信息）的表結構創(chuàng)建。表結構信息如表2-4 和表2-5。表2-4 Orders（訂購信息）表字段名描述數(shù)據(jù)類型是否為空是否為主鍵CustomerID客戶編號char(9)否ProductID

6、商品編號Varchar(9)否OrderID訂單編號Varchar(10)否OrderDate訂單日期datatime否PaidDate付款日期datatime否Oquantity訂購數(shù)量Int否表2-5 Carts（購物車信息）表字段名描述數(shù)據(jù)類型是否為空是否為主鍵CustomerID 客戶編號char(9)否ProductID商品編號Varchar(9)否 CartID購物車編號Varchar(10)否 Cquantity商品數(shù)量Int否方法一：使用ssms方式：（1）Orders（訂購信息）表結構的創(chuàng)建步驟一：啟動SQL Server 2016中的SQL Server Managemen

7、t Studio工具，以Windows身份驗證或SQL Server身份驗證登錄。步驟二：在“對象資源管理器”中，展開“數(shù)據(jù)庫”列表，展開創(chuàng)建的天意購物數(shù)據(jù)庫，右單擊“表”，在彈出的快捷菜單中選擇“新建表”命令。步驟三：打開“表設計器”窗口，定義表的結構。顯示表基本屬性列名、數(shù)據(jù)類型和允許Null值。輸入完畢。步驟四：保存創(chuàng)建的表：單擊“保存”按鈕，彈出“選擇名稱”對話框，輸入新建的表名稱：Orders，即完成了數(shù)據(jù)表的創(chuàng)建。方法二：使用T-SQL方式： CREATE TABLE Orders ( CustomerId CHAR(9) NOT NULL,ProductId VARCHAR(9)

8、 NOT NULL,OrderId VARCHAR(10) NOT NULL,OrderDate DATETIME NOT NULL,PaidDate DATETIME NOT NULL,Oquantity INT NOT NULL )任務二動手實踐1、要求向“天意購物”數(shù)據(jù)庫中的Orders（訂購信息）表添加記錄。數(shù)據(jù)如表2-8所示。（請使用SSMS方式完成）表2-8 訂購信息表（Orders）客 戶 編 號商 品 編 號訂 單 編 號訂 單 日 期付 款 日 期商 品 數(shù) 量202000198293269110225678902016-1-262016-1-3015781023561738

9、89123331234562016-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-26264

10、9471323195117281787688012018-11-112018-11-113212345678351174235317896652017-9-42017-9-81步驟一：選擇“開始”“所有程序”“Microsoft SQL Server 2016”SQL Server Management Studio 命令，使用“Windows 身份驗證”建立連接，進入SQL Server Management Studio窗口（簡稱SSMS窗口）。步驟二：在“對象資源管理器”窗格中依次展開數(shù)據(jù)庫“天意購物”，選擇Orders表，右擊，在彈出的快捷菜單中選擇“編輯前200行”命令。步驟三：在相

11、應列分別輸入表中信息，然后按【Enter】鍵即可。步驟四：添加每條記錄都重復步驟三。步驟五：全部數(shù)據(jù)輸入完畢后，直接關閉編輯窗口即可。2、要求向“天意購物”數(shù)據(jù)庫中的Carts（購物車）表添加記錄。數(shù)據(jù)如表2-9 所示。（請使用T-SQL方式完成）表2-9 購物車信息表（Carts）客 戶 編 號商 品 編 號購物車編號商 品 數(shù) 量202000198293269110100011222020001981738890251000112157810235617388902520012343301119782121882122123450156781234561180415122312348221

12、234567812188212211223456673833825169669624145678486738338251645245251588272538863093228696616625995022142258847169669624139388893874874552159649732885070492995706926221480415964487INSERT Carts VALUES(202000198,293269110,1000112,2)INSERT Carts VALUES(202000198,173889025,1000112,1)INSERT Carts VALUES(

13、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(

14、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語句將“天意購物”數(shù)據(jù)庫中的訂購信息表（Orders）表更名為Orders1。在查詢分析器中運行以下命令：SP_RENAME Prod,Products項目三任務實踐標準答案任務一動手實踐1、使用三種修改列標題的

15、方式顯示Products表中的”ProductId ”,”Type ”, ”ProductName ”的信息。USE 天意購物SELECT ProductId 商品編號, Type AS 類型, 商品名稱= ProductName FROM Products2、查詢在數(shù)據(jù)庫天意購物中商品信息表 Products中查找食品類的商品，價格在100元以內的商品信息。查詢窗口中輸入命令如下：USE 天意購物SELECT * FROM Products WHERE Type=食品 AND Price0 AND Price=10 AND Price=50方法二:USE 天意購物SELECT * FROM

16、Products WHERE Type=圖書 AND Price BETWEEN 10 AND 504、查詢數(shù)據(jù)庫天意購物的商品信息表Products中咖啡價格不在100元到200元的商品信息。查詢窗口中輸入命令如下： USE 天意購物SELECT * FROM Products WHERE ProductName=咖啡 AND Price NOT BETWEEN 100 AND 200任務二動手實踐查詢顯示天意購物數(shù)據(jù)庫商品信息表Products的商品類型，商品名稱，商品價格，訂單表Orders付款時間等信息,要求商品類型包含“電”字，付款時間在2020-1-1以前的信息。USE 天意購物S

17、ELECT 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 執(zhí)行結果如圖5-10所示。圖5-10 執(zhí)行結果任務二動手實踐（1）要求創(chuàng)建一個存儲過程，在“天意購物”數(shù)據(jù)庫carts表中添加一個折扣字

18、段（discount int），要求將客戶號為202000198的客戶所購商品的折扣信息通過以下方式寫入：商品數(shù)量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 Car

19、ts SET Discount=5 WHERE CustomerId=cardELSE IF QUA=3UPDATE Carts SET Discount=7 WHERE CustomerId=card 執(zhí)行結果如圖5-29所示。圖5-29 執(zhí)行結果（2）要求創(chuàng)建一個存儲過程，在“天意購物”數(shù)據(jù)庫carts表中添加一個折扣字段（discount int），要求將客戶號為202000198的客戶所購商品的折扣信息通過以下方式寫入：商品數(shù)量3件的可以打7折，3件以上可以打5折，5件以上打4折（使用case語句完成）。ALTER PROCEDURE pro_carts(card varchar(15

20、)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執(zhí)行結果如圖5-30所示。圖5-30 執(zhí)行結果任務三動手實踐使用T_SQL語言為表 customers 創(chuàng)建DML觸發(fā)器名為trig_禁止插入，使得當向表中插入數(shù)據(jù)時，顯示該事務不能被處理，不能插入數(shù)據(jù)！。方法一：1、創(chuàng)

21、建觸發(fā)器：CREATE TRIGGER trig_禁止插入1ON CustomersFOR INSERTASBEGIN PRINT 該事務不能被處理，不能插入數(shù)據(jù)！ ROLLBACK TRANSACTIONEND執(zhí)行上述代碼結果如圖5-48所示。圖5-48 創(chuàng)建觸發(fā)器trig_禁止插入12、觸發(fā)操作INSERT Customers VALUES(578102352,李紅,131015,1310156789,天津)SELECT * FROM Customers執(zhí)行上述代碼結果和進行插入操作結果，如圖5-49所示。圖5-49 執(zhí)行結果方法二：1、創(chuàng)建觸發(fā)器：CREATE TRIGGER trig_

22、禁止插入2ON CustomersINSTEAD OF INSERTASBEGIN PRINT 該事務不能被處理，不能插入數(shù)據(jù)！END執(zhí)行上述代碼結果如圖5-50所示。圖5-50 創(chuàng)建觸發(fā)器trig_禁止插入22、觸發(fā)操作INSERT Customers VALUES(578102352,李紅,131015,1310156789,天津)SELECT * FROM Customers執(zhí)行上述代碼結果和進行插入操作結果，如圖5-51所示。圖5-51 執(zhí)行結果任務一動手實踐1、要求使用T-SQL語言創(chuàng)建SQL Server身份驗證的登錄賬戶lisa，密碼為123，默認數(shù)據(jù)庫為master，默認語言為

23、簡體中文。之后再使用T-SQL語言刪除該用戶。EXEC SP_ADDLOGIN lisa,123,master,Simplified ChineseEXEC SP_DROPLOGIN lisa命令執(zhí)行結果如圖6-40所示圖 6-40 創(chuàng)建和刪除SQL Server身份驗證的登錄賬戶命令執(zhí)行結果2、要求使用T-SQL語言給登錄賬戶lisa添加一個天意購物據(jù)庫的同名數(shù)據(jù)庫用戶賬戶lisa，然后用T-SQL語言從數(shù)據(jù)庫“天意購物”中查看用戶lisa，最后從當前數(shù)據(jù)庫“天意購物”中刪除用戶lisa。創(chuàng)建并查看用戶的命令如下，執(zhí)行結果如圖6-41所示EXEC SP_ADDLOGIN lisa,123,天

24、意購物,Simplified ChineseUSE 天意購物 EXEC SP_GRANTDBACCESS lisa,lisaEXEC SP_HELPUSER lisa圖6-41 創(chuàng)建和查看數(shù)據(jù)庫用戶刪除用戶的命令如下，執(zhí)行結果如圖6-42所示USE 天意購物 EXEC SP_REVOKEDBACCESS lisa圖6-42 刪除數(shù)據(jù)庫用戶3、要求使用T-SQL語言將lisa用戶添加到“天意購物”數(shù)據(jù)庫的db_accessadmin角色中，然后刪除該角色。創(chuàng)建lisa用戶，將其添加到db_accessadmin角色的命令如下，執(zhí)行結果如圖6-43所示USE 天意購物 EXEC SP_GRANTD