2017年sql认证考试试题及答案
微软认证计划进行了全面升级，以涵盖云技术相关的解决方案，并将此类技能的考评引入行业已获得高度认可和备受瞩目的认证考试体系，从而推动整个行业向云计算时代进行变革。下面是小编整理关于sql认证考试试题及答案，欢迎大家参考!
第1题
你使用下面的Transact-SQL语句创建并填充两个表： CREATE TABLE CurrentStudents (LastName VARCHAR(50), FirstName VARCHAR(50),Address VARCHAR(100), Age INT);INSERT INTO CurrentStudents VALUES (’Fritz’, ’David’, ’181 Kline Street’, 14),(’Reese’, ’Paul’ , ’4429 South Union’, 14),(’Brown’, ’Jake’ , ’5401 Washington Ave’,14),(’Smith’, ’Tom’ , ’124 Water St’, 14),(’Holtz’, ’Mary’ , ’984 Mass Ct’, 14),(’Robbins’, ’Jan’ , ’4449 Union Ave’, 14),(’Larsen’, ’Frank’ , ’5812 Meadow St’, 14),(’Bishop’, ’Cathy’ , ’14429 Skyhigh Ave’, 14),(’Francis’, ’Thomas’ , ’15401 120th St’, 14)CREATE TABLE NewYearRoster(LastName VARCHAR(50), FirstName VARCHAR(50), AddressVARCHAR(100), Age INT);INSERT INTO NewYearRoster VALUES (’Fritz’, ’David’, ’181 Kline Street’, 15),(’Reese’, ’Paul’, ’1950 Grandview Place’, 15),(’Adams’, ’Wilbur’, ’4231 W. 93rd’, 15),(’Adams’, ’Norris’, ’100 1st Ave’, 15),(’Thomas’, ’Paul’, ’18176 Soundview Dr’, 15),(’Linderson’, ’Danielle’, ’941 W. 37 Ave’, 15),(’Moore’, ’Joshua’, ’2311 10st Ave’, 15),(’Dark’, ’Shelby’, ’1987 Fifth Ave’, 15),(’Scharp’, ’Mary’, ’1902 W. 303rd’, 15),(’Morris’, ’Walt’, ’100 12st St’, 15); 你运行下面的MERGE语句在CurrentStudents表中更新、插入和删除行。 MERGE TOP (3) CurrentStudents AS TUSING NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T. FirstName WHEN MATCHED AND NOT (T.Age = S.Age OR T.Address = S.Address) THEN UPDATE SETAddress = S.Address, Age = S.AgeWHEN NOT MATCHED BY TARGET THEN INSERT (LastName, FirstName, Address, Age)VALUES (S.LastName, S.FirstName, S.Address, S.Age)WHEN NOT MATCHED BY SOURCE THEN DELETE; 你需要确定CurrentStudent表中更新、插入和删除的行的总数。应选择哪个总行数?
A. 0
B. 3
C. 6
D. 9
答案：B
第2题
你要编写一个查询来返回2007年度总收入超过$10,000.00的产品的列表。你需要将下面的筛选表达式插入查询。 SUM([Order Details].UnitPrice * [Order Details].Quantity) > 10000 应将此表达式插入哪个子句?
A. ON
B. WHERE
C. HAVING
D. GROUP BY
答案：C
第3题
你有一个名为Sales的表。你的任务是列出只卖给不超过10家客户的.产品。你需要编写查询来实现此任务。应使用哪 一条Transact-SQL语句?
A. SELECT ProductID, COUNT(*) AS CustomerCount FROM Sales GROUP BY ProductID, CustomerID HAVING COUNT(*) < 10;
B. SELECT ProductID, COUNT(DISTINCT CustomerID) AS CustomerCount FROM Sales GROUP BY ProductID HAVING COUNT(DISTINCT CustomerID) < 10;
C. SELECT ProductID, CustomerID, COUNT(DISTINCT CustomerID) AS CustomerCount FROM Sales GROUP BY ProductID, CustomerID HAVING COUNT(DISTINCT CustomerID) < 10;
D. SELECT * FROM (SELECT ProductID, RANK() OVER (ORDER BY CustomerID DESC) AS Rnk FROM Sales) s WHERE s.Rnk <= 10;
答案：B
第4题
你有名为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 c JOIN Orders o ON c.CustomerId = o.CustomerId GROUP BY c.CustomerName
答案：D
第5题
你有一个名为Products的表。该表包含名为Color的列。你需要编写一条Transact-SQL语句来计算每种颜色的产品占所
有产品的百分比。应使用哪一条Transact-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
【2017年sql认证考试试题及答案】相关文章：