Script SQL Advanced data sampling correction.sql - master - SinotovIvan/Advanced_data_sampling_correction

Script SQL Advanced data sampling correction.sql
194 строки8 KB
SinotovIvan
upload files
14 авг 2025, 15:55
14 авг 2025, 15:55380931a
-- Создание таблицы жанров
CREATE TABLE IF NOT EXISTS genres (
    genre_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

-- Создание таблицы исполнителей
CREATE TABLE IF NOT EXISTS artists (
    artist_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Связующая таблица для отношения многие-ко-многим между исполнителями и жанрами
CREATE TABLE IF NOT EXISTS artist_genres (
    artist_id INTEGER REFERENCES artists(artist_id) ON DELETE CASCADE,
    genre_id INTEGER REFERENCES genres(genre_id) ON DELETE CASCADE,
    PRIMARY KEY (artist_id, genre_id)
);

-- Создание таблицы альбомов
CREATE TABLE IF NOT EXISTS albums (
    album_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    year INTEGER NOT NULL CHECK (year > 1900 AND year <= EXTRACT(YEAR FROM CURRENT_DATE))
);

-- Связующая таблица для отношения многие-ко-многим между исполнителями и альбомами
CREATE TABLE IF NOT EXISTS album_artists (
    album_id INTEGER REFERENCES albums(album_id) ON DELETE CASCADE,
    artist_id INTEGER REFERENCES artists(artist_id) ON DELETE CASCADE,
    PRIMARY KEY (album_id, artist_id)
);

-- Создание таблицы треков
CREATE TABLE IF NOT EXISTS tracks (
    track_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    duration INTEGER NOT NULL CHECK (duration > 0), -- в секундах
    album_id INTEGER NOT NULL REFERENCES albums(album_id) ON DELETE CASCADE
);

-- Создание таблицы сборников
CREATE TABLE IF NOT EXISTS collections (
    collection_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    year INTEGER NOT NULL CHECK (year > 1900 AND year <= EXTRACT(YEAR FROM CURRENT_DATE))
);

-- Связующая таблица для отношения многие-ко-многим между сборниками и треками
CREATE TABLE IF NOT EXISTS collection_tracks (
    collection_id INTEGER REFERENCES collections(collection_id) ON DELETE CASCADE,
    track_id INTEGER REFERENCES tracks(track_id) ON DELETE CASCADE,
    PRIMARY KEY (collection_id, track_id)
);

-- 1 Задание. Заполнение таблиц данными

-- Жанры
INSERT INTO genres (name) VALUES 
('Rock'), ('Pop'), ('Hip-Hop'), ('Jazz'), ('Electronic');

-- Исполнители
INSERT INTO artists (name) VALUES 
('The Beatles'), ('Madonna'), ('Eminem'), ('Miles Davis'), ('Daft Punk');

-- Связи исполнителей и жанров
INSERT INTO artist_genres (artist_id, genre_id) VALUES
(1, 1), (1, 2), (2, 2), (3, 3), (4, 4), (5, 5), (5, 2);

-- Альбомы
INSERT INTO albums (name, year) VALUES
('Abbey Road', 1969),
('Like a Virgin', 1984),
('The Marshall Mathers LP', 2000),
('Kind of Blue', 1959),
('Random Access Memories', 2013),
('Discovery', 2001);

-- Связи альбомов и исполнителей
INSERT INTO album_artists (album_id, artist_id) VALUES
(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 5);

-- Треки
INSERT INTO tracks (name, duration, album_id) VALUES
('Come Together', 259, 1),
('Something', 182, 1),
('Like a Virgin', 220, 2),
('Material Girl', 244, 2),
('The Real Slim Shady', 284, 3),
('Stan', 404, 3),
('So What', 562, 4),
('Freddie Freeloader', 589, 4),
('Get Lucky', 369, 5),
('Instant Crush', 337, 5),
('One More Time', 320, 6),
('Harder, Better, Faster, Stronger', 224, 6);

-- Сборники
INSERT INTO collections (name, year) VALUES
('Greatest Hits of 20th Century', 1999),
('Pop Divas Collection', 2018),
('Hip-Hop Essentials', 2019),
('Jazz Classics', 2020),
('Electronic Dance Anthems', 2021),
('Best of 2000s', 2022);

-- Связи сборников и треков
INSERT INTO collection_tracks (collection_id, track_id) VALUES
(1, 1), (1, 2), (1, 3), (2, 3), (2, 4), (3, 5), 
(3, 6), (4, 7), (4, 8), (5, 9), (5, 10), (5, 11), 
(5, 12), (6, 5), (6, 6), (6, 11), (6, 12);

-- 2 Задание. Основные SELECT-запросы

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

-- 2. Название треков, продолжительность которых не менее 3,5 минут (210 секунд)
SELECT name 
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 
    -- Для слова "my"
    name ~* '\mmy\M' OR      -- слово "my" как отдельное слово
    name ~* '^my ' OR        -- "my" в начале строки с пробелом после
    name ~* ' my$' OR        -- "my" в конце строки с пробелом перед
    name ~* ' my ' OR        -- "my" в середине строки (с пробелами с обеих сторон)
    name = 'my' OR           -- строка состоит только из "my"
    
    -- Для слова "мой"
    name ~* '\mмой\M' OR     -- слово "мой" как отдельное слово
    name ~* '^мой ' OR       -- "мой" в начале строки с пробелом после
    name ~* ' мой$' OR       -- "мой" в конце строки с пробелом перед
    name ~* ' мой ' OR       -- "мой" в середине строки (с пробелами с обеих сторон)
    name = 'мой';            -- строка состоит только из "мой"

-- 3 Задание. Продвинутые SELECT-запросы

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

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

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

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

-- 5. Названия сборников, в которых присутствует конкретный исполнитель (Daft Punk)
SELECT DISTINCT c.name
FROM collections c
JOIN collection_tracks ct ON c.collection_id = ct.collection_id
JOIN tracks t ON ct.track_id = t.track_id
JOIN albums a ON t.album_id = a.album_id
JOIN album_artists aa ON a.album_id = aa.album_id
JOIN artists ar ON aa.artist_id = ar.artist_id
WHERE ar.name = 'Daft Punk';