Sentencias MySQL Muy Complejas Utilizando la Base de Datos Comisaria

En este artículo, exploraremos algunas de las sentencias MySQL más complejas que se pueden utilizar para manejar y analizar datos en una base de datos. Utilizaremos la base de datos Comisaria, que incluye tablas para agentes, ciudadanos, casos y reportes. Estos ejemplos demostrarán el poder y la flexibilidad de MySQL para realizar consultas avanzadas y manipulaciones de datos.

Creación de la Base de Datos y Tablas

Para referencia, aquí están las definiciones de las tablas y algunos datos iniciales insertados en ellas:

CREATE DATABASE Comisaria;
USE Comisaria;

-- 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)
);

-- Insertar datos en la tabla Agentes
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');

-- Insertar datos en la tabla Ciudadanos
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');

-- Insertar datos en la tabla Casos
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);

-- Insertar datos en la tabla Reportes
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');

Sentencias SELECT Muy Complejas

1. Subconsultas Correlacionadas

Vamos a encontrar los agentes que han gestionado más casos que la media de casos gestionados por todos los agentes.

SELECT nombre
FROM Agentes a
WHERE (SELECT COUNT(*)
       FROM Casos c
       WHERE c.id_agente = a.id_agente) > 
      (SELECT AVG(cantidad)
       FROM (SELECT COUNT(*) AS cantidad
             FROM Casos
             GROUP BY id_agente) AS subquery);

Esta consulta utiliza una subconsulta correlacionada para contar los casos gestionados por cada agente y compararlo con la media.

2. CTEs y Funciones de Ventana

Vamos a usar una CTE para calcular la duración de cada caso y luego una función de ventana para clasificar los casos por duración.

WITH CasosDuracion AS (
    SELECT 
        id_caso, 
        descripcion, 
        DATEDIFF(CURDATE(), fecha_inicio) AS duracion
    FROM 
        Casos
)
SELECT 
    id_caso, 
    descripcion, 
    duracion, 
    RANK() OVER (ORDER BY duracion DESC) AS ranking
FROM 
    CasosDuracion;

Esta consulta utiliza una CTE para calcular la duración de cada caso y luego aplica la función de ventana RANK() para clasificar los casos por duración.

Sentencias DELETE Muy Complejas

3. Eliminación Condicional Basada en JOIN

Vamos a eliminar todos los reportes que están asociados a casos que están en estado «Cerrado» y que tienen más de un año.

DELETE r
FROM Reportes r
JOIN Casos c ON r.id_caso = c.id_caso
WHERE c.estado = 'Cerrado' AND c.fecha_inicio < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

Esta consulta utiliza un JOIN para identificar los reportes relacionados con casos cerrados y elimina aquellos que cumplen con la condición de antigüedad.

Sentencias UPDATE Muy Complejas

4. Actualización Basada en JOIN y Condiciones Anidadas

Vamos a actualizar el salario de los agentes que han gestionado casos en los últimos seis meses, incrementándolo en un 10%.

UPDATE Agentes a
JOIN (SELECT id_agente
      FROM Casos
      WHERE fecha_inicio >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
      GROUP BY id_agente) c ON a.id_agente = c.id_agente
SET a.salario = a.salario * 1.10;

Esta consulta utiliza un JOIN con una subconsulta que identifica a los agentes que han gestionado casos recientemente y actualiza sus salarios.

Combinaciones de SELECT, DELETE y UPDATE

5. Combinando Operaciones Complejas

Vamos a realizar una operación que seleccione los casos abiertos, actualice su estado a «En Proceso» y luego elimine todos los reportes asociados a esos casos.

Seleccionar y actualizar casos abiertos:

UPDATE Casos
SET estado = 'En Proceso'
WHERE estado = 'Abierto';

Eliminar reportes asociados:

DELETE r
FROM Reportes r
JOIN Casos c ON r.id_caso = c.id_caso
WHERE c.estado = 'En Proceso';

Conclusión

Las sentencias SQL complejas permiten manejar y analizar datos de manera avanzada en MySQL. Utilizando subconsultas, CTEs, funciones de ventana, JOINs y condiciones complejas, puedes realizar operaciones sofisticadas que optimizan la gestión y análisis de datos en una base de datos. Al dominar estas técnicas, puedes mejorar significativamente la eficiencia y efectividad de tus consultas SQL en proyectos reales.