Script.sql - master - OhioBossForRight/SQLDZ

Script.sql
250 строк11 KB
OhioBossForRight
upload files
11 окт 2025, 20:09
11 окт 2025, 20:09e442ff6
-- *** Эту часть выполнять в SQL-редакторе, подключенном к базе данных 'zzz' ***

-- Удаление таблиц (для чистоты, если скрипт запускается повторно без DROP DATABASE)
-- Удаляем в обратном порядке зависимостей, чтобы избежать ошибок внешних ключей.
DROP TABLE IF EXISTS collection_track;
DROP TABLE IF EXISTS artist_album;
DROP TABLE IF EXISTS artist_genre;
DROP TABLE IF EXISTS tracks;
DROP TABLE IF EXISTS collections;
DROP TABLE IF EXISTS albums;
DROP TABLE IF EXISTS genres;
DROP TABLE IF EXISTS artists;


-- Создание таблиц с добавлением UNIQUE ограничений
CREATE TABLE IF NOT EXISTS artists (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE -- Имя артиста должно быть уникальным
);

CREATE TABLE IF NOT EXISTS genres (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE -- Название жанра должно быть уникальным
);

CREATE TABLE IF NOT EXISTS albums (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    year INTEGER,
    UNIQUE (name, year) -- Альбом уникален по названию и году выпуска
);

CREATE TABLE IF NOT EXISTS tracks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    duration INTEGER, -- Продолжительность в секундах
    album_id INTEGER REFERENCES albums(id),
    UNIQUE (name, album_id) -- Трек в пределах одного альбома должен быть уникальным по названию
);

CREATE TABLE IF NOT EXISTS collections (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    year INTEGER,
    UNIQUE (name, year) -- Сборник уникален по названию и году выпуска
);

CREATE TABLE IF NOT EXISTS artist_genre (
    artist_id INTEGER REFERENCES artists(id),
    genre_id INTEGER REFERENCES genres(id),
    PRIMARY KEY (artist_id, genre_id)
);

CREATE TABLE IF NOT EXISTS artist_album (
    artist_id INTEGER REFERENCES artists(id),
    album_id INTEGER REFERENCES albums(id),
    PRIMARY KEY (artist_id, album_id)
);

CREATE TABLE IF NOT EXISTS collection_track (
    collection_id INTEGER REFERENCES collections(id),
    track_id INTEGER REFERENCES tracks(id),
    PRIMARY KEY (collection_id, track_id)
);


-- Заполнение таблиц данными (с защитой от дубликатов)

-- Артисты (не менее 4)
INSERT INTO artists (name) VALUES
('Nirvana'),
('Radiohead'),
('Billie Eilish'),
('Queen'),
('AC/DC'),
('The Beatles')
ON CONFLICT (name) DO NOTHING;

-- Жанры (не менее 3)
INSERT INTO genres (name) VALUES
('Grunge'),
('Alternative Rock'),
('Pop'),
('Rock'),
('Hard Rock'),
('Classic Rock')
ON CONFLICT (name) DO NOTHING;

-- Альбомы (не менее 3)
INSERT INTO albums (name, year) VALUES
('Nevermind', 1991),
('OK Computer', 1997),
('When We All Fall Asleep, Where Do We Go?', 2019),
('A Night at the Opera', 1975),
('Back in Black', 1980),
('Happier Than Ever', 2021),
('Abbey Road', 1969),
('The Wall', 1979),
('Future Nostalgia', 2020) -- Добавил этот альбом, если Billie Eilish там есть
ON CONFLICT (name, year) DO NOTHING;

-- Треки (не менее 6) - (duration в секундах)
INSERT INTO tracks (name, duration, album_id) VALUES
('Smells Like Teen Spirit', 301, (SELECT id FROM albums WHERE name = 'Nevermind' AND year = 1991)), -- 5:01
('Lithium', 257, (SELECT id FROM albums WHERE name = 'Nevermind' AND year = 1991)), -- 4:17
('Paranoid Android', 383, (SELECT id FROM albums WHERE name = 'OK Computer' AND year = 1997)), -- 6:23
('Creep', 236, (SELECT id FROM albums WHERE name = 'OK Computer' AND year = 1997)), -- 3:56
('Bad Guy', 194, (SELECT id FROM albums WHERE name = 'When We All Fall Asleep, Where Do We Go?' AND year = 2019)), -- 3:14
('Bury a Friend', 193, (SELECT id FROM albums WHERE name = 'When We All Fall Asleep, Where Do We Go?' AND year = 2019)), -- 3:13
('Bohemian Rhapsody', 355, (SELECT id FROM albums WHERE name = 'A Night at the Opera' AND year = 1975)), -- 5:55
('You Shook My Night Long', 210, (SELECT id FROM albums WHERE name = 'Back in Black' AND year = 1980)), -- 3:30 (содержит "My")
('My Future', 228, (SELECT id FROM albums WHERE name = 'Happier Than Ever' AND year = 2021)), -- 3:48 (содержит "My")
('Come Together', 259, (SELECT id FROM albums WHERE name = 'Abbey Road' AND year = 1969)), -- The Beatles, 4:19
('Мой рок-н-ролл', 240, (SELECT id FROM albums WHERE name = 'Future Nostalgia' AND year = 2020)) -- 4:00 (содержит "Мой"), в альбоме 2020
ON CONFLICT (name, album_id) DO NOTHING;

-- Сборники (не менее 4)
INSERT INTO collections (name, year) VALUES
('Greatest Hits Grunge', 2000),
('Alternative Anthems', 2015),
('Pop Sensations 2020', 2020),
('Best of 2010s', 2021),
('Classic Rock Legends', 1990),
('Hits of the 90s', 1995),
('Rock Ballads 2018', 2018)
ON CONFLICT (name, year) DO NOTHING;

-- Связь артистов с жанрами
INSERT INTO artist_genre (artist_id, genre_id) VALUES
((SELECT id FROM artists WHERE name = 'Nirvana'), (SELECT id FROM genres WHERE name = 'Grunge')),
((SELECT id FROM artists WHERE name = 'Radiohead'), (SELECT id FROM genres WHERE name = 'Alternative Rock')),
((SELECT id FROM artists WHERE name = 'Billie Eilish'), (SELECT id FROM genres WHERE name = 'Pop')),
((SELECT id FROM artists WHERE name = 'Queen'), (SELECT id FROM genres WHERE name = 'Rock')),
((SELECT id FROM artists WHERE name = 'Queen'), (SELECT id FROM genres WHERE name = 'Classic Rock')),
((SELECT id FROM artists WHERE name = 'AC/DC'), (SELECT id FROM genres WHERE name = 'Hard Rock')),
((SELECT id FROM artists WHERE name = 'AC/DC'), (SELECT id FROM genres WHERE name = 'Rock')),
((SELECT id FROM artists WHERE name = 'The Beatles'), (SELECT id FROM genres WHERE name = 'Classic Rock'))
ON CONFLICT (artist_id, genre_id) DO NOTHING;

-- Связь артистов с альбомами
INSERT INTO artist_album (artist_id, album_id) VALUES
((SELECT id FROM artists WHERE name = 'Nirvana'), (SELECT id FROM albums WHERE name = 'Nevermind' AND year = 1991)),
((SELECT id FROM artists WHERE name = 'Radiohead'), (SELECT id FROM albums WHERE name = 'OK Computer' AND year = 1997)),
((SELECT id FROM artists WHERE name = 'Billie Eilish'), (SELECT id FROM albums WHERE name = 'When We All Fall Asleep, Where Do We Go?' AND year = 2019)),
((SELECT id FROM artists WHERE name = 'Billie Eilish'), (SELECT id FROM albums WHERE name = 'Happier Than Ever' AND year = 2021)),
((SELECT id FROM artists WHERE name = 'Queen'), (SELECT id FROM albums WHERE name = 'A Night at the Opera' AND year = 1975)),
((SELECT id FROM artists WHERE name = 'AC/DC'), (SELECT id FROM albums WHERE name = 'Back in Black' AND year = 1980)),
((SELECT id FROM artists WHERE name = 'The Beatles'), (SELECT id FROM albums WHERE name = 'Abbey Road' AND year = 1969)),
((SELECT id FROM artists WHERE name = 'Billie Eilish'), (SELECT id FROM albums WHERE name = 'Future Nostalgia' AND year = 2020))
ON CONFLICT (artist_id, album_id) DO NOTHING;

-- Связь сборников с треками
INSERT INTO collection_track (collection_id, track_id) VALUES
((SELECT id FROM collections WHERE name = 'Greatest Hits Grunge' AND year = 2000), (SELECT id FROM tracks WHERE name = 'Smells Like Teen Spirit')),
((SELECT id FROM collections WHERE name = 'Alternative Anthems' AND year = 2015), (SELECT id FROM tracks WHERE name = 'Creep')),
((SELECT id FROM collections WHERE name = 'Pop Sensations 2020' AND year = 2020), (SELECT id FROM tracks WHERE name = 'Bad Guy')),
((SELECT id FROM collections WHERE name = 'Best of 2010s' AND year = 2021), (SELECT id FROM tracks WHERE name = 'My Future')),
((SELECT id FROM collections WHERE name = 'Classic Rock Legends' AND year = 1990), (SELECT id FROM tracks WHERE name = 'Bohemian Rhapsody')),
((SELECT id FROM collections WHERE name = 'Greatest Hits Grunge' AND year = 2000), (SELECT id FROM tracks WHERE name = 'Lithium')),
((SELECT id FROM collections WHERE name = 'Pop Sensations 2020' AND year = 2020), (SELECT id FROM tracks WHERE name = 'Bury a Friend')),
((SELECT id FROM collections WHERE name = 'Rock Ballads 2018' AND year = 2018), (SELECT id FROM tracks WHERE name = 'You Shook My Night Long')),
((SELECT id FROM collections WHERE name = 'Pop Sensations 2020' AND year = 2020), (SELECT id FROM tracks WHERE name = 'Мой рок-н-ролл'))
ON CONFLICT (collection_id, track_id) DO NOTHING;


--- Задание 2: SELECT-запросы ---

-- 1. Название и продолжительность самого длительного трека.
SELECT name, duration
FROM tracks
ORDER BY duration DESC
LIMIT 1;

-- 2. Название треков, продолжительность которых не менее 3,5 минут (210 секунд).
SELECT name, duration
FROM tracks
WHERE duration >= 210;

-- 3. Названия сборников, вышедших в период с 2018 по 2020 год включительно.
SELECT name
FROM collections
WHERE year BETWEEN 2018 AND 2020;

-- 4. Исполнители, чьё имя состоит из одного слова.
SELECT name
FROM artists
WHERE name NOT LIKE '% %';

-- 5. Название треков, которые содержат слово «мой» или «my» (регистронезависимо).
SELECT name
FROM tracks
WHERE name ~* '(^|\s)(мой|my)(\s|$)';


--- Задание 3: SELECT-запросы (Агрегатные функции и связи) ---

-- 1. Количество исполнителей в каждом жанре.
SELECT
    g.name AS genre_name,
    COUNT(DISTINCT ag.artist_id) AS artist_count
FROM
    genres g
JOIN
    artist_genre ag ON g.id = ag.genre_id
GROUP BY
    g.name
ORDER BY
    artist_count DESC;

-- 2. Количество треков, вошедших в альбомы 2019–2020 годов.
SELECT
    COUNT(t.id) AS track_count
FROM
    tracks t
JOIN
    albums a ON t.album_id = a.id
WHERE
    a.year BETWEEN 2019 AND 2020;

-- 3. Средняя продолжительность треков по каждому альбому.
SELECT
    a.name AS album_name,
    AVG(t.duration) AS average_duration_seconds
FROM
    albums a
JOIN
    tracks t ON a.id = t.album_id
GROUP BY
    a.name
ORDER BY
    average_duration_seconds DESC;

-- 4. Все исполнители, которые не выпустили альбомы в 2020 году.
SELECT DISTINCT ar.name
FROM artists ar
WHERE ar.id NOT IN (
    SELECT aa.artist_id
    FROM artist_album aa
    JOIN albums al ON aa.album_id = al.id
    WHERE al.year = 2020
);

-- 5. Названия сборников, в которых присутствует конкретный исполнитель (выберем "Queen").
SELECT DISTINCT c.name AS collection_name
FROM collections c
JOIN collection_track ct ON c.id = ct.collection_id
JOIN tracks t ON ct.track_id = t.id
JOIN artist_album aa ON t.album_id = aa.album_id
JOIN artists ar ON aa.artist_id = ar.id
WHERE ar.name = 'Queen';