Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The NO Root Puzzle – A SINGLE Select | SQL Interview Question

In this puzzle you have to find out nodes that doesn’t have root in database tree. The challenge is to do this in a single select. For more details please see the sample input and expected output.

Sample Input

PId Left Right
1 2 3
2 4 5
3 6 7
10 NULL NULL
9 8 NULL

Expected Output

PId
1
10
9

Script – DDL and INSERT Sample Data

--

CREATE TABLE TheLeftRight 
(
   PId INT NOT NULL
  ,[Left]  INT 
  ,[Right] INT 
)
GO

INSERT INTO TheLeftRight VALUES
(1,2,3),
(2,4,5),
(3,6,7),
(10,NULL,NULL),
(9,8,NULL)
GO

SELECT * FROM TheLeftRight
GO


--

SOLUTION – 1

--

SELECT T1.PId
FROM  TheLeftRight U
RIGHT JOIN TheLeftRight T1
ON (u.[Left]=T1.PId or u.[Right]=T1.PId )
WHERE u.PId IS NULL

--

OUTPUT – 1

--


PId
-----------
1
10
9

(3 rows affected)


--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements