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: Return the phone numbers for all customers and suppliers.
SQL: SELECT customer_phone FROM customers UNION SELECT supplier_phone FROM suppliers
-- 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 ids of all products that were ordered more than three times or supplied more than 80000.
SQL: SELECT product_id FROM Order_Items GROUP BY product_id HAVING count(*)  >  3 UNION SELECT product_id FROM Product_Suppliers GROUP BY product_id HAVING sum(total_amount_purchased)  >  80000
-- 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 ids of all products that were either ordered more than 3 times or have a cumulative amount purchased of above 80000?
SQL: SELECT product_id FROM Order_Items GROUP BY product_id HAVING count(*)  >  3 UNION SELECT product_id FROM Product_Suppliers GROUP BY product_id HAVING sum(total_amount_purchased)  >  80000
-- 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 id and name of the products whose price is lower than 600 or higher than 900?
SQL: SELECT product_id ,  product_name FROM products WHERE product_price  <  600 OR product_price  >  900
-- 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: Give the ids and names of products with price lower than 600 or higher than 900.
SQL: SELECT product_id ,  product_name FROM products WHERE product_price  <  600 OR product_price  >  900
-- 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 of suppliers whose average amount purchased for each product is above 50000 or below 30000.
SQL: SELECT supplier_id FROM Product_Suppliers GROUP BY supplier_id HAVING avg(total_amount_purchased)  >  50000 OR avg(total_amount_purchased)  <  30000
-- 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 ids of suppliers which have an average amount purchased of above 50000 or below 30000?
SQL: SELECT supplier_id FROM Product_Suppliers GROUP BY supplier_id HAVING avg(total_amount_purchased)  >  50000 OR avg(total_amount_purchased)  <  30000
-- 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 average amount purchased and value purchased for the supplier who supplies the most products.
SQL: SELECT avg(total_amount_purchased) ,  avg(total_value_purchased) FROM Product_Suppliers WHERE supplier_id  =  (SELECT supplier_id FROM Product_Suppliers GROUP BY supplier_id ORDER BY count(*) DESC 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 average total amount purchased and total value purchased for the supplier who supplies the greatest number of products.
SQL: SELECT avg(total_amount_purchased) ,  avg(total_value_purchased) FROM Product_Suppliers WHERE supplier_id  =  (SELECT supplier_id FROM Product_Suppliers GROUP BY supplier_id ORDER BY count(*) DESC 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 largest and smallest customer codes?
SQL: SELECT max(customer_code) ,  min(customer_code) FROM Customers
-- 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 maximum and minimum customer codes.
SQL: SELECT max(customer_code) ,  min(customer_code) FROM Customers
-- 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 names of all the distinct customers who bought a keyboard.
SQL: SELECT DISTINCT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id JOIN order_items AS T3 ON T2.order_id  =  T3.order_id JOIN products AS T4 ON T3.product_id  =  T4.product_id WHERE T4.product_name  =  "keyboard"
-- 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 names of customers who have purchased a keyboard?
SQL: SELECT DISTINCT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id JOIN order_items AS T3 ON T2.order_id  =  T3.order_id JOIN products AS T4 ON T3.product_id  =  T4.product_id WHERE T4.product_name  =  "keyboard"
-- 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 names and phone numbers of all the distinct suppliers who supply red jeans.
SQL: SELECT DISTINCT T1.supplier_name ,  T1.supplier_phone FROM suppliers AS T1 JOIN product_suppliers AS T2 ON T1.supplier_id  =  T2.supplier_id JOIN products AS T3 ON T2.product_id  =  T3.product_id WHERE T3.product_name  =  "red jeans"
-- 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 names and phone numbers for suppliers who have red jeans?
SQL: SELECT DISTINCT T1.supplier_name ,  T1.supplier_phone FROM suppliers AS T1 JOIN product_suppliers AS T2 ON T1.supplier_id  =  T2.supplier_id JOIN products AS T3 ON T2.product_id  =  T3.product_id WHERE T3.product_name  =  "red jeans"
-- 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 highest and lowest prices of products, grouped by and alphabetically ordered by product type?
SQL: SELECT max(product_price) ,  min(product_price) ,  product_type_code FROM products GROUP BY product_type_code ORDER BY product_type_code
-- 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: Give the maximum and minimum product prices for each product type, grouped and ordered by product type.
SQL: SELECT max(product_price) ,  min(product_price) ,  product_type_code FROM products GROUP BY product_type_code ORDER BY product_type_code