Question 6 of 201

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 create a table named Customer by running the following Transact-SQL statement:

You must insert the following data into the Customer table:

You need to ensure that both records are inserted or neither record is inserted.

Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

    Correct Answer: B

    The provided solution does not meet the goal. The question requires that both records be inserted or neither be inserted, which indicates a need for transaction handling. The given solution only performs an INSERT operation and does not ensure atomicity. Without using a transaction with BEGIN TRANSACTION, COMMIT, and ROLLBACK, there's no guarantee that both inserts will succeed or fail together, especially in case of an error. Therefore, the correct answer is B.

Question 7 of 201

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?

    Correct Answer: B

    The provided Transact-SQL statement creates a function that returns a table, but the requirement is to return only the area code from the phone number. The STRING_SPLIT function splits the phone number into parts using the '-' character, but it does not guarantee the order of the returned values, so it cannot reliably extract the area code, which is always the first segment. Additionally, a table-valued function is not necessary for simply extracting the area code. A more appropriate approach would be to use the LEFT function to directly extract the first three characters from the phone number. Therefore, the solution does not meet the requirement.

Question 8 of 201

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?

    Correct Answer: B

    The provided solution does not meet the goal. For the function to be used within an indexed view, it must be created with the WITH SCHEMABINDING option. This ensures that the underlying schema remains constant, and no alterations can occur without dropping the view. The absence of the schemabinding option in the function definition means that the function cannot be used in an indexed view, failing to meet the requirement specified in the scenario.

Question 9 of 201

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?

    Correct Answer: B

    The given T-SQL statement uses the STRING_SPLIT function to split the phone number based on the hyphen ('-'). However, the function STRING_SPLIT does not guarantee the order of the substrings, and thus selecting a single value without specifying an order may result in an incorrect part of the phone number being chosen. The comment about needing 'SELECT TOP 1' along with additional ordering logic to ensure the correct substring is returned is valid. Therefore, the solution does not meet the goal.

Question 10 of 201

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:

The Task 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?

A.

B.

C.

D.

    Correct Answer:

    To find the total count of tasks that are updated and are not associated with a project, you should use a query that captures both TaskId and ProjectId to filter correctly. The update and output logic must ensure that null StartTimes are targeted, updated, and the corresponding ProjectId is captured to count those with null ProjectIds. So, the correct set of Transact-SQL statements you should run is: DECLARE @startedTasks TABLE(TaskId int, ProjectId int) UPDATE Task SET StartTime = GETDATE() OUTPUT deleted.TaskId, deleted.ProjectId INTO @startedTasks WHERE StartTime is NULL SELECT COUNT(*) FROM @startedTasks WHERE ProjectId IS NULL This query first declares a table variable to capture TaskId and ProjectId of the updated rows. In the update statement, it sets the StartTime to the current date and time for tasks with a null StartTime. The OUTPUT clause captures the TaskId and ProjectId of each updated row into the @startedTasks table. Finally, a SELECT statement counts the rows in @startedTasks where ProjectId is null, effectively giving the count of tasks updated that are not associated with a project.