Problem Description: Bookstore owner needs to know how many copies of books writ

Question

Problem Description: 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

According to Schema given we need following columns of db :

ISBN and QUANTITY of ORDERITEMS table

ISBN and AUTHORID of BOOKAUTHOR table

AUTHORID , LNAME and FNAME of AUTHOR table

As we need to find out total no. of books sold that is written by an author, book of different title by same author will be counted as book by that author. Hence we just need to count books on the basis of author. So first we will find all the ordered books and their count as below query:

Select o.ISBN, o.QUANTITY, b.AUTHORID from ORDERITEMS as o, BOOKAUTHOR as b where o. ISBN= b.ISBN

Now we can perform group by over AUTHORID and join this resultant table with author table to get final details and use order by for arranging in descending order as below:

Select a.authorid, a.lname, a.fname, sum(t.quantity) as numbooks from author as a, (Select o.ISBN, o.QUANTITY, b.AUTHORID from ORDERITEMS as o, BOOKAUTHOR as b where o. ISBN= b.ISBN) as t where a.authorid=t.authorid groupby a.authorid and order by numbooks desc

According what I understand this should be the resulting query for what bookstore owner wants.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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