CREATE TABLE stores(store_id INT PRIMARY KEY AUTO_INCREMENT, store_name VARCHAR(50));

INSERT INTO stores (store_name) VALUES ('Dhaka’);

INSERT INTO stores (store_name) VALUES ('Rangpur');

CREATE TABLE products(product_id INT PRIMARY KEY AUTO_INCREMENT,

product_name VARCHAR(40), cost_price DOUBLE, retail_price DOUBLE,

availability VARCHAR(5));

cost_price – buying price

retail_price – Selling price

availability – LOCAL or ALL office

INSERT INTO products (product_name, cost_price, retail_price, availability)

VALUES ('WIRELESS MOUSE', 200.00, 300.00,'ALL');

INSERT INTO products (product_name, cost_price, retail_price, availability)

VALUES ('8 MP CAMERA', 10000.00, 15000.00,'ALL');

INSERT INTO products (product_name, cost_price, retail_price, availability)

VALUES ('SMART WATCH', 8000.00, 12000.00,'LOCAL');

DELIMITER //

CREATE FUNCTION calcProfit(cost_price double, retail_price double) RETURNS double

BEGIN

DECLARE profit double;

SET profit = retail_price-cost_price;

RETURN profit;

END //

DELIMITER ;

SELECT *, calcProfit(cost_price,retail_price) AS profit FROM products;

DELIMITER //

CREATE FUNCTION ProfitLabel(cost_price double, retail_price double) RETURNS varchar(255)

BEGIN

DECLARE profit double;

DECLARE profitlabel varchar(255);

SET profit = retail_price-cost_price;

if profit>=5000 then

set profitlabel='High';

end if ;

RETURN profitlabel;

END //

DELIMITER ;

SELECT *, calcProfit(cost_price,retail_price) AS profit FROM products;

DELIMITER //

CREATE FUNCTION ProfitLabel2(cost_price double, retail_price double) RETURNS varchar(255)

BEGIN

DECLARE profit double;

DECLARE profitlabel varchar(255);

SET profit = retail_price-cost_price;

if profit>=5000 then

set profitlabel='High';

else

set profitlabel= 'Low';

end if ;

RETURN profitlabel;

END //

DELIMITER ;

SELECT *, calcProfit(cost_price,retail_price) AS profit FROM products;