(SQL 문제 풀이) Binary Tree Nodes | HackerRank

문제 : https://www.hackerrank.com/challenges/binary-search-tree-1/problem

Binary Tree Nodes

Your Binary Tree Nodes submission got 30.00 points.

You are now 50 points away from the 1st star for your sql badge.

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

Explanation

The Binary Tree below illustrates the sample:

• Orace //내장 함수

SELECT n

,CASE

    WHEN CONNECT_BY_ISLEAF = 1 THEN 'Leaf'

    WHEN LEVEL =1 THEN 'Root'

    ELSE 'Inner' END

FROM BST

START WITH P IS NULL

CONNECT BY PRIOR  N = P

ORDER BY N

;

• Best Simple

SELECT 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;

• NOT IN 의 함정
• null 일 때 함정이 있음.
• https://wjheo.tistory.com/entry/IN%EA%B3%BC-NOT-IN%EC%9D%98-%ED%95%A8%EC%A0%95

Select N,

CASE WHEN x.N NOT IN (select distinct P from BST where P IS NOT NULL) THEN 'Leaf'

     WHEN x.P IS NULL THEN 'Root'

     ELSE 'Inner'

END AS NodeType

from BST x

order by N

• 개행 시점은 언제가 좋을까?
• SELECT
• FROM
• WHERE
• ORDER BY
• GROUP BY
• HAVING