sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', 'number']]

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



Question: Find the average age of students who live in the city with code "NYC" and have secretary votes in the spring election cycle.
SQL: SELECT avg(T1.Age) FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  SECRETARY_Vote WHERE T1.city_code  =  "NYC" AND T2.Election_Cycle  =  "Spring"
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', 'number']]

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



Question: What is the average age of students who have city code "NYC" and have secretary votes for the spring election cycle?
SQL: SELECT avg(T1.Age) FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  SECRETARY_Vote WHERE T1.city_code  =  "NYC" AND T2.Election_Cycle  =  "Spring"
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', 'number']]

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



Question: Find the average age of female (sex is F) students who have secretary votes in the spring election cycle.
SQL: SELECT avg(T1.Age) FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  SECRETARY_Vote WHERE T1.Sex  =  "F" AND T2.Election_Cycle  =  "Spring"
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', 'number']]

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



Question: What is the average age of the female students with secretary votes in the spring election cycle?
SQL: SELECT avg(T1.Age) FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  SECRETARY_Vote WHERE T1.Sex  =  "F" AND T2.Election_Cycle  =  "Spring"
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 distinct first names of all the students who have vice president votes and whose city code is not PIT.
SQL: SELECT DISTINCT T1.Fname FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  T2.VICE_PRESIDENT_Vote EXCEPT SELECT DISTINCT Fname FROM STUDENT WHERE city_code  =  "PIT"
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 distinct first names of the students who have vice president votes and reside in a city whose city code is not PIT?
SQL: SELECT DISTINCT T1.Fname FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  T2.VICE_PRESIDENT_Vote EXCEPT SELECT DISTINCT Fname FROM STUDENT WHERE city_code  =  "PIT"
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 distinct last names of all the students who have president votes and whose advisor is not 2192.
SQL: SELECT DISTINCT T1.LName FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  PRESIDENT_Vote EXCEPT SELECT DISTINCT LName FROM STUDENT WHERE Advisor  =  "2192"
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 distinct last names of the students who have president votes but do not have 2192 as the advisor?
SQL: SELECT DISTINCT T1.LName FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  PRESIDENT_Vote EXCEPT SELECT DISTINCT LName FROM STUDENT WHERE Advisor  =  "2192"
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 distinct last names of all the students who have president votes and whose advisor is 8741.
SQL: SELECT DISTINCT T1.LName FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  PRESIDENT_Vote INTERSECT SELECT DISTINCT LName FROM STUDENT WHERE Advisor  =  "8741"
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 distinct last names of the students who have president votes and have 8741 as the advisor?
SQL: SELECT DISTINCT T1.LName FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  PRESIDENT_Vote INTERSECT SELECT DISTINCT LName FROM STUDENT WHERE Advisor  =  "8741"
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 advisor, report the total number of students advised by him or her.
SQL: SELECT Advisor ,  count(*) FROM STUDENT GROUP BY Advisor
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', 'number']]

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



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

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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: Report all advisors that advise more than 2 students.
SQL: SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING COUNT(*)  >  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', 'number']]

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



Question: Which advisors have more than two students?
SQL: SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING COUNT(*)  >  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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: Report all majors that have less than 3 students.
SQL: SELECT Major FROM STUDENT GROUP BY Major HAVING COUNT(*)  <  3
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 majors only less than three students are studying?
SQL: SELECT Major FROM STUDENT GROUP BY Major HAVING COUNT(*)  <  3
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 election cycle, report the number of voting records.
SQL: SELECT Election_Cycle ,  count(*) FROM VOTING_RECORD GROUP BY Election_Cycle
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', 'number']]

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



Question: Count the number of voting records for each election cycle.
SQL: SELECT Election_Cycle ,  count(*) FROM VOTING_RECORD GROUP BY Election_Cycle
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', 'number']]

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



Question: Which major has the most students?
SQL: SELECT Major FROM STUDENT GROUP BY major ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 major that is studied by the largest number of students.
SQL: SELECT Major FROM STUDENT GROUP BY major ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 most common major among female (sex is F) students?
SQL: SELECT Major FROM STUDENT WHERE Sex  =  "F" GROUP BY major ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 major that is studied by the most female students.
SQL: SELECT Major FROM STUDENT WHERE Sex  =  "F" GROUP BY major ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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 city_code of the city that the most students live in?
SQL: SELECT city_code FROM STUDENT GROUP BY city_code ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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: Return the code of the city that has the most students.
SQL: SELECT city_code FROM STUDENT GROUP BY city_code ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', '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: Report the distinct advisors who have more than 2 students.
SQL: SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING count(*)  >  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: student 
columns : [['student id', 'number'], ['last name', 'text'], ['first name', 'text'], ['age', 'number'], ['sex', 'text'], ['major', 'number'], ['advisor', 'number'], ['city code', 'text']]

 -- Table: voting record 
columns : [['student id', 'number'], ['registration date', 'text'], ['election cycle', 'text'], ['president vote', 'number'], ['vice president vote', 'number'], ['secretary vote', 'number'], ['treasurer vote', 'number'], ['class president vote', 'number'], ['class senator vote', 'number']]

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



Question: Which advisors are advising more than 2 students?
SQL: SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING count(*)  >  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', 'text']]

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



Question: How many products are there?
SQL: SELECT count(*) FROM products
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 products.
SQL: SELECT count(*) FROM products
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 colors are there?
SQL: SELECT count(*) FROM ref_colors
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 colors.
SQL: SELECT count(*) FROM ref_colors
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 characteristics are there?
SQL: SELECT count(*) FROM CHARACTERISTICS
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 characteristics.
SQL: SELECT count(*) FROM CHARACTERISTICS
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 names and buying prices of all the products?
SQL: SELECT product_name ,  typical_buying_price FROM products
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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: Return the names and typical buying prices for all products.
SQL: SELECT product_name ,  typical_buying_price FROM products
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 the description of all the colors.
SQL: SELECT color_description FROM ref_colors
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 descriptions for each color?
SQL: SELECT color_description FROM ref_colors
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 names of all the product characteristics.
SQL: SELECT DISTINCT characteristic_name FROM CHARACTERISTICS
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 different names of the product characteristics?
SQL: SELECT DISTINCT characteristic_name FROM CHARACTERISTICS
-- Language PostgreSQL
        -- Tables: 
             -- Table: reference characteristic types 
columns : [['characteristic type code', 'text'], ['characteristic type description', 'text']]

 -- Table: reference colors 
columns : [['color code', 'text'], ['color description', 'text']]

 -- Table: reference product categories 
columns : [['product category code', 'text'], ['product category description', 'text'], ['unit of measure', 'text']]

 -- Table: characteristics 
columns : [['characteristic id', 'number'], ['characteristic type code', 'text'], ['characteristic data type', 'text'], ['characteristic name', 'text'], ['other characteristic details', 'text']]

 -- Table: products 
columns : [['product id', 'number'], ['color code', 'text'], ['product category code', 'text'], ['product name', 'text'], ['typical buying price', 'text'], ['typical selling price', 'text'], ['product description', 'text'], ['other product details', 'text']]

 -- Table: product characteristics 
columns : [['product id', 'number'], ['characteristic id', 'number'], ['product characteristic value', '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 names of products with category "Spices"?
SQL: SELECT product_name FROM products WHERE product_category_code  =  "Spices"