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
Find the distinct names of all songs that have a higher resolution than some songs in English.
assistant
SELECT DISTINCT song_name FROM song WHERE resolution > (SELECT min(resolution) FROM song WHERE languages = 'english')
human
What is the average rating of songs for each language?
assistant
SELECT avg(rating) , languages FROM song GROUP BY languages
human
Find the names of the artists who have produced English songs but have never received rating higher than 8.
assistant
SELECT DISTINCT artist_name FROM song WHERE languages = 'english' EXCEPT SELECT DISTINCT artist_name FROM song WHERE rating > 8
human
What are the names of all songs that have a lower rating than some song of blues genre?
assistant
SELECT song_name FROM song WHERE rating < (SELECT max(rating) FROM song WHERE genre_is = 'blues')
human
List the id, genre and artist name of English songs ordered by rating.
assistant
SELECT f_id , genre_is , artist_name FROM song WHERE languages = 'english' ORDER BY rating
human
What are the song names for every song whose rating is less than the minimum rating for English songs?
assistant
SELECT song_name FROM song WHERE rating < ( SELECT MIN( rating ) FROM song WHERE languages = "english" ) ;