data4.sql

-- (g) Obtain ticket numbers and names of all passengers departing from London.

SELECT "Booking"."ticketNo", "Booking"."name" FROM "Booking" LEFT JOIN "Flight" ON "Booking"."flightNo" = "Flight"."flightNo" LEFT JOIN "Airport" ON "Flight"."depAirport" = "Airport"."airportId" WHERE "Airport"."city" = 'London' 

-- (h) Retrieve the flight number and flight company of all flights from London to Paris.
SELECT "Flight"."flightNo", "Flight"."flightCompany" FROM "Flight" LEFT JOIN "Airport" AS "A1" ON "Flight"."depAirport" = "A1"."airportId" LEFT JOIN "Airport" AS "A2" ON "Flight"."arrAirport" = "A2"."airportId" WHERE "A1"."city" = 'London' AND "A2"."city" = 'Paris' 

-- (i) List the ticket numbers and names of all passengers travelling in Business class.
 SELECT "Booking"."name", "Booking"."ticketNo" FROM "Booking" LEFT JOIN "Seat" ON "Booking"."seatNo" = "Seat"."seatNo" AND "Booking"."flightNo" = "Seat"."flightNo" WHERE "Seat"."class" = 'Economy'

-- (j) Find the names and nationalities of all the Business class passengers travelling from London to Paris.

SELECT "Booking"."name", "Booking"."nationality" FROM "Booking" 
LEFT JOIN "Flight" as "F" on Booking.flightNo=F.flightNo
LEFT JOIN "Airport" AS "A1" ON "F"."depAirport" = "A1"."airportId"
LEFT JOIN "Airport" AS "A2" ON "F"."arrAirport" = "A2"."airportId"

LEFT JOIN "Seat" as "S" ON "Booking"."seatNo" = "S"."seatNo" AND "Booking"."flightNo" = "S"."flightNo"

WHERE "S"."class" = 'Economy'
and "A1"."city" = 'London' AND "A2"."city" = 'Paris';