Suppose you have a database modeling a very simple airline reservation system, g

Question

Suppose you have a database modeling a very simple airline reservation system, given by the following schema:

Passenger (pID, pName, pCity, pCountry)

Airline(aID, aName, aCountry)

Airport(apCode, apCity, apCountry)

Flight(fnum, aID, deptdate, deptTime, arrTime, originAp, destinAp)

Books(pID, fnum, deptdate, price)

In this schema, each passenger is identified by aa pID, and has a name, a home city, and a home country. Airlines have an aID, a name, and a country where their headquarter is located. Airports are identified by a 3-letter airport code (e.g., JFK or LAX), and have a nearby city (e.g., New York for JFK) and a country. A city could of course have several airports. A flight has flight number (e.g., AA45 say for flight 45 run by American Airlines), an airline that operates the flight, a departure date, departure and arrival times, and origin and destination airport codes. We only model direct flights, and if a flight happens every day, there would be a separate entry for each date in the Flight table, with the same fnum but different values of deptdate. For simplicity, we assume every flight arrives on the same day it departs, and we ignore different time zones. Finally, passengers book flights; the schema does not enforce any limits on the number of tickets or seats per flight.

Write statements in SQL for the following queries.

I. List the name of any passenger who has booked a flight from Japan to the US for November 5, 2018.

II. List the name of any passenger who has booked two flights with less than an hour transfer time, that is, they have booked a flight that departs less then one hour after their previous flight arrives.

III. For each destination airport that can be reached directly from JFK, output the number of people who flew from JFK to that destination in 2017.

IV. Output the name of the airline that had the largest number of passenger bookings in 2017.

V. Output the name of any passenger who took at least two flight from Japan to the US in 2017.

VI. Output the pIDs of any pair of passengers that took exactly the same flights in 2017. (Meaning, whenever one of them booked a flight, the other one also booked the same flight.)

VII. Output the airport codes of any airports that can be reached from JFK using at most two flights.

Explanation / Answer

I.select pName from Passanger P join Books B on p.pID=B.pID

join Flight F on F.fnum=B.fnum where B.deptdate='November 5,2018' and F.originAp='JP' and F.destinAp='US';

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.