postgres_air Database

This database contains several tables that involve customers (account table), that may also be frequent flyers (frequent_flyer table), who make air travel bookings (booking) for passengers (passenger). Each booking is composed of one or more legs (booking_leg), and each leg corresponds to a specific direct flight on a specific date (flight) between two airports (airport). Each flight has an aircraft (aircraft) assigned to it. There is a boarding pass (boarding_pass) for each passenger on each leg.

The database model illustrates its structure:

Queries on postgres_air

These queries will help you get familiar with the structure and contents of the postgres_air database. Discuss the queries and related questions with your group.

1. The flight data in the database is well-structured, so that before a specific date all the flights have landed, and after that date none of the flights have taken off. Write a query to find that date. Write a query to check if there are any flights that have taken off but not landed.

2. How many flights have no passengers booked on them?

3. Find the top 10 (using LIMIT 10) flights with the most passengers on them along with the airports those flights leave from. Now include the airport names, cities and countries to see.

4. Find the top 10 (using LIMIT 10) longest flights along with the airports those flights leave from. Now include the airport names, cities and countries to see.

5. Are there any bookings with no legs?

6. Select each booking_id, along with its legs, with each leg's flight departure and arrival airports. Does it seem that most bookings are for round-trip or one-way trips? What is the difference between the booking_leg_id and the leg_num?

7. The database model shows a frequent flyer can be related to with 0 or more accounts. Does the data include any frequent flyers not associated with an account, or frequent flyers associated with more than 1 account?

8. What is the average velocity of the aircraft that depart from airport 'BCN'?