sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1 number building', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

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

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

 -- Table: contacts 
columns : [['contact id', 'number'], ['customer id', 'number'], ['gender', 'text'], ['first name', 'text'], ['last name', 'text'], ['contact phone', 'text']]

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

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

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

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



Question: Show the product type codes which have at least two products.
SQL: SELECT product_type_code FROM products GROUP BY product_type_code HAVING count(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1 number building', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

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

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

 -- Table: contacts 
columns : [['contact id', 'number'], ['customer id', 'number'], ['gender', 'text'], ['first name', 'text'], ['last name', 'text'], ['contact phone', 'text']]

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

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

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

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



Question: Show the names of customers who have both an order in completed status and an order in part status.
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  'Completed' INTERSECT SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  'Part'
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1 number building', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

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

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

 -- Table: contacts 
columns : [['contact id', 'number'], ['customer id', 'number'], ['gender', 'text'], ['first name', 'text'], ['last name', 'text'], ['contact phone', 'text']]

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

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

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

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



Question: Show the name, phone, and payment method code for all customers in descending order of customer number.
SQL: SELECT customer_name ,  customer_phone ,  payment_method_code FROM customers ORDER BY customer_number DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1 number building', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

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

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

 -- Table: contacts 
columns : [['contact id', 'number'], ['customer id', 'number'], ['gender', 'text'], ['first name', 'text'], ['last name', 'text'], ['contact phone', 'text']]

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

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

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

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



Question: Show the product name and total order quantity for each product.
SQL: SELECT T1.product_name ,  sum(T2.order_quantity) FROM products AS T1 JOIN order_items AS T2 ON T1.product_id  =  T2.product_id GROUP BY T1.product_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1 number building', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

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

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

 -- Table: contacts 
columns : [['contact id', 'number'], ['customer id', 'number'], ['gender', 'text'], ['first name', 'text'], ['last name', 'text'], ['contact phone', 'text']]

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

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

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

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



Question: Show the minimum, maximum, average price for all products.
SQL: SELECT min(product_price) ,  max(product_price) ,  avg(product_price) FROM products
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1 number building', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

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

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

 -- Table: contacts 
columns : [['contact id', 'number'], ['customer id', 'number'], ['gender', 'text'], ['first name', 'text'], ['last name', 'text'], ['contact phone', 'text']]

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

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

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

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



Question: How many products have a price higher than the average?
SQL: SELECT count(*) FROM products WHERE product_price  >  (SELECT avg(product_price) FROM products)
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1 number building', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

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

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

 -- Table: contacts 
columns : [['contact id', 'number'], ['customer id', 'number'], ['gender', 'text'], ['first name', 'text'], ['last name', 'text'], ['contact phone', 'text']]

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

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

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

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



Question: Show the customer name, customer address city, date from, and date to for each customer address history.
SQL: SELECT T2.customer_name ,  T3.city ,  T1.date_from ,  T1.date_to FROM customer_address_history AS T1 JOIN customers AS T2 ON T1.customer_id  =  T2.customer_id JOIN addresses AS T3 ON T1.address_id  =  T3.address_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1 number building', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

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

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

 -- Table: contacts 
columns : [['contact id', 'number'], ['customer id', 'number'], ['gender', 'text'], ['first name', 'text'], ['last name', 'text'], ['contact phone', 'text']]

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

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

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

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



Question: Show the names of customers who use Credit Card payment method and have more than 2 orders.
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.payment_method_code  =  'Credit Card' GROUP BY T1.customer_id HAVING count(*)  >  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1 number building', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

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

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

 -- Table: contacts 
columns : [['contact id', 'number'], ['customer id', 'number'], ['gender', 'text'], ['first name', 'text'], ['last name', 'text'], ['contact phone', 'text']]

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

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

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

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



Question: What are the name and phone of the customer with the most ordered product quantity?
SQL: SELECT  T1.customer_name ,  T1.customer_phone FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id JOIN order_items AS T3 ON T3.order_id  =  T2.order_id GROUP BY T1.customer_id ORDER BY sum(T3.order_quantity) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1 number building', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

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

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

 -- Table: contacts 
columns : [['contact id', 'number'], ['customer id', 'number'], ['gender', 'text'], ['first name', 'text'], ['last name', 'text'], ['contact phone', 'text']]

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

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

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

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



Question: Show the product type and name for the products with price higher than 1000 or lower than 500.
SQL: SELECT product_type_code ,  product_name FROM products WHERE product_price  >  1000 OR product_price  <  500
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: Find the name of dorms only for female (F gender).
SQL: SELECT dorm_name FROM dorm WHERE gender  =  'F'
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: What are the names of the all-female dorms?
SQL: SELECT dorm_name FROM dorm WHERE gender  =  'F'
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: Find the name of dorms that can accommodate more than 300 students.
SQL: SELECT dorm_name FROM dorm WHERE student_capacity  >  300
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: What are the names of all the dorms that can accomdate more than 300 students?
SQL: SELECT dorm_name FROM dorm WHERE student_capacity  >  300
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: How many female students (sex is F) whose age is below 25?
SQL: SELECT count(*) FROM student WHERE sex  =  'F' AND age  <  25
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: How many girl students who are younger than 25?
SQL: SELECT count(*) FROM student WHERE sex  =  'F' AND age  <  25
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: Find the first name of students who is older than 20.
SQL: SELECT fname FROM student WHERE age  >  20
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: What are the first names of all students who are older than 20?
SQL: SELECT fname FROM student WHERE age  >  20
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: Find the first name of students living in city PHL whose age is between 20 and 25.
SQL: SELECT fname FROM student WHERE city_code  =  'PHL' AND age BETWEEN 20 AND 25
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: What is the first name of the students who are in age 20 to 25 and living in PHL city?
SQL: SELECT fname FROM student WHERE city_code  =  'PHL' AND age BETWEEN 20 AND 25
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: How many dorms are there?
SQL: SELECT count(*) FROM dorm
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: How many dorms are in the database?
SQL: SELECT count(*) FROM dorm
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: Find the number of distinct amenities.
SQL: SELECT count(*) FROM dorm_amenity
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: How many diffrent dorm amenities are there?
SQL: SELECT count(*) FROM dorm_amenity
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: Find the total capacity of all dorms.
SQL: SELECT sum(student_capacity) FROM dorm
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: What is the total student capacity of all dorms?
SQL: SELECT sum(student_capacity) FROM dorm
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: How many students are there?
SQL: SELECT count(*) FROM student
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: How many students exist?
SQL: SELECT count(*) FROM student
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: Find the average age of all students living in the each city.
SQL: SELECT avg(age) ,  city_code FROM student GROUP BY city_code
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: What is the average age for each city and what are those cities?
SQL: SELECT avg(age) ,  city_code FROM student GROUP BY city_code
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: Find the average and total capacity of dorms for the students with gender X.
SQL: SELECT avg(student_capacity) ,  sum(student_capacity) FROM dorm WHERE gender  =  'X'
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: What is the average and total capacity for all dorms who are of gender X?
SQL: SELECT avg(student_capacity) ,  sum(student_capacity) FROM dorm WHERE gender  =  'X'
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: Find the number of dorms that have some amenity.
SQL: SELECT count(DISTINCT dormid) FROM has_amenity
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: How many dorms have amenities?
SQL: SELECT count(DISTINCT dormid) FROM has_amenity
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: Find the name of dorms that do not have any amenity
SQL: SELECT dorm_name FROM dorm WHERE dormid NOT IN (SELECT dormid FROM has_amenity)
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: What are the names of all the dorms that don't have any amenities?
SQL: SELECT dorm_name FROM dorm WHERE dormid NOT IN (SELECT dormid FROM has_amenity)
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: dorm 
columns : [['dorm id', 'number'], ['dorm name', 'text'], ['student capacity', 'number'], ['gender', 'text']]

 -- Table: dorm amenity 
columns : [['amenity id', 'number'], ['amenity name', 'text']]

 -- Table: has amenity 
columns : [['dorm id', 'number'], ['amenity id', 'number']]

 -- Table: lives in 
columns : [['student id', 'number'], ['dorm id', 'number'], ['room number', 'number']]

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



Question: Find the number of distinct gender for dorms.
SQL: SELECT count(DISTINCT gender) FROM dorm