Views, Stored Procedures, Triggers and SQL Security

Beyond SELECT queries, interviewers ask about the database objects that encapsulate logic — views, procedures, triggers — and, increasingly, about security: SQL injection and least-privilege access. These are everyday concerns for backend developers.

1. What is a view and why use one?

A view is a saved query that behaves like a virtual table. Use it to simplify complex joins, present a stable interface while tables change, and restrict access by exposing only certain columns or rows. A normal view stores no data — it runs its query each time.

CREATE VIEW active_students AS
SELECT s.id, s.name, d.name AS department
FROM students s
JOIN departments d ON d.id = s.dept_id
WHERE s.status = 'ACTIVE';

SELECT * FROM active_students WHERE department = 'CSE';

2. What is a materialised view?

A view whose result is stored physically and refreshed on demand or on a schedule. Reads are fast — ideal for dashboards and heavy aggregations — but data can be stale between refreshes. Supported natively in PostgreSQL and Oracle; in MySQL you emulate it with summary tables.

CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, SUM(amount) AS revenue, COUNT(*) AS orders
FROM orders
GROUP BY order_date;

REFRESH MATERIALIZED VIEW daily_sales;   -- e.g. every night

3. Stored procedure vs function?

A function returns a value and can be used inside SQL statements; it should not change data. A procedure performs actions, may return result sets or output parameters, can manage transactions, and is called with CALL/EXEC. Procedures reduce network round trips but put business logic in the database, which is harder to version and test.

DELIMITER //
CREATE PROCEDURE transfer(IN from_id INT, IN to_id INT, IN amt DECIMAL(10,2))
BEGIN
  START TRANSACTION;
  UPDATE accounts SET balance = balance - amt WHERE id = from_id AND balance >= amt;
  IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
  END IF;
  UPDATE accounts SET balance = balance + amt WHERE id = to_id;
  COMMIT;
END //
DELIMITER ;

4. What are triggers and when should you avoid them?

Triggers run automatically BEFORE or AFTER INSERT, UPDATE or DELETE on a table — good for audit logs, enforcing complex rules or maintaining summary columns. Avoid heavy logic in them: they are invisible to application developers, run inside the same transaction (slowing writes), and can cascade unexpectedly.

CREATE TRIGGER salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO salary_history(employee_id, old_salary, new_salary, changed_at)
VALUES (OLD.id, OLD.salary, NEW.salary, NOW());

5. What is SQL injection?

An attack where user input is concatenated into a SQL string and changes the query's meaning — for example entering ' OR '1'='1 as a password to bypass login, or ; DROP TABLE users. It can expose or destroy the whole database.

-- vulnerable application code (any language):
-- "SELECT * FROM users WHERE email = '" + email + "' AND password = '" + pwd + "'"
-- input pwd:  ' OR '1'='1
SELECT * FROM users WHERE email = 'a@b.com' AND password = '' OR '1'='1';   -- returns every user

6. How do you prevent SQL injection?

Use parameterised queries / prepared statements everywhere (or an ORM that uses them) so input is always treated as data. Additionally validate input, allow-list dynamic identifiers such as sort columns, give the application account least privilege, and never show raw database errors to users.

-- Prepared statement: the value is sent separately from the SQL text
PREPARE find_user FROM 'SELECT id, name FROM users WHERE email = ?';
SET @email = 'asha@example.com';
EXECUTE find_user USING @email;

-- In application code: cursor.execute("SELECT * FROM users WHERE email = %s", (email,))

7. How do GRANT and REVOKE support least privilege?

Create separate database users per application or role and grant only what each needs: a reporting user gets SELECT on specific views; the web app gets SELECT/INSERT/UPDATE on its tables but no DROP or GRANT rights. Revoke access when it is no longer needed.

CREATE USER 'report_ro'@'%' IDENTIFIED BY '...';
GRANT SELECT ON school.active_students TO 'report_ro'@'%';

CREATE USER 'web_app'@'10.0.%' IDENTIFIED BY '...';
GRANT SELECT, INSERT, UPDATE ON school.* TO 'web_app'@'10.0.%';
REVOKE UPDATE ON school.salaries FROM 'web_app'@'10.0.%';

8. What is a cursor and why is it often discouraged?

A cursor processes a result set row by row inside a procedure. It is sometimes necessary for complex procedural logic, but set-based SQL (one UPDATE with joins or window functions) is usually far faster because the database optimises the whole operation at once.