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: Project and Task. The Project 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.
Hot Area:
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.
https://msdn.microsoft.com/en-us/library/ms190349.aspx
http://www.w3schools.com/Sql/sql_join_right.asp
wrong answer sorry 🙁
1
4
Box 1: COALESCE
Box 2: T.UserID, p.UserID, -1
Box 3: LEFT JOIN
37
2
do not mistake us, you always say ‘wrong’, you are bad man
3
0
Should be inner join.
0
13
If inner join, ‘-1’ will not appear in the result.
2
0
@ davgmane wrong answer sorry 🙁
3
1
It has to be left join because a task may not have a relation to a project.
Inner and right join would loose tasks without a relation to a project.
30
0
Agreed
4
0
I approve
2
0
no peter, it should be Right left, because the query started with Task table,
if the query started with project table then it will be left join .
2
4
I have a clarification.
i. The FROM clause states: FROM Task T (i.e., Table 2). So, this is the base table.
ii. The question further states:
“a. Return each task’s owner if the task has an owner.
b. If a task has no owner, but is associated with a project that has an owner,
return the project’s owner.
c. Return the value-1for all other cases.”
So, the matching rows in Projects Table are Project ID and User id.
So, should it not be INNER JOIN?
0
0
Sorry, Right Join, not INNER JOIN.
0
0
i think Left Join is correct answer.
8
1
COALESCE
Left Join
2
1
Box 1: COALESCE
Box 2: T.UserID, p.UserID, -1
Box 3: LEFT JOIN
9
2