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

sqlCopy code
-- 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;

Common Query Patterns

sqlCopy code
-- Pagination with offset
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 50 OFFSET 0;

-- Window functions
SELECT
  name,
  salary,
  RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- CTE for complex queries
WITH sales_by_region AS (
  SELECT region, SUM(amount) as total_sales
  FROM sales
  GROUP BY region
)
SELECT region, total_sales
FROM sales_by_region
WHERE total_sales > 10000;

Database-Specific Considerations

PostgreSQL

• Use EXPLAIN (ANALYZE, BUFFERS) for detailed analysis
• Consider partial indexes for filtered queries
• Use COPY for bulk imports
• Enable pg_stat_statements for query monitoring

MySQL

• Use EXPLAIN FORMAT=JSON for detailed analysis
• Consider index merge optimization
• Use INSERT DELAYED for non-critical inserts
• Enable slow query log

SQL Server

• Use SET SHOWPLAN_ALL ON for execution plan
• Consider filtered indexes
• Use BULK INSERT for bulk operations
• Monitor with DMVs

Common Mistakes

❌ Bad: N+1 Query Problem

sqlCopy code
-- Get users and their orders
SELECT * FROM users;  -- Query 1
-- Then for each user:
SELECT * FROM orders WHERE user_id = ?;  -- Query N

✅ Good: JOIN or IN clause

sqlCopy code
-- Single query with JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Or use IN with subquery
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users);

❌ Bad: Using Functions in WHERE Clause

sqlCopy code
-- Prevents index usage
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

✅ Good: Pre-process value

sqlCopy code
-- Allows index usage
SELECT * FROM users WHERE email = 'test@example.com';
-- Application converts to lowercase before query

Real-World Impact

Before this skill:

• Queries taking 10+ seconds
• No understanding of execution plans
• N+1 query patterns
• Missing indexes

After this skill:

• Queries under 100ms
• Understanding of query plans
• Proper JOIN usage
• Strategic indexing

SQL Formatting Standards

All SQL queries MUST follow this format:

sqlCopy code
-- ✅ CORRECT: Uppercase keywords, one column per line, indented JOINs
SELECT
    p.id,
    p.name,
    p.email,
    r.name AS role_name
FROM
    users p
    INNER JOIN roles r ON p.role_id = r.id
WHERE
    p.status = 'active'
    AND p.deleted_at IS NULL
ORDER BY
    p.created_at DESC
LIMIT 20 OFFSET 0;

sqlCopy code
-- ❌ WRONG: Lowercase, everything on one line, inline JOINs
select p.id, p.name, p.email, r.name as role_name from users p inner join roles r on p.role_id = r.id where p.status = 'active' and p.deleted_at is null order by p.created_at desc limit 20 offset 0;

Rules:

• Keywords: UPPERCASE (SELECT, FROM, WHERE, JOIN, AND, OR, ORDER BY, etc.)
• Columns: One per line with trailing comma
• JOINs: Indented under FROM clause
• WHERE conditions: One per line, AND/OR at start of line
• Always use parameterized queries (never string concatenation)

Cross-References

• designing-architecture - For database architecture decisions
• analyzing-projects - For database schema analysis
• tdd-workflow - For database testing strategies

References

• PostgreSQL Performance Tips
• SQL Performance Explained
• Use The Index, Luke