trjhtr

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;

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