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 that do not have a lead vocal.
assistant
SELECT DISTINCT title FROM vocals AS t1 JOIN songs AS t2 ON t1.songid = t2.songid EXCEPT SELECT t2.title FROM vocals AS t1 JOIN songs AS t2 ON t1.songid = t2.songid WHERE TYPE = 'lead'
human
Find the first name of the band mate that has performed in most songs.
assistant
SELECT t2.firstname FROM Performance AS t1 JOIN Band AS t2 ON t1.bandmate = t2.id JOIN Songs AS T3 ON T3.SongId = T1.SongId GROUP BY firstname ORDER BY count(*) DESC LIMIT 1
human
Find all the songs performed by artist with last name 'Heilo'
assistant
SELECT T3.Title FROM Performance AS T1 JOIN Band AS T2 ON T1.bandmate = T2.id JOIN Songs AS T3 ON T3.SongId = T1.SongId WHERE T2.Lastname = 'Heilo'
human
Which vocal type has the band mate with first name 'Marianne' played the most?
assistant
SELECT TYPE FROM vocals AS T1 JOIN band AS T2 ON T1.bandmate = T2.id WHERE firstname = 'Marianne' GROUP BY TYPE ORDER BY count(*) DESC LIMIT 1
human
Find all the songs whose name contains the word 'the'.
assistant
SELECT title FROM songs WHERE title LIKE '% the %'
human
Find all the songs produced by artists with first name 'Marianne'.
assistant
SELECT T3.Title FROM Performance AS T1 JOIN Band AS T2 ON T1.bandmate = T2.id JOIN Songs AS T3 ON T3.SongId = T1.SongId WHERE T2.firstname = "Marianne" ;