Notes

CREATE TABLE Customer (
customerID INT PRIMARY KEY,
cs_name VARCHAR(100) NOT NULL,
cs_dob DATE,
cs_mobile VARCHAR(15) UNIQUE,
cs_category VARCHAR(50)
);

CREATE TABLE Product (
productID INT PRIMARY KEY,
pr_name VARCHAR(100) NOT NULL,
pr_price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE OrderDetails (
orderID INT PRIMARY KEY AUTO_INCREMENT,
or_quantity INT NOT NULL,
or_date DATE NOT NULL,
customerID INT,
productID INT,
FOREIGN KEY (customerID) REFERENCES Customer(customerID),
FOREIGN KEY (productID) REFERENCES Product(productID)
);

Bbbb

ALTER TABLE OrderDetails
ADD CONSTRAINT FK_Customer
FOREIGN KEY (customerID) REFERENCES Customer(customerID);

ALTER TABLE OrderDetails
ADD CONSTRAINT FK_Product
FOREIGN KEY (productID) REFERENCES Product(productID);



Ccc
ALTER TABLE Product
ADD type ENUM('solid', 'liquid', 'individual', 'bulk');


D
UPDATE Customer
SET cs_category = 'premium'
WHERE customerID IN (
SELECT customerID
FROM OrderDetails
WHERE MONTH(or_date) = 11 AND YEAR(or_date) = 2019
GROUP BY customerID
HAVING COUNT(orderID) > 50
);


Eee
CREATE VIEW ProductSummary AS
SELECT type, pr_name, AVG(pr_price) AS avg_cost
FROM Product
GROUP BY type, pr_name
HAVING AVG(pr_price) > 500;


DELIMITER $$

CREATE PROCEDURE GetProductsOrderedMoreThan(IN minOrders INT)
BEGIN
SELECT p.productID, p.pr_name, COUNT(o.orderID) AS total_orders
FROM Product p
JOIN OrderDetails o ON p.productID = o.productID
GROUP BY p.productID, p.pr_name
HAVING total_orders > minOrders;
END $$

DELIMITER ;