Stored Procedures and Views

-- Create a view:
CREATE VIEW high_earners AS
SELECT name, department, salary
FROM employees
WHERE salary > 60000;

-- Use it like a table:
SELECT * FROM high_earners
WHERE department = 'Engineering';

A VIEW is a saved query that behaves like a virtual table. Benefits: simplifies complex queries, enforces security (show only certain columns), and centralises business logic.

-- Regular VIEW: runs the underlying query every time you query it
-- Data is always fresh but can be slow for complex queries

-- Materialized VIEW (PostgreSQL): stores the query result on disk
CREATE MATERIALIZED VIEW monthly_sales_mv AS
SELECT DATE_FORMAT(order_date,'%Y-%m') AS month, SUM(amount) AS rev
FROM orders GROUP BY 1;

-- Must refresh manually:
REFRESH MATERIALIZED VIEW monthly_sales_mv;

Regular views are always current but recompute each time. Materialized views are cached — fast but may be stale. Good for expensive aggregation queries used in dashboards.

DELIMITER //
CREATE PROCEDURE GetDeptEmployees(IN dept_name VARCHAR(100))
BEGIN
  SELECT name, salary
  FROM employees
  WHERE department = dept_name
  ORDER BY salary DESC;
END //
DELIMITER ;

-- Call it:
CALL GetDeptEmployees('Sales');

Stored procedures are reusable SQL programs stored in the database. They accept parameters, can contain IF/ELSE logic and loops, and reduce repetition. Used in ETL pipelines and report generation.

-- Fire automatically when a table event happens:
CREATE TRIGGER log_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF OLD.salary != NEW.salary THEN
    INSERT INTO salary_audit(emp_id, old_sal, new_sal, changed_at)
    VALUES(NEW.id, OLD.salary, NEW.salary, NOW());
  END IF;
END;

Triggers run automatically on INSERT, UPDATE, or DELETE. Common use: audit logging, enforcing business rules, maintaining derived data. Use sparingly — hidden logic makes debugging hard.