Home » Microsoft » 70-461 v.2 » SIMULATION
SIMULATION
You have a database named SALES that stored the sales data and the salespeople for your company.
You need to create a function that is passed a ProductID and then returns a list of SalesOrderlD values for orders that must be associated to a salesperson in the future.
The function must be able to return multiple SalesOrderlD values.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
Your Response: type here
A. "WHERE header.SalesPersonID IS NULL"
Correct Answer: A
Explanation/Reference:
Explanation:
On line 8 add: IS NULL to get:
CREATE FUNCTION dbo.OrdersWithNoSalesPerson(@ProductlD INT)
RETURNS
AS header.SalesOrderlD
FROM Sales.SalesOrderDetail detail
INNER JOIN Sales.SalesOrderHeader header on header.SalesOrderlD = detail.SalesOrderlD
WHERE header.SalesPersonID IS NULL
AND detail.ProductID = @ProductlD;
GO
Note: IS NULL determines whether a specified expression is NULL. If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns
FALSE.
To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL.
References:https://msdn. microsoft.com/en-us/library/msl88795.aspx
Thanks Sany.
I thought there was something missing in the logic of the function.
CREATE FUNCTION dbo.OrdersWithNoSalesPerson(@ProductlD INT)
RETURNS TABLE
AS
RETURN
SELECT header.SalesOrderlD
FROM Sales.SalesOrderDetail detail
INNER JOIN Sales.SalesOrderHeader header on header.SalesOrderlD = detail.SalesOrderlD
WHERE header.SalesPersonID IS NULL AND
detail.ProductID = @ProductlD;
GO