SQL & MySQL Interview Questions 41-45 (Views, Stored Procedures, Triggers, Functions)

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.

41. What are MySQL views? Why are they used?

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.

Why are they used?

-- 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

42. What are stored procedures? Explain advantages.

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.

Advantages:

-- 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

43. What are triggers in MySQL? Provide examples.

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

44. What are user-defined functions (UDFs) in MySQL?

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

45. What is the difference between procedures and functions in MySQL?

This is a very common follow-up question. Both are reusable blocks of code, but they have different purposes and rules.