Problem 107 Write a query to display the book number, title, subject, average cost of books within that subject (AVGCOST), and the difference between each books cost and the average cost of books in that subject. Sort the results by book title (Figure P7.107). SQL CODING

_______________________________________________________________________________

THESE DO NOT WORK, ALTHOUGH IF MODIFIED MAY WORK:

SELECT BOOK_NUM, BOOK_TITLE, BOOK_SUBJECT,

Round(AVGCOST, 2) AS "Average Subject Cost",

BOOK_COST - Round(AVGCOST, 2) AS DIFFERENCE

FROM BOOK JOIN (SELECT BOOK_SUBJECT, Avg(BOOK_COST) AS AVGCOST

FROM BOOK

GROUP BY BOOK_SUBJECT) USING (BOOK_SUBJECT)

ORDER BY BOOK_TITLE;

-----------------------------------------------------------------------------------------------------------------------------------------------

SELECT BOOK.BOOK_NUM, BOOK_TITLE, AUTHOR.AU_LNAME,

AU_FNAME, PATRON.PAT_ID, PAT_LNAME, PAT_TYPE FROM (((BOOK

INNER JOIN PATRON ON BOOK.PAT_ID = PATRON.PAT_ID) INNER JOIN

WRITES ON BOOK.BOOK_NUM = WRITES.BOOK_NUM) INNER JOIN

AUTHOR ON WRITES.AU_ID = AUTHOR.AU_ID) ORDER BY

BOOK_TITLE;

BOOK_NUM BOOK_TITLE BOOK SUBJECT AVGCOST DIFFERENCE 5235 Beginner's Guide to JAVA Programming 66.62 -6.67 5252 Beyond the Database Veil Database 84.95 -15.00 5242 C# in Middleware Deployment Middleware 89.95 -30.00 5246 Capture the Cloud Cloud 72.45 -2.50 5244 Cloud-based Mobile Applications Cloud 72.45 -2.50 5254 Coding Style for Maintenance Programming 66.62 -16.67 5238 Conceptual Programming Programming 66.62 -6.67 5236 Database in the Cloud Cloud 72.45 7.50 5243 DATABASES in Theory Database 84.95 45.00 5240 IOS Programming Programming 66.62 13.33