The following is a database design about receipts A receipt

The following is a database design about receipts. A receipt represents a sale, and belongs to a store. A receipt has many receipt_item\'s, where each item is for a product, and has a quantity. Each product belongs to exactly one category. The relational schema diagram is shown as follows:

7. (15 pts) write a SQL query to display the total number of different products belonging to category 1 (categoryId =1), and their average price.

8. (20 pts) write a SQL query to display the id and name of each category, with the number of products that belong to the category.

Receipt Id Issuedon Storeld Store ld Name Receipt Item Receiptld Quantity Id Name Categoryld Price Id Name

Solution

Select count(*) as NumberOfDifferentProduct,AVG(Price) as AveragePrice

From Product

where CategoryId = 1

Select C.ID,C.Name,COUNT(*) as NumberOfProducts

from Product P

inner join Category C

on P.CategoryId = C.ID

Group by C.ID,C.Name