sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: Find the id and name of the staff who has been assigned for the shortest period.
SQL: SELECT T1.staff_id ,  T1.staff_name FROM staff AS T1 JOIN Staff_Department_Assignments AS T2 ON T1.staff_id  =  T2.staff_id ORDER BY date_assigned_to - date_assigned_from LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: What is the id and name of the staff who has been assigned for the least amount of time?
SQL: SELECT T1.staff_id ,  T1.staff_name FROM staff AS T1 JOIN Staff_Department_Assignments AS T2 ON T1.staff_id  =  T2.staff_id ORDER BY date_assigned_to - date_assigned_from LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: Return the names and ids of all products whose price is between 600 and 700.
SQL: SELECT product_name ,  product_id FROM products WHERE product_price BETWEEN 600 AND 700
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: What are the names and ids of products costing between 600 and 700?
SQL: SELECT product_name ,  product_id FROM products WHERE product_price BETWEEN 600 AND 700
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: Find the ids of all distinct customers who made order after some orders that were Cancelled.
SQL: SELECT DISTINCT customer_id FROM Customer_Orders WHERE order_date  >  (SELECT min(order_date) FROM Customer_Orders WHERE order_status_code  =  "Cancelled")
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: What are the distinct ids of customers who made an order after any order that was Cancelled?
SQL: SELECT DISTINCT customer_id FROM Customer_Orders WHERE order_date  >  (SELECT min(order_date) FROM Customer_Orders WHERE order_status_code  =  "Cancelled")
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: What is id of the staff who had a Staff Department Assignment earlier than any Clerical Staff?
SQL: SELECT staff_id FROM Staff_Department_Assignments WHERE date_assigned_to  <  (SELECT max(date_assigned_to) FROM Staff_Department_Assignments WHERE job_title_code  =  'Clerical Staff')
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: Return the id of the staff whose Staff Department Assignment was earlier than that of any Clerical Staff.
SQL: SELECT staff_id FROM Staff_Department_Assignments WHERE date_assigned_to  <  (SELECT max(date_assigned_to) FROM Staff_Department_Assignments WHERE job_title_code  =  'Clerical Staff')
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: What are the names and ids of customers whose address contains TN?
SQL: SELECT customer_name ,  customer_id FROM customers WHERE customer_address LIKE "%TN%"
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: Return the names and ids of customers who have TN in their address.
SQL: SELECT customer_name ,  customer_id FROM customers WHERE customer_address LIKE "%TN%"
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: Return the name and gender of the staff who was assigned in 2016.
SQL: SELECT T1.staff_name ,  T1.staff_gender FROM staff AS T1 JOIN staff_department_assignments AS T2 ON T1.staff_id  =  T2.staff_id WHERE T2.date_assigned_from LIKE "2016%"
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: What are the names and genders of staff who were assigned in 2016?
SQL: SELECT T1.staff_name ,  T1.staff_gender FROM staff AS T1 JOIN staff_department_assignments AS T2 ON T1.staff_id  =  T2.staff_id WHERE T2.date_assigned_from LIKE "2016%"
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: List the name of staff who has been assigned multiple jobs.
SQL: SELECT T1.staff_name FROM staff AS T1 JOIN staff_department_assignments AS T2 ON T1.staff_id  =  T2.staff_id GROUP BY T2.staff_id HAVING COUNT (*)  >  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: What are the names of staff who have been assigned multiple jobs?
SQL: SELECT T1.staff_name FROM staff AS T1 JOIN staff_department_assignments AS T2 ON T1.staff_id  =  T2.staff_id GROUP BY T2.staff_id HAVING COUNT (*)  >  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: List the name and phone number of all suppliers in the alphabetical order of their addresses.
SQL: SELECT T1.supplier_name ,  T1.supplier_phone FROM Suppliers AS T1 JOIN supplier_addresses AS T2 ON T1.supplier_id  =  T2.supplier_id JOIN addresses AS T3 ON T2.address_id  =  T3.address_id ORDER BY T3.address_details
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: What are the names and phone numbers for all suppliers, sorted in alphabetical order of their addressed?
SQL: SELECT T1.supplier_name ,  T1.supplier_phone FROM Suppliers AS T1 JOIN supplier_addresses AS T2 ON T1.supplier_id  =  T2.supplier_id JOIN addresses AS T3 ON T2.address_id  =  T3.address_id ORDER BY T3.address_details
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text']]

 -- Table: staff 
columns : [['staff id', 'number'], ['staff gender', 'text'], ['staff name', 'text']]

 -- Table: suppliers 
columns : [['supplier id', 'number'], ['supplier name', 'text'], ['supplier phone', 'text']]

 -- Table: department store chain 
columns : [['department store chain id', 'number'], ['department store chain name', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method code', 'text'], ['customer code', 'text'], ['customer name', 'text'], ['customer address', 'text'], ['customer phone', 'text'], ['customer email', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['product type code', 'text'], ['product name', 'text'], ['product price', 'number']]

 -- Table: supplier addresses 
columns : [['supplier id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer addresses 
columns : [['customer id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: customer orders 
columns : [['order id', 'number'], ['customer id', 'number'], ['order status code', 'text'], ['order date', 'time']]

 -- Table: department stores 
columns : [['department store id', 'number'], ['department store chain id', 'number'], ['store name', 'text'], ['store address', 'text'], ['store phone', 'text'], ['store email', 'text']]

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

 -- Table: order items 
columns : [['order item id', 'number'], ['order id', 'number'], ['product id', 'number']]

 -- Table: product suppliers 
columns : [['product id', 'number'], ['supplier id', 'number'], ['date supplied from', 'time'], ['date supplied to', 'time'], ['total amount purchased', 'text'], ['total value purchased', 'number']]

 -- Table: staff department assignments 
columns : [['staff id', 'number'], ['department id', 'number'], ['date assigned from', 'time'], ['job title code', 'text'], ['date assigned to', 'time']]

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



Question: What are the phone numbers of all customers and suppliers.
SQL: SELECT customer_phone FROM customers UNION SELECT supplier_phone FROM suppliers