SELECT.sql - master - fess/BaseDate

SELECT.sql
118 строк4 KB
Станислав Константинов
upload files
02 фев 2026, 11:34
02 фев 2026, 11:3422536bb
-- select ������� ������� �2
SELECT title, duration FROM Tracks ORDER BY duration DESC LIMIT 1;
SELECT title FROM Tracks WHERE duration >= 210;
SELECT title, releaseyear FROM compilations WHERE releaseyear  BETWEEN 2018 and 2020;
SELECT name FROM artists WHERE name NOT LIKE '% %';

--��� ������ ��������� ������������ string_to_array, lower � &&.
SELECT title FROM Tracks
WHERE string_to_array(lower(title), ' ') && ARRAY['���', 'my'];
-- ��� ������ ������������ ����� ����������� ��������� ��� ����� �������� ~*. 
SELECT title FROM Tracks
WHERE title ~* '\m���\M' 
   OR title ~* '\mmy\M'
ORDER BY title;

-- select ������� ������� �3
-- ���������� ������������ � ������ �����.
SELECT g."name", COUNT(genreid) FROM ArtistGenres ag
JOIN genres g ON ag.genreid = g.id 
GROUP BY g."name";
--���������� ������, �������� � ������� 2019�2020 �����.
SELECT COUNT(t.title) FROM albums a 
JOIN tracks t ON t.albumid = a.id
WHERE a.releaseyear BETWEEN 2019 AND 2020;
--������� ����������������� ������ �� ������� �������.
SELECT a.title, AVG(t.duration)
FROM albums a 
LEFT JOIN tracks t ON t.albumid = a.id
GROUP BY a.title ;
--��� �����������, ������� �� ��������� ������� � 2020 ����.
-- 1. ���������� ������������ � ������ �����.
SELECT g.name, COUNT(ag.artistid) 
FROM Genres g
LEFT JOIN ArtistGenres ag ON g.id = ag.genreid
GROUP BY g.name;

-- 2. ���������� ������, �������� � ������� 2019�2020 �����.
SELECT COUNT(t.id) as track_count
FROM Tracks t
JOIN Albums a ON t.albumid = a.id
WHERE a.releaseyear BETWEEN 2019 AND 2020;

-- 3. ������� ����������������� ������ �� ������� �������.
SELECT a.title, ROUND(AVG(t.duration), 2) as avg_duration_seconds
FROM Albums a
LEFT JOIN Tracks t ON a.id = t.albumid
GROUP BY a.id, a.title
ORDER BY avg_duration_seconds DESC;

-- 4. ��� �����������, ������� �� ��������� ������� � 2020 ����.
SELECT a.name
FROM Artists a
WHERE a.id NOT IN (
    SELECT aa.artistid
    FROM AlbumArtists aa
    JOIN Albums al ON aa.albumid = al.id
    WHERE al.releaseyear = 2020
);
-- �������� ���������, � ������� ������������ ���������� ����������� (�������� ��� ����)(�����).
SELECT c.title 
FROM compilations c 
JOIN compilationtracks ct ON c.id = ct.compilationid
JOIN tracks t ON ct.trackid = t.id
JOIN albums a ON t.albumid = a.id 
JOIN albumartists a2 ON a.id = a2.albumid
JOIN artists a3 ON a3.id = a2.artistid 
WHERE a3."name" = '�����';

-- select ������� ������� �4 (��������������)
-- 1. �������� ��������, � ������� ������������ ����������� ����� ��� ������ �����.
SELECT DISTINCT a.title as album_title
FROM Albums a
WHERE a.id IN (
    SELECT aa.albumid
    FROM AlbumArtists aa
    JOIN Artists ar ON aa.artistid = ar.id
    JOIN ArtistGenres ag ON ar.id = ag.artistid
    GROUP BY aa.albumid, ar.id
    HAVING COUNT(DISTINCT ag.genreid) > 1
)
ORDER BY a.title;

-- 2. ������������ ������, ������� �� ������ � ��������
SELECT t.title as track_title
FROM Tracks t
LEFT JOIN CompilationTracks ct ON t.id = ct.trackid
WHERE ct.trackid IS NULL
ORDER BY t.title;

-- 3. ����������� ��� �����������, ���������� ����� �������� �� ����������������� ����, 
-- � ������������ ����� ������ ����� ���� ���������.
-- ����������� ������ ��������� �����
SELECT DISTINCT ar.name as artist_name, t.duration as track_duration
FROM Artists ar
JOIN AlbumArtists aa ON ar.id = aa.artistid
JOIN Albums a ON aa.albumid = a.id
JOIN Tracks t ON a.id = t.albumid
WHERE t.duration = (
    SELECT MIN(duration) 
    FROM Tracks
)
ORDER BY ar.name;

-- 4. �������� ��������, ���������� ���������� ���������� ������.
SELECT a.title as album_title, COUNT(t.id) as track_count
FROM Albums a
LEFT JOIN Tracks t ON a.id = t.albumid
GROUP BY a.id, a.title
HAVING COUNT(t.id) = (
    SELECT MIN(track_count)
    FROM (
        SELECT COUNT(t2.id) as track_count
        FROM Albums a2
        LEFT JOIN Tracks t2 ON a2.id = t2.albumid
        GROUP BY a2.id
    ) as album_track_counts
)
ORDER BY a.title;