Alternative way to store hierachies in SQL

--
--
--            Albert (1,12)
--            /        \
--          /            \
--    Bert (2,3)    Chuck (4,11)
--                   /    |   \
--                 /      |     \
--               /        |       \
--             /          |         \
--        Donna (5,6)  Eddie (7,8)  Fred (9,10)
--
--  Another representation of this is:
--
--    1  2  3  4  5  6  7  8  9 10 11 12
--    (          Albert                )
--       ( B)  (     Chuck    ( F)  )
--         e      ( D)  ( E)    r
--         r        o     d     e
--         t        n     d     d
--                  n     i
--                  a     e


set feedback off
set pages 5000

create table emp_hierarchy (name varchar2(30), salary number,left_ number, right_ number);

insert into emp_hierarchy values('Albert', 10, 1, 12);
insert into emp_hierarchy values('Bert'  ,  7, 2,  3);
insert into emp_hierarchy values('Chuck' ,  8, 4, 11);
insert into emp_hierarchy values('Donna' ,  5, 5,  6);
insert into emp_hierarchy values('Eddie' ,  4, 7,  8);
insert into emp_hierarchy values('Fred'  ,  3, 9, 10);

prompt
prompt bosses of Eddie
prompt

select 
  e_up.name 
from 
  emp_hierarchy e_up join
  emp_hierarchy e_ed on 
  e_ed.left_ between e_up.left_ and e_up.right_
where
  e_ed.name = 'Eddie';


prompt 
prompt Whose Boss is Chuck
prompt

select 
  e_dwn.name 
from 
  emp_hierarchy e_dwn join
  emp_hierarchy e_chk on 
  e_dwn.left_ between e_chk.left_ and e_chk.right_
where
  e_chk.name = 'Chuck';

prompt
prompt Total salary that each employee controls
prompt

select 
  e_up.name, sum(e_dwn.salary) tot_sal
from 
  emp_hierarchy e_dwn join
  emp_hierarchy e_up on 
  e_dwn.left_ between e_up.left_ and e_up.right_
group by
  e_up.name;

prompt
prompt  ...
prompt
  
select 
  substr(lpad(' ',count(e_up.name)-1 /* level */) || e_dwn.name,1,100) name
from 
  emp_hierarchy e_dwn join
  emp_hierarchy e_up on 
  e_dwn.left_ between e_up.left_ and e_up.right_
group by
  e_dwn.name;

drop table emp_hierarchy;