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.

person encoding in laptop
Photo by Lukas on Pexels.com
error: Content is protected !!

Descubre más desde InfoGonzalez - Blog de formador e informático

Suscríbete ahora para seguir leyendo y obtener acceso al archivo completo.

Seguir leyendo

Este sitio web utiliza cookies, si necesitas más información puedes visitar nuestra política de privacidad    Ver
Privacidad
Creative Commons License
Except where otherwise noted, the content on this site is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.