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?

You want to add a new GUID column named BookGUID toa 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 statementsto
the answer area and arrange them in the correct order.)

You create a view based on the following statement:

You grant the Select permission to User1 for this view. You need to change the view so that it display s only the
records that were processed in the month prior to the current month. You need to ensure that after the
changes, the view functions correctly for User1. 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 arrangethem
in the correct order.)

You use a Microsoft SQL Server 2012 database. You need to create an indexed view within the database for a
report that displays Customer Name and the total revenue for that customer. Which four T-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. You use an OrderDetail table that has the following
definition:

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only
rows that contain a value in the SpecialOfferID column. Which four Transact-SQL statements should you use?
(To answer, move the appropriate statements from the list of statements to the answer area and arrangethem
in the correct order.)