Type of Node

Write an SQL query to identify the type of node from the image given below:-

Root Node :- The topmost node.

Leaf Node :- The node that lacks any child node.

Inner Node :- The node that has one or more child nodes.

Input :-

Nodes table :-
ID is the Primary Key column for the Nodes table.

ID	Parent_ID
4	Null
8	4
7	4
5	7
3	7

Output :-

ID	Type
4	Root
8	Leaf
7	Inner
5	Leaf
3	Leaf

Query :-

select ID,
case
    when Parent_ID is null then "Root"
    when ID in (select Parent_ID from Nodes) then "Inner"
    else "Leaf"
end Type
from Nodes;

Explanation : - 

This query selects the 'ID' column from the "Nodes" table and uses a case statement to determine the type of each node.

• If the Parent_ID is Null, it means the node is a root node. The case statement assigns the value 'Root' to the Type column for this node.
• If the ID exists in the Parent_ID column of any other row in the Nodes table, it means the node is an inner node. The case statement assigns the value 'Inner' to the Type column for this node.
• If the above two conditions are not met and the node doesn't have any child node, it means the node is a leaf node. The case statement assigns the value 'Leaf' to the Type column for this node.

Overall, the query categorises each node in the Nodes table as either a root, inner, or leaf node based on the presence or absence of a Parent_ID.