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 minimum, maximum and average values of the resolution?
assistant
SELECT MIN( resolution ) , MAX( resolution ) , AVG ( resolution ) FROM song;
human
Which songs have a higher value than the average, list the song ids.
assistant
SELECT f_id FROM song WHERE resolution > ( SELECT AVG ( resolution ) FROM song );
human
How about the ones that is higher than the average resolution value for the modern genre songs?
assistant
SELECT f_id FROM song WHERE resolution > ( SELECT AVG ( resolution ) FROM song WHERE genre_is = "modern" ) ;