music_service_select.sql - master - urpdvk/SQL_database

music_service_select.sql
119 строк5 KB
urpdvk
Доработка
23 июн 2026, 16:21
23 июн 2026, 16:2151d613e
-- ============================================================
--  Файл 3 из 3: SELECT-запросы — Задания 2, 3, 4
-- ============================================================


-- ============================  ЗАДАНИЕ 2  ============================

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

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

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

-- 2.4. Исполнители, чьё имя состоит из одного слова (без пробела).
SELECT name
FROM artist
WHERE name NOT LIKE '% %';

-- 2.5. Названия треков, содержащих слово «мой» или «my» как отдельное СЛОВО
--      (без учёта регистра). Слово отделяется пробелами или границей строки,
--      поэтому для каждого искомого слова — 4 проверки:
--      начало строки, конец строки, середина строки, вся строка целиком.
SELECT title
FROM track
WHERE title ILIKE 'мой %'      -- «мой» в начале строки
   OR title ILIKE '% мой'      -- «мой» в конце строки
   OR title ILIKE '% мой %'    -- «мой» в середине строки
   OR title ILIKE 'мой'        -- вся строка = «мой»
   OR title ILIKE 'my %'       -- «my» в начале строки
   OR title ILIKE '% my'       -- «my» в конце строки
   OR title ILIKE '% my %'     -- «my» в середине строки
   OR title ILIKE 'my';        -- вся строка = «my»


-- ============================  ЗАДАНИЕ 3  ============================

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

-- 3.2. Количество треков, вошедших в альбомы 2019–2020 годов.
SELECT COUNT(*) AS tracks_count
FROM track t
JOIN album a ON a.id = t.album_id
WHERE a.release_year BETWEEN 2019 AND 2020;

-- 3.3. Средняя продолжительность треков по каждому альбому.
SELECT a.title, ROUND(AVG(t.duration), 1) AS avg_duration
FROM album a
JOIN track t ON t.album_id = a.id
GROUP BY a.id, a.title
ORDER BY avg_duration DESC;

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

-- 3.5. Названия сборников, в которых присутствует исполнитель «Баста».
SELECT DISTINCT c.title
FROM compilation c
JOIN compilation_track ct ON ct.compilation_id = c.id
JOIN track t             ON t.id = ct.track_id
JOIN artist_album aa     ON aa.album_id = t.album_id
JOIN artist ar           ON ar.id = aa.artist_id
WHERE ar.name = 'Баста';


-- ============================  ЗАДАНИЕ 4 (необязательное)  ============================

-- 4.1. Названия альбомов, в которых присутствует хотя бы один исполнитель,
--      поющий более чем в одном жанре. Группируем по альбому И по исполнителю,
--      чтобы считать число жанров для каждого исполнителя отдельно.
SELECT DISTINCT a.title
FROM album a
JOIN artist_album aa ON aa.album_id = a.id
JOIN artist_genre ag ON ag.artist_id = aa.artist_id
GROUP BY a.id, a.title, aa.artist_id
HAVING COUNT(DISTINCT ag.genre_id) > 1;

-- 4.2. Наименования треков, которые не входят в сборники.
SELECT title
FROM track
WHERE id NOT IN (SELECT track_id FROM compilation_track);

-- 4.3. Исполнитель(и), написавшие самый короткий по продолжительности трек.
SELECT DISTINCT ar.name, t.title, t.duration
FROM track t
JOIN artist_album aa ON aa.album_id = t.album_id
JOIN artist ar       ON ar.id = aa.artist_id
WHERE t.duration = (SELECT MIN(duration) FROM track);

-- 4.4. Названия альбомов, содержащих наименьшее количество треков.
SELECT a.title, COUNT(t.id) AS tracks_count
FROM album a
JOIN track t ON t.album_id = a.id
GROUP BY a.id, a.title
HAVING COUNT(t.id) = (
    SELECT MIN(cnt)
    FROM (SELECT COUNT(id) AS cnt FROM track GROUP BY album_id) sub
);