You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of the XML
type named XML1. The solution must ensure that XML1is validated by using Sales.InvoiceSchema.
Which code segment should you use?
To answer, type the correct code in the answer area.

You have a database that contains the tables as shown in the exhibit. (Refer to the Exhibit.)

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 thatthey are defined in the table.
PriceRank must appear last.
Which code segment should you use?
To answer, type the correct code in the answer area.

You support a database structure shown in the exhibit. (Refer to the Exhibit.)

You need to write a query that displays the following details:
Total sales made by sales people, year, city, and country
Sub totals only at the city level and country level
A grand total of the sales amount
Which Transact-SQL query should you use?

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 administer a Microsoft SQL Server database thatsupports a banking transaction management application.
You need to retrieve a list of account holders who live in cities that do not have a branch location. Which
Transact-SQL query or queries should you use? (Eachcorrect answer presents a complete solution. Choose all
that apply.)

You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part
of the Employee table is shown in the exhibit. (Refer to the Exhibit.)


Confidential information about the employees is stored in a separate table named EmployeeData. One record
exists within EmployeeData for each record in the Employee table. You need to assign the appropriate
constraints and table properties to ensure data integrity and visibility. On which column in the Employee table
should you a create a unique constraint?

You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part
of the Employee table is shown in the exhibit. (Refer to the Exhibit.)


Confidential information about the employees is stored in a separate table named EmployeeData. One record
exists within EmployeeData for each record in the Employee table. You need to assign the appropriate
constraints and table properties to ensure data integrity and visibility. On which column in the Employee table
should you use an identity specification to includea seed of 1,000 and an increment of 1?

You administer a Microsoft SQL Server 2012 databasethat includes a table named Products. The Products
table has columns named Productld, ProductName, andCreatedDateTime. The table contains a unique
constraint on the combination of ProductName and CreatedDateTime. You need to modify the Products table to
meet the following requirements:
Remove all duplicates of the Products table based on the ProductName column.
Retain only the newest Products row.
Which Transact-SQL query should you use?

You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You
have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named
dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other
databases. You need to ensure that callers that do not have permissions on Database1 or Database2 can
execute the stored procedure. Which Transact-SQL statement should you use?

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?