CREATE TABLE county_public_safety (
    County_ID NUMBER PRIMARY KEY,
    Name TEXT,
    Population NUMBER,
    Police_officers NUMBER,
    Residents_per_officer NUMBER,
    Case_burden NUMBER,
    Crime_rate NUMBER,
    Police_force TEXT,
    Location TEXT
);
CREATE TABLE city (
    City_ID NUMBER PRIMARY KEY,
    County_ID NUMBER,
    Name TEXT,
    White NUMBER,
    Black NUMBER,
    Amerindian NUMBER,
    Asian NUMBER,
    Multiracial NUMBER,
    Hispanic NUMBER,
    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