- 相關(guān)推薦
微軟認(rèn)證sql考試練習(xí)試題及答案
第1題
公司在數(shù)據(jù)庫(kù)中存儲(chǔ)供應(yīng)商和價(jià)格信息。數(shù)據(jù)庫(kù)中的所有商品都有牌價(jià)(list price)。 你需要只將供應(yīng)商Fabrikam的所有產(chǎn)品的牌價(jià)提高20.00。應(yīng)使用哪個(gè)查詢?
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的兩個(gè)表。 你需要找出尚未采購(gòu)任何東西的所有客戶， 以及那些訂單總額 (OrderTotal) 不到100的客戶。應(yīng)使用哪個(gè)查詢?
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的兩個(gè)表。Customer表中有1000個(gè)客戶，SalesOrder表中有其中900個(gè)客戶的訂單。你 執(zhí)行下面的查詢來(lái)列出至少有一筆銷售的所有客戶。 SELECT * FROM Customer WHERE Customer.CustomerID IN (SELECT Customer.CustomerID FROM SalesOrder)
你需要確定查詢的結(jié)果。該查詢應(yīng)返回什么結(jié)果?
A. 沒(méi)有行
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 你編寫(xiě)了下面 的查詢來(lái)返回狀態(tài)不為NULL或“Dormant”的所有客戶： SELECT * FROM Customer WHERE Status NOT IN (NULL, ’Dormant’) 你需要確定查詢的結(jié)果。你預(yù)計(jì)應(yīng)出現(xiàn)哪個(gè)結(jié)果?
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題
你有一個(gè)名為Employee的表。 你將每個(gè)員工的經(jīng)理EmployeeID插入ReportsTo列來(lái)記錄公司的組織層次結(jié)構(gòu)。你需要 編寫(xiě)遞歸查詢來(lái)生成員工及其經(jīng)理的列表。 該查詢還必須包含員工在層次結(jié)構(gòu)中的級(jí)別。 你編寫(xiě)了下面的代碼片段。 (包含的行號(hào)僅供參考。) 01 WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)02 AS (03 ......... 04 )05 SELECT EmployeeID, FullName, ManagerName, Level06 FROM EmployeeList; 應(yīng)在第3行插入哪一個(gè)代碼片段?
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
【微軟認(rèn)證sql考試練習(xí)試題及答案】相關(guān)文章：
微軟認(rèn)證考試試題及答案「經(jīng)典版」08-21
2017年sql認(rèn)證考試練習(xí)試題09-07
2017微軟認(rèn)證考試aspnet模擬練習(xí)及答案08-11
2017微軟認(rèn)證考試企業(yè)技術(shù)模擬練習(xí)及答案06-28
2017微軟認(rèn)證考試flash練習(xí)題及答案06-16