Las bases de datos relacionales permiten realizar consultas complejas para gestionar y analizar la información de manera eficiente. En este artículo, exploraremos una variedad de conceptos y técnicas que podrían ser esenciales en un examen de SQL, 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');
Conceptos Clave y Ejemplos de Consultas
SELECT
La instrucción SELECT
se utiliza para seleccionar datos de una base de datos.
SELECT nombre, rango FROM Agentes;
DISTINCT
El uso de DISTINCT
elimina filas duplicadas en los resultados.
SELECT DISTINCT estado FROM Casos;
AS
AS
permite renombrar columnas o tablas.
SELECT nombre AS 'Nombre del Agente', salario AS 'Salario Anual' FROM Agentes;
WHERE
La cláusula WHERE
se usa para filtrar registros.
SELECT * FROM Casos WHERE estado = 'Abierto';
Operadores en WHERE
SELECT * FROM Agentes WHERE salario > 40000;
SELECT * FROM Ciudadanos WHERE direccion LIKE 'Calle%';
SELECT * FROM Casos WHERE fecha_inicio BETWEEN '2022-01-01' AND '2022-12-31';
SELECT * FROM Casos WHERE id_agente IN (1, 2);
ORDER BY
Ordena los resultados de la consulta.
SELECT nombre, salario FROM Agentes ORDER BY salario DESC;
Funciones de Agregación
COUNT
Cuenta el número de filas.
SELECT COUNT(*) FROM Casos WHERE estado = 'Abierto';
SUM
Suma los valores de una columna.
SELECT SUM(salario) FROM Agentes;
MIN y MAX
Encuentra el valor mínimo y máximo.
SELECT MIN(salario) AS 'Salario Mínimo', MAX(salario) AS 'Salario Máximo' FROM Agentes;
AVG
Calcula el promedio.
SELECT AVG(salario) AS 'Salario Promedio' FROM Agentes;
JOIN
Combina filas de dos o más tablas.
SELECT A.nombre, C.descripcion
FROM Agentes A
JOIN Casos C ON A.id_agente = C.id_agente;
Subconsultas
Las subconsultas se utilizan para realizar consultas dentro de otras consultas.
SELECT nombre FROM Agentes WHERE id_agente IN (SELECT id_agente FROM Casos WHERE estado = 'Abierto');
INSERT
Inserta nuevos registros en una tabla.
INSERT INTO Agentes (nombre, rango, salario, fecha_ingreso) VALUES ('Laura Sánchez', 'Cabo', 42000.00, '2021-08-15');
UPDATE
Actualiza registros existentes.
UPDATE Agentes SET salario = salario * 1.05 WHERE rango = 'Cabo';
DELETE
Elimina registros de una tabla.
DELETE FROM Casos WHERE estado = 'Cerrado';
Vistas
Las vistas almacenan consultas como si fueran tablas.
CREATE VIEW AgentesActivos AS
SELECT nombre, rango, salario FROM Agentes WHERE fecha_ingreso > '2018-01-01';
Funciones para Cadenas de Caracteres
Algunas funciones útiles incluyen CHAR_LENGTH
, CONCAT
, LOWER
, UPPER
, y TRIM
.
SELECT CONCAT(nombre, ' - ', rango) AS 'Identificación' FROM Agentes;
Funciones de Fecha
Trabaja con fechas utilizando CURDATE()
, CURTIME()
, DATEDIFF
, DATE_ADD
, y DATE_SUB
.
SELECT CURDATE();
SELECT DATEDIFF(CURDATE(), fecha_ingreso) AS 'Días en Servicio' FROM Agentes;
HAVING
Filtra resultados después de una agregación.
SELECT id_agente, COUNT(*) AS 'Casos Asignados'
FROM Casos
GROUP BY id_agente
HAVING COUNT(*) > 1;
Conclusión
Este artículo proporciona una guía completa sobre los conceptos y consultas SQL más importantes utilizando una base de datos de ejemplo de una comisaría. Estos conceptos son fundamentales para gestionar y analizar datos de manera eficiente y son esenciales para cualquier examen de SQL.
Gracias por visitar mi blog de informática, mi nombre es Tomás y soy formador y desarrollador web. Si quiere usted dejarme alguna sugerencia, ayuda o quiere un servicio de formación estoy escuchando ofertas en tomas.gonzalez@infogonzalez.com, en Facebook a https://www.facebook.com/Infogonzalez estoy deseando escucharle. Su duda o sugerencia NO molesta.