SQL Assessment: Number of passengers with total

SQL code copied to buffer

Group tasks by:

complexity

category

database

Help make SQLtest.online even better!

Greetings, SQL lovers!

I am writing to you today because I need your help.

SQLtest.online is a free platform designed to help people of all levels master SQL.
We offer a wide range of interactive tests, problems and training materials to help you improve your SQL skills.
The platform has already helped a lot of people, but we want to make it even better. And this is where you can help us!

How you can help:

Invite your friends and colleagues to join SQLtest.online!
Tell your friends and colleagues about SQLtest.online. Share a link to our site on social networks, by email or in person.
Write an article or blog post about SQLtest.online. Share your experience with the platform.
Together we can make SQLtest.online the best resource for learning SQL!

The more people use the platform, the better it will become. We'll be able to add more content, improve features, and create a better community for SQL enthusiasts.

Thanks for your help!
Command SQLtest.online

Task

Improve the solution previous problem by adding a line to the output with the total value Total at the end of the list.

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.

Get hint

Copy code Clear editor

Explore over 360 diverse tasks on our platform.

Bookings Database (PostgreSQL)

The subject area of this database is airline flights through various airports.

ER diagram of the Bookings database

The Bookings Database contains 8 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)