writing-sql

What this skill does

# Writing SQL

## Overview

All SQL in this project follows a strict vertical formatting style: every top-level keyword on its own line, everything beneath it indented.

**No exceptions for short queries.** A one-column, one-table, one-condition query gets the same formatting as a 10-join monster.

## Rules

### Keyword Casing

All SQL keywords UPPERCASE: `SELECT`, `FROM`, `WHERE`, `JOIN`, `LEFT JOIN`, `RIGHT JOIN`, `CROSS JOIN`, `ON`, `AND`, `OR`, `NOT`, `IN`, `EXISTS`, `BETWEEN`, `LIKE`, `IS NULL`, `IS NOT NULL`, `INSERT INTO`, `VALUES`, `UPDATE`, `SET`, `DELETE FROM`, `ORDER BY`, `GROUP BY`, `HAVING`, `LIMIT`, `OFFSET`, `AS`, `CASE`, `WHEN`, `THEN`, `ELSE`, `END`, `UNION`, `UNION ALL`, `EXCEPT`, `INTERSECT`, `WITH`, `RECURSIVE`, `OVER`, `PARTITION BY`, `ROWS`, `RANGE`, `ON DUPLICATE KEY UPDATE`, `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, `ADD COLUMN`, `DROP COLUMN`, `MODIFY COLUMN`, `DEFAULT`, `NOT NULL`, `PRIMARY KEY`, `FOREIGN KEY`, `REFERENCES`, `AUTO_INCREMENT`, `ENGINE`, `CHARSET`, `COLLATE`, `INDEX`, `UNIQUE KEY`, `IF EXISTS`, `IF NOT EXISTS`, `CASCADE`.

### Vertical Layout

Every top-level clause keyword sits **alone on its own line, flush left**. Everything belonging to that clause is indented one level (4 spaces) below it.

Top-level clause keywords: `SELECT`, `FROM`, `WHERE`, `ORDER BY`, `GROUP BY`, `HAVING`, `LIMIT`, `SET`, `VALUES`, `ON DUPLICATE KEY UPDATE`.

### Columns

One column per line, indented, trailing commas:

```sql
SELECT
    a.name,
    a.weight_kg,
    a.birth_date
```

### FROM and JOINs

JOINs are indented under `FROM`. `ON` is indented under the JOIN. Use bare `JOIN` for inner joins (the `INNER` keyword is redundant). Use explicit `LEFT JOIN` or `RIGHT JOIN` when an outer join is needed:

```sql
FROM
    animals a
    JOIN species s
        ON a.species_id = s.id
    LEFT JOIN habitats h
        ON a.habitat_id = h.id
```

Multi-condition JOINs — each condition on its own line with `AND` leading:

```sql
FROM
    animals a
    JOIN observations o
        ON a.id = o.animal_id
        AND o.observed_at >= :start_date
        AND o.status = 'confirmed'
```

### WHERE

First condition indented. Continuation lines at the same indent with `AND`/`OR` leading:

```sql
WHERE
    s.class = :class
    AND h.region = :region
    AND a.released_at IS NULL
```

### ORDER BY / GROUP BY

```sql
ORDER BY
    a.name ASC
GROUP BY
    s.id,
    h.name
```

### LIMIT / OFFSET

```sql
LIMIT
    :limit
OFFSET
    :offset
```

---

## Complex Patterns

### Subqueries

Subqueries are indented one level inside their parentheses. The opening paren sits on the line that introduces the subquery. The closing paren aligns with the start of that line:

**Subquery in WHERE:**

```sql
SELECT
    a.name,
    a.weight_kg
FROM
    animals a
WHERE
    a.species_id IN (
        SELECT
            s.id
        FROM
            species s
        WHERE
            s.class = :class
    )
```

**Subquery in FROM (derived table):**

```sql
SELECT
    top_species.common_name,
    top_species.animal_count
FROM
    (
        SELECT
            s.common_name,
            COUNT(a.id) AS animal_count
        FROM
            species s
            JOIN animals a
                ON s.id = a.species_id
        GROUP BY
            s.id,
            s.common_name
        HAVING
            COUNT(a.id) > :min_count
    ) AS top_species
ORDER BY
    top_species.animal_count DESC
```

**Scalar subquery in SELECT:**

```sql
SELECT
    a.name,
    (
        SELECT
            COUNT(*)
        FROM
            sightings s
        WHERE
            s.animal_id = a.id
    ) AS sighting_count
FROM
    animals a
```

### Common Table Expressions (CTEs)

`WITH` is flush left. Each CTE name and `AS` sit on the same line. The CTE body is indented inside parentheses. Separate multiple CTEs with a comma after the closing paren:

```sql
WITH recent_sightings AS (
    SELECT
        s.animal_id,
        s.location,
        s.observed_at
    FROM
        sightings s
    WHERE
        s.observed_at >= :since
),
animal_counts AS (
    SELECT
        rs.animal_id,
        COUNT(*) AS sighting_count
    FROM
        recent_sightings rs
    GROUP BY
        rs.animal_id
)
SELECT
    a.name,
    ac.sighting_count,
    rs.location AS last_location
FROM
    animal_counts ac
    JOIN animals a
        ON ac.animal_id = a.id
    JOIN recent_sightings rs
        ON a.id = rs.animal_id
ORDER BY
    ac.sighting_count DESC
```

### CASE Expressions

`CASE` starts on the column line. `WHEN`, `THEN`, `ELSE`, and `END` are each indented one level under `CASE`:

```sql
SELECT
    a.name,
    CASE
        WHEN a.weight_kg > 1000 THEN 'large'
        WHEN a.weight_kg > 100 THEN 'medium'
        ELSE 'small'
    END AS size_category,
    a.birth_date
FROM
    animals a
```

### Window Functions

The `OVER` clause stays on the same line as the function. `PARTITION BY` and `ORDER BY` inside the window are each on their own indented line:

```sql
SELECT
    a.name,
    a.weight_kg,
    ROW_NUMBER() OVER (
        PARTITION BY
            a.species_id
        ORDER BY
            a.weight_kg DESC
    ) AS weight_rank,
    AVG(a.weight_kg) OVER (
        PARTITION BY
            a.species_id
    ) AS avg_species_weight
FROM
    animals a
```

### UNION / UNION ALL / EXCEPT / INTERSECT

Set operators sit flush left on their own line, with a blank line above and below for readability:

```sql
SELECT
    a.name,
    'resident' AS status
FROM
    animals a
WHERE
    a.released_at IS NULL

UNION ALL

SELECT
    a.name,
    'released' AS status
FROM
    animals a
WHERE
    a.released_at IS NOT NULL
ORDER BY
    name ASC
```

### EXISTS

```sql
SELECT
    s.common_name
FROM
    species s
WHERE
    EXISTS (
        SELECT
            1
        FROM
            animals a
        WHERE
            a.species_id = s.id
            AND a.weight_kg > :min_weight
    )
```

---

## DML Statements

### INSERT

```sql
INSERT INTO sightings (
    animal_id,
    location,
    observed_at
)
VALUES (
    :animal_id,
    :location,
    :observed_at
)
ON DUPLICATE KEY UPDATE
    location = new.location,
    observed_at = new.observed_at
```

**INSERT … SELECT:**

```sql
INSERT INTO archive_sightings (
    animal_id,
    location,
    observed_at
)
SELECT
    s.animal_id,
    s.location,
    s.observed_at
FROM
    sightings s
WHERE
    s.observed_at < :cutoff_date
```

### UPDATE

```sql
UPDATE
    animals
SET
    habitat_id = :habitat_id
WHERE
    id = :id
```

### DELETE

```sql
DELETE FROM
    sightings
WHERE
    animal_id = :animal_id
    AND observed_at = :observed_at
```

---

## DDL Statements

### CREATE TABLE (migrations)

```sql
CREATE TABLE IF NOT EXISTS animals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    species_id BIGINT UNSIGNED NOT NULL,
    habitat_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) DEFAULT NULL,
    weight_kg DECIMAL(10, 2) DEFAULT NULL,
    birth_date DATE DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_species_id (species_id),
    INDEX idx_habitat_id (habitat_id),
    FOREIGN KEY (species_id) REFERENCES species (id) ON DELETE CASCADE,
    FOREIGN KEY (habitat_id) REFERENCES habitats (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### ALTER TABLE

```sql
ALTER TABLE animals
    ADD COLUMN tag_number VARCHAR(50) DEFAULT NULL AFTER name,
    ADD INDEX idx_tag_number (tag_number);
```

### DROP TABLE

```sql
DROP TABLE IF EXISTS archive_sightings;
```

---

## SQL in PHP

### General Rule

When SQL is inside a quoted string, the opening and closing quotes go on their own lines. The SQL starts on the next line, indented one level from the quote. The closing quote sits at the same indent as the opening quote.

The SQL's "flush left" is one indent level in from the quote. Top-level clause keywords align there, and