2015計算機三級《數據庫技術》考前沖刺試卷(4)
三、設計與應用題
45某書店采用了SQL Server 2008數據庫管理系統，該書店有一個需求，需要統計指定年份中每一本書的銷售總額，例如：查詢2012年所有書的銷售總額。
已知圖書結構如下：
圖書表(書號BOOK_ID，書名BOOK_NAME，單價BOOK_PRICE)
銷售表(書號BOOK_ID，銷售時間SALE_TIME，銷售數量SALE_NUM)。
假設單價和銷售數量均為int型，書號和書名均為varchar(50)類型，銷售時問為datetime型。請給出滿足如下要求的多語句表值函數，該函數統計指定年份中每本書的銷售總額。(10分)設函數名為：BOOK_PROFIT(@year int)，函數的返回結果格式如下：
書號銷售總額
B001 60000
A004 50000
參考解析：
【解題思路】
采用JOIN聯合查詢，先用WHERE條件查出符合銷售時間=@year的記錄，再將找出的記錄和圖書表合并，并采用單價*銷售數量計算出聯合查詢的表數據，最后根據GROUP BY統計每種書的銷售價格總和。
【參考答案】
CREATE FUNCTION BOOK_PROFIT(@year int)
RETURNS@f_BOOK_PROFIT table(
書號varchar(50)，
銷售總額int)
AS
BEGIN
INSERT INTO@f_BOOK_PROFIT
SELECT a書號，SUM(a單價*b銷售數量)
FROM圖書表a JOIN銷售表b ON a書號=b書號
WHERE year(b銷售時間)=@year
GROUP BY a.書號
RETURN
END
或者
CREATE FUNCTION BOOK_PROFIT(@year int)
RETURNS@f_BOOK_PROFlT table(
BOOK_ID varchar(50)，
PROFIT int)
AS
BEGIN
INSERT INTO@f_BOOK_PROFIT
SELECT a.BOOK_ID，SUM(a.BOOK_PRICE*b,SALE_NUM)
FROM BOOK a JOIN SALE b ON a.BOOK ID=b.B00K ID
WHERE year(bSALE_TIME)=@year
GROUP BY a.BOOK_ID
RETURN
END
46某商場商品經營管理系統使用SQL Server 2008數據庫管理系統，此系統上線運行1年后，業務人員使用某統計功能(此功能每月使用一次)時發現速度很慢。該統計功能主要執行的SQL語句如下：
SELECT商品號，SUM(銷售數量*銷售價格)銷售額
FROM銷售明細
GROUP BY商品號;
該銷售明細表的建表語句如下：
CREATE TABLE銷售明細(
序列號intIDENTITY(1，1)NOT NULL，
商品號intNOT NULL，
銷售日期datetime NULL，
銷售數量intNOT NULL，
銷售價格intNOT NULL
);
并在銷售明細表上建有如下索引：
CREATE index ix_銷售明細_商品號on銷售明細(商品號);
某技術人員提出通過執行下述語句以提高此查詢的運行效率：
CREATE VIEW商品銷售額視圖
WITH SCHEMABINDING
AS
SELECT商品號，SUM(銷售數量*銷售價格)銷售額，
COUNT_BIG(*)cnt
FROM db0.銷售明細
GROUP BY商品號;
CREATE UNIQUE CLUSTERED INDEX ix_商品銷售額
ON商品銷售額視圖(商品號);
(1)請分析該技術人員給出的語句功能以及對原有查詢語句的性能影響，并給出原因。
(2)此商場的銷售量很大，每天有大量數據插入到銷售明細表中。請從數據庫整體性能角度分析，此技術人員提出的優化方法是否合適，并給出原因。
參考解析：
(1)【解題思路】
該技術人員使用了帶有索引的視圖，將所關心的數據(商品號，銷售額，該商品號在表中出現的次數)從銷售明細表中提取出來建立視圖，并對該視圖建立按商品號排序的聚簇索引，這樣大大減少了在搜索不同商品的銷售額時調用的數據表的規模，從而提高了查詢效率。由于表的數據規模很大，建立該視圖后，同一種商品不會多次出現在表中，而是通過一個計數變量cnt表示，即在檢索時大大減少了檢索規模。創建索引時，UNIQUE關鍵字表明此索引的每一個索引值只對應唯一的數據記錄。CLUSTER表示要建立的索引是聚簇索引(所謂聚簇索引是指索引項的順序與表中記錄的物理順序一致的索引組織)。
【參考答案】
語句功能：建立包含所關心數據(商品號，銷售額，該商品號在表中出現的次數)的帶索引的視圖，并建立按商品號對應銷售額UNIQUE聚簇排序的索引，從而大大縮小了查詢語句的查詢范圍，提高了查詢效率。
原因：視圖問接相關的屬性列(序列號，銷售日期，商品號，銷售數量，銷售價格)轉換成了目標屬性列，減少了搜索空問，同時建立UNIQUE CLUSTERED索引，使查詢商品號的數據記錄唯一，因此降低了搜索范圍，提高了搜索效率。
(2)【解題思路】
由于視圖是不實際存儲數據的虛表，因此對視圖的更新最終要轉換為對基本表的更新。而用戶通過視圖對數據進行增加、刪除、修改時，有意或無意地對不屬于視圖范圍內的基本表數據進行操作，會破壞數據的一致性。而且視圖中的數據本身就是冗余的，每次對表進行修改時，同時也要對相應的視圖進行修改，這大大增加了系統的負擔。
【參考答案】不合適，每天大量的插入操作使得在修改表的同時也要對視圖進行修改，增加了系統的負擔，然而該統計功能一個月才用一次，這樣導致系統的利用率也較為低下。
47設有圖書管理數據庫，包含三張表：
圖書明細表(圖書編號，圖書類別，圖書名稱，作者，出版社，出版日期，定價);
讀者表(借書證號，姓名，系別，辦證日期);
借出信息表(借出編號，借書證號，圖書編號，借書日期);
完成下列操作：
定義一個多語句表值函數，用于查詢學生借書情況，只需提供參數：借書證號，就可以通過調用函數返回此學生的借書情況，若有借書籍，則返回所借書籍的編號、書籍名稱、定價和借書日期;若沒有在借書籍，則返回記錄為空。設函數名為：f_BorrowBook(@jszh char(20))。
參考解析：
【解題思路】
SQL Server 2008多語句表值函數的格式為：
CREATE FUNCTION[schema_name.]function_name
([{@parameter_name[As][type_schema_name.]parameter data_type
[=default]}
[….n]
]
)
RETURNS@return_variable TABLE
[AS]
BEGIN
function_body
RETURN
END
[;]
：：=
({I}
[table_constraint][，...n])
采用三表聯合查詢，通過傳入的借書證號查出借出信息表中的圖書編號，然后查出圖書明細表的信息。
【參考答案】
CREATE FUNCTION BorrowBook(@jszh char(20))
RETURNS@jsqkb TABLE(書籍編號char(20)，書籍名稱char(50)，定價float，借書日期datetime)
AS
BEGIN
INSERT@jsqkb
SELECT圖書明細表.圖書編號，圖書名稱，定價，借出信息表.借書日期FROM借出信息表，圖書明細表
WHERE借出信息表.圖書編號=圖書明細表.圖書編號AND借出信息表.借書證號=@jszh
RETURN
END