Online Access Free 070-461 Exam Questions

You create a view based on the following statement:

You grant the Select permission to User1 for this view.
You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

Correct Answer:

You are developing an employee management database in Microsoft SQL Server. The database contains two tables that are defined with the following Transact-SQL statements:

If the employee qualify for a bonus level they must receive a percentage of any sales.
Select the appropriate from below:

Correct Answer:

You develop a database for a travel application. You need to design tables and other database objects.
You create a view that displays the dates and times of the airline schedules on a report.
You need to display dates and times in several international formats.
What should you do?

• A.Use the DATETIME2 data type.
• B.Use a user-defined table type.
• C.Use the DATETIME data type.
• D.Use the CAST function.
• E.Use the VARBINARY data type.
• F.Use an appropriate collation.
• G.Use the DATETIMEOFFSET data type.
• H.Use the TODATETIMEOFFSET function.
• I.Use the DATE data type.
• J.Use the FORMAT function.

A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit.
You need to create a report that displays the profits made by each territory for each year and its previous year.
Which Transact-SQL query should you use?

• A.SELECT Territory, Year, Profit,
  LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
• B.SELECT Territory, Year, Profit,
  LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
• C.SELECT Territory, Year, Profit,
  LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
• D.SELECT Territory, Year, Profit,
  LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits

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.
Which Transact-SQL statement should you use?

• A.ALTER TABLE Inventory
  ADD TotalItems AS ItemsInStore + ItemsInWarehouse
• B.ALTER TABLE Inventory
  ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse);
• C.ALTER TABLE Inventory
  ADD ItemsInStore - ItemsInWarehouse = TotalItemss
• D.ALTER TABLE Inventory
  ADD TotalItems = ItemsInStore + ItemsInWarehouse