sert121/SpiderSQL huggingface dataset:
-- 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 employee number and job id for all employees whose salary is smaller than any salary of those employees whose job title is MK_MAN.
SQL: SELECT employee_id ,  job_id FROM employees WHERE salary  <  ( SELECT min(salary) FROM employees WHERE job_id  =  'MK_MAN' )
-- 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 employee ids and job ids for employees who make less than the lowest earning employee with title MK_MAN?
SQL: SELECT employee_id ,  job_id FROM employees WHERE salary  <  ( SELECT min(salary) FROM employees WHERE job_id  =  'MK_MAN' )
-- 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 employee number, name( first name and last name ) and job title for all employees whose salary is more than any salary of those employees whose job title is PU_MAN.
SQL: SELECT employee_id ,  first_name ,  last_name ,  job_id FROM employees WHERE salary  >  ( SELECT max(salary) FROM employees WHERE job_id  =  'PU_MAN' )
-- 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 employee ids, full names, and job ids for employees who make more than the highest earning employee with title PU_MAN?
SQL: SELECT employee_id ,  first_name ,  last_name ,  job_id FROM employees WHERE salary  >  ( SELECT max(salary) FROM employees WHERE job_id  =  'PU_MAN' )
-- 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 department id and the total salary for those departments which contains at least two employees.
SQL: SELECT department_id ,  SUM(salary) FROM employees GROUP BY department_id HAVING count(*)  >=  2
-- 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 total salaries and department id for each department that has more than 2 employees?
SQL: SELECT department_id ,  SUM(salary) FROM employees GROUP BY department_id HAVING count(*)  >=  2
-- 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 of those employees who did not have any job in the past.
SQL: SELECT * FROM employees WHERE employee_id NOT IN (SELECT employee_id FROM job_history)
-- 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 employees who have never had a job in the past?
SQL: SELECT * FROM employees WHERE employee_id NOT IN (SELECT employee_id FROM job_history)
-- 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 department ID, full name (first and last name), salary for those employees who is highest salary in every department.
SQL: SELECT first_name ,   last_name ,   salary ,  department_id ,  MAX(salary) FROM employees GROUP BY 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 department ids, full names, and salaries for employees who make the most in their departments?
SQL: SELECT first_name ,   last_name ,   salary ,  department_id ,  MAX(salary) FROM employees GROUP BY 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: display the first and last name, department, city, and state province for each employee.
SQL: SELECT T1.first_name ,  T1.last_name ,  T2.department_name ,  T3.city ,  T3.state_province FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_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 full names, departments, cities, and state provinces for each employee?
SQL: SELECT T1.first_name ,  T1.last_name ,  T2.department_name ,  T3.city ,  T3.state_province FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_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: display those employees who contain a letter z to their first name and also display their last name, city.
SQL: SELECT T1.first_name ,  T1.last_name ,  T3.city FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id WHERE T1.first_name LIKE '%z%'
-- 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 cities of employees who have the letter Z in their first names?
SQL: SELECT T1.first_name ,  T1.last_name ,  T3.city FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id WHERE T1.first_name LIKE '%z%'
-- 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 department name, city, and state province for each department.
SQL: SELECT T1.department_name ,  T2.city ,  T2.state_province FROM departments AS T1 JOIN locations AS T2 ON T2.location_id  =  T1.location_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 department names, cities, and state provinces for each department?
SQL: SELECT T1.department_name ,  T2.city ,  T2.state_province FROM departments AS T1 JOIN locations AS T2 ON T2.location_id  =  T1.location_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: display the full name (first and last name ) of employee with ID and name of the country presently where (s)he is working.
SQL: SELECT T1.first_name ,  T1.last_name , T1.employee_id ,  T4.country_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id JOIN countries AS T4 ON T3.country_id  =  T4.country_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 the full names, ids of each employee and the name of the country they are in?
SQL: SELECT T1.first_name ,  T1.last_name , T1.employee_id ,  T4.country_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id JOIN countries AS T4 ON T3.country_id  =  T4.country_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: display the department name and number of employees in each of the department.
SQL: SELECT department_name ,  COUNT(*) FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id GROUP BY department_name
-- 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 department names and how many employees work in each of them?
SQL: SELECT department_name ,  COUNT(*) FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id GROUP BY department_name
-- 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 name), and salary of those employees who working in any department located in London.
SQL: SELECT first_name ,  last_name ,  salary FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN  locations AS T3 ON T2.location_id  =  T3.location_id WHERE  T3.city  =  'London'
-- 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 full names and salaries of employees working in the city of London?
SQL: SELECT first_name ,  last_name ,  salary FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN  locations AS T3 ON T2.location_id  =  T3.location_id WHERE  T3.city  =  'London'
-- Language PostgreSQL
        -- Tables: 
             -- Table: genre 
columns : [['genre name', 'text'], ['rating', 'text'], ['most popular in', 'text']]

 -- Table: artist 
columns : [['artist name', 'text'], ['country', 'text'], ['gender', 'text'], ['preferred genre', 'text']]

 -- Table: files 
columns : [['song id', 'number'], ['artist name', 'text'], ['file size', 'text'], ['duration', 'text'], ['formats', 'text']]

 -- Table: song 
columns : [['song name', 'text'], ['artist name', 'text'], ['country', 'text'], ['song id', 'number'], ['genre is', 'text'], ['rating', 'number'], ['languages', 'text'], ['releasedate', 'time'], ['resolution', 'number']]

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



Question: What is the name of the song that was released in the most recent year?
SQL: SELECT song_name ,  releasedate FROM song ORDER BY releasedate DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: genre 
columns : [['genre name', 'text'], ['rating', 'text'], ['most popular in', 'text']]

 -- Table: artist 
columns : [['artist name', 'text'], ['country', 'text'], ['gender', 'text'], ['preferred genre', 'text']]

 -- Table: files 
columns : [['song id', 'number'], ['artist name', 'text'], ['file size', 'text'], ['duration', 'text'], ['formats', 'text']]

 -- Table: song 
columns : [['song name', 'text'], ['artist name', 'text'], ['country', 'text'], ['song id', 'number'], ['genre is', 'text'], ['rating', 'number'], ['languages', 'text'], ['releasedate', 'time'], ['resolution', 'number']]

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



Question: What is the name of the song that was released most recently?
SQL: SELECT song_name ,  releasedate FROM song ORDER BY releasedate DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: genre 
columns : [['genre name', 'text'], ['rating', 'text'], ['most popular in', 'text']]

 -- Table: artist 
columns : [['artist name', 'text'], ['country', 'text'], ['gender', 'text'], ['preferred genre', 'text']]

 -- Table: files 
columns : [['song id', 'number'], ['artist name', 'text'], ['file size', 'text'], ['duration', 'text'], ['formats', 'text']]

 -- Table: song 
columns : [['song name', 'text'], ['artist name', 'text'], ['country', 'text'], ['song id', 'number'], ['genre is', 'text'], ['rating', 'number'], ['languages', 'text'], ['releasedate', 'time'], ['resolution', '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 id of the longest song?
SQL: SELECT f_id FROM files ORDER BY duration DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: genre 
columns : [['genre name', 'text'], ['rating', 'text'], ['most popular in', 'text']]

 -- Table: artist 
columns : [['artist name', 'text'], ['country', 'text'], ['gender', 'text'], ['preferred genre', 'text']]

 -- Table: files 
columns : [['song id', 'number'], ['artist name', 'text'], ['file size', 'text'], ['duration', 'text'], ['formats', 'text']]

 -- Table: song 
columns : [['song name', 'text'], ['artist name', 'text'], ['country', 'text'], ['song id', 'number'], ['genre is', 'text'], ['rating', 'number'], ['languages', 'text'], ['releasedate', 'time'], ['resolution', '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 of the song that lasts the longest.
SQL: SELECT f_id FROM files ORDER BY duration DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: genre 
columns : [['genre name', 'text'], ['rating', 'text'], ['most popular in', 'text']]

 -- Table: artist 
columns : [['artist name', 'text'], ['country', 'text'], ['gender', 'text'], ['preferred genre', 'text']]

 -- Table: files 
columns : [['song id', 'number'], ['artist name', 'text'], ['file size', 'text'], ['duration', 'text'], ['formats', 'text']]

 -- Table: song 
columns : [['song name', 'text'], ['artist name', 'text'], ['country', 'text'], ['song id', 'number'], ['genre is', 'text'], ['rating', 'number'], ['languages', 'text'], ['releasedate', 'time'], ['resolution', '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 all English songs.
SQL: SELECT song_name FROM song WHERE languages  =  "english"
-- Language PostgreSQL
        -- Tables: 
             -- Table: genre 
columns : [['genre name', 'text'], ['rating', 'text'], ['most popular in', 'text']]

 -- Table: artist 
columns : [['artist name', 'text'], ['country', 'text'], ['gender', 'text'], ['preferred genre', 'text']]

 -- Table: files 
columns : [['song id', 'number'], ['artist name', 'text'], ['file size', 'text'], ['duration', 'text'], ['formats', 'text']]

 -- Table: song 
columns : [['song name', 'text'], ['artist name', 'text'], ['country', 'text'], ['song id', 'number'], ['genre is', 'text'], ['rating', 'number'], ['languages', 'text'], ['releasedate', 'time'], ['resolution', 'number']]

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



Question: What are the names of all songs in English?
SQL: SELECT song_name FROM song WHERE languages  =  "english"
-- Language PostgreSQL
        -- Tables: 
             -- Table: genre 
columns : [['genre name', 'text'], ['rating', 'text'], ['most popular in', 'text']]

 -- Table: artist 
columns : [['artist name', 'text'], ['country', 'text'], ['gender', 'text'], ['preferred genre', 'text']]

 -- Table: files 
columns : [['song id', 'number'], ['artist name', 'text'], ['file size', 'text'], ['duration', 'text'], ['formats', 'text']]

 -- Table: song 
columns : [['song name', 'text'], ['artist name', 'text'], ['country', 'text'], ['song id', 'number'], ['genre is', 'text'], ['rating', 'number'], ['languages', 'text'], ['releasedate', 'time'], ['resolution', '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 id of songs whose format is mp3.
SQL: SELECT f_id FROM files WHERE formats  =  "mp3"
-- Language PostgreSQL
        -- Tables: 
             -- Table: genre 
columns : [['genre name', 'text'], ['rating', 'text'], ['most popular in', 'text']]

 -- Table: artist 
columns : [['artist name', 'text'], ['country', 'text'], ['gender', 'text'], ['preferred genre', 'text']]

 -- Table: files 
columns : [['song id', 'number'], ['artist name', 'text'], ['file size', 'text'], ['duration', 'text'], ['formats', 'text']]

 -- Table: song 
columns : [['song name', 'text'], ['artist name', 'text'], ['country', 'text'], ['song id', 'number'], ['genre is', 'text'], ['rating', 'number'], ['languages', 'text'], ['releasedate', 'time'], ['resolution', '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 id of all the files in mp3 format?
SQL: SELECT f_id FROM files WHERE formats  =  "mp3"
-- Language PostgreSQL
        -- Tables: 
             -- Table: genre 
columns : [['genre name', 'text'], ['rating', 'text'], ['most popular in', 'text']]

 -- Table: artist 
columns : [['artist name', 'text'], ['country', 'text'], ['gender', 'text'], ['preferred genre', 'text']]

 -- Table: files 
columns : [['song id', 'number'], ['artist name', 'text'], ['file size', 'text'], ['duration', 'text'], ['formats', 'text']]

 -- Table: song 
columns : [['song name', 'text'], ['artist name', 'text'], ['country', 'text'], ['song id', 'number'], ['genre is', 'text'], ['rating', 'number'], ['languages', 'text'], ['releasedate', 'time'], ['resolution', 'number']]

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



Question: List the name and country of origin for all singers who have produced songs with rating above 9.
SQL: SELECT DISTINCT T1.artist_name ,  T1.country FROM artist AS T1 JOIN song AS T2 ON T1.artist_name  =  T2.artist_name WHERE T2.rating  >  9