1) Display the book title and the number of books sold where the profit from the

Question

1) Display the book title and the number of books sold where the profit from the book is more the 70%. The resulting list should display highest quantity of books title sold first in the list. Profit for a book is calculated as (retail – cost) / cost.

2) 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

3) Display the categories and the count of number of books in those categories where the category has the more books then the minimum number of books for all the categories. Sort the resulting set in category order ascending.

4) Display book title, ISBN number and cost for all books which belong to the category with the least number of books in the category. Format the cost field as dollars ‘$’ with two decimal places. Rename the new formatted cost field to ‘COST’ (complex query). Order the result by book title.

5) Display all the categories and the total amount of profit for each category. Rename the calculated field as ‘CATEGORY_PROFIT’. Category profit is calculated as number of books sold times profit (retail – cost) in that category. Round the profit calculation to the nearest full value. Order the result set in the descending order of highest calculated profit.

6) Display category, book title and retail price for all the books where the retail price of the book is less than the maximum cost of all the books. Order the result set by the book category ascending and retail within the category descending order

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 normalization

Explanation / Answer

Query1)

SELECT Title, count(*) AS number_of_books
FROM BOOKS
WHERE ((retail – cost) / cost) > 0.7
GROUP BY Title
ORDER BY number_of_books DESC;

Query2)

SELECT A.Category,B.Name,'$'+CAST(round(avg(Retail),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 ;

Query3)

SELECT Category, count(*) AS number_of_books
FROM BOOKS
WHERE number_of_books > min(number_of_books)
GROUP BY Category
ORDER BY Category;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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