You are developing a database application by using Microsoft SQL Server 2012. An application that usesa
database begins to run slowly. Your investigation shows the root cause is a query against a read-only table that
has a clustered index. The query returns the following six columns:
One column in its WHERE clause contained in a non-clustered index Four additional columns
One COUNT (*) column based on a grouping of the four additional columns You need to optimize the
statement.
What should you do?

You administer a Microsoft SQL Server 2012 databasenamed ContosoDb. Tables are defined as shown in the
exhibit. (Refer to the Exhibit.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in
the following XML format.

Which Transact-SQL query should you use?

You administer a Microsoft SQL Server 2012 databasenamed ContosoDb. Tables are defined as shown in the
exhibit. (Refer to the Exhibit.)

You need to display rows from the Orders table for the Customers row having the Customerld value set to 1 in
the following XML format.

Which Transact-SQL query should you use?

You use Microsoft SQL Server 2012 to write code fora transaction that contains several statements. There is
high contention between readers and writers on several tables used by your transaction. You need to minimize
the use of the tempdb space. You also need to prevent reading queries from blocking writing queries. Which
isolation level 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 ensure that the top
half of the students arranged by their average marks must be given a rank of 1 and the remaining students
must be given a rank of 2. Which Transact-SQL queryshould 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 ensure that the
following requirements are met:
Students must be ranked based on their average marks.
If one or more students have the same average, the same rank must be given to these students.
Consecutive ranks must be skipped when the same rank is assigned.
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 alongwith the marks. Which Transact-SQL query should you
use?

You develop a database for a travel application. You need to design tables and other database objects.You
create the Airline_Schedules table. You need to store the departure and arrival dates and times of flights along
with time zone information. What should you do?

You develop a database for a travel application. You need to design tables and other database objects.You
create a stored procedure. You need to supply the stored procedure with multiple event names and theirdates
as parameters. What should you do?

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 accessingthe 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 applications.
What should you create for each application?