18 Self Joins and Hierarchical Queries

·         Joins a table to itself-self join

·         Retrieves data based on a natural hierarchical relationship between rows in a table- hierarchical query

·         Determines the number of steps down from the beginning row that should be returned by a hierarchical query-LEVEL

·         Identifies the beginning row for a hierarchical query-START WITH

·         Specifies the relationship between parent rows and child rows of a hierarchical query-CONNECT BY  ( PRIOR on either side of equal sign e.g. …PRIOR abc = xyz  /  abc = PRIOR  xyz)

For each problem, use the Oracle database.

1.  Display the employee’s last name and employee number along with the manager’s last name and manager number. Label the columns: Employee, Emp#, Manager, and Mgr#, respectively.

SELECT emp.last_name "Employee", emp.employee_id "Emp#", mgr.last_name "Manager", mgr.employee_Id  "Mgr#"

FROM  employees emp LEFT OUTER JOIN  employees  mgr  ON emp.manager_id  = mgr.employee_Id; 

But Reading next question suggests, problem wanted INNER JOIN instead, to demonstrate the difference J

SELECT emp.last_name "Employee", emp.employee_id "Emp#", mgr.last_name "Manager", mgr.employee_Id  "Mgr#"

FROM  employees emp INNER JOIN  employees  mgr  ON emp.manager_id  = mgr.employee_Id; 

2.  Modify question 1 to display all employees and their managers, even if the employee does not have a manager. Order the list alphabetically by the last name of the employee.

SELECT emp.last_name "Employee", emp.employee_id "Emp#", mgr.last_name "Manager", mgr.employee_Id  "Mgr#"

FROM  employees emp LEFT OUTER JOIN  employees  mgr  ON emp.manager_id  = mgr.employee_Id

ORDER BY  "Employee";

3.  Display the names and hire dates for all employees who were hired before their managers, along with their managers’ names and hire dates. Label the columns Employee, Emp Hired, Manager and Mgr Hired, respectively.

If I want to include King who has no manager, and he is the super boss:

SELECT emp.last_name "Employee",  emp.hire_date "Emp Hired",   mgr.last_name "Manager", mgr.hire_date "Mgr Hired"

FROM  employees emp LEFT OUTER JOIN  employees  mgr  ON emp.manager_id  = mgr.employee_Id

WHERE emp.hire_date < NVL( mgr.hire_date, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))

ORDER BY  "Employee";

If I don’t care for King, which is the most, probably scenario problem refers:

SELECT emp.last_name "Employee",  emp.hire_date "Emp Hired",   mgr.last_name "Manager", mgr.hire_date "Mgr Hired"

FROM  employees emp LEFT OUTER JOIN  employees  mgr  ON emp.manager_id  = mgr.employee_Id

WHERE emp.hire_date < mgr.hire_date

ORDER BY  "Employee";

4.  Write a report that shows the hierarchy for Lex De Haans department. Include last name, salary, and department id in the report.

SELECT last_name, salary, department_id

FROM employees

START WITH first_name = 'Lex'  AND last_name = 'De Haan'

CONNECT BY PRIOR employee_id = manager_id;

5.  What is wrong in the following statement?

SELECT last_name, department_id, salary

FROM employees

START WITH last_name = 'King'

CONNECT BY PRIOR manager_id = employee_id;

Query in problem says go from tree leave to tree base, but King is itself the base, so the result of query in problem is only one row, the king itself.  So there, is nothing wrong as per SQL in the query in problem, but logically thinking considering the data we have, king is the super boss.

To support my answer, let’s study similar case:

SELECT LEVEL, last_name, department_id, salary

FROM employees

START WITH first_name = 'Lex'  AND last_name = 'De Haan'

CONNECT BY PRIOR manager_id = employee_id;

Gives same result as

SELECT LEVEL, last_name, department_id, salary

FROM employees

START WITH first_name = 'Lex'  AND last_name = 'De Haan'

CONNECT BY  employee_id = PRIOR manager_id;

6.  Create a report that shows the organization chart for the entire employee table. Write the report so that each level will indent each employee 2 spaces. Since Oracle Application Express cannot display the spaces in front of the column, use - (minus) instead.

SELECT LPAD(last_name, LENGTH(last_name) + (LEVEL-1)*2, '-') "organization chart "

FROM employees

START WITH last_name = ( SELECT last_name from employees WHERE manager_id IS NULL)

CONNECT BY PRIOR employee_id = manager_id;

Super boss will be at level 1, not the zero.

7.  Re-write the report from 6 to exclude De Haan and all the people working for him.

SELECT LPAD(last_name, LENGTH(last_name) + (LEVEL-1)*2, '-') "organization chart "

FROM employees

START WITH last_name = ( SELECT last_name from employees WHERE manager_id IS NULL)

CONNECT BY PRIOR employee_id = manager_id AND last_name != 'De Haan';