Cheatsheet

Tiny page for "what was that syntax again". SQLite-flavored.

Read

SELECT col1, col2
FROM table_name
WHERE col1 = 'x'
  AND col2 IS NOT NULL
ORDER BY col1 ASC
LIMIT 20 OFFSET 0;

SELECT DISTINCT city FROM contacts;

SELECT c.name, co.name AS company
FROM contacts AS c
LEFT JOIN companies AS co ON co.id = c.company_id;

Write

INSERT INTO contacts (name, email) VALUES ('Ada', 'ada@example.com');

UPDATE contacts SET city = 'London' WHERE id = 1;

DELETE FROM contacts WHERE id = 1;

-- soft delete
UPDATE contacts SET deleted_at = datetime('now') WHERE id = 1;

Aggregate

SELECT stage, COUNT(*) AS n, SUM(amount) AS total
FROM deals
WHERE deleted_at IS NULL
GROUP BY stage
HAVING COUNT(*) > 0;

Schema

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  company_id INTEGER REFERENCES companies(id),
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  deleted_at TEXT
);

CREATE INDEX idx_contacts_company ON contacts(company_id)
  WHERE deleted_at IS NULL;

ALTER TABLE contacts ADD COLUMN phone TEXT;
DROP TABLE contacts;

NULL & coalescing

WHERE city IS NULL
WHERE city IS NOT NULL
SELECT COALESCE(city, 'Unknown') FROM contacts;

LIKE

WHERE name LIKE 'A%'      -- starts with
WHERE email LIKE '%@%'    -- contains
WHERE LOWER(email) LIKE '%ada%'

Transactions

BEGIN;
-- writes…
COMMIT;
-- ROLLBACK;

SQLite CLI

.headers on
.mode column
.tables
.schema contacts
.read file.sql
.quit
PRAGMA foreign_keys = ON;
EXPLAIN QUERY PLAN SELECT …;

FE ↔ SQL map

FE	SQL
.filter	WHERE
.sort	ORDER BY
.slice / page	LIMIT / OFFSET
.map fields	SELECT cols
nested include	JOIN
?? default	COALESCE
groupBy + count	GROUP BY + COUNT

CRM files

sqlite3 examples/crm/crm.db < examples/crm/schema.sql
sqlite3 examples/crm/crm.db < examples/crm/seed.sql
sqlite3 examples/crm/crm.db < examples/crm/queries.sql