Academia Web

Tema

Proyecto: tienda y escuela ​

Llegó el momento de juntar todo. En este proyecto final diseñarás y construirás dos bases de datos completas como lo harías para un cliente real:

  1. Tienda en línea ampliada: clientes, direcciones, catálogo con SKU, pedidos, pagos, reseñas e inventario, con índices, vistas, una transacción de compra y más de 10 consultas de negocio.
  2. Sistema escolar: alumnos, profesores, materias, grupos, inscripciones y calificaciones, con promedios, reprobados y rankings.

Los scripts son portables: funcionan en MySQL 8.0.16+ y PostgreSQL 13+ (las diferencias se indican). Todos los resultados mostrados salen de estos datos.

Parte 1: Tienda en línea ​

1.1 Requerimientos del cliente ​

"Vendo electrónica, libros, artículos del hogar y deportes. Mis clientes pueden tener varias direcciones de envío. Cada pedido se envía a una dirección y se paga con tarjeta, OXXO o transferencia; a veces hay reembolsos. Quiero que los clientes dejen reseñas (una por producto) y llevar el historial de entradas y salidas del almacén. Necesito reportes de ventas por mes, productos estrella, clientes frecuentes y clientes que dejaron de comprar."

1.2 Modelo entidad-relación ​

txt
                         ┌─────────────┐
                         │ categorias  │
                         └──────┬──────┘
                                │ 1:N
┌──────────────┐   1:N   ┌──────▼──────┐   1:N   ┌───────────────────────┐
│ direcciones  │◄──┐     │  productos  │────────►│ movimientos_inventario│
└──────▲───────┘   │     └──┬───────┬──┘         └───────────────────────┘
       │ N:1       │        │ 1:N   │ 1:N
       │           │        │       │
┌──────┴───────┐   │  ┌─────▼────┐  │       ┌─────────┐
│   clientes   │───┼─►│ resenas  │  │       │  pagos  │
└──────┬───────┘   │  └──────────┘  │       └────▲────┘
       │ 1:N       │                │            │ N:1
┌──────▼───────┐   │         ┌──────▼─────────┐  │
│   pedidos    │───┘ (envío) │ detalle_pedido │  │
│              │◄────────────┤                │  │
│              │◄──────────────────────────────────┘
└──────────────┘

Decisiones de diseño:

  • detalle_pedido.precio_unitario guarda el precio histórico.
  • El total del pedido no se guarda: se calcula (3FN). Lo exponemos en una vista.
  • pagos es una tabla aparte: un pedido puede tener un intento rechazado y luego uno aprobado.
  • Una reseña por cliente y producto (UNIQUE compuesto).
  • Clientes y productos usan borrado lógico (activo), porque tienen historial.

1.3 Script del esquema ​

Guárdalo como tienda_v2_esquema.sql:

sql
-- ============================================================
--  TIENDA EN LÍNEA v2 — Esquema
--  Compatible con MySQL 8.0.16+ y PostgreSQL 13+
-- ============================================================

DROP TABLE IF EXISTS movimientos_inventario;
DROP TABLE IF EXISTS resenas;
DROP TABLE IF EXISTS pagos;
DROP TABLE IF EXISTS detalle_pedido;
DROP TABLE IF EXISTS pedidos;
DROP TABLE IF EXISTS direcciones;
DROP TABLE IF EXISTS productos;
DROP TABLE IF EXISTS categorias;
DROP TABLE IF EXISTS clientes;

CREATE TABLE clientes (
  id              INT PRIMARY KEY,
  nombre          VARCHAR(100) NOT NULL,
  email           VARCHAR(150) NOT NULL,
  telefono        VARCHAR(20),
  activo          BOOLEAN      NOT NULL DEFAULT TRUE,
  fecha_registro  DATE         NOT NULL,
  CONSTRAINT uq_clientes_email UNIQUE (email)
);

CREATE TABLE direcciones (
  id             INT PRIMARY KEY,
  cliente_id     INT          NOT NULL,
  calle          VARCHAR(150) NOT NULL,
  ciudad         VARCHAR(80)  NOT NULL,
  estado         VARCHAR(50)  NOT NULL,
  codigo_postal  CHAR(5)      NOT NULL,
  es_principal   BOOLEAN      NOT NULL DEFAULT FALSE,
  CONSTRAINT fk_direcciones_cliente FOREIGN KEY (cliente_id)
    REFERENCES clientes(id) ON DELETE CASCADE
);

CREATE TABLE categorias (
  id      INT PRIMARY KEY,
  nombre  VARCHAR(50) NOT NULL,
  CONSTRAINT uq_categorias_nombre UNIQUE (nombre)
);

CREATE TABLE productos (
  id            INT PRIMARY KEY,
  sku           VARCHAR(20)   NOT NULL,
  nombre        VARCHAR(100)  NOT NULL,
  descripcion   TEXT,
  categoria_id  INT,
  precio        DECIMAL(10,2) NOT NULL,
  stock         INT           NOT NULL DEFAULT 0,
  activo        BOOLEAN       NOT NULL DEFAULT TRUE,
  CONSTRAINT uq_productos_sku UNIQUE (sku),
  CONSTRAINT chk_productos_precio CHECK (precio >= 0),
  CONSTRAINT chk_productos_stock  CHECK (stock >= 0),
  CONSTRAINT fk_productos_categoria FOREIGN KEY (categoria_id)
    REFERENCES categorias(id) ON DELETE SET NULL
);

CREATE TABLE pedidos (
  id            INT PRIMARY KEY,
  cliente_id    INT         NOT NULL,
  direccion_id  INT,
  fecha         DATE        NOT NULL,
  estado        VARCHAR(20) NOT NULL DEFAULT 'pendiente',
  CONSTRAINT chk_pedidos_estado CHECK
    (estado IN ('pendiente', 'pagado', 'enviado', 'entregado', 'cancelado')),
  CONSTRAINT fk_pedidos_cliente FOREIGN KEY (cliente_id)
    REFERENCES clientes(id) ON DELETE RESTRICT,
  CONSTRAINT fk_pedidos_direccion FOREIGN KEY (direccion_id)
    REFERENCES direcciones(id) ON DELETE SET NULL
);

CREATE TABLE detalle_pedido (
  pedido_id        INT           NOT NULL,
  producto_id      INT           NOT NULL,
  cantidad         INT           NOT NULL,
  precio_unitario  DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (pedido_id, producto_id),
  CONSTRAINT chk_detalle_cantidad CHECK (cantidad > 0),
  CONSTRAINT fk_detalle_pedido FOREIGN KEY (pedido_id)
    REFERENCES pedidos(id) ON DELETE CASCADE,
  CONSTRAINT fk_detalle_producto FOREIGN KEY (producto_id)
    REFERENCES productos(id) ON DELETE RESTRICT
);

CREATE TABLE pagos (
  id         INT PRIMARY KEY,
  pedido_id  INT           NOT NULL,
  metodo     VARCHAR(20)   NOT NULL,
  monto      DECIMAL(10,2) NOT NULL,
  estado     VARCHAR(20)   NOT NULL,
  pagado_en  TIMESTAMP     NOT NULL,
  CONSTRAINT chk_pagos_metodo CHECK (metodo IN ('tarjeta', 'oxxo', 'transferencia')),
  CONSTRAINT chk_pagos_estado CHECK (estado IN ('aprobado', 'rechazado', 'reembolsado')),
  CONSTRAINT chk_pagos_monto  CHECK (monto > 0),
  CONSTRAINT fk_pagos_pedido FOREIGN KEY (pedido_id)
    REFERENCES pedidos(id) ON DELETE RESTRICT
);

CREATE TABLE resenas (
  id            INT PRIMARY KEY,
  producto_id   INT  NOT NULL,
  cliente_id    INT  NOT NULL,
  calificacion  INT  NOT NULL,
  comentario    TEXT,
  creada_en     DATE NOT NULL,
  CONSTRAINT uq_resenas_cliente_producto UNIQUE (cliente_id, producto_id),
  CONSTRAINT chk_resenas_calificacion CHECK (calificacion BETWEEN 1 AND 5),
  CONSTRAINT fk_resenas_producto FOREIGN KEY (producto_id)
    REFERENCES productos(id) ON DELETE CASCADE,
  CONSTRAINT fk_resenas_cliente FOREIGN KEY (cliente_id)
    REFERENCES clientes(id) ON DELETE CASCADE
);

CREATE TABLE movimientos_inventario (
  id           INT PRIMARY KEY,
  producto_id  INT          NOT NULL,
  tipo         VARCHAR(10)  NOT NULL,      -- entrada | salida | ajuste
  cantidad     INT          NOT NULL,      -- positiva o negativa
  motivo       VARCHAR(150),
  creado_en    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_mov_tipo CHECK (tipo IN ('entrada', 'salida', 'ajuste')),
  CONSTRAINT fk_mov_producto FOREIGN KEY (producto_id)
    REFERENCES productos(id) ON DELETE RESTRICT
);

-- Índices para las consultas frecuentes
-- (En MySQL las FK ya crean índice; estos son necesarios sobre todo en PostgreSQL)
CREATE INDEX idx_direcciones_cliente  ON direcciones (cliente_id);
CREATE INDEX idx_productos_categoria  ON productos (categoria_id);
CREATE INDEX idx_pedidos_cliente_fecha ON pedidos (cliente_id, fecha);
CREATE INDEX idx_pedidos_estado_fecha ON pedidos (estado, fecha);
CREATE INDEX idx_detalle_producto     ON detalle_pedido (producto_id);
CREATE INDEX idx_pagos_pedido         ON pagos (pedido_id);
CREATE INDEX idx_resenas_producto     ON resenas (producto_id);
CREATE INDEX idx_mov_producto_fecha   ON movimientos_inventario (producto_id, creado_en);

1.4 Datos de ejemplo ​

Guárdalo como tienda_v2_datos.sql:

sql
INSERT INTO clientes (id, nombre, email, telefono, fecha_registro) VALUES
  (1, 'Ana López',     'ana@correo.com',    '5511111111', '2024-01-15'),
  (2, 'Carlos Ruiz',   'carlos@correo.com', '3322222222', '2024-02-03'),
  (3, 'María Pérez',   'maria@correo.com',  '8133333333', '2024-02-20'),
  (4, 'Jorge Díaz',    'jorge@correo.com',  NULL,         '2024-03-11'),
  (5, 'Lucía Gómez',   'lucia@correo.com',  NULL,         '2024-04-02'),
  (6, 'Pedro Sánchez', 'pedro@correo.com',  '9996666666', '2024-05-20');

INSERT INTO direcciones (id, cliente_id, calle, ciudad, estado, codigo_postal, es_principal) VALUES
  (1, 1, 'Av. Reforma 100',     'Ciudad de México', 'CDMX',            '06600', TRUE),
  (2, 2, 'Calle Juárez 25',     'Guadalajara',      'Jalisco',         '44100', TRUE),
  (3, 3, 'Av. Constitución 8',  'Monterrey',        'Nuevo León',      '64000', TRUE),
  (4, 4, 'Insurgentes Sur 450', 'Ciudad de México', 'CDMX',            '03100', TRUE),
  (5, 6, 'Calle 60 #123',       'Mérida',           'Yucatán',         '97000', TRUE);

INSERT INTO categorias (id, nombre) VALUES
  (1, 'Electrónica'), (2, 'Libros'), (3, 'Hogar'), (4, 'Deportes');

INSERT INTO productos (id, sku, nombre, categoria_id, precio, stock) VALUES
  (1, 'ELE-001', 'Audífonos Bluetooth',   1,  899.00, 25),
  (2, 'ELE-002', 'Teclado mecánico',      1, 1250.00, 10),
  (3, 'ELE-003', 'Monitor 24 pulgadas',   1, 3499.00,  5),
  (4, 'LIB-001', 'Libro Aprende SQL',     2,  349.00, 40),
  (5, 'LIB-002', 'Cien años de soledad',  2,  289.00, 30),
  (6, 'HOG-001', 'Lámpara de escritorio', 3,  459.00, 15),
  (7, 'HOG-002', 'Taza térmica',          3,  199.00, 50),
  (8, 'DEP-001', 'Balón de fútbol',       4,  399.00, 20);

INSERT INTO pedidos (id, cliente_id, direccion_id, fecha, estado) VALUES
  (1, 1, 1, '2024-05-02', 'entregado'),
  (2, 2, 2, '2024-05-10', 'entregado'),
  (3, 1, 1, '2024-06-01', 'entregado'),
  (4, 3, 3, '2024-06-15', 'enviado'),
  (5, 4, 4, '2024-06-20', 'cancelado'),
  (6, 6, 5, '2024-07-03', 'pagado'),
  (7, 2, 2, '2024-07-18', 'entregado'),
  (8, 1, 1, '2024-07-25', 'pendiente');

INSERT INTO detalle_pedido (pedido_id, producto_id, cantidad, precio_unitario) VALUES
  (1, 1, 1,  899.00), (1, 4, 2,  349.00),
  (2, 2, 1, 1250.00), (2, 7, 3,  199.00),
  (3, 3, 1, 3499.00),
  (4, 5, 1,  289.00), (4, 6, 1,  459.00), (4, 7, 2, 199.00),
  (5, 1, 1,  899.00),
  (6, 8, 2,  399.00), (6, 4, 1,  349.00),
  (7, 1, 1,  899.00), (7, 6, 2,  459.00),
  (8, 7, 4,  199.00);

INSERT INTO pagos (id, pedido_id, metodo, monto, estado, pagado_en) VALUES
  (1, 1, 'tarjeta',       1597.00, 'aprobado',    '2024-05-02 10:15:00'),
  (2, 2, 'oxxo',          1847.00, 'aprobado',    '2024-05-11 09:00:00'),
  (3, 3, 'tarjeta',       3499.00, 'aprobado',    '2024-06-01 18:30:00'),
  (4, 4, 'transferencia', 1146.00, 'aprobado',    '2024-06-15 12:00:00'),
  (5, 5, 'tarjeta',        899.00, 'reembolsado', '2024-06-20 08:00:00'),
  (6, 6, 'tarjeta',       1147.00, 'aprobado',    '2024-07-03 20:45:00'),
  (7, 7, 'oxxo',          1817.00, 'aprobado',    '2024-07-19 11:10:00');

INSERT INTO resenas (id, producto_id, cliente_id, calificacion, comentario, creada_en) VALUES
  (1, 1, 1, 5, 'Excelente sonido',                 '2024-05-10'),
  (2, 4, 1, 4, 'Muy claro para empezar',           '2024-05-12'),
  (3, 2, 2, 5, 'Las teclas se sienten increíbles', '2024-05-20'),
  (4, 7, 2, 3, 'Se enfría algo rápido',            '2024-05-21'),
  (5, 1, 2, 4, 'Buena batería',                    '2024-07-25'),
  (6, 6, 3, 5, 'Perfecta para leer de noche',      '2024-06-25');

INSERT INTO movimientos_inventario (id, producto_id, tipo, cantidad, motivo, creado_en) VALUES
  (1, 3, 'entrada', 10, 'Compra a proveedor',   '2024-04-01 09:00:00'),
  (2, 3, 'salida',  -1, 'Pedido 3',             '2024-06-01 18:31:00'),
  (3, 3, 'ajuste',  -4, 'Inventario físico',    '2024-06-30 17:00:00'),
  (4, 8, 'entrada', 22, 'Compra a proveedor',   '2024-06-01 10:00:00'),
  (5, 8, 'salida',  -2, 'Pedido 6',             '2024-07-03 20:46:00');

Definición de negocio: una venta es un pedido con estado pagado, enviado o entregado. Los pendiente aún no se cobran y los cancelado no cuentan.

1.5 Vistas de apoyo ​

sql
-- Totales por pedido (base de casi todos los reportes)
CREATE VIEW v_pedidos_totales AS
SELECT
  pe.id          AS pedido_id,
  pe.cliente_id,
  pe.fecha,
  pe.estado,
  SUM(d.cantidad)                     AS piezas,
  SUM(d.cantidad * d.precio_unitario) AS total
FROM pedidos pe
JOIN detalle_pedido d ON d.pedido_id = pe.id
GROUP BY pe.id, pe.cliente_id, pe.fecha, pe.estado;

-- Solo ventas válidas
CREATE VIEW v_ventas AS
SELECT * FROM v_pedidos_totales
WHERE estado IN ('pagado', 'enviado', 'entregado');
sql
SELECT * FROM v_pedidos_totales ORDER BY pedido_id;
pedido_idcliente_idfechaestadopiezastotal
112024-05-02entregado31597.00
222024-05-10entregado41847.00
312024-06-01entregado13499.00
432024-06-15enviado41146.00
542024-06-20cancelado1899.00
662024-07-03pagado31147.00
722024-07-18entregado31817.00
812024-07-25pendiente4796.00

1.6 Consultas de negocio ​

Q1. Resumen general de ventas

sql
SELECT
  COUNT(*)              AS pedidos,
  SUM(total)            AS ventas,
  ROUND(AVG(total), 2)  AS ticket_promedio,
  SUM(piezas)           AS piezas
FROM v_ventas;
pedidosventasticket_promediopiezas
611053.001842.1718

Q2. Ventas por mes con crecimiento (CTE + LAG)

sql
WITH mensual AS (
  SELECT
    EXTRACT(YEAR FROM fecha)  AS anio,
    EXTRACT(MONTH FROM fecha) AS mes,
    COUNT(*)   AS pedidos,
    SUM(total) AS ventas
  FROM v_ventas
  GROUP BY EXTRACT(YEAR FROM fecha), EXTRACT(MONTH FROM fecha)
)
SELECT
  anio, mes, pedidos, ventas,
  ventas - LAG(ventas) OVER (ORDER BY anio, mes) AS cambio_vs_mes_anterior
FROM mensual
ORDER BY anio, mes;
aniomespedidosventascambio_vs_mes_anterior
2024523444.00NULL
2024624645.001201.00
2024722964.00-1681.00

Q3. Productos estrella por ingresos (con ranking)

sql
SELECT
  pr.sku,
  pr.nombre,
  SUM(d.cantidad)                     AS piezas,
  SUM(d.cantidad * d.precio_unitario) AS ingresos,
  RANK() OVER (ORDER BY SUM(d.cantidad * d.precio_unitario) DESC) AS lugar
FROM detalle_pedido d
JOIN v_ventas v   ON v.pedido_id = d.pedido_id
JOIN productos pr ON pr.id = d.producto_id
GROUP BY pr.id, pr.sku, pr.nombre
ORDER BY ingresos DESC;
skunombrepiezasingresoslugar
ELE-003Monitor 24 pulgadas13499.001
ELE-001Audífonos Bluetooth21798.002
HOG-001Lámpara de escritorio31377.003
ELE-002Teclado mecánico11250.004
LIB-001Libro Aprende SQL31047.005
HOG-002Taza térmica5995.006
DEP-001Balón de fútbol2798.007
LIB-002Cien años de soledad1289.008

Q4. Ventas por categoría con porcentaje del total

sql
SELECT
  c.nombre AS categoria,
  SUM(d.cantidad * d.precio_unitario) AS ventas,
  ROUND(100.0 * SUM(d.cantidad * d.precio_unitario)
        / SUM(SUM(d.cantidad * d.precio_unitario)) OVER (), 1) AS porcentaje
FROM detalle_pedido d
JOIN v_ventas v    ON v.pedido_id = d.pedido_id
JOIN productos pr  ON pr.id = d.producto_id
JOIN categorias c  ON c.id = pr.categoria_id
GROUP BY c.nombre
ORDER BY ventas DESC;
categoriaventasporcentaje
Electrónica6547.0059.2
Hogar2372.0021.5
Libros1336.0012.1
Deportes798.007.2

Q5. Mejores clientes (incluye a quien no ha comprado)

sql
SELECT
  cl.nombre,
  COUNT(v.pedido_id)          AS compras,
  COALESCE(SUM(v.total), 0)   AS total_gastado,
  MAX(v.fecha)                AS ultima_compra
FROM clientes cl
LEFT JOIN v_ventas v ON v.cliente_id = cl.id
GROUP BY cl.id, cl.nombre
ORDER BY total_gastado DESC;
nombrecomprastotal_gastadoultima_compra
Ana López25096.002024-06-01
Carlos Ruiz23664.002024-07-18
Pedro Sánchez11147.002024-07-03
María Pérez11146.002024-06-15
Jorge Díaz00NULL
Lucía Gómez00NULL

Q6. Clientes sin compras válidas (para una campaña de reactivación)

sql
SELECT cl.nombre, cl.email, cl.fecha_registro
FROM clientes cl
WHERE cl.activo = TRUE
  AND NOT EXISTS (SELECT 1 FROM v_ventas v WHERE v.cliente_id = cl.id)
ORDER BY cl.fecha_registro;
nombreemailfecha_registro
Jorge Díazjorge@correo.com2024-03-11
Lucía Gómezlucia@correo.com2024-04-02

Q7. Clientes recurrentes: días entre compras

sql
-- PostgreSQL: resta de fechas = días. MySQL: DATEDIFF(fecha, LAG(fecha) OVER (...))
SELECT
  cl.nombre,
  v.fecha,
  v.fecha - LAG(v.fecha) OVER (PARTITION BY v.cliente_id ORDER BY v.fecha) AS dias_desde_anterior
FROM v_ventas v
JOIN clientes cl ON cl.id = v.cliente_id
WHERE v.cliente_id IN (
  SELECT cliente_id FROM v_ventas GROUP BY cliente_id HAVING COUNT(*) > 1
)
ORDER BY cl.nombre, v.fecha;
nombrefechadias_desde_anterior
Ana López2024-05-02NULL
Ana López2024-06-0130
Carlos Ruiz2024-05-10NULL
Carlos Ruiz2024-07-1869

Q8. Pagos aprobados por método

sql
SELECT metodo, COUNT(*) AS pagos, SUM(monto) AS monto_total
FROM pagos
WHERE estado = 'aprobado'
GROUP BY metodo
ORDER BY monto_total DESC;
metodopagosmonto_total
tarjeta36243.00
oxxo23664.00
transferencia11146.00

Q9. Conciliación: pedidos cuyo pago no cuadra con el total

sql
SELECT t.pedido_id, t.estado, t.total,
       COALESCE(SUM(pa.monto), 0) AS pagado
FROM v_pedidos_totales t
LEFT JOIN pagos pa ON pa.pedido_id = t.pedido_id AND pa.estado = 'aprobado'
WHERE t.estado <> 'cancelado'
GROUP BY t.pedido_id, t.estado, t.total
HAVING COALESCE(SUM(pa.monto), 0) <> t.total;
pedido_idestadototalpagado
8pendiente796.000

Q10. Calificación promedio y productos sin reseñas

sql
SELECT
  pr.nombre,
  COUNT(r.id)                      AS resenas,
  ROUND(AVG(r.calificacion), 2)    AS promedio
FROM productos pr
LEFT JOIN resenas r ON r.producto_id = pr.id
GROUP BY pr.id, pr.nombre
ORDER BY promedio DESC, resenas DESC;
nombreresenaspromedio
Teclado mecánico15.00
Lámpara de escritorio15.00
Audífonos Bluetooth24.50
Libro Aprende SQL14.00
Taza térmica13.00
Monitor 24 pulgadas0NULL
Cien años de soledad0NULL
Balón de fútbol0NULL

(PostgreSQL ordena los NULL al principio en DESC; agrega NULLS LAST para dejarlos al final como en la tabla. En MySQL salen al final por defecto en DESC.)

Q11. Alerta de stock bajo (stock menor o igual a 10)

sql
SELECT sku, nombre, stock,
       CASE WHEN stock <= 5 THEN 'URGENTE' ELSE 'Reabastecer pronto' END AS prioridad
FROM productos
WHERE activo = TRUE AND stock <= 10
ORDER BY stock;
skunombrestockprioridad
ELE-003Monitor 24 pulgadas5URGENTE
ELE-002Teclado mecánico10Reabastecer pronto

Q12. Kardex: saldo acumulado de movimientos de inventario

sql
SELECT
  pr.nombre,
  m.creado_en,
  m.tipo,
  m.cantidad,
  SUM(m.cantidad) OVER (PARTITION BY m.producto_id ORDER BY m.creado_en) AS saldo
FROM movimientos_inventario m
JOIN productos pr ON pr.id = m.producto_id
ORDER BY pr.nombre, m.creado_en;
nombrecreado_entipocantidadsaldo
Balón de fútbol2024-06-01 10:00:00entrada2222
Balón de fútbol2024-07-03 20:46:00salida-220
Monitor 24 pulgadas2024-04-01 09:00:00entrada1010
Monitor 24 pulgadas2024-06-01 18:31:00salida-19
Monitor 24 pulgadas2024-06-30 17:00:00ajuste-45

El saldo final coincide con el stock de cada producto (20 y 5): así se audita el inventario.

Q13. Top 1 producto por categoría (patrón top-N)

sql
WITH ventas_producto AS (
  SELECT pr.categoria_id, pr.nombre,
         SUM(d.cantidad * d.precio_unitario) AS ingresos,
         ROW_NUMBER() OVER (PARTITION BY pr.categoria_id
                            ORDER BY SUM(d.cantidad * d.precio_unitario) DESC) AS rn
  FROM detalle_pedido d
  JOIN v_ventas v   ON v.pedido_id = d.pedido_id
  JOIN productos pr ON pr.id = d.producto_id
  GROUP BY pr.categoria_id, pr.id, pr.nombre
)
SELECT c.nombre AS categoria, vp.nombre AS producto_estrella, vp.ingresos
FROM ventas_producto vp
JOIN categorias c ON c.id = vp.categoria_id
WHERE vp.rn = 1
ORDER BY vp.ingresos DESC;
categoriaproducto_estrellaingresos
ElectrónicaMonitor 24 pulgadas3499.00
HogarLámpara de escritorio1377.00
LibrosLibro Aprende SQL1047.00
DeportesBalón de fútbol798.00

1.7 Transacción de compra (checkout) ​

Lo que ejecuta tu backend cuando Lucía compra 1 Teclado y 2 Tazas pagando con tarjeta:

sql
BEGIN;  -- MySQL: START TRANSACTION;

INSERT INTO direcciones (id, cliente_id, calle, ciudad, estado, codigo_postal, es_principal)
VALUES (6, 5, 'Av. Juárez 300', 'Puebla', 'Puebla', '72000', TRUE);

INSERT INTO pedidos (id, cliente_id, direccion_id, fecha, estado)
VALUES (9, 5, 6, '2024-08-01', 'pendiente');

-- Descontar stock de forma atómica (la app verifica 1 fila afectada en cada uno)
UPDATE productos SET stock = stock - 1 WHERE id = 2 AND stock >= 1;
UPDATE productos SET stock = stock - 2 WHERE id = 7 AND stock >= 2;

INSERT INTO detalle_pedido (pedido_id, producto_id, cantidad, precio_unitario)
SELECT 9, id, CASE id WHEN 2 THEN 1 WHEN 7 THEN 2 END, precio
FROM productos WHERE id IN (2, 7);

INSERT INTO movimientos_inventario (id, producto_id, tipo, cantidad, motivo) VALUES
  (6, 2, 'salida', -1, 'Pedido 9'),
  (7, 7, 'salida', -2, 'Pedido 9');

-- La pasarela respondió "aprobado" ANTES de abrir esta transacción
INSERT INTO pagos (id, pedido_id, metodo, monto, estado, pagado_en)
VALUES (8, 9, 'tarjeta', 1648.00, 'aprobado', CURRENT_TIMESTAMP);

UPDATE pedidos SET estado = 'pagado' WHERE id = 9;

COMMIT;

(1 × 1,250 + 2 × 199 = 1,648.) Si cualquier paso falla, ROLLBACK y nada queda a medias. En producción los id los genera la BD (AUTO_INCREMENT / IDENTITY) y los obtienes con LAST_INSERT_ID() o RETURNING id.

Parte 2: Sistema escolar ​

2.1 Requerimientos ​

"Somos una escuela. Cada periodo abrimos grupos de las materias, cada uno con un profesor y un cupo máximo. Los alumnos se inscriben en grupos y al final reciben una calificación (de 0 a 10; aprueban con 6). Necesito promedios, reprobados, ranking de alumnos y saber qué grupos se llenaron."

2.2 Modelo E-R ​

txt
┌────────────┐ 1:N ┌──────────┐ N:1 ┌────────────┐
│ profesores │────►│  grupos  │◄────│  materias  │
└────────────┘     └────┬─────┘     └────────────┘
                        │ 1:N
                 ┌──────▼────────┐ N:1 ┌──────────┐
                 │ inscripciones │────►│ alumnos  │
                 │ (calificación)│     └──────────┘
                 └───────────────┘

Un alumno se relaciona con muchos grupos y un grupo con muchos alumnos (N:M) a través de inscripciones, que además guarda la calificación.

2.3 Script completo ​

sql
-- ============================================================
--  SISTEMA ESCOLAR — Esquema y datos
-- ============================================================
DROP TABLE IF EXISTS inscripciones;
DROP TABLE IF EXISTS grupos;
DROP TABLE IF EXISTS materias;
DROP TABLE IF EXISTS profesores;
DROP TABLE IF EXISTS alumnos;

CREATE TABLE alumnos (
  id         INT PRIMARY KEY,
  matricula  VARCHAR(10)  NOT NULL UNIQUE,
  nombre     VARCHAR(100) NOT NULL,
  email      VARCHAR(150) NOT NULL UNIQUE,
  ingreso    DATE         NOT NULL
);

CREATE TABLE profesores (
  id      INT PRIMARY KEY,
  nombre  VARCHAR(100) NOT NULL,
  email   VARCHAR(150) NOT NULL UNIQUE
);

CREATE TABLE materias (
  id        INT PRIMARY KEY,
  clave     VARCHAR(10)  NOT NULL UNIQUE,
  nombre    VARCHAR(100) NOT NULL,
  creditos  INT          NOT NULL CHECK (creditos > 0)
);

CREATE TABLE grupos (
  id           INT PRIMARY KEY,
  materia_id   INT         NOT NULL,
  profesor_id  INT         NOT NULL,
  periodo      VARCHAR(10) NOT NULL,          -- ej. '2024-2'
  cupo         INT         NOT NULL CHECK (cupo > 0),
  CONSTRAINT uq_grupo UNIQUE (materia_id, profesor_id, periodo),
  FOREIGN KEY (materia_id)  REFERENCES materias(id),
  FOREIGN KEY (profesor_id) REFERENCES profesores(id)
);

CREATE TABLE inscripciones (
  alumno_id     INT NOT NULL,
  grupo_id      INT NOT NULL,
  calificacion  DECIMAL(4,2),                 -- NULL = curso en progreso
  PRIMARY KEY (alumno_id, grupo_id),
  CONSTRAINT chk_calificacion CHECK (calificacion BETWEEN 0 AND 10),
  FOREIGN KEY (alumno_id) REFERENCES alumnos(id),
  FOREIGN KEY (grupo_id)  REFERENCES grupos(id)
);

CREATE INDEX idx_inscripciones_grupo ON inscripciones (grupo_id);

INSERT INTO alumnos (id, matricula, nombre, email, ingreso) VALUES
  (1, 'A001', 'Luis Torres',   'luis@escuela.mx',  '2023-08-14'),
  (2, 'A002', 'Eva Morales',   'eva@escuela.mx',   '2023-08-14'),
  (3, 'A003', 'Diego Herrera', 'diego@escuela.mx', '2024-01-08'),
  (4, 'A004', 'Sofía Navarro', 'sofia@escuela.mx', '2024-01-08'),
  (5, 'A005', 'Raúl Ortiz',    'raul@escuela.mx',  '2024-08-12');

INSERT INTO profesores (id, nombre, email) VALUES
  (1, 'Dra. Rojas',     'rojas@escuela.mx'),
  (2, 'Mtro. Castillo', 'castillo@escuela.mx'),
  (3, 'Mtra. Vega',     'vega@escuela.mx');

INSERT INTO materias (id, clave, nombre, creditos) VALUES
  (1, 'BD101',  'Bases de Datos', 8),
  (2, 'PRG101', 'Programación',   8),
  (3, 'RED101', 'Redes',          6);

INSERT INTO grupos (id, materia_id, profesor_id, periodo, cupo) VALUES
  (1, 1, 1, '2024-2', 30),
  (2, 2, 2, '2024-2', 25),
  (3, 3, 3, '2024-2',  3);

INSERT INTO inscripciones (alumno_id, grupo_id, calificacion) VALUES
  (1, 1,  9.0), (1, 2,  8.5), (1, 3, 7.0),
  (2, 1, 10.0), (2, 2,  9.5),
  (3, 1,  5.5), (3, 2,  6.0), (3, 3, 8.0),
  (4, 1,  8.0), (4, 3,  4.0),
  (5, 1,  7.5), (5, 2, NULL);

2.4 Consultas ​

E1. Boleta de un alumno

sql
SELECT m.clave, m.nombre AS materia, p.nombre AS profesor, i.calificacion,
       CASE
         WHEN i.calificacion IS NULL THEN 'En curso'
         WHEN i.calificacion >= 6 THEN 'Aprobado'
         ELSE 'Reprobado'
       END AS situacion
FROM inscripciones i
JOIN grupos g     ON g.id = i.grupo_id
JOIN materias m   ON m.id = g.materia_id
JOIN profesores p ON p.id = g.profesor_id
WHERE i.alumno_id = 3
ORDER BY m.clave;
clavemateriaprofesorcalificacionsituacion
BD101Bases de DatosDra. Rojas5.50Reprobado
PRG101ProgramaciónMtro. Castillo6.00Aprobado
RED101RedesMtra. Vega8.00Aprobado

E2. Ranking de alumnos por promedio

sql
SELECT
  a.matricula,
  a.nombre,
  ROUND(AVG(i.calificacion), 2) AS promedio,
  DENSE_RANK() OVER (ORDER BY AVG(i.calificacion) DESC) AS lugar
FROM alumnos a
JOIN inscripciones i ON i.alumno_id = a.id
WHERE i.calificacion IS NOT NULL
GROUP BY a.id, a.matricula, a.nombre
ORDER BY lugar;
matriculanombrepromediolugar
A002Eva Morales9.751
A001Luis Torres8.172
A005Raúl Ortiz7.503
A003Diego Herrera6.504
A004Sofía Navarro6.005

E3. Reprobados

sql
SELECT a.nombre, m.nombre AS materia, i.calificacion
FROM inscripciones i
JOIN alumnos a  ON a.id = i.alumno_id
JOIN grupos g   ON g.id = i.grupo_id
JOIN materias m ON m.id = g.materia_id
WHERE i.calificacion < 6
ORDER BY i.calificacion;
nombremateriacalificacion
Sofía NavarroRedes4.00
Diego HerreraBases de Datos5.50

E4. Estadísticas por materia

sql
SELECT
  m.nombre AS materia,
  COUNT(*)                        AS inscritos,
  COUNT(i.calificacion)           AS calificados,
  ROUND(AVG(i.calificacion), 2)   AS promedio,
  ROUND(100.0 * SUM(CASE WHEN i.calificacion >= 6 THEN 1 ELSE 0 END)
        / COUNT(i.calificacion), 1) AS pct_aprobacion
FROM inscripciones i
JOIN grupos g   ON g.id = i.grupo_id
JOIN materias m ON m.id = g.materia_id
GROUP BY m.id, m.nombre
ORDER BY m.id;
materiainscritoscalificadospromediopct_aprobacion
Bases de Datos558.0080.0
Programación438.00100.0
Redes336.3366.7

E5. Cupo disponible por grupo

sql
SELECT g.id AS grupo, m.nombre AS materia, g.cupo,
       COUNT(i.alumno_id)            AS inscritos,
       g.cupo - COUNT(i.alumno_id)   AS lugares_libres
FROM grupos g
JOIN materias m ON m.id = g.materia_id
LEFT JOIN inscripciones i ON i.grupo_id = g.id
GROUP BY g.id, m.nombre, g.cupo
ORDER BY lugares_libres;
grupomateriacupoinscritoslugares_libres
3Redes330
2Programación25421
1Bases de Datos30525

E6. Créditos aprobados por alumno

sql
SELECT a.nombre,
       COALESCE(SUM(CASE WHEN i.calificacion >= 6 THEN m.creditos END), 0) AS creditos_aprobados
FROM alumnos a
LEFT JOIN inscripciones i ON i.alumno_id = a.id
LEFT JOIN grupos g        ON g.id = i.grupo_id
LEFT JOIN materias m      ON m.id = g.materia_id
GROUP BY a.id, a.nombre
ORDER BY creditos_aprobados DESC, a.nombre;
nombrecreditos_aprobados
Luis Torres22
Eva Morales16
Diego Herrera14
Raúl Ortiz8
Sofía Navarro8

E7. Mejor calificación de cada materia

sql
WITH r AS (
  SELECT m.nombre AS materia, a.nombre AS alumno, i.calificacion,
         RANK() OVER (PARTITION BY m.id ORDER BY i.calificacion DESC) AS lugar
  FROM inscripciones i
  JOIN alumnos a  ON a.id = i.alumno_id
  JOIN grupos g   ON g.id = i.grupo_id
  JOIN materias m ON m.id = g.materia_id
  WHERE i.calificacion IS NOT NULL
)
SELECT materia, alumno, calificacion FROM r WHERE lugar = 1 ORDER BY materia;
materiaalumnocalificacion
Bases de DatosEva Morales10.00
ProgramaciónEva Morales9.50
RedesDiego Herrera8.00

2.5 Inscripción segura respetando el cupo ​

Un CHECK no puede contar filas de otra tabla, así que el cupo se protege con una transacción y un bloqueo del grupo:

sql
BEGIN;

-- Bloquea el grupo para que dos inscripciones simultáneas no rebasen el cupo
SELECT cupo FROM grupos WHERE id = 3 FOR UPDATE;

SELECT COUNT(*) FROM inscripciones WHERE grupo_id = 3;
-- La app compara: si inscritos >= cupo → ROLLBACK y "grupo lleno"

-- Solo si hay lugar:
-- INSERT INTO inscripciones (alumno_id, grupo_id) VALUES (2, 3);

COMMIT;

Para el grupo 3 (Redes), los 3 lugares están ocupados: la app rechaza la inscripción de Eva.

Checklist profesional ​

Antes de entregar una base de datos a un cliente, revisa:

  • [ ] Diseño en 3FN; cualquier desnormalización está justificada y documentada.
  • [ ] Toda tabla tiene PK; los datos naturales únicos tienen UNIQUE (email, SKU, matrícula).
  • [ ] Columnas obligatorias con NOT NULL y valores por defecto sensatos.
  • [ ] FK con la acción ON DELETE correcta (RESTRICT para negocio, CASCADE para "partes de").
  • [ ] CHECK para rangos y estados válidos (y MySQL 8.0.16+ si dependes de ellos).
  • [ ] Dinero en DECIMAL, fechas en tipos de fecha, charset utf8mb4 en MySQL.
  • [ ] Índices en FK y en las columnas de los filtros y ordenamientos frecuentes, verificados con EXPLAIN.
  • [ ] Operaciones de varios pasos dentro de transacciones; sobreventa evitada con UPDATE ... WHERE stock >= n o FOR UPDATE.
  • [ ] La app usa consultas preparadas (nunca concatena entradas del usuario).
  • [ ] Un usuario de BD por aplicación con privilegios mínimos, nunca root.
  • [ ] Esquema versionado (scripts o migraciones en Git) y datos de prueba reproducibles.
  • [ ] Respaldos automáticos y una restauración probada (MySQL, PostgreSQL).

Retos para seguir practicando ​

  1. Agrega cupones de descuento (código único, porcentaje, vigencia) y relaciónalos con los pedidos. Ajusta la vista de totales.
  2. Crea un trigger que inserte automáticamente un movimiento de inventario salida cada vez que se inserte un detalle_pedido.
  3. En la escuela, agrega periodos como tabla y calcula el promedio de cada alumno por periodo con su variación respecto al anterior (LAG).
  4. Genera 100,000 pedidos de prueba y optimiza Q2 y Q5 con índices, comparando EXPLAIN ANALYZE antes y después.
Ver solución del reto 1 (esquema)
sql
CREATE TABLE cupones (
  id INT PRIMARY KEY,
  codigo VARCHAR(30) NOT NULL UNIQUE,
  porcentaje DECIMAL(5,2) NOT NULL CHECK (porcentaje > 0 AND porcentaje <= 100),
  vigente_desde DATE NOT NULL,
  vigente_hasta DATE NOT NULL,
  CHECK (vigente_hasta >= vigente_desde)
);

ALTER TABLE pedidos ADD COLUMN cupon_id INT NULL;
ALTER TABLE pedidos ADD CONSTRAINT fk_pedidos_cupon
  FOREIGN KEY (cupon_id) REFERENCES cupones(id);

-- Total con descuento
SELECT t.pedido_id, t.total,
       ROUND(t.total * (1 - COALESCE(c.porcentaje, 0) / 100), 2) AS total_con_descuento
FROM v_pedidos_totales t
JOIN pedidos pe ON pe.id = t.pedido_id
LEFT JOIN cupones c ON c.id = pe.cupon_id;

Resumen ​

  • Un proyecto real empieza con requerimientos, sigue con el modelo E-R y termina en scripts de esquema y datos versionados.
  • Las vistas (v_pedidos_totales, v_ventas) centralizan definiciones de negocio y simplifican los reportes.
  • Combinaste todo el curso: JOINs, agregaciones, CTEs, funciones de ventana, transacciones, restricciones e índices.
  • Usa el checklist profesional en cada base de datos que entregues.
  • Siguiente paso: especialízate en MySQL o PostgreSQL y conéctalo a tu backend.

Academia Web — aprende, copia y construye.

© 2026