5. Correlated Subqueries I: Confusing WHERE clause


#1

https://www.codecademy.com/courses/sql-table-transformation/lessons/subqueries/exercises/correlated-i?action=lesson_resume

Hello. I am currently doing the SQL tutorial called: 5. Correlated Subquired I. The goal of this part of the tutorial is to find the id's of flights which whose flight distance is below the average flight distance for their carrier.

The entire code for the correct answer looks like this:



SELECT id
FROM flights AS f
WHERE distance > (
   SELECT AVG(distance)
   FROM flights
   WHERE carrier = f.carrier);

I'm puzzled by the "WHERE carrier = f.carrier" clause. Does it let the program know, that we need to specificy the average for each carrier individually? If so, does this code below achieve the same result?

SELECT id
FROM flights AS f
WHERE distance > (
   SELECT carrier, AVG(distance)
   FROM flights
   GROUP BY 1;


#2

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.