Notes

-- a. DDL statement to add three relations
CREATE TABLE Customer (
customerID INT PRIMARY KEY,
cs_name VARCHAR(100),
cs_dob DATE,
cs_mobile VARCHAR(15),
cs_category VARCHAR(50)
);

CREATE TABLE Order (
orderID INT PRIMARY KEY,
or_quantity INT,
or_date DATE,
customerID INT,
productID INT,
FOREIGN KEY (customerID) REFERENCES Customer(customerID),
FOREIGN KEY (productID) REFERENCES Product(productID)
);

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

-- b. Add referential integrity with ALTER TABLE statement
ALTER TABLE Order
ADD CONSTRAINT FK_Customer FOREIGN KEY (customerID) REFERENCES Customer(customerID);

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

-- c. Statement to add a new attribute to the Product relation with enumerated values
ALTER TABLE Product
ADD type ENUM('solid', 'liquid', 'individual', 'bulk');

-- d. Update statement for customers who ordered more than 50 times in November 2019
UPDATE Customer
SET cs_category = 'premium'
WHERE customerID IN (
SELECT customerID
FROM Order
WHERE or_date BETWEEN '2019-11-01' AND '2019-11-30'
GROUP BY customerID
HAVING COUNT(orderID) > 50
);

-- e. Create a view with product type, name, and average cost more than 500
CREATE VIEW ProductView AS
SELECT p.type, p.pr_name, AVG(pr_price) AS avg_cost
FROM Product p
JOIN Order o ON p.productID = o.productID
GROUP BY p.type, p.pr_name
HAVING AVG(pr_price) > 500;

-- f. Stored procedure to get all products ordered more than passed parameter
DELIMITER $$

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

DELIMITER ;