"Управление данными", магистры, очное обучение

Список вопросов для подготовки к экзамену

Знакомство с СУБД PostgreSQL и языком SQL

1. Установка PostgreSQL, базы данных pubs и менеджера баз данных DBeaver

PostgreSQL является популярной свободно распространяемой сетевой постреляционной базой данных уровня предприятия.

Установите PostgreSQL на свой персональный компьютер, скачав инсталляцию с официального сайта https://www.postgresql.org/download/, с установками по умолчанию. Используйте пособие разработчиков для первого знакомства с этой СУБД.

Если вы вдруг работаете в Linux, для управления СУБД используйте аккаунт администратора. Конкретно, если вы работаете в Debian, введите команду su - , а затем su - postgres.

Запустите программу SQLShell (psql) от имени администратора (пользователя с именем postgres). Создайте базу данных pubs с помощью команды CREATE DATABASE PUBS; (точка с запятой в конце команды обязательна). Выйдите из psql с помощью команды \q.

Скачайте по этой ссылке дамп базы данных pubs и сохраните его в файле с именем pubs.sql. Откройте окно командной строки (терминал) и перейдите в тот каталог, куда вы сохранили pubs.sql. Выполните команду psql -U postgres pubs < pubs.sql. Если система Windows не знает, что такое psql, добавьте к переменной окружения PATH путь к каталогу, где находятся исполняемые файлы PostgreSQL (обычно это что-то типа "C:\Program Files\PostgreSQL\12\bin"). После этого база данных будет готова к работе.

Для облегчения условий труда при работе с базой данных можно использовать какой-либо из графических пользовательских менеджеров. Можно использовать pgAdmin, который устанавливается вместе с PostgreSQL, но у него очень сложный и небыстрый интерфейс. Я могу порекомендовать универсальный менеджер баз данных DBeaver (Community Edition), который можно установить на ваш персональной компьютер с настройками по умолчанию. После установки и запуска программы вы должны создать новое соединение с источником данных PostgreSQL, в появившемся окне настроек указать хост как localhost, базу данных как pubs, пользователя как postgres, и указать пароль, который соответствует этому пользователю. После этого в левом окне "Базы данных" вы переходите к PostgreSQL -> pubs -> Схемы -> public -> Таблицы и убеждаетесь, что база данных содержит таблицы authorspublishers и другие. Затем в правом окне создаёте пустой документ для записи SQL-запросов (редактор SQL), и можно начинать выполнять задания, представленные ниже.

2. Выполнение заданий по языку SQL.

Все задания оформляются в виде единого отчёта. Перед каждым заданием указывается его описание, затем даётся скриншот, содержащий текст запроса и результат выполнения этого запроса. К заданию можно добавлять любые необходимые комментарии. Структура базы данных pubs(объяснение значений полей) приведена здесь.

Запросы к одной таблице

  1. Выведите на экран первые 10 записей из таблиц authors, titles, publishers, pub_info. Таблицы должны быть отсортированы по ключевому полю (по возрастанию значения). Выведите последнюю таблицу так, чтобы имена столбцов были на русском языке.
  2. Создайте запрос, который покажет фамилии и имена авторов, проживающих в городеSalt Lake City. Кроме того, создайте запрос, который покажет, сколько авторов проживает в этом городе.
  3. Напишите запрос, выводящий отсортированный по алфавиту список авторов, номера телефонов которых содержат цифру вашего варианта.
  4. Создайте запрос, который покажет, сколько различных штатов представлено в таблице Authors.
  5. Подсчитайте с помощью запроса, сколько авторов проживает в каждом штате.
  6. Выведите отсортированный по алфавиту список тем, по которым выпускались книги (повторы не допускаются).
  7. Напишите запрос, выводящий отсортированный по убыванию стоимости список названий книг и их цену (в рублях по курсу), игнорируя книги, цена на которые не указана.
  8. Напишите запрос, выводящий названия книг, выпущенных по темам "popular_comp" и "psychology".
  9. Напишите запрос, который выведет названия книг, содержащих первые две буквы вашего имени (в английской транскрипции)?
  10. Напишите запрос, выводящий тему, по которой издано наибольшее количество книг.

Запросы к связанным таблицам

Связывание должно осуществляться в разделе FROM оператора SELECT с помощью конструкции JOIN.

  1. Создайте запрос, выводящий список названий книг вместе с названием выпустившего книгу издательства.
  2. Создайте запрос, выводящий следующую таблицу: фамилия, имя автора, город проживания автора, название книги автора, год выпуска книги. Таблица должна быть отсортирована по фамилии автора.
  3. Напишите запрос, определяющий, какие книги написаны автором Albert Ringer.
  4. Покажите, какие книги имеют более одного автора.
  5. Напишите запрос, подсчитывающий, сколько написал книг и сколько всего денег заработал на них каждый из авторов. Доход автора от одной книги есть стоимость книги * объём продаж * % гонорара для всех авторов * % гонорара для одного автора (если их несколько).

Усложнённые запросы

  1. Напишите запрос, определяющий, какие книги написаны совместно авторами Ringer Anne и Ringer Albert.
  2. Вывести список авторов, которые публиковались в разных издательствах.

 

В отчёте допускается привести запрос, в правильности которого вы сомневаетесь. Не допускается вообще не написать ничего по конкретному заданию. 

Доступ к данным и более сложные запросы

1. Работа пользовательского приложения с СУБД PostgreSQL

Для выполнения задания мы будем использовать демонстрационную базу данных авиаперелётов (с именем demo), разработанную авторами одной из версий Postgres. Описание модели данных приведено ЗДЕСЬ. Обязательно изучите структуру базы данных, поскольку иначе суть задания вам окажется непонятной. Базу данных вы можете скачать с сайта авторов ПОСОБИЯ, вариант этой базы также размещен на сервере scilink.ru, порт 5432. Доступ к базе данных (только на чтение) осуществляется от имени пользователя demo с паролем, который вам сообщит преподаватель. Однако для удобства работы желательно установить эту демонстрационную базу данных на ваш компьютер.

Задание выполняется по вариантам. Номера вариантов вам указывает преподаватель. Суть работы заключается в написании запроса к базе данных в соответствии с поставленными условиями задачи. А особенность вывода результата заключается в том, что созданный запрос должен быть выполнен в программе, написанной на языке Python. Если вы выполняете задание на вашем компьютере, вам необходимо поставить актуальную версию IDLE Python и дополнительный модуль psycopg2 для доступа к СУБД PostgreSQL. Как это сделать, описано ЗДЕСЬ.

Варианты:

  1. Выберите все модели самолётов вместе с общим количеством мест в салоне, отсортируйте самолёты по дальности полётов.
  2. Выведите отменённые рейсы в сентябре 2017 года из аэропорта Пулково, отсортированные по возрастанию даты.
  3. Сколько мест было занято в самолёте, летевшем из Анапы в Шереметьево 5 августа 2017 года?
  4. Напишите запрос, возвращающий список названий аэропортов, в которых было принято более 1000 рейсов с 1 июня 2017 года. Список отсортировать по убыванию количества рейсов.
  5. Определите номера и времена отправления всех рейсов в 2017 году, опоздавших в аэропорты назначения более чем на один час.
  6. Определите 50 номеров маршрутов, на каких чаще всего использовались самолёты компании Boeing.
  7. Сколько маршрутов обслуживают самолёты каждого типа?
  8. Каковы были в 2017 году максимальные и минимальные цены на билеты из Шереметьево в Сочи?
  9. Определить фамилию и имя пассажира, пришедшего раньше всех на регистрацию рейса из Шереметьево в Воркуту 2 августа 2017 г.
  10. Получить перечень аэропортов в тех городах, в которых больше одного аэропорта.

Задание удобно выполнить в два этапа:

  1. Изучение структуры и содержания базы данныхdemo, написание и отладка требуемого запроса. В этом случае вам подойдёт любое из установленных у вас клиентских приложений для доступа кPostgreSQL, включая собственного клиента командной строки psql.
  2. Перенос отлаженного запроса в программу на языкеPython. В данном курсе вам не требуется изучать все возможности и синтаксические конструкции этого языка, достаточно изучить и понять демонстрационный пример и слегка его переработать, чтобы корректно вывести информацию на экран в текстовом режиме. Если же вы знаете язык Python или хотите научиться писать программы на нём, вы можете самостоятельно и творчески доработать функционал приложения.

Примеры реализации запросов к базе данных demo:

  1. Вывести список всех самолётов кампании:

SELECT * FROM aircrafts;

  1. Вывести любые пять аэропортов:

SELECT airport_code, city FROM airports LIMIT 5;

  1. Выбрать все самолёты компании Airbus:

SELECT * FROM aircrafts WHERE model LIKE 'Аэробус%';

  1. Найти в таблице «Аэропорты» те из них, которые имеют названия длиной три символа:

SELECT * FROM airports WHERE airport_name LIKE '___';

  1. Какие самолёты имеют дальность полёта в диапазоне от 3 000 км до 6 000 км?

SELECT * FROM aircrafts WHERE range BETWEEN 3000 AND 6000;

  1. В каких различных часовых поясах располагаются аэропорты?

SELECT DISTINCT timezone FROM airports ORDER BY timezone;

  1. Найти три самых восточных аэропорта:

SELECT airport_name, city, coordinates[0] FROM airports

ORDER BY 3 DESC LIMIT 3;

  1. Выбрать все места, предусмотренные компоновкой салона самолётаCessna:

SELECT s.seat_no, s.fare_conditions

FROM seats AS s JOIN aircrafts AS a ON s.aircraft_code = a.aircraft_code

WHERE a.model LIKE 'Сессна%' ORDER BY s.seat_no;

  1. Сколько всего маршрутов нужно было бы сформировать, если бы требовалось соединить каждый город со всеми остальными городами?

SELECT count(*) FROM airports a1 JOIN airports a2 ON a1.city <> a2.city;

  1. В каких городах больше одного аэропорта?

SELECT city, count(*) FROM airports

GROUP BY city HAVING count(*) > 1;

  1. Подсчитать количество операций бронирования, в которых общая сумма превышает среднюю величину операций бронирования по всем пассажирам.

SELECT COUNT (*) FROM bookings

WHERE total_amount > ( SELECT AVG (total_amount) FROM bookings

 

 

 

 

SandBOX

Пользователи
1
Материалы
29
Кол-во просмотров материалов
27604

© ФТИ ПетрГУ, Пикулев В. Б.