Introduction
Very simple subqueries
7. Subqueries with functions
Subqueries with multiple results
Correlated subqueries
Other subqueries

Instruction

Nice! Next, we can write more complicated subqueries by including some functions in them. Take a look:

SELECT
  *
FROM HikingTrip
WHERE HikingTrip.Length < (
  SELECT
    AVG(HikingTrip.Length)
  FROM HikingTrip
);

Now, our query looks for all hiking trips with a shorter distance than the average across all hiking trips. As you can see, we used the AVG() function in the subquery. As you may recall, the AVG() function gives us the average value of a column.

Exercise

Find all information about trips that are more expensive than the average across all trips.

Stuck? Here's a hint!

Type:

SELECT
  *
FROM Trip
WHERE Trip.Price > (
  SELECT
    AVG(Trip.Price)
  FROM Trip
);