Display the book category, publisher name and average price for books which belo
ID: 3782199 • Letter: D
Question
Display the book category, publisher name and average price for books which belong to the either a category of ‘COMPUTER’ or “CHILDREN’ and where the average retail cost for the category is more than 50 dollars. Rename the calculated field ‘AVERAGE_CAT_PRICE’ and format with a leading ‘$’ symbol and rounded to two decimal places. Order the result by book category ascending and average category price descending
ORDERS Order CUSTOMERS Customer Customer Orderd late Lastname Ship date Firstname Shipstreet Emal Shipcity Address Shipstate Shipzip BOOKAUTHOR AUTHOR State BOOKS Shipcost SBN AuthorlD Referred SBN AuthorID Lname ORDERITEMS Region Title Fname Order# Pubdate LA tem# Publ D SBN Cost Quantity Retail Discount PUBLISHER Category Paid each PublD Name PROMOTION Phone Contact Gift Minretaj Max retai FIGURE 1-5 JustLee Books's table structures after normalizationExplanation / Answer
Sequence of query:
FROM & JOINs determine & filter rows
WHERE more filters on the rows
GROUP BY combines those rows into groups
HAVING filters groups
ORDER BY arranges the remaining rows/groups
LIMIT limits the results
So,
the query for the above statement is:
SELECT A.Category,B.Name,'$'+CAST(round(avg(cost),2)) AS VARCHAR(5)) as AVERAGE_CAT_PRICE
FROM BOOKS as A INNER JOIN PUBLISHER as B
on A.PubID=B.PubID
WHERE A.Category IN ('COMPUTER','CHILDREN')
GROUP BY A.Category
HAVING AVERAGE_CAT_PRICE>50
ORDER BY A.Category , AVERAGE_CAT_PRICE desc ;