Database: book_2

CREATE TABLE "publication" (
"Publication_ID" int,
"Book_ID" int,
"Publisher" text,
"Publication_Date" text,
"Price" real,
PRIMARY KEY ("Publication_ID"),
FOREIGN KEY ("Book_ID") REFERENCES "book"("Book_ID")
);
CREATE TABLE "book" (
"Book_ID" int,
"Title" text,
"Issues" real,
"Writer" text,
PRIMARY KEY ("Book_ID")
);

Question: How many books are there?
SQL: SELECT count(*) FROM book

Question: List the writers of the books in ascending alphabetical order.
SQL: SELECT Writer FROM book ORDER BY Writer ASC

Question: List the titles of the books in ascending order of issues.
SQL: SELECT Title FROM book ORDER BY Issues ASC

Question: What are the titles of the books whose writer is not "Elaine Lee"?
SQL: SELECT Title FROM book WHERE Writer != "Elaine Lee"

Question: What are the title and issues of the books?
SQL: SELECT Title ,  Issues FROM book

Question: What are the dates of publications in descending order of price?
SQL: SELECT Publication_Date FROM publication ORDER BY Price DESC

Question: What are the distinct publishers of publications with price higher than 5000000?
SQL: SELECT DISTINCT Publisher FROM publication WHERE Price  >  5000000

Question: List the publisher of the publication with the highest price.
SQL: SELECT Publisher FROM publication ORDER BY Price DESC LIMIT 1

Question: List the publication dates of publications with 3 lowest prices.
SQL: SELECT Publication_Date FROM publication ORDER BY Price ASC LIMIT 3

Question: Show the title and publication dates of books.
SQL: SELECT T1.Title ,  T2.Publication_Date FROM book AS T1 JOIN publication AS T2 ON T1.Book_ID  =  T2.Book_ID

Question: Show writers who have published a book with price more than 4000000.
SQL: SELECT T1.Writer FROM book AS T1 JOIN publication AS T2 ON T1.Book_ID  =  T2.Book_ID WHERE T2.Price  >  4000000

Question: Show the titles of books in descending order of publication price.
SQL: SELECT T1.Title FROM book AS T1 JOIN publication AS T2 ON T1.Book_ID  =  T2.Book_ID ORDER BY T2.Price DESC

Question: Show publishers that have more than one publication.
SQL: SELECT Publisher FROM publication GROUP BY Publisher HAVING COUNT(*)  >  1

Question: Show different publishers together with the number of publications they have.
SQL: SELECT Publisher ,  COUNT(*) FROM publication GROUP BY Publisher

Question: Please show the most common publication date.
SQL: SELECT Publication_Date FROM publication GROUP BY Publication_Date ORDER BY COUNT(*) DESC LIMIT 1

Question: List the writers who have written more than one book.
SQL: SELECT Writer FROM book GROUP BY Writer HAVING COUNT(*)  >  1

Question: List the titles of books that are not published.
SQL: SELECT Title FROM book WHERE Book_ID NOT IN (SELECT Book_ID FROM publication)

Question: Show the publishers that have publications with price higher than 10000000 and publications with price lower than 5000000.
SQL: SELECT Publisher FROM publication WHERE Price  >  10000000 INTERSECT SELECT Publisher FROM publication WHERE Price  <  5000000

Question: What is the number of distinct publication dates?
SQL: SELECT COUNT (DISTINCT Publication_Date) FROM publication

Question: How many distinct publication dates are there in our record?
SQL: SELECT COUNT (DISTINCT Publication_Date) FROM publication

Question: Show the prices of publications whose publisher is either "Person" or "Wiley"
SQL: SELECT Price FROM publication WHERE Publisher  =  "Person" OR Publisher  =  "Wiley"