Select_t.sql - master - BARS-uk/bd_homework_3

Select_t.sql
116 строк4 KB
BARS-uk
update Select_t.sql
09 окт 2025, 21:01
09 окт 2025, 21:015f1d2e4
-- Задание 2

--Название и продолжительность самого длительного трека
SELECT id, name, duration
FROM track
WHERE duration = (SELECT MAX(duration) FROM track); 

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

--Названия сборников, вышедших в период с 2018 по 2020 год включительно
SELECT id, name, year_of_release
FROM collection
WHERE year_of_release between 2018 and 2020;

-- Исполнители, чьё имя состоит из одного слова
SELECT id, nick_name
FROM singer 
WHERE nick_name NOT LIKE '% %';

-- Название треков, которые содержат слово «мой» или «my»
SELECT id, name
FROM track
WHERE name ~* '\m(мой|my)\M';

-- Задание 3

-- Количество исполнителей в каждом жанре
SELECT g.name AS genre_name, 
	   count(sg.singer_id) AS singer_count
FROM genre g 
LEFT JOIN singergenre sg ON g.id = sg.genre_id
GROUP BY g.id, g.name;

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

-- Средняя продолжительность треков по каждому альбому
SELECT a.name AS album_name,       
       ceil(AVG(t.duration)) as avg_duration_track
FROM album a 
JOIN track t ON a.id = t.album_id 
GROUP BY a.id, a.name
ORDER BY a.id;

-- Все исполнители, которые не выпустили альбомы в 2020 году
SELECT s.nick_name AS singer
FROM singer s
WHERE s.nick_name NOT IN (
	select s.nick_name 
	from singer s
	join singeralbum sa on s.id = sa.singer_id 
	join album a on sa.album_id = a.id 
	where a.year_of_release = 2020
);

-- Названия сборников, в которых присутствует конкретный исполнитель
SELECT distinct c.name AS collection_name
FROM collection c
JOIN trackcollection tc ON c.id = tc.collection_id 
JOIN track t  ON tc.track_id = t.id 
JOIN album a ON t.album_id = a.id 
JOIN singeralbum sa ON a.id = sa.album_id 
JOIN singer s ON sa.singer_id = s.id 
WHERE s.nick_name = 'SHAMAN';

-- Задание 4

-- Названия альбомов, в которых присутствуют исполнители более чем одного жанра.
SELECT a.name AS album_name
FROM album a 
JOIN singeralbum sa ON a.id = sa.album_id
JOIN singer s ON sa.singer_id = s.id 
JOIN singergenre sg ON s.id = sg.singer_id 
GROUP BY a.id, a.name, sg.singer_id 
HAVING COUNT(distinct sg.genre_id) > 1;

-- Наименования треков, которые не входят в сборники.
--SELECT t.name AS track_nane, t.id 
--FROM track t 
--WHERE t.id not in (select track_id from trackcollection);
SELECT t.name AS track_nane
FROM track t 
LEFT JOIN trackcollection tc on t.id = tc.track_id 
WHERE tc.track_id is null 
ORDER BY t.name;

-- Исполнитель или исполнители, написавшие самый короткий по продолжительности трек.
SELECT s.nick_name AS singer,
       t.duration
FROM singer s 
JOIN singeralbum sa ON s.id = sa.singer_id 
JOIN album a  ON sa.album_id = a.id 
JOIN track t ON a.id = t.album_id 
WHERE t.duration = (select MIN(t.duration) from track t);

-- Названия альбомов, содержащих наименьшее количество треков.
SELECT a.name AS album_name, count(t.id) AS track_count
FROM album a 
JOIN track t ON a.id = t.album_id 
GROUP BY a.id, a.name 
HAVING COUNT(t.id) = (
	SELECT MIN(track_count)
	FROM (
		SELECT COUNt(t2.id) as track_count
		FROM album a2 
		JOIN track t2 ON a2.id = t2.album_id 
		GROUP BY a2.id
		) AS counts
)
ORDER BY a.name;