If it's an interview question and I answer it, do I get the job? ;)
To get the path, use a hierarchical query.
Here's an example using traditional connect by. With this an sys_connect_by_path, you can get the path to the root for each node:
create table t (
node int,
parentn int
);
insert into t values (1, null);
insert into t values (2, 1);
insert into t values (3, 1);
insert into t values (4, 2);
insert into t values (5, 2);
insert into t values (6, 3);
insert into t values (7, 6);
select node, parentn, sys_connect_by_path(node, ';') path
from t
start with parentn is null
connect by prior node = parentn;
NODE PARENTN PATH
1 ;1
2 1 ;1;2
4 2 ;1;2;4
5 2 ;1;2;5
3 1 ;1;3
6 3 ;1;3;6
7 6 ;1;3;6;7
You can also do this using the with clause:
https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2 To handle changes, the naive solution is to recompute all the paths every time:
alter table t add (path varchar2(100));
update t
set parentn = 4
where node = 5;
update t
set path = (
with paths as (
select node, parentn, sys_connect_by_path(node, ';') path
from t
start with parentn is null
connect by prior node = parentn
)
select path from paths p
where p.node = t.node
);
select * from t;
NODE PARENTN PATH
1 ;1
2 1 ;1;2
3 1 ;1;3
4 2 ;1;2;4
5 4 ;1;2;4;5
6 3 ;1;3;6
7 6 ;1;3;6;7
This inefficient. There are better approaches available. They come with their own trade-offs though. Bill Karwin discusses various method at:
http://www.slideshare.net/billkarwin/models-for-hierarchical-data Improving this and implementing using a trigger left as an exercise for the interviewee ;)