Very easy and crisp solution
select id,\n    case when p_id is null then \'Root\'\n         when Id in (select distinct p_id from Tree) then \'Inner\'\n    else\n        \'Leaf\'\n    end a
select id,\n    case when p_id is null then \'Root\'\n         when Id in (select distinct p_id from Tree) then \'Inner\'\n    else\n        \'Leaf\'\n    end as type\nfrom Tree\norder by id
[]