SQLize Online / PHPize Online  /  SQLtest Online

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 employee WHERE basic = ( SELECT MIN(basic) FROM employee AS e2 WHERE e2.job = employee.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 Employee; 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();