實驗三：復雜的單表查詢 一、實驗目的 1. 掌握SELECT語句的基本語法； 2. 掌握GROUP BY 子句的作用和使用方法； 3. 掌握HAVING子句的作用和使用方法； 4. 掌握聚集函數(shù)的作用和使用方法。 二、實驗環(huán)境 已安裝SQL Server 2005 或2012以及更高版本的計算機； 三、實驗學時 2學時 四、實驗要求 1. 了解單表的復雜查詢和實現(xiàn)方式； 2. 完成實驗報告； 五、實驗原理 1. 在查詢語句中用Group by子句進行分組； 2. 用Having子句對分組進行篩選。 3. 使用MAX（），MIN（），COUNT（），SUM（），AVG（）等函數(shù)在查詢結(jié)果集中生成匯總值。 六、實驗示例 l 針對S_T數(shù)據(jù)庫 1．計算出英語課程的平均分,最高分和最低分。 select avg(grade) 平均分,max(grade) 最高分,min(grade) 最低分 from sc where cno='1' 2. 查詢數(shù)據(jù)表SC的學生的學號、課程號及成績，對于成績按以下規(guī)定顯示：成績在60分以下顯示為不及格；60-79之間顯示為良好；80以上顯示為優(yōu)秀。 SELECT Sno,Cno,GradeLevel= case when Grade<60 then '不及格' when grade>=60 and Grade<=79 then '良好' when grade>=80 then '優(yōu)秀' end FROM SC l 針對bookshop數(shù)據(jù)庫 1、統(tǒng)計員工表中薪水在40000-60000之間的人數(shù) select count(*) as 人數(shù) from employee where salary between 40000 and 60000 2、計算圖書編號為'20652'圖書的平均銷售單價 select avg(unit_price) from sale_item where book_no='20652' 3、找出訂單總金額超過300的訂單信息，并按訂單總金額從大到小排序。 Select order_no,sum(qty*unit_price) tot_amt from sale_item group by order_no having sum(qty*unit_price)>300 order by sum(qty*unit_price) desc 4、查詢訂購了三種以上產(chǎn)品的訂單號。 Select order_no ,count(*) from sale_item Group by order_no Having count(*)>=3 七、實驗內(nèi)容及步驟 請使用T-SQL 語句實現(xiàn)以下操作： l 針對S_T數(shù)據(jù)庫 1. 顯示所有院系（要求不能重復，不包括空值），并在結(jié)果集中增加一列字段“院系規(guī)?！保渲腥粼撛合等藬?shù)>=5則該字段值為“規(guī)模很大”，若該院系人數(shù)大于等于4小于5則該字段值為“規(guī)模一般”， 若該院系人數(shù)大于等于2小于4則該字段值為“規(guī)模稍小”，否則顯示“規(guī)模很小”； 2. 顯示學生信息表中的學生總?cè)藬?shù)及平均年齡，在結(jié)果集中列標題分別指定為“學生總?cè)藬?shù)，平均年齡”； 3. 顯示選修的課程數(shù)大于3的各個學生的選修課程數(shù)； 4. 按課程號降序顯示選修各個課程的總?cè)藬?shù)、最高成績、最低成績及平均成績； 5. 列出有二門以上課程（含兩門）不及格的學生的學號及不及格門數(shù)； l 針對bookshop數(shù)據(jù)庫 1、在員工表employee中統(tǒng)計員工人數(shù)。 2、統(tǒng)計各部門員工的員工人數(shù)及平均薪水。 3、查詢銷售業(yè)績超過300元的員工編號。 4、計算每種圖書的銷售數(shù)量總和與平均銷售單價。 5、統(tǒng)計各部門不同性別、或各部門、或不同性別或所有員工的平均薪水。（在GROUP BY 子句中使用CUBE關(guān)鍵字） 6、統(tǒng)計各部門不同性別、或各部門或所有員工的平均薪水。（在GROUP BY 子句中使用ROLLUP關(guān)鍵字） 7、計算出一共銷售了幾種圖書。 8、顯示sale_item表中每種圖書的訂購金額總和，并且依據(jù)銷售金額由大到小排列來顯示出每一種圖書的排行榜。 9、顯示sale_item表中銷售金額在300元以上的訂單的銷售數(shù)量，銷售金額，并按降序排序。 10、統(tǒng)計各個類型圖書的種類數(shù)量。 11、統(tǒng)計各個客戶的消費金額。 12、統(tǒng)計各個客戶的訂單數(shù)量。 八、出現(xiàn)問題及解決辦法 如：某些查詢操作無法執(zhí)行，如何解決？

A.從SQL1控制臺運行SQLServerProfiler。將跟蹤信息存儲到SQL1上的表中

B.從連接到SQL1的遠程桌面運行SQLServerProfiler。將跟蹤信息存儲到另一臺計算機的文件中

C.連接到SQL1的一臺獨立計算機中運行SQLServerProfiler。將跟蹤信息存儲到SQL1上的表中

D.從連接到SQL1的一臺獨立計算機中運行SQLServerProfiler。將跟蹤信息存儲到運行SQLServerProfiler的計算機文件中

公司A有兩臺名為SQL1和SQL2的SQLSERVER2005數(shù)據(jù)庫服務器。在SQL1上有一個名為DB1的數(shù)據(jù)庫，它包含了一個表TableA。TableA被復制到SQL2上的DB1Repl數(shù)據(jù)庫后，有用戶反饋說查詢DB1Repl中表TableA的數(shù)據(jù)非常緩慢。經(jīng)過你的調(diào)查后發(fā)現(xiàn)，表TableA只有一個聚集索引被復制過來，所有其他的索引都沒有被復制。你檢查表TableA的項目屬性，如下圖所示。為了在訂閱被重新初始化的時候DB1中表TableA的所有索引都被復制，你應該改變哪兩項復制屬性？（）

A.復制聚集索引

B.復制非聚集索引

C.復制擴展屬性

D.復制主鍵約束

E.復制索引分區(qū)方案

F.復制XML索引

公司A有一個名為SQL1的SQLServer2005服務器。SQL1設置為SQLServer和Windows身份認證模式。使用Windows認證，在SQL1上你有sysadmin權(quán)限。使用SQL1工作的所有用戶都反映說：使用SQL1的所有應用程序均停止響應。你的上司讓你去調(diào)查此問題。在任務管理器中，你注意到CPU使用率為100%。你試圖用SQLServerManagementStudio連接到SQL1。但SQL1沒有響應。你需要連接到SQL1才能診斷問題。哪兩種方法可能讓你達成此目標？（）

A.用osql實用工具連接到SQL1

B.使用專用管理員連接的sqlcmd實用工具連接到SQL1

C.用SQLServerManagementStudio連接到SQL1。打開一個查詢窗口并執(zhí)行以下語句：DBCCTRACEON1244

D.在SQLServerManagementStudio中，在工具欄上，點擊數(shù)據(jù)庫引擎查詢以打開連接到數(shù)據(jù)庫引擎對話框。在服務器名稱文本框內(nèi)，敲入ADMIN：SQL1。選擇SQLServer身份認證并提供你的sa身份。點擊連接按鈕

你正在使用SQLSERVER2005服務器SQL1上的一個名為DB1的數(shù)據(jù)庫。DB17X24小時不間斷運行。DB1最近的一個副本保存在名為SQLtest的服務器上，SQLtest同時也在運行著SQLSERVER2005.你發(fā)覺在SQL1上的查詢存在著大量的對表完全掃描，為此你得出結(jié)論DB1數(shù)據(jù)庫需要額外的引。目前已經(jīng)有適合DTA（DatabaseEngineTuningAdvisor：數(shù)據(jù)庫引擎優(yōu)化顧問）的工作負荷文件，你需要做的是利用DTA對工作負荷文件進行分析。在分析的過程中，你必須要保證SQL1服務器良好的工作性能，而且要保證DTA推薦的建議是有效的。你應該如何實現(xiàn)這個目標？（）

A.把工作負荷文件保存在SQL1上。在SQLTest上啟動DTA并連接到SQL1上，然后選上所有的優(yōu)化選項并且在高級選項對話框中選中“僅生成聯(lián)機建議”，然后啟動分析。

B.把工作負荷文件存儲在SQLtest上。在SQLTest上啟動DTA并連接到SQLTest上，然后選上所有的優(yōu)化選項并且在高級選項對話框中選中“僅生成聯(lián)機建議”，然后啟動分析。

C.把工作負荷文件保存在SQL1上。在SQL1上啟動DTA并連接到SQL1上，然后選上所有的優(yōu)化選項并且在高級選項對話框中選中“所有建議均為脫機建議”，然后啟動分析。

D.把工作負荷文件存儲在SQLtest上。在SQLTest上啟動DTA并連接到SQLTest上，然后選上所有的優(yōu)化選項并且在高級選項對話框中選中“所有建議均為脫機建議”，然后啟動分析。

公司A有兩臺名為SQL1和SQL2的SQLSERVER2005數(shù)據(jù)庫服務器。這兩個服務器之間進行數(shù)據(jù)復制。SQL1即是發(fā)布服務器也是分發(fā)服務器，SQL2只作為訂閱服務器。在SQL1上只有一個名為Pub1的發(fā)布，并且由SQL1自己擔任分發(fā)者?，F(xiàn)在，管理者需要一個復制滯后時間狀態(tài)報表。因此，你需要在SQL1上使用復制監(jiān)視器找出當前在發(fā)布服務器到分發(fā)服務器、分發(fā)服務器到訂閱服務器之間的滯后時間是否在正常范圍之內(nèi)，你應該怎么做？（）

A.選擇SQL1節(jié)點，然后選擇訂閱監(jiān)視列表頁，查看列表中SQL2訂閱的滯后時間列。

B.選擇Pub1節(jié)點，然后選擇所有發(fā)布頁，查看列表中SQL2訂閱的滯后時間列。

C.選擇Pub1節(jié)點，然后選擇跟蹤令牌頁，選擇插入跟蹤器，然后在出現(xiàn)的列表中查看訂閱的滯后時間值。

D.選擇SQL1節(jié)點，然后選擇訂閱監(jiān)視列表頁，然后雙擊SQL2訂閱，查看發(fā)布服務器到分發(fā)服務器歷史記錄頁和分發(fā)服務器到訂閱服務器的歷史記錄頁中持續(xù)時間值否正常。

A.確信SQL1和SQL2在彼此的安全上下文采用相同的登錄名

B.配置SQL2為SQL1的遠程服務器。在SQL1上寫查詢

C.配置SQL2為SQL1的連接服務器并且去模擬遠程登錄

D.配置SQL2為分布式服務器.用pass-throughauthentication.（傳遞認證）

公司一個新的員工需要訪問名為SQL1服務器上的SQLSERVER2005數(shù)據(jù)庫.你用CREATELOGINajonesWITHPASSWORD=’SQLServer$1’為其創(chuàng)建了登錄名ajones。新員工報告說：當他在使用ajones登錄的時候，收到如下的錯誤“用戶‘a(chǎn)jones’登錄失敗。該用戶與可信的SQLSERVER連接無關(guān)聯(lián)”。你需要解決這個問題并允許這個員工去登錄SQL1。你應該怎么做？（）

A.改變SQLSERVER安全身份驗證模式為“SQLSERVER和Windows身份驗證模式”

B.改變SQLSERVER安全身份驗證模式為“Windows身份驗證模式”

C.保證創(chuàng)建的登錄名是帶方括號（[]）的。（例如：[loginname]）

D.通過用CREATEUSER語句給新創(chuàng)建的登錄訪問指定數(shù)據(jù)庫的權(quán)限

你正在創(chuàng)建一個提供客戶數(shù)據(jù)給外部應用的HTTP端點。你的SQLSERVER2005服務器名稱為SQL1?，F(xiàn)在，你在AdventureWorks數(shù)據(jù)中創(chuàng)建一個存儲過程dbo.usp_GetPersonData來查找數(shù)據(jù)。然后使用如下代碼創(chuàng)建一個HTTP端點：結(jié)果，連接到端點的第一個用戶反饋說不能得到任何數(shù)據(jù)。你連接到端點后，發(fā)現(xiàn)端點并不做出響應。為此，需要你修改端點，讓它能夠返回我們期望的數(shù)據(jù)，你應該怎么做？（）

A.改變AUTHENTICATION屬性為KERBEROS

B.指定BATCHES=ENABLED

C.指定STATE=Started

D.指定WSDL=’pr_GetPersonData’

A）通過SQL Native Client 連接到SQL1??

B）通過SQL Server 外圍應用配置器連接到SQL1??

C）在SSMS 的工具欄點擊“新建查詢”按鈕，在“服務器名稱”中輸入 ADMIN:SQL1，然后點擊“連接”按鈕??

D）在SSMS 的工具欄中，點擊“數(shù)據(jù)庫引擎查詢”按鈕，在“服務器名稱”中輸入ADMIN:SQL1，然后點擊“連接”按鈕