Proyecto avanzado SQL: sistema completo de ventas, auditoría y reportes

Este proyecto integra todos los conceptos avanzados vistos: CTEs, funciones, procedimientos, triggers, auditoría, normalización, desnormalización, concurrencia y optimización. Construiremos un sistema profesional de ventas con reportes dinámicos y auditoría completa.

1. Estructura del proyecto

El sistema se compone de:

• Tablas normalizadas de productos, clientes y ventas.
• Tablas desnormalizadas para reportes rápidos.
• Funciones para cálculos.
• Procedimientos para operaciones críticas.
• Triggers para auditoría.
• Materialized views para analítica.

2. Tablas principales

CREATE TABLE productos (
    id INT PRIMARY KEY,
    nombre VARCHAR(50),
    precio INT
);

CREATE TABLE clientes (
    id INT PRIMARY KEY,
    nombre VARCHAR(50)
);

CREATE TABLE ventas (
    id INT PRIMARY KEY,
    cliente_id INT,
    producto_id INT,
    cantidad INT,
    fecha DATETIME
);
    

3. Funciones avanzadas

CREATE FUNCTION total_venta(id_venta INT)
RETURNS INT
BEGIN
    DECLARE total INT;
    SELECT cantidad * precio INTO total
    FROM ventas v
    JOIN productos p ON p.id = v.producto_id
    WHERE v.id = id_venta;
    RETURN total;
END;
    

4. Procedimientos

CREATE PROCEDURE registrar_venta(IN cliente INT, IN producto INT, IN cantidad INT)
BEGIN
    INSERT INTO ventas(cliente_id, producto_id, cantidad, fecha)
    VALUES(cliente, producto, cantidad, NOW());
END;
    

5. Triggers de auditoría

CREATE TRIGGER aud_ventas
AFTER INSERT OR UPDATE OR DELETE ON ventas
FOR EACH ROW
BEGIN
    INSERT INTO auditoria(tabla, accion, fecha)
    VALUES('ventas', 'cambio', NOW());
END;
    

6. Reportes avanzados

SELECT cliente_id, SUM(cantidad) AS total
FROM ventas
GROUP BY cliente_id;
    

7. CTEs y análisis

WITH ventas_mes AS (
    SELECT producto_id, SUM(cantidad) AS total
    FROM ventas
    WHERE fecha >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY producto_id
)
SELECT * FROM ventas_mes;
    

8. Pivot y Unpivot

SELECT *
FROM ventas
PIVOT (
    SUM(cantidad)
    FOR producto_id IN (1,2,3)
);
    

9. Optimización

• Índices en claves foráneas.
• Materialized views.
• Desnormalización parcial.
• Evitar subconsultas innecesarias.

10. Ejercicios finales

WITH c AS (SELECT * FROM ventas WHERE fecha > NOW()-INTERVAL 30 DAY)
SELECT * FROM c;
            

SELECT * FROM ventas PIVOT(SUM(cantidad) FOR categoria IN ('A','B'));
            

-- Auditoría con triggers AFTER INSERT/UPDATE/DELETE
            

-- Índices y materialized views.
            

11. Preguntas frecuentes

¿Este proyecto es escalable?

Sí, está diseñado con buenas prácticas.

¿Puedo extenderlo?

Sí, puedes añadir módulos de facturación, stock o analítica.

Fin del módulo avanzado

Has completado todas las guías avanzadas de SQL.