微软认证sql考试练习试题及答案

2016-09-27 00:00:00少芬 微软认证

  第1题

  公司在数据库中存储供应商和价格信息。数据库中的所有商品都有牌价(list price)。 你需要只将供应商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

  第2题

  你有名为Customer和SalesOrder的两个表。 你需要找出尚未采购任何东西的所有客户, 以及那些订单总额 (OrderTotal) 不到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 WHERE EXISTS ( SELECT SalesOrder.CustomerID FROM SalesOrder WHERE Customer.CustomerID = SalesOrder.CustomerID AND SalesOrder.OrderTotal <= 100)

  答案:A

  第3题

  你有名为Customer和SalesOrder的两个表。Customer表中有1000个客户,SalesOrder表中有其中900个客户的订单。你 执行下面的查询来列出至少有一笔销售的所有客户。 SELECT * FROM Customer WHERE Customer.CustomerID IN (SELECT Customer.CustomerID FROM SalesOrder)

  你需要确定查询的结果。该查询应返回什么结果?

  A. 没有行

  B. 警告消息

  C. Customer表中的1000行

  D. Customer表中与SalesOrder表中的行匹配的900行。

  答案:C

  第4题

  Customer表中有下面的行:CustomerId Status 1 Active 2 Active 3 Inactive 4 NULL 5 Dormant 6 Dormant 你编写了下面 的查询来返回状态不为NULL或“Dormant”的所有客户: SELECT * FROM Customer WHERE Status NOT IN (NULL, ’Dormant’) 你需要确定查询的结果。你预计应出现哪个结果?

  A. CustomerId Status

  B. CustomerId Status 1 Active 2 Active 3 Inactive

  C. CustomerId Status 1 Active 2 Active 3 Inactive 4 NULL

  D. CustomerId Status 1 Active 2 Active 3 Inactive 4 NULL 5 Dormant 6 Dormant

  答案:B

  第5题

  你有一个名为Employee的表。 你将每个员工的经理EmployeeID插入ReportsTo列来记录公司的组织层次结构。你需要 编写递归查询来生成员工及其经理的列表。 该查询还必须包含员工在层次结构中的级别。 你编写了下面的代码片段。 (包含的行号仅供参考。) 01 WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)02 AS (03 ......... 04 )05 SELECT EmployeeID, FullName, ManagerName, Level06 FROM EmployeeList; 应在第3行插入哪一个代码片段?

  A. SELECT EmployeeID, FullName, ’’ AS [ReportsTo], 1 AS [Level] FROM Employee WHERE ReportsTo IS NULL UNION ALL SELECT emp.EmployeeID, emp.FullNName, mgr.FullName, 1 + 1 AS [Level] FROM Employee emp JOIN Employee mgr ON emp.ReportsTo = mgr.EmployeeID

  B. SELECT EmployeeID, FullName, ’’ AS [ReportsTo], 1 AS [Level] FROM Employee WHERE ReportsTo IS NULL UNION ALL SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1 FROM EmployeeList mgr JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeId

  C. SELECT EmployeeID, FullName, ’’ AS [Reports To], 1 AS [Level] FROM Employee UNION ALL SELECT emp.EmployeeID, emp.FullName, mgr.FullName, 1 + 1 AS [Level] FROM Employee emp LEFT JOIN Employee mgr ON emp.ReportsTo = mgr.EmployeeID

  D. SELECT EmployeeID, FullName, ’’ AS [ReportsTo], 1 AS [Level] FROM Employee UNION ALL SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1 FROM EmployeeList mgr JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeID

  答案:B

[微软认证]相关推荐

[微软认证]相关栏目推荐
查看更多
上一篇:微软认证经典习题及答案2016 下一篇:NtServer中层企业级试题及答案