Simplest Oracle Solution Using Case statement
select Distinct id,type from(\nselect T.id,\ncase\n    when T.p_id is null then \'Root\'\n    when T1.p_id is null then \'Leaf\'\n    ELSE \'Inner\'\nend as typ
select Distinct id,type from(\nselect T.id,\ncase\n    when T.p_id is null then \'Root\'\n    when T1.p_id is null then \'Leaf\'\n    ELSE \'Inner\'\nend as type\nfrom Tree T left outer join Tree T1\non T.id=T1.p_id\n    )
['Oracle']