Microsoft 70-461 Exam Questions

Question: 16

You work as a database administrator at manufacturing company named ABC.com. ABC.com has a SQL Server 2012 database named ProductionDB. The ProductionDB database has a table named Sites that was created using the following Transact-SQL code:CREATE TABLE Sites (SiteID int NOT NULL PRIMARY KEY,Location int NOT NULL,Manager nvarchar(200) NOT NULL,Week smallint NOT NULL,ItemsProduced int NOT NULL )You want to write the Transact-SQL query that returns the number of items produced at each location for each week. In addition, you want the result set to include a column named PrevItemsProduced that holds the number of items produced at each location for the week before.What Transact SQL statement would accomplish this task?

A. SELECT Location, Week, ItemsProduced,LEAD(Items Produced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) ASPrevItems Produced FROM Sites

B. SELECT Location, Week, ItemsProduced,FIRST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) ASPrevItems Produced FROM Sites

C. SELECT Location, Week, ItemsProduced,LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) ASPrevItemsProduced FROM Sites

D. SELECT Location, Week, ItemsProduced,LAST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) ASPrevItemsProduced FROM Sites

E. SELECT Location, Week, ItemsProduced,CUME_DIST( ) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced FROM Sites

Answer: C