Sql Development

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