sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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: Please show the different statuses of cities and the average population of cities with each status.
SQL: SELECT Status ,  avg(Population) FROM city GROUP BY Status
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 statuses and average populations of each city?
SQL: SELECT Status ,  avg(Population) FROM city GROUP BY Status
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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: Please show the different statuses, ordered by the number of cities that have each.
SQL: SELECT Status FROM city GROUP BY Status ORDER BY COUNT(*) ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 different statuses of cities, ascending by frequency.
SQL: SELECT Status FROM city GROUP BY Status ORDER BY COUNT(*) ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', 'number']]

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



Question: List the most common type of Status across cities.
SQL: SELECT Status FROM city GROUP BY Status ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 status across all cities?
SQL: SELECT Status FROM city GROUP BY Status ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', 'number']]

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



Question: List the official names of cities that have not held any competition.
SQL: SELECT Official_Name FROM city WHERE City_ID NOT IN (SELECT Host_city_ID FROM farm_competition)
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 official names of cities that have not hosted a farm competition?
SQL: SELECT Official_Name FROM city WHERE City_ID NOT IN (SELECT Host_city_ID FROM farm_competition)
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', 'number']]

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



Question: Show the status shared by cities with population bigger than 1500 and smaller than 500.
SQL: SELECT Status FROM city WHERE Population  >  1500 INTERSECT SELECT Status FROM city WHERE Population  <  500
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 statuses correspond to both cities that have a population over 1500 and cities that have a population lower than 500?
SQL: SELECT Status FROM city WHERE Population  >  1500 INTERSECT SELECT Status FROM city WHERE Population  <  500
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 official names of cities with population bigger than 1500 or smaller than 500.
SQL: SELECT Official_Name FROM city WHERE Population  >  1500 OR Population  <  500
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 official names of cities that have population over 1500 or less than 500?
SQL: SELECT Official_Name FROM city WHERE Population  >  1500 OR Population  <  500
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', 'number']]

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



Question: Show the census ranking of cities whose status are not "Village".
SQL: SELECT Census_Ranking FROM city WHERE Status !=  "Village"
-- Language PostgreSQL
        -- Tables: 
             -- Table: city 
columns : [['city id', 'number'], ['official name', 'text'], ['status', 'text'], ['area km 2', 'number'], ['population', 'number'], ['census ranking', 'text']]

 -- Table: farm 
columns : [['farm id', 'number'], ['year', 'number'], ['total horses', 'number'], ['working horses', 'number'], ['total cattle', 'number'], ['oxen', 'number'], ['bulls', 'number'], ['cows', 'number'], ['pigs', 'number'], ['sheep and goats', 'number']]

 -- Table: farm competition 
columns : [['competition id', 'number'], ['year', 'number'], ['theme', 'text'], ['host city id', 'number'], ['hosts', 'text']]

 -- Table: competition record 
columns : [['competition id', 'number'], ['farm id', 'number'], ['rank', '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 census rankings of cities that do not have the status "Village"?
SQL: SELECT Census_Ranking FROM city WHERE Status !=  "Village"
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 course has most number of registered students?
SQL: SELECT T1.course_name FROM courses AS T1 JOIN student_course_registrations AS T2 ON T1.course_id = T2.course_Id GROUP BY T1.course_id ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 is the name of the course with the most registered students?
SQL: SELECT T1.course_name FROM courses AS T1 JOIN student_course_registrations AS T2 ON T1.course_id = T2.course_Id GROUP BY T1.course_id ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 is id of students who registered some courses but the least number of courses in these students?
SQL: SELECT student_id FROM student_course_registrations GROUP BY student_id ORDER BY count(*) LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 students who registered for some courses but had the least number of courses for all students?
SQL: SELECT student_id FROM student_course_registrations GROUP BY student_id ORDER BY count(*) LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 candidates?
SQL: SELECT T2.first_name ,  T2.last_name FROM candidates AS T1 JOIN people AS T2 ON T1.candidate_id = T2.person_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 names of all the candidates?
SQL: SELECT T2.first_name ,  T2.last_name FROM candidates AS T1 JOIN people AS T2 ON T1.candidate_id = T2.person_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 id of students who never attends courses?
SQL: SELECT student_id FROM students WHERE student_id NOT IN (SELECT student_id FROM student_course_attendance)
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 every student who has never attended a course?
SQL: SELECT student_id FROM students WHERE student_id NOT IN (SELECT student_id FROM student_course_attendance)
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 id of students who attended some courses?
SQL: SELECT student_id FROM student_course_attendance
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 all students who have attended at least one course?
SQL: SELECT student_id FROM student_course_attendance
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 all students for courses and what are the names of those courses?
SQL: SELECT T1.student_id ,  T2.course_name FROM student_course_registrations AS T1 JOIN courses AS T2 ON T1.course_id = T2.course_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 is detail of the student who most recently registered course?
SQL: SELECT T2.student_details FROM student_course_registrations AS T1 JOIN students AS T2 ON T1.student_id = T2.student_id ORDER BY T1.registration_date DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 details do we have on the students who registered for courses most recently?
SQL: SELECT T2.student_details FROM student_course_registrations AS T1 JOIN students AS T2 ON T1.student_id = T2.student_id ORDER BY T1.registration_date DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 attend course English?
SQL: SELECT count(*) FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T1.course_name = "English"
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 attending English courses?
SQL: SELECT count(*) FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T1.course_name = "English"
-- Language PostgreSQL
        -- Tables: 
             -- Table: addresses 
columns : [['address id', 'number'], ['line 1', 'text'], ['line 2', 'text'], ['city', 'text'], ['zip postcode', 'text'], ['state province county', 'text'], ['country', 'text']]

 -- Table: people 
columns : [['person id', 'number'], ['first name', 'text'], ['middle name', 'text'], ['last name', 'text'], ['cell mobile number', 'text'], ['email address', 'text'], ['login name', 'text'], ['password', 'text']]

 -- Table: students 
columns : [['student id', 'number'], ['student details', 'text']]

 -- Table: courses 
columns : [['course id', 'text'], ['course name', 'text'], ['course description', 'text'], ['other details', 'text']]

 -- Table: people addresses 
columns : [['person address id', 'number'], ['person id', 'number'], ['address id', 'number'], ['date from', 'time'], ['date to', 'time']]

 -- Table: student course registrations 
columns : [['student id', 'number'], ['course id', 'number'], ['registration date', 'time']]

 -- Table: student course attendance 
columns : [['student id', 'number'], ['course id', 'number'], ['date of attendance', 'time']]

 -- Table: candidates 
columns : [['candidate id', 'number'], ['candidate details', 'text']]

 -- Table: candidate assessments 
columns : [['candidate id', 'number'], ['qualification', 'text'], ['assessment date', 'time'], ['asessment outcome code', '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 courses do the student whose id is 171 attend?
SQL: SELECT count(*) FROM courses AS T1 JOIN student_course_attendance AS T2 ON T1.course_id = T2.course_id WHERE T2.student_id = 171