You administer a Microsoft SQL Server 2012 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the
following statements:

You attempt to restore the Orders database and the restore fails. You copy the encryption
file to the original location.
A hardware failure occurs and so a new server must be installed and configured.
After installing SQL Server to the new server, you restore the Orders database and copy
the encryption files to their original location. However, you are unable to access the
database.
You need to be able to restore the database.
Which Transact-SQL statement should you use before attempting the restore?

You administer a Microsoft SQL Server database named Sales. The database is 3
terabytes in size.
The Sales database is configured as shown in the following table.

You discover that all files except Sales_2.ndf are corrupt.
You need to recover the corrupted data in the minimum amount of time.
What should you do?

You are developing a database application by using Microsoft SQL Server 2012.
You have a query that runs slower than expected.
You need to capture execution plans that will include detailed information on missing
indexes recommended by the query optimizer.
What should you do?

You administer a Microsoft SQL Server 2012 database.
You create an availability group named haContosoDbs. Your primary replica is available at
Server01\Contoso01.
You need to configure the availability group to have the highest availability. You also need
to ensure that no data is lost.
Which Transact-SQL statement should you use?

You administer a single server that contains a Microsoft SQL Server 2012 default instance.
You plan to install a new application that requires the deployment of a database on the
server. The application login requires sysadmin permissions.
You need to ensure that the application login is unable to access other production
databases.
What should you do?

You use Microsoft SQL Server 2012 to develop a database application.
You create two tables by using the following table definitions.

Which five 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.)

Your application contains a stored procedure for each country. Each stored procedure
accepts an employee identification number through the @EmpID parameter.
You plan to build a single process for each employee that will execute the stored procedure
based on the country of residence.
Which approach should you use?

You have a view that was created by using the following code:

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory.
Sales.fn_OrdersByTerritory must meet the following requirements:
*Accept the @T integer parameter.
*Use one-part names to reference columns.
*Filter the query results sorted by SalesTerritoryID.
*Return the columns in the same order as the order used in OrdersByTerritoryView.
Which code segment should you use?
To answer, type the correct code in the answer area.

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.)

You administer a Microsoft SQL Server 2012 database named Contoso on a server named
Server01.
You need to write messages to the Application Log when users are added to or removed
from a fixed server role in Server01.
What should you create?

You develop a Microsoft SQL Server 2012 database. The database is used by two web
applications that access a table named Products.
You want to create an object that will prevent the applications from accessing the table
directly while still providing access to the required data.
You need to ensure that the following requirements are met:
Future modifications to the table definition will not affect the applications' ability to access
data.
The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the existing
applications.
What should you create for each application?

Your database contains tables named Products and ProductsPriceLog. The Products table
contains columns named ProductCode and Price. The ProductsPriceLog table contains
columns named ProductCode, OldPrice, and NewPrice.
The ProductsPriceLog table stores the previous price in the OldPrice column and the new
price in the NewPrice column.
You need to increase the values in the Price column of all products in the Products table by
5 percent. You also need to log the changes to the ProductsPriceLog table.
Which Transact-SQL query should you use?

You create a table that has the StudentCode, SubjectCode, and Marks columns to record
mid-year marks for students. The table has marks obtained by 50 students for various
subjects.
You need to retrieve the students who scored the highest marks for each subject along with
the marks.
Which Transact-SQL query should you use?

You administer a Microsoft SQL Server 2012 database.
The database contains a table that has the following definition:

You want to export data from the table to a flat file by using the SQL Server Import and
Export Wizard.
You need to ensure that the following requirements are met:
✑ The first row of the file contains the first row of data.
✑ Each record is of the same length.
✑ The date follows the U.S. date format.
✑ The file supports international characters.
What should you do? (To answer, configure the appropriate option or options in the dialog
box in the answer area.)

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?