human
Schema:
Songs (SongId, Title)
Albums (AId, Title, Year, Label, Type)
Band (Id, Firstname, Lastname)
Instruments (SongId, BandmateId, Instrument)
Performance (SongId, Bandmate, StagePosition)
Tracklists (AlbumId, Position, SongId)
Vocals (SongId, Bandmate, Type)
Please answer with SQL.
assistant
Sure, I understand.
human
Find all the songs whose name contains the word 'the'.
assistant
SELECT title FROM songs WHERE title LIKE '% the %'
human
What is the last name of the musician that has been at the back position the most?
assistant
SELECT T2.lastname FROM Performance AS T1 JOIN Band AS T2 ON T1.bandmate = T2.id WHERE stageposition = 'back' GROUP BY lastname ORDER BY count(*) DESC LIMIT 1
human
Find all the instruments ever used by the musician with last name 'Heilo'?
assistant
SELECT instrument FROM instruments AS T1 JOIN Band AS T2 ON T1.bandmateid = T2.id WHERE T2.lastname = 'Heilo'
human
Who is performing in the back stage position for the song 'Badlands'? Show the first name and the last name.
assistant
SELECT T2.firstname , T2.lastname FROM Performance AS T1 JOIN Band AS T2 ON T1.bandmate = T2.id JOIN Songs AS T3 ON T3.SongId = T1.SongId WHERE T3.Title = 'Badlands' AND T1.StagePosition = 'back'
human
Who is performing in the back stage position for the song 'Der Kapitan'? Show the first name and last name.
assistant
SELECT T2.firstname , T2.lastname FROM Performance AS T1 JOIN Band AS T2 ON T1.bandmate = T2.id JOIN Songs AS T3 ON T3.SongId = T1.SongId WHERE T3.Title = 'Der Kapitan' AND T1.StagePosition = 'back'
human
Find all the stage positions of the musicians with first name 'Solveig
assistant
SELECT DISTINCT T1.stageposition FROM Performance AS T1 JOIN Band AS T2 ON T1.bandmate = T2.id WHERE Firstname = "Solveig" ;