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 product type, name, and price for products supplied by supplier 3.
SQL: SELECT T2.product_type_code ,  T2.product_name ,  T2.product_price FROM product_suppliers AS T1 JOIN products AS T2 ON T1.product_id  =  T2.product_id WHERE T1.supplier_id  =  3
-- 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 distinct name of customers whose order status is Pending, in the order of customer id.
SQL: SELECT DISTINCT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  "Pending" ORDER BY T2.customer_id
-- 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 with an order status of Pending, sorted by customer id?
SQL: SELECT DISTINCT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  "Pending" ORDER BY T2.customer_id
-- 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 name and address of the customers who have both New and Pending orders.
SQL: SELECT T1.customer_name ,  T1.customer_address FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  "New" INTERSECT SELECT T1.customer_name ,  T1.customer_address FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  "Pending"
-- 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 addressed of customers who have both New and Pending orders?
SQL: SELECT T1.customer_name ,  T1.customer_address FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  "New" INTERSECT SELECT T1.customer_name ,  T1.customer_address FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  "Pending"
-- 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 ids of all the products that are supplied by supplier id 2 and are more expensive than the average price of all products.
SQL: SELECT T1.product_id FROM product_suppliers AS T1 JOIN products AS T2 ON T1.product_id  =  T2.product_id WHERE T1.supplier_id  =  2 AND T2.product_price  >  (SELECT avg(product_price) FROM products)
-- 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 products from the supplier with id 2, which are more expensive than the average price across all products?
SQL: SELECT T1.product_id FROM product_suppliers AS T1 JOIN products AS T2 ON T1.product_id  =  T2.product_id WHERE T1.supplier_id  =  2 AND T2.product_price  >  (SELECT avg(product_price) FROM products)
-- 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 department store that has both marketing and managing department?
SQL: SELECT T2.dept_store_id ,  T2.store_name FROM departments AS T1 JOIN department_stores AS T2 ON T1.dept_store_id  =  T2.dept_store_id WHERE T1.department_name  =  "marketing" INTERSECT SELECT T2.dept_store_id ,  T2.store_name FROM departments AS T1 JOIN department_stores AS T2 ON T1.dept_store_id  =  T2.dept_store_id WHERE T1.department_name  =  "managing"
-- 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 and names of department stores with both marketing and managing departments?
SQL: SELECT T2.dept_store_id ,  T2.store_name FROM departments AS T1 JOIN department_stores AS T2 ON T1.dept_store_id  =  T2.dept_store_id WHERE T1.department_name  =  "marketing" INTERSECT SELECT T2.dept_store_id ,  T2.store_name FROM departments AS T1 JOIN department_stores AS T2 ON T1.dept_store_id  =  T2.dept_store_id WHERE T1.department_name  =  "managing"
-- 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 the two department store chains with the largest number of department stores?
SQL: SELECT dept_store_chain_id FROM department_stores GROUP BY dept_store_chain_id ORDER BY count(*) DESC LIMIT 2
-- 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 the two department store chains with the most department stores.
SQL: SELECT dept_store_chain_id FROM department_stores GROUP BY dept_store_chain_id ORDER BY count(*) DESC LIMIT 2
-- 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 of the department with the least number of staff?
SQL: SELECT department_id FROM staff_department_assignments GROUP BY department_id ORDER BY count(*) 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 id of the department with the fewest staff assignments.
SQL: SELECT department_id FROM staff_department_assignments GROUP BY department_id ORDER BY count(*) 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: For each product type, return the maximum and minimum price.
SQL: SELECT product_type_code ,  max(product_price) ,  min(product_price) FROM products GROUP 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: What are the maximum and minimum product prices for each product type?
SQL: SELECT product_type_code ,  max(product_price) ,  min(product_price) FROM products GROUP 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: Find the product type whose average price is higher than the average price of all products.
SQL: SELECT product_type_code FROM products GROUP BY product_type_code HAVING avg(product_price)  >  (SELECT avg(product_price) FROM products)
-- 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 code of the product type with an average price higher than the average price of all products?
SQL: SELECT product_type_code FROM products GROUP BY product_type_code HAVING avg(product_price)  >  (SELECT avg(product_price) FROM products)