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
Return the names of all counties sorted by population in ascending order.
assistant
SELECT County_name FROM county ORDER BY Population ASC
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 names of counties that have at least two delegates.
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
human
Show the average population of all counties.
assistant
SELECT avg(Population) FROM county
human
Show the county name and population of all counties.
assistant
SELECT County_name , Population FROM county
human
Count the total number of counties.
assistant
SELECT COUNT( * ) FROM county ;