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 ?
13
1
what is the correct answer ?
0
0
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
37
3
Reference:
https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
3
1
agree
1
1
Hi there, Just like to highlight that you changed the order of some columns in the 2. section and that can make some Briefers confused!
This is indeed the solution.
1
0
In other words both eder and peter’s answers are okay. but peter switches some column orders and misses on specifying the correct section, despite the code is right.
1
1
recursive common table expression:
https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017
0
0
error humano la respuesta correcta es:
WITH TaskWithLevel(
ParentTaskID,TaskID,TaskName,TaskLevel)
AS(
—
SELECT CAST(NULL AS INT)AS ParentTaskID,T.taskID,T.taskname,0 as TaskLevel
FROM TEST.Task as t WHERE T.ParentTaskId IS NULL
—
UNION ALL
—
SELECT R.TaskId as ParentTaskId,t.TaskId,t.TaskName,
R.TaskLevel+1 AS TaskLevel
FROM test.Task as t INNER JOIN TaskWithLevel
R ON t.ParentTaskId=R.TaskID
—
)
SELECT * FROM TaskWithLevel
7
1
This is the Solution
0
0
TaskID TaskName ParentTaskId ProjectID StartTime EndTime UserId
1 ANALYSIS NULL 1 2018-08-11 00:39:45.2266667 NULL 1
2 DESIGN REQUIREMENTS NULL 2 2018-08-11 00:39:45.2266667 NULL 2
3 DEVELOPMENT NULL 1 2018-08-11 00:39:45.2266667 NULL 1
6 TESTING 2 NULL 2018-08-11 00:39:45.2266667 NULL NULL
4 PLANNING 2 2 2018-08-11 00:39:45.2266667 NULL NULL
5 DEVELOPMENT NULL 2 2018-08-11 00:39:45.2266667 NULL NULL
7 TESTING NULL 3 2016-10-10 00:00:00.0000000 2016-12-26 11:10:16.1234567 3
8 DEVELOPMENT 1 3 2016-09-27 00:00:00.0000000 2016-10-09 00:00:00.0000000 3
9 DESIGN NULL NULL 2018-08-11 01:36:57.9133333 NULL NULL
0
0