SQL : Binary Tree Nodes Problem

 Hello All,

We are back with another SQL problem, focusing on use case of SQL CASE statement. CASE statement works similar to that of IF - ELSE ladder in other programming languages. This problem requires use of CONCAT() function.

You can visit following learning resources related to this particular question :

1. SQL CASE statement - SQL CASE Statement (w3schools.com)

2. concat function - CONCAT (Transact-SQL) - SQL Server | Microsoft Docs 

The problem below is sourced from HackerRank. You can visit the link: Binary Tree Nodes | HackerRank

Give a try to solve below problem. Solution is provided as usual. Feel free to comment if any queries and also suggest alternate approach to solve this problem. Have a Happy Learning !  

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

  • Root: If node is root node.
  • Leaf: If node is leaf node.
  • Inner: If node is neither root nor leaf node.

Sample Input

Sample Output

1 Leaf
2 Inner
3 Leaf
5 Root
6 Leaf
8 Inner
9 Leaf

Solution :


SELECT DISTINCT N,
(CASE WHEN P IS NULL THEN "Root"
            WHEN N IN (SELECT DISTINCT P FROM BST) THEN "Inner"
             ELSE "Leaf"
END)
FROM BST
ORDER BY N

Explanation :

If you observe the table carefully, you will notice that ROOT is the one with null parent node P. After ROOT is selected, INNER is the one in N which is present in P. And remaining everything is LEAF.
So, we have used CASE statement along with SELECT to present these 3 cases. And rest is done as usual !
 

Comments

Post a Comment

Popular posts from this blog

SQL for beginners : finding out Employees Earning More Than Their Managers

Leetcode question no. 181   181 .  Employees Earning More Than Their Managers SQL Schema Table:  Employee +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | salary | int | | managerId | int | +-------------+---------+ id is the primary key column for this table. Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.   Write an SQL query to find the employees who earn more than their managers. Return the result table in  any order . The query result format is in the following example.   Example 1: Input: Employee table: +----+-------+--------+-----------+ | id | name | salary | managerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | Null | | 4 | Max | 90000 | Null | +----+-------+--------+-----------+ Output: ...
Read more

Answer to SQL Leetcode Question 183. Customers Who Never Order

Answer to SQL Leetcode Question 183 183 .  Customers Who Never Order   SQL Schema Table:  Customers +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id is the primary key column for this table. Each row of this table indicates the ID and name of a customer.   Table:  Orders +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | customerId | int | +-------------+------+ id is the primary key column for this table. customerId is a foreign key of the ID from the Customers table. Each row of this table indicates the ID of an order and the ID of the customer who ordered it.   Write an SQL query to report all customers who never order anything. Return the result table in  any order . The query result format is in the following example.   Example 1: Input: Customers table: +----+-------+ | id | name | +---...
Read more

SQL Medium difficulty practice question : Consecutive Numbers

180 .  Consecutive Numbers   Hello All, I am back with another SQL leetcode solution. This time we are going one step ahead and practice question with medium difficulty. SQL Schema Table:  Logs +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ id is the primary key for this table. id is an autoincrement column.   Write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in  any order . The query result format is in the following example.   Example 1: Input: Logs table: +----+-----+ | id | num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ Output: +-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+ Explanation: 1 is the only number that appears consecutively for at least three ti...
Read more