20 casos de uso prácticos para consultar, limpiar, validar, resumir y auditar datos directamente desde tablas. SQL no solo extrae data: también ayuda a descubrir errores, excepciones, duplicados y señales de negocio.
20 practical use cases for querying, cleaning, validating, summarizing, and auditing data directly from tables. SQL does not only extract data: it also helps uncover errors, exceptions, duplicates, and business signals.
PowerShell ayuda a automatizar el entorno, Python integra y transforma datos, R analiza patrones estadísticos, pero SQL vive en el corazón de las bases de datos. Es el idioma natural para preguntar, filtrar, cruzar, resumir y auditar información donde realmente reside: en tablas relacionales.
SQL es especialmente valioso porque permite trabajar cerca de la fuente oficial. Antes de exportar a Excel, antes de cargar a Power BI y antes de crear un reporte ejecutivo, una buena consulta SQL puede detectar problemas de calidad, inconsistencias, duplicados, registros huérfanos y tendencias operativas.
PowerShell helps automate the environment, Python integrates and transforms data, R analyzes statistical patterns, but SQL lives at the heart of databases. It is the natural language for asking, filtering, joining, summarizing, and auditing information where it truly resides: relational tables.
SQL is especially valuable because it works close to the official source. Before exporting to Excel, before loading Power BI, and before creating an executive report, a good SQL query can detect quality issues, inconsistencies, duplicates, orphan records, and operational trends.
Sirve para entender rápidamente la estructura y los valores reales antes de diseñar una consulta más compleja.
Useful for quickly understanding structure and real values before designing a more complex query.
SELECT TOP 100
*
FROM dbo.EmployeeMaster
ORDER BY Employee_ID;Detecta registros incompletos que pueden romper reportes, cargas o validaciones.
Detect incomplete records that may break reports, loads, or validations.
SELECT
Employee_ID,
First_Name,
Last_Name,
Email,
Department
FROM dbo.EmployeeMaster
WHERE Employee_ID IS NULL
OR Email IS NULL
OR Department IS NULL
OR LTRIM(RTRIM(Email)) = '';Limpia espacios al inicio o final en nombres, departamentos o códigos administrativos.
Clean leading or trailing spaces in names, departments, or administrative codes.
SELECT
Employee_ID,
LTRIM(RTRIM(First_Name)) AS First_Name_Clean,
LTRIM(RTRIM(Last_Name)) AS Last_Name_Clean,
LTRIM(RTRIM(Department)) AS Department_Clean
FROM dbo.EmployeeMaster;Encuentra llaves que deberían ser únicas pero aparecen más de una vez.
Find keys that should be unique but appear more than once.
SELECT
Employee_ID,
COUNT(*) AS Record_Count
FROM dbo.EmployeeMaster
GROUP BY Employee_ID
HAVING COUNT(*) > 1;No todos los duplicados tienen el mismo ID. Esta consulta busca personas que parecen repetidas.
Not all duplicates share the same ID. This query looks for people who appear repeated.
SELECT
First_Name,
Last_Name,
Date_Of_Birth,
COUNT(*) AS Possible_Duplicates
FROM dbo.EmployeeMaster
GROUP BY First_Name, Last_Name, Date_Of_Birth
HAVING COUNT(*) > 1;Ejemplo de regla de negocio: si alguien está activo, debería tener departamento asignado.
Example business rule: if someone is active, they should have an assigned department.
SELECT
Employee_ID,
Full_Name,
Status,
Department
FROM dbo.EmployeeMaster
WHERE Status = 'Active'
AND (Department IS NULL OR LTRIM(RTRIM(Department)) = '');Trae información descriptiva desde una tabla maestra relacionada.
Bring descriptive information from a related master table.
SELECT
e.Employee_ID,
e.Full_Name,
e.Department_ID,
d.Department_Name,
d.Division
FROM dbo.EmployeeMaster e
LEFT JOIN dbo.DepartmentMaster d
ON e.Department_ID = d.Department_ID;Detecta códigos o IDs que existen en una tabla transaccional pero no en la tabla oficial.
Detect codes or IDs that exist in a transaction table but not in the official master table.
SELECT
t.Transaction_ID,
t.Employee_ID,
t.Transaction_Date
FROM dbo.TimeTransactions t
LEFT JOIN dbo.EmployeeMaster e
ON t.Employee_ID = e.Employee_ID
WHERE e.Employee_ID IS NULL;Una consulta simple para validar volúmenes antes de entregar un reporte.
A simple query to validate volumes before delivering a report.
SELECT
Department,
COUNT(*) AS Total_Employees
FROM dbo.EmployeeMaster
GROUP BY Department
ORDER BY Total_Employees DESC;Convierte eventos diarios en una tendencia mensual para análisis operativo.
Turn daily events into a monthly trend for operational analysis.
SELECT
DATEFROMPARTS(YEAR(Transaction_Date), MONTH(Transaction_Date), 1) AS Month_Start,
COUNT(*) AS Transaction_Count,
SUM(Amount) AS Total_Amount
FROM dbo.Transactions
GROUP BY
YEAR(Transaction_Date),
MONTH(Transaction_Date)
ORDER BY Month_Start;Identifica los departamentos, programas o cuentas con mayor volumen.
Identify departments, programs, or accounts with the highest volume.
SELECT
Department,
SUM(Amount) AS Total_Amount,
RANK() OVER (ORDER BY SUM(Amount) DESC) AS Amount_Rank
FROM dbo.Transactions
GROUP BY Department;Permite detectar registros por encima o por debajo del comportamiento normal de su propio grupo.
Detect records above or below the normal behavior of their own group.
SELECT
Employee_ID,
Department,
Amount,
AVG(Amount) OVER (PARTITION BY Department) AS Dept_Avg_Amount,
Amount - AVG(Amount) OVER (PARTITION BY Department) AS Difference_From_Avg
FROM dbo.Transactions;Muy útil para estados actuales, última transacción, último pago o última actualización.
Very useful for current status, latest transaction, latest payment, or latest update.
WITH ranked AS (
SELECT
Employee_ID,
Status,
Effective_Date,
ROW_NUMBER() OVER (
PARTITION BY Employee_ID
ORDER BY Effective_Date DESC
) AS rn
FROM dbo.EmployeeStatusHistory
)
SELECT *
FROM ranked
WHERE rn = 1;Detecta registros que usan códigos fuera de una lista oficial de valores válidos.
Detect records using codes outside an official list of valid values.
SELECT
Employee_ID,
Status_Code
FROM dbo.EmployeeMaster
WHERE Status_Code NOT IN ('A', 'I', 'L', 'T');Convierte valores numéricos o textuales en grupos fáciles de analizar.
Convert numeric or text values into groups that are easier to analyze.
SELECT
Employee_ID,
Amount,
CASE
WHEN Amount < 100 THEN 'Low'
WHEN Amount BETWEEN 100 AND 999 THEN 'Medium'
WHEN Amount >= 1000 THEN 'High'
ELSE 'Unknown'
END AS Amount_Category
FROM dbo.Transactions;Ejemplo de auditoría para operaciones registradas en horarios poco comunes.
Audit example for operations recorded at unusual times.
SELECT
Transaction_ID,
Employee_ID,
Transaction_DateTime
FROM dbo.Transactions
WHERE DATEPART(HOUR, Transaction_DateTime) < 7
OR DATEPART(HOUR, Transaction_DateTime) >= 19;Permite ver crecimiento, caída o cambios operativos mes a mes.
Shows growth, decline, or operational changes month over month.
WITH monthly AS (
SELECT
DATEFROMPARTS(YEAR(Transaction_Date), MONTH(Transaction_Date), 1) AS Month_Start,
SUM(Amount) AS Total_Amount
FROM dbo.Transactions
GROUP BY YEAR(Transaction_Date), MONTH(Transaction_Date)
)
SELECT
Month_Start,
Total_Amount,
LAG(Total_Amount) OVER (ORDER BY Month_Start) AS Previous_Month_Amount,
Total_Amount - LAG(Total_Amount) OVER (ORDER BY Month_Start) AS Month_Change
FROM monthly;Centraliza reglas de limpieza para que el dashboard consuma una fuente más confiable.
Centralize cleaning rules so the dashboard consumes a more reliable source.
CREATE VIEW reporting.vw_Employee_Clean AS
SELECT
Employee_ID,
LTRIM(RTRIM(Full_Name)) AS Full_Name,
UPPER(LTRIM(RTRIM(Status_Code))) AS Status_Code,
Department_ID,
CAST(Hire_Date AS date) AS Hire_Date
FROM dbo.EmployeeMaster
WHERE Employee_ID IS NOT NULL;Prepara datos antes de cargarlos a una tabla final o proceso ETL.
Prepare data before loading it into a final table or ETL process.
INSERT INTO staging.EmployeeClean (
Employee_ID,
Full_Name,
Email,
Department
)
SELECT
Employee_ID,
LTRIM(RTRIM(Full_Name)),
LOWER(LTRIM(RTRIM(Email))),
LTRIM(RTRIM(Department))
FROM staging.EmployeeRaw
WHERE Employee_ID IS NOT NULL
AND Email LIKE '%@%';Resume problemas principales en una sola salida para revisión técnica o gerencial.
Summarize the main issues in one output for technical or management review.
SELECT
'Missing Email' AS Issue_Type,
COUNT(*) AS Issue_Count
FROM dbo.EmployeeMaster
WHERE Email IS NULL OR LTRIM(RTRIM(Email)) = ''
UNION ALL
SELECT
'Missing Department' AS Issue_Type,
COUNT(*) AS Issue_Count
FROM dbo.EmployeeMaster
WHERE Department IS NULL OR LTRIM(RTRIM(Department)) = ''
UNION ALL
SELECT
'Duplicate Employee ID' AS Issue_Type,
COUNT(*) AS Issue_Count
FROM (
SELECT Employee_ID
FROM dbo.EmployeeMaster
GROUP BY Employee_ID
HAVING COUNT(*) > 1
) d;