Consultas SQL Avanzadas: Ejemplos Complejos con una Base de Datos de Comisaría

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.

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.