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 county along with the corresponding number of delegates from that county.
assistant
SELECT T1.County_name , COUNT(*) FROM county AS T1 JOIN election AS T2 ON T1.County_id = T2.District GROUP BY T1.County_id
human
Show the name of the party that has at least two records.
assistant
SELECT Party FROM party GROUP BY Party HAVING COUNT(*) >= 2
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
How many distinct governors are there?
assistant
SELECT count(DISTINCT Governor) FROM party
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
Find the distinct years when the governor was named 'Eliot Spitzer'.
assistant
SELECT DISTINCT YEAR FROM party WHERE Governor = "Eliot Spitzer" ;