Comparing data in SQL using foreign keys
Just think simple:

select f.flight_code, f.origin, f.destination
  from FlightDelays f
  join Airport fo on fo.airport_code = f.origin
  join Airport fd on fd.airport_code = f.destination
 where fo.city_name = fd.city_name


You can try at SQL Fiddle