This is a plain theory question with multiple sub-parts — a mix of SQL DDL, DML, and query-writing tasks, plus output prediction. I will answer every sub-part (a) through (k) in order.

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

CREATE TABLE Product (
    PCode VARCHAR(5) PRIMARY KEY,
    PName VARCHAR(30) NOT NULL,
    UPrice DECIMAL(8,2),
    Manufacturer VARCHAR(30)
);

The PCode column is the natural choice for the primary key — it is short, unique per row, and never null. VARCHAR(5) fits codes like P01. PName is marked NOT NULL because every product must have a name. UPrice uses DECIMAL(8,2) to store prices with two decimal places.

(b) Identify the primary key in Product.

The primary key is PCode. Each product has a unique code (P01, P02, …), and no two rows share the same PCode. No other column is guaranteed unique — for example, PName repeats ("Soap" appears twice), and Manufacturer repeats ("Dove" appears twice).

(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.

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

ORDER BY PName DESC sorts names from Z to A. The tiebreaker UPrice ASC ensures that when two rows have the same name (e.g., "Soap" for P03 and P05), the cheaper one comes first.

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

ALTER TABLE Product
ADD Discount DECIMAL(8,2);

ALTER TABLE modifies an existing table's structure. The new column Discount will initially contain NULL for all existing rows.

(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.

UPDATE Product
SET Discount = CASE
    WHEN UPrice > 100 THEN UPrice * 0.10
    ELSE 0
END;

The CASE expression evaluates each row individually. For P01 (UPrice 120), discount becomes 12.00; for P02 (UPrice 54), discount becomes 0.

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

UPDATE Product
SET UPrice = UPrice * 1.12
WHERE Manufacturer = 'Dove';

Only rows where Manufacturer is 'Dove' (P05 and P06) are affected. P05's price goes from 38 to 42.56; P06's from 245 to 274.40.

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

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

GROUP BY Manufacturer groups rows by manufacturer name. COUNT(*) counts the rows in each group. The result will show: Surf → 1, Colgate → 1, Lux → 1, Pepsodent → 1, Dove → 2.

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

This is a mixed-format question — part SQL query writing (a–g) and part output prediction (h–k). I'll answer each sub-part in order, with full reasoning.

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

The table has four columns: PCode (text, primary key), PName (text), UPrice (numeric, likely decimal), and Manufacturer (text). The primary key constraint ensures uniqueness and non-null values.

CREATE TABLE Product (
    PCode VARCHAR(3) PRIMARY KEY,
    PName VARCHAR(30) NOT NULL,
    UPrice DECIMAL(10,2) NOT NULL,
    Manufacturer VARCHAR(30) NOT NULL
);

(b) Identify the primary key in Product.

The primary key is PCode. Each product has a unique code (P01 through P06), and no two rows share the same PCode. This is the natural candidate because it uniquely identifies every row.

(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.

The ORDER BY clause handles this: first sort by PName descending, then by UPrice ascending as a tiebreaker.

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

Expected output:

Notice: "Soap" rows appear in ascending price order (25 then 38), and "Toothpaste" rows similarly (54 then 65).

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

Use ALTER TABLE with ADD:

ALTER TABLE Product
ADD Discount DECIMAL(10,2);

This adds a nullable column. Initially all values are NULL.

(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.

Use an UPDATE with a CASE expression:

UPDATE Product
SET Discount = CASE
    WHEN UPrice > 100 THEN UPrice * 0.10
    ELSE 0
END;

After update, the table becomes:

Only P01 (UPrice 120) and P06 (UPrice 245) get non-zero discounts.

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

UPDATE Product
SET UPrice = UPrice * 1.12
WHERE Manufacturer = 'Dove';

After update, Dove products change:

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

Use GROUP BY with COUNT(*):

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

Expected output (based on original data before updates):

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

This groups by product name and computes the average price for each group. Note: the original data has two "Soap" rows (25 and 38) and two "Toothpaste" rows (54 and 65).

Expected output: