Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
Instruction
Good! Still remember the ALL operator? Let's use it in a correlated subquery.
SELECT
*
FROM Trip MainTrip
WHERE Price >= ALL (
SELECT
SubTrip.Price
FROM Trip SubTrip
WHERE MainTrip.CityID = SubTrip.CityID
);
The above query looks for the most expensive trips to each city. The agency only chooses trips with a price greater than or equal to all trip prices in a given city.
Exercise
Select the hiking trip with the longest distance (Length column) for every mountain.
Stuck? Here's a hint!
Type:
SELECT
*
FROM HikingTrip MainHikingTrip
WHERE MainHikingTrip.Length >= ALL (
SELECT
SubHikingTrip.Length
FROM HikingTrip SubHikingTrip
WHERE MainHikingTrip.MountainID = SubHikingTrip.MountainID
);
