CREATE TABLE genre (
    g_name TEXT PRIMARY KEY,
    rating TEXT,
    most_popular_in TEXT
);
CREATE TABLE artist (
    artist_name TEXT PRIMARY KEY,
    country TEXT,
    gender TEXT,
    preferred_genre TEXT,
    FOREIGN KEY ("preferred_genre") REFERENCES genre("g_name")
);
CREATE TABLE files (
    f_id NUMBER PRIMARY KEY,
    artist_name TEXT,
    file_size TEXT,
    duration TEXT,
    formats TEXT,
    FOREIGN KEY ("artist_name") REFERENCES artist("artist_name")
);
CREATE TABLE song (
    song_name TEXT PRIMARY KEY,
    artist_name TEXT,
    country TEXT,
    f_id NUMBER,
    genre_is TEXT,
    rating NUMBER,
    languages TEXT,
    releasedate TIME,
    resolution NUMBER,
    FOREIGN KEY ("genre_is") REFERENCES genre("g_name"),
    FOREIGN KEY ("f_id") REFERENCES files("f_id"),
    FOREIGN KEY ("artist_name") REFERENCES artist("artist_name")
);

Question: What are the maximum duration and resolution of all songs, for each language, ordered alphabetically by language?
SQL: 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

Question: What are the shortest duration and lowest rating of songs grouped by genre and ordered by genre?
SQL: 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

Question: What is the shortest and most poorly rated song for each genre, ordered alphabetically by genre?
SQL: 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

Question: Find the names and number of works of all artists who have at least one English songs.
SQL: SELECT T1.artist_name ,  count(*) FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.languages  =  "english" GROUP BY T2.artist_name HAVING count(*)  >=  1

Question: What are the names and number of works for all artists who have sung at least one song in English?
SQL: SELECT T1.artist_name ,  count(*) FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.languages  =  "english" GROUP BY T2.artist_name HAVING count(*)  >=  1

Question: Find the name and country of origin for all artists who have release at least one song of resolution above 900.
SQL: SELECT T1.artist_name ,  T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.resolution  >  900 GROUP BY T2.artist_name HAVING count(*)  >=  1

Question: What is the name and country of origin for each artist who has released a song with a resolution higher than 900?
SQL: SELECT T1.artist_name ,  T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.resolution  >  900 GROUP BY T2.artist_name HAVING count(*)  >=  1

Question: Find the names and number of works of the three artists who have produced the most songs.
SQL: SELECT T1.artist_name ,  count(*) FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name GROUP BY T2.artist_name ORDER BY count(*) DESC LIMIT 3

Question: What are the names of the three artists who have produced the most songs, and how many works did they produce?
SQL: SELECT T1.artist_name ,  count(*) FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name GROUP BY T2.artist_name ORDER BY count(*) DESC LIMIT 3

Question: Find the country of origin for the artist who made the least number of songs?
SQL: SELECT T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name GROUP BY T2.artist_name ORDER BY count(*) LIMIT 1

Question: What country is the artist who made the fewest songs from?
SQL: SELECT T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name GROUP BY T2.artist_name ORDER BY count(*) LIMIT 1

Question: What are the names of the songs whose rating is below the rating of all songs in English?
SQL: SELECT song_name FROM song WHERE rating  <   (SELECT min(rating) FROM song WHERE languages  =  'english')

Question: What are the song names for every song whose rating is less than the minimum rating for English songs?
SQL: SELECT song_name FROM song WHERE rating  <   (SELECT min(rating) FROM song WHERE languages  =  'english')

Question: What is ids of the songs whose resolution is higher than the resolution of any songs with rating lower than 8?
SQL: SELECT f_id FROM song WHERE resolution  >  (SELECT max(resolution) FROM song WHERE rating  <  8)

Question: What is the id of every song that has a resolution higher than that of a song with a rating below 8?
SQL: SELECT f_id FROM song WHERE resolution  >  (SELECT max(resolution) FROM song WHERE rating  <  8)

Question: What is ids of the songs whose resolution is higher than the average resolution of songs in modern genre?
SQL: SELECT f_id FROM song WHERE resolution  >  (SELECT avg(resolution) FROM song WHERE genre_is  =  "modern")

Question: What are the ids of all songs that have higher resolution of the average resolution in the modern genre?
SQL: SELECT f_id FROM song WHERE resolution  >  (SELECT avg(resolution) FROM song WHERE genre_is  =  "modern")

Question: Find the top 3 artists who have the largest number of songs works whose language is Bangla.
SQL: SELECT T1.artist_name FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.languages  =  "bangla" GROUP BY T2.artist_name ORDER BY count(*) DESC LIMIT 3

Question: What are the top 3 artists with the largest number of songs in the language Bangla?
SQL: SELECT T1.artist_name FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.languages  =  "bangla" GROUP BY T2.artist_name ORDER BY count(*) DESC LIMIT 3

Question: List the id, genre and artist name of English songs ordered by rating.
SQL: SELECT f_id ,  genre_is ,  artist_name FROM song WHERE languages  =   "english" ORDER BY rating

Question: What is the id, genre, and name of the artist for every English song ordered by ascending rating?
SQL: SELECT f_id ,  genre_is ,  artist_name FROM song WHERE languages  =   "english" ORDER BY rating

Question: List the duration, file size and format of songs whose genre is pop, ordered by title?
SQL: SELECT T1.duration ,  T1.file_size ,  T1.formats FROM files AS T1 JOIN song AS T2 ON T1.f_id  =  T2.f_id WHERE T2.genre_is  =  "pop" ORDER BY T2.song_name

Question: What is the duration, file size, and song format for every pop song, ordered by title alphabetically?
SQL: SELECT T1.duration ,  T1.file_size ,  T1.formats FROM files AS T1 JOIN song AS T2 ON T1.f_id  =  T2.f_id WHERE T2.genre_is  =  "pop" ORDER BY T2.song_name

Question: Find the names of the artists who have produced English songs but have never received rating higher than 8.
SQL: SELECT DISTINCT artist_name FROM song WHERE languages  =  "english" EXCEPT SELECT DISTINCT artist_name FROM song WHERE rating  >  8

Question: What are the names of the different artists that have produced a song in English but have never receieved a rating higher than 8?
SQL: SELECT DISTINCT artist_name FROM song WHERE languages  =  "english" EXCEPT SELECT DISTINCT artist_name FROM song WHERE rating  >  8

Question: Find the names of the artists who are from Bangladesh and have never received rating higher than 7.
SQL: SELECT DISTINCT artist_name FROM artist WHERE country  =  "Bangladesh" EXCEPT SELECT DISTINCT artist_name FROM song WHERE rating  >  7

Question: What are the names of the different artists from Bangladesh who never received a rating higher than a 7?
SQL: SELECT DISTINCT artist_name FROM artist WHERE country  =  "Bangladesh" EXCEPT SELECT DISTINCT artist_name FROM song WHERE rating  >  7