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 the language that was used most often in songs with resolution above 500?
assistant
SELECT artist_name FROM song WHERE resolution > 500 GROUP BY languages ORDER BY count(*) DESC LIMIT 1
human
What is the average rating of songs for each language?
assistant
SELECT avg(rating) , languages FROM song GROUP BY 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
How many songs have 4 minute duration?
assistant
SELECT count(*) FROM files WHERE duration LIKE '4:%'
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
What are the maximum duration and resolution of all songs, for each language, ordered alphabetically by language?
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 ;