HR Hierarchy

A friend asked me to explain the hierarchical query, and when I went back to look at what I had on the blog, there was little explanation, and more of just a sample for me to follow next time I had to write one. So I decided to redo it in the HR schema and add a few comments. All the special words used for hierarchical queries are shown in red.

Line 3, 7, 8, 9 have to do with padding the name on the right with some dots. (for example: "3. Smith . . . .") Totally unnecessary, this was just for fun.

Line 4 is where I'm indenting the level#. I don't want to indent level 1 so I first subtract 1 from the level. I then multiply by 3, but you can make this any number you want. In my case:
Level 2 is indented by 3 spaces (2-1)*3
Level 3 is indented by 6 spaces (3-1)*3
Level 4 is indented by 9 spaces (4-1)*3
(If this is going to be HTML output, you'll have to follow this post.)

Line 5 concatenates a period and one space onto the back of the level number. (for example, "3. ")

Line 6 concatenates the last name, a comma, a space, and the first name together. (for example: "King, Steven")

Line 15 just got thrown in there to create a shorter output for this example.

Line 16 and 17 say that we want to use Steven King as level 1.

Line 18: I can't explain this better than Tom does in this post.

Line 19 says if there is more than one sibling on a level, sort by last_name, and within that, first_name.

/* 1*/ Select Employee_Id,
/* 2*/        -----------------------------------------
/* 3*/               Rpad(
/* 4*/                      Lpad(' ',3*(Level-1))
/* 5*/                    ||Level||'. '
/* 6*/                    ||Last_Name||', '||First_Name
/* 7*/                   ,30
/* 8*/                   ,'. '
/* 9*/                   )
/*10*/               As
/*11*/        Employee_Name,
/*12*/        -----------------------------------------
/*13*/        Manager_Id
/*14*/   From Employees
/*15*/  Where Department_Id Not in (50, 80)
/*16*/  Start With Last_Name  = 'King'
/*17*/         And First_Name = 'Steven'
/*18*/Connect By Manager_Id = Prior Employee_Id
/*19*/  Order Siblings By Last_name, First_name;