Microsoft Querying Microsoft SQL Server 2012/2014 : 070-461

You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button.)

You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure.
You need to create a stored procedure named usp_Customers for App1 that returns only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN, END, or DECLARE statements.
Part of the correct Transact-SQL statement has been provided in the answer area. Complete the Transact-SQL statement

• A. CREATE PROCEDURE usp_Customers #Count int
  SELECT TOP(Count)
  Customers.LastName
  FROM Customers
  ORDER BY Customers.LastName
• B. CREATE PROCEDURE usp_Customers @Count int
  SELECT TOP(@Count)
  Customers.LastName
  FROM Customers
  ORDER BY Customers.LastName

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.
You need to retrieve the students who scored the highest marks for each subject along with the marks.
Which Transact-SQL query should you use?

• A. SELECT StudentCode AS Code, Marks AS Value FROM (
  SELECT StudentCode, Marks AS Marks,
  RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• B. SELECT StudentCode AS Code, Marks AS Value FROM (
  SELECT StudentCode, Marks AS Marks,
  RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• C. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
• D. SELECT StudentCode AS Code, Marks AS Value FROM (
  SELECT StudentCode, Marks AS Marks,
  RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• E. SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
• F. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks
• G. SELECT StudentCode AS Code, Marks AS Value FROM (
  SELECT StudentCode, Marks AS Marks,
  RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• H. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode

You develop a Microsoft SQL Server 2012 server database that supports an application.
The application contains a table that has the following definition:
CREATE TABLE Inventory (
ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row.
The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use?

• A. ALTER TABLE Inventory
  ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED
• B. ALTER TABLE Inventory
  ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)
• C. ALTER TABLE Inventory
  ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED
• D. ALTER TABLE Inventory
  ADD TotalItems AS ItemslnStore + ItemsInWarehouse

You develop a Microsoft SQL Server 2012 database.
You need to create and call a stored procedure that meets the following requirements:
* Accepts a single input parameter for CustomerID.
* Returns a single integer to the calling application.
Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)

• A. CREATE PROCEDURE dbo.GetCustomerRating
  @CustomerID INT,
  @CustomerRating INT OUTPUT
  AS
  SET NOCOUNT ON
  SELECT @Result = CustomerOrders/CustomerValue
  FROM Customers
  WHERE CustomerID = @CustomerID
  RETURN @Result
  GO
• B. CREATE PROCEDURE dbo.GetCustomerRating
  @CustomerID INT,
  @CustomerRating INT OUTPUT
  AS
  SET NOCOUNT ON
  SELECT @CustomerRating = CustomerOrders/CustomerValue
  FROM Customers
  WHERE CustomerID = @CustomerID
  RETURN
  GO
• C. DECLARE @CustomerRatingByCustomer INT
  DECLARE @Result INT
  EXECUTE @Result = dbo.GetCustomerRating
  1745,
  @CustomerRatingByCustomer
• D. DECLARE @CustomerRatingByCustomer INT
  EXECUTE dbo.GetCustomerRating
  @CustomerID = 1745,
  @CustomerRating = @CustomerRatingByCustomer OUTPUT
• E. CREATE PROCEDURE dbo.GetCustomerRating
  @CustomerID INT
  AS
  DECLARE @Result INT
  SET NOCOUNT ON
  SELECT @Result = CustomerOrders/CustomerValue
  FROM Customers
  WHERE CustomerID = @CustomerID
  RETURNS @Result
  GO
• F. EXECUTE dbo.GetCustomerRating 1745

You need to create a view named uv_CustomerFullNames. The view must prevent the underlying structure of the customer table from being changed.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

• A. CREATE VIEW sales.uv_CustomerFullNames
  WITH SCHEMABINDING
  AS SELECT
  FirstName,
  FROM Sales.Customers
• B. CREATE VIEW sales.uv_CustomerFullNames
  WITH SCHEMABINDING
  AS SELECT
  FirstName,
  LastName
  FROM Sales.Customers