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 all the distinct districts for elections.
assistant
SELECT DISTINCT District FROM election
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
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
Who were the governors of the parties associated with delegates from district 1?
assistant
SELECT T2.Governor FROM election AS T1 JOIN party AS T2 ON T1.Party = T2.Party_ID WHERE T1.District = 1
human
Show the people that have been governor the most times.
assistant
SELECT Governor FROM party GROUP BY Governor ORDER BY COUNT(*) DESC LIMIT 1
human
Count the number of distinct governors.
assistant
SELECT COUNT( DISTINCT Governor ) FROM party ;