Approach: If parent p is null it is ROOT, For INNER parent P should not be null and child should be there, so number in Parent column P have a child so check if N number node present Parent p column then Inner

select N, CASE when P is null then "Root" when P is not null and N in (select distinct P from BST) then "Inner" else "Leaf" END as type_node FROM BST
order by N ASC

it works in oracle

select b.N, case when b.p is null then 'Root' when b.N in (select k.p from bst k where k.p is not null) then 'Inner' else 'Leaf' end from bst b order by 1;

Using IF()

select bst.N,
IF(bst.p IS NULL,'Root',  IF(bst.n in (select bst.n from bst where bst.p is not null and n in (select p from bst)), 'Inner', 'Leaf')) 
from bst
order by bst.N

select distinct b1.n, case when b2.p is null then 'Leaf' when b1.p is null then 'Root' else 'Inner' end as n_type from BST as b1 left join BST as b2 on b1.n = b2.p order by b1.n

MS SQL Sever

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