В этом репозитории собраны решения нескольких задач, по проектированию БД postrges
, ee наполнению и извлечению данных из нее средствами SQL.
Спроектировать схему — таблицы и связи между ними — для музыкального сайта. Требования:
- на сайте должна быть возможность увидеть список музыкальных жанров;
- для каждого жанра можно получить список исполнителей, которые выступают в соответствующем жанре;
- для каждого исполнителя можно получить список его альбомов;
- для каждого альбома можно получить список треков, которые в него входят;
- у жанра есть название;
- у исполнителя есть имя (псевдоним) и жанр, в котором он исполняет;
- у альбома есть название, год выпуска и его исполнитель;
- у трека есть название, длительность и альбом, которому этот трек принадлежит.
Результатом работы является изображение в формате PNG, содержащее схему БД.
Будем развивать схему для музыкального сервиса.
Ранее существовало ограничение, что каждый исполнитель поёт строго в одном жанре, пора его убрать. Исполнители могут петь в разных жанрах, как и одному жанру могут принадлежать несколько исполнителей.
Аналогичное ограничение было и с альбомами у исполнителей — альбом мог выпустить только один исполнитель. Теперь альбом могут выпустить несколько исполнителей вместе. Как и исполнитель может принимать участие во множестве альбомов.
С треками ничего не меняем, всё так же трек принадлежит строго одному альбому.
Но появилась новая сущность — сборник. Сборник имеет название и год выпуска. В него входят различные треки из разных альбомов.
Обратите внимание: один и тот же трек может присутствовать в разных сборниках.
Задание состоит из двух частей
- Спроектировать и нарисовать схему, как в первой домашней работе. Прислать изображение со схемой.
- Написать SQL-запросы, создающие спроектированную БД. Прислать ссылку на файл, содержащий SQL-запросы.
Примечание: можно прислать сначала схему, получить подтверждение, что схема верная, и после этого браться за написание запросов.
Заполните базу данных из предыдущего домашнего задания. В ней должно быть:
- не менее 8 исполнителей,
- не менее 5 жанров,
- не менее 8 альбомов,
- не менее 15 треков,
- не менее 8 сборников.
Внимание: должны быть заполнены все поля каждой таблицы, в том числе таблицы связей исполнителей с жанрами, исполнителей с альбомами, сборников с треками.
Написать SELECT-запросы, которые выведут информацию согласно инструкциям ниже.
Внимание: результаты запросов не должны быть пустыми, учтите это при заполнении таблиц.
- Название и год выхода альбомов, вышедших в 2018 году.
- Название и продолжительность самого длительного трека.
- Название треков, продолжительность которых не менее 3,5 минут.
- Названия сборников, вышедших в период с 2018 по 2020 год включительно.
- Исполнители, чьё имя состоит из одного слова.
- Название треков, которые содержат слово «мой» или «my».
Результатом работы будет три файла в формате .sql
:
- с INSERT-запросами (задание 1),
- с SELECT-запросами (задание 2),
- с CREATE-запросами (из предыдущей домашней работы).
Написать SELECT-запросы, которые выведут информацию согласно инструкциям ниже.
Внимание: результаты запросов не должны быть пустыми, при необходимости добавьте данные в таблицы.
- Количество исполнителей в каждом жанре.
- Количество треков, вошедших в альбомы 2019–2020 годов.
- Средняя продолжительность треков по каждому альбому.
- Все исполнители, которые не выпустили альбомы в 2020 году.
- Названия сборников, в которых присутствует конкретный исполнитель (выберите его сами).
- Названия альбомов, в которых присутствуют исполнители более чем одного жанра.
- Наименования треков, которые не входят в сборники.
- Исполнитель или исполнители, написавшие самый короткий по продолжительности трек, — теоретически таких треков может быть несколько.
- Названия альбомов, содержащих наименьшее количество треков.
Результатом работы будут три файла: с INSERT-, SELECT-запросами и CREATE-запросами из предыдущего задания в формате .sql или .py/.ipynb, если вы будете писать запросы с использованием SQLAlchemy.
В случае если INSERT- и CREATE-запросы остались без изменений, приложите файлы c ними из предыдущих домашних заданий.
Результатом работы будут три файла в формате .sql
:
- с INSERT-запросами (из предыдущей домашней работы),
- с SELECT-запросами (текущее задание),
- с CREATE-запросами (из второй домашней работы).
В случае если INSERT- и CREATE-запросы остались без изменений, приложите файлы c ними из предыдущих домашних заданий.