- Nephedov-SQL-48.sql - содержащий ряд запросов к БД, согласно условию задания.
- Установлен PostgreSQL-14.5.1 сервер под Windows x64, на порту 5432.
- Установлен DBeaver-22.1.5 и настроено подключение к серверу postgres.
- Создана пустая база данных "demo".
- Восстановлена база данных из бэкапа - avia.backup об авиаперевозках в базу данных "demo".
- Составлен sql файл, в котором реализованы sql запросы согласно условиям задания.
Требования к внешнему виду запросов:
- Решения должны быть приложены в формате *.sql одним файлом.
- Запросы должны быть отформатированы и в читаемом виде.
Для выполнения работы Вам необходимо:
- Ознакомиться с описанием базы данных: https://edu.postgrespro.ru/bookings.pdf.
Диаграмма схемы данных авиаперевозок.
- Подключиться к базе данных avia по одному из следующих вариантов - импорт sql запроса из sql файла, представленных на 2 странице описания базы.
| № | Вопрос | В решении обязательно должно быть |
|---|---|---|
| 1 | Какие самолеты имеют более 50 посадочных мест? | |
| 2 | В каких аэропортах есть рейсы, в рамках которых можно добраться бизнес - классом дешевле, чем эконом - классом? | - CTE |
| 3 | Есть ли самолеты, не имеющие бизнес - класса? | - array_agg |
| 4 | Найдите количество занятых мест для каждого рейса, процентное отношение количества занятых мест к общему количеству мест в самолете, добавьте накопительный итог вывезенных пассажиров по каждому аэропорту на каждый день. | - Оконная функция. - Подзапрос. |
| 5 | Найдите процентное соотношение перелетов по маршрутам от общего количества перелетов. Выведите в результат названия аэропортов и процентное отношение. |
- Оконная функция. - Оператор ROUND. |
| 6 | Выведите количество пассажиров по каждому коду сотового оператора, если учесть, что код оператора - это три символа после +7 | |
| 7 | Между какими городами не существует перелетов? | - Декартово произведение. - Оператор EXCEPT. |
| 8 | Классифицируйте финансовые обороты (сумма стоимости билетов) по маршрутам: До 50 млн - low От 50 млн включительно до 150 млн - middle От 150 млн включительно - high Выведите в результат количество маршрутов в каждом классе. |
- Оператор CASE. |
| 9 | Выведите пары городов между которыми расстояние более 5000 км | - Оператор RADIANS или использование sind/cosd |
Пояснения:
- Рейс, перелет - это flight_id, разовый перелет между двумя аэропортами.
- Маршрут - это все перелеты между двумя аэропортами.
- Кратчайшее расстояние между двумя точками A и B на земной поверхности (если принять ее за сферу) определяется зависимостью:
d = arccos {sin(latitude_a)·sin(latitude_b) + cos(latitude_a)·cos(latitude_b)·cos(longitude_a - longitude_b)}, где latitude_a и latitude_b — широты, longitude_a, longitude_b — долготы данных пунктов, d — расстояние между пунктами измеряется в радианах длиной дуги большого круга земного шара. - Расстояние между пунктами, измеряемое в километрах, определяется по формуле:
L = d·R, где R = 6371 км — средний радиус земного шара.