ITPEC FE Morning April 2022 Question 27

City listed twice under the aliases c1 and c2 — the origin and the destination are two different City rows, so each needs its own copy of the table in the join.

Each flight refers to City twice. In one result row the origin name and the destination name come from different City rows, which a single reference to the table cannot supply. Correlation names let FROM list the table twice:

• •City c1, joined on Flight.OriginCityID = c1.CityID, so c1.CityName is the origin
• •City c2, joined on Flight.DestinationCityID = c2.CityID, so c2.CityName is the destination

Answer (c)

Why not others:

• •(a) — one City row must satisfy both conditions, so only flights whose origin and destination are the same city qualify, normally none, and both columns would show the same name.
• •(b) — the same single-City join, so the same empty result, and it selects the city IDs instead of the city names.
• •(d) — aliases Flight twice instead of City: f1 and f2 are unrelated, so every flight's origin is paired with every flight's destination, a Cartesian product of wrong combinations.

Key rule: When one row refers to the same table through two foreign keys, list that table twice in FROM under different aliases and join each alias on its own key.

Databases · AI-written, may contain errors