How does one code a tree-structured query?
Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings. The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the “current” employee’s boss. The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example: select LEVEL, EMPNO, ENAME, MGR from EMP connect by prior EMPNO = MGR start with MGR is NULL; One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example: select lpad(‘ ‘, LEVEL * 2) || ENAME …….. One uses the “start with” clause to specify the start of the tree. More than one record can match the sta
