human
Schema:
book_club (book_club_id, Year, Author_or_Editor, Book_Title, Publisher, Category, Result)
movie (movie_id, Title, Year, Director, Budget_million, Gross_worldwide)
culture_company (Company_name, Type, Incorporated_in, Group_Equity_Shareholding, book_club_id, movie_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Show all book categories and the number of books in each category.
assistant
SELECT category , count(*) FROM book_club GROUP BY category
human
Show the years, book titles, and publishers for all books, in descending order by year.
assistant
SELECT YEAR , book_title , publisher FROM book_club ORDER BY YEAR DESC
human
Show all publishers which do not have a book in 1989.
assistant
SELECT publisher FROM book_club EXCEPT SELECT publisher FROM book_club WHERE YEAR = 1989
human
Show publishers with a book published in 1989 and a book in 1990.
assistant
SELECT publisher FROM book_club WHERE YEAR = 1989 INTERSECT SELECT publisher FROM book_club WHERE YEAR = 1990
human
show the titles, and authors or editors for all books made after the year 1989.
assistant
SELECT book_title , author_or_editor FROM book_club WHERE YEAR > 1989
human
List categories that have at least two books after year 1989.
assistant
SELECT category FROM book_club WHERE YEAR > 1989 GROUP BY category HAVING COUNT( * ) >= 2 ;