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 in PostgreSQL

Introduction
The very basics
Infinite looping
Processing graphs with recursive CTEs
31. Graphs – additional exercise
Summary

Instruction

Perfect! It's time for an exercise where you create the query all on your own!

Exercise

This time, we're traveling between cities in Germany. We'll use two tables:

  • german_city which contains the id and name, and
  • road, which contains the columns city_from, city_to, and time.

In this query, we won't be using GPS coordinates to calculate the distance; we'll use the time column instead. It contains the average trip duration from one city to another, on a given route.

Let's travel between the four cities, starting in Berlin. Your task is to list the travel paths starting in Berlin and covering all four cities. Order the paths in descending order by total_time.

In your answer, provide the following columns:

  • path – City names, separated by '-'.
  • last_id – The ID of the last city.
  • total_time – The total time spent driving.
  • count_places – The number of places visited, which should equal 4.

Stuck? Here's a hint!

This one can be tough, so here is a hint: In the recursive member, you will have to join the recursive table you create with the table road. And you'll have to join the table german_city twice (one JOIN for city_from, the other one for city_to). In the termination check, use position(x IN path) = 0 to make sure that city x has not been visited so far.