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:
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.
Select and Place:
Answer: 
Explanation:
Box 1: 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 2: UNION
We should use UNION and not UNION ALL as we do not went duplicate rows.
UNION specifies that multiple result sets are to be combined and returned as a single result set.
Incorrect Answers:
Not UNION ALL: ALL incorporates all rows into the results. This includes duplicates. If not specified, duplicate
rows are removed.
Box 3, Box 4, Box 5:
These statements select all tasks with task level >0.https://msdn.microsoft.com/en-us/library/ms180026.aspx
a joke ?
what is the correct answer ?
Answer is nonsense!
It needs to be as shown in the example code below:
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Task’)
BEGIN
DROP TABLE dbo.Task
END
CREATE TABLE dbo.Task
(
TaskID INT NOT NULL PRIMARY KEY
,TaskName NVARCHAR(20) NOT NULL
,ParentTaskID INT NULL
);
INSERT INTO dbo.Task VALUES
(1, ‘1’, NULL)
,(2, ‘1_1’, 1)
,(3, ‘1_1_1’,2)
,(4, ‘1_2’, 1)
,(5, ‘2’, NULL)
,(6, ‘2_1’, 5)
,(7, ‘2_1_1’,6)
,(8, ‘2_1_2’,6)
,(9, ‘2_2’, 5);
SELECT * FROM dbo.Task
ORDER BY TaskName;
;
————- 1. Section
WITH TaskWithLevel (TaskID, TaskName, ParentTaskID, TaskLevel)
AS
(
————- 2. Section
SELECT
T.TaskID
,T.TaskName
,CAST(NULL AS INT) AS ParentTaskID
,0 AS TaskLevel
FROM dbo.Task AS T WHERE ParentTaskID IS NULL
————- 3. Section
UNION ALL
————- 4. Section
SELECT
T.TaskID
,T.TaskName
,R.TaskID AS ParentTaskID
,(R.TaskLevel + 1) AS TaskLevel
FROM dbo.Task AS T
INNER JOIN TaskWithLevel AS R
ON T.ParentTaskID = R.TaskID
————- 5. Section
)
SELECT * FROM TaskWithLevel
ORDER BY TaskName
Reference:
https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
agree