Bookstore owner needs to know how many copies of books written by each author ha

Question

Bookstore owner needs to know how many copies of books written by each author have been sold. For each author, display author ID, first name, last name, and total number of books written by the author that have been sold. Display the output in descending order of total books sold.

ORDERS Order# Customer# Orderdate Shipdate Shipstreet Shipcity Shipstate Shipzip Shipcost CUSTOMERS Customer# Lastname Firstname Emall Address CI Sta Zi Referred Reglon BOOKAUTHOR AUTHOR BOOKS ISBN Title Pubdate PublD Cost Retalil Discount Category ISBN AuthorlD AuthorlD Lname Fname ORDER!TEMS | Order# I tem# ISBN Quantity Paldeach PUBLISHER PublD Name PROMOTION Contact Phone Gift Minretail Maxretall

Explanation / Answer

Ans 1. i) SELECT COUNT(ORDERITEMS.ISBN) AS "Number of books sold", CONCAT(AUTHOR.fname, AUTHOR.lname) AS "AUTHOR" FROM ORDERITEMS JOIN BOOK ON ORDERITEMS.ISBN = BOOK.ISBN RIGHT JOIN AUTHOR ON BOOK.AuthorID = AUTHOR. AuthorID ii) SELECT author ID, Fname, Lname, COUNT(ISBN) AS 'total number of books sold' FROM ORDERITEMS, BOOKAUTHOR WHERE ORDERITEMS.ISBN=BOOKAUTHOR.ISBN INNERJOIN AUTHOR on BOOKAUTHOR.AuthorID = AUTHOR.AuthorID GROUP BY AUTHOR.AuthorID;

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.