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
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
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
In which distinct years was the governor 'Eliot Spitzer'?
assistant
SELECT DISTINCT YEAR FROM party WHERE Governor = 'Eliot Spitzer'
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
Which people severed as governor most frequently?
assistant
SELECT Governor FROM party GROUP BY Governor ORDER BY COUNT( * ) DESC LIMIT 1 ;