You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part
of the Employee table is shown in the exhibit. (Refer to the Exhibit.)


Confidential information about the employees is stored in a separate table named EmployeeData. One record
exists within EmployeeData for each record in the Employee table. You need to assign the appropriate
constraints and table properties to ensure data integrity and visibility. On which column in the Employee table
should you use an identity specification to includea seed of 1,000 and an increment of 1?

You administer a Microsoft SQL Server 2012 databasethat includes a table named Products. The Products
table has columns named Productld, ProductName, andCreatedDateTime. 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 newest Products row.
Which Transact-SQL query should you use?

You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You
have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named
dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other
databases. You need to ensure that callers that do not have permissions on Database1 or Database2 can
execute the stored procedure. Which Transact-SQL statement should you use?

You develop 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 not include customers who have not placed any orders.
Which Transact-SQL query should you use?

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?