Claude Skill

sql-ops

Quick reference for common SQL patterns, CTEs, window functions, and indexing strategies. Triggers on: sql patterns, cte example, window functions, sql join, index strategy, pagination sql.

LLM Mart · 0 points · 1 views 0 listing impressions 0 install-command copies
Download 0xdarkmatter-claude-mods-skills_sql-ops-3dfaf0b.zip · 5 KB

Install

skills CLI npx skills add https://github.com/0xDarkMatter/claude-mods/tree/main/skills/sql-ops
Claude Code claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install 0xdarkmatter-claude-mods@llmmart
Git git clone https://github.com/0xDarkMatter/claude-mods.git

The skills CLI installs just this skill, for any of its supported agents. Claude Code installs the whole 0xdarkmatter/claude-mods collection as a plugin from our marketplace. Git is the plain clone.

Skill manifest

SQL Patterns

Quick reference for common SQL patterns.

CTE (Common Table Expressions)

WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';

Chained CTEs

WITH
    active_users AS (
        SELECT id, name FROM users WHERE status = 'active'
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) as order_count
        FROM orders GROUP BY user_id
    )
SELECT u.name, COALESCE(o.order_count, 0) as orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

Window Functions (Quick Reference)

Function Use
ROW_NUMBER() Unique sequential numbering
RANK() Rank with gaps (1, 2, 2, 4)
DENSE_RANK() Rank without gaps (1, 2, 2, 3)
LAG(col, n) Previous row value
LEAD(col, n) Next row value
SUM() OVER Running total
AVG() OVER Moving average
SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
    SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_sales;

JOIN Reference

Type Returns
INNER JOIN Only matching rows
LEFT JOIN All left + matching right
RIGHT JOIN All right + matching left
FULL JOIN All rows, NULL where no match

Pagination

-- OFFSET/LIMIT (simple, slow for large offsets)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;

-- Keyset (fast, scalable)
SELECT * FROM products WHERE id > 42 ORDER BY id LIMIT 20;

Transaction Isolation Levels

Level Dirty Read Non-Repeatable Read Phantom Read Use
READ UNCOMMITTED Possible Possible Possible Rarely (PostgreSQL treats as READ COMMITTED)
READ COMMITTED No Possible Possible Default in most databases
REPEATABLE READ No No Possible* Consistent multi-statement reads
SERIALIZABLE No No No Critical invariants (retry on serialization failure)

*PostgreSQL's REPEATABLE READ also prevents phantoms via snapshot isolation.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... critical operations ...
COMMIT;  -- be prepared to retry on serialization failure

Keep the default READ COMMITTED globally; raise the level per-transaction only where the logic requires it.

Index Quick Reference

Index Type Best For
B-tree Range queries, ORDER BY
Hash Exact equality only
GIN Arrays, JSONB, full-text
Covering Avoid table lookup

Anti-Patterns

Mistake Fix
SELECT * List columns explicitly
WHERE YEAR(date) = 2024 WHERE date >= '2024-01-01'
NOT IN with NULLs Use NOT EXISTS
N+1 queries Use JOIN or batch

Additional Resources

For detailed patterns, load:

  • ./references/window-functions.md - Complete window function patterns
  • ./references/indexing-strategies.md - Index types, covering indexes, optimization
Files (claude-mods)
  • assets
    • .gitkeep 0 B · in bundle
  • references
    • indexing-strategies.md 3.9 KB
      Loading…
    • window-functions.md 6.9 KB
      Loading…
  • scripts
    • .gitkeep 0 B · in bundle
  • SKILL.md 3.4 KB
    ---
    name: sql-ops
    description: "Quick reference for common SQL patterns, CTEs, window functions, and indexing strategies. Triggers on: sql patterns, cte example, window functions, sql join, index strategy, pagination sql."
    license: MIT
    allowed-tools: "Read Write"
    metadata:
      author: claude-mods
      related-skills: postgres-ops, sqlite-ops
    ---
    
    # SQL Patterns
    
    Quick reference for common SQL patterns.
    
    ## CTE (Common Table Expressions)
    
    ```sql
    WITH active_users AS (
        SELECT id, name, email
        FROM users
        WHERE status = 'active'
    )
    SELECT * FROM active_users WHERE created_at > '2024-01-01';
    ```
    
    ### Chained CTEs
    
    ```sql
    WITH
        active_users AS (
            SELECT id, name FROM users WHERE status = 'active'
        ),
        user_orders AS (
            SELECT user_id, COUNT(*) as order_count
            FROM orders GROUP BY user_id
        )
    SELECT u.name, COALESCE(o.order_count, 0) as orders
    FROM active_users u
    LEFT JOIN user_orders o ON u.id = o.user_id;
    ```
    
    ## Window Functions (Quick Reference)
    
    | Function | Use |
    |----------|-----|
    | `ROW_NUMBER()` | Unique sequential numbering |
    | `RANK()` | Rank with gaps (1, 2, 2, 4) |
    | `DENSE_RANK()` | Rank without gaps (1, 2, 2, 3) |
    | `LAG(col, n)` | Previous row value |
    | `LEAD(col, n)` | Next row value |
    | `SUM() OVER` | Running total |
    | `AVG() OVER` | Moving average |
    
    ```sql
    SELECT
        date,
        revenue,
        LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
        SUM(revenue) OVER (ORDER BY date) as running_total
    FROM daily_sales;
    ```
    
    ## JOIN Reference
    
    | Type | Returns |
    |------|---------|
    | `INNER JOIN` | Only matching rows |
    | `LEFT JOIN` | All left + matching right |
    | `RIGHT JOIN` | All right + matching left |
    | `FULL JOIN` | All rows, NULL where no match |
    
    ## Pagination
    
    ```sql
    -- OFFSET/LIMIT (simple, slow for large offsets)
    SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;
    
    -- Keyset (fast, scalable)
    SELECT * FROM products WHERE id > 42 ORDER BY id LIMIT 20;
    ```
    
    ## Transaction Isolation Levels
    
    | Level | Dirty Read | Non-Repeatable Read | Phantom Read | Use |
    |-------|-----------|--------------------|--------------|-----|
    | `READ UNCOMMITTED` | Possible | Possible | Possible | Rarely (PostgreSQL treats as READ COMMITTED) |
    | `READ COMMITTED` | No | Possible | Possible | Default in most databases |
    | `REPEATABLE READ` | No | No | Possible* | Consistent multi-statement reads |
    | `SERIALIZABLE` | No | No | No | Critical invariants (retry on serialization failure) |
    
    *PostgreSQL's REPEATABLE READ also prevents phantoms via snapshot isolation.
    
    ```sql
    BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    -- ... critical operations ...
    COMMIT;  -- be prepared to retry on serialization failure
    ```
    
    Keep the default `READ COMMITTED` globally; raise the level per-transaction only where the logic requires it.
    
    ## Index Quick Reference
    
    | Index Type | Best For |
    |------------|----------|
    | B-tree | Range queries, ORDER BY |
    | Hash | Exact equality only |
    | GIN | Arrays, JSONB, full-text |
    | Covering | Avoid table lookup |
    
    ## Anti-Patterns
    
    | Mistake | Fix |
    |---------|-----|
    | `SELECT *` | List columns explicitly |
    | `WHERE YEAR(date) = 2024` | `WHERE date >= '2024-01-01'` |
    | `NOT IN` with NULLs | Use `NOT EXISTS` |
    | N+1 queries | Use JOIN or batch |
    
    ## Additional Resources
    
    For detailed patterns, load:
    - `./references/window-functions.md` - Complete window function patterns
    - `./references/indexing-strategies.md` - Index types, covering indexes, optimization
    

Comments (0)

Sign in to join the conversation.

No comments yet.

Reviews (0)

No reviews yet.