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:

1. write a SQL query to display the id and name of each category, with the number of products that belong to the category, and make sure ALL categories appear, even those with no products.

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

Solution

Use below query Left join is used for making the table for category ID which does not have product id this will result productId null if there is no product assosciated with categoryId.

When count on distinct P.ID is used 0 will appear for which there is no product for a category.

Select C.Id,C.Name,COUNT(distinct P.Id)

from Category C

left join Product P

on C.Id = P.CategoryId

group by C.Id,C.Name