SQL Assessment: Aircraft usage analysis

SQL code copied to buffer

Group tasks by:

complexity

category

database

1. Find clients who have met each other

Task 106:

Count the number of flights actually performed by each aircraft model during August 2017.
Output the result in four columns: aircraft_code, model_ru, model_en, flights_count sorted by descending number of flights.

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

Use PostgreSQL syntax to write your answer. Descriptions of tables are provided at the bottom of the screen.

Get hint Copy code Clear editor

Bookings Database (PostgreSQL)

The subject area of this database is airline flights through various airports. The Bookings Database contains 8 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)

Learn PostgreSQL: Use, manage and build secure and scalable databases with PostgreSQL by Luca Ferrari & Enrico Pirozzi

Learn PostgreSQL: Use, manage and build secure and scalable databases with PostgreSQL by Luca Ferrari & Enrico Pirozzi

This new edition will help you learn PostgreSQL from scratch with the latest version, providing a complete focused view on aspects like configuration, high performance, partitioning, backup, server-side programming and replication.