1. Query all descendant nodes under a node (including parent nodes at all levels)

1 // query id Is all descendant nodes of 101, including all levels of parent nodes of 101
2 select t.* from SYS_ORG t start with id = '101' connect by parent_id = prior id

2. Query all descendant nodes under a node (excluding parent nodes at all levels)

1 select t.*
2   from SYS_ORG t
3  where not exists (select 1 from SYS_ORG s where s.parent_id = t.id)
4  start with id = '101'
5 connect by parent_id = prior id

3. Query all parent nodes of a node (all ancestor nodes)

1 select t.*
2   from SYS_ORG t
3  start with id = '401000501'
4 connect by prior parent_id = id

4. Query all siblings of a node (siblings)

1 select * from SYS_ORG t
2 where exists (select * from SYS_ORG s where t.parent_id=s.parent_id and s.id='401000501')

5. Query all peers (family nodes) of a node, assuming no level field is set

1 with tmp as(
2       select t.*, level leaf        
3       from SYS_ORG t               
4       start with t.parent_id = '0'     
5       connect by t.parent_id = prior t.id)
6 select *                               
7       from tmp                             
8 where leaf = (select leaf from tmp where id = '401000501');

Two techniques are used here. One is to use level to identify the level of each node in the table. The other is to use with syntax to simulate a temporary table with level

6. Query the parent node and sibling node of a node (Uncle node)

with tmp as(
    select t.*, level lev
    from SYS_ORG t
    start with t.parent_id = '0'
    connect by t.parent_id = prior t.id)  
select b.*
from tmp b,(select *
            from tmp
            where id = '401000501' and lev = '2') a
where b.lev = '1'
 
union all
 
select *
from tmp
where parent_id = (select distinct x.id
                from tmp x, --grandfather
                     tmp y, --father
                     (select *
                      from tmp
                      where id = '401000501' and lev > '2') z --Son
                where y.id = z.parent_id and x.id = y.parent_id); 

Here the query is divided into the following steps.
First, use temporary tables to add levels to all tables;
Secondly, there are several types according to the level. In the example above, there are three situations:
(1) If the current node is a top-level node, that is to say, the queried lev value is 1, then it has no parent node and will not be considered.
(2) If the current node is a level 2 node and the queried lev value is 2, then as long as the lev level is 1, it is the sibling of its parent node.
(3) In other cases, if the level is 3 or above, you need to select and query the parent node (grandfather), and then judge that the child nodes of the grandfather are all siblings of the parent node of the node.
Finally, union is used to combine the query results to form a result set.