Proyecto: base de datos de una tienda

1.El encargo

Una pequeña tienda de tecnología de tu ciudad lleva sus ventas en un cuaderno y quiere pasarlas a una base de datos. Te pide poder:

• Guardar el catálogo de productos con su categoría, precio y stock.
• Registrar a sus clientes, sin repetir sus datos.
• Anotar cada pedido con los productos y cantidades que incluye, y descontar el stock.
• Obtener informes: cuánto vende, quién compra más, qué se vende más y qué no se mueve.
• Estar protegida: datos coherentes, permisos limitados y copias de seguridad.

Es justo lo que has aprendido en las ocho lecciones. Aquí lo reúnes en un único proyecto, que funciona en SQLite (y con pocos cambios en PostgreSQL).

2.Paso 1: diseñar el modelo

Antes de escribir SQL, decide qué entidades hay y cómo se relacionan. Del encargo salen cuatro:

Las relaciones son: un cliente tiene muchos pedidos (uno a muchos) y un pedido contiene muchos productos y un producto está en muchos pedidos (muchos a muchos, resuelto con lineas_pedido).

3.Paso 2: crear la base de datos

Guarda todo en un único archivo tienda.sql. El script se puede ejecutar las veces que quieras: las líneas DROP TABLE IF EXISTS (en orden inverso a las dependencias) empiezan siempre desde cero. Este código se ha ejecutado de verdad en SQLite: crea 4 tablas con 8 productos, 4 clientes, 4 pedidos y 7 líneas.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS lineas_pedido;
DROP TABLE IF EXISTS pedidos;
DROP TABLE IF EXISTS clientes;
DROP TABLE IF EXISTS productos;

CREATE TABLE productos (
  id        INTEGER PRIMARY KEY,
  nombre    TEXT    NOT NULL,
  categoria TEXT    NOT NULL,
  precio    NUMERIC NOT NULL,
  stock     INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE clientes (
  id     INTEGER PRIMARY KEY,
  nombre TEXT NOT NULL,
  email  TEXT NOT NULL UNIQUE
);

CREATE TABLE pedidos (
  id         INTEGER PRIMARY KEY,
  cliente_id INTEGER NOT NULL REFERENCES clientes(id),
  fecha      TEXT    NOT NULL
);

CREATE TABLE lineas_pedido (
  pedido_id   INTEGER NOT NULL REFERENCES pedidos(id),
  producto_id INTEGER NOT NULL REFERENCES productos(id),
  cantidad    INTEGER NOT NULL CHECK (cantidad > 0),
  PRIMARY KEY (pedido_id, producto_id)
);

INSERT INTO productos (id, nombre, categoria, precio, stock) VALUES
  (1, 'Teclado',     'Periféricos', 120.00,  15),
  (2, 'Ratón',       'Periféricos',  45.50,  40),
  (3, 'Monitor FHD', 'Pantallas',   650.00,   8),
  (4, 'Auriculares', 'Audio',        89.90,   0),
  (5, 'Webcam',      'Periféricos', 150.00,  12),
  (6, 'Altavoz',     'Audio',        99.00,  25),
  (7, 'Monitor 4K',  'Pantallas',   899.00,   3),
  (8, 'Cable HDMI',  'Accesorios',   15.00, 100);

INSERT INTO clientes (id, nombre, email) VALUES
  (1, 'Ana Torres',  'ana@correo.com'),
  (2, 'Luis Pérez',  'luis@correo.com'),
  (3, 'Marta Ruiz',  'marta@correo.com'),
  (4, 'Diego Soto',  'diego@correo.com');

INSERT INTO pedidos (id, cliente_id, fecha) VALUES
  (1, 1, '2026-09-01'),
  (2, 1, '2026-09-15'),
  (3, 2, '2026-09-20'),
  (4, 3, '2026-10-02');

INSERT INTO lineas_pedido (pedido_id, producto_id, cantidad) VALUES
  (1, 1, 1), (1, 2, 1),
  (2, 5, 1),
  (3, 7, 1), (3, 8, 2),
  (4, 6, 2), (4, 2, 3);

sqlite3 tienda.db ".read tienda.sql"

Comprueba que todo entró bien:

Resultado esperado: conteo de filas

4.Paso 3: registrar un pedido con una transacción

Registrar un pedido toca varias tablas: crear el pedido, añadir sus líneas y descontar el stock. Si algo falla a medias, la tienda quedaría con datos incoherentes. Por eso se hace dentro de una transacción (lección 3): todo se guarda, o no se guarda nada.

BEGIN;

INSERT INTO pedidos (id, cliente_id, fecha)
VALUES (5, 4, '2026-10-06');

INSERT INTO lineas_pedido (pedido_id, producto_id, cantidad)
VALUES (5, 3, 1), (5, 2, 2);

UPDATE productos SET stock = stock - 1 WHERE id = 3;
UPDATE productos SET stock = stock - 2 WHERE id = 2;

COMMIT;

Si cualquiera de las instrucciones falla, ejecutas ROLLBACK; en lugar de COMMIT; y la base queda como estaba. Después de la transacción:

Stock actualizado (antes: Ratón 40, Monitor FHD 8)

El pedido nuevo

5.Paso 4: los informes

Los informes son consultas que combinan todo lo aprendido: JOIN, GROUP BY, HAVING y LEFT JOIN … IS NULL. Los resultados que ves son los de la base original (sin el pedido nuevo del paso anterior).

Pedidos con cliente e importe

SELECT p.id AS pedido, c.nombre, p.fecha,
       SUM(l.cantidad * pr.precio) AS total
FROM pedidos p
JOIN clientes c       ON c.id        = p.cliente_id
JOIN lineas_pedido l  ON l.pedido_id = p.id
JOIN productos pr     ON pr.id       = l.producto_id
GROUP BY p.id
ORDER BY p.id;

Resultado esperado

Gasto por cliente (incluye a quien no compró)

SELECT c.nombre,
       COUNT(DISTINCT p.id) AS pedidos,
       COALESCE(SUM(l.cantidad * pr.precio), 0) AS gasto
FROM clientes c
LEFT JOIN pedidos p       ON p.cliente_id = c.id
LEFT JOIN lineas_pedido l ON l.pedido_id  = p.id
LEFT JOIN productos pr    ON pr.id        = l.producto_id
GROUP BY c.id
ORDER BY gasto DESC;

Resultado esperado

Productos más vendidos

SELECT pr.nombre AS producto,
       SUM(l.cantidad) AS unidades,
       SUM(l.cantidad * pr.precio) AS ingresos
FROM lineas_pedido l
JOIN productos pr ON pr.id = l.producto_id
GROUP BY pr.id
ORDER BY unidades DESC, ingresos DESC;

Resultado esperado

Ingresos por categoría

SELECT pr.categoria,
       SUM(l.cantidad * pr.precio) AS ingresos
FROM lineas_pedido l
JOIN productos pr ON pr.id = l.producto_id
GROUP BY pr.categoria
ORDER BY ingresos DESC;

Resultado esperado

Productos que nunca se han vendido

SELECT pr.nombre, pr.stock
FROM productos pr
LEFT JOIN lineas_pedido l ON l.producto_id = pr.id
WHERE l.pedido_id IS NULL;

Resultado esperado

Ingresos por mes

SELECT substr(p.fecha, 1, 7) AS mes,
       COUNT(DISTINCT p.id) AS pedidos,
       SUM(l.cantidad * pr.precio) AS ingresos
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.id
JOIN productos pr    ON pr.id       = l.producto_id
GROUP BY mes
ORDER BY mes;

Resultado esperado

6.Paso 5: proteger la base

Un proyecto terminado no es solo el que consulta bien. Antes de darlo por cerrado revisa:

• Integridad: PRAGMA foreign_keys = ON en cada conexión (SQLite) y NOT NULL, UNIQUE y CHECK donde correspondan.
• Inyección: la aplicación solo usa consultas parametrizadas; ningún valor del usuario se pega en el SQL.
• Permisos: la aplicación no usa la cuenta de administrador (rol con GRANT solo de lo necesario).
• Datos personales: si añades usuarios con contraseña, guarda solo el hash (bcrypt o Argon2).
• Copias: un comando de respaldo programado y una restauración probada.

7.Pruébalo: panel de informes

Este panel reúne los informes del proyecto. Elige uno para ver su consulta y su resultado (ejecutado en SQLite real sobre los datos de tienda.sql).

8.Errores frecuentes

• Empezar a escribir tablas sin diseñar antes las entidades y relaciones: luego hay que rehacer todo.
• Olvidar PRAGMA foreign_keys = ON al cargar el script y al conectar desde la aplicación.
• Cargar datos antes de crear las tablas relacionadas, o crear las tablas en orden incorrecto: primero las que no dependen de otras.
• Registrar un pedido sin transacción: un fallo deja el pedido sin líneas o el stock sin descontar.
• Informes con INNER JOIN que ocultan clientes o productos sin ventas: usa LEFT JOIN cuando haya que incluirlos.
• Sumar importes tras un JOIN que repite filas (por ejemplo, unir pedidos con líneas y sumar un campo del pedido).
• No guardar el script .sql y modificar la base a mano: no se puede reproducir.
• No probar la restauración de la copia.

9.Glosario