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
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
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
How many distinct delegates are from counties with population larger than 50000?
assistant
SELECT count(DISTINCT T2.Delegate) FROM county AS T1 JOIN election AS T2 ON T1.County_id = T2.District WHERE T1.Population > 50000
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
Which delegates are from counties with population smaller than 100000?
assistant
SELECT T2.Delegate FROM county AS T1 JOIN election AS T2 ON T1.County_id = T2.District WHERE T1.Population < 100000
human
Which party has the largest number of 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 ;