| 1 | <!doctype html>
|
| 2 | <html lang="en">
|
| 3 | <head>
|
| 4 | <meta charset="utf-8">
|
| 5 | <meta name="viewport" content="width=device-width,initial-scale=1">
|
| 6 | <title>SQL Mega Cheat Sheet</title>
|
| 7 | <style>
|
| 8 | :root{--bg:#07100e;--panel:#0d1816;--panel2:#10201d;--text:#eff8f5;--muted:#9fb5ae;--line:#213c36;--accent:#4de3b0;--accent2:#8cf0cf;--cyan:#5bbcff;--warn:#ffd166;--shadow:0 16px 42px rgba(0,0,0,.34)}
|
| 9 | *{box-sizing:border-box}html{scroll-behavior:smooth}
|
| 10 | body{margin:0;background:radial-gradient(circle at 12% 0,rgba(77,227,176,.12),transparent 30rem),radial-gradient(circle at 90% 0,rgba(91,188,255,.08),transparent 30rem),var(--bg);color:var(--text);font-family:Inter,ui-sans-serif,system-ui,-apple-system,BlinkMacSystemFont,"Segoe UI",sans-serif}
|
| 11 | .hero{max-width:1500px;margin:auto;padding:48px 24px 26px}
|
| 12 | .badge{display:inline-block;border:1px solid rgba(77,227,176,.38);background:rgba(77,227,176,.09);color:var(--accent2);padding:7px 10px;border-radius:999px;font:800 12px ui-monospace,monospace;letter-spacing:.08em}
|
| 13 | h1{font-size:clamp(40px,7vw,82px);line-height:.94;letter-spacing:-.055em;margin:18px 0}
|
| 14 | .hero p{max-width:980px;color:var(--muted);font-size:18px;line-height:1.6}
|
| 15 | .stats{display:flex;gap:10px;flex-wrap:wrap;margin-top:22px}
|
| 16 | .stat{background:var(--panel);border:1px solid var(--line);border-radius:14px;padding:11px 15px;box-shadow:var(--shadow)}.stat b{color:var(--accent2);font-family:ui-monospace,monospace}
|
| 17 | .toolbar{position:sticky;top:0;z-index:20;background:rgba(7,16,14,.9);backdrop-filter:blur(16px);border-block:1px solid var(--line)}
|
| 18 | .toolbarin{max-width:1500px;margin:auto;padding:13px 24px;display:flex;gap:10px}
|
| 19 | #search{flex:1;min-width:0;background:#0a1412;border:1px solid #2a4a43;border-radius:12px;color:white;padding:13px 15px;font-size:15px;outline:none}
|
| 20 | #search:focus{border-color:var(--accent)}.toolbtn{border:1px solid #2a4a43;background:#0c1715;color:var(--text);border-radius:11px;padding:11px 13px;font-weight:800;cursor:pointer}
|
| 21 | .note{max-width:1500px;margin:20px auto 0;padding:0 24px}.notebox{border:1px solid rgba(91,188,255,.35);background:rgba(91,188,255,.07);border-radius:14px;padding:13px 15px;color:#cfe8f7;font-size:13px}
|
| 22 | .layout{max-width:1500px;margin:auto;padding:22px 24px 42px;display:grid;grid-template-columns:285px 1fr;gap:22px}
|
| 23 | nav{position:sticky;top:76px;align-self:start;max-height:calc(100vh - 96px);overflow:auto;background:var(--panel);border:1px solid var(--line);border-radius:16px;padding:13px}
|
| 24 | nav strong{display:block;color:var(--accent2);font:800 11px ui-monospace,monospace;padding:5px 9px 9px;letter-spacing:.08em}
|
| 25 | nav a{display:block;color:var(--muted);text-decoration:none;padding:7px 9px;border-radius:8px;font-size:12px}nav a:hover{background:#142521;color:white}
|
| 26 | .section{border:1px solid var(--line);border-radius:18px;overflow:hidden;background:rgba(13,24,22,.95);margin-bottom:17px;box-shadow:var(--shadow)}
|
| 27 | .sectionhead{width:100%;display:grid;grid-template-columns:1fr auto auto;gap:14px;align-items:center;border:0;background:linear-gradient(#14231f,#0d1715);color:white;padding:18px 20px;text-align:left;cursor:pointer;font-size:20px;font-weight:900}
|
| 28 | .sectionhead small{font:700 11px ui-monospace,monospace;color:var(--muted)}.sectionhead b{transition:.15s}.section.collapsed .sectionhead b{transform:rotate(-90deg)}
|
| 29 | .body{padding:15px}.section.collapsed .body{display:none}
|
| 30 | .grid{display:grid;grid-template-columns:repeat(2,minmax(0,1fr));gap:11px}
|
| 31 | .card{background:var(--panel2);border:1px solid #25463d;border-radius:14px;padding:13px;min-width:0}.card:hover{border-color:#3e7062}.card.warning{border-color:rgba(255,209,102,.35)}
|
| 32 | .cmdrow{display:flex;gap:10px;align-items:flex-start}pre{margin:0;flex:1;white-space:pre-wrap;word-break:break-word}
|
| 33 | code{color:var(--accent2);font:800 13px/1.5 ui-monospace,SFMono-Regular,Menlo,Consolas,monospace}
|
| 34 | .copy{border:1px solid rgba(77,227,176,.32);background:#0b1a16;color:var(--accent2);border-radius:8px;padding:5px 8px;font:800 10px ui-monospace,monospace;cursor:pointer}
|
| 35 | .card p{margin:8px 0 0;color:var(--muted);font-size:13px;line-height:1.45}
|
| 36 | .hidden{display:none!important}
|
| 37 | footer{max-width:1500px;margin:auto;padding:0 24px 38px;color:#6c827b;font-size:12px}
|
| 38 | @media(max-width:1000px){.layout{grid-template-columns:1fr}nav{position:static;max-height:none;display:flex;gap:5px;overflow:auto;white-space:nowrap}nav strong{display:none}nav a{display:inline-block}}
|
| 39 | @media(max-width:720px){.grid{grid-template-columns:1fr}.hero,.layout,.toolbarin,.note{padding-left:14px;padding-right:14px}.sectionhead{grid-template-columns:1fr auto}.sectionhead small{display:none}}
|
| 40 | @media print{.toolbar,nav,.copy{display:none!important}body{background:white;color:black}.layout{display:block;max-width:none}.card{background:white;border:1px solid #bbb}code{color:black}.card p{color:#333}}
|
| 41 | </style>
|
| 42 | </head>
|
| 43 | <body>
|
| 44 | <header class="hero">
|
| 45 | <span class="badge">SQL DATABASE MEGA REFERENCE</span>
|
| 46 | <h1>SQL<br>Cheat Sheet</h1>
|
| 47 | <p>A massive searchable, copy-ready SQL reference covering querying, joins, grouping, subqueries, CTEs, window functions, schema design, constraints, indexes, transactions, security, performance, and vendor-specific notes for MySQL, PostgreSQL, SQL Server, and SQLite.</p>
|
| 48 | <div class="stats"><div class="stat"><b>308</b> commands & patterns</div><div class="stat"><b>46</b> sections</div><div class="stat"><b>1</b> standalone HTML file</div></div>
|
| 49 | </header>
|
| 50 |
|
| 51 | <div class="toolbar"><div class="toolbarin">
|
| 52 | <input id="search" type="search" placeholder="Search SQL commands, clauses, functions, database topics, or vendors…">
|
| 53 | <button class="toolbtn" id="expand">Expand All</button>
|
| 54 | <button class="toolbtn" id="collapse">Collapse All</button>
|
| 55 | </div></div>
|
| 56 |
|
| 57 | <div class="note"><div class="notebox"><b>SQL note:</b> SQL syntax varies between database systems. Vendor-specific examples are labeled. Commands marked with ⚠ can delete or overwrite large amounts of data, so verify the target and WHERE clause first.</div></div>
|
| 58 |
|
| 59 | <div class="layout">
|
| 60 | <nav><strong>JUMP TO SECTION</strong><a href="#select-basics">SELECT Basics</a><a href="#where---filtering">WHERE & Filtering</a><a href="#order-by---pagination">ORDER BY & Pagination</a><a href="#insert">INSERT</a><a href="#update">UPDATE</a><a href="#delete">DELETE</a><a href="#create-table">CREATE TABLE</a><a href="#alter-table">ALTER TABLE</a><a href="#drop---truncate">DROP & TRUNCATE</a><a href="#data-types">Data Types</a><a href="#constraints">Constraints</a><a href="#aggregate-functions">Aggregate Functions</a><a href="#group-by---having">GROUP BY & HAVING</a><a href="#inner-join">INNER JOIN</a><a href="#left---right---full-join">LEFT / RIGHT / FULL JOIN</a><a href="#cross---self-join">CROSS & SELF JOIN</a><a href="#subqueries">Subqueries</a><a href="#ctes">CTEs</a><a href="#union---intersect---except">UNION / INTERSECT / EXCEPT</a><a href="#case-expressions">CASE Expressions</a><a href="#null-handling">NULL Handling</a><a href="#string-functions">String Functions</a><a href="#numeric-functions">Numeric Functions</a><a href="#date---time">Date & Time</a><a href="#window-functions">Window Functions</a><a href="#top-n-per-group">Top-N Per Group</a><a href="#views">Views</a><a href="#indexes">Indexes</a><a href="#transactions">Transactions</a><a href="#isolation---locking">Isolation & Locking</a><a href="#keys---relationships">Keys & Relationships</a><a href="#normalization-notes">Normalization Notes</a><a href="#stored-procedures---functions">Stored Procedures & Functions</a><a href="#triggers">Triggers</a><a href="#permissions---security">Permissions & Security</a><a href="#schema---database-management">Schema & Database Management</a><a href="#metadata---introspection">Metadata & Introspection</a><a href="#query-plans---performance">Query Plans & Performance</a><a href="#mysql-notes">MySQL Notes</a><a href="#postgresql-notes">PostgreSQL Notes</a><a href="#sql-server-notes">SQL Server Notes</a><a href="#sqlite-notes">SQLite Notes</a><a href="#common-admin-commands">Common Admin Commands</a><a href="#useful-query-patterns">Useful Query Patterns</a><a href="#common-pitfalls">Common Pitfalls</a><a href="#useful-one-liners">Useful One-Liners</a></nav>
|
| 61 | <main><section class="section" id="select-basics"><button class="sectionhead" aria-expanded="true"><span>SELECT Basics</span><small>10 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="select basics select * from users; return all columns from a table."><div class="cmdrow"><pre><code>SELECT * FROM users;</code></pre><button class="copy" data-copy="SELECT * FROM users;">COPY</button></div><p>Return all columns from a table.</p></article><article class="card" data-search="select basics select id, name from users; return selected columns."><div class="cmdrow"><pre><code>SELECT id, name FROM users;</code></pre><button class="copy" data-copy="SELECT id, name FROM users;">COPY</button></div><p>Return selected columns.</p></article><article class="card" data-search="select basics select distinct city from users; return unique values."><div class="cmdrow"><pre><code>SELECT DISTINCT city FROM users;</code></pre><button class="copy" data-copy="SELECT DISTINCT city FROM users;">COPY</button></div><p>Return unique values.</p></article><article class="card" data-search="select basics select name as customer_name from users; rename an output column."><div class="cmdrow"><pre><code>SELECT name AS customer_name FROM users;</code></pre><button class="copy" data-copy="SELECT name AS customer_name FROM users;">COPY</button></div><p>Rename an output column.</p></article><article class="card" data-search="select basics select * from users limit 10; return the first 10 rows in mysql/postgresql/sqlite."><div class="cmdrow"><pre><code>SELECT * FROM users LIMIT 10;</code></pre><button class="copy" data-copy="SELECT * FROM users LIMIT 10;">COPY</button></div><p>Return the first 10 rows in MySQL/PostgreSQL/SQLite.</p></article><article class="card" data-search="select basics select top 10 * from users; return the first 10 rows in sql server."><div class="cmdrow"><pre><code>SELECT TOP 10 * FROM users;</code></pre><button class="copy" data-copy="SELECT TOP 10 * FROM users;">COPY</button></div><p>Return the first 10 rows in SQL Server.</p></article><article class="card" data-search="select basics select * from users fetch first 10 rows only; ansi-style row limiting on supported databases."><div class="cmdrow"><pre><code>SELECT * FROM users FETCH FIRST 10 ROWS ONLY;</code></pre><button class="copy" data-copy="SELECT * FROM users FETCH FIRST 10 ROWS ONLY;">COPY</button></div><p>ANSI-style row limiting on supported databases.</p></article><article class="card" data-search="select basics select current_date; return the current date."><div class="cmdrow"><pre><code>SELECT CURRENT_DATE;</code></pre><button class="copy" data-copy="SELECT CURRENT_DATE;">COPY</button></div><p>Return the current date.</p></article><article class="card" data-search="select basics select current_timestamp; return the current timestamp."><div class="cmdrow"><pre><code>SELECT CURRENT_TIMESTAMP;</code></pre><button class="copy" data-copy="SELECT CURRENT_TIMESTAMP;">COPY</button></div><p>Return the current timestamp.</p></article><article class="card" data-search="select basics select 2 + 2 as result; evaluate an expression."><div class="cmdrow"><pre><code>SELECT 2 + 2 AS result;</code></pre><button class="copy" data-copy="SELECT 2 + 2 AS result;">COPY</button></div><p>Evaluate an expression.</p></article></div></div></section><section class="section" id="where---filtering"><button class="sectionhead" aria-expanded="true"><span>WHERE & Filtering</span><small>14 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="where & filtering select * from users where age >= 18; filter rows by a comparison."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE age >= 18;</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE age >= 18;">COPY</button></div><p>Filter rows by a comparison.</p></article><article class="card" data-search="where & filtering select * from users where status = 'active'; filter text values."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE status = 'active';</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE status = 'active';">COPY</button></div><p>Filter text values.</p></article><article class="card" data-search="where & filtering select * from users where age between 18 and 30; filter within an inclusive range."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE age BETWEEN 18 AND 30;</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE age BETWEEN 18 AND 30;">COPY</button></div><p>Filter within an inclusive range.</p></article><article class="card" data-search="where & filtering select * from users where city in ('lima','columbus'); filter against a list."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE city IN ('Lima','Columbus');</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE city IN ('Lima','Columbus');">COPY</button></div><p>Filter against a list.</p></article><article class="card" data-search="where & filtering select * from users where city not in ('lima','columbus'); exclude listed values."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE city NOT IN ('Lima','Columbus');</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE city NOT IN ('Lima','Columbus');">COPY</button></div><p>Exclude listed values.</p></article><article class="card" data-search="where & filtering select * from users where email is null; find null values."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE email IS NULL;</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE email IS NULL;">COPY</button></div><p>Find NULL values.</p></article><article class="card" data-search="where & filtering select * from users where email is not null; find non-null values."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE email IS NOT NULL;</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE email IS NOT NULL;">COPY</button></div><p>Find non-NULL values.</p></article><article class="card" data-search="where & filtering select * from users where name like 'a%'; wildcard match beginning with a."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE name LIKE 'A%';</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE name LIKE 'A%';">COPY</button></div><p>Wildcard match beginning with A.</p></article><article class="card" data-search="where & filtering select * from users where name like '%son'; wildcard match ending in son."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE name LIKE '%son';</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE name LIKE '%son';">COPY</button></div><p>Wildcard match ending in son.</p></article><article class="card" data-search="where & filtering select * from users where name like '%tech%'; wildcard match containing text."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE name LIKE '%tech%';</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE name LIKE '%tech%';">COPY</button></div><p>Wildcard match containing text.</p></article><article class="card" data-search="where & filtering select * from users where age >= 18 and status = 'active'; combine conditions with and."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE age >= 18 AND status = 'active';</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE age >= 18 AND status = 'active';">COPY</button></div><p>Combine conditions with AND.</p></article><article class="card" data-search="where & filtering select * from users where role = 'admin' or role = 'teacher'; combine conditions with or."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE role = 'admin' OR role = 'teacher';</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE role = 'admin' OR role = 'teacher';">COPY</button></div><p>Combine conditions with OR.</p></article><article class="card" data-search="where & filtering select * from users where not status = 'disabled'; negate a condition."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE NOT status = 'disabled';</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE NOT status = 'disabled';">COPY</button></div><p>Negate a condition.</p></article><article class="card" data-search="where & filtering select * from users where (role='admin' or role='teacher') and active=1; group boolean logic with parentheses."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE (role='admin' OR role='teacher') AND active=1;</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE (role='admin' OR role='teacher') AND active=1;">COPY</button></div><p>Group Boolean logic with parentheses.</p></article></div></div></section><section class="section" id="order-by---pagination"><button class="sectionhead" aria-expanded="true"><span>ORDER BY & Pagination</span><small>6 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="order by & pagination select * from users order by name asc; sort ascending."><div class="cmdrow"><pre><code>SELECT * FROM users ORDER BY name ASC;</code></pre><button class="copy" data-copy="SELECT * FROM users ORDER BY name ASC;">COPY</button></div><p>Sort ascending.</p></article><article class="card" data-search="order by & pagination select * from users order by created_at desc; sort descending."><div class="cmdrow"><pre><code>SELECT * FROM users ORDER BY created_at DESC;</code></pre><button class="copy" data-copy="SELECT * FROM users ORDER BY created_at DESC;">COPY</button></div><p>Sort descending.</p></article><article class="card" data-search="order by & pagination select * from users order by role, name; sort by multiple columns."><div class="cmdrow"><pre><code>SELECT * FROM users ORDER BY role, name;</code></pre><button class="copy" data-copy="SELECT * FROM users ORDER BY role, name;">COPY</button></div><p>Sort by multiple columns.</p></article><article class="card" data-search="order by & pagination select * from users order by 2; sort by select-list position; valid but less readable."><div class="cmdrow"><pre><code>SELECT * FROM users ORDER BY 2;</code></pre><button class="copy" data-copy="SELECT * FROM users ORDER BY 2;">COPY</button></div><p>Sort by select-list position; valid but less readable.</p></article><article class="card" data-search="order by & pagination select * from users limit 25 offset 50; paginate with limit/offset."><div class="cmdrow"><pre><code>SELECT * FROM users LIMIT 25 OFFSET 50;</code></pre><button class="copy" data-copy="SELECT * FROM users LIMIT 25 OFFSET 50;">COPY</button></div><p>Paginate with LIMIT/OFFSET.</p></article><article class="card" data-search="order by & pagination select * from users order by id offset 50 rows fetch next 25 rows only; paginate in sql server/ansi-style syntax."><div class="cmdrow"><pre><code>SELECT * FROM users ORDER BY id OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;</code></pre><button class="copy" data-copy="SELECT * FROM users ORDER BY id OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;">COPY</button></div><p>Paginate in SQL Server/ANSI-style syntax.</p></article></div></div></section><section class="section" id="insert"><button class="sectionhead" aria-expanded="true"><span>INSERT</span><small>5 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="insert insert into users (name, email) values ('ada','[email protected]'); insert one row."><div class="cmdrow"><pre><code>INSERT INTO users (name, email) VALUES ('Ada','[email protected]');</code></pre><button class="copy" data-copy="INSERT INTO users (name, email) VALUES ('Ada','[email protected]');">COPY</button></div><p>Insert one row.</p></article><article class="card" data-search="insert insert into users (name, email) values ('ada','[email protected]'),('linus','[email protected]'); insert multiple rows."><div class="cmdrow"><pre><code>INSERT INTO users (name, email) VALUES ('Ada','[email protected]'),('Linus','[email protected]');</code></pre><button class="copy" data-copy="INSERT INTO users (name, email) VALUES ('Ada','[email protected]'),('Linus','[email protected]');">COPY</button></div><p>Insert multiple rows.</p></article><article class="card" data-search="insert insert into archive_users select * from users where active = 0; insert rows from a query."><div class="cmdrow"><pre><code>INSERT INTO archive_users SELECT * FROM users WHERE active = 0;</code></pre><button class="copy" data-copy="INSERT INTO archive_users SELECT * FROM users WHERE active = 0;">COPY</button></div><p>Insert rows from a query.</p></article><article class="card" data-search="insert insert into users default values; insert a row using default values where supported."><div class="cmdrow"><pre><code>INSERT INTO users DEFAULT VALUES;</code></pre><button class="copy" data-copy="INSERT INTO users DEFAULT VALUES;">COPY</button></div><p>Insert a row using default values where supported.</p></article><article class="card" data-search="insert insert into users (name) values ('ada') returning id; insert and return generated data in postgresql/sqlite versions supporting returning."><div class="cmdrow"><pre><code>INSERT INTO users (name) VALUES ('Ada') RETURNING id;</code></pre><button class="copy" data-copy="INSERT INTO users (name) VALUES ('Ada') RETURNING id;">COPY</button></div><p>Insert and return generated data in PostgreSQL/SQLite versions supporting RETURNING.</p></article></div></div></section><section class="section" id="update"><button class="sectionhead" aria-expanded="true"><span>UPDATE</span><small>4 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="update update users set status = 'active' where id = 1; update one row."><div class="cmdrow"><pre><code>UPDATE users SET status = 'active' WHERE id = 1;</code></pre><button class="copy" data-copy="UPDATE users SET status = 'active' WHERE id = 1;">COPY</button></div><p>Update one row.</p></article><article class="card" data-search="update update users set status='inactive', updated_at=current_timestamp where id=1; update multiple columns."><div class="cmdrow"><pre><code>UPDATE users SET status='inactive', updated_at=CURRENT_TIMESTAMP WHERE id=1;</code></pre><button class="copy" data-copy="UPDATE users SET status='inactive', updated_at=CURRENT_TIMESTAMP WHERE id=1;">COPY</button></div><p>Update multiple columns.</p></article><article class="card" data-search="update update users set score = score + 10 where active = 1; update using the existing value."><div class="cmdrow"><pre><code>UPDATE users SET score = score + 10 WHERE active = 1;</code></pre><button class="copy" data-copy="UPDATE users SET score = score + 10 WHERE active = 1;">COPY</button></div><p>Update using the existing value.</p></article><article class="card warning" data-search="update update users set department = 'it'; update every row. ⚠"><div class="cmdrow"><pre><code>UPDATE users SET department = 'IT';</code></pre><button class="copy" data-copy="UPDATE users SET department = 'IT';">COPY</button></div><p>Update every row. ⚠</p></article></div></div></section><section class="section" id="delete"><button class="sectionhead" aria-expanded="true"><span>DELETE</span><small>4 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="delete delete from users where id = 1; delete one matching row."><div class="cmdrow"><pre><code>DELETE FROM users WHERE id = 1;</code></pre><button class="copy" data-copy="DELETE FROM users WHERE id = 1;">COPY</button></div><p>Delete one matching row.</p></article><article class="card" data-search="delete delete from users where active = 0; delete matching rows."><div class="cmdrow"><pre><code>DELETE FROM users WHERE active = 0;</code></pre><button class="copy" data-copy="DELETE FROM users WHERE active = 0;">COPY</button></div><p>Delete matching rows.</p></article><article class="card warning" data-search="delete delete from users; delete all rows while keeping the table. ⚠"><div class="cmdrow"><pre><code>DELETE FROM users;</code></pre><button class="copy" data-copy="DELETE FROM users;">COPY</button></div><p>Delete all rows while keeping the table. ⚠</p></article><article class="card warning" data-search="delete truncate table users; quickly remove all rows from a table where supported. ⚠"><div class="cmdrow"><pre><code>TRUNCATE TABLE users;</code></pre><button class="copy" data-copy="TRUNCATE TABLE users;">COPY</button></div><p>Quickly remove all rows from a table where supported. ⚠</p></article></div></div></section><section class="section" id="create-table"><button class="sectionhead" aria-expanded="true"><span>CREATE TABLE</span><small>8 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="create table create table users (id int primary key, name varchar(100)); create a simple table."><div class="cmdrow"><pre><code>CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));</code></pre><button class="copy" data-copy="CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));">COPY</button></div><p>Create a simple table.</p></article><article class="card" data-search="create table create table users (id integer generated always as identity primary key, name varchar(100)); create an identity column using standard-style syntax on supported systems."><div class="cmdrow"><pre><code>CREATE TABLE users (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100));</code></pre><button class="copy" data-copy="CREATE TABLE users (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100));">COPY</button></div><p>Create an identity column using standard-style syntax on supported systems.</p></article><article class="card" data-search="create table create table users (id int auto_increment primary key, name varchar(100)); mysql auto-increment example."><div class="cmdrow"><pre><code>CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));</code></pre><button class="copy" data-copy="CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));">COPY</button></div><p>MySQL auto-increment example.</p></article><article class="card" data-search="create table create table users (id integer primary key autoincrement, name text); sqlite auto-increment style."><div class="cmdrow"><pre><code>CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);</code></pre><button class="copy" data-copy="CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);">COPY</button></div><p>SQLite auto-increment style.</p></article><article class="card" data-search="create table create table users (id serial primary key, name text); legacy/common postgresql serial style."><div class="cmdrow"><pre><code>CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);</code></pre><button class="copy" data-copy="CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);">COPY</button></div><p>Legacy/common PostgreSQL serial style.</p></article><article class="card" data-search="create table create table users (id int identity(1,1) primary key, name nvarchar(100)); sql server identity style."><div class="cmdrow"><pre><code>CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100));</code></pre><button class="copy" data-copy="CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100));">COPY</button></div><p>SQL Server identity style.</p></article><article class="card" data-search="create table create table if not exists users (id int primary key, name varchar(100)); create only if missing where supported."><div class="cmdrow"><pre><code>CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY, name VARCHAR(100));</code></pre><button class="copy" data-copy="CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY, name VARCHAR(100));">COPY</button></div><p>Create only if missing where supported.</p></article><article class="card" data-search="create table create table backup as select * from users; create a table from query results on supported databases."><div class="cmdrow"><pre><code>CREATE TABLE backup AS SELECT * FROM users;</code></pre><button class="copy" data-copy="CREATE TABLE backup AS SELECT * FROM users;">COPY</button></div><p>Create a table from query results on supported databases.</p></article></div></div></section><section class="section" id="alter-table"><button class="sectionhead" aria-expanded="true"><span>ALTER TABLE</span><small>8 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="alter table alter table users add column phone varchar(30); add a column."><div class="cmdrow"><pre><code>ALTER TABLE users ADD COLUMN phone VARCHAR(30);</code></pre><button class="copy" data-copy="ALTER TABLE users ADD COLUMN phone VARCHAR(30);">COPY</button></div><p>Add a column.</p></article><article class="card warning" data-search="alter table alter table users drop column phone; drop a column. ⚠"><div class="cmdrow"><pre><code>ALTER TABLE users DROP COLUMN phone;</code></pre><button class="copy" data-copy="ALTER TABLE users DROP COLUMN phone;">COPY</button></div><p>Drop a column. ⚠</p></article><article class="card" data-search="alter table alter table users rename column name to full_name; rename a column on supported databases."><div class="cmdrow"><pre><code>ALTER TABLE users RENAME COLUMN name TO full_name;</code></pre><button class="copy" data-copy="ALTER TABLE users RENAME COLUMN name TO full_name;">COPY</button></div><p>Rename a column on supported databases.</p></article><article class="card" data-search="alter table alter table users rename to app_users; rename a table."><div class="cmdrow"><pre><code>ALTER TABLE users RENAME TO app_users;</code></pre><button class="copy" data-copy="ALTER TABLE users RENAME TO app_users;">COPY</button></div><p>Rename a table.</p></article><article class="card" data-search="alter table alter table users add constraint uq_users_email unique (email); add a unique constraint."><div class="cmdrow"><pre><code>ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);</code></pre><button class="copy" data-copy="ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);">COPY</button></div><p>Add a unique constraint.</p></article><article class="card" data-search="alter table alter table orders add constraint fk_orders_user foreign key (user_id) references users(id); add a foreign key."><div class="cmdrow"><pre><code>ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id);</code></pre><button class="copy" data-copy="ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id);">COPY</button></div><p>Add a foreign key.</p></article><article class="card" data-search="alter table alter table users alter column name set not null; set not null in postgresql-style syntax."><div class="cmdrow"><pre><code>ALTER TABLE users ALTER COLUMN name SET NOT NULL;</code></pre><button class="copy" data-copy="ALTER TABLE users ALTER COLUMN name SET NOT NULL;">COPY</button></div><p>Set NOT NULL in PostgreSQL-style syntax.</p></article><article class="card" data-search="alter table alter table users modify name varchar(150) not null; modify column definition in mysql."><div class="cmdrow"><pre><code>ALTER TABLE users MODIFY name VARCHAR(150) NOT NULL;</code></pre><button class="copy" data-copy="ALTER TABLE users MODIFY name VARCHAR(150) NOT NULL;">COPY</button></div><p>Modify column definition in MySQL.</p></article></div></div></section><section class="section" id="drop---truncate"><button class="sectionhead" aria-expanded="true"><span>DROP & TRUNCATE</span><small>6 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card warning" data-search="drop & truncate drop table users; delete a table and its data. ⚠"><div class="cmdrow"><pre><code>DROP TABLE users;</code></pre><button class="copy" data-copy="DROP TABLE users;">COPY</button></div><p>Delete a table and its data. ⚠</p></article><article class="card warning" data-search="drop & truncate drop table if exists users; delete a table only if it exists. ⚠"><div class="cmdrow"><pre><code>DROP TABLE IF EXISTS users;</code></pre><button class="copy" data-copy="DROP TABLE IF EXISTS users;">COPY</button></div><p>Delete a table only if it exists. ⚠</p></article><article class="card warning" data-search="drop & truncate drop database appdb; delete an entire database. ⚠"><div class="cmdrow"><pre><code>DROP DATABASE appdb;</code></pre><button class="copy" data-copy="DROP DATABASE appdb;">COPY</button></div><p>Delete an entire database. ⚠</p></article><article class="card warning" data-search="drop & truncate truncate table logs; remove all rows quickly. ⚠"><div class="cmdrow"><pre><code>TRUNCATE TABLE logs;</code></pre><button class="copy" data-copy="TRUNCATE TABLE logs;">COPY</button></div><p>Remove all rows quickly. ⚠</p></article><article class="card" data-search="drop & truncate drop view active_users; delete a view."><div class="cmdrow"><pre><code>DROP VIEW active_users;</code></pre><button class="copy" data-copy="DROP VIEW active_users;">COPY</button></div><p>Delete a view.</p></article><article class="card" data-search="drop & truncate drop index idx_users_email; drop an index; syntax varies by database."><div class="cmdrow"><pre><code>DROP INDEX idx_users_email;</code></pre><button class="copy" data-copy="DROP INDEX idx_users_email;">COPY</button></div><p>Drop an index; syntax varies by database.</p></article></div></div></section><section class="section" id="data-types"><button class="sectionhead" aria-expanded="true"><span>Data Types</span><small>16 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="data types int whole-number type."><div class="cmdrow"><pre><code>INT</code></pre><button class="copy" data-copy="INT">COPY</button></div><p>Whole-number type.</p></article><article class="card" data-search="data types bigint large whole-number type."><div class="cmdrow"><pre><code>BIGINT</code></pre><button class="copy" data-copy="BIGINT">COPY</button></div><p>Large whole-number type.</p></article><article class="card" data-search="data types smallint smaller whole-number type."><div class="cmdrow"><pre><code>SMALLINT</code></pre><button class="copy" data-copy="SMALLINT">COPY</button></div><p>Smaller whole-number type.</p></article><article class="card" data-search="data types decimal(10,2) exact numeric type suitable for money-like values."><div class="cmdrow"><pre><code>DECIMAL(10,2)</code></pre><button class="copy" data-copy="DECIMAL(10,2)">COPY</button></div><p>Exact numeric type suitable for money-like values.</p></article><article class="card" data-search="data types numeric(10,2) exact numeric type, often equivalent to decimal."><div class="cmdrow"><pre><code>NUMERIC(10,2)</code></pre><button class="copy" data-copy="NUMERIC(10,2)">COPY</button></div><p>Exact numeric type, often equivalent to DECIMAL.</p></article><article class="card" data-search="data types real floating-point type."><div class="cmdrow"><pre><code>REAL</code></pre><button class="copy" data-copy="REAL">COPY</button></div><p>Floating-point type.</p></article><article class="card" data-search="data types double precision double-precision floating-point type."><div class="cmdrow"><pre><code>DOUBLE PRECISION</code></pre><button class="copy" data-copy="DOUBLE PRECISION">COPY</button></div><p>Double-precision floating-point type.</p></article><article class="card" data-search="data types varchar(255) variable-length text."><div class="cmdrow"><pre><code>VARCHAR(255)</code></pre><button class="copy" data-copy="VARCHAR(255)">COPY</button></div><p>Variable-length text.</p></article><article class="card" data-search="data types char(2) fixed-length text."><div class="cmdrow"><pre><code>CHAR(2)</code></pre><button class="copy" data-copy="CHAR(2)">COPY</button></div><p>Fixed-length text.</p></article><article class="card" data-search="data types text large or unbounded text on many databases."><div class="cmdrow"><pre><code>TEXT</code></pre><button class="copy" data-copy="TEXT">COPY</button></div><p>Large or unbounded text on many databases.</p></article><article class="card" data-search="data types date calendar date."><div class="cmdrow"><pre><code>DATE</code></pre><button class="copy" data-copy="DATE">COPY</button></div><p>Calendar date.</p></article><article class="card" data-search="data types time time of day."><div class="cmdrow"><pre><code>TIME</code></pre><button class="copy" data-copy="TIME">COPY</button></div><p>Time of day.</p></article><article class="card" data-search="data types timestamp date and time."><div class="cmdrow"><pre><code>TIMESTAMP</code></pre><button class="copy" data-copy="TIMESTAMP">COPY</button></div><p>Date and time.</p></article><article class="card" data-search="data types boolean true/false type where supported."><div class="cmdrow"><pre><code>BOOLEAN</code></pre><button class="copy" data-copy="BOOLEAN">COPY</button></div><p>True/false type where supported.</p></article><article class="card" data-search="data types blob binary large object."><div class="cmdrow"><pre><code>BLOB</code></pre><button class="copy" data-copy="BLOB">COPY</button></div><p>Binary large object.</p></article><article class="card" data-search="data types json native json type on databases that support it."><div class="cmdrow"><pre><code>JSON</code></pre><button class="copy" data-copy="JSON">COPY</button></div><p>Native JSON type on databases that support it.</p></article></div></div></section><section class="section" id="constraints"><button class="sectionhead" aria-expanded="true"><span>Constraints</span><small>9 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="constraints primary key (id) uniquely identify each row."><div class="cmdrow"><pre><code>PRIMARY KEY (id)</code></pre><button class="copy" data-copy="PRIMARY KEY (id)">COPY</button></div><p>Uniquely identify each row.</p></article><article class="card" data-search="constraints foreign key (user_id) references users(id) enforce referential integrity."><div class="cmdrow"><pre><code>FOREIGN KEY (user_id) REFERENCES users(id)</code></pre><button class="copy" data-copy="FOREIGN KEY (user_id) REFERENCES users(id)">COPY</button></div><p>Enforce referential integrity.</p></article><article class="card" data-search="constraints unique (email) require unique values."><div class="cmdrow"><pre><code>UNIQUE (email)</code></pre><button class="copy" data-copy="UNIQUE (email)">COPY</button></div><p>Require unique values.</p></article><article class="card" data-search="constraints not null disallow null."><div class="cmdrow"><pre><code>NOT NULL</code></pre><button class="copy" data-copy="NOT NULL">COPY</button></div><p>Disallow NULL.</p></article><article class="card" data-search="constraints check (age >= 0) require a condition to be true."><div class="cmdrow"><pre><code>CHECK (age >= 0)</code></pre><button class="copy" data-copy="CHECK (age >= 0)">COPY</button></div><p>Require a condition to be true.</p></article><article class="card" data-search="constraints default 'active' provide a default value."><div class="cmdrow"><pre><code>DEFAULT 'active'</code></pre><button class="copy" data-copy="DEFAULT 'active'">COPY</button></div><p>Provide a default value.</p></article><article class="card" data-search="constraints constraint pk_users primary key (id) create a named constraint."><div class="cmdrow"><pre><code>CONSTRAINT pk_users PRIMARY KEY (id)</code></pre><button class="copy" data-copy="CONSTRAINT pk_users PRIMARY KEY (id)">COPY</button></div><p>Create a named constraint.</p></article><article class="card" data-search="constraints foreign key (user_id) references users(id) on delete cascade delete child rows when the parent is deleted."><div class="cmdrow"><pre><code>FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE</code></pre><button class="copy" data-copy="FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE">COPY</button></div><p>Delete child rows when the parent is deleted.</p></article><article class="card" data-search="constraints foreign key (user_id) references users(id) on delete set null set child foreign key to null when parent is deleted."><div class="cmdrow"><pre><code>FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL</code></pre><button class="copy" data-copy="FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL">COPY</button></div><p>Set child foreign key to NULL when parent is deleted.</p></article></div></div></section><section class="section" id="aggregate-functions"><button class="sectionhead" aria-expanded="true"><span>Aggregate Functions</span><small>8 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="aggregate functions select count(*) from users; count rows."><div class="cmdrow"><pre><code>SELECT COUNT(*) FROM users;</code></pre><button class="copy" data-copy="SELECT COUNT(*) FROM users;">COPY</button></div><p>Count rows.</p></article><article class="card" data-search="aggregate functions select count(email) from users; count non-null email values."><div class="cmdrow"><pre><code>SELECT COUNT(email) FROM users;</code></pre><button class="copy" data-copy="SELECT COUNT(email) FROM users;">COPY</button></div><p>Count non-NULL email values.</p></article><article class="card" data-search="aggregate functions select count(distinct city) from users; count distinct values."><div class="cmdrow"><pre><code>SELECT COUNT(DISTINCT city) FROM users;</code></pre><button class="copy" data-copy="SELECT COUNT(DISTINCT city) FROM users;">COPY</button></div><p>Count distinct values.</p></article><article class="card" data-search="aggregate functions select sum(amount) from orders; sum numeric values."><div class="cmdrow"><pre><code>SELECT SUM(amount) FROM orders;</code></pre><button class="copy" data-copy="SELECT SUM(amount) FROM orders;">COPY</button></div><p>Sum numeric values.</p></article><article class="card" data-search="aggregate functions select avg(amount) from orders; average numeric values."><div class="cmdrow"><pre><code>SELECT AVG(amount) FROM orders;</code></pre><button class="copy" data-copy="SELECT AVG(amount) FROM orders;">COPY</button></div><p>Average numeric values.</p></article><article class="card" data-search="aggregate functions select min(amount) from orders; smallest value."><div class="cmdrow"><pre><code>SELECT MIN(amount) FROM orders;</code></pre><button class="copy" data-copy="SELECT MIN(amount) FROM orders;">COPY</button></div><p>Smallest value.</p></article><article class="card" data-search="aggregate functions select max(amount) from orders; largest value."><div class="cmdrow"><pre><code>SELECT MAX(amount) FROM orders;</code></pre><button class="copy" data-copy="SELECT MAX(amount) FROM orders;">COPY</button></div><p>Largest value.</p></article><article class="card" data-search="aggregate functions select min(created_at), max(created_at) from users; get earliest and latest values."><div class="cmdrow"><pre><code>SELECT MIN(created_at), MAX(created_at) FROM users;</code></pre><button class="copy" data-copy="SELECT MIN(created_at), MAX(created_at) FROM users;">COPY</button></div><p>Get earliest and latest values.</p></article></div></div></section><section class="section" id="group-by---having"><button class="sectionhead" aria-expanded="true"><span>GROUP BY & HAVING</span><small>5 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="group by & having select city, count(*) from users group by city; count rows per city."><div class="cmdrow"><pre><code>SELECT city, COUNT(*) FROM users GROUP BY city;</code></pre><button class="copy" data-copy="SELECT city, COUNT(*) FROM users GROUP BY city;">COPY</button></div><p>Count rows per city.</p></article><article class="card" data-search="group by & having select department, avg(salary) from employees group by department; average by group."><div class="cmdrow"><pre><code>SELECT department, AVG(salary) FROM employees GROUP BY department;</code></pre><button class="copy" data-copy="SELECT department, AVG(salary) FROM employees GROUP BY department;">COPY</button></div><p>Average by group.</p></article><article class="card" data-search="group by & having select city, count(*) from users group by city having count(*) > 10; filter groups after aggregation."><div class="cmdrow"><pre><code>SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 10;</code></pre><button class="copy" data-copy="SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 10;">COPY</button></div><p>Filter groups after aggregation.</p></article><article class="card" data-search="group by & having select department, sum(salary) from employees group by department having sum(salary) > 100000; filter aggregate totals."><div class="cmdrow"><pre><code>SELECT department, SUM(salary) FROM employees GROUP BY department HAVING SUM(salary) > 100000;</code></pre><button class="copy" data-copy="SELECT department, SUM(salary) FROM employees GROUP BY department HAVING SUM(salary) > 100000;">COPY</button></div><p>Filter aggregate totals.</p></article><article class="card" data-search="group by & having select department, role, count(*) from employees group by department, role; group by multiple columns."><div class="cmdrow"><pre><code>SELECT department, role, COUNT(*) FROM employees GROUP BY department, role;</code></pre><button class="copy" data-copy="SELECT department, role, COUNT(*) FROM employees GROUP BY department, role;">COPY</button></div><p>Group by multiple columns.</p></article></div></div></section><section class="section" id="inner-join"><button class="sectionhead" aria-expanded="true"><span>INNER JOIN</span><small>3 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="inner join select u.name, o.total from users u inner join orders o on o.user_id = u.id; return only matching rows."><div class="cmdrow"><pre><code>SELECT u.name, o.total FROM users u INNER JOIN orders o ON o.user_id = u.id;</code></pre><button class="copy" data-copy="SELECT u.name, o.total FROM users u INNER JOIN orders o ON o.user_id = u.id;">COPY</button></div><p>Return only matching rows.</p></article><article class="card" data-search="inner join select * from a join b on a.id = b.a_id; join defaults to inner join in common sql dialects."><div class="cmdrow"><pre><code>SELECT * FROM a JOIN b ON a.id = b.a_id;</code></pre><button class="copy" data-copy="SELECT * FROM a JOIN b ON a.id = b.a_id;">COPY</button></div><p>JOIN defaults to INNER JOIN in common SQL dialects.</p></article><article class="card" data-search="inner join select u.name, d.name as department from users u join departments d on d.id = u.department_id; join lookup data."><div class="cmdrow"><pre><code>SELECT u.name, d.name AS department FROM users u JOIN departments d ON d.id = u.department_id;</code></pre><button class="copy" data-copy="SELECT u.name, d.name AS department FROM users u JOIN departments d ON d.id = u.department_id;">COPY</button></div><p>Join lookup data.</p></article></div></div></section><section class="section" id="left---right---full-join"><button class="sectionhead" aria-expanded="true"><span>LEFT / RIGHT / FULL JOIN</span><small>4 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="left / right / full join select u.name, o.id from users u left join orders o on o.user_id = u.id; keep all rows from the left table."><div class="cmdrow"><pre><code>SELECT u.name, o.id FROM users u LEFT JOIN orders o ON o.user_id = u.id;</code></pre><button class="copy" data-copy="SELECT u.name, o.id FROM users u LEFT JOIN orders o ON o.user_id = u.id;">COPY</button></div><p>Keep all rows from the left table.</p></article><article class="card" data-search="left / right / full join select u.name, o.id from users u right join orders o on o.user_id = u.id; keep all rows from the right table where supported."><div class="cmdrow"><pre><code>SELECT u.name, o.id FROM users u RIGHT JOIN orders o ON o.user_id = u.id;</code></pre><button class="copy" data-copy="SELECT u.name, o.id FROM users u RIGHT JOIN orders o ON o.user_id = u.id;">COPY</button></div><p>Keep all rows from the right table where supported.</p></article><article class="card" data-search="left / right / full join select * from a full outer join b on a.id = b.id; keep rows from both sides where supported."><div class="cmdrow"><pre><code>SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id;</code></pre><button class="copy" data-copy="SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id;">COPY</button></div><p>Keep rows from both sides where supported.</p></article><article class="card" data-search="left / right / full join select u.* from users u left join orders o on o.user_id=u.id where o.id is null; find left-side rows with no match."><div class="cmdrow"><pre><code>SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id=u.id WHERE o.id IS NULL;</code></pre><button class="copy" data-copy="SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id=u.id WHERE o.id IS NULL;">COPY</button></div><p>Find left-side rows with no match.</p></article></div></div></section><section class="section" id="cross---self-join"><button class="sectionhead" aria-expanded="true"><span>CROSS & SELF JOIN</span><small>2 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="cross & self join select * from colors cross join sizes; return every combination of rows."><div class="cmdrow"><pre><code>SELECT * FROM colors CROSS JOIN sizes;</code></pre><button class="copy" data-copy="SELECT * FROM colors CROSS JOIN sizes;">COPY</button></div><p>Return every combination of rows.</p></article><article class="card" data-search="cross & self join select e.name, m.name as manager from employees e left join employees m on e.manager_id = m.id; self-join to a manager row."><div class="cmdrow"><pre><code>SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;</code></pre><button class="copy" data-copy="SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;">COPY</button></div><p>Self-join to a manager row.</p></article></div></div></section><section class="section" id="subqueries"><button class="sectionhead" aria-expanded="true"><span>Subqueries</span><small>5 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="subqueries select * from users where id in (select user_id from orders); filter using a subquery."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);">COPY</button></div><p>Filter using a subquery.</p></article><article class="card" data-search="subqueries select * from users where exists (select 1 from orders o where o.user_id = users.id); filter using exists."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id);</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id);">COPY</button></div><p>Filter using EXISTS.</p></article><article class="card" data-search="subqueries select * from users where not exists (select 1 from orders o where o.user_id = users.id); find rows with no related row."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id);</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id);">COPY</button></div><p>Find rows with no related row.</p></article><article class="card" data-search="subqueries select name, (select count(*) from orders o where o.user_id=u.id) as order_count from users u; use a correlated scalar subquery."><div class="cmdrow"><pre><code>SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id=u.id) AS order_count FROM users u;</code></pre><button class="copy" data-copy="SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id=u.id) AS order_count FROM users u;">COPY</button></div><p>Use a correlated scalar subquery.</p></article><article class="card" data-search="subqueries select * from products where price > (select avg(price) from products); compare against an aggregate subquery."><div class="cmdrow"><pre><code>SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);</code></pre><button class="copy" data-copy="SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);">COPY</button></div><p>Compare against an aggregate subquery.</p></article></div></div></section><section class="section" id="ctes"><button class="sectionhead" aria-expanded="true"><span>CTEs</span><small>4 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="ctes with active_users as (select * from users where active=1) select * from active_users; define a common table expression."><div class="cmdrow"><pre><code>WITH active_users AS (SELECT * FROM users WHERE active=1) SELECT * FROM active_users;</code></pre><button class="copy" data-copy="WITH active_users AS (SELECT * FROM users WHERE active=1) SELECT * FROM active_users;">COPY</button></div><p>Define a common table expression.</p></article><article class="card" data-search="ctes with totals as (select user_id, sum(total) total from orders group by user_id) select * from totals; use a cte for grouped data."><div class="cmdrow"><pre><code>WITH totals AS (SELECT user_id, SUM(total) total FROM orders GROUP BY user_id) SELECT * FROM totals;</code></pre><button class="copy" data-copy="WITH totals AS (SELECT user_id, SUM(total) total FROM orders GROUP BY user_id) SELECT * FROM totals;">COPY</button></div><p>Use a CTE for grouped data.</p></article><article class="card" data-search="ctes with a as (...), b as (...) select * from a join b on ...; define multiple ctes."><div class="cmdrow"><pre><code>WITH a AS (...), b AS (...) SELECT * FROM a JOIN b ON ...;</code></pre><button class="copy" data-copy="WITH a AS (...), b AS (...) SELECT * FROM a JOIN b ON ...;">COPY</button></div><p>Define multiple CTEs.</p></article><article class="card" data-search="ctes with recursive nums(n) as (select 1 union all select n+1 from nums where n<10) select * from nums; recursive cte example for supported databases."><div class="cmdrow"><pre><code>WITH RECURSIVE nums(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n<10) SELECT * FROM nums;</code></pre><button class="copy" data-copy="WITH RECURSIVE nums(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n<10) SELECT * FROM nums;">COPY</button></div><p>Recursive CTE example for supported databases.</p></article></div></div></section><section class="section" id="union---intersect---except"><button class="sectionhead" aria-expanded="true"><span>UNION / INTERSECT / EXCEPT</span><small>4 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="union / intersect / except select email from customers union select email from leads; combine results and remove duplicates."><div class="cmdrow"><pre><code>SELECT email FROM customers UNION SELECT email FROM leads;</code></pre><button class="copy" data-copy="SELECT email FROM customers UNION SELECT email FROM leads;">COPY</button></div><p>Combine results and remove duplicates.</p></article><article class="card" data-search="union / intersect / except select email from customers union all select email from leads; combine results and keep duplicates."><div class="cmdrow"><pre><code>SELECT email FROM customers UNION ALL SELECT email FROM leads;</code></pre><button class="copy" data-copy="SELECT email FROM customers UNION ALL SELECT email FROM leads;">COPY</button></div><p>Combine results and keep duplicates.</p></article><article class="card" data-search="union / intersect / except select id from a intersect select id from b; return values present in both sets where supported."><div class="cmdrow"><pre><code>SELECT id FROM a INTERSECT SELECT id FROM b;</code></pre><button class="copy" data-copy="SELECT id FROM a INTERSECT SELECT id FROM b;">COPY</button></div><p>Return values present in both sets where supported.</p></article><article class="card" data-search="union / intersect / except select id from a except select id from b; return values present only in the first set where supported."><div class="cmdrow"><pre><code>SELECT id FROM a EXCEPT SELECT id FROM b;</code></pre><button class="copy" data-copy="SELECT id FROM a EXCEPT SELECT id FROM b;">COPY</button></div><p>Return values present only in the first set where supported.</p></article></div></div></section><section class="section" id="case-expressions"><button class="sectionhead" aria-expanded="true"><span>CASE Expressions</span><small>3 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="case expressions select name, case when age>=18 then 'adult' else 'minor' end as category from users; conditional output."><div class="cmdrow"><pre><code>SELECT name, CASE WHEN age>=18 THEN 'adult' ELSE 'minor' END AS category FROM users;</code></pre><button class="copy" data-copy="SELECT name, CASE WHEN age>=18 THEN 'adult' ELSE 'minor' END AS category FROM users;">COPY</button></div><p>Conditional output.</p></article><article class="card" data-search="case expressions select case status when 'a' then 'active' when 'i' then 'inactive' else 'other' end from users; simple case expression."><div class="cmdrow"><pre><code>SELECT CASE status WHEN 'A' THEN 'Active' WHEN 'I' THEN 'Inactive' ELSE 'Other' END FROM users;</code></pre><button class="copy" data-copy="SELECT CASE status WHEN 'A' THEN 'Active' WHEN 'I' THEN 'Inactive' ELSE 'Other' END FROM users;">COPY</button></div><p>Simple CASE expression.</p></article><article class="card" data-search="case expressions order by case when priority='high' then 1 when priority='medium' then 2 else 3 end; custom sort order."><div class="cmdrow"><pre><code>ORDER BY CASE WHEN priority='high' THEN 1 WHEN priority='medium' THEN 2 ELSE 3 END;</code></pre><button class="copy" data-copy="ORDER BY CASE WHEN priority='high' THEN 1 WHEN priority='medium' THEN 2 ELSE 3 END;">COPY</button></div><p>Custom sort order.</p></article></div></div></section><section class="section" id="null-handling"><button class="sectionhead" aria-expanded="true"><span>NULL Handling</span><small>5 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="null handling coalesce(phone, 'n/a') return the first non-null value."><div class="cmdrow"><pre><code>COALESCE(phone, 'N/A')</code></pre><button class="copy" data-copy="COALESCE(phone, 'N/A')">COPY</button></div><p>Return the first non-NULL value.</p></article><article class="card" data-search="null handling nullif(a, b) return null when two expressions are equal."><div class="cmdrow"><pre><code>NULLIF(a, b)</code></pre><button class="copy" data-copy="NULLIF(a, b)">COPY</button></div><p>Return NULL when two expressions are equal.</p></article><article class="card" data-search="null handling is null test for null."><div class="cmdrow"><pre><code>IS NULL</code></pre><button class="copy" data-copy="IS NULL">COPY</button></div><p>Test for NULL.</p></article><article class="card" data-search="null handling is not null test for non-null."><div class="cmdrow"><pre><code>IS NOT NULL</code></pre><button class="copy" data-copy="IS NOT NULL">COPY</button></div><p>Test for non-NULL.</p></article><article class="card" data-search="null handling coalesce(discount, 0) common pattern for replacing null numerics."><div class="cmdrow"><pre><code>COALESCE(discount, 0)</code></pre><button class="copy" data-copy="COALESCE(discount, 0)">COPY</button></div><p>Common pattern for replacing NULL numerics.</p></article></div></div></section><section class="section" id="string-functions"><button class="sectionhead" aria-expanded="true"><span>String Functions</span><small>10 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="string functions upper(name) convert text to uppercase."><div class="cmdrow"><pre><code>UPPER(name)</code></pre><button class="copy" data-copy="UPPER(name)">COPY</button></div><p>Convert text to uppercase.</p></article><article class="card" data-search="string functions lower(name) convert text to lowercase."><div class="cmdrow"><pre><code>LOWER(name)</code></pre><button class="copy" data-copy="LOWER(name)">COPY</button></div><p>Convert text to lowercase.</p></article><article class="card" data-search="string functions trim(name) remove surrounding whitespace."><div class="cmdrow"><pre><code>TRIM(name)</code></pre><button class="copy" data-copy="TRIM(name)">COPY</button></div><p>Remove surrounding whitespace.</p></article><article class="card" data-search="string functions length(name) return string length on many databases."><div class="cmdrow"><pre><code>LENGTH(name)</code></pre><button class="copy" data-copy="LENGTH(name)">COPY</button></div><p>Return string length on many databases.</p></article><article class="card" data-search="string functions char_length(name) ansi-style character length."><div class="cmdrow"><pre><code>CHAR_LENGTH(name)</code></pre><button class="copy" data-copy="CHAR_LENGTH(name)">COPY</button></div><p>ANSI-style character length.</p></article><article class="card" data-search="string functions substring(name from 1 for 3) ansi-style substring on supported databases."><div class="cmdrow"><pre><code>SUBSTRING(name FROM 1 FOR 3)</code></pre><button class="copy" data-copy="SUBSTRING(name FROM 1 FOR 3)">COPY</button></div><p>ANSI-style substring on supported databases.</p></article><article class="card" data-search="string functions substring(name, 1, 3) common substring syntax."><div class="cmdrow"><pre><code>SUBSTRING(name, 1, 3)</code></pre><button class="copy" data-copy="SUBSTRING(name, 1, 3)">COPY</button></div><p>Common substring syntax.</p></article><article class="card" data-search="string functions concat(first_name, ' ', last_name) concatenate text."><div class="cmdrow"><pre><code>CONCAT(first_name, ' ', last_name)</code></pre><button class="copy" data-copy="CONCAT(first_name, ' ', last_name)">COPY</button></div><p>Concatenate text.</p></article><article class="card" data-search="string functions replace(name, 'old', 'new') replace text."><div class="cmdrow"><pre><code>REPLACE(name, 'old', 'new')</code></pre><button class="copy" data-copy="REPLACE(name, 'old', 'new')">COPY</button></div><p>Replace text.</p></article><article class="card" data-search="string functions position('a' in name) find substring position in ansi-style syntax."><div class="cmdrow"><pre><code>POSITION('a' IN name)</code></pre><button class="copy" data-copy="POSITION('a' IN name)">COPY</button></div><p>Find substring position in ANSI-style syntax.</p></article></div></div></section><section class="section" id="numeric-functions"><button class="sectionhead" aria-expanded="true"><span>Numeric Functions</span><small>6 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="numeric functions round(price, 2) round numeric value."><div class="cmdrow"><pre><code>ROUND(price, 2)</code></pre><button class="copy" data-copy="ROUND(price, 2)">COPY</button></div><p>Round numeric value.</p></article><article class="card" data-search="numeric functions abs(balance) absolute value."><div class="cmdrow"><pre><code>ABS(balance)</code></pre><button class="copy" data-copy="ABS(balance)">COPY</button></div><p>Absolute value.</p></article><article class="card" data-search="numeric functions ceiling(price) round upward."><div class="cmdrow"><pre><code>CEILING(price)</code></pre><button class="copy" data-copy="CEILING(price)">COPY</button></div><p>Round upward.</p></article><article class="card" data-search="numeric functions floor(price) round downward."><div class="cmdrow"><pre><code>FLOOR(price)</code></pre><button class="copy" data-copy="FLOOR(price)">COPY</button></div><p>Round downward.</p></article><article class="card" data-search="numeric functions power(2, 8) exponentiation."><div class="cmdrow"><pre><code>POWER(2, 8)</code></pre><button class="copy" data-copy="POWER(2, 8)">COPY</button></div><p>Exponentiation.</p></article><article class="card" data-search="numeric functions mod(10, 3) remainder."><div class="cmdrow"><pre><code>MOD(10, 3)</code></pre><button class="copy" data-copy="MOD(10, 3)">COPY</button></div><p>Remainder.</p></article></div></div></section><section class="section" id="date---time"><button class="sectionhead" aria-expanded="true"><span>Date & Time</span><small>9 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="date & time current_date current date."><div class="cmdrow"><pre><code>CURRENT_DATE</code></pre><button class="copy" data-copy="CURRENT_DATE">COPY</button></div><p>Current date.</p></article><article class="card" data-search="date & time current_time current time."><div class="cmdrow"><pre><code>CURRENT_TIME</code></pre><button class="copy" data-copy="CURRENT_TIME">COPY</button></div><p>Current time.</p></article><article class="card" data-search="date & time current_timestamp current date and time."><div class="cmdrow"><pre><code>CURRENT_TIMESTAMP</code></pre><button class="copy" data-copy="CURRENT_TIMESTAMP">COPY</button></div><p>Current date and time.</p></article><article class="card" data-search="date & time extract(year from created_at) extract date/time component."><div class="cmdrow"><pre><code>EXTRACT(YEAR FROM created_at)</code></pre><button class="copy" data-copy="EXTRACT(YEAR FROM created_at)">COPY</button></div><p>Extract date/time component.</p></article><article class="card" data-search="date & time date_trunc('month', created_at) truncate timestamp in postgresql."><div class="cmdrow"><pre><code>DATE_TRUNC('month', created_at)</code></pre><button class="copy" data-copy="DATE_TRUNC('month', created_at)">COPY</button></div><p>Truncate timestamp in PostgreSQL.</p></article><article class="card" data-search="date & time dateadd(day, 7, created_at) add time in sql server."><div class="cmdrow"><pre><code>DATEADD(day, 7, created_at)</code></pre><button class="copy" data-copy="DATEADD(day, 7, created_at)">COPY</button></div><p>Add time in SQL Server.</p></article><article class="card" data-search="date & time datediff(day, start_date, end_date) difference in sql server."><div class="cmdrow"><pre><code>DATEDIFF(day, start_date, end_date)</code></pre><button class="copy" data-copy="DATEDIFF(day, start_date, end_date)">COPY</button></div><p>Difference in SQL Server.</p></article><article class="card" data-search="date & time created_at + interval '7 days' add interval in postgresql."><div class="cmdrow"><pre><code>created_at + INTERVAL '7 days'</code></pre><button class="copy" data-copy="created_at + INTERVAL '7 days'">COPY</button></div><p>Add interval in PostgreSQL.</p></article><article class="card" data-search="date & time date(created_at) extract date portion in mysql/sqlite-style usage."><div class="cmdrow"><pre><code>DATE(created_at)</code></pre><button class="copy" data-copy="DATE(created_at)">COPY</button></div><p>Extract date portion in MySQL/SQLite-style usage.</p></article></div></div></section><section class="section" id="window-functions"><button class="sectionhead" aria-expanded="true"><span>Window Functions</span><small>10 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="window functions row_number() over (order by score desc) assign sequential row numbers."><div class="cmdrow"><pre><code>ROW_NUMBER() OVER (ORDER BY score DESC)</code></pre><button class="copy" data-copy="ROW_NUMBER() OVER (ORDER BY score DESC)">COPY</button></div><p>Assign sequential row numbers.</p></article><article class="card" data-search="window functions rank() over (order by score desc) rank rows with gaps after ties."><div class="cmdrow"><pre><code>RANK() OVER (ORDER BY score DESC)</code></pre><button class="copy" data-copy="RANK() OVER (ORDER BY score DESC)">COPY</button></div><p>Rank rows with gaps after ties.</p></article><article class="card" data-search="window functions dense_rank() over (order by score desc) rank rows without gaps after ties."><div class="cmdrow"><pre><code>DENSE_RANK() OVER (ORDER BY score DESC)</code></pre><button class="copy" data-copy="DENSE_RANK() OVER (ORDER BY score DESC)">COPY</button></div><p>Rank rows without gaps after ties.</p></article><article class="card" data-search="window functions count(*) over () return total row count alongside each row."><div class="cmdrow"><pre><code>COUNT(*) OVER ()</code></pre><button class="copy" data-copy="COUNT(*) OVER ()">COPY</button></div><p>Return total row count alongside each row.</p></article><article class="card" data-search="window functions sum(amount) over (partition by user_id) calculate per-user total without grouping away rows."><div class="cmdrow"><pre><code>SUM(amount) OVER (PARTITION BY user_id)</code></pre><button class="copy" data-copy="SUM(amount) OVER (PARTITION BY user_id)">COPY</button></div><p>Calculate per-user total without grouping away rows.</p></article><article class="card" data-search="window functions avg(score) over (partition by class_id) calculate per-group average."><div class="cmdrow"><pre><code>AVG(score) OVER (PARTITION BY class_id)</code></pre><button class="copy" data-copy="AVG(score) OVER (PARTITION BY class_id)">COPY</button></div><p>Calculate per-group average.</p></article><article class="card" data-search="window functions sum(amount) over (order by created_at) running total."><div class="cmdrow"><pre><code>SUM(amount) OVER (ORDER BY created_at)</code></pre><button class="copy" data-copy="SUM(amount) OVER (ORDER BY created_at)">COPY</button></div><p>Running total.</p></article><article class="card" data-search="window functions lag(amount) over (order by created_at) read previous row value."><div class="cmdrow"><pre><code>LAG(amount) OVER (ORDER BY created_at)</code></pre><button class="copy" data-copy="LAG(amount) OVER (ORDER BY created_at)">COPY</button></div><p>Read previous row value.</p></article><article class="card" data-search="window functions lead(amount) over (order by created_at) read next row value."><div class="cmdrow"><pre><code>LEAD(amount) OVER (ORDER BY created_at)</code></pre><button class="copy" data-copy="LEAD(amount) OVER (ORDER BY created_at)">COPY</button></div><p>Read next row value.</p></article><article class="card" data-search="window functions first_value(score) over (partition by team order by created_at) get first value in each window."><div class="cmdrow"><pre><code>FIRST_VALUE(score) OVER (PARTITION BY team ORDER BY created_at)</code></pre><button class="copy" data-copy="FIRST_VALUE(score) OVER (PARTITION BY team ORDER BY created_at)">COPY</button></div><p>Get first value in each window.</p></article></div></div></section><section class="section" id="top-n-per-group"><button class="sectionhead" aria-expanded="true"><span>Top-N Per Group</span><small>2 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="top-n per group with x as (select *, row_number() over (partition by department order by salary desc) rn from employees) select * from x where rn <= 3; top 3 salaries per department."><div class="cmdrow"><pre><code>WITH x AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) rn FROM employees) SELECT * FROM x WHERE rn <= 3;</code></pre><button class="copy" data-copy="WITH x AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) rn FROM employees) SELECT * FROM x WHERE rn <= 3;">COPY</button></div><p>Top 3 salaries per department.</p></article><article class="card" data-search="top-n per group with x as (select *, rank() over (partition by category order by score desc) rnk from items) select * from x where rnk = 1; top-ranked rows per category, preserving ties."><div class="cmdrow"><pre><code>WITH x AS (SELECT *, RANK() OVER (PARTITION BY category ORDER BY score DESC) rnk FROM items) SELECT * FROM x WHERE rnk = 1;</code></pre><button class="copy" data-copy="WITH x AS (SELECT *, RANK() OVER (PARTITION BY category ORDER BY score DESC) rnk FROM items) SELECT * FROM x WHERE rnk = 1;">COPY</button></div><p>Top-ranked rows per category, preserving ties.</p></article></div></div></section><section class="section" id="views"><button class="sectionhead" aria-expanded="true"><span>Views</span><small>4 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="views create view active_users as select * from users where active=1; create a view."><div class="cmdrow"><pre><code>CREATE VIEW active_users AS SELECT * FROM users WHERE active=1;</code></pre><button class="copy" data-copy="CREATE VIEW active_users AS SELECT * FROM users WHERE active=1;">COPY</button></div><p>Create a view.</p></article><article class="card" data-search="views create or replace view active_users as select id,name from users where active=1; replace a view where supported."><div class="cmdrow"><pre><code>CREATE OR REPLACE VIEW active_users AS SELECT id,name FROM users WHERE active=1;</code></pre><button class="copy" data-copy="CREATE OR REPLACE VIEW active_users AS SELECT id,name FROM users WHERE active=1;">COPY</button></div><p>Replace a view where supported.</p></article><article class="card" data-search="views select * from active_users; query a view."><div class="cmdrow"><pre><code>SELECT * FROM active_users;</code></pre><button class="copy" data-copy="SELECT * FROM active_users;">COPY</button></div><p>Query a view.</p></article><article class="card" data-search="views drop view active_users; delete a view."><div class="cmdrow"><pre><code>DROP VIEW active_users;</code></pre><button class="copy" data-copy="DROP VIEW active_users;">COPY</button></div><p>Delete a view.</p></article></div></div></section><section class="section" id="indexes"><button class="sectionhead" aria-expanded="true"><span>Indexes</span><small>5 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="indexes create index idx_users_email on users(email); create a basic index."><div class="cmdrow"><pre><code>CREATE INDEX idx_users_email ON users(email);</code></pre><button class="copy" data-copy="CREATE INDEX idx_users_email ON users(email);">COPY</button></div><p>Create a basic index.</p></article><article class="card" data-search="indexes create unique index idx_users_email_uq on users(email); create a unique index."><div class="cmdrow"><pre><code>CREATE UNIQUE INDEX idx_users_email_uq ON users(email);</code></pre><button class="copy" data-copy="CREATE UNIQUE INDEX idx_users_email_uq ON users(email);">COPY</button></div><p>Create a unique index.</p></article><article class="card" data-search="indexes create index idx_orders_user_date on orders(user_id, created_at); create a composite index."><div class="cmdrow"><pre><code>CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);</code></pre><button class="copy" data-copy="CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);">COPY</button></div><p>Create a composite index.</p></article><article class="card" data-search="indexes drop index idx_users_email; drop an index; vendor syntax can vary."><div class="cmdrow"><pre><code>DROP INDEX idx_users_email;</code></pre><button class="copy" data-copy="DROP INDEX idx_users_email;">COPY</button></div><p>Drop an index; vendor syntax can vary.</p></article><article class="card" data-search="indexes create index idx_active_users on users(id) where active=1; create a partial index in postgresql/sqlite."><div class="cmdrow"><pre><code>CREATE INDEX idx_active_users ON users(id) WHERE active=1;</code></pre><button class="copy" data-copy="CREATE INDEX idx_active_users ON users(id) WHERE active=1;">COPY</button></div><p>Create a partial index in PostgreSQL/SQLite.</p></article></div></div></section><section class="section" id="transactions"><button class="sectionhead" aria-expanded="true"><span>Transactions</span><small>8 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="transactions begin; start a transaction in postgresql/sqlite-style syntax."><div class="cmdrow"><pre><code>BEGIN;</code></pre><button class="copy" data-copy="BEGIN;">COPY</button></div><p>Start a transaction in PostgreSQL/SQLite-style syntax.</p></article><article class="card" data-search="transactions start transaction; start a transaction in mysql-style syntax."><div class="cmdrow"><pre><code>START TRANSACTION;</code></pre><button class="copy" data-copy="START TRANSACTION;">COPY</button></div><p>Start a transaction in MySQL-style syntax.</p></article><article class="card" data-search="transactions begin transaction; start a transaction in sql server-style syntax."><div class="cmdrow"><pre><code>BEGIN TRANSACTION;</code></pre><button class="copy" data-copy="BEGIN TRANSACTION;">COPY</button></div><p>Start a transaction in SQL Server-style syntax.</p></article><article class="card" data-search="transactions commit; persist transaction changes."><div class="cmdrow"><pre><code>COMMIT;</code></pre><button class="copy" data-copy="COMMIT;">COPY</button></div><p>Persist transaction changes.</p></article><article class="card" data-search="transactions rollback; undo uncommitted transaction changes."><div class="cmdrow"><pre><code>ROLLBACK;</code></pre><button class="copy" data-copy="ROLLBACK;">COPY</button></div><p>Undo uncommitted transaction changes.</p></article><article class="card" data-search="transactions savepoint before_update; create a savepoint."><div class="cmdrow"><pre><code>SAVEPOINT before_update;</code></pre><button class="copy" data-copy="SAVEPOINT before_update;">COPY</button></div><p>Create a savepoint.</p></article><article class="card" data-search="transactions rollback to savepoint before_update; rollback part of a transaction."><div class="cmdrow"><pre><code>ROLLBACK TO SAVEPOINT before_update;</code></pre><button class="copy" data-copy="ROLLBACK TO SAVEPOINT before_update;">COPY</button></div><p>Rollback part of a transaction.</p></article><article class="card" data-search="transactions release savepoint before_update; release a savepoint."><div class="cmdrow"><pre><code>RELEASE SAVEPOINT before_update;</code></pre><button class="copy" data-copy="RELEASE SAVEPOINT before_update;">COPY</button></div><p>Release a savepoint.</p></article></div></div></section><section class="section" id="isolation---locking"><button class="sectionhead" aria-expanded="true"><span>Isolation & Locking</span><small>5 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="isolation & locking set transaction isolation level read committed; set common isolation level."><div class="cmdrow"><pre><code>SET TRANSACTION ISOLATION LEVEL READ COMMITTED;</code></pre><button class="copy" data-copy="SET TRANSACTION ISOLATION LEVEL READ COMMITTED;">COPY</button></div><p>Set common isolation level.</p></article><article class="card" data-search="isolation & locking set transaction isolation level repeatable read; use repeatable-read isolation."><div class="cmdrow"><pre><code>SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;</code></pre><button class="copy" data-copy="SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;">COPY</button></div><p>Use repeatable-read isolation.</p></article><article class="card" data-search="isolation & locking set transaction isolation level serializable; use strongest common isolation level."><div class="cmdrow"><pre><code>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;</code></pre><button class="copy" data-copy="SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;">COPY</button></div><p>Use strongest common isolation level.</p></article><article class="card" data-search="isolation & locking select * from users where id=1 for update; lock selected rows for update on supported databases."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE id=1 FOR UPDATE;</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE id=1 FOR UPDATE;">COPY</button></div><p>Lock selected rows for update on supported databases.</p></article><article class="card" data-search="isolation & locking select * from jobs where status='new' for update skip locked; lock work rows while skipping rows locked elsewhere on supported databases."><div class="cmdrow"><pre><code>SELECT * FROM jobs WHERE status='new' FOR UPDATE SKIP LOCKED;</code></pre><button class="copy" data-copy="SELECT * FROM jobs WHERE status='new' FOR UPDATE SKIP LOCKED;">COPY</button></div><p>Lock work rows while skipping rows locked elsewhere on supported databases.</p></article></div></div></section><section class="section" id="keys---relationships"><button class="sectionhead" aria-expanded="true"><span>Keys & Relationships</span><small>6 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="keys & relationships primary key uniquely identifies a row."><div class="cmdrow"><pre><code>PRIMARY KEY</code></pre><button class="copy" data-copy="PRIMARY KEY">COPY</button></div><p>Uniquely identifies a row.</p></article><article class="card" data-search="keys & relationships foreign key references a key in another table."><div class="cmdrow"><pre><code>FOREIGN KEY</code></pre><button class="copy" data-copy="FOREIGN KEY">COPY</button></div><p>References a key in another table.</p></article><article class="card" data-search="keys & relationships unique candidate key or uniqueness rule."><div class="cmdrow"><pre><code>UNIQUE</code></pre><button class="copy" data-copy="UNIQUE">COPY</button></div><p>Candidate key or uniqueness rule.</p></article><article class="card" data-search="keys & relationships on delete cascade cascade parent deletions to children."><div class="cmdrow"><pre><code>ON DELETE CASCADE</code></pre><button class="copy" data-copy="ON DELETE CASCADE">COPY</button></div><p>Cascade parent deletions to children.</p></article><article class="card" data-search="keys & relationships on update cascade cascade key updates where supported."><div class="cmdrow"><pre><code>ON UPDATE CASCADE</code></pre><button class="copy" data-copy="ON UPDATE CASCADE">COPY</button></div><p>Cascade key updates where supported.</p></article><article class="card" data-search="keys & relationships foreign key (manager_id) references employees(id) self-referencing relationship."><div class="cmdrow"><pre><code>FOREIGN KEY (manager_id) REFERENCES employees(id)</code></pre><button class="copy" data-copy="FOREIGN KEY (manager_id) REFERENCES employees(id)">COPY</button></div><p>Self-referencing relationship.</p></article></div></div></section><section class="section" id="normalization-notes"><button class="sectionhead" aria-expanded="true"><span>Normalization Notes</span><small>7 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="normalization notes 1nf atomic column values and no repeating groups."><div class="cmdrow"><pre><code>1NF</code></pre><button class="copy" data-copy="1NF">COPY</button></div><p>Atomic column values and no repeating groups.</p></article><article class="card" data-search="normalization notes 2nf 1nf plus no partial dependency on a composite key."><div class="cmdrow"><pre><code>2NF</code></pre><button class="copy" data-copy="2NF">COPY</button></div><p>1NF plus no partial dependency on a composite key.</p></article><article class="card" data-search="normalization notes 3nf 2nf plus no transitive dependency on non-key attributes."><div class="cmdrow"><pre><code>3NF</code></pre><button class="copy" data-copy="3NF">COPY</button></div><p>2NF plus no transitive dependency on non-key attributes.</p></article><article class="card" data-search="normalization notes bcnf every determinant is a candidate key."><div class="cmdrow"><pre><code>BCNF</code></pre><button class="copy" data-copy="BCNF">COPY</button></div><p>Every determinant is a candidate key.</p></article><article class="card" data-search="normalization notes junction table use for many-to-many relationships."><div class="cmdrow"><pre><code>junction table</code></pre><button class="copy" data-copy="junction table">COPY</button></div><p>Use for many-to-many relationships.</p></article><article class="card" data-search="normalization notes surrogate key artificial identifier such as an identity integer or uuid."><div class="cmdrow"><pre><code>surrogate key</code></pre><button class="copy" data-copy="surrogate key">COPY</button></div><p>Artificial identifier such as an identity integer or UUID.</p></article><article class="card" data-search="normalization notes natural key meaningful real-world key such as a unique code."><div class="cmdrow"><pre><code>natural key</code></pre><button class="copy" data-copy="natural key">COPY</button></div><p>Meaningful real-world key such as a unique code.</p></article></div></div></section><section class="section" id="stored-procedures---functions"><button class="sectionhead" aria-expanded="true"><span>Stored Procedures & Functions</span><small>6 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="stored procedures & functions create procedure ... create a stored procedure; exact syntax is vendor-specific."><div class="cmdrow"><pre><code>CREATE PROCEDURE ...</code></pre><button class="copy" data-copy="CREATE PROCEDURE ...">COPY</button></div><p>Create a stored procedure; exact syntax is vendor-specific.</p></article><article class="card" data-search="stored procedures & functions create function ... returns ... create a stored function; exact syntax is vendor-specific."><div class="cmdrow"><pre><code>CREATE FUNCTION ... RETURNS ...</code></pre><button class="copy" data-copy="CREATE FUNCTION ... RETURNS ...">COPY</button></div><p>Create a stored function; exact syntax is vendor-specific.</p></article><article class="card" data-search="stored procedures & functions call procedure_name(...); invoke a stored procedure in mysql/postgresql-style environments."><div class="cmdrow"><pre><code>CALL procedure_name(...);</code></pre><button class="copy" data-copy="CALL procedure_name(...);">COPY</button></div><p>Invoke a stored procedure in MySQL/PostgreSQL-style environments.</p></article><article class="card" data-search="stored procedures & functions exec procedure_name ...; invoke a procedure in sql server."><div class="cmdrow"><pre><code>EXEC procedure_name ...;</code></pre><button class="copy" data-copy="EXEC procedure_name ...;">COPY</button></div><p>Invoke a procedure in SQL Server.</p></article><article class="card" data-search="stored procedures & functions drop procedure procedure_name; delete a stored procedure."><div class="cmdrow"><pre><code>DROP PROCEDURE procedure_name;</code></pre><button class="copy" data-copy="DROP PROCEDURE procedure_name;">COPY</button></div><p>Delete a stored procedure.</p></article><article class="card" data-search="stored procedures & functions drop function function_name; delete a stored function."><div class="cmdrow"><pre><code>DROP FUNCTION function_name;</code></pre><button class="copy" data-copy="DROP FUNCTION function_name;">COPY</button></div><p>Delete a stored function.</p></article></div></div></section><section class="section" id="triggers"><button class="sectionhead" aria-expanded="true"><span>Triggers</span><small>6 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="triggers create trigger ... create a trigger; syntax varies by database."><div class="cmdrow"><pre><code>CREATE TRIGGER ...</code></pre><button class="copy" data-copy="CREATE TRIGGER ...">COPY</button></div><p>Create a trigger; syntax varies by database.</p></article><article class="card" data-search="triggers before insert trigger timing/event pattern."><div class="cmdrow"><pre><code>BEFORE INSERT</code></pre><button class="copy" data-copy="BEFORE INSERT">COPY</button></div><p>Trigger timing/event pattern.</p></article><article class="card" data-search="triggers after update trigger after rows are updated."><div class="cmdrow"><pre><code>AFTER UPDATE</code></pre><button class="copy" data-copy="AFTER UPDATE">COPY</button></div><p>Trigger after rows are updated.</p></article><article class="card" data-search="triggers old.column_name reference old row values in several sql dialects."><div class="cmdrow"><pre><code>OLD.column_name</code></pre><button class="copy" data-copy="OLD.column_name">COPY</button></div><p>Reference old row values in several SQL dialects.</p></article><article class="card" data-search="triggers new.column_name reference new row values in several sql dialects."><div class="cmdrow"><pre><code>NEW.column_name</code></pre><button class="copy" data-copy="NEW.column_name">COPY</button></div><p>Reference new row values in several SQL dialects.</p></article><article class="card" data-search="triggers drop trigger trigger_name; delete a trigger; syntax varies."><div class="cmdrow"><pre><code>DROP TRIGGER trigger_name;</code></pre><button class="copy" data-copy="DROP TRIGGER trigger_name;">COPY</button></div><p>Delete a trigger; syntax varies.</p></article></div></div></section><section class="section" id="permissions---security"><button class="sectionhead" aria-expanded="true"><span>Permissions & Security</span><small>6 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="permissions & security grant select on users to analyst; grant read permission."><div class="cmdrow"><pre><code>GRANT SELECT ON users TO analyst;</code></pre><button class="copy" data-copy="GRANT SELECT ON users TO analyst;">COPY</button></div><p>Grant read permission.</p></article><article class="card" data-search="permissions & security grant insert, update on users to app_user; grant write permissions."><div class="cmdrow"><pre><code>GRANT INSERT, UPDATE ON users TO app_user;</code></pre><button class="copy" data-copy="GRANT INSERT, UPDATE ON users TO app_user;">COPY</button></div><p>Grant write permissions.</p></article><article class="card" data-search="permissions & security revoke update on users from app_user; remove permission."><div class="cmdrow"><pre><code>REVOKE UPDATE ON users FROM app_user;</code></pre><button class="copy" data-copy="REVOKE UPDATE ON users FROM app_user;">COPY</button></div><p>Remove permission.</p></article><article class="card" data-search="permissions & security grant all privileges on database_name.* to 'user'@'host'; mysql-style broad grant."><div class="cmdrow"><pre><code>GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'host';</code></pre><button class="copy" data-copy="GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'host';">COPY</button></div><p>MySQL-style broad grant.</p></article><article class="card" data-search="permissions & security create role analyst; create a role where supported."><div class="cmdrow"><pre><code>CREATE ROLE analyst;</code></pre><button class="copy" data-copy="CREATE ROLE analyst;">COPY</button></div><p>Create a role where supported.</p></article><article class="card" data-search="permissions & security grant analyst to alice; grant a role to a user on supported systems."><div class="cmdrow"><pre><code>GRANT analyst TO alice;</code></pre><button class="copy" data-copy="GRANT analyst TO alice;">COPY</button></div><p>Grant a role to a user on supported systems.</p></article></div></div></section><section class="section" id="schema---database-management"><button class="sectionhead" aria-expanded="true"><span>Schema & Database Management</span><small>5 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="schema & database management create database appdb; create a database."><div class="cmdrow"><pre><code>CREATE DATABASE appdb;</code></pre><button class="copy" data-copy="CREATE DATABASE appdb;">COPY</button></div><p>Create a database.</p></article><article class="card" data-search="schema & database management create schema reporting; create a schema."><div class="cmdrow"><pre><code>CREATE SCHEMA reporting;</code></pre><button class="copy" data-copy="CREATE SCHEMA reporting;">COPY</button></div><p>Create a schema.</p></article><article class="card" data-search="schema & database management set search_path to reporting, public; set postgresql schema search path."><div class="cmdrow"><pre><code>SET search_path TO reporting, public;</code></pre><button class="copy" data-copy="SET search_path TO reporting, public;">COPY</button></div><p>Set PostgreSQL schema search path.</p></article><article class="card" data-search="schema & database management use appdb; select a database in mysql/sql server."><div class="cmdrow"><pre><code>USE appdb;</code></pre><button class="copy" data-copy="USE appdb;">COPY</button></div><p>Select a database in MySQL/SQL Server.</p></article><article class="card warning" data-search="schema & database management drop schema reporting cascade; delete a schema and dependent objects in postgresql. ⚠"><div class="cmdrow"><pre><code>DROP SCHEMA reporting CASCADE;</code></pre><button class="copy" data-copy="DROP SCHEMA reporting CASCADE;">COPY</button></div><p>Delete a schema and dependent objects in PostgreSQL. ⚠</p></article></div></div></section><section class="section" id="metadata---introspection"><button class="sectionhead" aria-expanded="true"><span>Metadata & Introspection</span><small>7 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="metadata & introspection select * from information_schema.tables; list table metadata in standard information schema."><div class="cmdrow"><pre><code>SELECT * FROM information_schema.tables;</code></pre><button class="copy" data-copy="SELECT * FROM information_schema.tables;">COPY</button></div><p>List table metadata in standard information schema.</p></article><article class="card" data-search="metadata & introspection select * from information_schema.columns where table_name='users'; inspect columns."><div class="cmdrow"><pre><code>SELECT * FROM information_schema.columns WHERE table_name='users';</code></pre><button class="copy" data-copy="SELECT * FROM information_schema.columns WHERE table_name='users';">COPY</button></div><p>Inspect columns.</p></article><article class="card" data-search="metadata & introspection select * from information_schema.table_constraints where table_name='users'; inspect constraints."><div class="cmdrow"><pre><code>SELECT * FROM information_schema.table_constraints WHERE table_name='users';</code></pre><button class="copy" data-copy="SELECT * FROM information_schema.table_constraints WHERE table_name='users';">COPY</button></div><p>Inspect constraints.</p></article><article class="card" data-search="metadata & introspection pragma table_info(users); inspect sqlite table columns."><div class="cmdrow"><pre><code>PRAGMA table_info(users);</code></pre><button class="copy" data-copy="PRAGMA table_info(users);">COPY</button></div><p>Inspect SQLite table columns.</p></article><article class="card" data-search="metadata & introspection show tables; list tables in mysql."><div class="cmdrow"><pre><code>SHOW TABLES;</code></pre><button class="copy" data-copy="SHOW TABLES;">COPY</button></div><p>List tables in MySQL.</p></article><article class="card" data-search="metadata & introspection describe users; describe table columns in mysql."><div class="cmdrow"><pre><code>DESCRIBE users;</code></pre><button class="copy" data-copy="DESCRIBE users;">COPY</button></div><p>Describe table columns in MySQL.</p></article><article class="card" data-search="metadata & introspection \d users describe a table in postgresql psql."><div class="cmdrow"><pre><code>\d users</code></pre><button class="copy" data-copy="\d users">COPY</button></div><p>Describe a table in PostgreSQL psql.</p></article></div></div></section><section class="section" id="query-plans---performance"><button class="sectionhead" aria-expanded="true"><span>Query Plans & Performance</span><small>6 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="query plans & performance explain select * from users where email='[email protected]'; show query plan."><div class="cmdrow"><pre><code>EXPLAIN SELECT * FROM users WHERE email='[email protected]';</code></pre><button class="copy" data-copy="EXPLAIN SELECT * FROM users WHERE email='[email protected]';">COPY</button></div><p>Show query plan.</p></article><article class="card" data-search="query plans & performance explain analyze select * from users where email='[email protected]'; execute and show actual plan/runtime on supported databases."><div class="cmdrow"><pre><code>EXPLAIN ANALYZE SELECT * FROM users WHERE email='[email protected]';</code></pre><button class="copy" data-copy="EXPLAIN ANALYZE SELECT * FROM users WHERE email='[email protected]';">COPY</button></div><p>Execute and show actual plan/runtime on supported databases.</p></article><article class="card" data-search="query plans & performance set statistics io on; show i/o statistics in sql server."><div class="cmdrow"><pre><code>SET STATISTICS IO ON;</code></pre><button class="copy" data-copy="SET STATISTICS IO ON;">COPY</button></div><p>Show I/O statistics in SQL Server.</p></article><article class="card" data-search="query plans & performance set statistics time on; show timing information in sql server."><div class="cmdrow"><pre><code>SET STATISTICS TIME ON;</code></pre><button class="copy" data-copy="SET STATISTICS TIME ON;">COPY</button></div><p>Show timing information in SQL Server.</p></article><article class="card" data-search="query plans & performance analyze users; refresh planner statistics on supported databases."><div class="cmdrow"><pre><code>ANALYZE users;</code></pre><button class="copy" data-copy="ANALYZE users;">COPY</button></div><p>Refresh planner statistics on supported databases.</p></article><article class="card" data-search="query plans & performance vacuum; reclaim/organize storage in postgresql/sqlite-style systems; behavior differs."><div class="cmdrow"><pre><code>VACUUM;</code></pre><button class="copy" data-copy="VACUUM;">COPY</button></div><p>Reclaim/organize storage in PostgreSQL/SQLite-style systems; behavior differs.</p></article></div></div></section><section class="section" id="mysql-notes"><button class="sectionhead" aria-expanded="true"><span>MySQL Notes</span><small>8 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="mysql notes select version(); show mysql version."><div class="cmdrow"><pre><code>SELECT VERSION();</code></pre><button class="copy" data-copy="SELECT VERSION();">COPY</button></div><p>Show MySQL version.</p></article><article class="card" data-search="mysql notes show databases; list databases."><div class="cmdrow"><pre><code>SHOW DATABASES;</code></pre><button class="copy" data-copy="SHOW DATABASES;">COPY</button></div><p>List databases.</p></article><article class="card" data-search="mysql notes show tables; list tables in current database."><div class="cmdrow"><pre><code>SHOW TABLES;</code></pre><button class="copy" data-copy="SHOW TABLES;">COPY</button></div><p>List tables in current database.</p></article><article class="card" data-search="mysql notes show create table users; show table ddl."><div class="cmdrow"><pre><code>SHOW CREATE TABLE users;</code></pre><button class="copy" data-copy="SHOW CREATE TABLE users;">COPY</button></div><p>Show table DDL.</p></article><article class="card" data-search="mysql notes auto_increment mysql identity/auto-number keyword."><div class="cmdrow"><pre><code>AUTO_INCREMENT</code></pre><button class="copy" data-copy="AUTO_INCREMENT">COPY</button></div><p>MySQL identity/auto-number keyword.</p></article><article class="card" data-search="mysql notes ifnull(value, 0) mysql-style null replacement."><div class="cmdrow"><pre><code>IFNULL(value, 0)</code></pre><button class="copy" data-copy="IFNULL(value, 0)">COPY</button></div><p>MySQL-style NULL replacement.</p></article><article class="card" data-search="mysql notes date_format(created_at, '%y-%m-%d') format a date in mysql."><div class="cmdrow"><pre><code>DATE_FORMAT(created_at, '%Y-%m-%d')</code></pre><button class="copy" data-copy="DATE_FORMAT(created_at, '%Y-%m-%d')">COPY</button></div><p>Format a date in MySQL.</p></article><article class="card" data-search="mysql notes insert ... on duplicate key update ... mysql upsert pattern."><div class="cmdrow"><pre><code>INSERT ... ON DUPLICATE KEY UPDATE ...</code></pre><button class="copy" data-copy="INSERT ... ON DUPLICATE KEY UPDATE ...">COPY</button></div><p>MySQL upsert pattern.</p></article></div></div></section><section class="section" id="postgresql-notes"><button class="sectionhead" aria-expanded="true"><span>PostgreSQL Notes</span><small>9 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="postgresql notes select version(); show postgresql version."><div class="cmdrow"><pre><code>SELECT version();</code></pre><button class="copy" data-copy="SELECT version();">COPY</button></div><p>Show PostgreSQL version.</p></article><article class="card" data-search="postgresql notes \l list databases in psql."><div class="cmdrow"><pre><code>\l</code></pre><button class="copy" data-copy="\l">COPY</button></div><p>List databases in psql.</p></article><article class="card" data-search="postgresql notes \dt list tables in psql."><div class="cmdrow"><pre><code>\dt</code></pre><button class="copy" data-copy="\dt">COPY</button></div><p>List tables in psql.</p></article><article class="card" data-search="postgresql notes \d users describe a table in psql."><div class="cmdrow"><pre><code>\d users</code></pre><button class="copy" data-copy="\d users">COPY</button></div><p>Describe a table in psql.</p></article><article class="card" data-search="postgresql notes returning * return affected rows from insert/update/delete."><div class="cmdrow"><pre><code>RETURNING *</code></pre><button class="copy" data-copy="RETURNING *">COPY</button></div><p>Return affected rows from INSERT/UPDATE/DELETE.</p></article><article class="card" data-search="postgresql notes ilike '%text%' case-insensitive pattern match."><div class="cmdrow"><pre><code>ILIKE '%text%'</code></pre><button class="copy" data-copy="ILIKE '%text%'">COPY</button></div><p>Case-insensitive pattern match.</p></article><article class="card" data-search="postgresql notes insert ... on conflict (...) do update ... postgresql upsert pattern."><div class="cmdrow"><pre><code>INSERT ... ON CONFLICT (...) DO UPDATE ...</code></pre><button class="copy" data-copy="INSERT ... ON CONFLICT (...) DO UPDATE ...">COPY</button></div><p>PostgreSQL upsert pattern.</p></article><article class="card" data-search="postgresql notes generated always as identity preferred modern postgresql identity syntax."><div class="cmdrow"><pre><code>GENERATED ALWAYS AS IDENTITY</code></pre><button class="copy" data-copy="GENERATED ALWAYS AS IDENTITY">COPY</button></div><p>Preferred modern PostgreSQL identity syntax.</p></article><article class="card" data-search="postgresql notes jsonb binary json type with indexing capabilities."><div class="cmdrow"><pre><code>jsonb</code></pre><button class="copy" data-copy="jsonb">COPY</button></div><p>Binary JSON type with indexing capabilities.</p></article></div></div></section><section class="section" id="sql-server-notes"><button class="sectionhead" aria-expanded="true"><span>SQL Server Notes</span><small>9 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="sql server notes select @@version; show sql server version."><div class="cmdrow"><pre><code>SELECT @@VERSION;</code></pre><button class="copy" data-copy="SELECT @@VERSION;">COPY</button></div><p>Show SQL Server version.</p></article><article class="card" data-search="sql server notes select top 10 * from users; limit rows."><div class="cmdrow"><pre><code>SELECT TOP 10 * FROM users;</code></pre><button class="copy" data-copy="SELECT TOP 10 * FROM users;">COPY</button></div><p>Limit rows.</p></article><article class="card" data-search="sql server notes identity(1,1) auto-numbering column property."><div class="cmdrow"><pre><code>IDENTITY(1,1)</code></pre><button class="copy" data-copy="IDENTITY(1,1)">COPY</button></div><p>Auto-numbering column property.</p></article><article class="card" data-search="sql server notes getdate() current date/time."><div class="cmdrow"><pre><code>GETDATE()</code></pre><button class="copy" data-copy="GETDATE()">COPY</button></div><p>Current date/time.</p></article><article class="card" data-search="sql server notes isnull(value, 0) replace null with a fallback."><div class="cmdrow"><pre><code>ISNULL(value, 0)</code></pre><button class="copy" data-copy="ISNULL(value, 0)">COPY</button></div><p>Replace NULL with a fallback.</p></article><article class="card" data-search="sql server notes len(name) string length."><div class="cmdrow"><pre><code>LEN(name)</code></pre><button class="copy" data-copy="LEN(name)">COPY</button></div><p>String length.</p></article><article class="card" data-search="sql server notes string_agg(name, ',') aggregate strings."><div class="cmdrow"><pre><code>STRING_AGG(name, ',')</code></pre><button class="copy" data-copy="STRING_AGG(name, ',')">COPY</button></div><p>Aggregate strings.</p></article><article class="card" data-search="sql server notes merge ... sql server merge/upsert-style statement; use carefully."><div class="cmdrow"><pre><code>MERGE ...</code></pre><button class="copy" data-copy="MERGE ...">COPY</button></div><p>SQL Server merge/upsert-style statement; use carefully.</p></article><article class="card" data-search="sql server notes exec sp_help 'users'; show object information."><div class="cmdrow"><pre><code>EXEC sp_help 'users';</code></pre><button class="copy" data-copy="EXEC sp_help 'users';">COPY</button></div><p>Show object information.</p></article></div></div></section><section class="section" id="sqlite-notes"><button class="sectionhead" aria-expanded="true"><span>SQLite Notes</span><small>8 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="sqlite notes select sqlite_version(); show sqlite version."><div class="cmdrow"><pre><code>SELECT sqlite_version();</code></pre><button class="copy" data-copy="SELECT sqlite_version();">COPY</button></div><p>Show SQLite version.</p></article><article class="card" data-search="sqlite notes .tables list tables in sqlite3 shell."><div class="cmdrow"><pre><code>.tables</code></pre><button class="copy" data-copy=".tables">COPY</button></div><p>List tables in sqlite3 shell.</p></article><article class="card" data-search="sqlite notes .schema users show table schema in sqlite3 shell."><div class="cmdrow"><pre><code>.schema users</code></pre><button class="copy" data-copy=".schema users">COPY</button></div><p>Show table schema in sqlite3 shell.</p></article><article class="card" data-search="sqlite notes pragma table_info(users); inspect columns."><div class="cmdrow"><pre><code>PRAGMA table_info(users);</code></pre><button class="copy" data-copy="PRAGMA table_info(users);">COPY</button></div><p>Inspect columns.</p></article><article class="card" data-search="sqlite notes integer primary key aliases sqlite rowid behavior."><div class="cmdrow"><pre><code>INTEGER PRIMARY KEY</code></pre><button class="copy" data-copy="INTEGER PRIMARY KEY">COPY</button></div><p>Aliases SQLite rowid behavior.</p></article><article class="card" data-search="sqlite notes insert or replace into ... sqlite replacement/upsert-style syntax."><div class="cmdrow"><pre><code>INSERT OR REPLACE INTO ...</code></pre><button class="copy" data-copy="INSERT OR REPLACE INTO ...">COPY</button></div><p>SQLite replacement/upsert-style syntax.</p></article><article class="card" data-search="sqlite notes insert ... on conflict(...) do update set ... sqlite upsert syntax on modern versions."><div class="cmdrow"><pre><code>INSERT ... ON CONFLICT(...) DO UPDATE SET ...</code></pre><button class="copy" data-copy="INSERT ... ON CONFLICT(...) DO UPDATE SET ...">COPY</button></div><p>SQLite UPSERT syntax on modern versions.</p></article><article class="card" data-search="sqlite notes datetime('now') current utc date/time string."><div class="cmdrow"><pre><code>datetime('now')</code></pre><button class="copy" data-copy="datetime('now')">COPY</button></div><p>Current UTC date/time string.</p></article></div></div></section><section class="section" id="common-admin-commands"><button class="sectionhead" aria-expanded="true"><span>Common Admin Commands</span><small>7 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="common admin commands backup database ... sql server backup statement; syntax depends on destination."><div class="cmdrow"><pre><code>BACKUP DATABASE ...</code></pre><button class="copy" data-copy="BACKUP DATABASE ...">COPY</button></div><p>SQL Server backup statement; syntax depends on destination.</p></article><article class="card" data-search="common admin commands restore database ... sql server restore statement."><div class="cmdrow"><pre><code>RESTORE DATABASE ...</code></pre><button class="copy" data-copy="RESTORE DATABASE ...">COPY</button></div><p>SQL Server restore statement.</p></article><article class="card" data-search="common admin commands pg_dump appdb > backup.sql postgresql logical backup from shell."><div class="cmdrow"><pre><code>pg_dump appdb > backup.sql</code></pre><button class="copy" data-copy="pg_dump appdb > backup.sql">COPY</button></div><p>PostgreSQL logical backup from shell.</p></article><article class="card" data-search="common admin commands psql appdb < backup.sql restore postgresql sql dump from shell."><div class="cmdrow"><pre><code>psql appdb < backup.sql</code></pre><button class="copy" data-copy="psql appdb < backup.sql">COPY</button></div><p>Restore PostgreSQL SQL dump from shell.</p></article><article class="card" data-search="common admin commands mysqldump appdb > backup.sql mysql logical backup from shell."><div class="cmdrow"><pre><code>mysqldump appdb > backup.sql</code></pre><button class="copy" data-copy="mysqldump appdb > backup.sql">COPY</button></div><p>MySQL logical backup from shell.</p></article><article class="card" data-search="common admin commands mysql appdb < backup.sql restore mysql sql dump from shell."><div class="cmdrow"><pre><code>mysql appdb < backup.sql</code></pre><button class="copy" data-copy="mysql appdb < backup.sql">COPY</button></div><p>Restore MySQL SQL dump from shell.</p></article><article class="card" data-search="common admin commands .backup backup.db sqlite shell backup command."><div class="cmdrow"><pre><code>.backup backup.db</code></pre><button class="copy" data-copy=".backup backup.db">COPY</button></div><p>SQLite shell backup command.</p></article></div></div></section><section class="section" id="useful-query-patterns"><button class="sectionhead" aria-expanded="true"><span>Useful Query Patterns</span><small>9 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="useful query patterns select * from users where created_at >= current_date - interval '7 days'; rows from the last seven days in postgresql-style syntax."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';">COPY</button></div><p>Rows from the last seven days in PostgreSQL-style syntax.</p></article><article class="card" data-search="useful query patterns select email, count(*) from users group by email having count(*) > 1; find duplicate emails."><div class="cmdrow"><pre><code>SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;</code></pre><button class="copy" data-copy="SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;">COPY</button></div><p>Find duplicate emails.</p></article><article class="card warning" data-search="useful query patterns delete from users where id not in (select min(id) from users group by email); simple duplicate-removal pattern. ⚠ verify carefully before running."><div class="cmdrow"><pre><code>DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);</code></pre><button class="copy" data-copy="DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);">COPY</button></div><p>Simple duplicate-removal pattern. ⚠ Verify carefully before running.</p></article><article class="card" data-search="useful query patterns select * from users order by random() limit 1; random row in postgresql/sqlite."><div class="cmdrow"><pre><code>SELECT * FROM users ORDER BY RANDOM() LIMIT 1;</code></pre><button class="copy" data-copy="SELECT * FROM users ORDER BY RANDOM() LIMIT 1;">COPY</button></div><p>Random row in PostgreSQL/SQLite.</p></article><article class="card" data-search="useful query patterns select * from users order by rand() limit 1; random row in mysql."><div class="cmdrow"><pre><code>SELECT * FROM users ORDER BY RAND() LIMIT 1;</code></pre><button class="copy" data-copy="SELECT * FROM users ORDER BY RAND() LIMIT 1;">COPY</button></div><p>Random row in MySQL.</p></article><article class="card" data-search="useful query patterns select * from users order by newid(); randomized order in sql server."><div class="cmdrow"><pre><code>SELECT * FROM users ORDER BY NEWID();</code></pre><button class="copy" data-copy="SELECT * FROM users ORDER BY NEWID();">COPY</button></div><p>Randomized order in SQL Server.</p></article><article class="card" data-search="useful query patterns select department, count(*) from employees group by department order by count(*) desc; count rows by category."><div class="cmdrow"><pre><code>SELECT department, COUNT(*) FROM employees GROUP BY department ORDER BY COUNT(*) DESC;</code></pre><button class="copy" data-copy="SELECT department, COUNT(*) FROM employees GROUP BY department ORDER BY COUNT(*) DESC;">COPY</button></div><p>Count rows by category.</p></article><article class="card" data-search="useful query patterns select date(created_at), count(*) from events group by date(created_at); daily counts on databases supporting date()."><div class="cmdrow"><pre><code>SELECT DATE(created_at), COUNT(*) FROM events GROUP BY DATE(created_at);</code></pre><button class="copy" data-copy="SELECT DATE(created_at), COUNT(*) FROM events GROUP BY DATE(created_at);">COPY</button></div><p>Daily counts on databases supporting DATE().</p></article><article class="card" data-search="useful query patterns select user_id, sum(total) as lifetime_value from orders group by user_id order by lifetime_value desc; rank users by total order value."><div class="cmdrow"><pre><code>SELECT user_id, SUM(total) AS lifetime_value FROM orders GROUP BY user_id ORDER BY lifetime_value DESC;</code></pre><button class="copy" data-copy="SELECT user_id, SUM(total) AS lifetime_value FROM orders GROUP BY user_id ORDER BY lifetime_value DESC;">COPY</button></div><p>Rank users by total order value.</p></article></div></div></section><section class="section" id="common-pitfalls"><button class="sectionhead" aria-expanded="true"><span>Common Pitfalls</span><small>8 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="common pitfalls where column = null wrong: use is null because null is not equal to anything."><div class="cmdrow"><pre><code>WHERE column = NULL</code></pre><button class="copy" data-copy="WHERE column = NULL">COPY</button></div><p>Wrong: use IS NULL because NULL is not equal to anything.</p></article><article class="card" data-search="common pitfalls select * convenient, but avoid it in stable production interfaces when you only need specific columns."><div class="cmdrow"><pre><code>SELECT *</code></pre><button class="copy" data-copy="SELECT *">COPY</button></div><p>Convenient, but avoid it in stable production interfaces when you only need specific columns.</p></article><article class="card warning" data-search="common pitfalls update users set active=0; missing where updates every row. ⚠"><div class="cmdrow"><pre><code>UPDATE users SET active=0;</code></pre><button class="copy" data-copy="UPDATE users SET active=0;">COPY</button></div><p>Missing WHERE updates every row. ⚠</p></article><article class="card warning" data-search="common pitfalls delete from users; missing where deletes every row. ⚠"><div class="cmdrow"><pre><code>DELETE FROM users;</code></pre><button class="copy" data-copy="DELETE FROM users;">COPY</button></div><p>Missing WHERE deletes every row. ⚠</p></article><article class="card" data-search="common pitfalls not in (subquery_with_nulls) nulls can make not in behave unexpectedly; not exists is often safer."><div class="cmdrow"><pre><code>NOT IN (subquery_with_nulls)</code></pre><button class="copy" data-copy="NOT IN (subquery_with_nulls)">COPY</button></div><p>NULLs can make NOT IN behave unexpectedly; NOT EXISTS is often safer.</p></article><article class="card" data-search="common pitfalls float for money prefer decimal/numeric for exact financial values."><div class="cmdrow"><pre><code>FLOAT for money</code></pre><button class="copy" data-copy="FLOAT for money">COPY</button></div><p>Prefer DECIMAL/NUMERIC for exact financial values.</p></article><article class="card" data-search="common pitfalls function(column) in where wrapping indexed columns in functions can prevent index use."><div class="cmdrow"><pre><code>function(column) in WHERE</code></pre><button class="copy" data-copy="function(column) in WHERE">COPY</button></div><p>Wrapping indexed columns in functions can prevent index use.</p></article><article class="card" data-search="common pitfalls offset pagination on huge tables large offsets can be slow; keyset/seek pagination is often better."><div class="cmdrow"><pre><code>OFFSET pagination on huge tables</code></pre><button class="copy" data-copy="OFFSET pagination on huge tables">COPY</button></div><p>Large offsets can be slow; keyset/seek pagination is often better.</p></article></div></div></section><section class="section" id="useful-one-liners"><button class="sectionhead" aria-expanded="true"><span>Useful One-Liners</span><small>9 entries</small><b>▾</b></button><div class="body"><div class="grid"><article class="card" data-search="useful one-liners select count(*) from table_name; count rows."><div class="cmdrow"><pre><code>SELECT COUNT(*) FROM table_name;</code></pre><button class="copy" data-copy="SELECT COUNT(*) FROM table_name;">COPY</button></div><p>Count rows.</p></article><article class="card" data-search="useful one-liners select distinct column_name from table_name; list unique values."><div class="cmdrow"><pre><code>SELECT DISTINCT column_name FROM table_name;</code></pre><button class="copy" data-copy="SELECT DISTINCT column_name FROM table_name;">COPY</button></div><p>List unique values.</p></article><article class="card" data-search="useful one-liners select column_name, count(*) from table_name group by column_name order by count(*) desc; frequency table."><div class="cmdrow"><pre><code>SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name ORDER BY COUNT(*) DESC;</code></pre><button class="copy" data-copy="SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name ORDER BY COUNT(*) DESC;">COPY</button></div><p>Frequency table.</p></article><article class="card" data-search="useful one-liners select * from table_name order by created_at desc limit 1; newest row in limit-capable databases."><div class="cmdrow"><pre><code>SELECT * FROM table_name ORDER BY created_at DESC LIMIT 1;</code></pre><button class="copy" data-copy="SELECT * FROM table_name ORDER BY created_at DESC LIMIT 1;">COPY</button></div><p>Newest row in LIMIT-capable databases.</p></article><article class="card" data-search="useful one-liners select min(value), max(value), avg(value) from table_name; quick numeric summary."><div class="cmdrow"><pre><code>SELECT MIN(value), MAX(value), AVG(value) FROM table_name;</code></pre><button class="copy" data-copy="SELECT MIN(value), MAX(value), AVG(value) FROM table_name;">COPY</button></div><p>Quick numeric summary.</p></article><article class="card" data-search="useful one-liners select * from users where name like '%test%'; simple contains search."><div class="cmdrow"><pre><code>SELECT * FROM users WHERE name LIKE '%test%';</code></pre><button class="copy" data-copy="SELECT * FROM users WHERE name LIKE '%test%';">COPY</button></div><p>Simple contains search.</p></article><article class="card" data-search="useful one-liners select table_name from information_schema.tables where table_schema='public'; list postgresql public-schema tables."><div class="cmdrow"><pre><code>SELECT table_name FROM information_schema.tables WHERE table_schema='public';</code></pre><button class="copy" data-copy="SELECT table_name FROM information_schema.tables WHERE table_schema='public';">COPY</button></div><p>List PostgreSQL public-schema tables.</p></article><article class="card" data-search="useful one-liners select name from sqlite_master where type='table'; list sqlite tables."><div class="cmdrow"><pre><code>SELECT name FROM sqlite_master WHERE type='table';</code></pre><button class="copy" data-copy="SELECT name FROM sqlite_master WHERE type='table';">COPY</button></div><p>List SQLite tables.</p></article><article class="card" data-search="useful one-liners select table_name from information_schema.tables where table_type='base table'; list base tables using information schema."><div class="cmdrow"><pre><code>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';</code></pre><button class="copy" data-copy="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';">COPY</button></div><p>List base tables using information schema.</p></article></div></div></section></main>
|
| 62 | </div>
|
| 63 |
|
| 64 | <footer>SQL Mega Cheat Sheet • MySQL + PostgreSQL + SQL Server + SQLite coverage • Search and copy controls run locally in your browser.</footer>
|
| 65 |
|
| 66 | <script>
|
| 67 | const q=document.getElementById('search');
|
| 68 | const cards=[...document.querySelectorAll('.card')];
|
| 69 | const sections=[...document.querySelectorAll('.section')];
|
| 70 |
|
| 71 | q.addEventListener('input',()=>{
|
| 72 | const s=q.value.trim().toLowerCase();
|
| 73 | cards.forEach(c=>c.classList.toggle('hidden',s&&!c.dataset.search.includes(s)));
|
| 74 | sections.forEach(sec=>{
|
| 75 | const visible=sec.querySelectorAll('.card:not(.hidden)').length;
|
| 76 | sec.classList.toggle('hidden',s&&visible===0);
|
| 77 | if(s&&visible)sec.classList.remove('collapsed');
|
| 78 | });
|
| 79 | });
|
| 80 |
|
| 81 | document.querySelectorAll('.sectionhead').forEach(b=>b.onclick=()=>{
|
| 82 | const sec=b.closest('.section');
|
| 83 | sec.classList.toggle('collapsed');
|
| 84 | b.setAttribute('aria-expanded',String(!sec.classList.contains('collapsed')));
|
| 85 | });
|
| 86 | document.getElementById('expand').onclick=()=>sections.forEach(s=>s.classList.remove('collapsed'));
|
| 87 | document.getElementById('collapse').onclick=()=>sections.forEach(s=>s.classList.add('collapsed'));
|
| 88 |
|
| 89 | document.addEventListener('click',async e=>{
|
| 90 | const b=e.target.closest('.copy'); if(!b) return;
|
| 91 | try{
|
| 92 | await navigator.clipboard.writeText(b.dataset.copy);
|
| 93 | b.textContent='COPIED';
|
| 94 | setTimeout(()=>b.textContent='COPY',800);
|
| 95 | }catch{
|
| 96 | b.textContent='SELECT';
|
| 97 | setTimeout(()=>b.textContent='COPY',800);
|
| 98 | }
|
| 99 | });
|
| 100 | </script>
|
| 101 | </body>
|
| 102 | </html>
|