sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: list 
columns : [['last name', 'text'], ['first name', 'text'], ['grade', 'number'], ['class room', 'number']]

 -- Table: teachers 
columns : [['last name', 'text'], ['first name', 'text'], ['class room', '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 teacher who teaches the largest number of students.
SQL: SELECT T2.firstname ,  T2.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom GROUP BY T2.firstname ,  T2.lastname ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: list 
columns : [['last name', 'text'], ['first name', 'text'], ['grade', 'number'], ['class room', 'number']]

 -- Table: teachers 
columns : [['last name', 'text'], ['first name', 'text'], ['class room', '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 teacher teaches the most students? Give me the first name and last name of the teacher.
SQL: SELECT T2.firstname ,  T2.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom GROUP BY T2.firstname ,  T2.lastname ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: list 
columns : [['last name', 'text'], ['first name', 'text'], ['grade', 'number'], ['class room', 'number']]

 -- Table: teachers 
columns : [['last name', 'text'], ['first name', 'text'], ['class room', '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 number of students in one classroom.
SQL: SELECT count(*) ,  classroom FROM list GROUP BY classroom
-- Language PostgreSQL
        -- Tables: 
             -- Table: list 
columns : [['last name', 'text'], ['first name', 'text'], ['grade', 'number'], ['class room', 'number']]

 -- Table: teachers 
columns : [['last name', 'text'], ['first name', 'text'], ['class room', '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 students does one classroom have?
SQL: SELECT count(*) ,  classroom FROM list GROUP BY classroom
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 companies are headquartered in the US?
SQL: SELECT count(*) FROM company WHERE Headquarters  =  'USA'
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 companies by ascending number of sales.
SQL: SELECT Name FROM company ORDER BY Sales_in_Billion ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 headquarters and industries of all companies?
SQL: SELECT Headquarters ,  Industry FROM company
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 names of companies in the banking or retailing industry?
SQL: SELECT Name FROM company WHERE Industry  =  "Banking" OR Industry  =  "Retailing"
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 maximum and minimum market value of companies?
SQL: SELECT max(Market_Value_in_Billion) ,  min(Market_Value_in_Billion) FROM company
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 headquarter of the company with the largest sales?
SQL: SELECT Headquarters FROM company ORDER BY Sales_in_Billion DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 different headquarters and number of companies at each headquarter.
SQL: SELECT Headquarters ,  COUNT(*) FROM company GROUP BY Headquarters
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 most common headquarter for companies.
SQL: SELECT Headquarters FROM company GROUP BY Headquarters ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 headquarters that have at least two companies.
SQL: SELECT Headquarters FROM company GROUP BY Headquarters HAVING COUNT(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 headquarters that have both companies in banking industry and companies in oil and gas industry.
SQL: SELECT Headquarters FROM company WHERE Industry  =  "Banking" INTERSECT SELECT Headquarters FROM company WHERE Industry  =  "Oil and gas"
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 names of companies and of employees.
SQL: SELECT T3.Name ,  T2.Name FROM employment AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID JOIN company AS T3 ON T1.Company_ID  =  T3.Company_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 companies and that of employees in descending order of number of years working for that employee.
SQL: SELECT T3.Name ,  T2.Name FROM employment AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID JOIN company AS T3 ON T1.Company_ID  =  T3.Company_ID ORDER BY T1.Year_working
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 names of employees that work for companies with sales bigger than 200.
SQL: SELECT T2.Name FROM employment AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID JOIN company AS T3 ON T1.Company_ID  =  T3.Company_ID WHERE T3.Sales_in_Billion  >  200
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 names of companies and the number of employees they have
SQL: SELECT T3.Name ,  COUNT(*) FROM employment AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID JOIN company AS T3 ON T1.Company_ID  =  T3.Company_ID GROUP BY T3.Name
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 people that are not employed by any company
SQL: SELECT Name FROM people WHERE People_ID NOT IN (SELECT People_ID FROM employment)
-- Language PostgreSQL
        -- Tables: 
             -- Table: people 
columns : [['people id', 'number'], ['age', 'number'], ['name', 'text'], ['nationality', 'text'], ['graduation college', 'text']]

 -- Table: company 
columns : [['company id', 'number'], ['name', 'text'], ['headquarters', 'text'], ['industry', 'text'], ['sales in billion', 'number'], ['profits in billion', 'number'], ['assets in billion', 'number'], ['market value in billion', 'number']]

 -- Table: employment 
columns : [['company id', 'number'], ['people id', 'number'], ['year working', '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 companies with more than 200 sales in the descending order of sales and profits.
SQL: SELECT name FROM company WHERE Sales_in_Billion  >  200 ORDER BY Sales_in_Billion ,  Profits_in_Billion DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 film are there?
SQL: SELECT count(*) FROM film
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 films.
SQL: SELECT count(*) FROM film
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 director of all films.
SQL: SELECT DISTINCT Director FROM film
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 film Directors?
SQL: SELECT DISTINCT Director FROM film
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 average ticket sales gross in dollars of films?
SQL: SELECT avg(Gross_in_dollar) FROM film
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 average gross sales in dollars across all films.
SQL: SELECT avg(Gross_in_dollar) FROM film
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 low and high estimates of film markets?
SQL: SELECT Low_Estimate ,  High_Estimate FROM film_market_estimation
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 low and high estimates for all film markets.
SQL: SELECT Low_Estimate ,  High_Estimate FROM film_market_estimation
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 types of film market estimations in year 1995?
SQL: SELECT TYPE FROM film_market_estimation WHERE YEAR  =  1995
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 types of film market estimations in 1995.
SQL: SELECT TYPE FROM film_market_estimation WHERE YEAR  =  1995
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 maximum and minimum number of cities in all markets.
SQL: SELECT max(Number_cities) ,  min(Number_cities) FROM market
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 and minimum number of cities across all markets.
SQL: SELECT max(Number_cities) ,  min(Number_cities) FROM market
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 markets have number of cities smaller than 300?
SQL: SELECT count(*) FROM market WHERE Number_cities  <  300
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 markets that have a number of cities lower than 300.
SQL: SELECT count(*) FROM market WHERE Number_cities  <  300
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 countries of markets in ascending alphabetical order.
SQL: SELECT Country FROM market ORDER BY Country ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 countries for each market, ordered alphabetically?
SQL: SELECT Country FROM market ORDER BY Country ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 countries of markets in descending order of number of cities.
SQL: SELECT Country FROM market ORDER BY Number_cities DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 countries for each market ordered by decreasing number of cities?
SQL: SELECT Country FROM market ORDER BY Number_cities DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 titles of films and the types of market estimations.
SQL: SELECT T1.Title ,  T2.Type FROM film AS T1 JOIN film_market_estimation AS T2 ON T1.Film_ID  =  T2.Film_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 titles of films and corresponding types of market estimations?
SQL: SELECT T1.Title ,  T2.Type FROM film AS T1 JOIN film_market_estimation AS T2 ON T1.Film_ID  =  T2.Film_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 distinct director of films with market estimation in the year of 1995.
SQL: SELECT DISTINCT T1.Director FROM film AS T1 JOIN film_market_estimation AS T2 ON T1.Film_ID  =  T2.Film_ID WHERE T2.Year  =  1995
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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: Who are the different directors of films which had market estimation in 1995?
SQL: SELECT DISTINCT T1.Director FROM film AS T1 JOIN film_market_estimation AS T2 ON T1.Film_ID  =  T2.Film_ID WHERE T2.Year  =  1995
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 average number of cities of markets with low film market estimate bigger than 10000?
SQL: SELECT avg(T2.Number_cities) FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID WHERE T1.Low_Estimate  >  10000
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 average number of cities within markets that had a low market estimation larger than 10000?
SQL: SELECT avg(T2.Number_cities) FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID WHERE T1.Low_Estimate  >  10000
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 list the countries and years of film market estimations.
SQL: SELECT T2.Country ,  T1.Year FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 countries of markets and their corresponding years of market estimation?
SQL: SELECT T2.Country ,  T1.Year FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 list the years of film market estimations when the market is in country "Japan" in descending order.
SQL: SELECT T1.Year FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID WHERE T2.Country  =  "Japan" ORDER BY T1.Year DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: film 
columns : [['film id', 'number'], ['title', 'text'], ['studio', 'text'], ['director', 'text'], ['gross in dollar', 'number']]

 -- Table: market 
columns : [['market id', 'number'], ['country', 'text'], ['number cities', 'number']]

 -- Table: film market estimation 
columns : [['estimation id', 'number'], ['low estimate', 'number'], ['high estimate', 'number'], ['film id', 'number'], ['type', 'text'], ['market id', 'number'], ['year', '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 years of film market estimation for the market of Japan, ordered by year descending?
SQL: SELECT T1.Year FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID WHERE T2.Country  =  "Japan" ORDER BY T1.Year DESC