Question

A department store maintains data on customers, products, and purchase records in three tables: customer, product, and purchase. the store manager wants to know which product is on maximum discount for each category. write a query to print the following fields for each category, ordered by category, ascending: category, product id and discount for the product that has the maximum discount in the category. in the case of multiple products having same maximum discount within a category, print the product with minimum product_id.

Answer 1

The maximum discount using SQL

Product_Id  Product_Name   Category  Price   Discount  Available

    1           P-1            C-5        720     10       1

    2           P-2            C-1        935     17       1

    3           P-3            C-2        588     19       1

    4           P-4            C-4        619     5        0

    5           P-5            C-1        803     16       1

The Output

C-1 2 17

C-2 3 19

C-4 4 5

C-5 1 10

The code that would print the fields in SQL for the maximum discount is:

WITH newTable AS (

SELECT

t.Category,

t.Product_Id,

b.Discount as maxDiscount,  

ROW_NUMBER() OVER (

                        PARTITION BY t.Category

                        ORDER BY Product_Id DESC

                  ) AS [ROW NUMBER]

FROM Products t

INNER JOIN

(

 SELECT MAX(Discount) as maxDiscount, Category

 FROM Products

GROUP BY Category

) b ON t.Discount = b.Discount AND t.Category = b.Category)

select Category,Product_Id,maxDiscount from newTable

WHERE newTable.[ROW NUMBER] = 1

Read more about SQL here:

brainly.com/question/25694408

#SPJ1