sql-pro-skill_skill

Overview

This skill provides expert SQL development, database design, and performance tuning across PostgreSQL, MySQL, SQL Server, and Oracle. It focuses on writing complex queries, optimizing execution plans, and designing scalable schemas to meet OLTP and analytics requirements. Use it to translate business requirements into efficient SQL, implement stored logic, and guide cross-platform migrations.

How this skill works

I inspect SQL requirements, analyze execution plans, and recommend changes to schema, indexes, and queries. I apply ANSI SQL best practices and platform-specific optimizations, produce rewritten queries, and propose indexing and statistics strategies. When needed, I map dialect differences for migrations and create step-by-step tuning actions with measurable goals.

When to use it

• Writing complex queries with CTEs, window functions, recursive logic, or advanced aggregations.
• Designing or refactoring database schemas for performance, normalization, or scalability.
• Diagnosing and optimizing slow queries using EXPLAIN / EXPLAIN ANALYZE and execution plans.
• Migrating data and SQL logic between different database platforms (e.g., MySQL → PostgreSQL).
• Implementing stored procedures, functions, triggers, or platform-specific features.
• Building analytical reports requiring ranking, running totals, pivoting, or time-series queries.

Best practices

• Prefer SELECT of only required columns and use explicit table aliases to improve clarity and planning.
• Use CTEs for readability or reuse; prefer JOINs for simple lookups and performance-critical paths.
• Choose window functions for row-level aggregates and GROUP BY for pure aggregation results.
• Design covering and composite indexes aligned with query WHERE, JOIN, and ORDER BY patterns.
• Review EXPLAIN plans for sequential scans, costly joins, and missing index usage before changing code.
• Parameterize queries to avoid injection and ensure proper type usage to prevent implicit conversions.

Example use cases

• Refactor a slow reporting query using window functions to replace multiple self-joins and reduce runtime.
• Design a new transactional schema with appropriate primary keys, foreign keys, and composite indexes.
• Tune a high-traffic OLTP query to meet <100ms service-level targets by adding covering indexes and rewriting joins.
• Convert stored procedures and platform-specific SQL from Oracle to PostgreSQL-safe equivalents.
• Resolve cross-platform compatibility issues in migrations and provide a migration plan with fallback steps.