sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the names of the reviewers who have rated a movie more than 3 stars before?
SQL: SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T1.stars  >  3
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the average rating star for each movie that are not reviewed by Brittany Harris.
SQL: SELECT mID ,  avg(stars) FROM Rating WHERE mID NOT IN (SELECT T1.mID FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T2.name  =  "Brittany Harris") GROUP BY mID
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What is the average rating for each movie that has never been reviewed by Brittany Harris?
SQL: SELECT mID ,  avg(stars) FROM Rating WHERE mID NOT IN (SELECT T1.mID FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T2.name  =  "Brittany Harris") GROUP BY mID
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the ids of the movies that are not reviewed by Brittany Harris.
SQL: SELECT mID FROM Rating EXCEPT SELECT T1.mID FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T2.name  =  "Brittany Harris"
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the ids of all moviest hat have not been reviewed by Britanny Harris?
SQL: SELECT mID FROM Rating EXCEPT SELECT T1.mID FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T2.name  =  "Brittany Harris"
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the average rating star for each movie that received at least 2 ratings.
SQL: SELECT mID ,  avg(stars) FROM Rating GROUP BY mID HAVING count(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: For each movie that received more than 3 reviews, what is the average rating?
SQL: SELECT mID ,  avg(stars) FROM Rating GROUP BY mID HAVING count(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: find the ids of reviewers who did not give 4 star.
SQL: SELECT rID FROM Rating EXCEPT SELECT rID FROM Rating WHERE stars  =  4
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the ids of all reviewers who did not give 4 stars?
SQL: SELECT rID FROM Rating EXCEPT SELECT rID FROM Rating WHERE stars  =  4
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Find the ids of reviewers who didn't only give 4 star.
SQL: SELECT rID FROM Rating WHERE stars != 4
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the ids of all reviewers who have not given 4 stars at least once?
SQL: SELECT rID FROM Rating WHERE stars != 4
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are names of the movies that are either made after 2000 or reviewed by Brittany Harris?
SQL: SELECT DISTINCT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID WHERE T3.name  =  'Brittany Harris' OR T2.year  >  2000
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the names of all movies that were made after 2000 or reviewed by Brittany Harris?
SQL: SELECT DISTINCT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID JOIN Reviewer AS T3 ON T1.rID  =  T3.rID WHERE T3.name  =  'Brittany Harris' OR T2.year  >  2000
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are names of the movies that are either made before 1980 or directed by James Cameron?
SQL: SELECT title FROM Movie WHERE director  =  "James Cameron" OR YEAR   <  1980
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the names of all movies made before 1980 or had James Cameron as the director?
SQL: SELECT title FROM Movie WHERE director  =  "James Cameron" OR YEAR   <  1980
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the names of reviewers who had rated 3 star and 4 star?
SQL: SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T1.stars  =  3 INTERSECT SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T1.stars  =  4
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the names of all reviewers that have given 3 or 4 stars for reviews?
SQL: SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T1.stars  =  3 INTERSECT SELECT T2.name FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID WHERE T1.stars  =  4
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the names of movies that get 3 star and 4 star?
SQL: SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T1.stars  =  3 INTERSECT SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T1.stars  =  4
-- Language PostgreSQL
        -- Tables: 
             -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text']]

 -- Table: reviewer 
columns : [['reviewer id', 'number'], ['name', 'text']]

 -- Table: rating 
columns : [['reviewer id', 'number'], ['movie id', 'number'], ['rating stars', 'number'], ['rating date', 'time']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the names of all movies that received 3 or 4 stars?
SQL: SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T1.stars  =  3 INTERSECT SELECT T2.title FROM Rating AS T1 JOIN Movie AS T2 ON T1.mID  =  T2.mID WHERE T1.stars  =  4
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: How many counties are there?
SQL: SELECT count(*) FROM county_public_safety
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Count the number of countries.
SQL: SELECT count(*) FROM county_public_safety
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: List the names of counties in descending order of population.
SQL: SELECT Name FROM county_public_safety ORDER BY Population DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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"
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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"
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the minimum and maximum crime rate of counties?
SQL: SELECT min(Crime_rate) ,  max(Crime_rate) FROM county_public_safety
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Return the minimum and maximum crime rates across all counties.
SQL: SELECT min(Crime_rate) ,  max(Crime_rate) FROM county_public_safety
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the names of cities in ascending alphabetical order?
SQL: SELECT Name FROM city ORDER BY Name ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Return the names of cities, ordered alphabetically.
SQL: SELECT Name FROM city ORDER BY Name ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the percentage of hispanics in cities with the black percentage higher than 10?
SQL: SELECT Hispanic FROM city WHERE Black  >  10
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Return the hispanic percentage for cities in which the black percentage is greater than 10.
SQL: SELECT Hispanic FROM city WHERE Black  >  10
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: List the name of the county with the largest population.
SQL: SELECT Name FROM county_public_safety ORDER BY Population DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: List the names of the city with the top 5 white percentages.
SQL: SELECT Name FROM city ORDER BY White DESC LIMIT 5
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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)
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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)
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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)
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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)
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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
-- Language PostgreSQL
        -- Tables: 
             -- Table: county public safety 
columns : [['county id', 'number'], ['name', 'text'], ['population', 'number'], ['police officers', 'number'], ['residents per officer', 'number'], ['case burden', 'number'], ['crime rate', 'number'], ['police force', 'text'], ['location', 'text']]

 -- Table: city 
columns : [['city id', 'number'], ['county id', 'number'], ['name', 'text'], ['white', 'number'], ['black', 'number'], ['amerindian', 'number'], ['asian', 'number'], ['multiracial', 'number'], ['hispanic', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



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