SQLize | PHPize | SQLtest

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]))) ); INSERT INTO flights(flight_id, flight_no, scheduled_departure, scheduled_arrival, departure_airport, arrival_airport, status, aircraft_code, actual_departure, actual_arrival) VALUES (1, 'PG0405', '2016-08-10 08:35:00+03', '2016-08-10 09:30:00+03', 'DME', 'LED', 'Arrived', '321', '2016-08-10 08:39:00+03', '2016-08-10 09:35:00+03'); 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 ); INSERT INTO tickets(ticket_no, book_ref, passenger_id, passenger_name, contact_data) VALUES('0005432000860', '8BBCD2', '4750 122452', 'VLADIMIR FROLOV', '{"phone": "+70125366530"}'); CREATE TABLE bookings ( book_ref character(6) NOT NULL, book_date timestamp with time zone NOT NULL, total_amount numeric(10,2) NOT NULL ); INSERT INTO bookings(book_ref, book_date, total_amount) VALUES('00000F', '2016-09-02 02:12:00+03', 265700.00); 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 ); INSERT INTO boarding_passes(ticket_no, flight_id, boarding_no, seat_no) VALUES('0005435208229', 60731, '1', '1H'); 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]))) ); INSERT INTO ticket_flights(ticket_no, flight_id, fare_conditions, amount) VALUES('0005432081075', 11002, 'Business', 99800.00); 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 ); INSERT INTO airports(airport_code, airport_name, city, longitude, latitude, timezone) VALUES('MJZ', 'Мирный', 'Мирный', 114.038928, 62.534689, 'Asia/Yakutsk'); 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]))) ); INSERT INTO seats(aircraft_code, seat_no, fare_conditions) VALUES('319', '2A', 'Business'); 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 t.ticket_no, COUNT(bp.flight_id) JOIN tickets t ON t.book_ref = b.book_ref JOIN boarding_passes bp ON bp.ticket_no = t.ticket_no GROUP BY t.ticket_no HAVING COUNT(bp.flight_id) > 1;