You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment.
(Line numbers are included for reference only.)

The procedure can be called within other transactions. You need to ensure that when the DELETE statement
from the HumanResourcesJobCandidate table succeeds,the modification is retained even if the insert into the
Audit.Log table fails. Which code segment should you add to line 14?

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

You use Microsoft SQL Server 2012 to develop a database application. Your application sends data to an
NVARCHAR(MAX) variable named @var. You need to write a Transact-SQL statement that will find out the
success of a cast to a decimal (36,9). Which code segment should you use?select

You are writing a set of queries against a FILESTREAM-enabled database. You create a stored procedure that
will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you
include at the beginning of the stored procedure?

You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The
tables are related by a column named CustomerID. You need to create a query that meets the following
requirements:
Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.
Results must include customers who have not placed any orders.
Which Transact-SQL query should you use?

You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if
the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which
Transact-SQL statement should you include at the beginning of the stored procedure?

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables
contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in
the two tables is distinct from one another. Business users want a report that includes aggregate information
about the total number of global sales and total sales amounts. You need to ensure that your query executes in
the minimum possible time. Which query should you use?

You are a database developer at an independent software vendor. You create stored procedures that contain
proprietary code. You need to protect the code frombeing viewed by your customers. Which stored procedure
option should you use?

You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word
documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which
Transact-SQL statement should you use?

You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal.
You write the following Transact-SQL query:
INSERT INTO OrdersHistorical
SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?