CORRECT TEXT
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:
Use one-part names to reference columns.
Return the columns in the same order as the order used in OrdersByTerritoryView.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete
code.

CORRECT TEXT
You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit
button.)

You need to create a query that meets the following requirements:
References columns by using one-part names only.
Groups aggregates by SalesTerritorylD, and then by ProductlD.
Orders the results in descending order by SalesTerritorylD and then by ProductlD.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete
code.

CORRECT TEXT
You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit
button).

You need to create a query for a report. The query must meet the following requirements:
NOT use object delimiters.
Use the first initial of the table as an alias.
Return the most recent order date for each customer.
Retrieve the last name of the person who placed the order.
The solution must support the ANSI SQL-99 standard.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete
code.

CORRECT TEXT
You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit
button.)

You need to create a query that returns a list of products from Sales.ProductCatalog. The solution
must meet the following requirements:
UnitPrice must be returned in descending order.
The query must use two-part names to reference the table.
The query must use the RANK function to calculate the results.
The query must return the ranking of rows in a column named PriceRank.
The list must display the columns in the order that they are defined in the table. PriceRank must
appear last.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete
code.

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 a table named Customer that has columns named CustomerID and Name.
You want to write a query that retrieves data from the Customer table sorted by Name listing 20
rows at a time.
You need to view rows 41 through 60.
Which Transact-SQL query should you create?

You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale of six digits.
Which data type should you use?

You develop a database application. You create four tables. Each table stores different categories of
products.
You create a Primary Key field on each table.
You need to ensure that the following requirements are met:

The fields must use the minimum amount of space.
The fields must be an incrementing series of values.
The values must be unique among the four tables.
What should you do?

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

DRAG DROP
You write the following SELECT statement to get the last order date for a particular customer.

You need to create the user-defined function to return the last order date for the specified
customer.
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.)