Scribd
Upload
3 views3 pages

SQL Code

The document contains SQL code for creating an 'employees' table and inserting records. It includes a stored procedure to update employee salaries, a function to retrieve salaries, and a trigger to log salary changes in an 'audit_table'. The final output shows updated employee salaries and audit records after salary adjustments.

Uploaded by

Naumaan Ahmed
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views3 pages

SQL Code

The document contains SQL code for creating an 'employees' table and inserting records. It includes a stored procedure to update employee salaries, a function to retrieve salaries, and a trigger to log salary changes in an 'audit_table'. The final output shows updated employee salaries and audit records after salary adjustments.

Uploaded by

Naumaan Ahmed
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQL Code:

CREATE TABLE employees (

id INT PRIMARY KEY,

name VARCHAR(50),

salary INT

);

INSERT INTO employees VALUES (101, 'Rahul', 20000);

INSERT INTO employees VALUES (102, 'Anita', 25000);

DELIMITER $$

CREATE PROCEDURE update_salary(IN emp_id INT, IN inc_amt INT)

BEGIN

UPDATE employees

SET salary = salary + inc_amt

WHERE id = emp_id;

END$$

DELIMITER ;

CALL update_salary(101, 5000);

SELECT * FROM employees;

DELIMITER $$

CREATE FUNCTION get_salary(emp_id INT)

RETURNS INT

DETERMINISTIC

BEGIN
DECLARE sal INT;

SELECT salary INTO sal FROM employees WHERE id = emp_id;

RETURN sal;

END$$

DELIMITER ;

SELECT get_salary(101);

CREATE TABLE audit_table (

emp_id INT,

old_salary INT,

new_salary INT

);

DELIMITER $$

CREATE TRIGGER salary_audit

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

INSERT INTO audit_table(emp_id, old_salary, new_salary)

VALUES ([Link], [Link], [Link]);

END$$

DELIMITER ;

CALL update_salary(102, 3000);

SELECT * FROM audit_table;


Output:
+-----+-------+--------+
| id | name | salary |
+-----+-------+--------+
| 101 | Rahul | 25000 |
| 102 | Anita | 25000 |
+-----+-------+--------+
+-----------------+
| get_salary(101) |
+-----------------+
| 25000 |
+-----------------+
+--------+------------+------------+
| emp_id | old_salary | new_salary |
+--------+------------+------------+
| 102 | 25000 | 28000 |
+--------+---------

You might also like