sql

Plugin sql from marketplace sql-skills-marketplace in ctoth/sql-skills-plugin. Commands read from the repo's manifest — 1 month ago.

Skills (32)

• sql-aggregation-and-grouping

  Guides aggregation correctness — every column in the SELECT list of a grouped query must be either named in GROUP BY or wrapped in an aggregate (the functional-dependency rule), because a non-grouped, non-aggregated column has no single value per group. Bans the "ambiguous groups" antipattern that e

• sql-constraints-and-integrity

  Guides database-enforced data integrity — push the rules into the schema as declarative constraints rather than re-checking them in every application that writes. Covers PRIMARY KEY (= UNIQUE + NOT NULL, one per table) vs UNIQUE (many per table, and by default permits multiple NULLs — the SQL:2023 N

• sql-cte-and-recursion

  Guides common table expressions for readable query decomposition (`WITH`) and `WITH RECURSIVE` for hierarchies, transitive closure, and series generation — always with a termination or cycle guard so recursion cannot run away. Auto-invokes when writing or editing `WITH`/`WITH RECURSIVE`, hierarchy/t

• sql-data-modification

  Guides the core write statements — INSERT, UPDATE, DELETE — as set-based operations the database serializes, not row-by-row loops. Teaches the INSERT forms (single-row VALUES, multi-row VALUES in ONE statement, INSERT ... SELECT for bulk copy, DEFAULT VALUES) and bans the nightly job that fires 100k

• sql-data-types-and-numerics

  Guides standard-SQL type selection so the storage matches the data's meaning — exact `NUMERIC`/`DECIMAL(p,s)` for money and counts (never `FLOAT`/`REAL`/`DOUBLE`, whose binary floating point cannot store 0.1 exactly and whose rounding error compounds when summed), `SMALLINT`/`INTEGER`/`BIGINT` chose

• sql-datetime-and-intervals

  Guides standard temporal handling in SQL — the temporal types (DATE, TIME, TIMESTAMP, and crucially TIMESTAMP WITH TIME ZONE), typed literals (DATE '...', TIMESTAMP '...', INTERVAL '...'), EXTRACT, the standard CURRENT_DATE/CURRENT_TIMESTAMP keywords, and INTERVAL arithmetic — instead of jamming dat

• sql-explain-and-set-based-thinking

  Guides the two performance habits that matter most — think in sets, not rows, and measure the plan instead of guessing. Replaces the N+1 / RBAR ("row-by-agonizing-row") antipattern — application code looping to issue one query per row, or a correlated per-row subquery — with a single set-based query

• sql-expressions-case-and-functions

  Guides portable scalar expressions in SQL — use standard `CASE`, `COALESCE`, `NULLIF`, `||` concatenation, and the keyword string functions (`SUBSTRING(... FROM ... FOR ...)`, `TRIM([LEADING|TRAILING|BOTH] c FROM s)`, `POSITION(sub IN s)`, `OVERLAY`, `CHAR_LENGTH`, `UPPER`/`LOWER`), plus `CAST(x AS

• sql-gaps-and-islands

  Guides the canonical "gaps and islands" pattern family — detecting unbroken runs of consecutive values (islands — active streaks, sessions, contiguous date/number ranges) and the missing stretches between them (gaps) — with the portable set-based solution instead of brittle self-joins or procedural

• sql-generated-and-identity-columns

  Guides the two standard SQL ways to let the database derive a column value for you — `GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY` for surrogate keys instead of vendor `SERIAL`/`AUTO_INCREMENT`/`IDENTITY(1,1)`, and `GENERATED ALWAYS AS (expr) STORED|VIRTUAL` computed columns instead of recomputing

• sql-indexing-and-sargability

  Guides portable index design and the sargability rule — an index is a sorted B-tree, so the database can use it only when the predicate leaves the indexed column bare. Bans the recurring index-killers — wrapping an indexed column in a function or expression (`WHERE LOWER(email) = …`, `WHERE DATE(ts)

• sql-injection-and-parameterization

  Guides the one non-negotiable rule for combining SQL with data — pass every value through a bind parameter / prepared statement, and never build SQL by string interpolation, concatenation, f-strings, or `format`. Explains why this beats hand-escaping (OWASP ranks escaping last and "STRONGLY DISCOURA

• sql-joins

  Guides correct join composition and the single most damaging join bug — putting a filter on the null-able side of an outer join in WHERE instead of ON, which silently demotes a LEFT JOIN to an INNER JOIN and drops the very rows you were looking for. Covers INNER/LEFT/RIGHT/FULL/CROSS semantics with

• sql-json

  Guides standard SQL/JSON (SQL:2016 functions, SQL:2023 type) instead of reflexive vendor operators — use the constructors (JSON_OBJECT/JSON_ARRAY/JSON_OBJECTAGG/JSON_ARRAYAGG), and the query functions JSON_VALUE (for a scalar) vs JSON_QUERY (for an object/array) vs JSON_EXISTS (for a path test) rath

• sql-lateral-and-correlated-derived

  Guides LATERAL — a derived table in FROM that may reference columns of preceding FROM items, which a plain subquery cannot. It is the clean, standard answer to top-N-per-group ("latest 3 orders per customer"), per-row set-returning-function expansion, and pulling several correlated values in one pas

• sql-match-recognize

  Guides `MATCH_RECOGNIZE` (SQL:2016 row pattern recognition) — regex-style pattern matching across ordered rows for time-series problems (V-shapes/dip-and-recovery, trend reversals, threshold breaches, complex sessionization) — instead of convoluted self-joins or nested LAG/CASE window gymnastics. Au

• sql-merge-and-upsert

  Guides atomic upsert — never a race-prone "SELECT then INSERT-or-UPDATE" in application code. Teaches the standard `MERGE` statement (`USING ... ON ...` with `WHEN MATCHED THEN UPDATE/DELETE` and `WHEN NOT MATCHED THEN INSERT`) and maps the real-world alternatives — `INSERT ... ON CONFLICT (col) DO

• sql-pagination-and-keyset

  Guides correct, scalable pagination and the row-value machinery behind it. Prefer keyset/seek pagination — `WHERE (sort_key, id) greater than (:last_key, :last_id) ORDER BY sort_key, id FETCH FIRST n ROWS ONLY` — over `LIMIT n OFFSET m`, because `OFFSET` makes the server fetch-and-discard the first

• sql-pattern-matching-and-collation

  Guides correct, portable text matching in SQL — LIKE with its `%` (any sequence) and `_` (any single character) wildcards, the ESCAPE clause for literal `%`/`_`, and the central trap that LIKE's case sensitivity is decided by the active COLLATION and therefore silently differs across engines (case-S

• sql-privileges-and-access-control

  Guides the standard SQL access-control model — `GRANT` adds privileges, `REVOKE` removes them, roles are the grant targets (a role is a user or a group), and access is default-deny so every privilege must be explicitly granted. Drives toward least privilege — the application connects as a dedicated

• sql-property-graph-queries

  Guides SQL:2023 SQL/PGQ (ISO/IEC 9075-16, Part 16) — define a property graph as a metadata overlay over existing relational tables with `CREATE PROPERTY GRAPH ... VERTEX TABLES (...) EDGE TABLES (...)`, then query it with `GRAPH_TABLE (graph MATCH (a)-[e]- greater than (b) WHERE ... COLUMNS (...))`

• sql-relational-and-null-discipline

  Guides the core correctness floor of SQL — query results are unordered sets with no defined order unless you write ORDER BY, NULL means "unknown" and propagates UNKNOWN through every comparison and arithmetic expression, and WHERE/HAVING/ON keep only rows that evaluate to TRUE while CHECK rejects on

• sql-schema-design-and-normalization

  Guides portable relational schema design — normalize to remove the insertion, update, and deletion anomalies (the 1NF→BCNF ladder), choose natural vs surrogate keys deliberately, model many-to-many with a junction table, and denormalize only with a stated reason. Bans the structural antipatterns tha

• sql-select-and-query-processing

  Guides the logical clause-evaluation order of a SELECT — FROM → WHERE → GROUP BY/aggregates → HAVING → SELECT-list/window → DISTINCT → UNION → ORDER BY → OFFSET/FETCH — and why that order, not the written order, decides what each clause can reference. A SELECT-list alias is computed late, so it is i

• sql-set-operations

  Guides SQL set operations and the default-to-UNION cost trap — UNION sorts and de-duplicates its whole combined result (expensive) while UNION ALL just appends, so use UNION ALL whenever duplicates are impossible or wanted. Covers INTERSECT/EXCEPT (native set intersection and difference, so they are

• sql-standard-vs-dialect-map

  The portability index for SQL — maps each standard feature to whether the readable engines (PostgreSQL, SQLite, MySQL/MariaDB, plus notes on SQL Server / Oracle / DuckDB) support it and how they spell it. Covers `OFFSET … FETCH` vs `LIMIT`, `GENERATED AS IDENTITY` vs `SERIAL`/`AUTO_INCREMENT`/`IDENT

• sql-style-and-naming

  Guides readable, reviewable SQL and the one genuine correctness trap in formatting — `'single quotes'` are string literals and `"double quotes"` are delimited identifiers per the SQL standard, so `WHERE name = "John"` references a column named John (error on PostgreSQL, silent misbehavior on MySQL u

• sql-subqueries-and-exists

  Guides correct subquery use and owns the deep dive the foundation defers — the `NOT IN` + NULL trap, where a single NULL anywhere in the list or subquery collapses `NOT IN` to zero rows because `NOT IN` expands to a chain of `not equal to` comparisons AND'd together and the NULL conjunct is forever

• sql-temporal-tables

  Guides SQL:2011 temporal tables — system-versioned tables (`PERIOD FOR SYSTEM_TIME` + `WITH SYSTEM VERSIONING`) that make the engine auto-record a full history so you query past states with `FOR SYSTEM_TIME AS OF / FROM..TO / BETWEEN / ALL` instead of hand-rolling trigger-based history tables, appli

• sql-transactions-and-isolation

  Guides the SQL-statement surface of transactions — wrap any multi-statement invariant or check-then-act sequence in `START TRANSACTION`/`COMMIT`/`ROLLBACK` so it is atomic (all-or-nothing), use `SAVEPOINT`/`ROLLBACK TO` for partial rollback, set the isolation level with `SET TRANSACTION ISOLATION LE

• sql-views-and-introspection

  Guides SQL views and portable schema introspection — a view is a stored query (`CREATE VIEW`), not a table, re-run on every reference; only simple single-table views are automatically updatable while joins/aggregates/DISTINCT/GROUP BY/set-operation views are read-only by default; `WITH [LOCAL|CASCAD

• sql-window-functions

  Guides window functions — the highest-leverage modern-SQL technique and the frame-clause traps LLMs reliably botch. A window function computes across related rows without collapsing them (PARTITION BY is not GROUP BY). The centerpiece — the default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PREC

Similar plugins