Proyecto SQL Intermedio Nº2: Sistema de gestión de usuarios y pedidos

En este segundo proyecto aplicarás filtros avanzados, JOINS, subconsultas, CASE, funciones avanzadas, vistas e índices. Construiremos un sistema de gestión de usuarios y pedidos con reportes completos.

1. Definición del proyecto

Construiremos un sistema con tres tablas:

• usuarios
• pedidos
• productos

2. Crear tablas

CREATE TABLE usuarios (
    id INT PRIMARY KEY,
    nombre VARCHAR(50),
    ciudad VARCHAR(50),
    activo BOOLEAN
);

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

CREATE TABLE pedidos (
    id INT PRIMARY KEY,
    usuario_id INT,
    producto_id INT,
    fecha DATE,
    estado VARCHAR(50),
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id),
    FOREIGN KEY (producto_id) REFERENCES productos(id)
);
    

3. Insertar datos

INSERT INTO usuarios VALUES
(1, 'Ana', 'Madrid', TRUE),
(2, 'Luis', 'Barcelona', TRUE),
(3, 'Marta', 'Valencia', FALSE),
(4, 'Carlos', 'Madrid', TRUE);

INSERT INTO productos VALUES
(1, 'Laptop', 799.99),
(2, 'Tablet', 299.99),
(3, 'Auriculares', 49.99);

INSERT INTO pedidos VALUES
(1, 1, 1, '2024-02-10', 'Enviado'),
(2, 1, 3, '2024-02-12', 'Pendiente'),
(3, 2, 2, '2024-02-15', 'Enviado'),
(4, 4, 1, '2024-02-20', 'Cancelado');
    

4. Consultas básicas

SELECT * FROM usuarios;
SELECT * FROM productos;
SELECT * FROM pedidos;
    

5. Consultas avanzadas

-- Pedidos con detalle de usuario y producto
SELECT u.nombre AS usuario, p.nombre AS producto, pe.estado, pe.fecha
FROM pedidos pe
JOIN usuarios u ON pe.usuario_id = u.id
JOIN productos p ON pe.producto_id = p.id;

-- Usuarios activos con pedidos enviados
SELECT u.nombre, pe.producto_id, pe.estado
FROM usuarios u
JOIN pedidos pe ON u.id = pe.usuario_id
WHERE u.activo = TRUE AND pe.estado = 'Enviado';

-- Clasificación de pedidos por estado
SELECT id,
       CASE
           WHEN estado = 'Enviado' THEN 'Completado'
           WHEN estado = 'Pendiente' THEN 'En proceso'
           ELSE 'Cancelado'
       END AS estado_clasificado
FROM pedidos;
    

6. Subconsultas

-- Usuarios con pedidos enviados
SELECT nombre
FROM usuarios
WHERE id IN (
    SELECT usuario_id FROM pedidos WHERE estado = 'Enviado'
);

-- Productos más caros que el promedio
SELECT nombre, precio
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos);
    

7. Vistas

CREATE VIEW vista_pedidos_detalle AS
SELECT u.nombre AS usuario, p.nombre AS producto, pe.estado, pe.fecha
FROM pedidos pe
JOIN usuarios u ON pe.usuario_id = u.id
JOIN productos p ON pe.producto_id = p.id;
    

8. Índices

CREATE INDEX idx_pedidos_usuario
ON pedidos(usuario_id);

CREATE INDEX idx_pedidos_estado
ON pedidos(estado);
    

9. Reporte final

SELECT u.nombre AS usuario,
       COUNT(*) AS total_pedidos,
       SUM(pr.precio) AS total_gastado
FROM pedidos pe
JOIN usuarios u ON pe.usuario_id = u.id
JOIN productos pr ON pe.producto_id = pr.id
GROUP BY u.nombre
ORDER BY total_gastado DESC;
    

10. Ejercicios

1. Crea una vista con usuarios activos y sus pedidos.
2. Crea un índice para acelerar búsquedas por ciudad.
3. Muestra el producto más vendido.
4. Muestra el usuario con más pedidos enviados.
5. Explica por qué las vistas simplifican reportes.

11. Errores comunes

• No usar CASE correctamente.
• Crear vistas demasiado complejas.
• Indexar columnas con pocos valores distintos.
• Confundir subconsultas con JOIN.

Fin del nivel intermedio

¡Has completado todas las guías del nivel intermedio! Puedes continuar con el nivel avanzado cuando esté disponible.