O'Reilly logo

Mastering Oracle SQL by Alan Beaulieu, Sanjay Mishra

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 8. Hierarchical Queries

A relational database is based upon sets, with each table representing a set. However, there are some types of information that are not directly amenable to the set data structure. Think, for example, of an organization chart, a bill of material in a manufacturing and assembly plant, or a family tree. These types of information are hierarchical in nature, and most conveniently represented in a tree structure. In this chapter we discuss how we can represent such hierarchical information in a relational table. We also discuss in detail various SQL constructs that we need to use to extract hierarchical information from a relational table.

Representing Hierarchical Information

Let’s look at an example to understand how we can represent hierarchical information in a relational database. As a basis for the example, we’ll use an organization chart showing how one employee reports to another within a large organization, as shown in Figure 8-1.

An organization chart

Figure 8-1. An organization chart

Figure 8-1 represents a hierarchy of employees. The information regarding an employee, his manager, and the reporting relationship need to be represented in one table, EMPLOYEE, as shown in the Entity Relationship Diagram in Figure 8-2.

Entity Relationship Diagram of the EMPLOYEE table

Figure 8-2. Entity Relationship Diagram of the EMPLOYEE ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required