為大家收集整理了《2013sql認證考試練習試題7》供大家參考，希望對大家有所幫助?。?！
第31題
你需要將值1.75進位到最近的整數(shù)。應使用哪個代碼片段？
A. Select ROUND(1.75,0)
B. Select ROUND(1.75,2)
C. Select ROUND(1.75,1.0)
D. Select ROUND(1.75,2.0)
答案：A
第32題
有一個名為TelephoneNumber的列， 其中將數(shù)字作為varchar(20)存儲。 你需要編寫查詢來返回電話號碼的前三個字符。 應使用哪個表達式？
A. LEFT(TelephoneNumber, 3)
B. SUBSTRING(TelephoneNumber, 3, 3)
C. SUBSTRING (TelephoneNumber, 3, 1)
D. CHARINDEX(’[0-9][0-9][0-9]’, TelephoneNumber, 3)
答案：A
第33題
你是位于西雅圖的數(shù)據(jù)庫開發(fā)人員。你有一個客戶在墨爾本，其時區(qū)不同于西雅圖。你使用datetimeoffset數(shù)據(jù)類型， 并使用西雅圖的偏移量來存儲數(shù)據(jù)。你需要以墨爾本的偏移量顯示日期。應使用哪個函數(shù)？
A. CONVERT
B. DATEADD
C. SWITCHOFFSET
D. TODATETIMEOFFSET
答案：C
第34題
你有一個數(shù)據(jù)庫，其中包含兩個表，分別名為ProductCategory和ProductSubCategory。你需要編寫一個查詢來返回包 含10個以上子類別的產(chǎn)品類別的列表。 應使用哪個查詢？
A. SELECT [Name] FROM ProductSubCategory WHERE ProductCategoryID IN ( SELECT ProductCategoryID FROM ProductCategory) GROUP BY [Name] HAVING COUNT(*) > 10 )
B. SELECT [Name] FROM ProductSubCategory WHERE ProductCategoryID NOT IN ( SELECT ProductCategoryID FROM ProductCategory) GROUP BY [Name] HAVING COUNT(*) > 10)
C. SELECT [Name] FROM Product Category c WHERE EXISTS ( SELECT ProductCategoryID FROM ProductSubCategory WHERE ProductCategoryID = c.ProductCategoryID GROUP BY ProductCategoryID HAVING COUNT(*) > 10)
D. SELECT [Name] FROM Product Category c WHERE NOT EXISTS ( SELECT ProductCategoryID FROM ProductSubCategory WHERE ProductCategoryID = c.ProductCategoryID GROUP BY ProductCategoryID HAVING COUNT(*) > 10)
答案：C
第35題
你的數(shù)據(jù)庫包含數(shù)百萬個訂單的銷售信息。你需要確定平均單價的訂單，以及訂單總額大于10,000的訂單。該列表應包含不超過20個訂單。 應使用哪個查詢？
A. SELECT (20) o.SalesOrderId, o.OrderDate, o.Total, SUM(od.QTY * od.UnitPrice) / SUM(od.Qty) AS [AvgUnitPrice] FROM Sales.SalesOrderHeader o JOIN SALES.SalesOrderDetail od ON o.SalesOrderId = od.SalesOrderId WHERE o.Total> 10000 GROUP BY o.SalesOrderId, o.OrderDate, o.Total ORDER BY AvgUnitPrice;
B. SELECT (20) o.SalesOrderId, o.OrderDate, o.Total, (SELECT SUM(od.Qty * od.UnitPrice) / SUM(od.QTY) FROM Sales.SalesOrderDetail od WHERE o. SalesOrderId = od.SalesOrderId) AS [AvgUnitPrice] FROM Sales.SalesOrderHeader o WHERE o.Total> 10000 ORDER BY AvgUnitPrice DESC;
C. SELECT (20) o.SalesOrderId, o.OrderDate, o.Total, SUM(od.Qty * od.UnitPrice) / SUM(od.Qty) AS [AvgUnitPrice] FROM Sales.SalesOrderHeader o JOIN Sales.SalesOrderDetail od ON o.SalesOrderId = od.SalesOrderId WHERE o.Total> 10000 GROUP BY o.SalesOrderId, o.OrderDate, o.Total ORDER BY Total DESC;
D. SELECT (20) o.SalesOrderId, o.OrderDate, o.Total, (SELECT SUM(od.Qty * od.UnitPrice) / SUM(od.Qty) FROM Sales.SalesOrderDetail od WHERE o. SalesOrderId = od.SalesOrderId) AS [AvgUnitPrice] FROM Sales.SalesOrderHeader o WHERE o.Total > 10000 ORDER BY o.Total DESC, AvgUnitPrice;
答案：B
第31題
你需要將值1.75進位到最近的整數(shù)。應使用哪個代碼片段？
A. Select ROUND(1.75,0)
B. Select ROUND(1.75,2)
C. Select ROUND(1.75,1.0)
D. Select ROUND(1.75,2.0)
答案：A
第32題
有一個名為TelephoneNumber的列， 其中將數(shù)字作為varchar(20)存儲。 你需要編寫查詢來返回電話號碼的前三個字符。 應使用哪個表達式？
A. LEFT(TelephoneNumber, 3)
B. SUBSTRING(TelephoneNumber, 3, 3)
C. SUBSTRING (TelephoneNumber, 3, 1)
D. CHARINDEX(’[0-9][0-9][0-9]’, TelephoneNumber, 3)
答案：A
第33題
你是位于西雅圖的數(shù)據(jù)庫開發(fā)人員。你有一個客戶在墨爾本，其時區(qū)不同于西雅圖。你使用datetimeoffset數(shù)據(jù)類型， 并使用西雅圖的偏移量來存儲數(shù)據(jù)。你需要以墨爾本的偏移量顯示日期。應使用哪個函數(shù)？
A. CONVERT
B. DATEADD
C. SWITCHOFFSET
D. TODATETIMEOFFSET
答案：C
第34題
你有一個數(shù)據(jù)庫，其中包含兩個表，分別名為ProductCategory和ProductSubCategory。你需要編寫一個查詢來返回包 含10個以上子類別的產(chǎn)品類別的列表。 應使用哪個查詢？
A. SELECT [Name] FROM ProductSubCategory WHERE ProductCategoryID IN ( SELECT ProductCategoryID FROM ProductCategory) GROUP BY [Name] HAVING COUNT(*) > 10 )
B. SELECT [Name] FROM ProductSubCategory WHERE ProductCategoryID NOT IN ( SELECT ProductCategoryID FROM ProductCategory) GROUP BY [Name] HAVING COUNT(*) > 10)
C. SELECT [Name] FROM Product Category c WHERE EXISTS ( SELECT ProductCategoryID FROM ProductSubCategory WHERE ProductCategoryID = c.ProductCategoryID GROUP BY ProductCategoryID HAVING COUNT(*) > 10)
D. SELECT [Name] FROM Product Category c WHERE NOT EXISTS ( SELECT ProductCategoryID FROM ProductSubCategory WHERE ProductCategoryID = c.ProductCategoryID GROUP BY ProductCategoryID HAVING COUNT(*) > 10)
答案：C
第35題
你的數(shù)據(jù)庫包含數(shù)百萬個訂單的銷售信息。你需要確定平均單價的訂單，以及訂單總額大于10,000的訂單。該列表應包含不超過20個訂單。 應使用哪個查詢？
A. SELECT (20) o.SalesOrderId, o.OrderDate, o.Total, SUM(od.QTY * od.UnitPrice) / SUM(od.Qty) AS [AvgUnitPrice] FROM Sales.SalesOrderHeader o JOIN SALES.SalesOrderDetail od ON o.SalesOrderId = od.SalesOrderId WHERE o.Total> 10000 GROUP BY o.SalesOrderId, o.OrderDate, o.Total ORDER BY AvgUnitPrice;
B. SELECT (20) o.SalesOrderId, o.OrderDate, o.Total, (SELECT SUM(od.Qty * od.UnitPrice) / SUM(od.QTY) FROM Sales.SalesOrderDetail od WHERE o. SalesOrderId = od.SalesOrderId) AS [AvgUnitPrice] FROM Sales.SalesOrderHeader o WHERE o.Total> 10000 ORDER BY AvgUnitPrice DESC;
C. SELECT (20) o.SalesOrderId, o.OrderDate, o.Total, SUM(od.Qty * od.UnitPrice) / SUM(od.Qty) AS [AvgUnitPrice] FROM Sales.SalesOrderHeader o JOIN Sales.SalesOrderDetail od ON o.SalesOrderId = od.SalesOrderId WHERE o.Total> 10000 GROUP BY o.SalesOrderId, o.OrderDate, o.Total ORDER BY Total DESC;
D. SELECT (20) o.SalesOrderId, o.OrderDate, o.Total, (SELECT SUM(od.Qty * od.UnitPrice) / SUM(od.Qty) FROM Sales.SalesOrderDetail od WHERE o. SalesOrderId = od.SalesOrderId) AS [AvgUnitPrice] FROM Sales.SalesOrderHeader o WHERE o.Total > 10000 ORDER BY o.Total DESC, AvgUnitPrice;
答案：B