Last modified on 10 January 2012, at 13:36

Oracle Programming/Retrieving Data Using Sub-queries

Write a multiple-column sub-queryEdit

Use scalar sub-queries in SQLEdit

Solve problems with correlated sub-queriesEdit

Update and delete rows using correlated sub-queriesEdit

Use the EXISTS and NOT EXISTS operatorsEdit

Use the WITH clauseEdit

Hierarchical QueryEdit

Hierarchical Query allows you the transverse through a self-reference table and display the Hierarchical structure. eg. the employee table contain the manager id the employee.

list out the whole hierarchical structure of the employees

SELECT LPAD(' ', 4*(level-1))||last_name "Last Name", salary, department_id
FROM hr.employees
CONNECT BY PRIOR employee_id = manager_id
      START WITH manager_id IS NULL
ORDER SIBLINGS BY last_name;

list out all the employees under manager 'Kochhar'

SELECT LPAD(' ', 4*(level-1))||last_name "Last Name", 
       salary, 
       department_id,
       CONNECT_BY_ISLEAF
FROM hr.employees
  CONNECT BY PRIOR employee_id = manager_id
        START WITH last_name = 'Kochhar'
 ORDER SIBLINGS BY last_name;

list out all the manager that 'Lorentz' report to

SELECT LPAD(' ', 4*(level-1))||last_name "Last Name", salary, department_id,
       SYS_CONNECT_BY_PATH(last_name, '/') "Path", CONNECT_BY_ISLEAF
FROM hr.employees
        CONNECT BY employee_id = PRIOR manager_id
        START WITH last_name = 'Lorentz'
 ORDER SIBLINGS BY last_name;
  • pseudocolumn LEVEL -> root = 1, next level=2,3,4,5...etc
  • SYS_CONNECT_BY_PATH(col, '/') shows the full path, 2nd parameter is seperator (9i)
  • CONNECT_BY_ROOT(col) return the value of the root node in the current hierarchy (10g)
  • pseudocolumn CONNECT_BY_ISLEAF return 1 if the return value is at the last node on the Hierarchy (ie. leaf) (10g)
  • order SIBLINGS by re-order the sequence of the output and preserve the hierarchical relationship (10g)
  • connect by NOCYCLE prior child = parent
    • NOCYCLE means stop tranverse the hierarchy at the level when the child reference back to the root. (10g)
    • pseudocolumn CONNECT_BY_ISCYCLE evaluate to "1" if the current row references a parent. (10g)