2
\$\begingroup\$

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;
\$\endgroup\$
2
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ 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 :-) \$\endgroup\$ – Mast May 1 '18 at 7:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.