select_queries.sql - master - Askoline/Homework_DB

select_queries.sql
195 строк7 KB
Askoline
Add music database schema, data, and queries
01 авг 2026, 20:49
01 авг 2026, 20:49a62a964
-- =====================================================
-- SELECT-ЗАПРОСЫ ДЛЯ МУЗЫКАЛЬНОЙ БАЗЫ ДАННЫХ
-- ЗАДАНИЯ 2, 3, 4
-- =====================================================

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

-- 1. Название и продолжительность самого длительного трека
SELECT 
    title AS "Название трека",
    duration AS "Продолжительность (секунд)",
    CONCAT(FLOOR(duration / 60), ':', LPAD((duration % 60)::TEXT, 2, '0')) AS "Продолжительность (мм:сс)"
FROM track
ORDER BY duration DESC
LIMIT 1;

-- 2. Название треков, продолжительность которых не менее 3,5 минут
SELECT 
    title AS "Название трека",
    duration AS "Продолжительность (секунд)",
    CONCAT(FLOOR(duration / 60), ':', LPAD((duration % 60)::TEXT, 2, '0')) AS "Продолжительность (мм:сс)"
FROM track
WHERE duration >= 210
ORDER BY duration DESC;

-- 3. Названия сборников, вышедших в период с 2018 по 2020 год включительно
SELECT 
    title AS "Название сборника",
    year AS "Год выпуска"
FROM compilation
WHERE year BETWEEN 2018 AND 2020
ORDER BY year DESC, title;

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

-- 5. Название треков, которые содержат слово «мой» или «my»
SELECT 
    id AS "ID",
    title AS "Название трека",
    duration AS "Продолжительность (секунд)"
FROM track
WHERE LOWER(title) LIKE '%мой%' 
   OR LOWER(title) LIKE '%my%'
ORDER BY title;

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

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

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

-- Детализация по альбомам
SELECT 
    a.title AS "Альбом",
    a.year AS "Год",
    COUNT(t.id) AS "Количество треков"
FROM album a
LEFT JOIN track t ON a.id = t.album_id
WHERE a.year BETWEEN 2019 AND 2020
GROUP BY a.id, a.title, a.year
ORDER BY a.year, a.title;

-- 3. Средняя продолжительность треков по каждому альбому
SELECT 
    a.title AS "Альбом",
    a.year AS "Год",
    COUNT(t.id) AS "Количество треков",
    ROUND(AVG(t.duration), 2) AS "Средняя продолжительность (сек)",
    CONCAT(
        FLOOR(AVG(t.duration) / 60), 
        ':', 
        LPAD(ROUND(AVG(t.duration) % 60)::TEXT, 2, '0')
    ) AS "Средняя продолжительность (мм:сс)"
FROM album a
LEFT JOIN track t ON a.id = t.album_id
GROUP BY a.id, a.title, a.year
HAVING COUNT(t.id) > 0
ORDER BY AVG(t.duration) DESC;

-- 4. Все исполнители, которые не выпустили альбомы в 2020 году
SELECT 
    a.id AS "ID",
    a.name AS "Исполнитель"
FROM artist a
WHERE a.id NOT IN (
    SELECT DISTINCT aa.artist_id
    FROM album_artist aa
    JOIN album al ON aa.album_id = al.id
    WHERE al.year = 2020
)
ORDER BY a.name;

-- 5. Названия сборников, в которых присутствует конкретный исполнитель
-- (The Weeknd, id = 11)
SELECT DISTINCT
    c.title AS "Название сборника",
    c.year AS "Год выпуска"
FROM compilation c
JOIN compilation_track ct ON c.id = ct.compilation_id
JOIN track t ON ct.track_id = t.id
JOIN album al ON t.album_id = al.id
JOIN album_artist aa ON al.id = aa.album_id
WHERE aa.artist_id = 11  -- The Weeknd
ORDER BY c.year, c.title;

-- =====================================================
-- ЗАДАНИЕ 4 (НЕОБЯЗАТЕЛЬНОЕ)
-- =====================================================

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

-- 2. Наименования треков, которые не входят в сборники
SELECT 
    t.id AS "ID",
    t.title AS "Название трека",
    t.duration AS "Длительность (сек)",
    CONCAT(FLOOR(t.duration / 60), ':', LPAD((t.duration % 60)::TEXT, 2, '0')) AS "Длительность (мм:сс)",
    al.title AS "Альбом"
FROM track t
LEFT JOIN compilation_track ct ON t.id = ct.track_id
JOIN album al ON t.album_id = al.id
WHERE ct.compilation_id IS NULL
ORDER BY t.title;

-- 3. Исполнитель или исполнители, написавшие самый короткий 
--    по продолжительности трек
SELECT 
    a.name AS "Исполнитель",
    t.title AS "Название трека",
    t.duration AS "Длительность (сек)",
    CONCAT(FLOOR(t.duration / 60), ':', LPAD((t.duration % 60)::TEXT, 2, '0')) AS "Длительность (мм:сс)",
    al.title AS "Альбом"
FROM track t
JOIN album al ON t.album_id = al.id
JOIN album_artist aa ON al.id = aa.album_id
JOIN artist a ON aa.artist_id = a.id
WHERE t.duration = (SELECT MIN(duration) FROM track)
ORDER BY a.name;

-- 4. Названия альбомов, содержащих наименьшее количество треков
WITH track_counts AS (
    SELECT 
        al.id,
        al.title,
        al.year,
        COUNT(t.id) AS track_count
    FROM album al
    LEFT JOIN track t ON al.id = t.album_id
    GROUP BY al.id, al.title, al.year
),
min_count AS (
    SELECT MIN(track_count) AS min_tracks FROM track_counts
)
SELECT 
    tc.title AS "Название альбома",
    tc.year AS "Год выпуска",
    tc.track_count AS "Количество треков"
FROM track_counts tc
CROSS JOIN min_count mc
WHERE tc.track_count = mc.min_tracks
ORDER BY tc.title;