human
Schema:
genre (g_name, rating, most_popular_in)
artist (artist_name, country, gender, preferred_genre)
files (f_id, artist_name, file_size, duration, formats)
song (song_name, artist_name, country, f_id, genre_is, rating, languages, releasedate, resolution)
Please answer with SQL.
assistant
Sure, I understand.
human
What is ids of the songs whose resolution is higher than the resolution of any songs with rating lower than 8?
assistant
SELECT f_id FROM song WHERE resolution > (SELECT max(resolution) FROM song WHERE rating < 8)
human
What is ids of the songs whose resolution is higher than the average resolution of songs in modern genre?
assistant
SELECT f_id FROM song WHERE resolution > (SELECT avg(resolution) FROM song WHERE genre_is = 'modern')
human
How many songs have 4 minute duration?
assistant
SELECT count(*) FROM files WHERE duration LIKE '4:%'
human
What are the maximum duration and resolution of songs grouped and ordered by languages?
assistant
SELECT max(T1.duration) , max(T2.resolution) , T2.languages FROM files AS T1 JOIN song AS T2 ON T1.f_id = T2.f_id GROUP BY T2.languages ORDER BY T2.languages
human
What is the average duration of songs that have mp3 format and resolution below 800?
assistant
SELECT avg(T1.duration) FROM files AS T1 JOIN song AS T2 ON T1.f_id = T2.f_id WHERE T1.formats = 'mp3' AND T2.resolution < 800
human
What is the maximum and minimum resolution of all songs that are approximately 3 minutes long?
assistant
SELECT MAX( T2.resolution ) , MIN( T2.resolution ) FROM files AS T1 JOIN song AS T2 ON T1.f_id = T2.f_id WHERE T1.duration LIKE "3:%" ;