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
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 (table german_city with columns: id and name) in Germany and we don't calculate the distance using a gps function. Instead, you are given a second table called road (columns: city_from, city_to, time) with average trip durations on a given route from one city to another.

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 – ID of the last city,
  • total_time – total time spent driving,
  • count_places – the number of places visited, 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 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.