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 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:

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 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:

DRAG DROP
You need to create a stored procedure to update a table named Sales.Customers. The structure of the table is
shown in the exhibit. (Click the exhibit button.)

The stored procedure must meet the following requirements:
Accept two input parameters.
Update the company name if the customer exists.
Return a custom error message if the customer does not exist.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.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders
you select.
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.

HOTSPOT
You need to develop a Transact-SQL statement that meets the following requirements:
The statement must return a custom error when there are problems updating a table.
The error number must be value50555.
Theerror severity level must be14.
A Microsoft SQL Server alert must be triggered when the error condition occurs.
Which Transact-SQL segment should you use for each requirement? To answer, select the appropriate
Transact-SQL segments in the answer area.
Hot Area:

DRAG DROP
Note: This question is part of a series of questions that use the same scenario. For your convenience,
the scenario is repeated in each question. Each question presents a different goal and answer choices,
but the text of the scenario is exactly the same in each question in this series.
You query a database that includes two tables: Project and Task. The Project table includes the following
columns:

Task level is defined using the following rules:

You need to determine the task level for each task in the hierarchy.
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:

DRAG DROP
Note: This question is part of a series of questions that use the same scenario. For your convenience,
the scenario is repeated in each question. Each question presents a different goal and answer choices,
but the text of the scenario is exactly the same in each question in this series.
You query a database that includes two tables: Project and Task. The Project table includes the following
columns:

When running an operation, you updated a column named EndTime for several records in the Project table, but
updates to the corresponding task records in the Task table failed.You need to synchronize the value of the EndTime column in the Task table with the value of the EndTime
column in the project table. The solution must meet the following requirements:
If the EndTime column has a value, make no changes to the record.
If the value of the EndTime column is null and the corresponding project record is marked as completed,
update the record with the project finish time.
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:

DRAG DROP
You need to create a stored procedure that meets the following requirements:
Produces a warning if the credit limit parameter is greater than 7,000
Propagates all unexpected errors to the calling process
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQP
segments to the correct locations. Each Transact-SQL segments may be used once, more than once, or not at
all. You may need to drag the split bar between panes or scroll to view content.
Select and Place: