Assignment no 11
NAME : PRATHMESH KHANDARE
SUB : DBMS
BATCH: B
ROLL NO : SI35
1. Employee (EmpID, Name, Salary) Insert
minimum 10 records.
Write a cursor to give a 10% salary raise to all the employees.
mysql> create table employee(empid int (4) primary key , name varchar (20) , salary int );
Query OK, 0 rows affected, 1 warning (0.02 sec)
2. The database contains two tables: "Products" and "Sales". The "Products" table includes
information about each product such as product ID, name, and category. The "Sales" table records
each sale transaction with details including the product ID, quantity sold, and sale date.
mysql> CREATE TABLE Products (
->
-> productID INT PRIMARY KEY,
->
-> name VARCHAR(50),
->
-> category VARCHAR(50),
->
-> supplierID INT
->
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> CREATE TABLE Sales (
->
-> saleID INT PRIMARY KEY,
->
-> productID INT,
->
-> quantity INT,
->
-> saleDate DATE,
->
-> FOREIGN KEY (productID) REFERENCES Products(productID)
->
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> INSERT INTO Products (productID, name, category, supplierID) VALUES
->
-> (1, 'Pencil 3B', 'Stationery', 1),
->
-> (2, 'Pencil 6B', 'Stationery', 1),
->
-> (3, 'Notebook A4', 'Stationery', 2),
->
-> (4, 'Notebook A5', 'Stationery', 2),
->
-> (5, 'Pen Red', 'Stationery', 3),
->
-> (6, 'Pen Blue', 'Stationery', 3),
->
-> (7, 'Pen Black', 'Stationery', 3);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> INSERT INTO Sales (saleID, productID, quantity, saleDate) VALUES
->
-> (1, 1, 10, '2024-04-15'),
->
-> (2, 2, 5, '2024-04-16'),
->
-> (3, 3, 8, '2024-04-17'),
->
-> (4, 1, 12, '2024-04-18'),
->
-> (5, 4, 7, '2024-04-19');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> DELIMITER //
mysql> CREATE PROCEDURE CalculateTotalRevenueByCategory()
-> BEGIN
-> DECLARE done INT DEFAULT FALSE;
-> DECLARE category VARCHAR(50);
-> DECLARE total_revenue DECIMAL(10, 2);
-> DECLARE cur CURSOR FOR SELECT DISTINCT category FROM Products;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
->
-> DROP TEMPORARY TABLE IF EXISTS category_revenue;
-> CREATE TEMPORARY TABLE category_revenue (
-> category VARCHAR(50),
-> total_revenue DECIMAL(10, 2)
-> );
->
-> OPEN cur;
->
-> read_loop: LOOP
-> FETCH cur INTO category;
->
-> IF done THEN
-> LEAVE read_loop;
-> END IF;
->
-> SELECT SUM(quantity * price) INTO total_revenue
-> FROM Sales
-> JOIN Products ON Sales.productID = Products.productID
-> WHERE Products.category = category;
->
-> INSERT INTO category_revenue (category, total_revenue) VALUES (category, total_revenue);
-> END LOOP;
->
-> CLOSE cur;
->
-> SELECT * FROM category_revenue;
-> END;
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> UPDATE Products SET price = 1.50 WHERE productID = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE Products SET price = 1.50 WHERE productID = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE Products SET price = 2.50 WHERE productID = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE Products SET price = 3.00 WHERE productID = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE Products SET price = 1.00 WHERE productID = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE Products SET price = 1.00 WHERE productID = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE Products SET price = 1.00 WHERE productID = 7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT P.category, SUM(S.quantity * P.price) AS total_revenue
-> FROM Sales S
-> JOIN Products P ON S.productID = P.productID
-> GROUP BY P.category;
+------------+---------------+
| category | total_revenue |
+------------+---------------+
| Stationery | 81.50 |
+------------+---------------+
