sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'text'], ['name', 'text'], ['nationality', 'text'], ['role', 'text']]

 -- Table: performance 
columns : [['performance id', 'number'], ['date', 'text'], ['host', 'text'], ['location', 'text'], ['attendance', 'number']]

 -- Table: member attendance 
columns : [['member id', 'number'], ['performance id', 'number'], ['num of pieces', '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 number of attendees for performances?
SQL: SELECT avg(Attendance) FROM performance
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'text'], ['name', 'text'], ['nationality', 'text'], ['role', 'text']]

 -- Table: performance 
columns : [['performance id', 'number'], ['date', 'text'], ['host', 'text'], ['location', 'text'], ['attendance', 'number']]

 -- Table: member attendance 
columns : [['member id', 'number'], ['performance id', 'number'], ['num of pieces', '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 date of the performance with the highest number of attendees?
SQL: SELECT Date FROM performance ORDER BY Attendance DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'text'], ['name', 'text'], ['nationality', 'text'], ['role', 'text']]

 -- Table: performance 
columns : [['performance id', 'number'], ['date', 'text'], ['host', 'text'], ['location', 'text'], ['attendance', 'number']]

 -- Table: member attendance 
columns : [['member id', 'number'], ['performance id', 'number'], ['num of pieces', '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 different locations and the number of performances at each location.
SQL: SELECT LOCATION ,  COUNT(*) FROM performance GROUP BY LOCATION
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'text'], ['name', 'text'], ['nationality', 'text'], ['role', 'text']]

 -- Table: performance 
columns : [['performance id', 'number'], ['date', 'text'], ['host', 'text'], ['location', 'text'], ['attendance', 'number']]

 -- Table: member attendance 
columns : [['member id', 'number'], ['performance id', 'number'], ['num of pieces', 'number']]

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



Question: Show the most common location of performances.
SQL: SELECT LOCATION FROM performance GROUP BY LOCATION ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'text'], ['name', 'text'], ['nationality', 'text'], ['role', 'text']]

 -- Table: performance 
columns : [['performance id', 'number'], ['date', 'text'], ['host', 'text'], ['location', 'text'], ['attendance', 'number']]

 -- Table: member attendance 
columns : [['member id', 'number'], ['performance id', 'number'], ['num of pieces', '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 locations that have at least two performances.
SQL: SELECT LOCATION FROM performance GROUP BY LOCATION HAVING COUNT(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'text'], ['name', 'text'], ['nationality', 'text'], ['role', 'text']]

 -- Table: performance 
columns : [['performance id', 'number'], ['date', 'text'], ['host', 'text'], ['location', 'text'], ['attendance', 'number']]

 -- Table: member attendance 
columns : [['member id', 'number'], ['performance id', 'number'], ['num of pieces', '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 locations that have both performances with more than 2000 attendees and performances with less than 1000 attendees.
SQL: SELECT LOCATION FROM performance WHERE Attendance  >  2000 INTERSECT SELECT LOCATION FROM performance WHERE Attendance  <  1000
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'text'], ['name', 'text'], ['nationality', 'text'], ['role', 'text']]

 -- Table: performance 
columns : [['performance id', 'number'], ['date', 'text'], ['host', 'text'], ['location', 'text'], ['attendance', 'number']]

 -- Table: member attendance 
columns : [['member id', 'number'], ['performance id', 'number'], ['num of pieces', 'number']]

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



Question: Show the names of members and the location of the performances they attended.
SQL: SELECT T2.Name ,  T3.Location FROM member_attendance AS T1 JOIN member AS T2 ON T1.Member_ID  =  T2.Member_ID JOIN performance AS T3 ON T1.Performance_ID  =  T3.Performance_ID
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'text'], ['name', 'text'], ['nationality', 'text'], ['role', 'text']]

 -- Table: performance 
columns : [['performance id', 'number'], ['date', 'text'], ['host', 'text'], ['location', 'text'], ['attendance', 'number']]

 -- Table: member attendance 
columns : [['member id', 'number'], ['performance id', 'number'], ['num of pieces', 'number']]

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



Question: Show the names of members and the location of performances they attended in ascending alphabetical order of their names.
SQL: SELECT T2.Name ,  T3.Location FROM member_attendance AS T1 JOIN member AS T2 ON T1.Member_ID  =  T2.Member_ID JOIN performance AS T3 ON T1.Performance_ID  =  T3.Performance_ID ORDER BY T2.Name ASC
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'text'], ['name', 'text'], ['nationality', 'text'], ['role', 'text']]

 -- Table: performance 
columns : [['performance id', 'number'], ['date', 'text'], ['host', 'text'], ['location', 'text'], ['attendance', 'number']]

 -- Table: member attendance 
columns : [['member id', 'number'], ['performance id', 'number'], ['num of pieces', '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 dates of performances with attending members whose roles are "Violin".
SQL: SELECT T3.Date FROM member_attendance AS T1 JOIN member AS T2 ON T1.Member_ID  =  T2.Member_ID JOIN performance AS T3 ON T1.Performance_ID  =  T3.Performance_ID WHERE T2.Role  =  "Violin"
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'text'], ['name', 'text'], ['nationality', 'text'], ['role', 'text']]

 -- Table: performance 
columns : [['performance id', 'number'], ['date', 'text'], ['host', 'text'], ['location', 'text'], ['attendance', 'number']]

 -- Table: member attendance 
columns : [['member id', 'number'], ['performance id', 'number'], ['num of pieces', 'number']]

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



Question: Show the names of members and the dates of performances they attended in descending order of attendance of the performances.
SQL: SELECT T2.Name ,  T3.Date FROM member_attendance AS T1 JOIN member AS T2 ON T1.Member_ID  =  T2.Member_ID JOIN performance AS T3 ON T1.Performance_ID  =  T3.Performance_ID ORDER BY T3.Attendance DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: member 
columns : [['member id', 'text'], ['name', 'text'], ['nationality', 'text'], ['role', 'text']]

 -- Table: performance 
columns : [['performance id', 'number'], ['date', 'text'], ['host', 'text'], ['location', 'text'], ['attendance', 'number']]

 -- Table: member attendance 
columns : [['member id', 'number'], ['performance id', 'number'], ['num of pieces', 'number']]

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



Question: List the names of members who did not attend any performance.
SQL: SELECT Name FROM member WHERE Member_ID NOT IN (SELECT Member_ID FROM member_attendance)
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 buildings which have rooms with capacity more than 50.
SQL: SELECT DISTINCT building FROM classroom WHERE capacity  >  50
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 distinct buildings with capacities of greater than 50?
SQL: SELECT DISTINCT building FROM classroom WHERE capacity  >  50
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 rooms that are not in the Lamberton building.
SQL: SELECT count(*) FROM classroom WHERE building != 'Lamberton'
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 classrooms are not in Lamberton?
SQL: SELECT count(*) FROM classroom WHERE building != 'Lamberton'
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 and building of the departments whose budget is more than the average budget?
SQL: SELECT dept_name ,  building FROM department WHERE budget  >  (SELECT avg(budget) FROM department)
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', 'text']]

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



Question: Give the name and building of the departments with greater than average budget.
SQL: SELECT dept_name ,  building FROM department WHERE budget  >  (SELECT avg(budget) FROM department)
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 room number of the rooms which can sit 50 to 100 students and their buildings.
SQL: SELECT building ,  room_number FROM classroom WHERE capacity BETWEEN 50 AND 100
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 room numbers and corresponding buildings for classrooms which can seat between 50 to 100 students?
SQL: SELECT building ,  room_number FROM classroom WHERE capacity BETWEEN 50 AND 100
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 name and building of the department with the highest budget.
SQL: SELECT dept_name ,  building FROM department ORDER BY budget DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 department name and corresponding building for the department with the greatest budget?
SQL: SELECT dept_name ,  building FROM department ORDER BY budget DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 student who has the highest total credits in the History department.
SQL: SELECT name FROM student WHERE dept_name  =  'History' ORDER BY tot_cred DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', 'text']]

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



Question: Give the name of the student in the History department with the most credits.
SQL: SELECT name FROM student WHERE dept_name  =  'History' ORDER BY tot_cred DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 rooms does the Lamberton building have?
SQL: SELECT count(*) FROM classroom WHERE building  =  'Lamberton'
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 classrooms in Lamberton.
SQL: SELECT count(*) FROM classroom WHERE building  =  'Lamberton'
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 have advisors?
SQL: SELECT count(DISTINCT s_id) FROM advisor
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 students who have advisors.
SQL: SELECT count(DISTINCT s_id) FROM advisor
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 departments offer courses?
SQL: SELECT count(DISTINCT dept_name) FROM course
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 departments which offer courses.
SQL: SELECT count(DISTINCT dept_name) FROM course
-- Language PostgreSQL
        -- Tables: 
             -- Table: classroom 
columns : [['building', 'text'], ['room number', 'text'], ['capacity', 'number']]

 -- Table: department 
columns : [['department name', 'text'], ['building', 'text'], ['budget', 'number']]

 -- Table: course 
columns : [['course id', 'text'], ['title', 'text'], ['department name', 'text'], ['credits', 'number']]

 -- Table: instructor 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['salary', 'number']]

 -- Table: section 
columns : [['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['building', 'text'], ['room number', 'text'], ['time slot id', 'text']]

 -- Table: teaches 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number']]

 -- Table: student 
columns : [['id', 'text'], ['name', 'text'], ['department name', 'text'], ['total credits', 'number']]

 -- Table: takes classes 
columns : [['id', 'text'], ['course id', 'text'], ['section id', 'text'], ['semester', 'text'], ['year', 'number'], ['grade', 'text']]

 -- Table: advisor 
columns : [['student id', 'text'], ['instructor id', 'text']]

 -- Table: time slot 
columns : [['time slot id', 'text'], ['day', 'text'], ['start hour', 'number'], ['start minute', 'number'], ['end hour', 'number'], ['end minute', 'number']]

 -- Table: prerequisite 
columns : [['course id', 'text'], ['prerequisite id', '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 different courses offered by Physics department?
SQL: SELECT count(DISTINCT course_id) FROM course WHERE dept_name  =  'Physics'