52 你有名为 Customers 和 Orders 的两个表。因为所有的客户至少有一个订单，你需要为每个客户列出客户姓名及每个客户的订单数。
A. SELECT c.CustomerName,SUM(o.OrderID) AS [OrderCount] FROM Customers c JOIN Orders o
ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName
B. SELECT COUNT(o.OrderId) AS [OrderCount]
FROM CUSTOMERS c JOIN ORDERS o ON c.CUSTOMERID = o.CUSTOMERID
C. SELECT c.CustomerName,COUNT(o.OrderID) AS [OrderCount] FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName HAVING COUNT(o.OrderID) > 1
D. SELECT c.CustomerName,COUNT(o.OrderId) AS [OrderCount] FROM Customers cJOIN Orders o ON c.CustomerId = o.CustomerId GROUP BY c.CustomerName 答案：D
53 你有一个 Products 表。这个表包含名为 Color 的一列。你需要写一个 T-SQL 语句来计算每种产品颜色占所有产品中的百分率。 A. SELECT Color,
COUNT(*) OVER(PARTITION BY Color)/ (COUNT(*) * 1.0) AS PercentColor FROM Products GROUP BY Color;
B. SELECT Color,
COUNT(*) OVER() / (COUNT(*) * 1.0) AS PercentColor/ (COUNT(*) * 1.0) AS PercentColor
FROM Products GROUP BY Color;
C. SELECT Color, (COUNT(*) * 1.0)/ COUNT(*) OVER() AS PercentColor FROM Products GROUP BY Color;
D. SELECT Color
COUNT(*) * 1.0) / COUNT(*) OVER(PARTITION BY Color) AS PercentColor FROM Products GROUP BY Color; 答案：C
54 你有名为 SalesPerson 和 SalesTerritory 的两个表。你需要创建包含 SalesPerson 表和 SalesTerritory 表的笛卡尔积的样例数据。 A. SELECT p.SalesPersonId,t.Name AS [Territory]
FROM Sales.SalesPerson p FULL JOIN Sales.SalesTerritory t ON p.TerritoryId = t.TerritoryId
B. SELECT p.SalesPersonId,t.Name AS [Territory]
FROM Sales.SalesPerson p INNER JOIN Sales.SalesTerritory t ON p.TerritoryId = t.TerritoryId
C. SELECT p.SalesPersonId,t.Name AS [Territory]
FROM Sales.SalesPerson p CROSS JOIN Sales.SalesTerritory t WHERE p.TerritoryId = t.TerritoryId
D. SELECT p.SalesPersonId,t.Name AS [Territory]
第 21 页
FROM Sales.SalesPerson p CROSS JOIN Sales.SalesTerritory t 答案：D
55 你有一个名为 Employees 的表。你想确认每位雇员汇报的直接领导者。你写了下列查询。
SELECT e.EmloyeeName AS [EmployeeName],
s.EmployeeName AS [SuperVisorName] FROM Employees e
你需要确认这个查询返回了所有的雇员和他们各自主管人的列表。你应使用哪个 JOIN 子句完成这个查询？
A. LEFT JOIN Employees s ON e.ReportsTo = s.EmployeeId B. RIGHT JOIN Employees s ON e.ReportsTo = s.EmployeeId C. INNER JOIN Employees s ON e.EmployeeId = s.EmployeeId D. LEFT JOIN Employees s ON e.EmployeeId = s.EmployeeId 答案：A
56 你有一个名为 Subcategories 的表，包含了短袜、女士背心和安全帽的子分类。另外一张表 Products 只包含短袜和女士背心子类的产品。你有下列查询：SELECT s.Name, p.Name AS ProductName FROM Subcategories s OUTER APPLY (SELECT * FROM Products pr
WHERE pr.SubcategoryID = s.SubcategoryID) p WHERE s.Name IS NOT NULL;
你需要预估查询结果。这个查询应生成哪个结果？ A. Name ProductName Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, M Socks Racing Socks, L Vests Classic Vest, S Vests Classic Vest, M Vests Classic Vest, L
B Name ProductName
Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, M Socks Racing Socks, L Vests Classic Vest, S Vests Classic Vest, M Vests Classic Vest, L Helmets NULL
C Name ProductName Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, M Socks Racing Socks, L Vests Classic Vest, S Vests Classic Vest, M Vests Classic Vest, L Helmets NULL NULL NULL
第 22
页
D Name ProductName Socks Mountain Bike Socks, Socks Mountain Bike Socks, Socks Racing Socks, M Socks Racing Socks, L Vests Classic Vest, S Vests Classic Vest, M Vests Classic Vest, L NULL Mountain Bike Socks, NULL Mountain Bike Socks, NULL Racing Socks, M NULL Racing Socks, L NULL Classic Vest, S NULL Classic Vest, M NULL Classic Vest, L Helmets NULL NULL NULL 答案：B
57 你有名为 dbo.CurrentProducts 和 dbo.ArchiveProducts 的两个表。你有下列查询： SELECT ProductID, Name FROM dbo.CurrentProducts UNION ALL SELECT ProductID, Name FROM dbo.ArchiveProducts;
你需要预估这个查询将生成的产品列表。这个查询应返回哪个产品列表
A. Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts but not in both. B. Products that have a matching ProductID and Name in dbo.CurrentProducts or dbo.ArchiveProducts.
C. Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts. Products that appear in both tables are listed only once.
D. Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts. Products that appear in both tables are listed multiple times.
(出现 dbo.CurrentProducts 和 dbo.ArchiveProducts 的两个表中的产品，并且两表都有的产品出现多次。) 答案：D
58 你有名为 Products 和 NewProducts 的两个结构相同的表。你有下列查询（ 你需要选择合适的 T-SQL
01 SELECT Product, Description 02 FROM dbo.Products 03
04 SELECT Product, Description 05 FROM dbo.NewProducts
运算操作符显示两表中都有的行。你应该在 03 行插入哪个操作运算符？ A. UNION B. EXCEPT C. UNION ALL D. INTERSECT 答案：D
59 你的任务是创建一个表，该表包含必须存储精确到 10 微秒的当前时间的一列。你需要为列定义设置默认值为系统函数。你应使用哪个系统函数？] A. DATEADD B. GETUTCDATE
C. SYSDATETIME D. CURRENT_TIMESTAMP
第 23 页
答案：C
60 你需要对 1.75 四舍五入到最接近的整数。你应使用哪个代码段？ 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
61 你有一个名为 TelephoneNumber 字段来存储 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
62 你 是 位 于 西 雅 图 的 数 据 库 开 发 人 员 ， 你 有 一 个 与 西 雅 图 不 同 时 区 的 墨 尔 本 客 户 端 。 你 准 备 使 用datetimeoffset 数据类型存储含西雅图时区的数据。你需要显示墨尔本的时间。你应使用哪个函数？
A. CONVERT B. DATEADD C. SWITCHOFFSET D. TODATETIMEOFFSET 答案：C
63 你有名为 ProductCategory 和 ProductSubCategory 的两个表。你需要写一个查询，返回包含超过十个子类的产品分类列表。你应使用哪个查询？ 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
64 你的数据库包含无数订单的销售信息。你需要确认某个订单总量超过 10,000 的最高平均售价的订单。 你应使用哪个查询？
A. SELECT TOP (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 TOP (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
第 24 页
WHERE o.Total> 10000 ORDER BY AvgUnitPrice DESC;
C. SELECT TOP (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 TOP (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
65 你的公司数据库中存储了卖主和价格的信息。数据库中所有数据项都有一个价格表价格。你需要给卖 主 Fabrikam 提供所有产品的价目表价格增加 20.00。你应使用哪个查询？
A. UPDATE Production.Product SET ListPrice = ListPrice + 20.00
WHERE NOT EXISTS (SELECT VendorId FROM Purchasing.Vendor); WHERE VendorName = 'Fabrikam');
B. UPDATE Production.Product SET ListPrice = ListPrice + 20.00
WHERE VendorId NOT IN (SELECT VendorId FROM Purchasing.Vendor); WHERE VendorName = 'Fabrikam');
C. UPDATE Production.Product SET ListPrice = ListPrice + 20.00 WHERE EXISTS (SELECT VendorId FROM Purchasing.Vendor WHERE VendorName = 'Fabrikam');
D. UPDATE Production.Product SET ListPrice = ListPrice + 20.00 WHERE VendorId IN (SELECT VendorId FROM Purchasing.Vendor WHERE VendorName = 'Fabrikam'); 答案：D
66 你有名为 Customer 和 SalesOrder 的两个表。你需要确认那些还没有任何购买和订购总量少于 100 的 所有客户。你应使用哪个查询？ 答案：
A.select * from Customer
WHERE 100 > ALL (SELECT OrderTotal FROM SalesOrder WHERE Customer.CustomerID = SalesOrder.CustomerID)
B. SELECT * FROM Customer
WHERE 100 > SOME (SELECT OrderTotal from SalesOrder WHERE Customer.CustomerID = SalesOrder.CustomerID)
C. SELECT * FROM Customer
WHERE 100 > (SELECT MAX(OrderTotal) FROM SalesOrder WHERE Customer.CustomerID = SalesOrder.CustomerID)
D. SELECT * FROM Customer
第 25 页