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 are the shortest duration and lowest rating of songs grouped by genre and ordered by genre?
assistant
SELECT min(T1.duration) , min(T2.rating) , T2.genre_is FROM files AS T1 JOIN song AS T2 ON T1.f_id = T2.f_id GROUP BY T2.genre_is ORDER BY T2.genre_is
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 are the maximum and minimum resolution of songs whose duration is 3 minutes?
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:%'
human
List the names of all songs that have 4 minute duration or are in English.
assistant
SELECT T2.song_name FROM files AS T1 JOIN song AS T2 ON T1.f_id = T2.f_id WHERE T1.duration LIKE '4:%' UNION SELECT song_name FROM song WHERE languages = 'english'
human
How many songs have 4 minute duration?
assistant
SELECT COUNT( * ) FROM files WHERE duration LIKE "4:%" ;