Comparing data in SQL using foreign keys 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 ﬂights 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