CREATE TABLE emp (
empno INT PRIMARY KEY,
emp VARCHAR(50),
name VARCHAR(100),
job VARCHAR(50),
basic DECIMAL(10,2),
da DECIMAL(10,2),
hra DECIMAL(10,2),
pf DECIMAL(10,2),
grosspay DECIMAL(10,2),
netpay DECIMAL(10,2)
);
SELECT * FROM emp;
ALTER TABLE emp ADD COLUMN email VARCHAR(50);
SELECT * FROM emp;
INSERT INTO emp(empno, emp, name, job, basic, da, hra, pf, grosspay, netpay, email) VALUES
(1, 'E001', 'Alice', 'Manager', 50000, 15000, 20000, 5000, 85000, 80000, 'alice@example.com'),
(2, 'E002', 'Bob', 'Analyst', 30000, 9000, 12000, 3000, 51000, 48000, 'bob@example.com'),
(3, 'E003', 'Charlie', 'Clerk', 20000, 6000, 8000, 2000, 34000, 32000, 'charlie@example.com'),
(4, 'E004', 'David', 'Salesman', 25000, 7500, 10000, 2500, 42500, 40000, 'david@example.com'),
(5, 'E005', 'Eva', 'Clerk', 18000, 5400, 7200, 1800, 30600, 28800, 'eva@example.com');
SELECT * FROM emp;
SELECT
name AS employeename,
job,
basic
FROM
emp
WHERE
basic = (
SELECT MIN(basic)
FROM emp AS e2
WHERE e2.job = emp.job
);
DELIMITER $$
CREATE PROCEDURE show_employees()
BEGIN
DECLARE v_EmpName VARCHAR(50);
DECLARE v_Job VARCHAR(50);
DECLARE v_Basic DECIMAL(10,2);
DECLARE done INT DEFAULT 0;
DECLARE emp_cursor CURSOR FOR
SELECT EmpName, Job, Basic FROM Emp;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN emp_cursor;
read_loop: LOOP
FETCH emp_cursor INTO v_EmpName, v_Job, v_Basic;
IF done = 1 THEN
LEAVE read_loop;
END IF;
SELECT CONCAT('Name: ', v_EmpName, ', Department: ', v_Job, ', Salary: ', v_Basic) AS Employee_Details;
END LOOP;
CLOSE emp_cursor;
END$$
DELIMITER ;
CALL show_employees();
Telegram: DB developers channel