Qsql-pro

SQL Pro

Core Workflow

1. Schema Analysis - Review database structure, indexes, query patterns, performance bottlenecks
2. Design - Create set-based operations using CTEs, window functions, appropriate joins
3. Optimize - Analyze execution plans, implement covering indexes, eliminate table scans
4. Verify - Run EXPLAIN ANALYZE and confirm no sequential scans on large tables; if query does not meet sub-100ms target, iterate on index selection or query rewrite before proceeding
5. Document - Provide query explanations, index rationale, performance metrics

Reference Guide

Load detailed guidance based on context:

Topic	Reference	Load When
Query Patterns	references/query-patterns.md	JOINs, CTEs, subqueries, recursive queries
Window Functions	references/window-functions.md	ROW_NUMBER, RANK, LAG/LEAD, analytics
Optimization	references/optimization.md	EXPLAIN plans, indexes, statistics, tuning
Database Design	references/database-design.md	Normalization, keys, constraints, schemas
Dialect Differences	references/dialect-differences.md	PostgreSQL vs MySQL vs SQL Server specifics

Code Patterns

Basic: SELECT with JOIN

-- Purpose: Retrieve customer orders with inline total calculation
-- Parameters: order_date must be parameterized (?1) to prevent SQL injection
-- Expected output: customer_name, order_id, total_amount (sum of line items)
SELECT 
    c.name AS customer_name,
    o.order_id,
    SUM(li.quantity * li.unit_price) AS total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN line_items li ON o.order_id = li.order_id
WHERE o.order_date > ?1
GROUP BY c.customer_id, c.name, o.order_id;

Error Handling: Transaction with SAVEPOINT

-- Purpose: Safely process fund transfers with rollback on constraint violation
-- Rollback: ROLLBACK TO sp_transfer; handles duplicate key or insufficient funds
BEGIN TRANSACTION;
    SAVEPOINT sp_transfer;
    UPDATE accounts SET balance = balance - ?1 WHERE account_id = ?2;
    UPDATE accounts SET balance = balance + ?1 WHERE account_id = ?3;
    
    -- Constraint violation triggers automatic rollback to savepoint
    IF @@ERROR <> 0 ROLLBACK TRANSACTION;
COMMIT TRANSACTION;