SQL Expert Skill

Expert guidance for writing, optimizing, and managing SQL databases across PostgreSQL, MySQL, SQLite, and SQL Server.

Core Capabilities

This skill enables you to:

• Write complex SQL queries with JOINs, subqueries, CTEs, and window functions
• Optimize slow queries using EXPLAIN plans and index recommendations
• Design database schemas with proper normalization (1NF, 2NF, 3NF, BCNF)
• Create effective indexes for query performance
• Write database migrations safely with rollback support
• Debug SQL errors and understand error messages
• Handle transactions with proper isolation levels
• Work with JSON/JSONB data types
• Generate sample data for testing
• Convert between database dialects (PostgreSQL ↔ MySQL ↔ SQLite)

---

Supported Database Systems

PostgreSQL

Best for: Complex queries, JSON data, advanced features, ACID compliance

pip install psycopg2-binary sqlalchemy

MySQL/MariaDB

Best for: Web applications, WordPress, high-read workloads

pip install mysql-connector-python sqlalchemy

SQLite

Best for: Local development, embedded databases, testing

pip install sqlite3  # Built into Python

SQL Server

Best for: Enterprise applications, Windows environments

pip install pyodbc sqlalchemy

---

Query Writing

Basic SELECT with JOINs

-- Simple SELECT with filtering
SELECT
    column1,
    column2,
    column3
FROM
    table_name
WHERE
    condition = 'value'
    AND another_condition > 100
ORDER BY
    column1 DESC
LIMIT 10;

-- INNER JOIN
SELECT
    users.name,
    orders.order_date,
    orders.total_amount
FROM
    users
INNER JOIN
    orders ON users.id = orders.user_id
WHERE
    orders.status = 'completed';

-- LEFT JOIN (include all users, even without orders)
SELECT
    users.name,
    COUNT(orders.id) as order_count,
    COALESCE(SUM(orders.total_amount), 0) as total_spent
FROM
    users
LEFT JOIN
    orders ON users.id = orders.user_id
GROUP BY
    users.id, users.name;

Subqueries and CTEs

-- Subquery in WHERE clause
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Common Table Expression (CTE)
WITH high_value_customers AS (
    SELECT
        user_id,
        SUM(total_amount) as lifetime_value
    FROM orders
    GROUP BY user_id
    HAVING SUM(total_amount) > 1000
)
SELECT
    users.name,
    users.email,
    hvc.lifetime_value
FROM users
INNER JOIN high_value_customers hvc ON users.id = hvc.user_id;

Window Functions

-- Ranking within groups
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM
    employees;

-- Running totals
SELECT
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) as running_total
FROM
    orders;

-- Moving averages
SELECT
    order_date,
    total_amount,
    AVG(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7days
FROM
    daily_sales;

See examples/complex_queries.sql for more advanced query patterns.

---

Query Optimization

Using EXPLAIN

-- Analyze query performance
EXPLAIN ANALYZE
SELECT
    users.name,
    COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

-- Look for:
-- - Seq Scan (bad) vs Index Scan (good)
-- - High cost numbers
-- - Large row counts being processed

Quick Optimization Tips

-- BAD: Function on indexed column
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- GOOD: Keep indexed column clean
SELECT * FROM users WHERE email = LOWER('user@example.com');

-- BAD: SELECT *
SELECT * FROM large_table WHERE id = 123;

-- GOOD: Select only needed columns
SELECT id, name, email FROM large_table WHERE id = 123;

For comprehensive optimization techniques, see references/query-optimization.md.

---

Schema Design

Normalization Principles

First Normal Form (1NF): Eliminate repeating groups, use atomic values

-- GOOD: Separate table for order items
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_name VARCHAR(100)
);

Second Normal Form (2NF): All non-key attributes depend on entire primary key

-- GOOD: Separate product information
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10, 2)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Third Normal Form (3NF): No transitive dependencies

Common Schema Patterns

**On

...