Which five Transact-SQL segments should you use to develop the solution?

DRAG DROP

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:

The Task table includes the following columns:

Task level is defined using the following rules:

You need to determine the task level for each task in the hierarchy.

Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

View Answer

Answer:

Explanation:

Box 1: With TaskWithLevel (ParentTaskID, TaskID, TaskName, TaskLevel) as {

WITH common_table_expression specifies a temporary named result set, known as a common table expression (CTE).

Box 2: SELECT CAST (NULL AS INT) AS ParentTaskID, etc.

This statement selects all tasks with task level 0.

The ParentTaskID could be null so we should use CAST (NULL AS INT) AS ParentTaskID.

Box 3: UNION ALL

In a Recursive CTE Union ALL must be used.

Box 4: SELECT R.TaskId as ParentTaskId,t.TaskId,t.TaskName, etc

Box 5: )

SELECT * FROM TaskWithLevel

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