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

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Count the number of Professors who have office in building NEB.
SQL: SELECT count(*) FROM Faculty WHERE Rank  =  "Professor" AND building  =  "NEB"
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Show the first name and last name for all the instructors.
SQL: SELECT fname ,  lname FROM Faculty WHERE Rank  =  "Instructor"
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: What are the first name and last name of all the instructors?
SQL: SELECT fname ,  lname FROM Faculty WHERE Rank  =  "Instructor"
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Show all the buildings along with the number of faculty members the buildings have.
SQL: SELECT building ,  count(*) FROM Faculty GROUP BY building
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: How many faculty members does each building have? List the result with the name of the building.
SQL: SELECT building ,  count(*) FROM Faculty GROUP BY building
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Which building has most faculty members?
SQL: SELECT building FROM Faculty GROUP BY building ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Find the building that has the largest number of faculty members.
SQL: SELECT building FROM Faculty GROUP BY building ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Show all the buildings that have at least 10 professors.
SQL: SELECT building FROM Faculty WHERE rank  =  "Professor" GROUP BY building HAVING count(*)  >=  10
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: In which buildings are there at least ten professors?
SQL: SELECT building FROM Faculty WHERE rank  =  "Professor" GROUP BY building HAVING count(*)  >=  10
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: For each faculty rank, show the number of faculty members who have it.
SQL: SELECT rank ,  count(*) FROM Faculty GROUP BY rank
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: How many faculty members do we have for each faculty rank?
SQL: SELECT rank ,  count(*) FROM Faculty GROUP BY rank
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Show all the ranks and the number of male and female faculty for each rank.
SQL: SELECT rank ,  sex ,  count(*) FROM Faculty GROUP BY rank ,  sex
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: How many faculty members do we have for each rank and gender?
SQL: SELECT rank ,  sex ,  count(*) FROM Faculty GROUP BY rank ,  sex
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Which rank has the smallest number of faculty members?
SQL: SELECT rank FROM Faculty GROUP BY rank ORDER BY count(*) ASC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Find the faculty rank that has the least members.
SQL: SELECT rank FROM Faculty GROUP BY rank ORDER BY count(*) ASC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Show the number of male and female assistant professors.
SQL: SELECT sex ,  count(*) FROM Faculty WHERE rank  =  "AsstProf" GROUP BY sex
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: How many male and female assistant professors do we have?
SQL: SELECT sex ,  count(*) FROM Faculty WHERE rank  =  "AsstProf" GROUP BY sex
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: What are the first name and last name of Linda Smith's advisor?
SQL: SELECT T1.fname ,  T1.lname FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor WHERE T2.fname  =  "Linda" AND T2.lname  =  "Smith"
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Who is the advisor of Linda Smith? Give me the first name and last name.
SQL: SELECT T1.fname ,  T1.lname FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor WHERE T2.fname  =  "Linda" AND T2.lname  =  "Smith"
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Show the ids of students whose advisors are professors.
SQL: SELECT T2.StuID FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor WHERE T1.rank  =  "Professor"
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Which students have professors as their advisors? Find their student ids.
SQL: SELECT T2.StuID FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor WHERE T1.rank  =  "Professor"
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Show first name and last name for all the students advised by Michael Goodrich.
SQL: SELECT T2.fname ,  T2.lname FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor WHERE T1.fname  =  "Michael" AND T1.lname  =  "Goodrich"
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Which students are advised by Michael Goodrich? Give me their first and last names.
SQL: SELECT T2.fname ,  T2.lname FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor WHERE T1.fname  =  "Michael" AND T1.lname  =  "Goodrich"
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Show the faculty id of each faculty member, along with the number of students he or she advises.
SQL: SELECT T1.FacID ,  count(*) FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.FacID
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: What are the faculty id and the number of students each faculty has?
SQL: SELECT T1.FacID ,  count(*) FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.FacID
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Show all the faculty ranks and the number of students advised by each rank.
SQL: SELECT T1.rank ,  count(*) FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.rank
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: How many students are advised by each rank of faculty? List the rank and the number of students.
SQL: SELECT T1.rank ,  count(*) FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.rank
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: What are the first and last name of the faculty who has the most students?
SQL: SELECT T1.fname ,  T1.lname FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.FacID ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Give me the the first and last name of the faculty who advises the most students.
SQL: SELECT T1.fname ,  T1.lname FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.FacID ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Show the ids for all the faculty members who have at least 2 students.
SQL: SELECT T1.FacID FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.FacID HAVING count(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Which faculty members advise two ore more students? Give me their faculty ids.
SQL: SELECT T1.FacID FROM Faculty AS T1 JOIN Student AS T2 ON T1.FacID  =  T2.advisor GROUP BY T1.FacID HAVING count(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Show ids for the faculty members who don't advise any student.
SQL: SELECT FacID FROM Faculty EXCEPT SELECT advisor FROM Student
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: What are the ids of the faculty members who do not advise any student.
SQL: SELECT FacID FROM Faculty EXCEPT SELECT advisor FROM Student
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: What activities do we have?
SQL: SELECT activity_name FROM Activity
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: List all the activities we have.
SQL: SELECT activity_name FROM Activity
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: How many activities do we have?
SQL: SELECT count(*) FROM Activity
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Find the number of activities available.
SQL: SELECT count(*) FROM Activity
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: How many faculty members participate in an activity?
SQL: SELECT count(DISTINCT FacID) FROM Faculty_participates_in
-- Language PostgreSQL
        -- Tables: 
             -- Table: activity 
columns : [['activity id', 'number'], ['activity name', 'text']]

 -- Table: participates in 
columns : [['student id', 'number'], ['activity id', 'number']]

 -- Table: faculty participates in 
columns : [['faculty id', 'number'], ['activity id', 'number']]

 -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: faculty 
columns : [['faculty id', 'number'], ['last name', 'text'], ['first name', 'text'], ['rank', 'text'], ['sex', 'text'], ['phone', 'number'], ['room', 'text'], ['building', 'text']]

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



Question: Give me the number of faculty members who participate in an activity
SQL: SELECT count(DISTINCT FacID) FROM Faculty_participates_in