The following is a database design about receipts. A receipt represents a sale,
ID: 3682744 • Letter: T
Question
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:
Keep in mind that in the diagram the arrows represent foreign keys; the arrows are directional – they point from FK to PK.
1. (8 pts) write SQL statements to create table for receipt_item.
2. (6 pts) write SQL statements to create table for receipt.
3. (8 pts) write SQL statements to create table for product.
4. (4 pts) write SQL statements to create table for store.
5. (4 pts) write SQL statements to create table for category.
6. (10 pts) write a SQL query to display the id and name of all the products that belong to the category with id = 1.
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.
9. (25 pts) 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 NameExplanation / Answer
Answer:
1.
2.
CREATE TABLE Receipt
3.
4.
5.
6.
Select id, name from product where categoryid = 1 ;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.