START WITH and CONNECT BY in Oracle SQL

One way to store hierarchical data is to create a self referencing table. It is called self referencing because the foreign key (column parent_id) references the same table as the table in which the foreign key is:

create table tblActivity (

  id         number primary key, 

 parent_id  references tblActivity, -- references itself

  descr      varchar2(20),

 

);

This table is now filled with some values:

-- 1st level:

insert into tblActivity values ( 1, null, 'Yahoo'         , 1);

-- 2nd level:

insert into tblActivity values ( 2,    1, 'Entertainment' , 1);

insert into tblActivity values ( 3,    1, 'Science'       , 1);

insert into tblActivity values ( 4,    1, 'Social Science', 1);

-- 3rd level (below Entertainment)

insert into tblActivity values ( 5,    2, 'Awards'        , 1);

insert into tblActivity values ( 6,    2, 'Comedy'        , 1);

insert into tblActivity values ( 7,    2, 'Humor'         , 0);

insert into tblActivity values ( 8,    2, 'Magic'         , 1);

-- 4th level (below Humor)

insert into tblActivity values (19,    7, 'Jokes'         , 1);

insert into tblActivity values (20,    7, 'Advice'        , 1);

insert into tblActivity values (21,    7, 'Parody'        , 1);

-- 4th level (below Magic)

insert into tblActivity values (16,    8, 'Card Tricks'   , 1);

insert into tblActivity values (17,    8, 'Magazines'     , 1);

insert into tblActivity values (18,    8, 'Organizations' , 1);

-- 3rd level (below Science)

insert into tblActivity values ( 9,    3, 'Astronomy'     , 1);

insert into tblActivity values (10,    3, 'Biology'       , 1);

insert into tblActivity values (11,    3, 'Geography'     , 1);

insert into tblActivity values (12,    3, 'Physics'       , 1);

insert into tblActivity values (13,    3, 'Research'      , 1);

-- 4th level (below Astronomy)

insert into tblActivity values (22,    9, 'Galaxies'     , 1);

insert into tblActivity values (23,    9, 'Pictures'     , 1);

insert into tblActivity values (24,    9, 'Stars'        , 1);

-- 4th level (below Physics)

insert into tblActivity values (25,   12, 'Electricity'  , 1);

insert into tblActivity values (26,   12, 'Mechanics'    , 1);

-- 3rd level (below Social Science)

insert into tblActivity values (14,    4, 'Psychology'   , 1);

insert into tblActivity values (15,    4, 'Sociology'    , 1);

-- 4th level (below Psychology)

insert into tblActivity values (27,   14, 'Conferences'  , 1);

insert into tblActivity values (28,   14, 'Intelligence' , 1);

--Now Select 

select * from   tblActivity 

start with id = 3
connect by prior id = parent_id; 

It return the below mention record

Science
    Astronomy
      Galaxies
      Pictures
      Stars
    Biology
    Geography
    Physics
      Electricity
      Mechanics
    Research