Продолжаем изучать базы данных в рамках курса python_advanced на одной образовательной платформе.
Предыдущие части (1 и 2):
Задача 1. Создание схемы
Реализуйте следующие таблицы:
SQL-запросы для создания схемы оформите в SQL-файле или в Python-скрипте.
Подумайте, как должны вести себя ссылки при удалении родительских записей.
В отдельном файле напишите классификацию всех типов связей между таблицами в схеме.
Попробуем разобраться, как тут всё должно работать.
У нас будет три основных таблицы:
Первая таблица - "актёры" (actors), у которой есть следующие параметры: (некий уникальный числовой примари кей который будет этого актёра идентифицировать - act_id, имя, фамилия, пол). Итого: 4 поля.
Вторая таблица - "кино" (movie), у которой есть следующие параметры: (айди, который будет примари кей, фильма и название). Итого 2 поля.
Третья таблица - "режиссёр". В этой таблице 3 поля (айди режиссёра - примари кей, его имя и фамилия). Итого 3 поля.
Еще три таблицы - вспомогательные, они будут иметь связи с первыми тремя таблицами
Первая вспомогательная таблица - "в ролях" (movie_cast). Эта таблица будет иметь связь one-to-many. В одном фильме может сниматься множество актёров. В ней будет три поля: id-актёра, id-фильма и роль, которую сыграл этот актер.
Вторая вспомогательная таблица - "награждение Оскаром " (oscar_awarded). Тут будет связь "one-to-one", у одного фильма есть один Оскар (когда говорят что у фильма стопятьсот Оскаров то эти оскары разные: за лучший сценарий, роль, еще что-то там). Так что "один к одному". В таблице 2 поля: id-оскара и id-фильма.
Третья вспомогательная таблица - "режиссёр фильма" (movie_direction). Связь "one-to-many" - один режиссёр может снять множество фильмов. Два поля: id-режиссёра, id-фильма.
Давайте попробуем написать SQL-запрос который создаст все эти таблицы. Я пока не буду это делать в python-коде, просто открою программу DBEaver, создам там новую базу данных и буду писать SQL-запросы.
Первые три таблицы создаются без различных условий (типа foreign key). Вторые три - с использованием ключевого слова REFERENCES. И получается вот такая красота:
Я попробовал добавить, режиссёра, актера, фильм и вот что из этого получилось:
Вроде бы всё работает... Попробуем весь этот SQL-скрипт перенести в код на python:
Выполнено без ошибок, открываем в DBEaver...
В описании к заданию было условие: "приведена классификация всех пяти видов связей между таблицами". Надеюсь я правильно понял это условие:
Двигаемся дальше...
Задача 2. SQL-запросы
Запустите скрипт homework/generate_hw_database.py, чтобы подготовить базу данных.
Выполните следующие запросы:
Найдите и выведите всю информацию о каждом заказе:имя покупателя,
имя продавца,
сумму,
дату.
Найдите и выведите имена покупателей, которые не сделали ни одного заказа.
Выведите номер заказа, имена продавца и покупателя, если место жительства продавца и покупателя не совпадают.
Для покупателей, которые сделали заказ напрямую (без помощи менеджеров), выведите имена и номера заказов.
По желанию. Выведите имена уникальных пар покупателей, живущих в одном городе и имеющих одного менеджера.
Получившиеся SQL-запросы оформите в отдельных файлах 2_X.sql, где X — номер подзадачи.
Итак, у нас есть три таблицы в которых:
Все запросы я буду сначала делать в программе
И только потом, когда всё будет работать как нужно - перенесу в код.
Итак, погнали, 1 задание:
Найдите и выведите всю информацию о каждом заказе:имя покупателя,
имя продавца,
сумму,
дату.
2 Задание. Найдите и выведите имена покупателей, которые не сделали ни одного заказа.
3 Задание. Выведите номер заказа, имена продавца и покупателя, если место жительства продавца и покупателя не совпадают.
4 Задание. Для покупателей, которые сделали заказ напрямую (без помощи менеджеров), выведите имена и номера заказов.
Есть там еще пятое задание.
Задание по желанию. Выведите имена уникальных пар покупателей, живущих в одном городе и имеющих одного менеджера.
Но признаюсь, я его не решил, во-первых оно довольно двояко сформулировано, во вторых я не смог понять как же группировать людей по городам. По одному городу - нет проблем, а вот все сразу - непонятно...
Как это всё выглядит в Pycharm, в коде:
Но оформлять нужно в отдельные файлы с расширением sql
И третья задача. Так же по желанию.
Выполните упражнения:
https://www.sql-ex.ru/learn_exercises.php?LN=6
https://www.sql-ex.ru/learn_exercises.php?LN=7
https://www.sql-ex.ru/learn_exercises.php?LN=9
https://www.sql-ex.ru/learn_exercises.php?LN=36
https://www.sql-ex.ru/learn_exercises.php?LN=50
Получившиеся SQL-запросы оформите в отдельных файлах 3_X.sql, где X — номер упражнения на сайте.
Хотел было все задания выполнить, но как оказалось - сервис на этом сайте платный, какое-то количество запросов можно сделать бесплатно, а затем - плати 300 рублей. Я успел сделать 4 из 5, только 4ю задачу не успел проверить - закончились бесплатные запросы...
Вот как я решил четыре из пяти задач:
Ну вот как-то так у меня получилось с базами данных. Если с этим заданием будут вопросы у куратора - в комментах отпишусь.