DATABASE NESTED QUERIES

profileitreport

I NEED HELP WITH BELOW QUERIES

 

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 publisher name, category 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.

 

3)      Display the categories and the count of number of books in those categories where the category has the most 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)

 

 

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. 

 

 

  • 9 years ago
  • 15
Answer(2)

Purchase the answer to view it

blurred-text
  • attachment
    juslee_database.docx

Purchase the answer to view it

blurred-text