sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: club 
columns : [['club id', 'number'], ['name', 'text'], ['region', 'text'], ['start year', 'text']]

 -- Table: club rank 
columns : [['rank', 'number'], ['club id', 'number'], ['gold', 'number'], ['silver', 'number'], ['bronze', 'number'], ['total', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['name', 'text'], ['position', 'text'], ['club id', 'number'], ['apps', 'number'], ['tries', 'number'], ['goals', 'text'], ['points', 'number']]

 -- Table: competition 
columns : [['competition id', 'number'], ['year', 'number'], ['competition type', 'text'], ['country', 'text']]

 -- Table: competition result 
columns : [['competition id', 'number'], ['club id 1', 'number'], ['club id 2', 'number'], ['score', '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 name of the country that participated in the most tournament competitions?
SQL: SELECT country FROM competition WHERE competition_type  =  'Tournament' GROUP BY country ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: club 
columns : [['club id', 'number'], ['name', 'text'], ['region', 'text'], ['start year', 'text']]

 -- Table: club rank 
columns : [['rank', 'number'], ['club id', 'number'], ['gold', 'number'], ['silver', 'number'], ['bronze', 'number'], ['total', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['name', 'text'], ['position', 'text'], ['club id', 'number'], ['apps', 'number'], ['tries', 'number'], ['goals', 'text'], ['points', 'number']]

 -- Table: competition 
columns : [['competition id', 'number'], ['year', 'number'], ['competition type', 'text'], ['country', 'text']]

 -- Table: competition result 
columns : [['competition id', 'number'], ['club id 1', 'number'], ['club id 2', 'number'], ['score', '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 countries did participated in both Friendly and Tournament type competitions.
SQL: SELECT country FROM competition WHERE competition_type  =  'Friendly' INTERSECT SELECT country FROM competition WHERE competition_type  =  'Tournament'
-- Language PostgreSQL
        -- Tables: 
             -- Table: club 
columns : [['club id', 'number'], ['name', 'text'], ['region', 'text'], ['start year', 'text']]

 -- Table: club rank 
columns : [['rank', 'number'], ['club id', 'number'], ['gold', 'number'], ['silver', 'number'], ['bronze', 'number'], ['total', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['name', 'text'], ['position', 'text'], ['club id', 'number'], ['apps', 'number'], ['tries', 'number'], ['goals', 'text'], ['points', 'number']]

 -- Table: competition 
columns : [['competition id', 'number'], ['year', 'number'], ['competition type', 'text'], ['country', 'text']]

 -- Table: competition result 
columns : [['competition id', 'number'], ['club id 1', 'number'], ['club id 2', 'number'], ['score', '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 countries that participated in both friendly and tournament type competitions?
SQL: SELECT country FROM competition WHERE competition_type  =  'Friendly' INTERSECT SELECT country FROM competition WHERE competition_type  =  'Tournament'
-- Language PostgreSQL
        -- Tables: 
             -- Table: club 
columns : [['club id', 'number'], ['name', 'text'], ['region', 'text'], ['start year', 'text']]

 -- Table: club rank 
columns : [['rank', 'number'], ['club id', 'number'], ['gold', 'number'], ['silver', 'number'], ['bronze', 'number'], ['total', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['name', 'text'], ['position', 'text'], ['club id', 'number'], ['apps', 'number'], ['tries', 'number'], ['goals', 'text'], ['points', 'number']]

 -- Table: competition 
columns : [['competition id', 'number'], ['year', 'number'], ['competition type', 'text'], ['country', 'text']]

 -- Table: competition result 
columns : [['competition id', 'number'], ['club id 1', 'number'], ['club id 2', 'number'], ['score', '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: Find the countries that have never participated in any competition with Friendly type.
SQL: SELECT country FROM competition EXCEPT SELECT country FROM competition WHERE competition_type  =  'Friendly'
-- Language PostgreSQL
        -- Tables: 
             -- Table: club 
columns : [['club id', 'number'], ['name', 'text'], ['region', 'text'], ['start year', 'text']]

 -- Table: club rank 
columns : [['rank', 'number'], ['club id', 'number'], ['gold', 'number'], ['silver', 'number'], ['bronze', 'number'], ['total', 'number']]

 -- Table: player 
columns : [['player id', 'number'], ['name', 'text'], ['position', 'text'], ['club id', 'number'], ['apps', 'number'], ['tries', 'number'], ['goals', 'text'], ['points', 'number']]

 -- Table: competition 
columns : [['competition id', 'number'], ['year', 'number'], ['competition type', 'text'], ['country', 'text']]

 -- Table: competition result 
columns : [['competition id', 'number'], ['club id 1', 'number'], ['club id 2', 'number'], ['score', '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 countries that have never participated in any friendly-type competitions?
SQL: SELECT country FROM competition EXCEPT SELECT country FROM competition WHERE competition_type  =  'Friendly'
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 furniture components are there in total?
SQL: SELECT sum(num_of_component) FROM furniture
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 name and id of the furniture with the highest market rate.
SQL: SELECT name ,  furniture_id FROM furniture ORDER BY market_rate DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 total market rate of the furnitures that have the top 2 market shares.
SQL: SELECT sum(market_rate) FROM furniture ORDER BY market_rate DESC LIMIT 2
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 component amounts and names of all furnitures that have more than 10 components.
SQL: SELECT Num_of_Component ,  name FROM furniture WHERE Num_of_Component  >  10
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', 'number']]

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



Question: Find the name and component amount of the least popular furniture.
SQL: SELECT name ,  Num_of_Component FROM furniture ORDER BY market_rate LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', 'number']]

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



Question: Find the names of furnitures whose prices are lower than the highest price.
SQL: SELECT t1.name FROM furniture AS t1 JOIN furniture_manufacte AS t2 ON t1.Furniture_ID  =  t2.Furniture_ID WHERE t2.Price_in_Dollar  <  (SELECT max(Price_in_Dollar) FROM furniture_manufacte)
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 manufacturer has the most number of shops? List its name and year of opening.
SQL: SELECT open_year ,  name FROM manufacturer ORDER BY num_of_shops DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', 'number']]

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



Question: Find the average number of factories for the manufacturers that have more than 20 shops.
SQL: SELECT avg(Num_of_Factories) FROM manufacturer WHERE num_of_shops  >  20
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 manufacturer names and ids ordered by their opening year.
SQL: SELECT name ,  manufacturer_id FROM manufacturer ORDER BY open_year
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 me the name and year of opening of the manufacturers that have either less than 10 factories or more than 10 shops.
SQL: SELECT name ,  open_year FROM manufacturer WHERE num_of_shops  >  10 OR Num_of_Factories  <  10
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 factories and maximum number of shops for manufacturers that opened before 1990.
SQL: SELECT max(num_of_shops) ,  avg(Num_of_Factories) FROM manufacturer WHERE open_year  <  1990
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 id and number of shops for the company that produces the most expensive furniture.
SQL: SELECT t1.manufacturer_id ,  t1.num_of_shops FROM manufacturer AS t1 JOIN furniture_manufacte AS t2 ON t1.manufacturer_id  =  t2.manufacturer_id ORDER BY t2.Price_in_Dollar DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 funiture types produced by each manufacturer as well as the company names.
SQL: SELECT count(*) ,  t1.name FROM manufacturer AS t1 JOIN furniture_manufacte AS t2 ON t1.manufacturer_id  =  t2.manufacturer_id GROUP BY t1.manufacturer_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 me the names and prices of furnitures which some companies are manufacturing.
SQL: SELECT t1.name ,  t2.price_in_dollar FROM furniture AS t1 JOIN furniture_manufacte AS t2 ON t1.Furniture_ID  =  t2.Furniture_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 market shares and names of furnitures which no any company is producing in our records.
SQL: SELECT Market_Rate ,  name FROM furniture WHERE Furniture_ID NOT IN (SELECT Furniture_ID FROM furniture_manufacte)
-- Language PostgreSQL
        -- Tables: 
             -- Table: manufacturer 
columns : [['manufacturer id', 'number'], ['open year', 'number'], ['name', 'text'], ['num of factories', 'number'], ['num of shops', 'number']]

 -- Table: furniture 
columns : [['furniture id', 'number'], ['name', 'text'], ['num of component', 'number'], ['market rate', 'number']]

 -- Table: furniture manufacte 
columns : [['manufacturer id', 'number'], ['furniture id', 'number'], ['price in dollar', '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 company that produces both furnitures with less than 6 components and furnitures with more than 10 components.
SQL: SELECT t3.name FROM furniture AS t1 JOIN furniture_manufacte AS t2 ON t1.Furniture_ID  =  t2.Furniture_ID JOIN manufacturer AS t3 ON t2.manufacturer_id  =  t3.manufacturer_id WHERE t1.num_of_component  <  6 INTERSECT SELECT t3.name FROM furniture AS t1 JOIN furniture_manufacte AS t2 ON t1.Furniture_ID  =  t2.Furniture_ID JOIN manufacturer AS t3 ON t2.manufacturer_id  =  t3.manufacturer_id WHERE t1.num_of_component  >  10
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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: Display the first name and department name for each employee.
SQL: SELECT T1.first_name ,  T2.department_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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 first name and department name of all employees?
SQL: SELECT T1.first_name ,  T2.department_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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 full name (first and last name), and salary for those employees who earn below 6000.
SQL: SELECT first_name ,  last_name ,  salary FROM employees WHERE salary  <  6000
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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 full names and salaries for any employees earning less than 6000?
SQL: SELECT first_name ,  last_name ,  salary FROM employees WHERE salary  <  6000
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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: Display the first name, and department number for all employees whose last name is "McEwen".
SQL: SELECT first_name ,  department_id FROM employees WHERE last_name  =  'McEwen'
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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 first names and department numbers for employees with last name McEwen?
SQL: SELECT first_name ,  department_id FROM employees WHERE last_name  =  'McEwen'
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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: Return all the information for all employees without any department number.
SQL: SELECT * FROM employees WHERE department_id  =  "null"
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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 all the employees without a department number?
SQL: SELECT * FROM employees WHERE department_id  =  "null"
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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: Display all the information about the department Marketing.
SQL: SELECT * FROM  departments WHERE department_name  =  'Marketing'
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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 all the information about the Marketing department?
SQL: SELECT * FROM  departments WHERE department_name  =  'Marketing'
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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: when is the hire date for those employees whose first name does not containing the letter M?
SQL: SELECT hire_date FROM employees WHERE first_name NOT LIKE '%M%'
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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: On what dates were employees without the letter M in their first names hired?
SQL: SELECT hire_date FROM employees WHERE first_name NOT LIKE '%M%'
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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: display the full name (first and last), hire date, salary, and department number for those employees whose first name does not containing the letter M.
SQL: SELECT first_name ,  last_name ,  hire_date ,  salary ,  department_id FROM employees WHERE first_name NOT LIKE '%M%'
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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 full name, hire date, salary, and department id for employees without the letter M in their first name?
SQL: SELECT first_name ,  last_name ,  hire_date ,  salary ,  department_id FROM employees WHERE first_name NOT LIKE '%M%'
-- Language PostgreSQL
        -- Tables: 
             -- Table: regions 
columns : [['region id', 'number'], ['region name', 'text']]

 -- Table: countries 
columns : [['country id', 'text'], ['country name', 'text'], ['region id', 'number']]

 -- Table: departments 
columns : [['department id', 'number'], ['department name', 'text'], ['manager id', 'number'], ['location id', 'number']]

 -- Table: jobs 
columns : [['job id', 'text'], ['job title', 'text'], ['min salary', 'number'], ['max salary', 'number']]

 -- Table: employees 
columns : [['employee id', 'number'], ['first name', 'text'], ['last name', 'text'], ['email', 'text'], ['phone number', 'text'], ['hire date', 'time'], ['job id', 'text'], ['salary', 'number'], ['commission pct', 'number'], ['manager id', 'number'], ['department id', 'number']]

 -- Table: job history 
columns : [['employee id', 'number'], ['start date', 'time'], ['end date', 'time'], ['job id', 'text'], ['department id', 'number']]

 -- Table: locations 
columns : [['location id', 'number'], ['street address', 'text'], ['postal code', 'text'], ['city', 'text'], ['state province', 'text'], ['country id', '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: display the full name (first and last), hire date, salary, and department number for those employees whose first name does not containing the letter M and make the result set in ascending order by department number.
SQL: SELECT first_name ,  last_name ,  hire_date ,  salary ,  department_id FROM employees WHERE first_name NOT LIKE '%M%' ORDER BY department_id