sql-query-assistant

SQL Query Assistant

Overview

This skill provides guidance for writing, optimizing, and debugging SQL queries in enterprise environments. It covers best practices for performance, query patterns, and database-specific considerations. Based on SQL certification standards and database performance tuning best practices.

When to Use

Use this skill when:

• Writing complex SQL queries for reporting and analytics
• Optimizing slow database queries (performance tuning)
• Debugging query performance issues with EXPLAIN plans
• Designing database schemas and indexes
• Working with PostgreSQL, MySQL, SQL Server, or SQLite
• Need to understand query execution plans and optimization
• Implementing window functions for complex calculations
• Creating CTEs for readable multi-step queries
• Building efficient JOIN strategies
• Implementing pagination for large datasets
• Writing subqueries and correlated subqueries
• Optimizing aggregate functions and GROUP BY clauses
• Implementing database-specific optimizations
• Setting up database indexes for query optimization
• Creating materialized views for performance
• Implementing query caching strategies
• Debugging deadlocks and locking issues
• Writing efficient batch processing queries
• Implementing data warehouse star schemas
• Building ETL pipelines with SQL
• Creating database views for complex joins
• Implementing recursive queries for hierarchical data
• Optimizing full-text search queries
• Setting up database partitioning strategies

Do NOT use this skill when:

• Simple CRUD operations that can use an ORM
• Using an ORM for basic queries (use ORM-specific skill)
• Database administration tasks (backup, restore, user management)
• Database migration scripting (use database migration skill)
• NoSQL database queries (use NoSQL-specific skill)

Core Patterns

Query Optimization Checklist

-- 1. Use EXPLAIN to analyze query plan
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;

-- 2. Add indexes for frequently queried columns
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 3. Avoid SELECT *
SELECT id, name, email FROM users WHERE active = true;

-- 4. Use JOINs instead of subqueries when possible
SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;

-- 5. Limit result sets
SELECT * FROM orders WHERE created_at > '2024-01-01' LIMIT 100;