You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the
following requirements:
Status information must be logged to a status table.
If the status table does not exist at the beginningof the batch, it must be created.
Which object should you use?

You administer a database that includes a table named Customers that contains more than 750 rows. You
create a new column named PartitionNumber of the int type in the table. You need to assign a PartitionNumber
for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies thefollowing
conditions:
Always starts with 1.
Starts again from 1 after it reaches 100.
Which Transact-SQL statement should you use?

You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following
columns:

Id is the Primary Key.
You need to append the “This is in a draft stage” string to the Summary column of the recent 10 entries based
on the values in EntryDateTime. Which Transact-SQL statement should you use?

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named
DeleteJobCandidate. You need to ensure that if DeleteJobCandidate encounters an error, the execution of the
stored procedure reports the error number. Which Transact-SQL statement should you use?

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?