select.sql - master - Kirby/MusicWork

select.sql
99 строк2 KB
Alex
Initial commit
28 апр 2026, 12:29
28 апр 2026, 12:29f458ccb
-- 2.1
SELECT name, duration
FROM track
ORDER BY duration DESC
LIMIT 1;

-- 2.2
SELECT name
FROM track
WHERE duration >= 210;

-- 2.3
SELECT name
FROM collection
WHERE year BETWEEN 2018 AND 2020;

-- 2.4
SELECT name
FROM artist
WHERE name NOT LIKE '% %';

-- 2.5
SELECT name
FROM track
WHERE name ILIKE '% my %' OR name ILIKE '% мой %'
   OR name ILIKE 'my %' OR name ILIKE 'мой %'
   OR name ILIKE '% my' OR name ILIKE '% мой';

-- 3.1
SELECT g.name, count(ag.artist_id)
FROM genre g
JOIN artist_genre ag ON g.id = ag.genre_id
GROUP BY g.name;

-- 3.2
SELECT count(t.id)
FROM track t
JOIN album a ON t.album_id = a.id
WHERE a.year BETWEEN 2019 AND 2020;

-- 3.3
SELECT a.name, AVG(t.duration)
FROM album a
JOIN track t ON a.id = t.album_id
GROUP BY a.name;

-- 3.4
SELECT ar.name
FROM artist ar
WHERE ar.id NOT IN (
    SELECT aa.artist_id
    FROM artist_album aa
    JOIN album al ON aa.album_id = al.id
    WHERE al.year = 2020
);

-- 3.5
SELECT DISTINCT c.name
FROM collection c
JOIN collection_track ct ON c.id = ct.collection_id
JOIN track t ON ct.track_id = t.id
JOIN album a ON t.album_id = a.id
JOIN artist_album aa ON a.id = aa.album_id
JOIN artist ar ON aa.artist_id = ar.id
WHERE ar.name = 'Lumen';

-- 4.1
SELECT DISTINCT a.name
FROM album a
JOIN artist_album aa ON a.id = aa.album_id
JOIN artist_genre ag ON aa.artist_id = ag.artist_id
GROUP BY a.name, aa.artist_id
HAVING count(ag.genre_id) > 1;

-- 4.2
SELECT t.name
FROM track t
LEFT JOIN collection_track ct ON t.id = ct.track_id
WHERE ct.track_id IS NULL;

-- 4.3
SELECT ar.name
FROM artist ar
JOIN artist_album aa ON ar.id = aa.artist_id
JOIN track t ON aa.album_id = t.album_id
WHERE t.duration = (SELECT MIN(duration) FROM track);

-- 4.4
SELECT a.name
FROM album a
JOIN track t ON a.id = t.album_id
GROUP BY a.name
HAVING count(t.id) = (
    SELECT MIN(cnt)
    FROM (SELECT count(t.id) AS cnt
          FROM album a
          JOIN track t ON a.id = t.album_id
          GROUP BY a.name) AS track_counts
);