SIMULATION
You have a database that contains the following tables.

You need to create a query that lists all complaints from the Complaints table, and the name of the person
handling the complaints if a person is assigned. The ComplaintID must be displayed first, followed by the
person name.
Construct the query using the following guidelines:Use two-part column names.
Use one-part table names.
Do not use aliases for column names or table names.
Do not use Transact-SQL functions.
Do not use implicit joins.
Do not surround object names with square brackets.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer
area that resolves the problem and meets the stated goals or requirements. You can add code within the code
that has been provided as well as below it.

Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and
character position.

SIMULATION
You have a database that contains the following tables.

You need to create a query that lists the lowest-performing salespersons based on the current year-to-date
sales period. The query must meet the following requirements:
Return a column named Fullname that includes the salesperson FirstName, a space, and then LastName.
Include the current year-to-date sales for each salesperson.
Display only data for the three salespersons with the lowest year-to-year sales values.
Exclude salespersons that have no value for TerritoryID.
Construct the query using the following guidelines:
Use the first letter of a table name as the table alias.
Use two-part column names.
Do not surround object names with square brackets.
Do not use implicit joins.Use only single quotes for literal text.
Use aliases only if required.

Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer
area that resolves the problem and meets the stated goals or requirements. You can add code within the code
that has been provided as well as below it.

Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and
character position.

SIMULATION
You create a table named Sales.Orders by running the following Transact-SQL statement:

You need to write a query that meets the following requirements:
removes orders from the table that were placed before January 1, 2012
uses the date format of YYYYMMDD
ensures that the order has been shipped before deleting the record
Construct the query using the following guidelines:
use one-part column names and two-part table names
do not use functions
do not surround object names with square brackets
do not use variables
do not use aliases for column names and table names

Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer
area that resolves the problem and meets the stated goals or requirements. You can add code within the code
that has been provided as well as below it.

Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and
character position.

HOTSPOT
You run the following Transact-SQL statement:

You need to ensure that you can insert data into the table.
What are the characteristics of the data? To answer, select the appropriate options in the answer area.
Hot Area:

DRAG DROP
You have a database that includes the following tables:

You need to create a list of all customer IDs and the date of the last order that each customer placed. If the
customer has not placed any orders, you must return the date January 1, 1900. The column names must be
CustomerID and LastOrderDate.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate
Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the
correct order.
Select and Place:

HOTSPOT
You have the following Transact-SQL query:SELECT
City.CityID,
City.CityName,
TranslateName(Nearby.CityName) AS NearbyCity
FROM Cities AS City
CROSS APPLY NearbyCities(City.CityID) AS Nearby
What type of functions are used in the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

DRAG DROP
You have a table named HR.Employees as shown in the exhibit. (Click the exhibit button.)

You need to write a query that will change the value of the job title column to Customer Representative for any
employee who lives in Seattle and has a job title of Sales Representative. If the employee does not have a
manager defined, you must not change the title.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate
Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the
correct order.
Select and Place:

SIMULATION
You work for an organization that monitors seismic activity around volcanos. You have a table named
GroundSensors. The table stored data collected from seismic sensors. It includes the columns describes in the
following table:

The database also contains a scalar value function named NearestMountain that returns the name of the
mountain that is nearest to the sensor.You need to create a query that shows the average of the normalized readings from the sensors for each
mountain. The query must meet the following requirements:
Include the average normalized readings and nearest mountain name.
Exclude sensors for which no normalized reading exists.
Exclude those sensors with value of zero for tremor.
Construct the query using the following guidelines:
Use one part names to reference tables, columns and functions.
Do not use parentheses unless required.
Do not use aliases for column names and table names.
Do not surround object names with square brackets.

Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer
area that resolves the problem and meets the stated goals or requirements. You can add code within the code
that has been provided as well as below it.

Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and
character position.

DRAG DROP
You have two tables named UserLogin and Employee respectively.
You need to create a Transact-SQL script that meets the following requirements:
The script must update the value of the IsDeleted column for the UserLogin table to1if the value of the Id
column for the UserLogin table is equal to1.
The script must update the value of the IsDeleted column of the Employee table to1if the value ofthe Id
column is equal to1for the Employee table when an update to the UserLogin table throws an error.
The error message “No tables updated!” must be produced when an update to the Employee table throws
an error.
Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate
Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the
correct order.
Select and Place:

You need to create an indexed view that requires logic statements to manipulate the data that the view displays.
Which two database objects should you use? Each correct answer presents a complete solution.