SQL — 108 (Intermediate)

Introduction

This series is the revision of SQL concepts. In this blog, I will solve some intermediate questions from Hackerrank.

Basics questions are already solved in the previous blogs. [Links are at the end of the blog]

Question 1

[Ref: Hackerrank]

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in a 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 the node is the root node.
• Leaf: If the node is a leaf node.
• Inner: If the 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

Explanation:

The Binary Tree below illustrates the sample:

Solution:

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

Question 2

[Ref: Hackerrank]

You are given three tables: Students, Friends and Packages. Students contains two columns: ID and Name. Friends contains two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contains two columns: ID and Salary (offered salary in $ thousands per month).

Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.

Get Ravi raj’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Sample Input

Sample Output

Samantha
Julia
Scarlet

Explanation

See the following table:

Now,

• Samantha’s best friend got offered a higher salary than her at 11.55
• Julia’s best friend got offered a higher salary than her at 12.12
• Scarlet’s best friend got offered a higher salary than her at 15.2
• Ashley’s best friend did NOT get offered a higher salary than her

The name output, when ordered by the salary offered to their friends, will be:

• Samantha
• Julia
• Scarlet

Solution:

SELECT S.name FROM STUDENTS S 
JOIN FRIENDS F ON S.ID = F.ID
JOIN PACKAGES Student_Package ON S.ID = Student_Package.ID
JOIN packages BestFriend_Package ON F.Friend_id = BestFriend_Package.ID
WHERE Student_Package.salary < BestFriend_Package.salary
ORDER BY BestFriend_Package.salary;

Previous Posts

1. SQL — 104 (Basics) Aggregation
2. SQL — 105 (Basics) Aggregation
3. SQL — 106 (Basics) Joins
4. SQL — 107 (Basics) Misc