ভিউ, স্টোরড প্রসিডিওর, ফাংশন আর ট্রিগার

CREATE VIEW order_totals AS
SELECT o.order_id,
       o.customer_id,
       o.order_date,
       o.status,
       SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.customer_id, o.order_date, o.status;

SELECT c.name, COUNT(*) AS orders, SUM(t.total) AS spent
FROM order_totals t
JOIN customers c ON c.customer_id = t.customer_id
WHERE t.status = 'delivered'
GROUP BY c.name
ORDER BY spent DESC
LIMIT 3;

+---------------+--------+-------+
| name          | orders | spent |
+---------------+--------+-------+
| Nadia Rahman  |      3 | 23600 |
| Tanvir Ahmed  |      2 | 19500 |
| Farhana Akter |      3 |  9500 |
+---------------+--------+-------+

UPDATE order_items SET quantity = 3 WHERE order_id = 1 AND product_id = 4;
SELECT order_id, total FROM order_totals WHERE order_id = 1;

+----------+-------+
| order_id | total |
+----------+-------+
|        1 |  3900 |
+----------+-------+

UPDATE order_totals SET total = 0 WHERE order_id = 1;

Error: cannot modify order_totals because it is a view

-- PostgreSQL
CREATE VIEW dhaka_customers AS
SELECT customer_id, name, email, city
FROM customers
WHERE city = 'Dhaka'
WITH CHECK OPTION;

UPDATE dhaka_customers SET name = 'Nadia R. Rahman' WHERE customer_id = 1;
SELECT customer_id, name, city FROM customers WHERE customer_id = 1;

UPDATE dhaka_customers SET city = 'Sylhet' WHERE customer_id = 3;

+-------------+-----------------+-------+
| customer_id | name            | city  |
+-------------+-----------------+-------+
|           1 | Nadia R. Rahman | Dhaka |
+-------------+-----------------+-------+
Error: new row violates check option for view "dhaka_customers"

-- PostgreSQL
CREATE VIEW order_totals AS
SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id;

UPDATE order_totals SET total = 0 WHERE order_id = 1;

Error: cannot update view "order_totals"

-- PostgreSQL
CREATE MATERIALIZED VIEW product_sales AS
SELECT p.product_id,
       p.name,
       SUM(oi.quantity) AS units,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi ON oi.product_id = p.product_id
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY p.product_id, p.name;

CREATE UNIQUE INDEX idx_product_sales_id ON product_sales (product_id);

SELECT * FROM product_sales ORDER BY revenue DESC LIMIT 3;

+------------+-------------------------+-------+---------+
| product_id | name                    | units | revenue |
+------------+-------------------------+-------+---------+
|          7 | AI Engineering Bootcamp |     2 |   30000 |
|          5 | 27-inch Monitor         |     1 |   18500 |
|          3 | Mechanical Keyboard     |     2 |    8550 |
+------------+-------------------------+-------+---------+

-- PostgreSQL
INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (15, 4, '2026-06-20', 'delivered');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (15, 5, 2, 18500);

SELECT * FROM product_sales WHERE product_id = 5;

REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales;

SELECT * FROM product_sales WHERE product_id = 5;

+------------+-----------------+-------+---------+
| product_id | name            | units | revenue |
+------------+-----------------+-------+---------+
|          5 | 27-inch Monitor |     1 |   18500 |
+------------+-----------------+-------+---------+
+------------+-----------------+-------+---------+
| product_id | name            | units | revenue |
+------------+-----------------+-------+---------+
|          5 | 27-inch Monitor |     3 |   55500 |
+------------+-----------------+-------+---------+

mysql shop <<'SQL'
DELIMITER //
CREATE PROCEDURE order_summary(IN p_order_id INT, OUT p_items INT, OUT p_total INT)
BEGIN
    SELECT COUNT(*), COALESCE(SUM(quantity * unit_price), 0)
    INTO p_items, p_total
    FROM order_items
    WHERE order_id = p_order_id;
END //
DELIMITER ;
SQL

-- MySQL
SELECT product_id, name, stock FROM products WHERE product_id IN (6, 9) ORDER BY product_id;

+------------+-----------------+-------+
| product_id | name            | stock |
+------------+-----------------+-------+
|          6 | SQL Masterclass | NULL  |
|          9 | USB-C Hub       |     0 |
+------------+-----------------+-------+

import os

import pymysql

mysql = pymysql.connect(
    host=os.environ["MYSQL_HOST"], port=int(os.environ["MYSQL_PORT"]),
    user=os.environ["MYSQL_USER"], password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"], autocommit=True,
)

ORDER_SUMMARY = """
CREATE PROCEDURE order_summary(IN p_order_id INT, OUT p_items INT, OUT p_total INT)
BEGIN
    SELECT COUNT(*), COALESCE(SUM(quantity * unit_price), 0)
    INTO p_items, p_total
    FROM order_items
    WHERE order_id = p_order_id;
END"""

RESTOCK = """
CREATE PROCEDURE restock(IN p_product_id INT, IN p_qty INT, OUT p_new_stock INT)
BEGIN
    DECLARE v_stock INT;
    IF p_qty <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'quantity must be positive';
    END IF;
    SELECT stock INTO v_stock FROM products WHERE product_id = p_product_id;
    IF v_stock IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'unknown product or stock not tracked';
    END IF;
    UPDATE products SET stock = stock + p_qty WHERE product_id = p_product_id;
    SET p_new_stock = v_stock + p_qty;
END"""

with mysql.cursor() as cur:
    for name, ddl in (("order_summary", ORDER_SUMMARY), ("restock", RESTOCK)):
        cur.execute(f"DROP PROCEDURE IF EXISTS {name}")
        cur.execute(ddl)
        print("created", name)

created order_summary
created restock

-- MySQL
CALL order_summary(6, @items, @total);
SELECT @items AS items, @total AS total;

CALL restock(9, 10, @new_stock);
SELECT @new_stock AS new_stock;

CALL restock(6, 5, @new_stock);

+-------+-------+
| items | total |
+-------+-------+
|     1 | 15000 |
+-------+-------+
+-----------+
| new_stock |
+-----------+
|        10 |
+-----------+
Error: ERROR 1644: unknown product or stock not tracked

-- MySQL
CALL restock(9, -5, @new_stock);

Error: ERROR 1644: quantity must be positive

-- MySQL
CREATE FUNCTION order_total(p_order_id INT) RETURNS INT
READS SQL DATA
RETURN (SELECT COALESCE(SUM(quantity * unit_price), 0)
        FROM order_items WHERE order_id = p_order_id);

SELECT order_id, status, order_total(order_id) AS total
FROM orders
WHERE customer_id = 1
ORDER BY order_id;

+----------+-----------+-------+
| order_id | status    | total |
+----------+-----------+-------+
|        1 | delivered |  2100 |
|        3 | delivered |  3000 |
|       10 | delivered | 18500 |
+----------+-----------+-------+

-- PostgreSQL
CREATE FUNCTION order_total(p_order_id int) RETURNS bigint
LANGUAGE sql STABLE
AS $$
    SELECT COALESCE(SUM(quantity * unit_price), 0)
    FROM order_items WHERE order_id = p_order_id
$$;

SELECT order_id, status, order_total(order_id) AS total
FROM orders
WHERE customer_id = 1
ORDER BY order_id;

+----------+-----------+-------+
| order_id | status    | total |
+----------+-----------+-------+
|        1 | delivered |  2100 |
|        3 | delivered |  3000 |
|       10 | delivered | 18500 |
+----------+-----------+-------+

CREATE TRIGGER trg_products_price_guard
BEFORE UPDATE OF price ON products
FOR EACH ROW
WHEN NEW.price < OLD.price * 0.5
BEGIN
    SELECT RAISE(ABORT, 'price cut of more than 50% needs approval');
END;

UPDATE products SET price = 2000 WHERE product_id = 3;

Error: price cut of more than 50% needs approval

-- MySQL
CREATE TRIGGER trg_products_price_guard
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price < OLD.price * 0.5 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'price cut of more than 50% needs approval';
    END IF;
END;

UPDATE products SET price = 2000 WHERE product_id = 3;

Error: ERROR 1644: price cut of more than 50% needs approval

CREATE TABLE product_audit (
    audit_id   INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    action     TEXT    NOT NULL,
    old_values TEXT,
    new_values TEXT,
    changed_at TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_products_audit_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_audit (product_id, action, new_values)
    VALUES (NEW.product_id, 'insert', json_object('price', NEW.price, 'stock', NEW.stock));
END;

CREATE TRIGGER trg_products_audit_update
AFTER UPDATE ON products
FOR EACH ROW
WHEN OLD.price IS NOT NEW.price OR OLD.stock IS NOT NEW.stock
BEGIN
    INSERT INTO product_audit (product_id, action, old_values, new_values)
    VALUES (OLD.product_id, 'update',
            json_object('price', OLD.price, 'stock', OLD.stock),
            json_object('price', NEW.price, 'stock', NEW.stock));
END;

CREATE TRIGGER trg_products_audit_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_audit (product_id, action, old_values)
    VALUES (OLD.product_id, 'delete', json_object('price', OLD.price, 'stock', OLD.stock));
END;

UPDATE products SET price = 4200 WHERE product_id = 3;
UPDATE products SET stock = stock - 1 WHERE product_id IN (1, 4);
UPDATE products SET name = 'Python Crash Course (3rd ed.)' WHERE product_id = 1;
INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (12, 'Webcam', 2, 3500, 20);
DELETE FROM products WHERE product_id = 11;

SELECT audit_id, product_id, action, old_values, new_values
FROM product_audit
ORDER BY audit_id;

+----------+------------+--------+-----------------------------+---------------------------+
| audit_id | product_id | action | old_values                  | new_values                |
+----------+------------+--------+-----------------------------+---------------------------+
|        1 |          3 | update | {"price":4500,"stock":12}   | {"price":4200,"stock":12} |
|        2 |          1 | update | {"price":1200,"stock":40}   | {"price":1200,"stock":39} |
|        3 |          4 | update | {"price":900,"stock":60}    | {"price":900,"stock":59}  |
|        4 |         12 | insert | NULL                        | {"price":3500,"stock":20} |
|        5 |         11 | delete | {"price":1000,"stock":null} | NULL                      |
+----------+------------+--------+-----------------------------+---------------------------+

-- MySQL
CREATE TABLE daily_sales (
    sales_day DATE PRIMARY KEY,
    revenue   INT NOT NULL
);

CREATE EVENT refresh_daily_sales
ON SCHEDULE EVERY 1 DAY STARTS '2030-01-01 02:00:00'
DO
    REPLACE INTO daily_sales (sales_day, revenue)
    SELECT o.order_date, SUM(oi.quantity * oi.unit_price)
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.order_date;

SELECT EVENT_NAME, INTERVAL_VALUE, INTERVAL_FIELD, STARTS, STATUS
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = DATABASE();

+---------------------+----------------+----------------+---------------------+---------+
| EVENT_NAME          | INTERVAL_VALUE | INTERVAL_FIELD | STARTS              | STATUS  |
+---------------------+----------------+----------------+---------------------+---------+
| refresh_daily_sales | 1              | DAY            | 2030-01-01 02:00:00 | ENABLED |
+---------------------+----------------+----------------+---------------------+---------+

-- ১. সহজ
CREATE VIEW product_catalog AS
SELECT p.name AS product, COALESCE(c.name, 'Uncategorised') AS category, p.price
FROM products p
LEFT JOIN categories c ON c.category_id = p.category_id;

SELECT * FROM product_catalog ORDER BY price DESC, product LIMIT 3;

-- ২. মাঝারি
CREATE TRIGGER trg_order_items_price_check
BEFORE INSERT ON order_items
FOR EACH ROW
WHEN NEW.unit_price > (SELECT price FROM products WHERE product_id = NEW.product_id)
BEGIN
    SELECT RAISE(ABORT, 'unit_price above the current list price');
END;

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (13, 4, 1, 99999);

Error: unit_price above the current list price

-- MySQL
-- ৩. কঠিন
CREATE FUNCTION customer_spend(p_customer_id INT) RETURNS INT
READS SQL DATA
RETURN (SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)
        FROM orders o
        JOIN order_items oi ON oi.order_id = o.order_id
        WHERE o.customer_id = p_customer_id AND o.status <> 'cancelled');

SELECT customer_id, name, customer_spend(customer_id) AS spend
FROM customers
ORDER BY spend DESC, customer_id;