為大家收集整理了《2013微軟認(rèn)證:sql認(rèn)證考試練習(xí)試題10》供大家參考，希望對(duì)大家有所幫助?。?！
第46題 你有一個(gè)名為ProductCounts的表，表中包含1000樣產(chǎn)品，以及每樣產(chǎn)品的售出件數(shù)。你需要編寫(xiě)查詢來(lái)顯示銷售最 頻繁的前5%的產(chǎn)品。應(yīng)使用哪一個(gè)Transact-SQL代碼片段？ A. WITH Percentages AS ( SELECT *, NTILE(5) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM percentages WHERE groupingColumn =1; B. WITH Percentages AS ( SELECT *, NTILE(5) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM Percentages WHERE groupingColumn = 5; C. WITH Percentages AS ( SELECT *, NTILE(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM Percentages WHERE groupingColumn = 1; D. WITH Percentages AS ( SELECT *, NTILE(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM Percentages WHERE groupingColumn = 20; 答案：D
第47題 你為一家國(guó)際慈善組織工作。你要編寫(xiě)一個(gè)查詢來(lái)列出的100個(gè)不同的捐贈(zèng)額。你編寫(xiě)了下面的代碼片段（包 含的行號(hào)僅供參考）： 01 SELECT * 02 FROM (SELECT Customer.CustomerID, SUM(TotalDue) AS 03 TotalGiven, .................... FROM Customer 04 05 JOIN SalesOrder 06 ON Customer.CustomerID = SalesOrder.CustomerID GROUP BY Customer.CustomerID) AS DonationsToFilterWHERE FilterCriteria <= 07 08 100 你需要在第03行插入Transact-SQL子句來(lái)完成查詢。應(yīng)插入哪個(gè)Transact-SQL子句？ A. RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria B. NTILE(100) OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria C. ROW_NUMBER() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria D. DENSE_RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria 答案：D
第48題 有一臺(tái)數(shù)據(jù)庫(kù)服務(wù)器有四個(gè)四核處理器。此數(shù)據(jù)庫(kù)服務(wù)器執(zhí)行用來(lái)生成報(bào)表的復(fù)雜查詢。你需要強(qiáng)制該查詢只使用 一個(gè)處理器內(nèi)核，而不影響其他查詢。應(yīng)使用哪個(gè)選項(xiàng)？ A. OPTION (FAST 1) B. OPTION (MAXDOP 1) C. OPTION (RECOMPILE) D. OPTION (MAXRECURSION 1) 答案：B
第49題 你注意到，對(duì)于某一組參數(shù)值，下面的查詢有時(shí)執(zhí)行很快，其他時(shí)候執(zhí)行很慢。你還注意到Address表中90%的行包 含相同的城市值。 SELECT AddressId, AddressLine1, City, PostalCodeFROM Person.Address WHERE City = @city_nameAND PostalCode = @postal_code 你需要使用查詢提示，對(duì)于這組參數(shù)值，該提示將產(chǎn)生更一致的查詢執(zhí)行時(shí)間。應(yīng)使用哪個(gè)查詢提示？ A. FAST B. MAXDOP C. OPTIMIZE FOR D. PARAMETERIZATION FORCED 答案：C
第50題 你的任務(wù)是編寫(xiě)一條查詢來(lái)選擇一百萬(wàn)行。你需要優(yōu)化查詢以盡快返回前50行。 應(yīng)使用哪個(gè)查詢提示？ A. FAST 50 B. MAXDOP 50 C. OPTIMIZE FOR @ROWS=50 D. TABLE HINT(table, INDEX(50)) 答案：A
第46題 你有一個(gè)名為ProductCounts的表，表中包含1000樣產(chǎn)品，以及每樣產(chǎn)品的售出件數(shù)。你需要編寫(xiě)查詢來(lái)顯示銷售最 頻繁的前5%的產(chǎn)品。應(yīng)使用哪一個(gè)Transact-SQL代碼片段？ A. WITH Percentages AS ( SELECT *, NTILE(5) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM percentages WHERE groupingColumn =1; B. WITH Percentages AS ( SELECT *, NTILE(5) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM Percentages WHERE groupingColumn = 5; C. WITH Percentages AS ( SELECT *, NTILE(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM Percentages WHERE groupingColumn = 1; D. WITH Percentages AS ( SELECT *, NTILE(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts) SELECT * FROM Percentages WHERE groupingColumn = 20; 答案：D
第47題 你為一家國(guó)際慈善組織工作。你要編寫(xiě)一個(gè)查詢來(lái)列出的100個(gè)不同的捐贈(zèng)額。你編寫(xiě)了下面的代碼片段（包 含的行號(hào)僅供參考）： 01 SELECT * 02 FROM (SELECT Customer.CustomerID, SUM(TotalDue) AS 03 TotalGiven, .................... FROM Customer 04 05 JOIN SalesOrder 06 ON Customer.CustomerID = SalesOrder.CustomerID GROUP BY Customer.CustomerID) AS DonationsToFilterWHERE FilterCriteria <= 07 08 100 你需要在第03行插入Transact-SQL子句來(lái)完成查詢。應(yīng)插入哪個(gè)Transact-SQL子句？ A. RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria B. NTILE(100) OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria C. ROW_NUMBER() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria D. DENSE_RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria 答案：D
第48題 有一臺(tái)數(shù)據(jù)庫(kù)服務(wù)器有四個(gè)四核處理器。此數(shù)據(jù)庫(kù)服務(wù)器執(zhí)行用來(lái)生成報(bào)表的復(fù)雜查詢。你需要強(qiáng)制該查詢只使用 一個(gè)處理器內(nèi)核，而不影響其他查詢。應(yīng)使用哪個(gè)選項(xiàng)？ A. OPTION (FAST 1) B. OPTION (MAXDOP 1) C. OPTION (RECOMPILE) D. OPTION (MAXRECURSION 1) 答案：B
第49題 你注意到，對(duì)于某一組參數(shù)值，下面的查詢有時(shí)執(zhí)行很快，其他時(shí)候執(zhí)行很慢。你還注意到Address表中90%的行包 含相同的城市值。 SELECT AddressId, AddressLine1, City, PostalCodeFROM Person.Address WHERE City = @city_nameAND PostalCode = @postal_code 你需要使用查詢提示，對(duì)于這組參數(shù)值，該提示將產(chǎn)生更一致的查詢執(zhí)行時(shí)間。應(yīng)使用哪個(gè)查詢提示？ A. FAST B. MAXDOP C. OPTIMIZE FOR D. PARAMETERIZATION FORCED 答案：C
第50題 你的任務(wù)是編寫(xiě)一條查詢來(lái)選擇一百萬(wàn)行。你需要優(yōu)化查詢以盡快返回前50行。 應(yīng)使用哪個(gè)查詢提示？ A. FAST 50 B. MAXDOP 50 C. OPTIMIZE FOR @ROWS=50 D. TABLE HINT(table, INDEX(50)) 答案：A