sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 heads of the departments are older than 56 ?
SQL: SELECT count(*) FROM head WHERE age  >  56
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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



Question: List the name, born state and age of the heads of departments ordered by age.
SQL: SELECT name ,  born_state ,  age FROM head ORDER BY age
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 creation year, name and budget of each department.
SQL: SELECT creation ,  name ,  budget_in_billions FROM department
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 budget of the departments?
SQL: SELECT max(budget_in_billions) ,  min(budget_in_billions) FROM department
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 employees of the departments whose rank is between 10 and 15?
SQL: SELECT avg(num_employees) FROM department WHERE ranking BETWEEN 10 AND 15
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 heads who are born outside the California state?
SQL: SELECT name FROM head WHERE born_state != 'California'
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 distinct creation years of the departments managed by a secretary born in state 'Alabama'?
SQL: SELECT DISTINCT T1.creation FROM department AS T1 JOIN management AS T2 ON T1.department_id  =  T2.department_id JOIN head AS T3 ON T2.head_id  =  T3.head_id WHERE T3.born_state  =  'Alabama'
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 states where at least 3 heads were born?
SQL: SELECT born_state FROM head GROUP BY born_state HAVING count(*)  >=  3
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 which year were most departments established?
SQL: SELECT creation FROM department GROUP BY creation ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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



Question: Show the name and number of employees for the departments managed by heads whose temporary acting value is 'Yes'?
SQL: SELECT T1.name ,  T1.num_employees FROM department AS T1 JOIN management AS T2 ON T1.department_id  =  T2.department_id WHERE T2.temporary_acting  =  'Yes'
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 acting statuses are there?
SQL: SELECT count(DISTINCT temporary_acting) FROM management
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 departments are led by heads who are not mentioned?
SQL: SELECT count(*) FROM department WHERE department_id NOT IN (SELECT department_id FROM management);
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 distinct ages of the heads who are acting?
SQL: SELECT DISTINCT T1.age FROM management AS T2 JOIN head AS T1 ON T1.head_id  =  T2.head_id WHERE T2.temporary_acting  =  'Yes'
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 states where both the secretary of 'Treasury' department and the secretary of 'Homeland Security' were born.
SQL: SELECT T3.born_state FROM department AS T1 JOIN management AS T2 ON T1.department_id  =  T2.department_id JOIN head AS T3 ON T2.head_id  =  T3.head_id WHERE T1.name  =  'Treasury' INTERSECT SELECT T3.born_state FROM department AS T1 JOIN management AS T2 ON T1.department_id  =  T2.department_id JOIN head AS T3 ON T2.head_id  =  T3.head_id WHERE T1.name  =  'Homeland Security'
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 department has more than 1 head at a time? List the id, name and the number of heads.
SQL: SELECT T1.department_id ,  T1.name ,  count(*) FROM management AS T2 JOIN department AS T1 ON T1.department_id  =  T2.department_id GROUP BY T1.department_id HAVING count(*)  >  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: department 
columns : [['department id', 'number'], ['name', 'text'], ['creation', 'text'], ['ranking', 'number'], ['budget in billions', 'number'], ['num employees', 'number']]

 -- Table: head 
columns : [['head id', 'number'], ['name', 'text'], ['born state', 'text'], ['age', 'number']]

 -- Table: management 
columns : [['department id', 'number'], ['head id', 'number'], ['temporary acting', 'text']]

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 head's name has the substring 'Ha'? List the id and name.
SQL: SELECT head_id ,  name FROM head WHERE name LIKE '%Ha%'
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 farms are there?
SQL: SELECT count(*) FROM farm
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 farms.
SQL: SELECT count(*) FROM farm
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 total number of horses on farms in ascending order.
SQL: SELECT Total_Horses FROM farm ORDER BY Total_Horses ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 total horses record for each farm, sorted ascending?
SQL: SELECT Total_Horses FROM farm ORDER BY Total_Horses ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 hosts of competitions whose theme is not "Aliens"?
SQL: SELECT Hosts FROM farm_competition WHERE Theme !=  'Aliens'
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 hosts of competitions for which the theme is not Aliens?
SQL: SELECT Hosts FROM farm_competition WHERE Theme !=  'Aliens'
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 themes of farm competitions sorted by year in ascending order?
SQL: SELECT Theme FROM farm_competition ORDER BY YEAR ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 themes of farm competitions, sorted by year ascending.
SQL: SELECT Theme FROM farm_competition ORDER BY YEAR ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 working horses of farms with more than 5000 total number of horses?
SQL: SELECT avg(Working_Horses) FROM farm WHERE Total_Horses  >  5000
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 working horses on farms with more than 5000 total horses.
SQL: SELECT avg(Working_Horses) FROM farm WHERE Total_Horses  >  5000
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 cows across all farms.
SQL: SELECT max(Cows) ,  min(Cows) FROM farm
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 cows across all farms.
SQL: SELECT max(Cows) ,  min(Cows) FROM farm
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 different statuses do cities have?
SQL: SELECT count(DISTINCT Status) FROM city
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 different statuses.
SQL: SELECT count(DISTINCT Status) FROM city
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 official names of cities in descending order of population.
SQL: SELECT Official_Name FROM city ORDER BY Population DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 official names of cities, ordered descending by population?
SQL: SELECT Official_Name FROM city ORDER BY Population DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 official name and status of the city with the largest population.
SQL: SELECT Official_Name ,  Status FROM city ORDER BY Population DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 official name and status of the city with the most residents?
SQL: SELECT Official_Name ,  Status FROM city ORDER BY Population DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 years and the official names of the host cities of competitions.
SQL: SELECT T2.Year ,  T1.Official_Name FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 years and official names of the cities of each competition.
SQL: SELECT T2.Year ,  T1.Official_Name FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 official names of the cities that have hosted more than one competition.
SQL: SELECT T1.Official_Name FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID GROUP BY T2.Host_city_ID HAVING COUNT(*)  >  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 official names of cities that have hosted more than one competition?
SQL: SELECT T1.Official_Name FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID GROUP BY T2.Host_city_ID HAVING COUNT(*)  >  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 status of the city that has hosted the greatest number of competitions.
SQL: SELECT T1.Status FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID GROUP BY T2.Host_city_ID ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 status of the city that has hosted the most competitions?
SQL: SELECT T1.Status FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID GROUP BY T2.Host_city_ID ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 themes of competitions with host cities having populations larger than 1000.
SQL: SELECT T2.Theme FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID WHERE T1.Population  >  1000
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 themes of competitions that have corresponding host cities with more than 1000 residents?
SQL: SELECT T2.Theme FROM city AS T1 JOIN farm_competition AS T2 ON T1.City_ID  =  T2.Host_city_ID WHERE T1.Population  >  1000