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 publishers and the number of books for each publisher.
assistant
SELECT publisher , count(*) FROM book_club GROUP BY publisher
human
Show all distinct publishers for books.
assistant
SELECT DISTINCT publisher FROM book_club
human
How many book clubs are there?
assistant
SELECT count(*) FROM book_club
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
human
What is the publisher with most number of books?
assistant
SELECT publisher FROM book_club GROUP BY publisher ORDER BY count(*) DESC LIMIT 1
human
How many books fall into each category?
assistant
SELECT category , COUNT( * ) FROM book_club GROUP BY category ;