PrepAway - Latest Free Exam Questions & Answers

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-SQLsegments in the answer area.
Hot Area:

PrepAway - Latest Free Exam Questions & Answers

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.
https://msdn.microsoft.com/en-us/library/ms186755.aspx

2 Comments on “How should you complete the Transact-SQL statements?

  1. eder says:

    CORRECT ANSWER,VERIFIED……….

    CREATE FUNCTION Sales.CalculateOrderPrices
    (@OrderID INT)
    RETURNS decimal(18,2)
    AS
    BEGIN
    DECLARE @OrderPrice decimal (18,2)
    DECLARE @CalculatedTaxRate decimal(18,2)
    SET @OrderPrice=(SELECT SUM(quantity*unitprice)FROM q33.OrderLines
    WHERE orderid=@orderID)
    SET @CalculatedTaxRate=(SELECT 1 +(MAX(TaxRate)/100)FROM Q33.OrderLines WHERE orderid=@orderID)
    RETURN (@OrderPrice *@CalculatedTaxRate)
    END

    COMPROBANDO

    select Sales.CalculateOrderPrices (11)
    (No column name)
    13902.00

    select (sum(quantity*unitprice))*1+(max(taxrate)/100)
    from q33.OrderLines
    where orderid=11
    (No column name)
    13902.00




    0



    0

Leave a Reply