Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries

Introduction
The very basics
Infinite looping
Processing graphs with recursive CTE
24. Graphs in computer science
Summary

Instruction

Very well done! Since you already know how to tackle hierarchical structures represented in the form of trees, it's time to move on and introduce the second type of real-world situations we can tackle with recursive CTEs: graphs.

Graphs consists of vertices and edges. In real life graphs can often correspond to road maps. Take a look:

recursion

In a database, we may store vertices (cities) in one table, and the edges (roads between them) in another one:

city
name
London
Cambridge
...
road
name_from name_to distance_km
London Cambridge 78
... ... ...

For educational purposes, we will simplify things a bit. We will only use a single table city with the city name and gps coordinates and we assume that you can go directly from each city to another.

First, let's take a look at this table.

Exercise

Select all data from table city.

The table contains the following columns:

  • name – name of the city.
  • lat – latitude of the city.
  • lon – longitude of the city

Last two columns indicates coordinates. Based on those coordinates, we will calculate the distance between two cities in a straight line and treat it as the distance.