human
Schema:
artist (Artist_ID, Artist, Age, Famous_Title, Famous_Release_date)
volume (Volume_ID, Volume_Issue, Issue_Date, Weeks_on_Top, Song, Artist_ID)
music_festival (ID, Music_Festival, Date_of_ceremony, Category, Volume, Result)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the maximum and minimum week on top of all volumes?
assistant
SELECT max(Weeks_on_Top) , min(Weeks_on_Top) FROM volume
human
Please list all songs in volumes in ascending alphabetical order.
assistant
SELECT Song FROM volume ORDER BY Song
human
Show the famous titles of the artists with both volumes that lasted more than 2 weeks on top and volumes that lasted less than 2 weeks on top.
assistant
SELECT T1.Famous_Title FROM artist AS T1 JOIN volume AS T2 ON T1.Artist_ID = T2.Artist_ID WHERE T2.Weeks_on_Top > 2 INTERSECT SELECT T1.Famous_Title FROM artist AS T1 JOIN volume AS T2 ON T1.Artist_ID = T2.Artist_ID WHERE T2.Weeks_on_Top < 2
human
What are the famous title of the artists associated with volumes with more than 2 weeks on top?
assistant
SELECT T1.Famous_Title FROM artist AS T1 JOIN volume AS T2 ON T1.Artist_ID = T2.Artist_ID WHERE T2.Weeks_on_Top > 2
human
What is the song in the volume with the maximum weeks on top?
assistant
SELECT Song FROM volume ORDER BY Weeks_on_Top DESC LIMIT 1
human
What are the songs in volumes with more than 1 week on top?
assistant
SELECT Song FROM volume WHERE Weeks_on_Top > 1 ;