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

 -- Table: bridge 
columns : [['architect id', 'number'], ['id', 'number'], ['name', 'text'], ['location', 'text'], ['length meters', 'number'], ['length feet', 'number']]

 -- Table: mill 
columns : [['architect id', 'number'], ['id', 'number'], ['location', 'text'], ['name', 'text'], ['type', 'text'], ['built year', 'number'], ['notes', '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 names and nationalities of the architects who have ever built a mill?
SQL: SELECT DISTINCT T1.name ,  T1.nationality FROM architect AS T1 JOIN mill AS t2 ON T1.id  =  T2.architect_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: architect 
columns : [['id', 'text'], ['name', 'text'], ['nationality', 'text'], ['gender', 'text']]

 -- Table: bridge 
columns : [['architect id', 'number'], ['id', 'number'], ['name', 'text'], ['location', 'text'], ['length meters', 'number'], ['length feet', 'number']]

 -- Table: mill 
columns : [['architect id', 'number'], ['id', 'number'], ['location', 'text'], ['name', 'text'], ['type', 'text'], ['built year', 'number'], ['notes', 'text']]

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



Question: What are the names of the mills which are not located in 'Donceel'?
SQL: SELECT name FROM mill WHERE LOCATION != 'Donceel'
-- Language PostgreSQL
        -- Tables: 
             -- Table: architect 
columns : [['id', 'text'], ['name', 'text'], ['nationality', 'text'], ['gender', 'text']]

 -- Table: bridge 
columns : [['architect id', 'number'], ['id', 'number'], ['name', 'text'], ['location', 'text'], ['length meters', 'number'], ['length feet', 'number']]

 -- Table: mill 
columns : [['architect id', 'number'], ['id', 'number'], ['location', 'text'], ['name', 'text'], ['type', 'text'], ['built year', 'number'], ['notes', '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 types of mills that are built by American or Canadian architects?
SQL: SELECT DISTINCT T1.type FROM mill AS T1 JOIN architect AS t2 ON T1.architect_id  =  T2.id WHERE T2.nationality  =  'American' OR T2.nationality  =  'Canadian'
-- Language PostgreSQL
        -- Tables: 
             -- Table: architect 
columns : [['id', 'text'], ['name', 'text'], ['nationality', 'text'], ['gender', 'text']]

 -- Table: bridge 
columns : [['architect id', 'number'], ['id', 'number'], ['name', 'text'], ['location', 'text'], ['length meters', 'number'], ['length feet', 'number']]

 -- Table: mill 
columns : [['architect id', 'number'], ['id', 'number'], ['location', 'text'], ['name', 'text'], ['type', 'text'], ['built year', 'number'], ['notes', '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 and names of the architects who built at least 3 bridges ?
SQL: SELECT T1.id ,  T1.name FROM architect AS T1 JOIN bridge AS T2 ON T1.id  =  T2.architect_id GROUP BY T1.id HAVING count(*)  >=  3
-- Language PostgreSQL
        -- Tables: 
             -- Table: architect 
columns : [['id', 'text'], ['name', 'text'], ['nationality', 'text'], ['gender', 'text']]

 -- Table: bridge 
columns : [['architect id', 'number'], ['id', 'number'], ['name', 'text'], ['location', 'text'], ['length meters', 'number'], ['length feet', 'number']]

 -- Table: mill 
columns : [['architect id', 'number'], ['id', 'number'], ['location', 'text'], ['name', 'text'], ['type', 'text'], ['built year', 'number'], ['notes', '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 id, name and nationality of the architect who built most mills?
SQL: SELECT T1.id ,  T1.name ,  T1.nationality FROM architect AS T1 JOIN mill AS T2 ON T1.id  =  T2.architect_id GROUP BY T1.id ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: architect 
columns : [['id', 'text'], ['name', 'text'], ['nationality', 'text'], ['gender', 'text']]

 -- Table: bridge 
columns : [['architect id', 'number'], ['id', 'number'], ['name', 'text'], ['location', 'text'], ['length meters', 'number'], ['length feet', 'number']]

 -- Table: mill 
columns : [['architect id', 'number'], ['id', 'number'], ['location', 'text'], ['name', 'text'], ['type', 'text'], ['built year', 'number'], ['notes', '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, names and genders of the architects who built two bridges or one mill?
SQL: SELECT T1.id ,  T1.name ,  T1.gender FROM architect AS T1 JOIN bridge AS T2 ON T1.id  =  T2.architect_id GROUP BY T1.id HAVING count(*)  =  2 UNION SELECT T1.id ,  T1.name ,  T1.gender FROM architect AS T1 JOIN mill AS T2 ON T1.id  =  T2.architect_id GROUP BY T1.id HAVING count(*)  =  1
-- Language PostgreSQL
        -- Tables: 
             -- Table: architect 
columns : [['id', 'text'], ['name', 'text'], ['nationality', 'text'], ['gender', 'text']]

 -- Table: bridge 
columns : [['architect id', 'number'], ['id', 'number'], ['name', 'text'], ['location', 'text'], ['length meters', 'number'], ['length feet', 'number']]

 -- Table: mill 
columns : [['architect id', 'number'], ['id', 'number'], ['location', 'text'], ['name', 'text'], ['type', 'text'], ['built year', 'number'], ['notes', '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 location of the bridge named 'Kolob Arch' or 'Rainbow Bridge'?
SQL: SELECT LOCATION FROM bridge WHERE name  =  'Kolob Arch' OR name  =  'Rainbow Bridge'
-- Language PostgreSQL
        -- Tables: 
             -- Table: architect 
columns : [['id', 'text'], ['name', 'text'], ['nationality', 'text'], ['gender', 'text']]

 -- Table: bridge 
columns : [['architect id', 'number'], ['id', 'number'], ['name', 'text'], ['location', 'text'], ['length meters', 'number'], ['length feet', 'number']]

 -- Table: mill 
columns : [['architect id', 'number'], ['id', 'number'], ['location', 'text'], ['name', 'text'], ['type', 'text'], ['built year', 'number'], ['notes', '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 of the mill names contains the french word 'Moulin'?
SQL: SELECT name FROM mill WHERE name LIKE '%Moulin%'
-- Language PostgreSQL
        -- Tables: 
             -- Table: architect 
columns : [['id', 'text'], ['name', 'text'], ['nationality', 'text'], ['gender', 'text']]

 -- Table: bridge 
columns : [['architect id', 'number'], ['id', 'number'], ['name', 'text'], ['location', 'text'], ['length meters', 'number'], ['length feet', 'number']]

 -- Table: mill 
columns : [['architect id', 'number'], ['id', 'number'], ['location', 'text'], ['name', 'text'], ['type', 'text'], ['built year', 'number'], ['notes', '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 name of the mills built by the architects who have also built a bridge longer than 80 meters?
SQL: SELECT DISTINCT T1.name FROM mill AS T1 JOIN architect AS t2 ON T1.architect_id  =  T2.id JOIN bridge AS T3 ON T3.architect_id  =  T2.id WHERE T3.length_meters  >  80
-- Language PostgreSQL
        -- Tables: 
             -- Table: architect 
columns : [['id', 'text'], ['name', 'text'], ['nationality', 'text'], ['gender', 'text']]

 -- Table: bridge 
columns : [['architect id', 'number'], ['id', 'number'], ['name', 'text'], ['location', 'text'], ['length meters', 'number'], ['length feet', 'number']]

 -- Table: mill 
columns : [['architect id', 'number'], ['id', 'number'], ['location', 'text'], ['name', 'text'], ['type', 'text'], ['built year', 'number'], ['notes', '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 most common mill type, and how many are there?
SQL: SELECT TYPE ,  count(*) FROM mill GROUP BY TYPE ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: architect 
columns : [['id', 'text'], ['name', 'text'], ['nationality', 'text'], ['gender', 'text']]

 -- Table: bridge 
columns : [['architect id', 'number'], ['id', 'number'], ['name', 'text'], ['location', 'text'], ['length meters', 'number'], ['length feet', 'number']]

 -- Table: mill 
columns : [['architect id', 'number'], ['id', 'number'], ['location', 'text'], ['name', 'text'], ['type', 'text'], ['built year', 'number'], ['notes', '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 architects haven't built a mill before year 1850?
SQL: SELECT count(*) FROM architect WHERE id NOT IN ( SELECT architect_id FROM mill WHERE built_year  <  1850 );
-- Language PostgreSQL
        -- Tables: 
             -- Table: architect 
columns : [['id', 'text'], ['name', 'text'], ['nationality', 'text'], ['gender', 'text']]

 -- Table: bridge 
columns : [['architect id', 'number'], ['id', 'number'], ['name', 'text'], ['location', 'text'], ['length meters', 'number'], ['length feet', 'number']]

 -- Table: mill 
columns : [['architect id', 'number'], ['id', 'number'], ['location', 'text'], ['name', 'text'], ['type', 'text'], ['built year', 'number'], ['notes', 'text']]

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



Question: show the name of all bridges that was designed by american archtect, and sort the result by the bridge feet length.
SQL: SELECT t1.name FROM bridge AS t1 JOIN architect AS t2 ON t1.architect_id  =  t2.id WHERE t2.nationality  =  'American' ORDER BY t1.length_feet
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 book clubs are there?
SQL: SELECT count(*) FROM book_club
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 book clubs.
SQL: SELECT count(*) FROM book_club
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: show the titles, and authors or editors for all books made after the year 1989.
SQL: SELECT book_title ,  author_or_editor FROM book_club WHERE YEAR  >  1989
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 titles and authors or editors that correspond to books made after 1989?
SQL: SELECT book_title ,  author_or_editor FROM book_club WHERE YEAR  >  1989
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Show all distinct publishers for books.
SQL: SELECT DISTINCT publisher FROM book_club
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 all the different book publishers?
SQL: SELECT DISTINCT publisher FROM book_club
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Show the years, book titles, and publishers for all books, in descending order by year.
SQL: SELECT YEAR ,  book_title ,  publisher FROM book_club ORDER BY YEAR DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 years, titles, and publishers for all books, ordered by year descending?
SQL: SELECT YEAR ,  book_title ,  publisher FROM book_club ORDER BY YEAR DESC
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Show all publishers and the number of books for each publisher.
SQL: SELECT publisher ,  count(*) FROM book_club GROUP BY publisher
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 books are there for each publisher?
SQL: SELECT publisher ,  count(*) FROM book_club GROUP BY publisher
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 publisher with most number of books?
SQL: SELECT publisher FROM book_club GROUP BY publisher ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Return the publisher that has published the most books.
SQL: SELECT publisher FROM book_club GROUP BY publisher ORDER BY count(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Show all book categories and the number of books in each category.
SQL: SELECT category ,  count(*) FROM book_club GROUP BY category
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 books fall into each category?
SQL: SELECT category ,  count(*) FROM book_club GROUP BY category
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: List categories that have at least two books after year 1989.
SQL: SELECT category FROM book_club WHERE YEAR  >  1989 GROUP BY category HAVING count(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 categories have two or more corresponding books that were made after 1989?
SQL: SELECT category FROM book_club WHERE YEAR  >  1989 GROUP BY category HAVING count(*)  >=  2
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Show publishers with a book published in 1989 and a book in 1990.
SQL: SELECT publisher FROM book_club WHERE YEAR  =  1989 INTERSECT SELECT publisher FROM book_club WHERE YEAR  =  1990
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 publishers who have published a book in both 1989 and 1990?
SQL: SELECT publisher FROM book_club WHERE YEAR  =  1989 INTERSECT SELECT publisher FROM book_club WHERE YEAR  =  1990
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Show all publishers which do not have a book in 1989.
SQL: SELECT publisher FROM book_club EXCEPT SELECT publisher FROM book_club WHERE YEAR  =  1989
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Which publishers did not publish a book in 1989?
SQL: SELECT publisher FROM book_club EXCEPT SELECT publisher FROM book_club WHERE YEAR  =  1989
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Show all movie titles, years, and directors, ordered by budget.
SQL: SELECT title ,  YEAR ,  director FROM movie ORDER BY budget_million
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 titles, years, and directors of all movies, ordered by budget in millions?
SQL: SELECT title ,  YEAR ,  director FROM movie ORDER BY budget_million
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 movie directors are there?
SQL: SELECT COUNT (DISTINCT director) FROM movie
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 different directors.
SQL: SELECT COUNT (DISTINCT director) FROM movie
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 title and director for the movie with highest worldwide gross in the year 2000 or before?
SQL: SELECT title ,  director FROM movie WHERE YEAR  <=  2000 ORDER BY gross_worldwide DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Return the title and director of the movie released in the year 2000 or earlier that had the highest worldwide gross.
SQL: SELECT title ,  director FROM movie WHERE YEAR  <=  2000 ORDER BY gross_worldwide DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Show all director names who have a movie in both year 1999 and 2000.
SQL: SELECT director FROM movie WHERE YEAR  =  2000 INTERSECT SELECT director FROM movie WHERE YEAR  =  1999
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Which directors had a movie both in the year 1999 and 2000?
SQL: SELECT director FROM movie WHERE YEAR  =  2000 INTERSECT SELECT director FROM movie WHERE YEAR  =  1999
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Show all director names who have a movie in the year 1999 or 2000.
SQL: SELECT director FROM movie WHERE YEAR  =  1999 OR YEAR  =  2000
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Which directors had a movie in either 1999 or 2000?
SQL: SELECT director FROM movie WHERE YEAR  =  1999 OR YEAR  =  2000
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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 average, maximum, and minimum budget for all movies before 2000.
SQL: SELECT avg(budget_million) ,  max(budget_million) ,  min(budget_million) FROM movie WHERE YEAR  <  2000
-- Language PostgreSQL
        -- Tables: 
             -- Table: book club 
columns : [['book club id', 'number'], ['year', 'number'], ['author or editor', 'text'], ['book title', 'text'], ['publisher', 'text'], ['category', 'text'], ['result', 'text']]

 -- Table: movie 
columns : [['movie id', 'number'], ['title', 'text'], ['year', 'number'], ['director', 'text'], ['budget million', 'number'], ['gross worldwide', 'number']]

 -- Table: culture company 
columns : [['company name', 'text'], ['type', 'text'], ['incorporated in', 'text'], ['group equity shareholding', 'number'], ['book club id', 'text'], ['movie 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: Return the average, maximum, and minimum budgets in millions for movies made before the year 2000.
SQL: SELECT avg(budget_million) ,  max(budget_million) ,  min(budget_million) FROM movie WHERE YEAR  <  2000