Task 116:

Flight Statistics

Get statistics on flights departing from Domodedovo Airport (DME) in August 2017.
Output the result as a table with columns:
     count_departed - the total number of flights departing from the airport,
     count_delayed - the number of delayed flights (a flight that actually took off more than half an hour later than scheduled is considered delayed) and
     delayed_flights_rate - percentage of delayed flights rounded to two decimal places.

Use PostgreSQL syntax to write your answer. Descriptions of tables are provided in the right pane.

Write your request in the field below and click the "Check it!" button.

Tutorial Get hint

Copy code Clear editor

Bookings Database: table structure and schema overview

The Bookings database (PostgreSQL) models airline flights across multiple airports and is widely used for SQL practice.

This page shows the table structure, key columns, and constraints used in typical analytical and transactional SQL queries.

The Bookings database contains 8 main tables.

ER diagram of the Bookings database

The list of tables

aircrafts_data - table of aircrafts.

aircraft_codeUnique code for each aircraft
modelAircraft model name in English and Russian in JSON format
rangeAircraft fly range in kilometers

PRIMARY KEY, btree (aircraft_code)

	aircraft_code	model	range
1	773	{"en": "Boeing 777-300", "ru": "Боинг 777-300"}	11100

airports_data - table of airports.

airport_codeUnique code for each airport
airport_nameAirport name in English and Russian in JSON format
cityAirport city in English and Russian in JSON format
coordinatesAirport coordinates as POINT(longitude, latitude)
timezoneAirport timezone name

PRIMARY KEY, btree (airport_code)

	airport_code	airport_name	city	coordinates	timezone
1	YKS	{"en": "Yakutsk Airport", "ru": "Якутск"}	{"en": "Yakutsk", "ru": "Якутск"}	(129.77099609375,62.0932998657227)	Asia/Yakutsk

boarding_passes - table of boarding passes.

ticket_noTicket number
flight_idFlight identificator
boarding_noBoarding pass number
seat_noSeat number

PRIMARY KEY, btree (ticket_no, flight_id)
UNIQUE CONSTRAINT, btree (flight_id, boarding_no)
UNIQUE CONSTRAINT, btree (flight_id, seat_no)

FOREIGN KEY (ticket_no, flight_id) REFERENCES ticket_flights(ticket_no, flight_id)

	ticket_no	flight_id	boarding_no	seat_no
1	0005435212351	30625	1	2D

bookings - table of bookings.

book_refBooking number
book_dateBooking date
total_amountTotal booking cost

PRIMARY KEY, btree (book_ref)

	book_ref	book_date	total_amount
1	00000F	2017-07-05 00:12:00+00	265700.00

flights - table of flights.

flight_idFlight ID
flight_noFlight number
scheduled_departureScheduled departure time
scheduled_arrivalScheduled arrival time
departure_airportAirport of departure
arrival_airportAirport of arrival
statusFlight status
aircraft_codeAircraft code, IATA
actual_departureActual departure time
actual_arrivalActual arrival time

PRIMARY KEY, btree (flight_id)
UNIQUE CONSTRAINT, btree (flight_no, scheduled_departure)

	flight_id	flight_no	scheduled_departure	scheduled_arrival	departure_airport	arrival_airport	status	aircraft_code	actual_departure	actual_arrival
1	1185	PG0134	2017-09-10 06:50:00+00	2017-09-10 11:55:00+00	DME	BTK	Scheduled	319

seats - table of aircraft seats.

aircraft_codeAircraft code, IATA
seat_noSeat number
fare_conditionsTravel class

PRIMARY KEY, btree (aircraft_code, seat_no)

FOREIGN KEY (aircraft_code) REFERENCES aircrafts(aircraft_code) ON DELETE CASCADE

	aircraft_code	seat_no	fare_conditions
1	319	2A	Business

ticket_flights - ticket to flights relations.

ticket_noTicket number
flight_idFlight ID
fare_conditionsTravel class
amountTravel cost

PRIMARY KEY, btree (ticket_no, flight_id)

FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)

	ticket_no	flight_id	fare_conditions	amount
1	0005432159776	30625	Business	42100.00

tickets - table of tickets.

ticket_noTicket number
book_refBooking number
passenger_idPassenger ID
passenger_namePassenger name
contact_dataPassenger contact information

	ticket_no	book_ref	passenger_id	passenger_name	contact_data
1	0005432000987	06B046	8149 604011	VALERIY TIKHONOV	{"phone": "+70127117011"}

PRIMARY KEY, btree (ticket_no)

FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)

PostgreSQL 14 Administration Cookbook by Simon Riggs

Over 175 proven recipes for database administrators to manage enterprise databases effectively. This Postgres 14 book is for database administrators, data architects, database developers, and anyone with an interest in planning and running live production databases using PostgreSQL 14. Those looking for hands-on solutions to any problem associated with PostgreSQL 14 administration will also find this book useful. Some experience with handling PostgreSQL databases will help you to make the most out of this book, however, it is a useful resource even if you are just beginning your Postgres journey.

Practice

Lessons

Playground

Test

Flight Statistics

Login

Register

Flight Statistics