CREATE TABLE book_club (
    book_club_id NUMBER PRIMARY KEY,
    Year NUMBER,
    Author_or_Editor TEXT,
    Book_Title TEXT,
    Publisher TEXT,
    Category TEXT,
    Result TEXT
);
CREATE TABLE movie (
    movie_id NUMBER PRIMARY KEY,
    Title TEXT,
    Year NUMBER,
    Director TEXT,
    Budget_million NUMBER,
    Gross_worldwide NUMBER
);
CREATE TABLE culture_company (
    Company_name TEXT PRIMARY KEY,
    Type TEXT,
    Incorporated_in TEXT,
    Group_Equity_Shareholding NUMBER,
    book_club_id TEXT,
    movie_id TEXT,
    FOREIGN KEY ("movie_id") REFERENCES movie("movie_id"),
    FOREIGN KEY ("book_club_id") REFERENCES book_club("book_club_id")
);

Question: How many book clubs are there?
SQL: SELECT count(*) FROM book_club

Question: Count the number of book clubs.
SQL: SELECT count(*) FROM book_club

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

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

Question: Show all distinct publishers for books.
SQL: SELECT DISTINCT publisher FROM book_club

Question: What are all the different book publishers?
SQL: SELECT DISTINCT publisher FROM book_club

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

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

Question: Show all publishers and the number of books for each publisher.
SQL: SELECT publisher ,  count(*) FROM book_club GROUP BY publisher

Question: How many books are there for each publisher?
SQL: SELECT publisher ,  count(*) FROM book_club GROUP BY publisher

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

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

Question: Show all book categories and the number of books in each category.
SQL: SELECT category ,  count(*) FROM book_club GROUP BY category

Question: How many books fall into each category?
SQL: SELECT category ,  count(*) FROM book_club GROUP BY category

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

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

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

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

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

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

Question: Show all movie titles, years, and directors, ordered by budget.
SQL: SELECT title ,  YEAR ,  director FROM movie ORDER BY budget_million

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

Question: How many movie directors are there?
SQL: SELECT COUNT (DISTINCT director) FROM movie

Question: Count the number of different directors.
SQL: SELECT COUNT (DISTINCT director) FROM movie

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

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

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

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

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

Question: Which directors had a movie in either 1999 or 2000?
SQL: SELECT director FROM movie WHERE YEAR  =  1999 OR YEAR  =  2000

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

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

Question: List all company names with a book published by Alyson.
SQL: SELECT T1.company_name FROM culture_company AS T1 JOIN book_club AS T2 ON T1.book_club_id  =  T2.book_club_id WHERE T2.publisher  =  'Alyson'

Question: What are all the company names that have a book published by Alyson?
SQL: SELECT T1.company_name FROM culture_company AS T1 JOIN book_club AS T2 ON T1.book_club_id  =  T2.book_club_id WHERE T2.publisher  =  'Alyson'

Question: Show the movie titles and book titles for all companies in China.
SQL: SELECT T1.title ,  T3.book_title FROM movie AS T1 JOIN culture_company AS T2 ON T1.movie_id  =  T2.movie_id JOIN book_club AS T3 ON T3.book_club_id  =  T2.book_club_id WHERE T2.incorporated_in  =  'China'

Question: What are the titles of movies and books corresponding to companies incorporated in China?
SQL: SELECT T1.title ,  T3.book_title FROM movie AS T1 JOIN culture_company AS T2 ON T1.movie_id  =  T2.movie_id JOIN book_club AS T3 ON T3.book_club_id  =  T2.book_club_id WHERE T2.incorporated_in  =  'China'

Question: Show all company names with a movie directed in year 1999.
SQL: SELECT T2.company_name FROM movie AS T1 JOIN culture_company AS T2 ON T1.movie_id  =  T2.movie_id WHERE T1.year  =  1999

Question: What are all company names that have a corresponding movie directed in the year 1999?
SQL: SELECT T2.company_name FROM movie AS T1 JOIN culture_company AS T2 ON T1.movie_id  =  T2.movie_id WHERE T1.year  =  1999