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

SQL Basics

Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
17. The operator ALL in correlated subqueries
Other subqueries

Instruction

Good. Still remember the operator ALL? Let's use it in a correlated subquery.

SELECT *
FROM trip main_trip
WHERE price >= ALL (
  SELECT price
  FROM trip sub_trip
  WHERE main_trip.city_id = sub_trip.city_id
);

The above query looks for all trips which are the most expensive of all trips to that specific city. The instructions only choose trips with their price equal to or greater than all trip prices in the specific city.

Exercise

Select the hiking trip with the longest distance (column length) for every mountain.

Stuck? Here's a hint!

Type:

SELECT *
FROM hiking_trip main_hiking_trip
WHERE length >= ALL (
  SELECT length
  FROM hiking_trip sub_hiking_trip
  WHERE main_hiking_trip.mountain_id = sub_hiking_trip.mountain_id);