Database: county_public_safety

CREATE TABLE "county_public_safety" (
"County_ID" int,
"Name" text,
"Population" int,
"Police_officers" int,
"Residents_per_officer" int,
"Case_burden" int,
"Crime_rate" real,
"Police_force" text,
"Location" text,
PRIMARY KEY ("County_ID")
);
CREATE TABLE "city" (
"City_ID" int,
"County_ID" int,
"Name" text,
"White" real,
"Black" real,
"Amerindian" real,
"Asian" real,
"Multiracial" real,
"Hispanic" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY ("County_ID") REFERENCES "county_public_safety"("County_ID")
);

Question: How many counties are there?
SQL: SELECT count(*) FROM county_public_safety

Question: Count the number of countries.
SQL: SELECT count(*) FROM county_public_safety

Question: List the names of counties in descending order of population.
SQL: SELECT Name FROM county_public_safety ORDER BY Population DESC

Question: What are the names of the counties of public safety, ordered by population descending?
SQL: SELECT Name FROM county_public_safety ORDER BY Population DESC

Question: List the distinct police forces of counties whose location is not on east side.
SQL: SELECT DISTINCT Police_force FROM county_public_safety WHERE LOCATION != "East"

Question: What are the different police forces of counties that are not located in the East?
SQL: SELECT DISTINCT Police_force FROM county_public_safety WHERE LOCATION != "East"

Question: What are the minimum and maximum crime rate of counties?
SQL: SELECT min(Crime_rate) ,  max(Crime_rate) FROM county_public_safety

Question: Return the minimum and maximum crime rates across all counties.
SQL: SELECT min(Crime_rate) ,  max(Crime_rate) FROM county_public_safety

Question: Show the crime rates of counties in ascending order of number of police officers.
SQL: SELECT Crime_rate FROM county_public_safety ORDER BY Police_officers ASC

Question: What are the crime rates of counties sorted by number of offices ascending?
SQL: SELECT Crime_rate FROM county_public_safety ORDER BY Police_officers ASC

Question: What are the names of cities in ascending alphabetical order?
SQL: SELECT Name FROM city ORDER BY Name ASC

Question: Return the names of cities, ordered alphabetically.
SQL: SELECT Name FROM city ORDER BY Name ASC

Question: What are the percentage of hispanics in cities with the black percentage higher than 10?
SQL: SELECT Hispanic FROM city WHERE Black  >  10

Question: Return the hispanic percentage for cities in which the black percentage is greater than 10.
SQL: SELECT Hispanic FROM city WHERE Black  >  10

Question: List the name of the county with the largest population.
SQL: SELECT Name FROM county_public_safety ORDER BY Population DESC LIMIT 1

Question: What is the name of the county with the greatest population?
SQL: SELECT Name FROM county_public_safety ORDER BY Population DESC LIMIT 1

Question: List the names of the city with the top 5 white percentages.
SQL: SELECT Name FROM city ORDER BY White DESC LIMIT 5

Question: What are the names of the five cities with the greatest proportion of white people?
SQL: SELECT Name FROM city ORDER BY White DESC LIMIT 5

Question: Show names of cities and names of counties they are in.
SQL: SELECT T1.Name ,  T2.Name FROM city AS T1 JOIN county_public_safety AS T2 ON T1.County_ID  =  T2.County_ID

Question: What are the names of cities, as well as the names of the counties they correspond to?
SQL: SELECT T1.Name ,  T2.Name FROM city AS T1 JOIN county_public_safety AS T2 ON T1.County_ID  =  T2.County_ID

Question: Show white percentages of cities and the crime rates of counties they are in.
SQL: SELECT T1.White ,  T2.Crime_rate FROM city AS T1 JOIN county_public_safety AS T2 ON T1.County_ID  =  T2.County_ID

Question: What are the white percentages of cities, and the corresponding crime rates of the counties they correspond to?
SQL: SELECT T1.White ,  T2.Crime_rate FROM city AS T1 JOIN county_public_safety AS T2 ON T1.County_ID  =  T2.County_ID

Question: Show the name of cities in the county that has the largest number of police officers.
SQL: SELECT name FROM city WHERE county_ID  =  (SELECT county_ID FROM county_public_safety ORDER BY Police_officers DESC LIMIT 1)

Question: What are the names of cities that are in the county with the most police officers?
SQL: SELECT name FROM city WHERE county_ID  =  (SELECT county_ID FROM county_public_safety ORDER BY Police_officers DESC LIMIT 1)

Question: Show the number of cities in counties that have a population more than 20000.
SQL: SELECT count(*) FROM city WHERE county_ID IN (SELECT county_ID FROM county_public_safety WHERE population  >  20000)

Question: How many cities are in counties that have populations of over 20000?
SQL: SELECT count(*) FROM city WHERE county_ID IN (SELECT county_ID FROM county_public_safety WHERE population  >  20000)

Question: Show the crime rate of counties with a city having white percentage more than 90.
SQL: 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

Question: What are the crime rates of counties that contain cities that have white percentages of over 90?
SQL: 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

Question: Please show the police forces and the number of counties with each police force.
SQL: SELECT Police_force ,  COUNT(*) FROM county_public_safety GROUP BY Police_force

Question: How many counties correspond to each police force?
SQL: SELECT Police_force ,  COUNT(*) FROM county_public_safety GROUP BY Police_force

Question: What is the location shared by most counties?
SQL: SELECT LOCATION FROM county_public_safety GROUP BY LOCATION ORDER BY COUNT(*) DESC LIMIT 1

Question: Which location has the most corresponding counties?
SQL: SELECT LOCATION FROM county_public_safety GROUP BY LOCATION ORDER BY COUNT(*) DESC LIMIT 1

Question: List the names of counties that do not have any cities.
SQL: SELECT Name FROM county_public_safety WHERE County_ID NOT IN (SELECT County_ID FROM city)

Question: What are the names of counties that do not contain any cities?
SQL: SELECT Name FROM county_public_safety WHERE County_ID NOT IN (SELECT County_ID FROM city)

Question: Show the police force shared by counties with location on the east and west.
SQL: SELECT Police_force FROM county_public_safety WHERE LOCATION  =  "East" INTERSECT SELECT Police_force FROM county_public_safety WHERE LOCATION  =  "West"

Question: Which police forces operate in both counties that are located in the East and in the West?
SQL: SELECT Police_force FROM county_public_safety WHERE LOCATION  =  "East" INTERSECT SELECT Police_force FROM county_public_safety WHERE LOCATION  =  "West"

Question: Show the names of cities in counties that have a crime rate less than 100.
SQL: SELECT name FROM city WHERE county_id IN (SELECT county_id FROM county_public_safety WHERE Crime_rate  <  100)

Question: What are the names of cities that are in counties that have a crime rate below 100?
SQL: SELECT name FROM city WHERE county_id IN (SELECT county_id FROM county_public_safety WHERE Crime_rate  <  100)

Question: Show the case burden of counties in descending order of population.
SQL: SELECT Case_burden FROM county_public_safety ORDER BY Population DESC

Question: What are the case burdens of counties, ordered descending by population?
SQL: SELECT Case_burden FROM county_public_safety ORDER BY Population DESC