Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
The very basics
Infinite looping
Processing trees with recursive CTEs
Processing graphs with recursive CTEs
Summary

Instruction

You're doing great! Now that you know the basics, we will examine some real-world examples of recursion.

Let's say we have a company with a hierarchical structure, i.e. each employee has a superior (except for the boss). Each employee has precisely one superior, but one superior may supervise multiple employees. An example structure may look like this:

recursion

In computer science, we call such a structure a tree, where each node (employee) has a parent node (superior), except for the root (boss), which has no parent node.

How can we store these hierarchical relations in a database? It's quite simple: each employee is represented with a row that has a column superior_id with the ID of their superior.

recursion

Check out the employee table to see what we're talking about.

Exercise

Select all data from employee table. It contatins the following columns:

  • id – A unique idetifier for each employee.
  • first_name – The employee's first name.
  • last_name – The employee's last name.
  • superior_id – The identifier of the employee's supervisor or manager.