set search_path to bookings; -- 1 В каких городах больше одного аэропорта? /* * Группирую таблицу аэропортов по городу и вывожу только те, у которых количество airport_code больше 1 */ select city "Город" from airports a group by city having count(airport_code) > 1; -- 2 В каких аэропортах есть рейсы, выполняемые самолетом с максимальной дальностью перелета? -- - Подзапрос /* * Подзапрос получает код самолета с самыой большой дальностью (с помощью сортировки и ограничения вывода). * Далее в основном запросе указывается условие соответствия самолета. * Основной запрос получает имя аэропорта по джойну с таблицей перелётов */ select distinct a.airport_name "Аэропорт" from airports a join flights f on a.airport_code = f.departure_airport where f.aircraft_code = ( select a.aircraft_code from aircrafts a order by a."range" desc limit 1 ); -- 3 Вывести 10 рейсов с максимальным временем задержки вылета - Оператор LIMIT /* * Отбираю только те рейсы, которые вылетели (actual_departure заполнено) * Задержка считается простым вычитанием. * Наконец, сортировка по убыванию и ограничение вывода */ select f.flight_id, f.scheduled_departure, f.actual_departure, f.actual_departure - f.scheduled_departure "Задержка" from flights f where f.actual_departure is not null order by "Задержка" desc limit 10; -- 4 Были ли брони, по которым не были получены посадочные талоны? - Верный тип JOIN /* * Left join, т.к. нужно полное множество броней. * Джойню таблицу tickets т.к. таблица броней связывается с талонами через билет. */ select case when count(b.book_ref) > 0 then 'Да' else 'Нет' end "Наличие броней без пт", count(b.book_ref) "Их количество" from bookings b join tickets t on t.book_ref = b.book_ref left join boarding_passes bp on bp.ticket_no = t.ticket_no where bp.boarding_no is null; -- 5 Найдите свободные места для каждого рейса, их % отношение к общему количеству мест в самолете. -- Добавьте столбец с накопительным итогом - суммарное накопление количества вывезенных пассажиров из каждого аэропорта на каждый день. -- Т.е. в этом столбце должна отражаться накопительная сумма - сколько человек уже вылетело из данного аэропорта на этом или более ранних рейсах за день. -- - Оконная функция -- - Подзапросы или cte /* * CTE boarded получает количество выданных посадочных талонов по каждому рейсу * Ограничение actual_departure is not null для того, чтобы отслеживать уже вылетевшие рейсы * CTE max_seats_by_aircraft получает количество мест в самолёте * В итоговом запросе оба CTE джойнятся по aircraft_code * Для подсчета накопительной суммы использется оконная функция c разделением по аэропорту отправления и времени вылета приведенному к формату date. */ with boarded as ( select f.flight_id, f.flight_no, f.aircraft_code, f.departure_airport, f.scheduled_departure, f.actual_departure, count(bp.boarding_no) boarded_count from flights f join boarding_passes bp on bp.flight_id = f.flight_id where f.actual_departure is not null group by f.flight_id ), max_seats_by_aircraft as( select s.aircraft_code, count(s.seat_no) max_seats from seats s group by s.aircraft_code ) select b.flight_no, b.departure_airport, b.scheduled_departure, b.actual_departure, b.boarded_count, m.max_seats - b.boarded_count free_seats, round((m.max_seats - b.boarded_count) / m.max_seats :: dec, 2) * 100 free_seats_percent, sum(b.boarded_count) over (partition by (b.departure_airport, b.actual_departure::date) order by b.actual_departure) "Накопительно пассажиров" from boarded b join max_seats_by_aircraft m on m.aircraft_code = b.aircraft_code; -- 6 Найдите процентное соотношение перелетов по типам самолетов от общего количества. - Подзапрос -- - Оператор ROUND /* * Используется подзапрос для получения общего числа полетов (проверяем, вылетел ли самолет при подсчете) * В основном запросе используется группировка по полю model */ select a.model "Модель самолета", count(f.flight_id) "Количество рейсов", round(count(f.flight_id) / (select count(f.flight_id) from flights f where f.actual_departure is not null )::dec * 100, 4) "В процентах от общего числа" from aircrafts a join flights f on f.aircraft_code = a.aircraft_code where f.actual_departure is not null group by a.model; -- 7 Были ли города, в которые можно добраться бизнес - классом дешевле, чем эконом-классом в рамках перелета? -- - CTE /* * В CTE prices собираются стоимости билетов на рейс: максимальная для Эконома и минимальная для бизнеса. * Затем из него отбираются эти стоимости и группируются в одну строку по каждому аэропорту - это внешний * CTE eco_busi. Результаты фильтруются по сравнению полей b_min_amount и e_max_amount * Далее этот CTE джойнится с таблицами рейсов и аэропортов, чтобы достать из них города отправления и прибытия. * Судя по тому, что результат пустой, таких рейсов нет */ with eco_busi as ( with prices as( select f.flight_id, case when tf.fare_conditions = 'Business' then min(tf.amount) end b_min_amount, case when tf.fare_conditions = 'Economy' then max(tf.amount) end e_max_amount from ticket_flights tf join flights f on tf.flight_id = f.flight_id group by f.flight_id, tf.fare_conditions ) select p.flight_id, min(p.b_min_amount), max(p.e_max_amount) from prices p group by p.flight_id having min(p.b_min_amount) < max(p.e_max_amount) ) select e.flight_id, a.city depatrure_city, a2.city arrival_city from eco_busi e join flights f on e.flight_id = f.flight_id join airports a on f.departure_airport = a.airport_code join airports a2 on f.arrival_airport = a2.airport_code /* * Этот вариант смотрит стоимость билета между городами без учета рейса * CTE max_min_by_city формирует минимальную стоимость по бизнес классу и муксимальную по эконому * с группировкой по городу отправления и прибытия и по классу билета. * результаты его отправляются во внешний запрос, который собирает минимум и максимум по двум городам * в одну строку. В итоговом условии выводятся только те строки, в которых min(b_min_amount) < max(e_max_amount). * Таких строк нет, так что и в этом случае бизнес всегда дороже эконома */ with max_min_by_city as( select a.city dep_city, a2.city arr_city, tf.fare_conditions, case when tf.fare_conditions = 'Business' then min(tf.amount) end b_min_amount, case when tf.fare_conditions = 'Economy' then max(tf.amount) end e_max_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 a2 on f.arrival_airport = a2.airport_code group by (1, 2), 3 ) select dep_city "Из", arr_city "В", min(b_min_amount) "Минимум за бизнес", max(e_max_amount) "Максимум за эконом" from max_min_by_city group by (1, 2) having min(b_min_amount) < max(e_max_amount); -- 8 Между какими городами нет прямых рейсов? -- - Декартово произведение в предложении FROM -- - Самостоятельно созданные представления -- - Оператор EXCEPT /* * Создаю представление для получения городов, между которыми есть рейсы * Два джойна в представлении для получения города отправления и города прибытия * В основном запросе получаю декартово произведение всех городов, с условием их неравенства * Затем из него убираю данные, которые есть в представлении. */ create view dep_arr_city as select distinct a.city departure_city, a2.city arrival_city from flights f join airports a on f.departure_airport = a.airport_code join airports a2 on f.arrival_airport = a2.airport_code; select distinct a.city departure_city, a2.city arrival_city from airports a, airports a2 where a.city != a2.city except select * from dep_arr_city -- 9 Вычислите расстояние между аэропортами, связанными прямыми рейсами, сравните с допустимой максимальной дальностью перелетов -- в самолетах, обслуживающих эти рейсы * - Оператор RADIANS или использование sind/cosd -- - CASE /* * Опять два раза джойн таблицы аэропортов. * Поле "Долетит?" заполняется по условию того, что рассчитанная дальность между городами меньше дальности самолета. * Расстояние между городами делал по формуле из задания не особо задумываясь об этом */ select distinct ad.airport_name "Из", aa.airport_name "В", a."range" "Дальность самолета", round((acos(sind(ad.latitude) * sind(aa.latitude) + cosd(ad.latitude) * cosd(aa.latitude) * cosd(ad.longitude - aa.longitude)) * 6371)::dec, 2) "Расстояние", case when a."range" < acos(sind(ad.latitude) * sind(aa.latitude) + cosd(ad.latitude) * cosd(aa.latitude) * cosd(ad.longitude - aa.longitude)) * 6371 then 'Нет!' else 'Да!' end "Долетит?" from flights f join airports ad on f.departure_airport = ad.airport_code join airports aa on f.arrival_airport = aa.airport_code join aircrafts a on a.aircraft_code = f.aircraft_code
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter