SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE aircrafts ( aircraft_code character(3) NOT NULL, model text NOT NULL, range integer NOT NULL, CONSTRAINT aircrafts_range_check CHECK ((range > 0)) ); INSERT INTO aircrafts (aircraft_code, model, range) VALUES ('773', 'Boeing 777-300', 11100), ('763', 'Boeing 767-300', 7900), ('SU9', 'Sukhoi SuperJet-100', 3000), ('320', 'Airbus A320-200', 5700), ('321', 'Airbus A321-200', 5600), ('319', 'Airbus A319-100', 6700), ('733', 'Boeing 737-300,', 4200), ('CN1', 'Cessna 208 Caravan', 1200), ('CR2', 'Bombardier CRJ-200', 2700); CREATE TABLE flights ( flight_id integer NOT NULL, flight_no character(6) NOT NULL, scheduled_departure timestamp with time zone NOT NULL, scheduled_arrival timestamp with time zone NOT NULL, departure_airport character(3) NOT NULL, arrival_airport character(3) NOT NULL, status character varying(20) NOT NULL, aircraft_code character(3) NOT NULL, actual_departure timestamp with time zone, actual_arrival timestamp with time zone, CONSTRAINT flights_check CHECK ((scheduled_arrival > scheduled_departure)), CONSTRAINT flights_check1 CHECK (((actual_arrival IS NULL) OR ((actual_departure IS NOT NULL) AND (actual_arrival IS NOT NULL) AND (actual_arrival > actual_departure)))), CONSTRAINT flights_status_check CHECK (((status)::text = ANY (ARRAY[('On Time'::character varying)::text, ('Delayed'::character varying)::text, ('Departed'::character varying)::text, ('Arrived'::character varying)::text, ('Scheduled'::character varying)::text, ('Cancelled'::character varying)::text]))) ); SELECT '1. Сколько суммарно каждый тип самолёта провёл в воздухе, если брать завершённые перелеты.'; SELECT a.model "Модель_самолета", SUM(f.actual_arrival - f.actual_departure) "В_воздухе_часов" FROM aircrafts a JOIN flights f ON f.aircraft_code = a.aircraft_code WHERE f.status = 'Arrived' GROUP BY a.model ORDER BY В_воздухе_часов DESC; CREATE TABLE tickets ( ticket_no character(13) NOT NULL, book_ref character(6) NOT NULL, passenger_id character varying(20) NOT NULL, passenger_name text NOT NULL, contact_data jsonb ); CREATE TABLE bookings ( book_ref character(6) NOT NULL, book_date timestamp with time zone NOT NULL, total_amount numeric(10,2) NOT NULL ); CREATE TABLE boarding_passes ( ticket_no character(13) NOT NULL, flight_id integer NOT NULL, boarding_no integer NOT NULL, seat_no character varying(4) NOT NULL ); SELECT '2. Сколько было получено посадочных талонов по каждой брони'; SELECT b.book_ref, COUNT(bp.boarding_no) as boarded_count FROM boarding_passes bp JOIN tickets t ON t.ticket_no = bp.ticket_no JOIN bookings b ON b.book_ref = t.book_ref WHERE bp.boarding_no IS NOT NULL GROUP BY b.book_ref; CREATE TABLE ticket_flights ( ticket_no character(13) NOT NULL, flight_id integer NOT NULL, fare_conditions character varying(10) NOT NULL, amount numeric(10,2) NOT NULL, CONSTRAINT ticket_flights_amount_check CHECK ((amount >= (0)::numeric)), CONSTRAINT ticket_flights_fare_conditions_check CHECK (((fare_conditions)::text = ANY (ARRAY[('Economy'::character varying)::text, ('Comfort'::character varying)::text, ('Business'::character varying)::text]))) ); SELECT '3. Вывести общую сумму продаж по каждому классу билетов'; SELECT tf.fare_conditions AS "Класс", SUM(amount) AS "Сумма" FROM ticket_flights AS tf GROUP BY tf.fare_conditions ORDER BY Сумма DESC; CREATE TABLE airports ( airport_code character(3) NOT NULL, airport_name text NOT NULL, city text NOT NULL, longitude double precision NOT NULL, latitude double precision NOT NULL, timezone text NOT NULL ); CREATE TABLE seats ( aircraft_code character(3) NOT NULL, seat_no character varying(4) NOT NULL, fare_conditions character varying(10) NOT NULL, CONSTRAINT seats_fare_conditions_check CHECK (((fare_conditions)::text = ANY (ARRAY[('Economy'::character varying)::text, ('Comfort'::character varying)::text, ('Business'::character varying)::text]))) ); SELECT '4. Найти маршрут с наибольшим финансовым оборотом'; SELECT DISTINCT a.city dep_city, b.city arr_city, tf.amount FROM flights f JOIN ticket_flights tf ON tf.flight_id = f.flight_id JOIN airports a ON f.departure_airport = a.airport_code JOIN airports b ON f.arrival_airport = b.airport_code ORDER BY tf.amount DESC LIMIT 1; SELECT '5. Найти наилучший и наихудший месяцы по бронированию билетов (количество и сумма)'; SELECT DATE_TRUNC('month', b.book_date) AS "Месяц", -- DATE_PART('month', DATE_TRUNC('month', b.book_date)), DATE_PART('year', DATE_TRUNC('month', b.book_date)), COUNT(bp.boarding_no) "Количество", SUM(b.total_amount) "Сумма" FROM bookings b JOIN tickets t ON t.book_ref = b.book_ref JOIN boarding_passes bp ON bp.ticket_no = t.ticket_no GROUP BY b.book_date, bp.boarding_no, b.total_amount ORDER BY b.book_date LIMIT 100; SELECT '6. Между какими городами пассажиры делали пересадки? Пересадкой считается нахождение пассажира в промежуточном аэропорту менее 24 часов' SELECT count( * ) FROM ( ticket_flights t JOIN flights f ON t.flight_id = f.flight_id ) LEFT OUTER JOIN boarding_passes b ON t.ticket_no = b.ticket_no AND t.flight_id = b.flight_id WHERE f.actual_departure IS NOT NULL AND b.flight_id IS NULL;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear