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:

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:

HOTSPOT
You have the following stored procedure:

You run the following Transact-SQL statements:

What is the result of each Transact-SQL statement? To answer, select the appropriate options in the answer
area.
Hot Area:

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:

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

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

You need to identify the owner of each task by using the following rules:
Return each task’s owner if the task has an owner.
If a task has no owner, but is associated with a project that has an owner, return the project’s owner.
Return the value-1for all other cases.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL
segments in the answer area.
Hot Area:

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:

You plan to run the following query to update tasks that are not yet started:
UPDATE Task SET StartTime = GETDATE() WHERE StartTime IS NULL
You need to return the total count of tasks that are impacted by this UPDATE operation, but are not associated
with a project.
What set of Transact-SQL statements should you run?

Note: This question is part of a series of questions that present the same scenario. Each question in
the series contains a unique solution that might meet the stated goals. Some question sets might have
more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these
questions will not appear in the review screen.
You have a database that tracks orders and deliveries for customers in North America. The database contains
the following tables:
Sales.Customers

Application.Cities

Sales.CustomerCategories

The company’s development team is designing a customer directory application. The application must list
customers by the area code of their phone number. The area code is defined as the first three characters of the
phone number.
The main page of the application will be based on an indexed view that contains the area and phone number
for all customers.
You need to return the area code from the PhoneNumber field.
Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

Note: This question is part of a series of questions that present the same scenario. Each question in
the series contains a unique solution that might meet the stated goals. Some question sets might have
more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these
questions will not appear in the review screen.
You have a database that tracks orders and deliveries for customers in North America. The database contains
the following tables:
Sales.Customers

Application.Cities

Sales.CustomerCategories

The company’s development team is designing a customer directory application. The application must list
customers by the area code of their phone number. The area code is defined as the first three characters of the
phone number.
The main page of the application will be based on an indexed view that contains the area and phone number
for all customers.
You need to return the area code from the PhoneNumber field.
Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?