SQL Development

SQL Development

Purpose

Design efficient database schemas, write optimized SQL queries, and manage data operations following database best practices and performance patterns.

When to Use

• Designing database schemas
• Writing data queries
• Optimizing slow queries
• Creating database migrations
• Managing data relationships

Key Capabilities

1. Schema Design - Create normalized, efficient table structures
2. Query Optimization - Write performant SELECT, INSERT, UPDATE, DELETE
3. Index Strategy - Design indexes for query performance

Approach

1. Design schema following normalization principles
2. Define primary keys, foreign keys, and constraints
3. Write queries using proper JOINs and WHERE clauses
4. Create indexes for frequently queried columns
5. Use EXPLAIN to analyze query performance
6. Test with realistic data volumes

Example

Context: Task management database

-- Schema Design
CREATE TABLE tasks (
    id VARCHAR(50) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    assigned_agent VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL,
    priority VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    INDEX idx_status (status),
    INDEX idx_agent_status (assigned_agent, status),
    INDEX idx_created (created_at)
);

-- Optimized Query
SELECT t.id, t.title, t.status, a.name as agent_name
FROM tasks t
JOIN agents a ON t.assigned_agent = a.agent_file
WHERE t.status IN ('pending', 'active')
  AND t.priority = 'high'
ORDER BY t.created_at DESC
LIMIT 10;

Best Practices

• ✅ Use indexes on frequently queried columns
• ✅ Avoid SELECT * - specify needed columns
• ✅ Use prepared statements to prevent SQL injection
• ✅ Normalize data to reduce redundancy
• ❌ Avoid: N+1 query problems
• ❌ Avoid: Missing WHERE clause on large tables