human
Schema:
county_public_safety (County_ID, Name, Population, Police_officers, Residents_per_officer, Case_burden, Crime_rate, Police_force, Location)
city (City_ID, County_ID, Name, White, Black, Amerindian, Asian, Multiracial, Hispanic)
Please answer with SQL.
assistant
Sure, I understand.
human
Show names of cities and names of counties they are in.
assistant
SELECT T1.Name , T2.Name FROM city AS T1 JOIN county_public_safety AS T2 ON T1.County_ID = T2.County_ID
human
Show the number of cities in counties that have a population more than 20000.
assistant
SELECT count(*) FROM city WHERE county_ID IN (SELECT county_ID FROM county_public_safety WHERE population > 20000)
human
How many counties are there?
assistant
SELECT count(*) FROM county_public_safety
human
Show the name of cities in the county that has the largest number of police officers.
assistant
SELECT name FROM city WHERE county_ID = (SELECT county_ID FROM county_public_safety ORDER BY Police_officers DESC LIMIT 1)
human
List the names of counties in descending order of population.
assistant
SELECT Name FROM county_public_safety ORDER BY Population DESC
human
List the name of the county with the largest population.
assistant
SELECT Name FROM county_public_safety ORDER BY Population DESC LIMIT 1 ;