Free Microsoft 70-461 Exam Dumps Questions & Answers

You have a SQL database that contains a table named Products.
You are implementing a stored procedure that retrieves the list of products, performs custom business logic and then retrieve the list of products again.
The custom business logic in the stored procedure does not modify data from the Products table.
The stored procedure contains the following:

You need to complete line 01 of the stored procedure to ensure that when the transaction occurs, the data read from the SELECT * FROM Products statement on line 05 is identical to the data read from the SELECT * FROM Products statement on line 10. The solution must maximize concurrency.
Part of the correct Transact-SQL has been provided in the answer area 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 have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

You deploy a new server that has SQL Server 2012 installed. You need to create a table named Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:
* Write the results to a disk.
* Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row.
* The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table. Which code segment should you use?

• A.  CREATE TABLE Sales.OrderDetails (
  Quantity int not null,
  LineItemTotal as (ListPrice * Quantity) PERSISTED)
• B.  CREATE TABLE Sales.OrderDetails (
  ListPrice money not null,
  Quantity int not null,
  LineItemTotal as (ListPrice * Quantity) PERSISTED)

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

You need to ensure that Orders are added to the Orders table only for customers that have an account balance of zero.
How should you complete the relevant Transact-SQL statement? To answer, select the correct Transact-SQL statement from each list in the answer area.

Correct Answer:

Explanation:
The Transact SQL CREATE TRIGGER command creates a DML, DDL, or logon trigger. A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.
Partial syntax is:
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
Reference:
https://msdn.microsoft.com/en-us/library/ms189799.aspx

You develop a database for a travel application. You need to design tables and other database objects.
You need to store media files in several tables.
Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently.
What should you do?

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

You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data.
BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book.
You need to ensure that the new column is assigned a GUID for existing rows.
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:

Explanation:
Actually, in the real world, you don't have to use WITH VALUES at the end of the statement and it works just as well. But because the question specifically states which FOUR TSQL statements to use, we have to include it.