human
Schema:
county (County_Id, County_name, Population, Zip_code)
party (Party_ID, Year, Party, Governor, Lieutenant_Governor, Comptroller, Attorney_General, US_Senate)
election (Election_ID, Counties_Represented, District, Delegate, Party, First_Elected, Committee)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the name of each party and the corresponding number of delegates from that party.
assistant
SELECT T2.Party , COUNT(*) FROM election AS T1 JOIN party AS T2 ON T1.Party = T2.Party_ID GROUP BY T1.Party
human
Who were the comptrollers of the parties associated with the delegates from district 1 or district 2?
assistant
SELECT T2.Comptroller FROM election AS T1 JOIN party AS T2 ON T1.Party = T2.Party_ID WHERE T1.District = 1 OR T1.District = 2
human
Show the name of the party that has the most delegates.
assistant
SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party = T2.Party_ID GROUP BY T1.Party ORDER BY COUNT(*) DESC LIMIT 1
human
What are the names of parties that do not have delegates in election?
assistant
SELECT Party FROM party WHERE Party_ID NOT IN (SELECT Party FROM election)
human
Which committees have delegates from both democratic party and liberal party?
assistant
SELECT T1.Committee FROM election AS T1 JOIN party AS T2 ON T1.Party = T2.Party_ID WHERE T2.Party = 'Democratic' INTERSECT SELECT T1.Committee FROM election AS T1 JOIN party AS T2 ON T1.Party = T2.Party_ID WHERE T2.Party = 'Liberal'
human
Which party has two or more records?
assistant
SELECT Party FROM party GROUP BY Party HAVING COUNT( * ) >= 2 ;