FLIGHT = ( FlightN, FromCity, ToCity, Date, DepartureTime, ArrivalTime ) //. You may use <, >, !=, or = between any two dates or between any two times. Also, you may assume the attribute Date = arrival date= departure date and that ToCity and FromCity are in the same time zone. TICKET = ( TicketN, FlightN, Cost, Completed ) // Since Ticket# and Flight# together are the key, this means a single ticket may include several flights. Completed may assume the values ‘Yes’ or NULL, Null means the flight hasn’t been completed.
PASSENGER = ( Name , TicketN ) // This table includes every passenger and the ticket numbers he or she purchased. Assume the name of the passenger is unique.
Express the following queries by SQl statements using a minimum number of tables and operations:
1. List every possible flight schedule from "LA’ to "NY" with only one stop. The flight schedule includes DepartureTime time from LA, Name of a stop city, ArrivalTime to the stop, DepartureTime from the stop, ArrivalTime to NY. Assume both flights are on the same date.
2. Retrieve the names of every passenger and total amount paid for the tickets he/she purchased.
3. Retrieve the names of all passengers who have traveled to "LA’ at most two times. .