Hi! Could we please enable some services and cookies to improve your experience and our website?

Privacy & Cookie Policy.
SQL code copied to buffer

SQLtest.online

Practice

Lessons

Test

RU PT FR
Task 115: 
Find the fastest flight option between Pulkovo Airport (LED) and Bryansk Airport (BZK) (with one transfer) on the date August 16, 2017. For time calculations, use the fields scheduled_departure and scheduled_arrival
Display a table with fields:
     flight1_no - code for the first flight, 
     departure_airport - departure airport code, 
     connection_airport - transfer airport code,
     flight1_time - first flight duration,
     connection_time - waiting time,
     flight2_no - code for the second flight,
     arrival_airport - airport code,
     flight2_time - second flight duration,
     total_trip_time - total trip time.

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 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_codemodelrange
1773{"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_codeairport_namecitycoordinatestimezone
1YKS{"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_noflight_idboarding_noseat_no
100054352123513062512D
bookings - table of bookings.
  • book_refBooking number
  • book_dateBooking date
  • total_amountTotal booking cost
  • PRIMARY KEY, btree (book_ref)
book_refbook_datetotal_amount
100000F2017-07-05 00:12:00+00265700.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
11185PG01342017-09-10 06:50:00+002017-09-10 11:55:00+00DMEBTKScheduled319
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_codeseat_nofare_conditions
13192ABusiness
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
1000543215977630625Business42100.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
1000543200098706B0468149 604011VALERIY TIKHONOV{"phone": "+70127117011"}
  • PRIMARY KEY, btree (ticket_no)
  • FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)
PostgreSQL 14 Administration Cookbook by Simon Riggs
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.