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 songs in volumes with more than 1 week on top?
assistant
SELECT Song FROM volume WHERE Weeks_on_Top > 1
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 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
Please show the date of ceremony of the volumes that last more than 2 weeks on top.
assistant
SELECT T1.Date_of_ceremony FROM music_festival AS T1 JOIN volume AS T2 ON T1.Volume = T2.Volume_ID WHERE T2.Weeks_on_Top > 2
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
Return the issue date of the volume that has spent the fewest weeks on top.
assistant
SELECT Issue_Date FROM volume ORDER BY Weeks_on_Top ASC LIMIT 1 ;