Repaso de SQL intermedio: lo que se asume

Conceptos que el curso da por sabidos

El material avanzado entra rápido. La primera lección del módulo siguiente trabaja CTE recursivas sobre jerarquías, asumiendo que el alumno ya escribe CTEs simples sin pensarlas. Antes de llegar ahí conviene comprobar que la base intermedia está sólida.

Esta lección es un chequeo diagnóstico organizado en seis bloques. No es un re-aprendizaje: cada bloque revisa la idea esencial, muestra un fragmento de código de referencia y termina con una pregunta de autoevaluación. Si el alumno responde con seguridad a las seis, está listo para el resto del curso.

Si tras el repaso queda alguna pregunta sin respuesta clara, lo recomendable es revisar el módulo correspondiente del curso intermedio antes de continuar. El esfuerzo invertido en cerrar la base se devuelve con creces durante las lecciones avanzadas.

DML completo: filtrar, ordenar y combinar

El DML (Data Manipulation Language) cubre las cuatro operaciones de manipulación: SELECT, INSERT, UPDATE y DELETE. En el nivel intermedio se asume comodidad con WHERE con operadores lógicos (AND, OR, NOT), IN, BETWEEN, LIKE, IS NULL, además de ORDER BY, LIMIT/OFFSET y DISTINCT.

SELECT nombre, salario, departamento
FROM empleados
WHERE departamento IN ('Ingeniería', 'Producto')
  AND salario BETWEEN 30000 AND 80000
  AND fecha_alta IS NOT NULL
ORDER BY salario DESC
LIMIT 10;

Las modificaciones con UPDATE y DELETE exigen siempre una cláusula WHERE precisa. Una buena práctica es ejecutar primero un SELECT con la misma condición para confirmar las filas afectadas y luego envolver la modificación en una transacción.

BEGIN;
UPDATE empleados SET salario = salario * 1.05
 WHERE departamento = 'Ingeniería';
-- comprobar el número de filas afectadas
COMMIT;  -- o ROLLBACK; si algo no cuadra

Autoevaluación: ¿Sabes diferenciar cuándo WHERE col IS NULL es la única forma correcta de filtrar nulos y por qué WHERE col = NULL no funciona?

Combinación de tablas: los cuatro JOINs

El curso avanzado parte de que el alumno escribe INNER JOIN, LEFT JOIN, RIGHT JOIN y FULL JOIN con criterio. La regla clave es qué filas devuelve cada uno cuando no hay coincidencia en la tabla del otro lado.

SELECT e.nombre, d.nombre AS departamento
FROM empleados e
LEFT JOIN departamentos d ON d.id = e.id_departamento;
-- empleados sin departamento aparecen con d.nombre = NULL

INNER JOIN filtra; LEFT JOIN preserva todas las filas de la izquierda; RIGHT JOIN es su simétrico; FULL JOIN preserva ambas. Las consultas de auditoría aprovechan esta propiedad: un LEFT JOIN ... WHERE col_derecha IS NULL detecta filas huérfanas.

-- Detectar empleados sin departamento asignado
SELECT e.id, e.nombre
FROM empleados e
LEFT JOIN departamentos d ON d.id = e.id_departamento
WHERE d.id IS NULL;

Autoevaluación: ¿Por qué INNER JOIN y LEFT JOIN ... WHERE col_derecha IS NOT NULL devuelven el mismo resultado?

Agregaciones, GROUP BY y HAVING

Las funciones COUNT, SUM, AVG, MIN, MAX aplicadas con GROUP BY resumen filas en grupos. La distinción clave del nivel intermedio es WHERE filtra antes de agrupar; HAVING filtra después.

SELECT departamento,
       COUNT(*)        AS empleados,
       AVG(salario)    AS salario_medio,
       MAX(salario)    AS salario_top
FROM empleados
WHERE fecha_baja IS NULL          -- antes de agrupar
GROUP BY departamento
HAVING COUNT(*) >= 5;              -- después de agrupar

Un detalle que el avanzado retoma: en SQL estándar todas las columnas no agregadas en SELECT deben aparecer en GROUP BY (o el motor exige el modo only_full_group_by en MySQL). Esto enseña a pensar en grupos como unidades, no como filas.

Autoevaluación: ¿Sabes por qué SELECT departamento, nombre, COUNT(*) FROM empleados GROUP BY departamento da error o resultados inesperados según el motor?

Subqueries y CTE simple con WITH

Las subqueries son consultas dentro de otra. Aparecen en WHERE (correlacionadas o no), en FROM como tablas derivadas, en SELECT como columnas calculadas y con EXISTS/NOT EXISTS para presencia o ausencia.

-- Empleados con salario superior al medio de su departamento (correlacionada)
SELECT nombre, salario, departamento
FROM empleados e
WHERE salario > (
  SELECT AVG(salario)
  FROM empleados
  WHERE departamento = e.departamento
);

Las CTE (Common Table Expressions) con WITH ofrecen una forma más legible de escribir consultas en varios pasos. Una CTE simple es un bloque temporal con nombre que vive durante la ejecución de la consulta principal.

WITH ventas_mes AS (
  SELECT id_cliente, SUM(importe) AS total
  FROM facturas
  WHERE fecha >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY id_cliente
)
SELECT c.nombre, v.total
FROM clientes c
JOIN ventas_mes v ON v.id_cliente = c.id
WHERE v.total > 5000;

El curso avanzado parte de aquí y lleva la idea más lejos con CTE recursivas y CTEs encadenadas que pasan información entre ellas.

Autoevaluación: ¿Tienes claro cuándo una subquery correlacionada es ineficiente y cuándo conviene reescribirla como JOIN o como CTE?

Window functions básicas

Las funciones de ventana calculan valores sobre un conjunto de filas relacionadas con la fila actual sin colapsar el resultado en grupos. Las básicas que el curso avanzado da por sabidas son ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD y agregaciones con OVER.

SELECT nombre, departamento, salario,
       ROW_NUMBER() OVER (PARTITION BY departamento ORDER BY salario DESC) AS posicion,
       AVG(salario) OVER (PARTITION BY departamento)                       AS media_dpto
FROM empleados;

La cláusula OVER define la ventana: PARTITION BY agrupa filas como GROUP BY pero sin colapsarlas, ORDER BY define el orden dentro de cada partición. El curso avanzado profundiza con frames explícitos (ROWS BETWEEN, RANGE BETWEEN) para medias móviles y totales acumulados.

Autoevaluación: ¿Distingues ROW_NUMBER de RANK y de DENSE_RANK cuando hay empates?

Transacciones e índices simples

Las transacciones garantizan las propiedades ACID (atomicidad, consistencia, aislamiento, durabilidad). En el nivel intermedio se asume el flujo BEGIN → operaciones → COMMIT o ROLLBACK, y la idea de que una transacción es todo o nada.

BEGIN;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;  -- ambos UPDATE persisten o ninguno

El curso avanzado retoma esto con los niveles de aislamiento (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) y los fenómenos de concurrencia que evita cada uno: dirty reads, non-repeatable reads, phantom reads.

Los índices simples se asumen también: el alumno sabe crear un índice B-tree con CREATE INDEX, conoce que las claves primarias y UNIQUE crean índice automáticamente y entiende el coste en escrituras a cambio del acelerón en lecturas.

CREATE INDEX idx_empleados_departamento ON empleados (departamento);
CREATE INDEX idx_empleados_salario      ON empleados (salario);

El curso avanzado va al detalle: índices compuestos con orden de columnas, covering indexes con INCLUDE, parciales con WHERE, funcionales sobre expresiones y los tipos BRIN, GIN, GiST, además de leer planes de ejecución para decidir qué índice hace falta.

Autoevaluación: ¿Sabes por qué crear un índice sobre cada columna de una tabla es una mala idea?

Si todo encaja, sigamos

Si las seis preguntas de autoevaluación tienen respuesta clara, el alumno está preparado para el resto del curso. La siguiente lección presenta el itinerario módulo a módulo y propone recorridos alternativos según el perfil profesional.