You must use the Access database and create queries that will answer the request

Question

You must use the Access database and create queries that will answer the requests below.
You may use direct SQL to create the queries.

Question: Obtain the book titles, the last name of their first authors, and the sum of all copies of the book sold. Include only the titles with more than 5 copies ordered by customers in total. Sort the list in descending number of books sold.

We're NOT using Join or Join In functions. Just SELECT, FROM, WHERE, ORDER BY, and SORT BY Thanks!

Book Wrote Author Authorid AuthornD FrstName nitials Category AuthorRank OrderLine Order Customer OrderNo OrderRank ISEN CustomerName CustomerAddress Stato OrderDeliveryCate Quanbty PercentageOiscount

Explanation / Answer

The above query can be wrtitten using nested select statement.

Nesting is done to fetch the Author Last Name and to get the quantities of the orders.

Order by is ued to display the result in descending order of quantities sold.

Query

SELECT b.Title,(SELECT a.LastName FROM Author AS a

WHERE AuthorID = (SELECT w.AuthorID FROM Wrote AS w WHERE w.ISBN = b.ISBN )) AS ,

(SELECT SUM(Quantity) FROM OrderLine AS o WHERE o.ISBN = b.ISBN) AS total

FROM Book AS b

WHERE (SELECT SUM(Quantity) FROM OrderLine AS o WHERE o.ISBN = b.ISBN) > 5

ORDER BY total DESC;

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

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