BenchMark Quizzes by MDFT Pro

Choose the Correct T-SQL Function

You are working as a data analyst for MDFT Pro, a well-known training agency. The academy uses a Fabric warehouse to track their training course sales. The warehouse contains a table named Courses that stores information about their training courses. The table contains the following columns.

Name	Datatype	Nullable
CourselD	Integer	No
CourseName	Decimal(18, 2)	No
ListPrice	Decimal(18, 2)	No
WholesalePrice	Decimal(18, 2)	Yes
AgentPrice	Decimal(18, 2)	Yes

Your manager has asked you to write a T-SQL query that will return the following columns to help analyze course pricing.

Name	Description
CourseID	Return the CourseID value
HighestPrice	Returns the highest value from ListPrice, WholesalePrice and AgentPrice
TradePrice	Returns AgentPrice if present, otherwise returns WholesalePrice if present, otherwise returns ListPrice

You have the following T-SQL query:

SELECT 
    ProductID, 
    ________________ (ListPrice, WholesalePrice, AgentPrice) AS TradePrice
FROM Sales.Products

How should you complete the query?

Choose the correct answer from the options below.

Explanations for each answer:

COALESCE is correct.
CHOOSE is incorrect. The CHOOSE function returns an item from a list of values based on an index position. It is not suitable for selecting the first non-null value from multiple columns.
GREATEST is incorrect. The GREATEST function returns the largest value from a list of expressions. While it can find the highest value, it cannot handle NULL values in the way required for this scenario.
IIF is incorrect. The IIF function is a shorthand way of writing a CASE expression, but it only works with two possible outcomes. It cannot be used to check multiple columns for the first non-null value.
MAX is incorrect. The MAX function returns the maximum value from a set of values, but it cannot be used to return the first non-null value from multiple columns as required in this scenario.

Learn more about COALESCE function:

COALESCE Function

Exam Preparation Quiz

Choose the Correct T-SQL Function