sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 all people names in the order of their date of birth from old to young.
SQL: SELECT name FROM people ORDER BY date_of_birth
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 all people, ordered by their date of birth?
SQL: SELECT name FROM people ORDER BY date_of_birth
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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: Find the average height and weight for all males (sex is M).
SQL: SELECT avg(height) ,  avg(weight) FROM people WHERE sex  =  'M'
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 average height and weight across males (sex is M)?
SQL: SELECT avg(height) ,  avg(weight) FROM people WHERE sex  =  'M'
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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: find the names of people who are taller than 200 or lower than 190.
SQL: SELECT name FROM people WHERE height  >  200 OR height  <  190
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 people who have a height greater than 200 or less than 190?
SQL: SELECT name FROM people WHERE height  >  200 OR height  <  190
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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: Find the average and minimum weight for each gender.
SQL: SELECT avg(weight) ,  min(weight) ,  sex FROM people GROUP BY sex
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 average and minimum weights for people of each sex?
SQL: SELECT avg(weight) ,  min(weight) ,  sex FROM people GROUP BY sex
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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: Find the name and gender of the candidate who got the highest support rate.
SQL: SELECT t1.name ,  t1.sex FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id ORDER BY t2.support_rate DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 and sex of the candidate with the highest support rate?
SQL: SELECT t1.name ,  t1.sex FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id ORDER BY t2.support_rate DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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: Find the name of the candidates whose oppose percentage is the lowest for each sex.
SQL: SELECT t1.name ,  t1.sex ,  min(oppose_rate) FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id GROUP BY t1.sex
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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: For each sex, what is the name and sex of the candidate with the oppose rate for their sex?
SQL: SELECT t1.name ,  t1.sex ,  min(oppose_rate) FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id GROUP BY t1.sex
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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: which gender got the highest average uncertain ratio.
SQL: SELECT t1.sex FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id GROUP BY t1.sex ORDER BY avg(t2.unsure_rate) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 sex of the candidate who had the highest unsure rate?
SQL: SELECT t1.sex FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id GROUP BY t1.sex ORDER BY avg(t2.unsure_rate) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 people who did not participate in the candidate election.
SQL: SELECT name FROM people WHERE people_id NOT IN (SELECT people_id FROM candidate)
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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: Give the names of people who did not participate in the candidate election.
SQL: SELECT name FROM people WHERE people_id NOT IN (SELECT people_id FROM candidate)
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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: Find the names of the candidates whose support percentage is lower than their oppose rate.
SQL: SELECT t1.name FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id WHERE t2.support_rate  <  t2.oppose_rate
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 candidates who have a lower support rate than oppose rate?
SQL: SELECT t1.name FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id WHERE t2.support_rate  <  t2.oppose_rate
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 people are there whose weight is higher than 85 for each gender?
SQL: SELECT count(*) ,  sex FROM people WHERE weight  >  85 GROUP BY sex
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 people of each sex who have a weight higher than 85.
SQL: SELECT count(*) ,  sex FROM people WHERE weight  >  85 GROUP BY sex
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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: find the highest support percentage, lowest consider rate and oppose rate of all candidates.
SQL: SELECT max(support_rate) ,  min(consider_rate) ,  min(oppose_rate) FROM candidate
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 maximum support rate, minimum consider rate, and minimum oppose rate across all candidates?
SQL: SELECT max(support_rate) ,  min(consider_rate) ,  min(oppose_rate) FROM candidate
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 all female (sex is F) candidate names in the alphabetical order.
SQL: SELECT t1.name FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id WHERE t1.sex  =  'F' ORDER BY t1.name
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 all female candidates in alphabetical order (sex is F)?
SQL: SELECT t1.name FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id WHERE t1.sex  =  'F' ORDER BY t1.name
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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: find the name of people whose height is lower than the average.
SQL: SELECT name FROM people WHERE height  <  (SELECT avg(height) FROM people)
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 people who are shorter than average?
SQL: SELECT name FROM people WHERE height  <  (SELECT avg(height) FROM people)
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 all info about all people.
SQL: SELECT * FROM people
-- Language PostgreSQL
        -- Tables: 
             -- Table: candidate 
columns : [['candidate id', 'number'], ['people id', 'number'], ['poll source', 'text'], ['date', 'text'], ['support rate', 'number'], ['consider rate', 'number'], ['oppose rate', 'number'], ['unsure rate', 'number']]

 -- Table: people 
columns : [['people id', 'number'], ['sex', 'text'], ['name', 'text'], ['date of birth', 'text'], ['height', 'number'], ['weight', '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 all the information about all people?
SQL: SELECT * FROM people
-- 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 titles of all movies directed by steven spielberg.
SQL: SELECT title FROM Movie WHERE director = 'Steven Spielberg'
-- 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 directed by Steven Spielberg?
SQL: SELECT title FROM Movie WHERE director = 'Steven Spielberg'
-- 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 name of the movie produced after 2000 and directed by James Cameron?
SQL: SELECT title FROM Movie WHERE director = 'James Cameron' AND 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 titles of all movies that James Cameron directed after 2000?
SQL: SELECT title FROM Movie WHERE director = 'James Cameron' AND 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: How many movies were made before 2000?
SQL: SELECT count(*) FROM Movie WHERE 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: Who is the director of movie Avatar?
SQL: SELECT director FROM Movie WHERE title  = 'Avatar'
-- 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: Who directed Avatar?
SQL: SELECT director FROM Movie WHERE title  = 'Avatar'
-- 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: How many reviewers listed?
SQL: SELECT count(*) FROM Reviewer
-- 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: How many reviewers are there?
SQL: SELECT count(*) FROM Reviewer
-- 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 id of the reviewer whose name has substring “Mike”?
SQL: SELECT rID FROM Reviewer WHERE name LIKE "%Mike%"
-- 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 id of the reviewer whose name includes the word "Mike"?
SQL: SELECT rID FROM Reviewer WHERE name LIKE "%Mike%"
-- 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 reviewer id of Daniel Lewis?
SQL: SELECT rID FROM Reviewer WHERE name  =  "Daniel Lewis"
-- 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 id of the reviewer named Daniel Lewis?
SQL: SELECT rID FROM Reviewer WHERE name  =  "Daniel Lewis"
-- 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 total number of ratings that has more than 3 stars?
SQL: SELECT count(*) FROM Rating WHERE 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: How many movie ratings have more than 3 stars?
SQL: SELECT count(*) FROM Rating WHERE 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: What is the lowest and highest rating star?
SQL: SELECT max(stars) ,  min(stars) FROM Rating
-- 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 maximum and mininum number of stars a rating can receive?
SQL: SELECT max(stars) ,  min(stars) FROM Rating
-- 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 all years that have a movie that received a rating of 4 or 5, and sort them in increasing order of year.
SQL: SELECT DISTINCT YEAR FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID  =  T2.mID WHERE T2.stars  >=  4 ORDER BY T1.year
-- 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: In what years did a movie receive a 4 or 5 star rating, and list the years from oldest to most recently?
SQL: SELECT DISTINCT YEAR FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID  =  T2.mID WHERE T2.stars  >=  4 ORDER BY T1.year
-- 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 directors who directed movies with 5 star rating? Also return the title of these movies.
SQL: SELECT T1.director ,  T1.title FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID  =  T2.mID WHERE T2.stars  =  5
-- 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 directors who created a movie with a 5 star rating, and what was the name of those movies?
SQL: SELECT T1.director ,  T1.title FROM Movie AS T1 JOIN Rating AS T2 ON T1.mID  =  T2.mID WHERE T2.stars  =  5
-- 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 star for each reviewer?
SQL: SELECT T2.name ,  avg(T1.stars) FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID GROUP BY T2.name
-- 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 number of stars that each reviewer awards for a movie?
SQL: SELECT T2.name ,  avg(T1.stars) FROM Rating AS T1 JOIN Reviewer AS T2 ON T1.rID  =  T2.rID GROUP BY T2.name
-- 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 titles of all movies that have no ratings.
SQL: SELECT title FROM Movie WHERE mID NOT IN (SELECT mID FROM Rating)
-- 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 titles of all movies that have not been rated?
SQL: SELECT title FROM Movie WHERE mID NOT IN (SELECT mID FROM Rating)
-- 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 names of all reviewers who have ratings with a NULL value for the date.
SQL: SELECT DISTINCT name FROM Reviewer AS T1 JOIN Rating AS T2 ON T1.rID  =  T2.rID WHERE ratingDate  =  "null"