human
Schema:
publication (Publication_ID, Book_ID, Publisher, Publication_Date, Price)
book (Book_ID, Title, Issues, Writer)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the title and publication dates of books.
assistant
SELECT T1.Title , T2.Publication_Date FROM book AS T1 JOIN publication AS T2 ON T1.Book_ID = T2.Book_ID
human
Show the prices of publications whose publisher is either 'Person' or 'Wiley'
assistant
SELECT Price FROM publication WHERE Publisher = 'Person' OR Publisher = 'Wiley'
human
What are the distinct publishers of publications with price higher than 5000000?
assistant
SELECT DISTINCT Publisher FROM publication WHERE Price > 5000000
human
Show the publishers that have publications with price higher than 10000000 and publications with price lower than 5000000.
assistant
SELECT Publisher FROM publication WHERE Price > 10000000 INTERSECT SELECT Publisher FROM publication WHERE Price < 5000000
human
Show publishers that have more than one publication.
assistant
SELECT Publisher FROM publication GROUP BY Publisher HAVING COUNT(*) > 1
human
Show different publishers together with the number of publications they have.
assistant
SELECT Publisher , COUNT( * ) FROM publication GROUP BY Publisher ;