Misión 2 · Lección 3

Consultas Avanzadas en SQL

Objetivos

• Dominar subconsultas y consultas anidadas.
• Aprender sobre uniones, intersecciones y diferencias.
• Utilizar funciones de ventana para análisis avanzado.

Subconsultas

-- Productos con precio mayor al promedio
SELECT nombre, precio
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos);

-- Clientes con al menos un pedido
SELECT nombre FROM clientes
WHERE id IN (SELECT cliente_id FROM pedidos);

Uso de EXISTS

-- Clientes que tienen pedidos
SELECT nombre FROM clientes c
WHERE EXISTS (
  SELECT 1 FROM pedidos p
  WHERE p.cliente_id = c.id
);

EXISTS devuelve verdadero si la subconsulta tiene resultados.

UNION, INTERSECT, EXCEPT

-- UNION: combina resultados
SELECT nombre FROM clientes
UNION
SELECT nombre FROM proveedores;

-- INTERSECT: coincidencias
SELECT nombre FROM clientes
INTERSECT
SELECT nombre FROM empleados;

-- EXCEPT: diferencia
SELECT nombre FROM clientes
EXCEPT
SELECT nombre FROM clientes_inactivos;

Funciones de Ventana

-- Ranking de productos más caros
SELECT nombre, precio,
       RANK() OVER (ORDER BY precio DESC) AS ranking
FROM productos;

-- Suma acumulada de pedidos por cliente
SELECT cliente_id, total,
       SUM(total) OVER (PARTITION BY cliente_id ORDER BY id) AS acumulado
FROM pedidos;

CTE — Common Table Expressions

-- Total de ventas por cliente usando CTE
WITH ventas AS (
  SELECT cliente_id, SUM(total) AS total
  FROM pedidos
  GROUP BY cliente_id
)
SELECT c.nombre, v.total
FROM clientes c
JOIN ventas v ON v.cliente_id = c.id;

CTE Recursivas

-- Ejemplo: jerarquía de empleados
WITH RECURSIVE subordinates AS (
  SELECT id, nombre, jefe_id FROM empleados WHERE id = 1
  UNION ALL
  SELECT e.id, e.nombre, e.jefe_id
  FROM empleados e
  INNER JOIN subordinates s ON s.id = e.jefe_id
)
SELECT * FROM subordinates;

Vistas

-- Crear vista
CREATE VIEW v_clientes_activos AS
SELECT * FROM clientes WHERE activo = true;

-- Consultar vista
SELECT * FROM v_clientes_activos;

Vistas Materializadas (PostgreSQL)

-- Crear vista materializada
CREATE MATERIALIZED VIEW v_resumen_ventas AS
SELECT cliente_id, SUM(total) AS total
FROM pedidos
GROUP BY cliente_id;

-- Actualizar datos
REFRESH MATERIALIZED VIEW v_resumen_ventas;

Ejercicio Guiado

1. Consulta productos más caros que el promedio.
2. Obtén clientes que han comprado más de 3 veces.
3. Usa una función de ventana para calcular ranking de ventas.

-- Pista: ranking de clientes por total gastado
SELECT c.nombre,
       SUM(p.total) AS total_gastado,
       RANK() OVER (ORDER BY SUM(p.total) DESC) AS ranking
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
GROUP BY c.nombre;