Microsoft Querying Microsoft SQL Server 2012/2014 - 070-461 FREE EXAM DUMPS QUESTIONS & ANSWERS

You are maintaining a Microsoft SQL Server database. You run the following query:

You observe performance issues when you run the query. You capture the following query execution plan:

You need to ensure that the query performs returns the results as quickly as possible.
Which action should you perform?

You use a Microsoft SQL Server 2012 database.
You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer.
Which four T-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.)

You use Microsoft SQL Server 2012 to develop a database application.
You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9)
)
You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index.
Which three 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.)

You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition.

You need to ensure that users are able to modify data by using the view.
What should you do?

You need to execute an UPDATE statement that modifies a subset of rows in a table. The rows affected by the UPDATE statement must only be committed if the row count criteria is met.
Part of the correct Transact-SQL has been provided in the answer are below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.


Use the 'Check Syntax' button to verify your work. Any syntax or spelling errors will be reported by line and character position.

You are designing an order entry system that uses an SQL Server database. The following tables exist in the Purchasing database:

You create the following trigger. Line numbers are included for reference only.

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

You administer a Microsoft SQL Server database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime.
The table contains a unique constraint on the combination of ProductName and CreatedDateTime.
You need to modify the Products table to meet the following requirements:
* Remove all duplicates of the Products table based on the ProductName column.
* Retain only the oldest Products row.
Which Transact-SQL query should you run?

You use a Microsoft Azure SQL Database instance named ContosoDb. ContosoDb contains a table named Products that has existing records.
The Products table has columns named Code and QuantityOnHand.
You need to create a new column in the Products table named Category that allows null values and sets the value of the Category column to General for all existing records.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

You have a database that contains the tables as shown below:

You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column is changed to datetime. You need to update the SELECT statement to meet the following requirements:
The code must NOT use aliases.
The code must NOT use object delimiters.
The objects called in Procedure1 must be able to be resolved by all users.
OrderDate must be compared to Parameter1 after the data type of Parameter1 is changed to datetime.
Which SELECT statement should you use?
To answer, type the correct code in the answer area.

You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.
What should you do?

Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column.
The business team wants a report that displays the total number of purchases made on the current day.
You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?

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

You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements:
* Use one-part names to reference columns.
* Sort the order of the results from OrderId.
* NOT depend on the default schema of a user.
* Use an alias of TotalSales for the calculated ExtendedAmount.
* Display only the OrderId column and the calculated TotalSales column.
Which code segment should you use?
To answer, type the correct code in the answer area.