Database: culture_company

CREATE TABLE "book_club" (
"book_club_id" int,
"Year" int,
"Author_or_Editor" text,
"Book_Title" text,
"Publisher" text,
"Category" text,
"Result" text,
PRIMARY KEY ("book_club_id")
);
CREATE TABLE "movie" (
"movie_id" int,
"Title" text,
"Year" int,
"Director" text,
"Budget_million" real,
"Gross_worldwide" int,
PRIMARY KEY("movie_id")
);
CREATE TABLE "culture_company" (
"Company_name" text,
"Type" text,
"Incorporated_in" text,
"Group_Equity_Shareholding" real,
"book_club_id" text,
"movie_id" text,
PRIMARY KEY("Company_name"),
FOREIGN KEY ("book_club_id") REFERENCES "book_club"("book_club_id"),
FOREIGN KEY ("movie_id") REFERENCES "movie"("movie_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