Loading…
Hello! In our previous lessons, we've focused on structuring data and writing queries to retrieve it. Now, we're moving into a more advanced and powerful area: server-side SQL.
This lesson covers the "programs" you can store inside the database itself. We'll explore virtual tables (Views), reusable scripts (Stored Procedures), automatic actions (Triggers), and custom formulas (Functions). Understanding these concepts is key to building secure, efficient, and maintainable applications.
A View is a virtual tablewhose content is defined by a `SELECT` query. It's like a saved query that you can interact with as if it were a real table.
Analogy: A View is like a "Smart Folder" or a "Saved Search" on your computer. The folder doesn't contain any files itself; it just shows you a live, filtered list of files from other folders. If you add a new file that matches the filter, it automatically appears in the Smart Folder.
Views do not store data themselves (unless they are "materialized views," a more advanced topic). They just run their underlying query every time you access them.
-- Setup: Two tables
CREATE TABLE Employees (
id INT, name VARCHAR(50), salary INT, dept_id INT
);
CREATE TABLE Departments (
id INT, name VARCHAR(50)
);
INSERT INTO Employees VALUES (1, 'Alice', 90000, 1);
INSERT INTO Employees VALUES (2, 'Bob', 85000, 2);
INSERT INTO Departments VALUES (1, 'Engineering'), (2, 'Sales');
-- 1. Create a View to simplify the data
CREATE VIEW EmployeeDetails AS
SELECT
e.name AS employee_name,
e.salary,
d.name AS department_name
FROM
Employees e
JOIN
Departments d ON e.dept_id = d.id;
-- 2. Now, you can query the View like a simple table
SELECT * FROM EmployeeDetails WHERE department_name = 'Engineering';
Expected Output (from the final `SELECT`):
+---------------+--------+-----------------+
| employee_name | salary | department_name |
+---------------+--------+-----------------+
| Alice | 90000 | Engineering |
+---------------+--------+-----------------+
1 row in set
A stored procedureis a set of one or more SQL statements that are pre-compiled and stored on the database server. You can then execute this "script" by simply calling its name.
Analogy: It's like a "macro" in Excel or a "function" in Python. Instead of re-typing 10 complex steps every time, you save those steps as `RunMonthlyReport()`, and just run that one command.
-- We need to change the delimiter to define a procedure
DELIMITER //
CREATE PROCEDURE sp_GetEmployeeByDept(IN dept_name VARCHAR(50))
BEGIN
SELECT * FROM EmployeeDetails -- Using our View from Q41
WHERE department_name = dept_name;
END //
DELIMITER ;
-- Now, we can CALL the procedure
CALL sp_GetEmployeeByDept('Sales');
Expected Output:
+---------------+--------+-----------------+
| employee_name | salary | department_name |
+---------------+--------+-----------------+
| Bob | 85000 | Sales |
+---------------+--------+-----------------+
1 row in set
A trigger is a special type of stored procedure that is automatically executed (it "fires") when a specific event happens on a table.
The events are `INSERT`, `UPDATE`, or `DELETE`. You can set the trigger to fire `BEFORE` or `AFTER` the event.
Analogy: A trigger is a "motion-sensor camera" on your table. You don't watch the table 24/7. You just set up a rule: "If (BEFORE) anyone tries to `UPDATE` a row,automatically take a snapshot of the old data and save it in my `AuditLog` table."
Triggers are powerful but can be hard to debug because they are invisible. They are mostly used for:
-- Setup: Create a log table
CREATE TABLE AuditLog (
id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
old_salary INT,
new_salary INT,
change_time TIMESTAMP DEFAULT NOW()
);
-- Create a trigger that fires BEFORE an UPDATE on Employees
DELIMITER //
CREATE TRIGGER trg_BeforeEmployeeSalaryUpdate
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
-- 'OLD' and 'NEW' are special keywords inside a trigger
-- OLD.salary is the value *before* the update
-- NEW.salary is the value *after* the update
IF OLD.salary != NEW.salary THEN
INSERT INTO AuditLog (employee_id, old_salary, new_salary)
VALUES (OLD.id, OLD.salary, NEW.salary);
END IF;
END //
DELIMITER ;
-- Now, let's test it. This UPDATE will fire the trigger.
UPDATE Employees SET salary = 95000 WHERE id = 1;
-- Let's check the log table!
SELECT * FROM AuditLog;
Expected Output (from `SELECT * FROM AuditLog`):
+----+-------------+------------+------------+---------------------+
| id | employee_id | old_salary | new_salary | change_time |
+----+-------------+------------+------------+---------------------+
| 1 | 1 | 90000 | 95000 | 2025-11-17 21:44:00 |
+----+-------------+------------+------------+---------------------+
1 row in set
A User-Defined Function (UDF) is a custom function that you create and store on the database server. Once created, you can use it in your SQL queries just like built-in functions (e.g., `UPPER()`, `NOW()`, `COUNT()`).
Analogy: This is like creating a custom formula in Excel. Excel gives you `SUM()` and `AVERAGE()`, but you might create your own formula called `CalculateFinalPriceWithTax()` that you can reuse in any cell.
The key rule is that a function must take some inputs and must return a single value.
-- Create a function to categorize salary levels
DELIMITER //
CREATE FUNCTION fn_GetSalaryLevel(salary INT)
RETURNS VARCHAR(20)
-- DETERMINISTIC means it always returns the same
-- result for the same inputs
DETERMINISTIC
BEGIN
DECLARE level VARCHAR(20);
IF salary > 90000 THEN
SET level = 'High';
ELSEIF salary > 80000 THEN
SET level = 'Medium';
ELSE
SET level = 'Standard';
END IF;
RETURN (level);
END //
DELIMITER ;
-- Now, use our UDF in a SELECT statement!
SELECT
name,
salary,
fn_GetSalaryLevel(salary) AS level
FROM Employees;
Expected Output:
+-------+--------+----------+
| name | salary | level |
+-------+--------+----------+
| Alice | 95000 | High |
| Bob | 85000 | Medium |
+-------+--------+----------+
2 rows in set
This is a very common follow-up question. Both are reusable blocks of code, but they have different purposes and rules.
| Feature | Stored Procedure | User-Defined Function (UDF) |
|---|---|---|
| Main Purpose | To perform an action or set of actions. (e.g., `sp_RegisterUser`) | To perform a calculation and return a value. (e.g., `fn_GetPrice()`) |
| How to Call | By itself: `CALL MyProcedure();` | Inside a query: `SELECT MyFunction(col) FROM ...` |
| Return Value | Cannot use `RETURN`. Can use `OUT` parameters to pass data back. | Must return one single value using `RETURN`. |
| Transactions | Can use `COMMIT` and `ROLLBACK` inside. | Cannot manage transactions. |
| Data Modification | Can run `INSERT`, `UPDATE`, `DELETE`. | Generally cannot (or should not) modify data. Meant for reads. |
3 questions · no sign-up, nothing stored
Progress is stored in this browser only - no sign-up, nothing sent anywhere.