← SQL roadmap

Topic 28 of 52 · Expert

Views, Procedures & Triggers

Reusable logic stored inside the database.

Concept

A view is a saved SELECT that behaves like a table — great for hiding complexity or restricting columns.

Stored procedures bundle SQL with parameters and control flow; functions return a single value and can be used in queries.

Triggers run automatically BEFORE/AFTER INSERT, UPDATE or DELETE — useful for audit logs, but hidden logic can surprise developers.

Code (SQL · MySQL 8)

CREATE VIEW v_customer_revenue AS
SELECT c.id, c.name, SUM(o.amount) AS revenue
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

DELIMITER //
CREATE PROCEDURE top_customers(IN n INT)
BEGIN
  SELECT * FROM v_customer_revenue ORDER BY revenue DESC LIMIT n;
END //
DELIMITER ;

CALL top_customers(5);

Remember

  • Views store the query, not the data.
  • Triggers add hidden side effects — document them.
  • Use DELIMITER when creating procedures in the MySQL client.

Interview questions

View vs materialized view?

A view reruns its query each time. A materialized view stores the result and must be refreshed. MySQL has no native materialized views; use summary tables.

Procedure vs function?

A function returns one value and can be used in SELECT. A procedure is called with CALL, can return result sets and use OUT parameters.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Views, Procedures & Triggers” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.