A department store maintains data on customers, products, and purchase records in three tables: CUSTOMER, PRODUCT,...

 

Transcribed Image Text:

A department store maintains data on customers, products, and purchase records in three tables: CUSTOMER, PRODUCT, and PURCHASE. The store manager wants to find the average price of all distinct products that are purchased within a category for each category. Write a query to print category and the average of price minus discount for the distinct products that are purchased in the category for each category. ▼ Schema Name: Type ID STATE CUSTOMER Description Integer A customer's ID in the inclusive range [1, 500]. This is a primary key. NAME String CITY String A city name. This field contains between 1 and 50 characters (inclusive). A state name. This field contains between 1 and 50 characters (inclusive). String A customer's name. This field contains between 1 and 100 characters (inclusive). Name ID Type Integer A product's ID in the inclusive range [1, 500]. This is a primary key. String A product's name. This field contains between 1 and 50 characters (inclusive). CATEGORY String A category name of the product. This field contains between 1 and 50 characters (inclusive). PRICE Integer The price of the product in the inclusive range [500, 1000]. DISCOUNT Integer The discount associated with the product in the inclusive range [5, 20]. AVAILABLE Integer The availability of a product. It is 1 if the product is available or it is O if the product is not available. NAME Name PRODUCT ID Description CUSTOMER_ID Type Integer An id associated with a purchase that is done in the inclusive range [1, 1000]. This is a primary key. Integer A customer ID. This is a foreign key to customer.customer_id. PRODUCT_ID Integer A product ID. This is a foreign key to product.product_id. PURCHASE_DATE Date The date associated with a purchase. The date falls in the range '2000-01-01' to '2000-12-31' (inclusive). PURCHASE Description A department store maintains data on customers, products, and purchase records in three tables: CUSTOMER, PRODUCT, and PURCHASE. The store manager wants to find the average price of all distinct products that are purchased within a category for each category. Write a query to print category and the average of price minus discount for the distinct products that are purchased in the category for each category. ▼ Schema Name: Type ID STATE CUSTOMER Description Integer A customer's ID in the inclusive range [1, 500]. This is a primary key. NAME String CITY String A city name. This field contains between 1 and 50 characters (inclusive). A state name. This field contains between 1 and 50 characters (inclusive). String A customer's name. This field contains between 1 and 100 characters (inclusive). Name ID Type Integer A product's ID in the inclusive range [1, 500]. This is a primary key. String A product's name. This field contains between 1 and 50 characters (inclusive). CATEGORY String A category name of the product. This field contains between 1 and 50 characters (inclusive). PRICE Integer The price of the product in the inclusive range [500, 1000]. DISCOUNT Integer The discount associated with the product in the inclusive range [5, 20]. AVAILABLE Integer The availability of a product. It is 1 if the product is available or it is O if the product is not available. NAME Name PRODUCT ID Description CUSTOMER_ID Type Integer An id associated with a purchase that is done in the inclusive range [1, 1000]. This is a primary key. Integer A customer ID. This is a foreign key to customer.customer_id. PRODUCT_ID Integer A product ID. This is a foreign key to product.product_id. PURCHASE_DATE Date The date associated with a purchase. The date falls in the range '2000-01-01' to '2000-12-31' (inclusive). PURCHASE Description