Solving SQL Interview Query | Find Tree Node type using SQL

[@arjundev4908]

Damn. This was asked in my ANZ interview which I couldn't answer this clearly. Thanks Toufiq for the clarity that you provided.

[@shaikusman536]

Awsome brother....Loved the way you approach problem statement....Respect from Bangalore,INDIA.

[@techTFQ]

Thank you brother 🙏🏼

[@chimadivine7715]

I decided to solve it before watching your solution, and I actually solved it right. The difference is that, in the second 'when' statement, I didnt add: "p is not NULL" And I think it will still work correctly like that. I'm happy that my solution was same as the solution of a genius like you. Many thanks for this quality video!

[@sandeepreddy9929]

Thanks Toufiq for the effort you put in making these videos, I was actually waiting for a video on hierarchical queries. Another way if it helps others SELECT id, CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 'Leaf' WHEN CONNECT_BY_ISLEAF = 0 AND LEVEL = 1 THEN 'Root' WHEN CONNECT_BY_ISLEAF = 0 AND LEVEL 1 THEN 'Inner' END typ from tree connect by prior id = p_id start with p_id IS NULL;

[@arshadmohammed2346]

way simple way to explain ! Thanks Taufiq

[@venkatram9698]

I actually very impressed by your way of teaching please try solve more leet code problem

[@muthu8025]

The way you explained the problem I really stunned

[@alisonhenley2551]

Excellent! Feeling a lot more prepared to take on interviews because of you!!!!

[@suyash7450]

Your explanation is just too good....

[@greentechkey]

Great Explanation

[@Zenoid_26]

Thanks a lot for doing these kind of videos❤️

[@fathimafarahna2633]

As always THE BEST

[@techTFQ]

❤️❤️

[@hariniharnath9932]

Well explained 👏. Made it easy

[@lokeshmadiga6215]

Thank you bro for this solution !

[@sse6]

Great explanation

[@dilipinamdar5523]

Thanks a lot Taufiq, Please suggest books to read about MS SQL.

[@satheeshkumarak6708]

Hello Sir @techTFQ, Can you make a video on working with JSON & Nested JSON data ?

[@shaikhabib7799]

Keep up the good work 🎉.

[@vinaykumarpatnana]

good class

[@cococnk388]

great thanks

[@raitup00]

Hi Taufiq, I think I have the next level of this excercise, actually is a real problem in a Pharmaceutical company. I was wondering if I can send you the the problem with extra complications and if you consider that the problem worth, would you mind solve it in a video? That would be nice

[@avi8016]

Thanks a lot Taufiq, One question it should be possible to approach the same problem via self join

[@santhiv9029]

Hi,Your vedios are very useful and informative y don't you start oracle plsql topics also it will help to get logic how sql queries used to plsql as well

[@Ajjeshh]

Thanks for the explanation . I have tried on my own and could you pls tell me what is the issue with the below code drop table if exists tree_node; create table tree_node (id integer, pid integer); insert into tree_node (id, pid ) values (1, null); insert into tree_node (id, pid) values (2, 1); insert into tree_node (id, pid) values (3,1 ); insert into tree_node (id, pid) values (4, 2); insert into tree_node (id, pid) values (5, 2); select *, case when pid is null then 'rootnode' when id not in (select pid from tree_node) then 'leaf_node' else 'inner_node' end as level from tree_node; Based on my case statement , it should print as "leaf_node" for records 3,4,5 . But It is not .. Any issue with my case statement ? Thanks.! This is the o/p i am getting id pid level 1 rootnode 2 1 inner_node 3 1 inner_node 4 2 inner_node 5 2 inner_node

[@Iamabhay_]

Sir, please-please makes a video on Database administrator vs Data science

[@sujathav-rf4vt]

when you will start your next batch ? and is there any offer for new year for our course ?

[@nikhilavemuri955]

Hi Thoufiq, Waiting to join your online training. Please let me know when your next live training batch will get started.

[@techTFQ]

Noted Nikhila, I’ll announce something by this month end

[@nikhilavemuri955]

@@techTFQ Sure, thank you! Thoufiq

[@mdrimon3276]

awesome!

[@RAHUDAS]

Can we build custom function for same , kindly make video on custom functions

[@CassStevens]

your solution is simpler than mine: select id, 'root' as type from tree where p_id is null union select distinct p_id as id, 'inner' as type from tree where p_id (select id from tree where p_id is null) and p_id is not null union select id, 'leaf' as type from tree where id not in (select distinct p_id from tree where p_id is not null) and p_id is not null

[@hungry_panda9241]

Eagerly waiting for your reply

[@rjc3872]

Briiliant !!!

[@sreekanth5059]

Hi bro , Can you do a Video on ADF

[@Tusharchitrakar]

Hi, firstly you have great pedagogical technique of explaining and kudos to you. Secondly, I was trying on my own before seeing your video and I used the exact same technique to derive the result but I wasn't happy for one reason. It's because of how the IN operator works: it does an entire table scan. For example, if I'm searching for id 3 in the p_id column, it will still keep searching till the end of the p_id even if it finds 3 earlier. Hence, one can imagine how inefficient this approach is for extremely large trees with thousands of nodes. I tried using an index too but still the query cost was not reduced. Is there something like a WHERE EXISTS that we can use where the search will stop as soon as I find the target (in this case 3 in p_id) instead of going till the end of the inner subquery table? Kindly give your input, thanks!

[@rjc3872]

Select Exists( Select p_id from tree where p_id=3 )...just giving an example , it will return true or false at the first instance it finds it ....

[@Tusharchitrakar]

@@rjc3872 thanks but my earlier understanding few months back was wrong. The SQL optimizer in the background does not necessarily do a full table scan and it depends on the specific DBMS engine being used

[@GeekSP1]

@techTFQ in practice, why do we find the tree node type? i mean are there any use cases you can mention?? I appreciate spreading value

[@DailyShit.]

Hierarchical database models. Literally the oldest one.

[@anilkumargovindan4138]

Need a solution for the below problem. Statement Input : Id val 1 3 2 1 3 2 Output Id val 1 1 1 1 1 1 2 1 3 1 3 1 Based on the value of the column 'val' the result set should be generated as shown

[@prashantmhatre9225]

Use recursive with clause

[@AnandhiSeshadri]

I have 3yrs experience in SQL server and I have changed testing domain for 8 months but I don't like testing again I want to change my domain SQL server. Can you please give some idea. Is SQL server is good in future and I have some career gap because of my baby and my baby has 2 month old now? It is the right time to look another job ? can you pls help me out?

[@satyabharadwaj7779]

I have a doubt with the 'else' statement. Will it negate just the second 'when' statement or to both the 'when' statements?

[@Soumchak96]

Else logic will only be applied if the earlier logic fails. Which means if it's not a root or an inner node then only it'll be a leaf node.

[@satyabharadwaj7779]

@@Soumchak96 but we can use else for every when statement, isn't it? Here we have two when statements hence my doubt.

[@rohitahuja999]

hi taufiq i am getting this error foreign key p_id = does not exist in the Tree table

[@techTFQ]

U trying to solve it in leetcode is it? I thinking something wrong with leetcode today, even I faced some wierd error

[@rohitahuja999]

@@techTFQ Hi do u have any video regarding any, all, some operator ?

[@parveen8122]

Slightly different condition ======================== select id, case when p_id is null then "Root" when id not in (select distinct p_id from tree) then " Leaf " else " Inner" from tree

[@techTFQ]

Nice 👍

[@b_rizzle4808]

i was thinking this too since the null exception is already covered in the first(root) case then it's not needed in the second(leaf) case; since it was already attributed in the first

[@parveen8122]

@@b_rizzle4808 yes

[@meenayegan6634]

One small change, not in operator will return null values if null is present in the select list. change is: when id not in (select distinct p_id from tree where p_id is not null) then " Leaf "

[@jabedhasan21]

@@meenayegan6634 Nice Catch👍

[@hungry_panda9241]

He hi bro how to contact you personally bro i want all SQL classes videos plz plz plz i will pay for that bro plz tell me how to contact you

[@hungry_panda9241]

Bro plplz reply How can i contact you plz bro