Comparing data in SQL using foreign keys

Question

The following is given:

CREATE TABLE City (
 city_name varchar(255),
 country varchar(255),
 primary key (city_name)
 );
insert into City(city_name, country) values('London', 'England');
insert into City(city_name, country) values('Santiago', 'Chile');
insert into City(city_name, country) values('Edinburgh', 'Scotland');
insert into City(city_name, country) values('Tallinn', 'Estonia');
insert into City(city_name, country) values('Madrid', 'Spain');
insert into City(city_name, country) values('Paris', 'France');
CREATE TABLE Airport (
 airport_code int,
 city_name varchar(255),
 primary key (airport_code)
 );
insert into Airport(airport_code, city_name) values (123, 'London');
insert into Airport(airport_code, city_name) values (431, 'London');
insert into Airport(airport_code, city_name) values (439, 'Tallinn');
insert into Airport(airport_code, city_name) values (252, 'Santiago');
insert into Airport(airport_code, city_name) values (240, 'Paris');
insert into Airport(airport_code, city_name) values (992, 'Edinburgh');
insert into Airport(airport_code, city_name) values (972, 'Tallinn');
insert into Airport(airport_code, city_name) values (754, 'Tallinn');
CREATE TABLE FlightDelays (
 flight_code varchar(255),
 origin int,
 destination int,
 average_minute_delay int,
 price int,
 primary key (flight_code),
 foreign key (origin) references Airport (airport_code),
 foreign key (destination) references Airport (airport_code)
 );
insert into FlightDelays(flight_code, origin, destination, average_minute_delay, price) values 
('XQ1242', 123, 431, 10, 20);
insert into FlightDelays(flight_code, origin, destination, average_minute_delay, price) values 
('PX1203', 439, 252, 2, 20);
insert into FlightDelays(flight_code, origin, destination, average_minute_delay, price) values 
('OO2442', 992, 972, 9, 20);
insert into FlightDelays(flight_code, origin, destination, average_minute_delay, price) values 
('XK1442', 972, 431, 15, 20);
insert into FlightDelays(flight_code, origin, destination, average_minute_delay, price) values 
('PW4332', 123, 754, 40, 20);
insert into FlightDelays(flight_code, origin, destination, average_minute_delay, price) values 
('QJ332', 754, 431, 4, 20);

Question:

Express the following query in SQL: "What are the flights whose origin airport and destination airport are in the same city?"

How can I improve my solution?

select z.flight_code, z.origin, z.destination 
from (
 select a.flight_code, a.origin, a.oCity, a.destination, Airport.city_name as dCity
 from (
 select FlightDelays.flight_code, FlightDelays.origin, Airport.city_name as oCity, FlightDelays.destination
 from FlightDelays
 inner join Airport on FlightDelays.origin = Airport.airport_code) as a
 inner join Airport on a.destination = Airport.airport_code) as z
where oCity=dCity;

Answer 1

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

• Mast: Usually an alternative implementation without explanation or review is not acceptable as answer, However, in this case I'm not sure what would have to be added. It's perfectly clear. Just try to keep it in mind for your next answer :-)