sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 name, address road, and city for all branches sorted by open year.
SQL: SELECT name ,  address_road ,  city FROM branch ORDER BY open_year
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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, address roads, and cities of the branches ordered by opening year?
SQL: SELECT name ,  address_road ,  city FROM branch ORDER BY open_year
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 names for top three branches with most number of membership?
SQL: SELECT name FROM branch ORDER BY membership_amount DESC LIMIT 3
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 for the 3 branches that have the most memberships?
SQL: SELECT name FROM branch ORDER BY membership_amount DESC LIMIT 3
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 all distinct city where branches with at least 100 memberships are located.
SQL: SELECT DISTINCT city FROM branch WHERE membership_amount  >=  100
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 cities that have more than 100 memberships?
SQL: SELECT DISTINCT city FROM branch WHERE membership_amount  >=  100
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 all open years when at least two shops are opened.
SQL: SELECT open_year FROM branch GROUP BY open_year HAVING count(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 opening years in which at least two shops opened?
SQL: SELECT open_year FROM branch GROUP BY open_year HAVING count(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 minimum and maximum amount of memberships for all branches opened in 2011 or located at city London.
SQL: SELECT min(membership_amount) ,  max(membership_amount) FROM branch WHERE open_year  =  2011 OR city  =  'London'
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 minimum and maximum membership amounts for all branches that either opened in 2011 or are located in London?
SQL: SELECT min(membership_amount) ,  max(membership_amount) FROM branch WHERE open_year  =  2011 OR city  =  'London'
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 city and the number of branches opened before 2010 for each city.
SQL: SELECT city ,  count(*) FROM branch WHERE open_year  <  2010 GROUP BY city
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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: For each city, how many branches opened before 2010?
SQL: SELECT city ,  count(*) FROM branch WHERE open_year  <  2010 GROUP BY city
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 different levels do members have?
SQL: SELECT count(DISTINCT LEVEL) FROM member
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 membership levels?
SQL: SELECT count(DISTINCT LEVEL) FROM member
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 card number, name, and hometown for all members in a descending order of level.
SQL: SELECT card_number ,  name ,  hometown FROM member ORDER BY LEVEL DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 card numbers, names, and hometowns of every member ordered by descending level?
SQL: SELECT card_number ,  name ,  hometown FROM member ORDER BY LEVEL DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 membership level with most number of members.
SQL: SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 membership level with the most people?
SQL: SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 all member names and registered branch names sorted by register year.
SQL: SELECT T3.name ,  T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id ORDER BY T1.register_year
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', 'number']]

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



Question: What are the names of the members and branches at which they are registered sorted by year of registration?
SQL: SELECT T3.name ,  T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id ORDER BY T1.register_year
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 all branch names with the number of members in each branch registered after 2015.
SQL: SELECT T2.name ,  count(*) FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  >  2015 GROUP BY T2.branch_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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: For each branch id, what are the names of the branches that were registered after 2015?
SQL: SELECT T2.name ,  count(*) FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  >  2015 GROUP BY T2.branch_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 member names without any registered branch.
SQL: SELECT name FROM member WHERE member_id NOT IN (SELECT member_id FROM membership_register_branch)
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', 'number']]

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



Question: What are the names of the members that have never registered at any branch?
SQL: SELECT name FROM member WHERE member_id NOT IN (SELECT member_id FROM membership_register_branch)
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 branch name and city without any registered members.
SQL: SELECT name ,  city FROM branch WHERE branch_id NOT IN (SELECT branch_id FROM membership_register_branch)
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 cities of the branches that do not have any registered members?
SQL: SELECT name ,  city FROM branch WHERE branch_id NOT IN (SELECT branch_id FROM membership_register_branch)
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 and open year for the branch with most number of memberships registered in 2016?
SQL: SELECT T2.name ,  T2.open_year FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  =  2016 GROUP BY T2.branch_id ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 and opening year for the branch that registered the most members in 2016?
SQL: SELECT T2.name ,  T2.open_year FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  =  2016 GROUP BY T2.branch_id ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 member name and hometown who registered a branch in 2016.
SQL: SELECT T2.name ,  T2.hometown FROM membership_register_branch AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T1.register_year  =  2016
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 member names and hometowns of those who registered at a branch in 2016?
SQL: SELECT T2.name ,  T2.hometown FROM membership_register_branch AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T1.register_year  =  2016
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 all city with a branch opened in 2001 and a branch with more than 100 membership.
SQL: SELECT city FROM branch WHERE open_year  =  2001 AND membership_amount  >  100
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 cities that have a branch that opened in 2001 and a branch with more than 100 members?
SQL: SELECT city FROM branch WHERE open_year  =  2001 AND membership_amount  >  100
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 all cities without a branch having more than 100 memberships.
SQL: SELECT city FROM branch EXCEPT SELECT city FROM branch WHERE membership_amount  >  100
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 cities that do not have any branches with more than 100 members?
SQL: SELECT city FROM branch EXCEPT SELECT city FROM branch WHERE membership_amount  >  100
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 sum of total pounds of purchase in year 2018 for all branches in London?
SQL: SELECT sum(total_pounds) FROM purchase AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T2.city  =  'London' AND T1.year  =  2018
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 total pounds were purchased in the year 2018 at all London branches?
SQL: SELECT sum(total_pounds) FROM purchase AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T2.city  =  'London' AND T1.year  =  2018
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 purchases for members with level 6?
SQL: SELECT count(*) FROM purchase AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T2.level  =  6
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 total purchases for members rated at level 6?
SQL: SELECT count(*) FROM purchase AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T2.level  =  6
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 branches where have some members whose hometown is in Louisville, Kentucky and some in Hiram, Georgia.
SQL: SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id WHERE T3.Hometown  =  'Louisville ,  Kentucky' INTERSECT SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id WHERE T3.Hometown  =  'Hiram ,  Georgia'
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', 'number']]

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



Question: What are the names of the branches that have some members with a hometown in Louisville, Kentucky and also those from Hiram, Goergia?
SQL: SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id WHERE T3.Hometown  =  'Louisville ,  Kentucky' INTERSECT SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id WHERE T3.Hometown  =  'Hiram ,  Georgia'
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 card number of all members whose hometown address includes word "Kentucky".
SQL: SELECT card_number FROM member WHERE Hometown LIKE "%Kentucky%"
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'number'], ['card number', 'text'], ['name', 'text'], ['hometown', 'text'], ['level', 'number']]

 -- Table: branch 
columns : [['branch id', 'number'], ['name', 'text'], ['open year', 'text'], ['address road', 'text'], ['city', 'text'], ['membership amount', 'text']]

 -- Table: membership register branch 
columns : [['member id', 'number'], ['branch id', 'text'], ['register year', 'text']]

 -- Table: purchase 
columns : [['member id', 'number'], ['branch id', 'text'], ['year', 'text'], ['total pounds', '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 card numbers of members from Kentucky?
SQL: SELECT card_number FROM member WHERE Hometown LIKE "%Kentucky%"