- 相關(guān)推薦
2017年sql認(rèn)證考試試題及答案
微軟認(rèn)證計(jì)劃進(jìn)行了全面升級(jí)，以涵蓋云技術(shù)相關(guān)的解決方案，并將此類技能的考評(píng)引入行業(yè)已獲得高度認(rèn)可和備受矚目的認(rèn)證考試體系，從而推動(dòng)整個(gè)行業(yè)向云計(jì)算時(shí)代進(jìn)行變革。下面是小編整理關(guān)于sql認(rèn)證考試試題及答案，歡迎大家參考!
第1題
你使用下面的Transact-SQL語句創(chuàng)建并填充兩個(gè)表： 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); 你運(yùn)行下面的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表中更新、插入和刪除的行的總數(shù)。應(yīng)選擇哪個(gè)總行數(shù)?
A. 0
B. 3
C. 6
D. 9
答案：B
第2題
你要編寫一個(gè)查詢來返回2007年度總收入超過$10,000.00的產(chǎn)品的列表。你需要將下面的篩選表達(dá)式插入查詢。 SUM([Order Details].UnitPrice * [Order Details].Quantity) > 10000 應(yīng)將此表達(dá)式插入哪個(gè)子句?
A. ON
B. WHERE
C. HAVING
D. GROUP BY
答案：C
第3題
你有一個(gè)名為Sales的表。你的任務(wù)是列出只賣給不超過10家客戶的產(chǎn)品。你需要編寫查詢來實(shí)現(xiàn)此任務(wù)。應(yīng)使用哪 一條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的兩個(gè)表。對(duì)于至少下過一筆訂單的客戶，你需要生成一份清單列出客戶名稱以及每個(gè) 客戶的訂單數(shù)。應(yīng)使用哪個(gè)查詢?
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題
你有一個(gè)名為Products的表。該表包含名為Color的列。你需要編寫一條Transact-SQL語句來計(jì)算每種顏色的產(chǎn)品占所
有產(chǎn)品的百分比。應(yīng)使用哪一條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
【sql認(rèn)證考試試題及答案】相關(guān)文章：
2017年sql認(rèn)證考試練習(xí)試題09-07
Adobe認(rèn)證考試題及答案06-25
java認(rèn)證考試試題及答案07-21
華為認(rèn)證考試試題及答案08-20
Adobe photoshop認(rèn)證考試試題及答案09-13
Linux認(rèn)證考試課后基礎(chǔ)試題及答案07-18
linux認(rèn)證考試筆試題及答案01-24
微軟認(rèn)證考試試題及答案「經(jīng)典版」08-21
ccf認(rèn)證考試試題答案10-10