SET SCHEMA 'airtraffic';

-- We want to know the information of all flights with the name of cities instead of the airport codes

SELECT * 
FROM flights;


SELECT * 
FROM flightcodes;


SELECT * 
FROM flightcodes,flights;



SELECT * 
FROM flightcodes,flights
WHERE flights.code = flightcodes.code;

SELECT *
FROM flightcodes INNER JOIN flights
ON flights.code = flightcodes.code;

SELECT *
FROM flightcodes NATURAL JOIN flights;









SELECT *
FROM airports;

SELECT * 
FROM flightcodes,flights,airports
WHERE flights.code = flightcodes.code
	AND airports.code = flights.departureairport;


SELECT * 
FROM flightcodes,flights,airports
WHERE flights.code = flightcodes.code
	AND airports.code = flights.destinationairport;


SELECT * 
FROM flightcodes,flights,airports
WHERE flights.code = flightcodes.code
	AND airports.code = flights.departureairport
	AND airports.code = flights.destinationairport;







-- We need two cities and we only have one.... 
-- ........
-- ......
-- ....
-- ...
-- ..
-- .

-- Cartesian product!!!!	

SELECT city, city 
   FROM Airports, Airports;


SELECT D.city AS departureCity, A.city AS destinationCity
   FROM airtraffic.Airports AS D, airtraffic.Airports AS A;
	








-- Let's put it together...

SELECT *
   FROM Flights, Airports AS D, Airports AS A;



-- Looks nice, but...

SELECT departureAirport, D.city AS departureCity, destinationAirport,
		A.city AS destinationCity, departureTime, arrivalTime, Flights.code
   FROM Flights, Airports AS D, Airports AS A;









-- Now it's clear what we need to filter out....
		
	
	
SELECT departureAirport, D.city AS departureCity, destinationAirport,
		A.city AS destinationCity, departureTime, arrivalTime, Flights.code
   FROM Flights, Airports AS D, Airports AS A
   WHERE D.code = departureAirport AND A.code = destinationAirport
	
