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 lieutenant governor and comptroller from the democratic party.
assistant
SELECT Lieutenant_Governor , Comptroller FROM party WHERE Party = 'Democratic'
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
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
How many distinct governors are there?
assistant
SELECT count(DISTINCT Governor) FROM 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
Which people severed as comptroller most frequently? Give me the name of the person and the frequency count.
assistant
SELECT Comptroller , COUNT( * ) FROM party GROUP BY Comptroller ORDER BY COUNT( * ) DESC LIMIT 1 ;