Las bases de datos relacionales permiten realizar consultas avanzadas para obtener información precisa y compleja. En este artículo, exploraremos sentencias SQL más rebuscadas utilizando una base de datos de ejemplo relacionada con una comisaría.
Creación de la Base de Datos
Primero, crearemos la 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
Insertaremos 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 Complejas
Subconsultas Correlacionadas
Una subconsulta correlacionada es una subconsulta que depende de la consulta externa.
Ejemplo: Encontrar el salario promedio de los agentes y listar los agentes que ganan más que este promedio
SELECT nombre, salario
FROM Agentes
WHERE salario > (SELECT AVG(salario) FROM Agentes);JOIN con Subconsultas
Combina filas de dos o más tablas utilizando subconsultas.
Ejemplo: Obtener el nombre del agente y la descripción del caso para todos los casos abiertos
SELECT A.nombre, C.descripcion
FROM Agentes A
JOIN (SELECT id_caso, descripcion, id_agente FROM Casos WHERE estado = 'Abierto') C
ON A.id_agente = C.id_agente;Uso Avanzado de Funciones Agregadas
Ejemplo: Calcular el salario total y promedio por rango, ordenado por salario total en orden descendente
SELECT rango, SUM(salario) AS salario_total, AVG(salario) AS salario_promedio
FROM Agentes
GROUP BY rango
ORDER BY salario_total DESC;Combinación de JOIN y Funciones de Agregación
Ejemplo: Contar el número de casos asignados a cada agente y filtrar los agentes con más de un caso
SELECT A.nombre, COUNT(C.id_caso) AS numero_de_casos
FROM Agentes A
LEFT JOIN Casos C ON A.id_agente = C.id_agente
GROUP BY A.id_agente
HAVING COUNT(C.id_caso) > 1;Subconsultas en la Cláusula FROM
Las subconsultas pueden utilizarse en la cláusula FROM para crear tablas temporales.
Ejemplo: Encontrar el agente con el mayor número de casos asignados
SELECT nombre, numero_de_casos
FROM (
SELECT A.nombre, COUNT(C.id_caso) AS numero_de_casos
FROM Agentes A
LEFT JOIN Casos C ON A.id_agente = C.id_agente
GROUP BY A.nombre
) AS AgentesCasos
ORDER BY numero_de_casos DESC
LIMIT 1;Uso de CASE
La cláusula CASE permite realizar evaluaciones condicionales.
Ejemplo: Clasificar los agentes según su salario
SELECT nombre,
CASE
WHEN salario < 40000 THEN 'Bajo'
WHEN salario BETWEEN 40000 AND 50000 THEN 'Medio'
ELSE 'Alto'
END AS clasificacion_salario
FROM Agentes;Manipulación de Cadenas y Fechas
Ejemplo: Convertir nombres de agentes a mayúsculas y calcular los días desde su ingreso
SELECT UPPER(nombre) AS nombre_mayusculas,
DATEDIFF(CURDATE(), fecha_ingreso) AS dias_desde_ingreso
FROM Agentes;Combinación de JOIN y Vistas
Ejemplo: Crear una vista de casos abiertos y luego consultar 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
JOIN CasosAbiertos CA ON R.id_caso = CA.id_caso;Subconsultas Complejas con EXISTS
El operador EXISTS se utiliza para comprobar la existencia de filas en una subconsulta.
Ejemplo: Encontrar todos los agentes que tienen al menos un caso abierto asignado
SELECT nombre
FROM Agentes A
WHERE EXISTS (
SELECT 1
FROM Casos C
WHERE C.id_agente = A.id_agente AND C.estado = 'Abierto'
);Subconsultas en SELECT
Las subconsultas también pueden utilizarse en la cláusula SELECT para devolver valores calculados.
Ejemplo: Listar agentes con el número de casos asignados y la descripción del caso más reciente
SELECT A.nombre,
(SELECT COUNT(*) FROM Casos C WHERE C.id_agente = A.id_agente) AS numero_de_casos,
(SELECT descripcion FROM Casos C WHERE C.id_agente = A.id_agente ORDER BY fecha_inicio DESC LIMIT 1) AS caso_mas_reciente
FROM Agentes A;Conclusión
Las consultas SQL avanzadas permiten realizar análisis complejos y obtener información detallada de las bases de datos. En este artículo, hemos explorado una variedad de técnicas avanzadas utilizando una base de datos de comisaría como ejemplo. Estas técnicas incluyen subconsultas correlacionadas, JOIN con subconsultas, funciones de agregación avanzadas, uso de CASE, manipulación de cadenas y fechas, vistas, y operadores como EXISTS. Estas herramientas son esenciales para cualquier profesional que trabaje con bases de datos y puede ser cruciales en un examen de SQL.
