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
List the names of the city with the top 5 white percentages.
assistant
SELECT Name FROM city ORDER BY White DESC LIMIT 5
human
Show the crime rates of counties in ascending order of number of police officers.
assistant
SELECT Crime_rate FROM county_public_safety ORDER BY Police_officers ASC
human
What are the minimum and maximum crime rate of counties?
assistant
SELECT min(Crime_rate) , max(Crime_rate) FROM county_public_safety
human
Show the names of cities in counties that have a crime rate less than 100.
assistant
SELECT name FROM city WHERE county_id IN (SELECT county_id FROM county_public_safety WHERE Crime_rate < 100)
human
Show white percentages of cities and the crime rates of counties they are in.
assistant
SELECT T1.White , T2.Crime_rate FROM city AS T1 JOIN county_public_safety AS T2 ON T1.County_ID = T2.County_ID
human
Show the crime rate of counties with a city having white percentage more than 90.
assistant
SELECT T2.Crime_rate FROM city AS T1 JOIN county_public_safety AS T2 ON T1.County_ID = T2.County_ID WHERE T1.White > 90 ;