第-10章-SQL高級應用

1、精選優(yōu)質文檔-傾情為你奉上第 10章CHAPTER 10SQL高級應用練習題10參考答案1數(shù)據檢索時使用COMPUTE和COMPUTE BY產生的結果有何不同？答 使用COMPUTE子句和COMPUTE BY子句都能既瀏覽明細數(shù)據，又看到統(tǒng)計的結果。只使用COMPUTE子句時，其查詢的結果類似于總計；而使用COMPUTE BY子句時，其查詢的結果將為帶具體內容的分類進行統(tǒng)計。2進行連接查詢時應注意什么？答 連接查詢是指以指定表中的某個列或某些列作為連接條件，從兩個或更多的表中查詢關聯(lián)數(shù)據的查詢。進行連接查詢時應注意以下幾點： 一般而言，基于主鍵和外鍵指定查詢條件，連接條件可使用“主鍵=外鍵”。

2、 如果一個表有復合關鍵字，在連接表時，必須引用整個關鍵字。 應盡可能限制連接語句中表的數(shù)目，連接的表越多，查詢處理的時間越長。 對于連接表的兩個列應有相同或類似的數(shù)據類型。 不要使用空值作為連接條件，因為空值計算不會和其他任何值相等。3什么是交叉連接？答 交叉連接是兩個表的笛卡爾積，即兩個表的記錄進行交叉組合。4內連接、外連接有什么區(qū)別？答 內連接是從結果中刪除與其他被連接表中沒有匹配行的所有行，因此內連接可能會丟失信息。外連接會把內連接中刪除原表中的一些行保留下來，保留哪些行由外連接的類型決定。5外連接分為左外連接、右外連接和全外連接，它們有什么區(qū)別？答 左外連接從結果中保留第一個表的所有行

3、，但只包含第二個表中與第一個表匹配的行，第二個表相應的空行被放入NULL值。右外連接從結果中保留第二個表的所有行，但只包含第一個表中與第二個表匹配的行，第一個表相應的空行被放入NULL值。全外連接會把兩個表所有行都顯示在結果中，并盡可能多地匹配數(shù)據和連接條件。6什么是事務？事務的特點是什么？答 事務是指一個操作序列，這些操作序列要么都被執(zhí)行，要么都不被執(zhí)行，它是一個不可分割的工作單元。事務中任何一個語句執(zhí)行時出錯，系統(tǒng)都會返回到事務開始前的狀態(tài)。事務是并發(fā)控制的基本單元，是數(shù)據庫維護數(shù)據一致性的單位。在每個事務結束時，都能保持數(shù)據一致性。7對事務的管理包括哪幾方面？答 在SQL Server中

4、，對事務的管理包含3個方面。 事務控制語句：控制事務執(zhí)行的語句。包括將一系列操作定義為一個工作單元來處理。 鎖機制：封鎖正被一個事務修改的數(shù)據，防止其他用戶訪問到“不一致”的數(shù)據。 事務日志：使事務具有可恢復性。8事務中能否包含CREATE DATABASE語句？答 事務中不能包含CREATE DATABASE語句。9簡述事務保存點的概念。答 保存點提供了一種機制，用于回滾部分事務?？梢允褂?SAVE TRANSACTION savepoint_name語句創(chuàng)建一個保存點，然后再執(zhí)行ROLLBACK TRANSACTION savepoint_name 語句回滾到該保存點，從而無須回滾到事務的

5、開始。在不可能發(fā)生錯誤的情況下，保存點很有用。在很少出現(xiàn)錯誤的情況下使用保存點回滾部分事務，比讓每個事務在更新之前測試更新的有效性更為有效。更新和回滾操作代價很大，因此只有在遇到錯誤的可能性很小，而且預先檢查更新的有效性的代價相對很高的情況下，使用保存點才會非常有效。10在應用程序中如何控制事務？答 應用程序主要通過指定事務啟動和結束的時間來控制事務。主要使用Transact-SQL語句。系統(tǒng)還必須能夠正確處理那些在事務完成之前便終止事務的錯誤。事務是在連接層進行管理。當事務在一個連接上啟動時，在該連接上執(zhí)行的所有的T-SQL語句在該事務結束之前都是該事務的一部分。（1）啟動事務在SQL Se

6、rver中，可以按顯式、自動提交或隱性模式啟動事務。 顯式事務：通過發(fā)出BEGIN TRANSACTION語句顯式啟動事務。 自動提交事務：這是SQL Server的默認模式。每個單獨的T-SQL語句都在其完成后提交，不必指定任何語句控制事務。 隱性事務：通過T-SQL SET IMPLICIT_TRANSACTIONS ON語句，將隱性事務模式設置為打開，下一個語句自動啟動一個新事務，當該事務完成時，再下一個 T-SQL 語句又將啟動一個新事務。（2）結束事務可以使用 COMMIT 或 ROLLBACK 語句結束事務。 COMMIT：如果事務成功，則提交。COMMIT 語句保證事務的所有修改

7、在數(shù)據庫中都永久有效。COMMIT 語句還釋放資源，如事務使用的鎖。 ROLLBACK：如果事務中出現(xiàn)錯誤，或者用戶決定取消事務，可回滾該事務。ROLLBACK 語句通過將數(shù)據返回到它在事務開始時所處的狀態(tài)，來恢復在該事務中所做的所有修改。ROLLBACK 還會釋放由事務占用的資源。11什么是鎖定？答 在SQL Server 2005中，鎖定就是給數(shù)據庫對象加鎖。使用鎖定能確保事務完整性和數(shù)據庫一致性。鎖定可以防止用戶讀取正在由其他用戶更改的數(shù)據，并可以防止多個用戶同時更改相同數(shù)據。如果不使用鎖定，則數(shù)據庫中的數(shù)據可能在邏輯上不正確，并且對數(shù)據的查詢可能會產生意想不到的結果。12什么是死鎖？答

8、 死鎖是一種條件，不僅僅是在關系數(shù)據庫管理系統(tǒng) (RDBMS) 中發(fā)生，在任何多用戶系統(tǒng)中都可以發(fā)生。當兩個用戶(或會話)具有不同對象的鎖，并且每個用戶需要另一個對象的鎖時，就會出現(xiàn)死鎖。每個用戶都等待另一個用戶釋放他的鎖。當兩個連接陷入死鎖時，SQL Server會進行檢測，其中一個連接被選做死鎖犧牲品，該連接的事務回滾，同時應用程序收到錯誤。13簡述游標的概念。答 關系數(shù)據庫中的操作會對整個行集產生影響。由SELECT語句返回的行集包括所有滿足該語句WHERE子句中條件的行。由語句所返回的這一完整的行集被稱為結果集。應用程序，特別是交互式聯(lián)機應用程序，并不總能將整個結果集作為一個單元來有效

9、地處理。這些應用程序需要一種機制以便每次處理一行或一部分行。游標就是提供這種機制的結果集擴展。游標通過以下方式擴展結果處理： 允許定位在結果集的特定行。 從結果集的當前位置檢索一行或多行。 支持對結果集中當前位置的行進行數(shù)據修改。 為由其他用戶對顯示在結果集中的數(shù)據庫數(shù)據所做的更改提供不同級別的可見性支持。 提供腳本、存儲過程和觸發(fā)器中使用的訪問結果集中的數(shù)據的T-SQL語句。14給出以下程序的執(zhí)行結果。USE schoolSELECT sno,cno,degreeFROM scoreWHERE sno IN (103,105)ORDER BY snoCOMPUTE AVG(degree) B

10、Y snoGO解：結果如下：15給出以下程序的執(zhí)行結果。USE schoolGOSELECT teacher.tname AS '教師',student.sclass AS '班號', AVG(score.degree) AS '平均分'FROM student,course,score,teacherWHERE student.sno=score.sno AND o=o AND course.tno= teacher.tnoGROUP BY teacher.tname,student.sclass WITH CUBEGO解：結果如下：16給出以

11、下程序的執(zhí)行結果。USE schoolGOBEGIN TRANSACTION Mytran -啟動事務 INSERT INTO teacher VALUES(999,'張英','男','1960/03/05','教授','計算機系')-插入一個教師記錄SAVE TRANSACTION Mytran -保存點 INSERT INTO teacher VALUES(888,'胡麗','男','1982/8/04','副教授','電子工程系'

12、)-插入一個教師記錄ROLLBACK TRANSACTION MytranCOMMIT TRANSACTIONGOSELECT * FROM teacher -查詢teacher表的記錄GODELETE teacher WHERE tno='999'-刪除插入的記錄GO解：結果如下：17編寫一個程序，查詢最高分的課程名。解 采用子查詢方式。程序如下：USE schoolGOSELECT cnameFROM courseWHERE cno=(SELECT cno FROM score WHERE degree=(SELECT MAX(degree) FROM score)GO 1

13、8編寫一個程序，查詢95033班的最高分的學生的學號、姓名、班號、課程號和分數(shù)。解：程序如下：USE schoolGOSELECT s.sno,s.sname,s.sclass,o,sc.degreeFROM student s,score scWHERE s.sno=sc.sno AND s.sclass='95033' AND sc.degree= (SELECT MAX(degree) FROM student,score WHERE student.sno=score.sno AND student.sclass='95033' )GO19編寫一個程序，

14、查詢平均分高于所有平均分的課程號。解：程序如下：USE schoolGOSELECT cno,AVG(degree)FROM score GROUP BY cnoHAVING AVG(degree)>(SELECT AVG(degree) FROM score)GO20編寫一個程序，創(chuàng)建一個新表stud，包含所有學生的姓名、課程名和分數(shù)，并以姓名排序。解：程序如下：USE schoolGOSELECT s.sname,ame,sc.degreeINTO studFROM student s,course c,score scWHERE s.sno=sc.sno AND o=o AND s

15、c.degree IS NOT NULLORDER BY s.snameSELECT * FROM studGO21編寫一個程序，輸出每個班最高分的課程名和分數(shù)。解：采用數(shù)據來源為SELECT查詢結果的方法。程序如下：USE schoolGOSELECT sclass,cname,MAX(degree) degreeFROM (SELECT s.sno,s.sname,s.sclass,ame,sc.degree FROM student s,course c,score sc WHERE s.sno=sc.sno AND o=o AND degree IS NOT NULL) TGROUP

16、BY sclass,cnameORDER BY sclassGO執(zhí)行結果如下：22編寫一個程序，采用游標方式輸出所有課程的平均分。解：程序如下：USE schoolGO-聲明變量DECLARE c_name varchar(8),s_avg float-聲明游標DECLARE st_cursor CURSOR FOR SELECT ame,AVG(score.degree) FROM course,score WHERE o=o AND score.degree IS NOT NULL GROUP BY ame-打開游標OPEN st_cursor-提取第一行數(shù)據FETCH NEXT FROM

17、 st_cursor INTO c_name,s_avg-打印表標題PRINT '課程平均分'PRINT '-'WHILE FETCH_STATUS = 0 BEGIN -打印一行數(shù)據 PRINT c_name+' '+CAST(s_avg AS char(10) -提取下一行數(shù)據 FETCH NEXT FROM st_cursor INTO c_name,s_avgEND-關閉游標CLOSE st_cursor-釋放游標DEALLOCATE st_cursorGO其執(zhí)行結果如下：23編寫一個程序，采用游標方式輸出所有學號、課程號和成績等級。解：

18、程序如下：USE schoolGO-聲明變量DECLARE no1 char(5),no2 char(6),fs char(2)-聲明游標DECLARE fs_cursor CURSOR FOR SELECT sno,cno, CASE WHEN degree>=90 THEN 'A' WHEN degree>=80 THEN 'B' WHEN degree>=70 THEN 'C' WHEN degree>=60 THEN 'D' WHEN degree<60 THEN 'E' EN

19、D FROM score WHERE degree IS NOT NULL ORDER BY sno-打開游標OPEN fs_cursor-提取第一行數(shù)據FETCH NEXT FROM fs_cursor INTO no1,no2,fs-打印表標題PRINT '學號課程號等級'PRINT '-'WHILE FETCH_STATUS = 0 BEGIN -打印一行數(shù)據 PRINT no1+' '+no2+' '+fs -提取下一行數(shù)據 FETCH NEXT FROM fs_cursor INTO no1,no2,fsEND-關閉游標

20、CLOSE fs_cursor-釋放游標DEALLOCATE fs_cursorGO其執(zhí)行結果如下：24編寫一個程序，采用游標方式輸出各班各課程的平均分。解：程序如下：USE schoolGO-聲明變量DECLARE bh char(5),kc char(10),fs float-聲明游標DECLARE fs_cursor CURSOR FOR SELECT s.sclass,ame,AVG(sc.degree) FROM student s,course c,score sc WHERE s.sno=sc.sno AND o=o AND sc.degree IS NOT NULL GROUP

21、 BY s.sclass,ame-打開游標OPEN fs_cursor-提取第一行數(shù)據FETCH NEXT FROM fs_cursor INTO bh,kc,fs-打印表標題PRINT '班號課程 平均分'PRINT '-'WHILE FETCH_STATUS = 0 BEGIN -打印一行數(shù)據 PRINT bh+' '+kc+' '+CAST(fs AS varchar(10) -提取下一行數(shù)據 FETCH NEXT FROM fs_cursor INTO bh,kc,fsEND-關閉游標CLOSE fs_cursor-釋放游

22、標DEALLOCATE fs_cursorGO其執(zhí)行結果如下：上機實驗題5參考答案在上機實驗題4建立的factory數(shù)據庫上，完成如下各題（所有SELECT語句的查詢結果以文本格式顯示）。（1）刪除factory數(shù)據庫上各個表之間建立的關系。（2）顯示各職工的工資記錄和相應的工資小計。（3）按性別和部門名的所有組合方式列出相應的平均工資。（4）在worker表中使用以下語句插入一個職工記錄：INSERT INTO worker VALUES(20,'陳立','女','55/03/08',1,'75/10/10',4)在depart

23、表中使用以下語句插入一個部門記錄：INSERT INTO depart VALUES(5,'設備處')對worker和depart表進行全外連接顯示職工的職工號、姓名和部門名，然后刪除這兩個插入的記錄。（5）顯示最高工資的職工的職工號、姓名、部門名、工資發(fā)放日期和工資。（6）顯示最高工資的職工所在的部門名。（7）顯示所有平均工資低于全部職工平均工資的職工的職工號和姓名。（8）采用游標方式實現(xiàn)（6）小題的功能。（9）采用游標方式實現(xiàn)（7）小題的功能。（10）先顯示worker表中的職工人數(shù)，開始一個事務，插入一個職工記錄，再顯示worker表中的職工人數(shù)，回滾該事務，最后顯示wo

24、rker表中的職工人數(shù)。操作過程（1）刪除factory數(shù)據庫上各個表之間建立的關系的操作步驟如下： 啟動SQL Server管理控制器。 在“對象資源管理器”中展開LCB-PC服務器節(jié)點。 展開“數(shù)據庫”節(jié)點。 選中school，將其展開。 展開“數(shù)據庫關系圖”節(jié)點。 選中dbo.Diagram_1，右擊，在出現(xiàn)的快捷菜單中選擇“修改”命令，如圖10.1所示。 在數(shù)據庫關系圖中，選擇表示要從關系圖中刪除的關系的連接線（對于兩條連線均進行的操作）。 右擊關系線，從快捷菜單中選擇“從數(shù)據庫中刪除關系”命令。 出現(xiàn)一個消息框，提示確認刪除。單擊“是”按鈕。 在出現(xiàn)的對話框中單擊“是”按鈕保存所做的

25、修改。這樣就將worker表和depart表以及worker表和salary表之間的關系刪除了。圖10.1 factory數(shù)據庫關系圖（2）對應的程序如下：USE factoryGOSELECT worker.職工號,worker.姓名,salary.工資FROM worker,salaryWHERE worker.職工號=salary.職工號ORDER BY worker.職工號,worker.姓名COMPUTE SUM(salary.工資) BY worker.職工號 GO執(zhí)行結果如下：職工號 姓名 工資- - -1 孫華 1201.51 孫華 12010.5sum-2408職工號 姓名

26、工資- - -10 陳濤 1245.810 陳濤 1250.8sum-24910.6職工號 姓名 工資- - -11 劉欣 125511 劉欣 1250sum-2505職工號 姓名 工資- - -12 李涵 134512 李涵 1350sum-2695職工號 姓名 工資- - -13 王小燕 120513 王小燕 1200sum-2405職工號 姓名 工資- - -14 李藝 1000.614 李藝 1005.6sum-20010.2職工號 姓名 工資- - -15 魏君 110515 魏君 1100sum-2205職工號 姓名 工資- - -2 孫天奇 9052 孫天奇 900sum-180

27、5職工號 姓名 工資- - -3 陳明 1350.63 陳明 1355.6sum-27010.2職工號 姓名 工資- - -4 李華 1500.54 李華 1505.5sum-3006職工號 姓名 工資- - -5 余慧 7305 余慧 725sum-1455職工號 姓名 工資- - -6 歐陽少兵 10856 歐陽少兵 1085sum-2170職工號 姓名 工資- - -7 程西 755.87 程西 750.8sum-15010.6職工號 姓名 工資- - -8 張旗 7288 張旗 733sum-1461職工號 姓名 工資- - -9 劉夫文 20010.89 劉夫文 2011.8sum-

28、4018.6（3）對應的程序如下：USE factoryGOSELECT worker.性別,depart.部門名,AVG(salary.工資) AS '平均工資'FROM worker,depart,salaryWHERE worker.職工號=salary.職工號AND worker.部門號=depart.部門號GROUP BY worker.性別,depart.部門名WITH CUBE GO執(zhí)行結果如下：性別 部門名 平均工資- - -男 財務部 1228.25男 人事部 1335.3男 市場部 1165.75男 NULL 12410.07女 財務部 977.9女 人事部

29、 902.5女 市場部 1052.8女 NULL 992.78NULL NULL 1161.64NULL 財務部 1103.075NULL 人事部 1248.74NULL 市場部 1128.1（4）對應的程序如下：USE factoryGOINSERT INTO worker VALUES('20','陳立','女','55/03/08',1,'75/10/10',4)GOINSERT INTO depart VALUES(5,'設備處')GOSELECT worker.職工號,worker.姓名,

30、depart.部門名FROM worker FULL JOIN departON(worker.部門號=depart.部門號)ORDER BY worker.職工號GODELETE FROM worker WHERE 職工號='20'GODELETE FROM depart WHERE 部門號=5GO執(zhí)行結果如下：職工號 姓名 部門名- - -NULLNULL設備處 1 孫華財務部 10 陳濤 人事部 11 劉欣 財務部 12 李涵 市場部 13 王小燕 財務部 14 李藝 市場部 15 魏君 市場部 2 孫天奇 人事部 20 陳立 NULL3 陳明 人事部 4 李華 市場部

31、5 余慧 市場部 6 歐陽少兵 市場部 7 程西 財務部 8 張旗 人事部 9 劉夫文 人事部 （5）對應的程序如下：USE factoryGOSELECT worker.職工號,worker.姓名,depart.部門名,salary.日期,salary.工資FROM worker,depart,salaryWHERE worker.部門號=depart.部門號AND worker.職工號=salary.職工號AND salary.工資= (SELECT MAX(工資) FROM salary)GO執(zhí)行結果如下：職工號 姓名 部門名 日期 工資- - - -9 劉夫文 人事部 2004-02-

32、04 00:00:00.000 2011.8（6）對應的程序如下：USE factoryGOSELECT 部門名FROM departWHERE 部門號= (SELECT 部門號 FROM worker WHERE 職工號= (SELECT 職工號 FROM salary WHERE 工資= (SELECT MAX(工資) FROM salary) ) )GO執(zhí)行結果如下：部門名 - 人事處（7）對應的程序如下：USE factoryGOSELECT 職工號,姓名FROM workerWHERE 職工號IN (SELECT 職工號 FROM salary GROUP BY 職工號 HAVING

33、 AVG(工資)<(SELECT AVG(工資) FROM salary) )GO執(zhí)行結果如下：職工號 姓名- -14 李藝 15 魏君 2 孫天奇 5 余慧 6 歐陽少兵 7 程西 8 張旗 （8）對應的程序如下：USE factoryGOSET NOCOUNT ON-聲明變量DECLARE dname char(10)-聲明游標DECLARE d_cursor CURSOR FOR SELECT 部門名 FROM depart WHERE 部門號= (SELECT 部門號 FROM worker WHERE 職工號= (SELECT 職工號 FROM salary WHERE 工資= (SELECT MAX(工資) FROM salary) ) )-打開游標OPEN d_cursor-提取第一行數(shù)據FETCH NEXT FROM d_cursor INTO dname-打印表標題PRINT '部門名'PRINT '-'WHILE FETCH_STATUS = 0BEGIN -打印一行數(shù)據 PRINT dname -提取下一行數(shù)據 FETCH NEXT FROM d_cursor INTO dnameEND-關閉游標CLOSE d_cursor-釋放游標DE