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?

You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that
meets the following requirements:
Takes an input variable
Returns a table of values
Cannot be referenced within a view
Which object should you use?

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database
contains two tables that have the following definitions:

Global customers place orders from several countries. You need to view the country from which each customer
has placed the most orders. Which Transact-SQL query do you use?