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 is the name of the song that was released in the most recent year?
SQL: SELECT song_name ,  releasedate FROM song ORDER BY releasedate DESC LIMIT 1

Question: What is the name of the song that was released most recently?
SQL: SELECT song_name ,  releasedate FROM song ORDER BY releasedate DESC LIMIT 1

Question: What is the id of the longest song?
SQL: SELECT f_id FROM files ORDER BY duration DESC LIMIT 1

Question: Find the id of the song that lasts the longest.
SQL: SELECT f_id FROM files ORDER BY duration DESC LIMIT 1

Question: Find the names of all English songs.
SQL: SELECT song_name FROM song WHERE languages  =  "english"

Question: What are the names of all songs in English?
SQL: SELECT song_name FROM song WHERE languages  =  "english"

Question: What are the id of songs whose format is mp3.
SQL: SELECT f_id FROM files WHERE formats  =  "mp3"

Question: What are the id of all the files in mp3 format?
SQL: SELECT f_id FROM files WHERE formats  =  "mp3"

Question: List the name and country of origin for all singers who have produced songs with rating above 9.
SQL: SELECT DISTINCT T1.artist_name ,  T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.rating  >  9

Question: What are the different names and countries of origins for all artists whose song ratings are above 9?
SQL: SELECT DISTINCT T1.artist_name ,  T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.rating  >  9

Question: List the file size and format for all songs that have resolution lower than 800.
SQL: SELECT DISTINCT T1.file_size ,  T1.formats FROM files AS T1 JOIN song AS T2 ON T1.f_id  =  T2.f_id WHERE T2.resolution  <  800

Question: What are the file sizes and formats for all songs with a resolution lower than 800?
SQL: SELECT DISTINCT T1.file_size ,  T1.formats FROM files AS T1 JOIN song AS T2 ON T1.f_id  =  T2.f_id WHERE T2.resolution  <  800

Question: What is the name of the artist who produced the shortest song?
SQL: SELECT T1.artist_name FROM song AS T1 JOIN files AS T2 ON T1.f_id  =  T2.f_id ORDER BY T2.duration LIMIT 1

Question: What are the names of the artists who sang the shortest song?
SQL: SELECT T1.artist_name FROM song AS T1 JOIN files AS T2 ON T1.f_id  =  T2.f_id ORDER BY T2.duration LIMIT 1

Question: What are the names and countries of origin for the artists who produced the top three highly rated songs.
SQL: SELECT T1.artist_name ,  T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name ORDER BY T2.rating DESC LIMIT 3

Question: What are the names of the singers who sang the top 3 most highly rated songs and what countries do they hail from?
SQL: SELECT T1.artist_name ,  T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name ORDER BY T2.rating DESC LIMIT 3

Question: How many songs have 4 minute duration?
SQL: SELECT count(*) FROM files WHERE duration LIKE "4:%"

Question: What is the count of the songs that last approximately 4 minutes?
SQL: SELECT count(*) FROM files WHERE duration LIKE "4:%"

Question: How many artists are from Bangladesh?
SQL: SELECT count(*) FROM artist WHERE country  =  "Bangladesh"

Question: How many Bangladeshi artists are listed?
SQL: SELECT count(*) FROM artist WHERE country  =  "Bangladesh"

Question: What is the average rating of songs produced by female artists?
SQL: SELECT avg(T2.rating) FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T1.gender  =  "Female"

Question: How many songs, on average, are sung by a female artist?
SQL: SELECT avg(T2.rating) FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T1.gender  =  "Female"

Question: What is the most popular file format?
SQL: SELECT formats FROM files GROUP BY formats ORDER BY COUNT (*) DESC LIMIT 1

Question: Find the file format that is used by the most files.
SQL: SELECT formats FROM files GROUP BY formats ORDER BY COUNT (*) DESC LIMIT 1

Question: Find the names of the artists who are from UK and have produced English songs.
SQL: SELECT artist_name FROM artist WHERE country  =  "UK" INTERSECT SELECT artist_name FROM song WHERE languages  =  "english"

Question: What are the names of the artists that are from the UK and sang songs in English?
SQL: SELECT artist_name FROM artist WHERE country  =  "UK" INTERSECT SELECT artist_name FROM song WHERE languages  =  "english"

Question: Find the id of songs that are available in mp4 format and have resolution lower than 1000.
SQL: SELECT f_id FROM files WHERE formats  =  "mp4" INTERSECT SELECT f_id FROM song WHERE resolution  <  1000

Question: What is the id of the files that are available in the format of mp4 and a resolution smaller than 1000?
SQL: SELECT f_id FROM files WHERE formats  =  "mp4" INTERSECT SELECT f_id FROM song WHERE resolution  <  1000

Question: What is the country of origin of the artist who is female and produced a song in Bangla?
SQL: SELECT T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T1.gender  =  "Female" AND T2.languages  =  "bangla"

Question: What countries are the female artists who sung in the language Bangla from?
SQL: SELECT T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T1.gender  =  "Female" AND T2.languages  =  "bangla"

Question: What is the average duration of songs that have mp3 format and resolution below 800?
SQL: 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

Question: What is the average song duration for the songs that are in mp3 format and whose resolution below 800?
SQL: 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

Question: What is the number of artists for each gender?
SQL: SELECT count(*) ,  gender FROM artist GROUP BY gender

Question: How many artists are male and how many are female?
SQL: SELECT count(*) ,  gender FROM artist GROUP BY gender

Question: What is the average rating of songs for each language?
SQL: SELECT avg(rating) ,  languages FROM song GROUP BY languages

Question: What is the average song rating for each language?
SQL: SELECT avg(rating) ,  languages FROM song GROUP BY languages

Question: Return the gender and name of artist who produced the song with the lowest resolution.
SQL: SELECT T1.gender ,  T1.artist_name FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name ORDER BY T2.resolution LIMIT 1

Question: What is the gender and name of the artist who sang the song with the smallest resolution?
SQL: SELECT T1.gender ,  T1.artist_name FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name ORDER BY T2.resolution LIMIT 1

Question: For each file format, return the number of artists who released songs in that format.
SQL: SELECT count(*) ,  formats FROM files GROUP BY formats

Question: How many songs were released for each format?
SQL: SELECT count(*) ,  formats FROM files GROUP BY formats