Consider the following table named "Product", showing details of products being sold in a grocery shop.

PCode	PName	UPrice	Manufacturer
P01	Washing Powder	120	Surf
P02	Toothpaste	54	Colgate
P03	Soap	25	Lux
P04	Toothpaste	65	Pepsodent
P05	Soap	38	Dove
P06	Shampoo	245	Dove

Write SQL queries for the following:

(a) Create the table Product with appropriate data types and constraints.

(b) Identify the primary key in Product.

(c) List the Product Code, Product name and price in descending order of their product name. If PName is the same, then display the data in ascending order of price.

(d) Add a new column Discount to the table Product.

(e) Calculate the value of the discount in the table Product as 10 per cent of the UPrice for all those products where the UPrice is more than 100, otherwise the discount will be 0.

(f) Increase the price by 12 per cent for all the products manufactured by Dove.

(g) Display the total number of products manufactured by each manufacturer.

Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product:

(h) SELECT PName, avg(UPrice) FROM Product GROUP BY Pname;

(i) SELECT DISTINCT Manufacturer FROM Product;

(j) SELECT COUNT (DISTINCT PName) FROM Product;

(k) SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;

(a)

CREATE TABLE Product (
    PCode VARCHAR(10) PRIMARY KEY,
    PName VARCHAR(50),
    UPrice int,
    Manufacturer VARCHAR(50)
);

(b) The primary key in the table "Product" is PCode.

(c)

SELECT PCode, PName, UPrice
FROM Product
ORDER BY PName DESC, UPrice ASC;

Output

+-------+----------------+--------+
| PCode | PName          | UPrice |
+-------+----------------+--------+
| P01   | WASHING POWDER |    120 |
| P02   | TOOTHPASTE     |     54 |
| P04   | TOOTHPASTE     |     65 |
| P03   | SOAP           |     25 |
| P05   | SOAP           |     38 |
| P06   | SHAMPOO        |    245 |
+-------+----------------+--------+

(d)

ALTER TABLE Product
ADD COLUMN Discount float;

(e)

UPDATE Product 
SET Discount = IF(UPrice > 100, (UPrice * (10/100)) + UPrice, 0);

Output

SELECT * FROM Product;

+-------+----------------+--------+--------------+----------+
| PCode | PName          | UPrice | Manufacturer | Discount |
+-------+----------------+--------+--------------+----------+
| P01   | WASHING POWDER |    120 | SURF         |       12 |
| P02   | TOOTHPASTE     |     54 | COLGATE      |        0 |
| P03   | SOAP           |     25 | LUX          |        0 |
| P04   | TOOTHPASTE     |     65 | PEPSODENT    |        0 |
| P05   | SOAP           |     38 | DOVE         |        0 |
| P06   | SHAMPOO        |    245 | DOVE         |     24.5 |
+-------+----------------+--------+--------------+----------+

(f)

UPDATE Product
SET UPrice = (UPrice * (12/100)) + UPrice
WHERE Manufacturer = 'Dove';

Output

SELECT * from Product;

+-------+----------------+--------+--------------+----------+
| PCode | PName          | UPrice | Manufacturer | Discount |
+-------+----------------+--------+--------------+----------+
| P01   | WASHING POWDER |    120 | SURF         |       12 |
| P02   | TOOTHPASTE     |     54 | COLGATE      |        0 |
| P03   | SOAP           |     25 | LUX          |        0 |
| P04   | TOOTHPASTE     |     65 | PEPSODENT    |        0 |
| P05   | SOAP           |     43 | DOVE         |        0 |
| P06   | SHAMPOO        |    274 | DOVE         |     24.5 |
+-------+----------------+--------+--------------+----------+

(g)

SELECT Manufacturer, COUNT(*) AS TotalProducts
FROM Product
GROUP BY Manufacturer;

Output

+--------------+---------------+
| Manufacturer | TotalProducts |
+--------------+---------------+
| SURF         |             1 |
| COLGATE      |             1 |
| LUX          |             1 |
| PEPSODENT    |             1 |
| DOVE         |             2 |
+--------------+---------------+

(h)

SELECT PName, avg(UPrice) FROM Product GROUP 
BY Pname;

Output

+----------------+-------------+
| PName          | avg(UPrice) |
+----------------+-------------+
| WASHING POWDER |    120.0000 |
| TOOTHPASTE     |     59.5000 |
| SOAP           |     34.0000 |
| SHAMPOO        |    274.0000 |
+----------------+-------------+

(i)

SELECT DISTINCT Manufacturer FROM Product;

Output

+--------------+
| Manufacturer |
+--------------+
| SURF         |
| COLGATE      |
| LUX          |
| PEPSODENT    |
| DOVE         |
+--------------+

(j)

SELECT COUNT(DISTINCT PName) FROM Product;

Output

+-----------------------+
| COUNT(DISTINCT PName) |
+-----------------------+
|                     4 |
+-----------------------+

(k)

SELECT PName, MAX(UPrice), MIN(UPrice) FROM 
Product GROUP BY PName;

Output

+----------------+-------------+-------------+
| PName          | MAX(UPrice) | MIN(UPrice) |
+----------------+-------------+-------------+
| WASHING POWDER |         120 |         120 |
| TOOTHPASTE     |          65 |          54 |
| SOAP           |          43 |          25 |
| SHAMPOO        |         274 |         274 |
+----------------+-------------+-------------+