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 are developing a database to track customer orders. The database contains the following tables: Sales.Customers, Sales.Orders, and Sales.OrderLines.

The following table describes the columns in Sales.Customers.

The following table describes the columns in Sales.Orders.

The following table describes the columns in Sales.OrderLines.

You need to create a database object that calculates the total price of an order including the sales tax.

The database object must meet the following requirements:

– Reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated execution.

– Return a value.

– Be callable from a SELECT statement.

How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: FUNCTION

To be able to return a value we should use a scalar function.

CREATE FUNCTION creates a user-defined function in SQL Server and Azure SQL Database. The return value can either be a scalar (single) value or a table.

Box 2: RETURNS decimal(18,2)

Use the same data format as used in the UnitPrice column.

Box 3: BEGIN

Transact-SQL Scalar Function Syntax include the BEGIN ..END construct.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type

[ = default ] [ READONLY ] }

[ ,…n ]

]

)

RETURNSreturn_data_type

[ WITH <function_option> [ ,…n ] ]

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END

[ ; ]

Box 4: @OrderPrice * @CalculatedTaxRate

Calculate the price including tax.

Box 5: END

Transact-SQL Scalar Function Syntax include theBEGIN ..END construct.

References: https://msdn.microsoft.com/en-us/library/ms186755.aspx

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 are developing a database to track customer orders. The database contains the following tables: Sales.Customers, Sales.Orders, and Sales.OrderLines. The following table describes the columns in Sales.Customers.

The following table describes the columns in Sales.Orders.

The following table describes the columns in Sales.OrderLines.

You need to create a stored procedure that inserts data into the Customers table.

The stored procedure must meet the following requirements:

– Data changes occur as a single unit of work.

– Data modifications that are successful are committed and a value of 0 is returned to the calling procedure.

– Data modifications that are unsuccessful are rolled back. You must display a message that uses severity level 16 and a value of -1.

– The stored procedure uses a built-in scalar function to evaluate the current condition of data modifications.

– The entire unit of work is terminated and rolled back if a run-time error occurs during execution of the stored procedure.

How should complete the stored procedure definition? To answer, drag the appropriate Transact-SQL segments to the correct targets. Each Transact-SQL segment 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. NOTE: Each correct selection is worth one point.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: XACT_ABORT

XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-timeerror, the entire transaction is terminated and rolled back.

Box 2: COMMIT

Commit the transaction.

Box 3: XACT_STATE

Box 4: ROLLBACK

Rollback the transaction

Box 5: THROW

THROW raises an exception and the severity is set to 16.

Requirement: Data modifications that are unsuccessful are rolled back. The exception severity level is set to 16 and a value of -1 is returned.

References:

https://msdn.microsoft.com/en-us/library/ms188792.aspx

https://msdn.microsoft.com/en-us/library/ee677615.aspx

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 are developing a database to track customer orders. The database contains the following tables: Sales.Customers, Sales.Orders, and Sales.OrderLines. The following table describes the columns in Sales.Customers.

The following table describes the columns in Sales.Orders.

The following table describes the columns in Sales.OrderLines.

You need to create a function that calculates the highest tax rate charged for an item in a specific order.

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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box1: CREATE FUNCTION…@OrderID

Include definition for the …@OrderID parameter.

Box 2: RETURNS decimal(18,2)

The function is defined to return a scalar value.

Box 3: AS BEGIN …

Declare the local variables of the function.

Box 4: SET @CalculatedTaxRate = (..

Calculate the tax rate.

Box 5: RETURN @CalculatedRate END

Return a scalar value.

References: https://msdn.microsoft.com/en-us/library/ms186755.aspx

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You need to audit all customer data.Which Transact-SQL statement should you run?

Reveal Solution Hide Solution

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You create a table by running the following Transact-SQL statement:

You need to return normalized data for all customers that were added in the year 2014.

Which Transact-SQL statement should you run?

Reveal Solution Hide Solution

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You are developing a report that displays customer information. The report must contain a grand totalcolumn.You need to write a query that returns the data for the report.Which Transact-SQL statement should you run?

Reveal Solution Hide Solution

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You create a table named Customers. Data stored in the table must be exchanged between web pages and web servers by using AJAX calls that use REST endpoints.

You need to return all customer information by using a data exchange format that is text-based and lightweight.

Which Transact-SQL statement should you run?

Reveal Solution Hide Solution

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You create a table by running the following Transact-SQL statement:

You are developing a report that aggregates customer data only for the year 2014. The report requires that the data be denormalized. You need to return the data for the report.

Which Transact-SQL statement should you run?

Reveal Solution Hide Solution

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.

You create a table by running the following Transact-SQL statement:

You need to develop a query that meets the following requirements:

– Output data by using a tree-like structure.

– Allow mixed content types.

– Use custom metadata attributes.

Which Transact-SQL statement should you run?

Reveal Solution Hide Solution

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.

A database has two tables as shown in the following database diagram:

You need to list all provinces that have at least two large cities. A large city is defined as having a population of at least one million residents.

The query must return the following columns:

– tblProvince.ProvinceId

– tblProvince.ProvinceName

– a derived column named LargeCityCount that presents the total count of large cities for the province

Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

Reveal Solution Hide Solution