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 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 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
human
What are the names of cities in ascending alphabetical order?
assistant
SELECT Name FROM city ORDER BY Name ASC
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
What are the percentage of hispanics in cities with the black percentage higher than 10?
assistant
SELECT Hispanic FROM city WHERE Black > 10
human
What are the names of the five cities with the greatest proportion of white people?
assistant
SELECT Name FROM city ORDER BY White DESC LIMIT 5 ;