Oracle9i Study Notes-17 grading Data Retrieval

1. Hierarchical Query

Select [level], column, expr...
From table
[Where condition (s)]
[Start with condition (s)]
[Connect by prior condition (s)];

Level: returns a level for each row, 1 for the Level pseudo column of the root row, 2 for the Level pseudo column of the child, and so on.
Start with: Specifies the root row of a level.
Connct by prior: Specifies the query direction

1) The start with clause can be used together with any valid conditions.
Start with column1 = Value

2) use the EMP table, starting with an employee named King.
... Start with ename = 'King'
... Start with Mgr is null

3) a start with condition can contain subqueries.
... Start with empno = (select empno
From EMP
Where ename = 'King ')
Connect by prior column1 = column2

4) use the EMP table to traverse from top to bottom:
... Connect by prior empno = Mgr

5) use the EMP table to traverse from the bottom up:
... Connect by empno = prior Mgr

2. Traverse tree:

1) Top-Down Traversal

Example:
Select level, empno, ename | 'employee's manager is '|
Prior ename "employment relationship"
From EMP
Start with ename = 'King'
Connect by prior empno = Mgr;
Result:
Level empno employment relationship
1. The manager of King employees is
2. The manager of Jones's employee is king.
3. Scott's employee manager is Jones.
4 7876 the manager of Adams's employee is Scott
3. The manager of Ford employees is Jones.
4 7369 Smith's employee's manager is Ford
2 7698 the manager of the employee Blake is king.
3. The manager of Allen's employee is Blake.
3. The manager of Ward employee is Blake.
3. The manager of Martin's employee is Blake.
3. The manager of Turner's employee is Blake.
3. The manager of James's employee is Blake.
2. The manager of Clark's employee is king.
3. The manager of Miller's employee is Clark.

2) traverse from the bottom up
 
Example:
Select level, empno, ename, job, Mgr
From EMP
Start with empno = 7876
Connect by empno = prior Mgr;
Result:
Level empno ename job Mgr
1 7876 Adams clerk 7788
2 7788 Scott analyst 7566
3 7566 Jones manager 7839
4 7839 King President

3. format the grading report with level and lpad
Create a report to show the management level of the company, starting from the highest level, indent the following level

Example:
Select level, lpad (ename, length (ename) + (level * 2)-2, '-') as org_chart
From EMP
Start with ename = 'King'
Connect by prior empno = Mgr;

Result:
Level org_chart
1 King
2 -- Jones
3 ---- Scott
4 ------ Adams
3 ---- Ford
4 ------ Smith
2 -- blake
3 ---- Allen
3 ---- ward
3 ---- Martin
3 ---- Turner
3 ---- James
2 -- Clark
3 ---- Miller

4. Trim Branch
1) Remove a node using the WHERE clause
 
Where ename! = 'Scott'

2) Remove a branch using the connect by clause

Connect by prior
Empno = Mgr
And ename! = 'Scott'

 

Exercise
1. Design a table to record the tree structure of the customer's calls within a period of time. For example, user a calls user B at 05:10, user B calls user C at 05:05, user B calls user d at, and user D calls user e.
Query hierarchical relationships by time period