

-- Airports (_code_,city)
-- FlightCodes (_code_, airlineName)
-- Flights (_code_, departureAirport, destinationAirport)
--   code -> FlightCodes.code
--   departureAirport -> Airports.code
--   destinationAirport -> Airports.code


CREATE TABLE Airports (
  code CHAR(3) PRIMARY KEY,
  city TEXT NOT NULL);

CREATE TABLE FlightCodes (
  code TEXT PRIMARY KEY,
  airlineName TEXT NOT NULL);

CREATE TABLE Flights (
  code TEXT PRIMARY KEY REFERENCES FlightCodes,
  departureAirport CHAR(3) NOT NULL REFERENCES Airports,
  destinationAirport CHAR(3) NOT NULL REFERENCES Airports); 


INSERT INTO Airports VALUES ('GOT', 'Gothenburg');
INSERT INTO Airports VALUES ('FRA', 'Frankfurt');
INSERT INTO Airports VALUES ('ORY', 'Paris');
INSERT INTO Airports VALUES ('MLA', 'Malta');
INSERT INTO Airports VALUES ('MUC', 'Munich');

INSERT INTO FlightCodes VALUES ('SK111', 'SAS');
INSERT INTO FlightCodes VALUES ('AF222', 'Air France');
INSERT INTO FlightCodes VALUES ('AB222', 'Air Berlin');
INSERT INTO FlightCodes VALUES ('KM111', 'Air Malta');

INSERT INTO Flights VALUES ('SK111', 'GOT', 'FRA');
INSERT INTO Flights VALUES ('AF222', 'ORY', 'MLA');
INSERT INTO Flights VALUES ('AB222', 'FRA', 'MUC');
INSERT INTO Flights VALUES ('KM111', 'MUC', 'MLA');


-- Query with the table in the exercise
SELECT F.code       AS flight_code, 
       airlineName  AS airline,
       Dep.city     AS DepCity,
       Dep.code     AS DepAirport,
       Dest.city    AS ArrCity,
       Dest.code    AS ArrAirport       
  FROM Flights F 
       JOIN FlightCodes USING (code)
       JOIN Airports Dep ON (departureAirport = Dep.code)
       JOIN Airports Dest ON (destinationAirport = Dest.code);
