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
What are the names of the county that the delegates on 'Appropriations' committee belong to?
assistant
SELECT T1.County_name FROM county AS T1 JOIN election AS T2 ON T1.County_id = T2.District WHERE T2.Committee = 'Appropriations'
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
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 delegates and the names of county they belong to.
assistant
SELECT T2.Delegate , T1.County_name FROM county AS T1 JOIN election AS T2 ON T1.County_id = T2.District
human
Which counties have two or more delegates? Give me the county names.
assistant
SELECT T1.County_name FROM county AS T1 JOIN election AS T2 ON T1.County_id = T2.District GROUP BY T1.County_id HAVING COUNT( * ) >= 2 ;