sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 of Bob's friends?
SQL: SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend  =  'Bob'
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 names of females who are friends with Zach
SQL: SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend  =  'Zach' AND T1.gender  =  'female'
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 females who are friends with Zach?
SQL: SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend  =  'Zach' AND T1.gender  =  'female'
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 female friends of Alice.
SQL: SELECT T2.friend FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T2.name  =  'Alice' AND T1.gender  =  'female'
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 all the friends of Alice who are female?
SQL: SELECT T2.friend FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T2.name  =  'Alice' AND T1.gender  =  'female'
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 male friend of Alice whose job is a doctor?
SQL: SELECT T2.friend FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T2.name  =  'Alice' AND T1.gender  =  'male' AND T1.job  =  'doctor'
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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: Who are the friends of Alice that are doctors?
SQL: SELECT T2.friend FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T2.name  =  'Alice' AND T1.gender  =  'male' AND T1.job  =  'doctor'
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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: Who has a friend that is from new york city?
SQL: SELECT T2.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T1.city  =  'new york city'
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 friends who are from New York?
SQL: SELECT T2.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T1.city  =  'new york city'
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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: Who has friends that are younger than the average age?
SQL: SELECT DISTINCT T2.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T1.age  <  (SELECT avg(age) FROM person)
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 different names of friends who are younger than the average age for a friend?
SQL: SELECT DISTINCT T2.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T1.age  <  (SELECT avg(age) FROM person)
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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: Who has friends that are older than the average age? Print their friends and their ages as well
SQL: SELECT DISTINCT T2.name ,  T2.friend ,  T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T1.age  >  (SELECT avg(age) FROM person)
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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: Whare the names, friends, and ages of all people who are older than the average age of a person?
SQL: SELECT DISTINCT T2.name ,  T2.friend ,  T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T1.age  >  (SELECT avg(age) FROM person)
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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: Who is the friend of Zach with longest year relationship?
SQL: SELECT friend FROM PersonFriend WHERE name =  'Zach' AND YEAR  =  (SELECT max(YEAR) FROM PersonFriend WHERE name =  'Zach')
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 friend of Zach has the longest-lasting friendship?
SQL: SELECT friend FROM PersonFriend WHERE name =  'Zach' AND YEAR  =  (SELECT max(YEAR) FROM PersonFriend WHERE name =  'Zach')
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 age of the friend of Zach with longest year relationship?
SQL: SELECT T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T2.name =  'Zach' AND T2.year  =  (SELECT max(YEAR) FROM PersonFriend WHERE name =  'Zach')
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 ages of all of Zach's friends who are in the longest relationship?
SQL: SELECT T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T2.name =  'Zach' AND T2.year  =  (SELECT max(YEAR) FROM PersonFriend WHERE name =  'Zach')
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 persons who are friends with Alice for the shortest years.
SQL: SELECT name FROM PersonFriend WHERE friend =  'Alice' AND YEAR  =  (SELECT min(YEAR) FROM PersonFriend WHERE friend =  'Alice')
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 people who are friends with Alice for the shortest amount of time?
SQL: SELECT name FROM PersonFriend WHERE friend =  'Alice' AND YEAR  =  (SELECT min(YEAR) FROM PersonFriend WHERE friend =  'Alice')
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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, age, and job title of persons who are friends with Alice for the longest years.
SQL: SELECT T1.name ,  T1.age ,  T1.job FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend =  'Alice' AND T2.year  =  (SELECT max(YEAR) FROM PersonFriend WHERE friend =  'Alice')
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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, ages, and jobs of all people who are friends with Alice for the longest amount of time?
SQL: SELECT T1.name ,  T1.age ,  T1.job FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend =  'Alice' AND T2.year  =  (SELECT max(YEAR) FROM PersonFriend WHERE friend =  'Alice')
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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: Who is the person that has no friend?
SQL: SELECT name FROM person EXCEPT SELECT name FROM PersonFriend
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 people who do not have friends?
SQL: SELECT name FROM person EXCEPT SELECT name FROM PersonFriend
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 person whose friends have the oldest average age?
SQL: SELECT T2.name ,  avg(T1.age) FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend GROUP BY T2.name ORDER BY avg(T1.age) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 name of the person who has the oldest average age for their friends, and what is that average age?
SQL: SELECT T2.name ,  avg(T1.age) FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend GROUP BY T2.name ORDER BY avg(T1.age) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 number of people who has no friend living in the city of Austin.
SQL: SELECT count(DISTINCT name) FROM PersonFriend WHERE friend NOT IN (SELECT name FROM person WHERE city  =  'Austin')
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 number of people who have no friends living in Austin?
SQL: SELECT count(DISTINCT name) FROM PersonFriend WHERE friend NOT IN (SELECT name FROM person WHERE city  =  'Austin')
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 Alice's friends of friends.
SQL: SELECT DISTINCT T4.name FROM PersonFriend AS T1 JOIN Person AS T2 ON T1.name  =  T2.name JOIN PersonFriend AS T3 ON T1.friend  =  T3.name JOIN PersonFriend AS T4 ON T3.friend  =  T4.name WHERE T2.name  =  'Alice' AND T4.name != 'Alice'
-- Language PostgreSQL
        -- Tables: 
             -- Table: person 
columns : [['name', 'text'], ['age', 'number'], ['city', 'text'], ['gender', 'text'], ['job', 'text']]

 -- Table: person friend 
columns : [['name', 'text'], ['friend', 'text'], ['year', '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 of Alice's friends of friends?
SQL: SELECT DISTINCT T4.name FROM PersonFriend AS T1 JOIN Person AS T2 ON T1.name  =  T2.name JOIN PersonFriend AS T3 ON T1.friend  =  T3.name JOIN PersonFriend AS T4 ON T3.friend  =  T4.name WHERE T2.name  =  'Alice' AND T4.name != 'Alice'
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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 members are there?
SQL: SELECT count(*) FROM member
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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: List the names of members in ascending alphabetical order.
SQL: SELECT Name FROM member ORDER BY Name ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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 and countries of members?
SQL: SELECT Name ,  Country FROM member
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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: Show the names of members whose country is "United States" or "Canada".
SQL: SELECT Name FROM member WHERE Country  =  "United States" OR Country  =  "Canada"
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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: Show the different countries and the number of members from each.
SQL: SELECT Country ,  COUNT(*) FROM member GROUP BY Country
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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: Show the most common country across members.
SQL: SELECT Country FROM member GROUP BY Country ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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 countries have more than two members?
SQL: SELECT Country FROM member GROUP BY Country HAVING COUNT(*)  >  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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: Show the leader names and locations of colleges.
SQL: SELECT Leader_Name ,  College_Location FROM college
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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: Show the names of members and names of colleges they go to.
SQL: SELECT T2.Name ,  T1.Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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: Show the names of members and the locations of colleges they go to in ascending alphabetical order of member names.
SQL: SELECT T2.Name ,  T1.College_Location FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID ORDER BY T2.Name ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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: Show the distinct leader names of colleges associated with members from country "Canada".
SQL: SELECT DISTINCT T1.Leader_Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID WHERE T2.Country  =  "Canada"
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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: Show the names of members and the decoration themes they have.
SQL: SELECT T1.Name ,  T2.Decoration_Theme FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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: Show the names of members that have a rank in round higher than 3.
SQL: SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID WHERE T2.Rank_in_Round  >  3
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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: Show the names of members in ascending order of their rank in rounds.
SQL: SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID ORDER BY Rank_in_Round ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: college 
columns : [['college id', 'number'], ['name', 'text'], ['leader name', 'text'], ['college location', 'text']]

 -- Table: member 
columns : [['member id', 'number'], ['name', 'text'], ['country', 'text'], ['college id', 'number']]

 -- Table: round 
columns : [['round id', 'number'], ['member id', 'number'], ['decoration theme', 'text'], ['rank in round', '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: List the names of members who did not participate in any round.
SQL: SELECT Name FROM member WHERE Member_ID NOT IN (SELECT Member_ID FROM round)
-- Language PostgreSQL
        -- Tables: 
             -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: users 
columns : [['user id', 'number'], ['role code', 'text'], ['user name', 'text'], ['user login', 'text'], ['password', 'text']]

 -- Table: document structures 
columns : [['document structure code', 'text'], ['parent document structure code', 'text'], ['document structure description', 'text']]

 -- Table: functional areas 
columns : [['functional area code', 'text'], ['parent functional area code', 'text'], ['functional area description', 'text']]

 -- Table: images 
columns : [['image id', 'number'], ['image alt text', 'text'], ['image name', 'text'], ['image url', 'text']]

 -- Table: documents 
columns : [['document code', 'text'], ['document structure code', 'text'], ['document type code', 'text'], ['access count', 'number'], ['document name', 'text']]

 -- Table: document functional areas 
columns : [['document code', 'text'], ['functional area code', 'text']]

 -- Table: document sections 
columns : [['section id', 'number'], ['document code', 'text'], ['section sequence', 'number'], ['section code', 'text'], ['section title', 'text']]

 -- Table: document sections images 
columns : [['section id', 'number'], ['image id', '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 and access counts of all documents, in alphabetic order of the document name.
SQL: SELECT document_name ,  access_count FROM documents ORDER BY document_name
-- Language PostgreSQL
        -- Tables: 
             -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: users 
columns : [['user id', 'number'], ['role code', 'text'], ['user name', 'text'], ['user login', 'text'], ['password', 'text']]

 -- Table: document structures 
columns : [['document structure code', 'text'], ['parent document structure code', 'text'], ['document structure description', 'text']]

 -- Table: functional areas 
columns : [['functional area code', 'text'], ['parent functional area code', 'text'], ['functional area description', 'text']]

 -- Table: images 
columns : [['image id', 'number'], ['image alt text', 'text'], ['image name', 'text'], ['image url', 'text']]

 -- Table: documents 
columns : [['document code', 'text'], ['document structure code', 'text'], ['document type code', 'text'], ['access count', 'number'], ['document name', 'text']]

 -- Table: document functional areas 
columns : [['document code', 'text'], ['functional area code', 'text']]

 -- Table: document sections 
columns : [['section id', 'number'], ['document code', 'text'], ['section sequence', 'number'], ['section code', 'text'], ['section title', 'text']]

 -- Table: document sections images 
columns : [['section id', 'number'], ['image id', '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 documents, as well as the access counts of each, ordered alphabetically?
SQL: SELECT document_name ,  access_count FROM documents ORDER BY document_name
-- Language PostgreSQL
        -- Tables: 
             -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: users 
columns : [['user id', 'number'], ['role code', 'text'], ['user name', 'text'], ['user login', 'text'], ['password', 'text']]

 -- Table: document structures 
columns : [['document structure code', 'text'], ['parent document structure code', 'text'], ['document structure description', 'text']]

 -- Table: functional areas 
columns : [['functional area code', 'text'], ['parent functional area code', 'text'], ['functional area description', 'text']]

 -- Table: images 
columns : [['image id', 'number'], ['image alt text', 'text'], ['image name', 'text'], ['image url', 'text']]

 -- Table: documents 
columns : [['document code', 'text'], ['document structure code', 'text'], ['document type code', 'text'], ['access count', 'number'], ['document name', 'text']]

 -- Table: document functional areas 
columns : [['document code', 'text'], ['functional area code', 'text']]

 -- Table: document sections 
columns : [['section id', 'number'], ['document code', 'text'], ['section sequence', 'number'], ['section code', 'text'], ['section title', 'text']]

 -- Table: document sections images 
columns : [['section id', 'number'], ['image id', '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 the document that has been accessed the greatest number of times, as well as the count of how many times it has been accessed?
SQL: SELECT document_name ,  access_count FROM documents ORDER BY access_count DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: users 
columns : [['user id', 'number'], ['role code', 'text'], ['user name', 'text'], ['user login', 'text'], ['password', 'text']]

 -- Table: document structures 
columns : [['document structure code', 'text'], ['parent document structure code', 'text'], ['document structure description', 'text']]

 -- Table: functional areas 
columns : [['functional area code', 'text'], ['parent functional area code', 'text'], ['functional area description', 'text']]

 -- Table: images 
columns : [['image id', 'number'], ['image alt text', 'text'], ['image name', 'text'], ['image url', 'text']]

 -- Table: documents 
columns : [['document code', 'text'], ['document structure code', 'text'], ['document type code', 'text'], ['access count', 'number'], ['document name', 'text']]

 -- Table: document functional areas 
columns : [['document code', 'text'], ['functional area code', 'text']]

 -- Table: document sections 
columns : [['section id', 'number'], ['document code', 'text'], ['section sequence', 'number'], ['section code', 'text'], ['section title', 'text']]

 -- Table: document sections images 
columns : [['section id', 'number'], ['image id', '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 name of the document which has been accessed the most times, as well as the number of times it has been accessed?
SQL: SELECT document_name ,  access_count FROM documents ORDER BY access_count DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: users 
columns : [['user id', 'number'], ['role code', 'text'], ['user name', 'text'], ['user login', 'text'], ['password', 'text']]

 -- Table: document structures 
columns : [['document structure code', 'text'], ['parent document structure code', 'text'], ['document structure description', 'text']]

 -- Table: functional areas 
columns : [['functional area code', 'text'], ['parent functional area code', 'text'], ['functional area description', 'text']]

 -- Table: images 
columns : [['image id', 'number'], ['image alt text', 'text'], ['image name', 'text'], ['image url', 'text']]

 -- Table: documents 
columns : [['document code', 'text'], ['document structure code', 'text'], ['document type code', 'text'], ['access count', 'number'], ['document name', 'text']]

 -- Table: document functional areas 
columns : [['document code', 'text'], ['functional area code', 'text']]

 -- Table: document sections 
columns : [['section id', 'number'], ['document code', 'text'], ['section sequence', 'number'], ['section code', 'text'], ['section title', 'text']]

 -- Table: document sections images 
columns : [['section id', 'number'], ['image id', '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 types of documents with more than 4 documents.
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*)  >  4
-- Language PostgreSQL
        -- Tables: 
             -- Table: roles 
columns : [['role code', 'text'], ['role description', 'text']]

 -- Table: users 
columns : [['user id', 'number'], ['role code', 'text'], ['user name', 'text'], ['user login', 'text'], ['password', 'text']]

 -- Table: document structures 
columns : [['document structure code', 'text'], ['parent document structure code', 'text'], ['document structure description', 'text']]

 -- Table: functional areas 
columns : [['functional area code', 'text'], ['parent functional area code', 'text'], ['functional area description', 'text']]

 -- Table: images 
columns : [['image id', 'number'], ['image alt text', 'text'], ['image name', 'text'], ['image url', 'text']]

 -- Table: documents 
columns : [['document code', 'text'], ['document structure code', 'text'], ['document type code', 'text'], ['access count', 'number'], ['document name', 'text']]

 -- Table: document functional areas 
columns : [['document code', 'text'], ['functional area code', 'text']]

 -- Table: document sections 
columns : [['section id', 'number'], ['document code', 'text'], ['section sequence', 'number'], ['section code', 'text'], ['section title', 'text']]

 -- Table: document sections images 
columns : [['section id', 'number'], ['image id', '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 codes of types of documents of which there are for or more?
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*)  >  4