Dominando SQL Avanzado: Una Guía Completa para Consultas Complejas y Conceptos Clave

En el ámbito de las bases de datos, el dominio de SQL es esencial para extraer y manipular datos de manera eficiente. En este artículo, exploraremos una mezcla de consultas SQL avanzadas utilizando una base de datos de ejemplo relacionada con una comisaría. Cubriremos INNER JOIN, OUTER JOIN, subconsultas complejas, y otros conceptos clave no mencionados en los apuntes pero que son importantes.

Creación de la Base de Datos

Para ilustrar los conceptos, comenzamos con la creación de una base de datos y las tablas necesarias:

CREATE DATABASE Comisaria;
USE Comisaria;

Tablas de la Base de Datos

Tabla de Agentes

CREATE TABLE Agentes (
    id_agente INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    rango VARCHAR(30),
    salario DECIMAL(10, 2) NOT NULL,
    fecha_ingreso DATE
);

Tabla de Ciudadanos

CREATE TABLE Ciudadanos (
    id_ciudadano INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    direccion VARCHAR(100),
    telefono VARCHAR(15)
);

Tabla de Casos

CREATE TABLE Casos (
    id_caso INT AUTO_INCREMENT PRIMARY KEY,
    descripcion TEXT,
    fecha_inicio DATE,
    estado VARCHAR(20),
    id_agente INT,
    FOREIGN KEY (id_agente) REFERENCES Agentes(id_agente)
);

Tabla de Reportes

CREATE TABLE Reportes (
    id_reporte INT AUTO_INCREMENT PRIMARY KEY,
    id_caso INT,
    id_ciudadano INT,
    descripcion TEXT,
    fecha_reporte DATE,
    FOREIGN KEY (id_caso) REFERENCES Casos(id_caso),
    FOREIGN KEY (id_ciudadano) REFERENCES Ciudadanos(id_ciudadano)
);

Inserción de Datos

Insertamos algunos datos en estas tablas:

INSERT INTO Agentes (nombre, rango, salario, fecha_ingreso) VALUES
('Juan Pérez', 'Sargento', 45000.00, '2015-06-23'),
('Ana Gómez', 'Teniente', 50000.00, '2018-09-15'),
('Carlos López', 'Cabo', 40000.00, '2020-01-10');

INSERT INTO Ciudadanos (nombre, direccion, telefono) VALUES
('Pedro Martínez', 'Calle Falsa 123', '555-1234'),
('María Fernández', 'Avenida Siempre Viva 742', '555-5678'),
('Luis García', 'Boulevard Central 456', '555-8765');

INSERT INTO Casos (descripcion, fecha_inicio, estado, id_agente) VALUES
('Robo en tienda', '2022-03-10', 'Abierto', 1),
('Asalto a mano armada', '2022-04-22', 'Cerrado', 2),
('Vandalismo en parque', '2022-05-15', 'En Proceso', 3);

INSERT INTO Reportes (id_caso, id_ciudadano, descripcion, fecha_reporte) VALUES
(1, 1, 'Robo de mercancía en tienda', '2022-03-10'),
(2, 2, 'Asalto a mano armada en banco', '2022-04-22'),
(3, 3, 'Daños en parque público', '2022-05-15');

Consultas SQL Avanzadas

INNER JOIN

El INNER JOIN devuelve las filas que tienen coincidencias en ambas tablas.

Ejemplo: Obtener los nombres de los agentes y las descripciones de los casos que están gestionando

SELECT A.nombre, C.descripcion
FROM Agentes A
INNER JOIN Casos C ON A.id_agente = C.id_agente;

LEFT OUTER JOIN

El LEFT OUTER JOIN devuelve todas las filas de la tabla de la izquierda y las filas coincidentes de la tabla de la derecha. Si no hay coincidencia, los resultados contendrán NULL en las columnas de la tabla de la derecha.

Ejemplo: Listar todos los agentes y los casos que están gestionando, incluyendo agentes sin casos asignados

SELECT A.nombre, C.descripcion
FROM Agentes A
LEFT OUTER JOIN Casos C ON A.id_agente = C.id_agente;

RIGHT OUTER JOIN

El RIGHT OUTER JOIN devuelve todas las filas de la tabla de la derecha y las filas coincidentes de la tabla de la izquierda. Si no hay coincidencia, los resultados contendrán NULL en las columnas de la tabla de la izquierda.

Ejemplo: Listar todos los casos y los nombres de los agentes responsables, incluyendo casos sin agentes asignados

SELECT C.descripcion, A.nombre
FROM Casos C
RIGHT OUTER JOIN Agentes A ON C.id_agente = A.id_agente;

Subconsultas Complejas

Ejemplo: Encontrar los agentes que tienen más de un caso asignado y listar sus nombres y la cantidad de casos

SELECT A.nombre, COUNT(C.id_caso) AS numero_de_casos
FROM Agentes A
INNER JOIN Casos C ON A.id_agente = C.id_agente
GROUP BY A.nombre
HAVING COUNT(C.id_caso) > 1;

Uso de SELECT IN

El operador IN se utiliza para especificar múltiples valores en una cláusula WHERE.

Ejemplo: Obtener los nombres de los agentes que han gestionado casos cerrados

SELECT nombre
FROM Agentes
WHERE id_agente IN (SELECT id_agente FROM Casos WHERE estado = 'Cerrado');

Combinación de Subconsultas y JOIN

Ejemplo: Listar los ciudadanos que han reportado casos gestionados por el agente «Juan Pérez»

SELECT C.nombre
FROM Ciudadanos C
WHERE C.id_ciudadano IN (
    SELECT R.id_ciudadano
    FROM Reportes R
    INNER JOIN Casos CA ON R.id_caso = CA.id_caso
    INNER JOIN Agentes A ON CA.id_agente = A.id_agente
    WHERE A.nombre = 'Juan Pérez'
);

Subconsultas Correlacionadas

Las subconsultas correlacionadas dependen de la consulta externa para cada fila procesada.

Ejemplo: Encontrar los agentes cuyo salario es mayor que el salario promedio de todos los agentes

SELECT nombre, salario 
FROM Agentes A1
WHERE salario > (SELECT AVG(salario) FROM Agentes A2 WHERE A1.id_agente != A2.id_agente);

Vistas y Consultas Complejas

Las vistas pueden simplificar la consulta de datos complejos.

Ejemplo: Crear una vista de los casos abiertos y luego listar los reportes relacionados

CREATE VIEW CasosAbiertos AS
SELECT id_caso, descripcion, id_agente
FROM Casos
WHERE estado = 'Abierto';

SELECT R.id_reporte, R.descripcion, CA.descripcion AS caso_descripcion
FROM Reportes R
INNER JOIN CasosAbiertos CA ON R.id_caso = CA.id_caso;

Conceptos Clave Adicionales

Índices

Los índices mejoran la velocidad de las consultas en grandes tablas. Crearlos en columnas frecuentemente buscadas puede acelerar las consultas.

Ejemplo: Crear un índice en la columna nombre de la tabla Agentes

CREATE INDEX idx_nombre ON Agentes(nombre);

Transacciones

Las transacciones aseguran que un conjunto de operaciones SQL se realice de manera completa y correcta.

Ejemplo: Uso de transacciones para asegurar integridad

START TRANSACTION;

UPDATE Agentes SET salario = salario * 1.1 WHERE rango = 'Cabo';
DELETE FROM Casos WHERE estado = 'Cerrado';

COMMIT;

Funciones de Ventana

Las funciones de ventana permiten realizar cálculos sobre un conjunto de filas relacionadas.

Ejemplo: Calcular el salario promedio por rango de agente y mostrarlo junto a cada agente

SELECT nombre, rango, salario, 
       AVG(salario) OVER (PARTITION BY rango) AS salario_promedio_por_rango
FROM Agentes;

CTEs (Common Table Expressions)

Las expresiones de tabla comunes (CTEs) proporcionan una forma de crear tablas temporales para usar dentro de una consulta.

Ejemplo: Utilizar CTE para listar agentes con más de un caso

WITH AgentesConCasos AS (
    SELECT A.id_agente, A.nombre, COUNT(C.id_caso) AS numero_de_casos
    FROM Agentes A
    INNER JOIN Casos C ON A.id_agente = C.id_agente
    GROUP BY A.id_agente, A.nombre
)
SELECT nombre, numero_de_casos
FROM AgentesConCasos
WHERE numero_de_casos > 1;

Conclusión

Este artículo proporciona una guía completa sobre conceptos y consultas SQL avanzadas utilizando una base de datos de comisaría. Hemos cubierto INNER JOIN, OUTER JOIN, subconsultas complejas, vistas, índices, transacciones, funciones de ventana y CTEs.