How should you complete the Transact-SQL statement?

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 query a database that includes two tables: Projectand Task.

The Project table includes the following columns:

The Task table includes the following columns:

You need to identify the owner of each task by using the following rules:

– Return each task’s owner if the task has an owner.

– If a task has no owner, but is associated with a project that has an owner, return the project’s owner.

– Return the value-1for all other cases.

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

View Answer

Answer:

Explanation:

Box 1: COALESCE

COALESCE evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

Box 2: T.UserID, p.UserID, -1

Return each task’s owner if the task has an owner.

If a task has no owner, but is associated with a project that has an owner, return the project’s owner.

Return the value -1 for all other cases.

Box 3: RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. Here the right side could be NULL as the projectID of the task could be NULL.

References:

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

http://www.w3schools.com/Sql/sql_join_right.asp