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 names of the artists from Bangladesh?
assistant
SELECT artist_name FROM artist WHERE country = 'Bangladesh';
human
Which artists from Bangladesh have never received a rating higher that 7?
assistant
SELECT DISTINCT artist_name FROM artist WHERE country = 'Bangladesh' EXCEPT SELECT DISTINCT artist_name FROM song WHERE rating > 7;
human
Can you add their genders into the table please?
assistant
SELECT artist_name , gender FROM artist WHERE artist_name IN ( SELECT DISTINCT artist_name FROM artist WHERE country = 'Bangladesh' EXCEPT SELECT DISTINCT artist_name FROM song WHERE rating > 7 );
human
What are their preferred genres?
assistant
SELECT preferred_genre FROM artist WHERE artist_name IN ( SELECT DISTINCT artist_name FROM artist WHERE country = "Bangladesh" EXCEPT SELECT DISTINCT artist_name FROM song WHERE rating > 7 ) ;