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
List the writers who have written more than one book.
assistant
SELECT Writer FROM book GROUP BY Writer HAVING COUNT(*) > 1
human
Show the titles of books in descending order of publication price.
assistant
SELECT T1.Title FROM book AS T1 JOIN publication AS T2 ON T1.Book_ID = T2.Book_ID ORDER BY T2.Price DESC
human
List the publisher of the publication with the highest price.
assistant
SELECT Publisher FROM publication ORDER BY Price DESC LIMIT 1
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 writers who have published a book with price more than 4000000.
assistant
SELECT T1.Writer FROM book AS T1 JOIN publication AS T2 ON T1.Book_ID = T2.Book_ID WHERE T2.Price > 4000000 ;