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
What is the last name of the musician that have produced the most number of songs?
assistant
SELECT T2.lastname FROM Performance AS T1 JOIN Band AS T2 ON T1.bandmate = T2.id JOIN Songs AS T3 ON T3.SongId = T1.SongId GROUP BY lastname ORDER BY count(*) DESC LIMIT 1
human
Find all the songs that do not have a back 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 = 'back'
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
How many songs have a shared vocal?
assistant
SELECT count(DISTINCT title) FROM vocals AS T1 JOIN songs AS T2 ON T1.songid = T2.songid WHERE TYPE = 'shared'
human
How many songs have a lead vocal?
assistant
SELECT count(DISTINCT title) FROM vocals AS T1 JOIN songs AS T2 ON T1.songid = T2.songid WHERE TYPE = 'lead'
human
What is the song with the most vocals?
assistant
SELECT title FROM vocals AS T1 JOIN songs AS T2 ON T1.songid = T2.songid GROUP BY T1.songid ORDER BY COUNT( * ) DESC LIMIT 1 ;