SQL code copied to buffer

Group tasks by:

complexity

Find the median booking amount

Find the median booking amount in July 2017, according to the total_amount column of the bookings table.
Display the result in the median_amount column.
The median is the value such that half of the records have an amount lower than the specified value and the other half have an amount higher than it.

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

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)

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

Test

Find the median booking amount

Bookings Database (PostgreSQL)

The list of tables:

Login

Register

Find the median booking amount

Bookings Database (PostgreSQL)

The list of tables: