How should you complete the Transact-SQL statements?

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.

View Answer

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