Below, you find a nice example of an hierarchical query in Oracle. The idea is that you see the top-down hierarchy with some indications on the level that is shown.
First the query:
select level,
case
when level=1 then first_name||' '||last_name
when level>1 then lpad (first_name||' '||last_name,length(first_name||' '||last_name)+level-1, '-')
end tree
from employees
start with employee_id=100
connect by prior employee_id=manager_id --top down
The result is straight forward to interpret:
The level is provided, with dashes to provide additional information.
The query can be undertaken from top to bottom as shown here. The idea is that starts with the top, going down to the lower levels. However if the foreign key and primary key are reversed, one starts the analysis from a person to the top, like:
select level,
case
when level=1 then first_name||' '||last_name
when level>1 then lpad (first_name||' '||last_name,length(first_name||' '||last_name)+level-1, '-')
end tree
from employees
start with employee_id=113
connect by prior manager_id=employee_id -- down-top
, which then leads to another view:
