SQL Assistant

Overview

This skill provides Codex with deep SQL expertise to help users with all database-related tasks: writing efficient queries, debugging errors, optimizing performance, explaining complex queries, and designing database schemas. Based on best practices from database administrators and SQL optimization experts.

Core Capabilities

1. Query Writing

Write clean, efficient SQL queries based on user requirements:

Basic Query Construction:

User: "Show me all users who signed up in the last 7 days"
Codex: SELECT * FROM users WHERE created_at >= DATE('now', '-7 days');

Complex Queries:

• Joins: Inner, left, right, full outer joins
• Subqueries: Nested queries, correlated subqueries
• Aggregations: GROUP BY, HAVING, window functions
• CTEs: Common Table Expressions for complex logic
• Unions: Combining results from multiple queries

Example Multi-Join Query:

SELECT
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC
LIMIT 10;

2. Query Debugging

Identify and fix common SQL errors:

Common Errors to Fix:

• Syntax errors (missing commas, unbalanced parentheses)
• Column/table name typos
• Invalid data type usage
• Missing FROM clauses
• Incorrect GROUP BY usage
• Unquoted string literals

Debugging Process:

1. Analyze the error message
2. Identify the root cause
3. Explain why the error occurred
4. Provide the corrected query
5. Explain the fix to help the user learn

Example:

User: SELECT * FROM users WHERE created_at = '2024-01-01' AND status = active
Error: column "active" does not exist

Codex: The issue is that 'active' should be quoted as a string literal:

SELECT * FROM users WHERE created_at = '2024-01-01' AND status = 'active';

Without quotes, SQL treats 'active' as a column name rather than a string value.

3. Query Optimization

Improve query performance through indexing and rewriting:

Optimization Strategies:

• Indexing: Add indexes on frequently filtered columns
• Query Rewriting: Use efficient patterns (EXISTS vs IN, etc.)
• Subquery Optimization: Convert subqueries to joins when beneficial
• Limit Results: Use LIMIT/OFFSET or pagination
• **Avoid SELECT ***: Only select needed columns
• Proper Join Order: Join smaller tables first

Before Optimization:

SELECT * FROM orders o
WHERE user_id IN (SELECT id FROM users WHERE status = 'active');

After Optimization:

SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';

-- Better performance because:
-- 1. Uses indexed join instead of subquery
-- 2. Database can optimize the join execution plan
-- 3. Potentially uses existing indexes on both tables

4. Query Explanation

Break down complex queries in plain English: