SQL Queries - Documentation

Overview

This section contains SQL queries for data extraction and analysis in the Procesar system.

Available Query Categories

Data Validation Queries

• CURP Validation: Verify CURP format and existence
• Email Validation: Check email format and domain
• Phone Number Validation: Validate phone number formats
• Date Range Validation: Ensure dates are within allowed ranges

Data Extraction Queries

• User Information: Extract user profile data
• Transaction History: Retrieve transaction records
• Account Balance: Query account balances and status
• Audit Logs: Extract system access logs

Reporting Queries

• Daily Reports: Generate daily operational reports
• Monthly Summaries: Create monthly activity summaries
• Performance Metrics: Calculate system performance indicators
• Error Analysis: Identify and categorize system errors

Query Examples

CURP Validation

-- Validate CURP format
SELECT 
  CASE 
    WHEN LENGTH(curp) = 18 
      AND curp REGEXP '^[A-Z]{4}[0-9]{6}[HM][A-Z]{5}[0-2][A-Z][0-9]$'
    THEN 1
    WHEN LENGTH(curp) != 18 
      THEN 0
  END AS is_valid_curp;

-- Check if CURP exists in database
SELECT COUNT(*) AS curp_count
FROM usuarios
WHERE curp = :curp_value;

User Information

-- Get complete user profile
SELECT 
  u.id,
  u.nombre,
  u.apellido_paterno,
  u.apellido_materno,
  u.curp,
  u.email,
  u.telefono,
  u.fecha_registro
FROM usuarios u
WHERE u.id = :user_id;

Transaction History

-- Get last 10 transactions
SELECT 
  t.id,
  t.tipo_transaccion,
  t.monto,
  t.fecha,
  t.descripcion
FROM transacciones t
WHERE t.id_usuario = :user_id
ORDER BY t.fecha DESC
LIMIT 10;

Best Practices

Query Optimization

• Use Indexes: Create appropriate indexes for frequently queried columns
• Limit Results: Use LIMIT clauses to prevent excessive data retrieval
• Parameterized Queries: Use prepared statements to prevent SQL injection
• Explain Plans: Use EXPLAIN to analyze query execution plans

Security Considerations

• Input Validation: Always validate and sanitize input parameters
• Least Privilege: Use accounts with minimum required permissions
• Audit Logging: Log all query executions for security monitoring
• Connection Security: Use encrypted connections for all database access

Support

For SQL script issues:

• Database Team: Contact database administrators
• Development Team: Consult with application developers
• Documentation: Review specific query examples and procedures