Задание 3 Написать SELECT-запросы, которые выводят информацию согласно молодым людям ниже.

[anton216216]

-- 1. Number of artists in each genre
SELECT g.name AS genre, COUNT(ag.Artist_id) AS artist_count
FROM genres g
LEFT JOIN Artist_genre ag ON g.id = ag.genre_id
GROUP BY g.id, g.name
ORDER BY artist_count DESC;

-- 2. Number of tracks included in albums from 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. Average track duration by album
SELECT a.title AS album,
ROUND(AVG(t.duration), 2) AS avg_duration_sec,
CONCAT(ROUND(AVG(t.duration) / 60, 2), ' min') AS avg_duration
FROM albums a
JOIN tracks t ON a.id = t.album_id
GROUP BY a.id, a.title
ORDER BY AVG(t.duration) DESC;

-- 4. All artists who did not release albums in 2020
SELECT DISTINCT ar.name AS artist
FROM Artists ar
WHERE ar.name NOT IN (
SELECT DISTINCT aa.artist_id
FROM album_artist aa
JOIN albums a ON aa.album_id = a.id
WHERE a.year = 2020
)
ORDER BY ar.name;

-- 5. Names of compilations that feature a specific artist (Zemfira)
SELECT DISTINCT c.title AS compilation, c.year AS release_year
FROM compilation c
JOIN track_compilation tc ON c.id = tc.compilation_id
JOIN tracks t ON tc.track_id = t.id
JOIN albums a ON t.album_id = a.id
JOIN album_artist aa ON a.id = aa.album_id
JOIN Artists ar ON aa.artist_id = ar.name
WHERE ar.name = 'Zemfira'
ORDER BY c.year;

-- 5. Names of collections that feature a specific artist (Zemfira)
SELECT DISTINCT c.title AS compilation, c.year AS release_year
FROM compilations c
JOIN compilation_track tc ON c.id = tc.compilation_id
JOIN tracks t ON tc.track_id = t.id
JOIN albums a ON t.album = a.id
JOIN album_artist aa ON a.id = aa.album_id
JOIN artists ar ON aa.artist_id = ar.name
WHERE ar.name = 'Zemfira'
ORDER BY c.year;