sert121/SpiderSQL huggingface dataset:
-- 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: restaurant 
columns : [['restaurant id', 'number'], ['restaurant name', 'text'], ['address', 'text'], ['rating', 'number']]

 -- Table: type of restaurant 
columns : [['restaurant id', 'number'], ['restaurant type id', 'number']]

 -- Table: restaurant type 
columns : [['restaurant type id', 'number'], ['restaurant type name', 'text'], ['restaurant type description', 'text']]

 -- Table: visits restaurant 
columns : [['student id', 'number'], ['restaurant id', 'number'], ['time', 'time'], ['spent', '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 long does student Linda Smith spend on the restaurant in total?
SQL: SELECT sum(Spent) FROM Student JOIN Visits_Restaurant ON Student.StuID = Visits_Restaurant.StuID WHERE Student.Fname = "Linda" AND Student.Lname = "Smith";
-- 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: restaurant 
columns : [['restaurant id', 'number'], ['restaurant name', 'text'], ['address', 'text'], ['rating', 'number']]

 -- Table: type of restaurant 
columns : [['restaurant id', 'number'], ['restaurant type id', 'number']]

 -- Table: restaurant type 
columns : [['restaurant type id', 'number'], ['restaurant type name', 'text'], ['restaurant type description', 'text']]

 -- Table: visits restaurant 
columns : [['student id', 'number'], ['restaurant id', 'number'], ['time', 'time'], ['spent', '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 times has the student Linda Smith visited Subway?
SQL: SELECT count(*) FROM Student JOIN Visits_Restaurant ON Student.StuID = Visits_Restaurant.StuID JOIN Restaurant ON Visits_Restaurant.ResID = Restaurant.ResID WHERE Student.Fname = "Linda" AND Student.Lname = "Smith" AND Restaurant.ResName = "Subway";
-- 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: restaurant 
columns : [['restaurant id', 'number'], ['restaurant name', 'text'], ['address', 'text'], ['rating', 'number']]

 -- Table: type of restaurant 
columns : [['restaurant id', 'number'], ['restaurant type id', 'number']]

 -- Table: restaurant type 
columns : [['restaurant type id', 'number'], ['restaurant type name', 'text'], ['restaurant type description', 'text']]

 -- Table: visits restaurant 
columns : [['student id', 'number'], ['restaurant id', 'number'], ['time', 'time'], ['spent', '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: When did Linda Smith visit Subway?
SQL: SELECT TIME FROM Student JOIN Visits_Restaurant ON Student.StuID = Visits_Restaurant.StuID JOIN Restaurant ON Visits_Restaurant.ResID = Restaurant.ResID WHERE Student.Fname = "Linda" AND Student.Lname = "Smith" AND Restaurant.ResName = "Subway";
-- 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: restaurant 
columns : [['restaurant id', 'number'], ['restaurant name', 'text'], ['address', 'text'], ['rating', 'number']]

 -- Table: type of restaurant 
columns : [['restaurant id', 'number'], ['restaurant type id', 'number']]

 -- Table: restaurant type 
columns : [['restaurant type id', 'number'], ['restaurant type name', 'text'], ['restaurant type description', 'text']]

 -- Table: visits restaurant 
columns : [['student id', 'number'], ['restaurant id', 'number'], ['time', 'time'], ['spent', '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: At which restaurant did the students spend the least amount of time? List restaurant and the time students spent on in total.
SQL: SELECT Restaurant.ResName ,  sum(Visits_Restaurant.Spent) FROM Visits_Restaurant JOIN Restaurant ON Visits_Restaurant.ResID = Restaurant.ResID GROUP BY Restaurant.ResID ORDER BY sum(Visits_Restaurant.Spent) ASC LIMIT 1;
-- 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: restaurant 
columns : [['restaurant id', 'number'], ['restaurant name', 'text'], ['address', 'text'], ['rating', 'number']]

 -- Table: type of restaurant 
columns : [['restaurant id', 'number'], ['restaurant type id', 'number']]

 -- Table: restaurant type 
columns : [['restaurant type id', 'number'], ['restaurant type name', 'text'], ['restaurant type description', 'text']]

 -- Table: visits restaurant 
columns : [['student id', 'number'], ['restaurant id', 'number'], ['time', 'time'], ['spent', '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: Which student visited restaurant most often? List student's first name and last name.
SQL: SELECT Student.Fname , Student.Lname FROM Student JOIN Visits_Restaurant ON Student.StuID = Visits_Restaurant.StuID GROUP BY Student.StuID ORDER BY count(*) DESC LIMIT 1;
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', 'time']]

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



Question: Find the ids of orders whose status is 'Success'.
SQL: SELECT actual_order_id FROM actual_orders WHERE order_status_code  =  'Success'
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', '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 price of the product that has been ordered the greatest number of times.
SQL: SELECT t1.product_name ,   t1.product_price FROM products AS t1 JOIN regular_order_products AS t2 ON t1.product_id  =  t2.product_id GROUP BY t2.product_id ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', '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 number of customers in total.
SQL: SELECT count(*) FROM customers
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', '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: How many different payment methods are there?
SQL: SELECT count(DISTINCT payment_method) FROM customers
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', '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: Show the details of all trucks in the order of their license number.
SQL: SELECT truck_details FROM trucks ORDER BY truck_licence_number
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', '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 of the most expensive product.
SQL: SELECT product_name FROM products ORDER BY product_price DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', '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 names of customers who are not living in the state of California.
SQL: SELECT customer_name FROM customers EXCEPT SELECT t1.customer_name FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id WHERE t3.state_province_county  =  'California'
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', 'time']]

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



Question: List the names and emails of customers who payed by Visa card.
SQL: SELECT customer_email ,  customer_name FROM customers WHERE payment_method  =  'Visa'
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', '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 names and phone numbers of customers living in California state.
SQL: SELECT t1.customer_name ,  t1.customer_phone FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id WHERE t3.state_province_county  =  'California'
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', '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 states which do not have any employee in their record.
SQL: SELECT state_province_county FROM addresses WHERE address_id NOT IN (SELECT employee_address_id FROM Employees)
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', 'time']]

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



Question: List the names, phone numbers, and emails of all customers sorted by their dates of becoming customers.
SQL: SELECT customer_name ,  customer_phone ,  customer_email FROM Customers ORDER BY date_became_customer
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', '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 of the first 5 customers.
SQL: SELECT customer_name FROM Customers ORDER BY date_became_customer LIMIT 5
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', '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 payment method that is used most frequently.
SQL: SELECT payment_method FROM Customers GROUP BY payment_method ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', 'time']]

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



Question: List the names of all routes in alphabetic order.
SQL: SELECT route_name FROM Delivery_Routes ORDER BY route_name
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', '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 of route that has the highest number of deliveries.
SQL: SELECT t1.route_name FROM Delivery_Routes AS t1 JOIN Delivery_Route_Locations AS t2 ON t1.route_id  =  t2.route_id GROUP BY t1.route_id ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: products 
columns : [['product id', 'number'], ['product name', 'text'], ['product price', 'number'], ['product description', 'text']]

 -- Table: addresses 
columns : [['address id', 'number'], ['address details', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: customers 
columns : [['customer id', 'number'], ['payment method', 'text'], ['customer name', 'text'], ['customer phone', 'text'], ['customer email', 'text'], ['date became customer', 'time']]

 -- Table: regular orders 
columns : [['regular order id', 'number'], ['distributer id', 'number']]

 -- Table: regular order products 
columns : [['regular order id', 'number'], ['product id', 'number']]

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

 -- Table: actual order products 
columns : [['actual order id', 'number'], ['product id', 'number']]

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

 -- Table: delivery routes 
columns : [['route id', 'number'], ['route name', 'text'], ['other route details', 'text']]

 -- Table: delivery route locations 
columns : [['location code', 'text'], ['route id', 'number'], ['location address id', 'number'], ['location name', 'text']]

 -- Table: trucks 
columns : [['truck id', 'number'], ['truck licence number', 'text'], ['truck details', 'text']]

 -- Table: employees 
columns : [['employee id', 'number'], ['employee address id', 'number'], ['employee name', 'text'], ['employee phone', 'text']]

 -- Table: order deliveries 
columns : [['location code', 'text'], ['actual order id', 'number'], ['delivery status code', 'text'], ['driver employee id', 'number'], ['truck id', 'number'], ['delivery date', 'time']]

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



Question: List the state names and the number of customers living in each state.
SQL: SELECT t2.state_province_county ,  count(*) FROM customer_addresses AS t1 JOIN addresses AS t2 ON t1.address_id  =  t2.address_id GROUP BY t2.state_province_county
-- Language PostgreSQL
        -- Tables: 
             -- Table: institution 
columns : [['institution id', 'number'], ['name', 'text'], ['country', 'text']]

 -- Table: authors 
columns : [['author id', 'number'], ['last name', 'text'], ['first name', 'text']]

 -- Table: papers 
columns : [['paper id', 'number'], ['title', 'text']]

 -- Table: authorship count 
columns : [['author id', 'number'], ['institution id', 'number'], ['paper id', 'number'], ['author count', '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 authors are there?
SQL: SELECT count(*) FROM authors
-- Language PostgreSQL
        -- Tables: 
             -- Table: institution 
columns : [['institution id', 'number'], ['name', 'text'], ['country', 'text']]

 -- Table: authors 
columns : [['author id', 'number'], ['last name', 'text'], ['first name', 'text']]

 -- Table: papers 
columns : [['paper id', 'number'], ['title', 'text']]

 -- Table: authorship count 
columns : [['author id', 'number'], ['institution id', 'number'], ['paper id', 'number'], ['author count', '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: Count the number of authors.
SQL: SELECT count(*) FROM authors