Database: music_1

create table genre(
	g_name varchar2(20) not null,
	rating varchar2(10),
	most_popular_in varchar2(50),
	primary key(g_name)
);
create table artist(
	artist_name varchar2(50) not null,
	country varchar2(20),
	gender varchar2(20),
	preferred_genre varchar2(50),
	constraint a_name primary key(artist_name),
	foreign key(preferred_genre) references genre(g_name) ON DELETE CASCADE
);
create table files(
	f_id number(10) not null,
	artist_name varchar2(50),
	file_size varchar2(20),
	duration varchar2(20),
	formats varchar2(20),
	primary key(f_id),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE
);
create table song(
	song_name varchar2(50),
	artist_name varchar2(50),
	country varchar2(20),
	f_id number(10),
    	genre_is varchar2(20),
	rating number(10) check(rating>0 and rating<11),
	languages varchar2(20),
	releasedate Date, 
	resolution number(10) not null,
	constraint s_name primary key(song_name),
	foreign key(artist_name) references artist(artist_name) ON DELETE CASCADE,
	foreign key(f_id) references files(f_id) ON DELETE CASCADE,
	foreign key(genre_is) references genre(g_name) ON DELETE CASCADE
);

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

Question: Find the distinct names of all songs that have a higher resolution than some songs in English.
SQL: SELECT DISTINCT song_name FROM song WHERE resolution  >  (SELECT min(resolution) FROM song WHERE languages  =  "english")

Question: What are the different names for all songs that have a higher resolution than English songs?
SQL: SELECT DISTINCT song_name FROM song WHERE resolution  >  (SELECT min(resolution) FROM song WHERE languages  =  "english")

Question: What are the names of all songs that have a lower rating than some song of blues genre?
SQL: SELECT song_name FROM song WHERE rating  <  (SELECT max(rating) FROM song WHERE genre_is  =  "blues")

Question: What are the names of the songs that have a lower rating than at least one blues song?
SQL: SELECT song_name FROM song WHERE rating  <  (SELECT max(rating) FROM song WHERE genre_is  =  "blues")

Question: What is the name and country of origin of the artist who released a song that has "love" in its title?
SQL: SELECT T1.artist_name ,  T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.song_name LIKE "%love%"

Question: What are the names of the artists who released a song that has the word love in its title, and where are the artists from?
SQL: SELECT T1.artist_name ,  T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.song_name LIKE "%love%"

Question: List the name and gender for all artists who released songs in March.
SQL: SELECT T1.artist_name ,  T1.gender FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.releasedate LIKE "%Mar%"

Question: What are the names and genders of all artists who released songs in the month of March?
SQL: SELECT T1.artist_name ,  T1.gender FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.releasedate LIKE "%Mar%"

Question: List the names of all genres in alphabetical oder, together with its ratings.
SQL: SELECT g_name ,  rating FROM genre ORDER BY g_name

Question: What are the names of all genres in alphabetical order, combined with its ratings?
SQL: SELECT g_name ,  rating FROM genre ORDER BY g_name

Question: Give me a list of the names of all songs ordered by their resolution.
SQL: SELECT song_name FROM song ORDER BY resolution

Question: What are the names of all songs that are ordered by their resolution numbers?
SQL: SELECT song_name FROM song ORDER BY resolution

Question: What are the ids of songs that are available in either mp4 format or have resolution above 720?
SQL: SELECT f_id FROM files WHERE formats  =  "mp4" UNION SELECT f_id FROM song WHERE resolution  >  720

Question: What are the ids of all songs that are available on mp4 or have a higher resolution than 720?
SQL: SELECT f_id FROM files WHERE formats  =  "mp4" UNION SELECT f_id FROM song WHERE resolution  >  720

Question: List the names of all songs that have 4 minute duration or are in English.
SQL: SELECT T2.song_name FROM files AS T1 JOIN song AS T2 ON T1.f_id  =  T2.f_id WHERE T1.duration LIKE "4:%" UNION SELECT song_name FROM song WHERE languages  =  "english"

Question: What are the names of all songs that are approximately 4 minutes long or are in English?
SQL: SELECT T2.song_name FROM files AS T1 JOIN song AS T2 ON T1.f_id  =  T2.f_id WHERE T1.duration LIKE "4:%" UNION SELECT song_name FROM song WHERE languages  =  "english"

Question: What is the language used most often in the songs?
SQL: SELECT languages FROM song GROUP BY languages ORDER BY count(*) DESC LIMIT 1

Question: What are the languages that are used most often in songs?
SQL: SELECT languages FROM song GROUP BY languages ORDER BY count(*) DESC LIMIT 1

Question: What is the language that was used most often in songs with resolution above 500?
SQL: SELECT artist_name FROM song WHERE resolution  >  500 GROUP BY languages ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the artist, for each language, that has the most songs with a higher resolution than 500?
SQL: SELECT artist_name FROM song WHERE resolution  >  500 GROUP BY languages ORDER BY count(*) DESC LIMIT 1

Question: What are the names of artists who are Male and are from UK?
SQL: SELECT artist_name FROM artist WHERE country  =  "UK" AND gender  =  "Male"

Question: What are the names of all male British artists?
SQL: SELECT artist_name FROM artist WHERE country  =  "UK" AND gender  =  "Male"

Question: Find the names of songs whose genre is modern or language is English.
SQL: SELECT song_name FROM song WHERE genre_is  =  "modern" OR languages  =  "english"

Question: What are the names of the songs that are modern or sung in English?
SQL: SELECT song_name FROM song WHERE genre_is  =  "modern" OR languages  =  "english"

Question: Return the names of songs for which format is mp3 and resolution is below 1000.
SQL: SELECT T2.song_name FROM files AS T1 JOIN song AS T2 ON T1.f_id  =  T2.f_id WHERE T1.formats  =  "mp3" INTERSECT SELECT song_name FROM song WHERE resolution  <  1000

Question: What are the names of all songs that are in mp3 format and have a resolution lower than 1000?
SQL: SELECT T2.song_name FROM files AS T1 JOIN song AS T2 ON T1.f_id  =  T2.f_id WHERE T1.formats  =  "mp3" INTERSECT SELECT song_name FROM song WHERE resolution  <  1000

Question: Return the names of singers who are from UK and released an English song.
SQL: SELECT artist_name FROM artist WHERE country  =  "UK" INTERSECT SELECT T1.artist_name FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.languages  =  "english"

Question: What are the names of all singers that are from the UK and released a song in English?
SQL: SELECT artist_name FROM artist WHERE country  =  "UK" INTERSECT SELECT T1.artist_name FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.languages  =  "english"

Question: What are the average rating and resolution of songs that are in Bangla?
SQL: SELECT avg(rating) ,  avg(resolution) FROM song WHERE languages  =  "bangla"

Question: What is the average rating and resolution of all bangla songs?
SQL: SELECT avg(rating) ,  avg(resolution) FROM song WHERE languages  =  "bangla"

Question: What are the maximum and minimum resolution of songs whose duration is 3 minutes?
SQL: SELECT max(T2.resolution) ,  min(T2.resolution) FROM files AS T1 JOIN song AS T2 ON T1.f_id  =  T2.f_id WHERE T1.duration LIKE "3:%"

Question: What is the maximum and minimum resolution of all songs that are approximately 3 minutes long?
SQL: SELECT max(T2.resolution) ,  min(T2.resolution) FROM files AS T1 JOIN song AS T2 ON T1.f_id  =  T2.f_id WHERE T1.duration LIKE "3:%"

Question: What are the maximum duration and resolution of songs grouped and ordered by languages?
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 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