49. Музыкальные жанры

Борис работает с метаданными в сервисе Музыка. Он подготовил большое обновление данных по музыкальным жанрам, но столкнулся с проблемой: в результате этого обновления для треков будут указаны жанры, про которые знают далеко не все пользователи. Например, не так много людей знает, что страйд - это поджанр джаза. Если рядом с треком будет указано, что его жанр — это не только страйд, но и джаз, пользователям будет проще.

Помогите Борису написать запрос, который для всех треков вернёт все жанры, к которым эти треки принадлежат.

Ниже приведены DDL для создания таблиц с данными о жанрах и треках

Жанры:

CREATE TABLE genre
(
    id bigint PRIMARY KEY,
    name varchar(100) NOT NULL,
    parent_genre_id bigint,
    FOREIGN KEY (parent_genre_id) REFERENCES genre(id)
);

Треки:

CREATE TABLE track
(
    id bigint PRIMARY KEY ,
    name varchar(100) NOT NULL
);

Связь трека и жанра:

CREATE TABLE track_genre
(
    track_id bigint,
    genre_id bigint,
    PRIMARY KEY(track_id, genre_id),
    FOREIGN KEY (track_id) REFERENCES track(id),
    FOREIGN KEY (genre_id) REFERENCES genre(id)
);

Гарантируется, что таблица genre описывает древовидную иерархию жанров и не содержит циклических зависимостей.

Необходимо написать запрос, который для каждого трека возвращает все жанры, к которым этот трек принадлежит, в следующем формате:

• track_id — id трека;
• genre_id — id жанра;
• track_name — наименование трека;
• genre_name — наименование жанра.

Записи должны быть отсортированы по возрастанию id треков. При равенстве id` треков записи необходимо отсортировать по возрастанию id`` жанров.

Предположим, есть следующая иерархия жанров Rock -> Metal -> Trash Metal, то есть Trash Metal является поджанром Metal, а Metal является поджанром Rock. Если у трека есть связь с жанром Metal, то запрос должен вернуть жанры Rock и Metal. Жанр Trash Metal в результат попасть не должен.

Обратите внимание: если ваш запрос вернёт неверный набор столбцов или попытается обратиться к несуществующим таблицам или столбцам, ваше решение получит вердикт «Crash». Кроме того, к такому вердикту может привести использование DDL, в том числе для создания временных таблиц или представлений.

Для проверки решений используется СУБД SQLite.

Если вы хотите протестировать запрос до отправки в систему, но у вас не установлено среды для работы с базами данных, вы можете воспользоваться любой online-средой, например, sqliteonline.

Пример

Скрипт с примером заполнения базы данных можно скачать по ссылке.

Для этого примера результат выполнения запроса должен быть следующим: