Q5 - Why do I need to specify "flight AS f" or carrier as "f.carrier"?


#1

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

I got the code to work and I have the correct answer. I just don't know why I have to have an alias for flights As f in line 2. But in line 5, I say "from flights," not "from f." And why do I have to specify that it's coming from carrier = f.carrier?



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


#2

I think f is an indicator of the id you are working on, so that the code could calculate the average distance of the carrier of the id (indicated by that f)


#3

You should always use aliases in SQL.
Over time Your Queries will get complexer. Adding aliases for all tables improves the readability immensely and is generally best practice. Also you wanna have the one and same alias for one table in all selects. That also makes things easier to read/understand.

SELECT id
FROM flights a
WHERE distance < (
 SELECT AVG(distance)
 FROM flights b
 WHERE a.carrier = b.carrier);

#4

Why must write WHERE a.carrier=b.carrier?


#5

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