Data Analysis SQL Advanced

Stored Procedures

A SQL Stored Procedure is a collection of SQL statements bundled together to perform a specific task. These procedures are stored in the database and can be called upon by users, applications, or other procedures. Stored procedures are essential for automating database tasks, improving efficiency, and reducing redundancy. By encapsulating logic within stored procedures, developers can streamline their workflow and enforce consistent business rules across multiple applications and systems.

CREATE PROCEDURE procedure_name
(parameter1 data_type, parameter2 data_type, ...)
AS
BEGIN
-- SQL statements to be executed
END

Types of SQL Stored Procedures

1. System Stored Procedures(ss_help, ss_rename)
2. User-Defined Stored Procedures (UDPs)
3. Extended Stored Procedures
4. CLR Stored Procedures

SQL Triggers

A trigger is a special stored procedure in a database that automatically executes when specific events (like INSERT, UPDATE, or DELETE) occur on a table. Triggers help automate tasks, maintain data consistency, and record database activities. Each trigger is tied to a particular table and runs without manual execution.

Uses of SQL Triggers

Automation: Handles repetitive tasks.
Data Integrity: Ensures clean and accurate data.
Business Rules: Enforces database logic.
Audit Trails: Tracks and records changes.

Types of SQL Triggers

*1. DDL Triggers *
CREATE TRIGGER prevent_table_creation
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
PRINT 'you can not create, drop and alter table in this database';
ROLLBACK;
END;

2. DML Triggers
CREATE TRIGGER prevent_update
ON students
FOR UPDATE
AS
BEGIN
PRINT 'You can not insert, update and delete this table i';
ROLLBACK;
END;

3. Logon Triggers

CREATE TRIGGER track_logon
ON LOGON
AS
BEGIN
PRINT 'A new user has logged in.';
END;

GRAND and REVOKE

CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'StrongPass123';

GRANT ALL PRIVILEGES ON companydb.* TO 'report_user'@'localhost';

GRANT SELECT ON companydb.Employees TO 'report_user'@'localhost';

GRANT INSERT, UPDATE ON companydb.Employees TO 'report_user'@'localhost';

REVOKE INSERT ON companydb.Employees FROM 'report_user'@'localhost';

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'report_user'@'localhost';

FLUSH PRIVILEGES;

SHOW GRANTS FOR 'report_user'@'localhost';

select * from Employees e 


-- Create a stored procedure named "GetEmployeesBySalary"
DELIMITER //
CREATE PROCEDURE GetEmployeesBySalaryNew(IN p_salary INT)
BEGIN
    SELECT id, first_name, last_name 
    FROM Employees
    WHERE salary = p_salary;
END //
DELIMITER ;

-- Execute the stored procedure with parameter "Sri lanka"
CALL GetEmployeesBySalaryNew(60000);

DELIMITER //
create function calcuate_bonus(salary INT)
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    RETURN salary*0.5;
END

select emp_id,first_name,salary,calcuate_bonus(salary) from Employees e 


DROP PROCEDURE IF EXISTS GetEmployeesBySalary;



DELIMITER $$

CREATE TRIGGER after_employee_insert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log (emp_id, action, created_at)
    VALUES (NEW.emp_id, 'Inserted', NOW());
END

$$

DELIMITER ;

create table employee_log(
emp_id int,
action varchar(50),
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
)


INSERT INTO employees.Employees
(emp_id, first_name, last_name, dept_id, salary, hire_date, manager_id)
VALUES(112, 'Ashok', 'Rohit', 4, 90000.00, '2021-09-12', 103);

select * from employee_log el 




select * from Employees e 

Explain select * from Employees e where emp_id=101;

Explain select * from Employees e where first_name='Raj';


SET @running_total := 0;
select cumulative_sum from (
SELECT
    salary,
    (@running_total := @running_total + salary) AS cumulative_sum
FROM
    Employees e 
ORDER BY
    salary) t order by cumulative_sum desc limit 1 ;