三、設計與應用題

　　45某書店采用了SQL Server 2008數(shù)據庫管理系統(tǒng)，該書店有一個需求，需要統(tǒng)計指定年份中每一本書的銷售總額，例如：查詢2012年所有書的銷售總額。

　　已知圖書結構如下：

　　圖書表(書號BOOK_ID，書名BOOK_NAME，單價BOOK_PRICE)

　　銷售表(書號BOOK_ID，銷售時間SALE_TIME，銷售數(shù)量SALE_NUM)。

　　假設單價和銷售數(shù)量均為int型，書號和書名均為varchar(50)類型，銷售時問為datetime型。請給出滿足如下要求的多語句表值函數(shù)，該函數(shù)統(tǒng)計指定年份中每本書的銷售總額。(10分)設函數(shù)名為：BOOK_PROFIT(@year　int)，函數(shù)的返回結果格式如下：

　　書號銷售總額

　　B001　60000

　　A004　50000

　　參考解析：

　　【解題思路】

　　采用JOIN聯(lián)合查詢，先用WHERE條件查出符合銷售時間=@year的記錄，再將找出的記錄和圖書表合并，并采用單價*銷售數(shù)量計算出聯(lián)合查詢的表數(shù)據，最后根據GROUP BY統(tǒng)計每種書的銷售價格總和。

　　【參考答案】

　　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銷售數(shù)量)

　　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某商場商品經營管理系統(tǒng)使用SQL Server 2008數(shù)據庫管理系統(tǒng)，此系統(tǒng)上線運行1年后，業(yè)務人員使用某統(tǒng)計功能(此功能每月使用一次)時發(fā)現(xiàn)速度很慢。該統(tǒng)計功能主要執(zhí)行的SQL語句如下：

　　SELECT商品號，SUM(銷售數(shù)量*銷售價格)銷售額

　　FROM銷售明細

　　GROUP BY商品號;

　　該銷售明細表的建表語句如下：

　　CREATE TABLE銷售明細(

　　序列號intIDENTITY(1，1)NOT NULL，

　　商品號intNOT NULL，

　　銷售日期datetime NULL，

　　銷售數(shù)量intNOT NULL，

　　銷售價格intNOT NULL

　　);

　　并在銷售明細表上建有如下索引：

　　CREATE index ix_銷售明細_商品號on銷售明細(商品號);

　　某技術人員提出通過執(zhí)行下述語句以提高此查詢的運行效率：

　　CREATE VIEW商品銷售額視圖

　　WITH SCHEMABINDING

　　AS

　　SELECT商品號，SUM(銷售數(shù)量*銷售價格)銷售額，

　　COUNT_BIG(*)cnt

　　FROM db0.銷售明細

　　GROUP BY商品號;

　　CREATE UNIQUE CLUSTERED INDEX ix_商品銷售額

　　ON商品銷售額視圖(商品號);

　　(1)請分析該技術人員給出的語句功能以及對原有查詢語句的性能影響，并給出原因。

　　(2)此商場的銷售量很大，每天有大量數(shù)據插入到銷售明細表中。請從數(shù)據庫整體性能角度分析，此技術人員提出的優(yōu)化方法是否合適，并給出原因。

　　參考解析：

　　(1)【解題思路】

　　該技術人員使用了帶有索引的視圖，將所關心的數(shù)據(商品號，銷售額，該商品號在表中出現(xiàn)的次數(shù))從銷售明細表中提取出來建立視圖，并對該視圖建立按商品號排序的聚簇索引，這樣大大減少了在搜索不同商品的銷售額時調用的數(shù)據表的規(guī)模，從而提高了查詢效率。由于表的數(shù)據規(guī)模很大，建立該視圖后，同一種商品不會多次出現(xiàn)在表中，而是通過一個計數(shù)變量cnt表示，即在檢索時大大減少了檢索規(guī)模。創(chuàng)建索引時，UNIQUE關鍵字表明此索引的每一個索引值只對應唯一的數(shù)據記錄。CLUSTER表示要建立的索引是聚簇索引(所謂聚簇索引是指索引項的順序與表中記錄的物理順序一致的索引組織)。

　　【參考答案】

　　語句功能：建立包含所關心數(shù)據(商品號，銷售額，該商品號在表中出現(xiàn)的次數(shù))的帶索引的視圖，并建立按商品號對應銷售額UNIQUE聚簇排序的索引，從而大大縮小了查詢語句的查詢范圍，提高了查詢效率。

　　原因：視圖中將問接相關的屬性列(序列號，銷售日期，商品號，銷售數(shù)量，銷售價格)轉換成了目標屬性列，減少了搜索空問，同時建立UNIQUE CLUSTERED索引，使查詢商品號的數(shù)據記錄唯一，因此降低了搜索范圍，提高了搜索效率。

　　(2)【解題思路】

　　由于視圖是不實際存儲數(shù)據的虛表，因此對視圖的更新最終要轉換為對基本表的更新。而用戶通過視圖對數(shù)據進行增加、刪除、修改時，有意或無意地對不屬于視圖范圍內的基本表數(shù)據進行操作，會破壞數(shù)據的一致性。而且視圖中的數(shù)據本身就是冗余的，每次對表進行修改時，同時也要對相應的視圖進行修改，這大大增加了系統(tǒng)的負擔。

　　【參考答案】不合適，每天大量的插入操作使得在修改表的同時也要對視圖進行修改，增加了系統(tǒng)的負擔，然而該統(tǒng)計功能一個月才用一次，這樣導致系統(tǒng)的利用率也較為低下。

　　相關推薦：

　　2015計算機三級《數(shù)據庫》上機試題及答案匯總

　　2015年計算機三級《網絡技術》高分突破試卷匯總

　　2015計算機三級《數(shù)據庫技術》練習題及答案匯總

　　2015年9月計算機等級考試各科目考前必做試題