I am having trouble with this problem on SQL and cannot seem to get the exact ou

Question

I am having trouble with this problem on SQL and cannot seem to get the exact output. Here's the question:

4. Write a query to list all books where cost is greater than the average cost. The report needs to be sorted by cost and should look like this: Title Cost Avg Cost 1 Database in the Cloud $79.95 $74.95 2 iOS Programming $79.95 $74.95 3 Virtual Programming for Virtual Environments $79.95 $74.95 $89.95 $74.95 4 Mastering the database environment 5 Reengineering the Middle Tier $89.95 $74.95 6 Shining Through the Cloud: Sun Programming $109.95 $74.95 7 The Golden Road to Platform independence $119.95 $74.95 8 DATABASES in Theory $129.95 $74.95

Explanation / Answer

First we design the query to filter Books with cost >Average and then display a sorted table by the cost.

(SELECT BOOK_TITLE AS Title , BOOK_COST AS Cost FROM BOOK WHERE BOOK_COST > AVG(BOOK_COST) ORDER BY BOOK_COST ASC)

To generate the required table we need to append a column of Average Price. This can be join with a JOIN.

As follows.

SELECT * FROM
(SELECT BOOK_TITLE AS Title , BOOK_COST AS Cost FROM BOOK WHERE BOOK_COST > AVG(BOOK_COST) ORDER BY BOOK_COST ASC)
JOIN (SELECT AVG(BOOK_COST) AS Avg_Cost FROM BOOK);

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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