SQL flashcards

Ask questions of your data and get exact answers.

Learn

Beginner friendly, starts from zero

CardsCheatsheetTips and tricks

SQLStart from zero · 1 of 12

What is SQL, in one plain sentence?

Think of the answer, then tap

SQL is a language for asking a questions and changing what it stores, like 'show me every order from Chennai'.

You say what you want; the finds it.

Space turn over · K got it · J again · ← back

All topics

SQL at a glance

SQL is a language for asking a database questions and changing what it stores.

Like handing a librarian a request slip: you say what you want, and he works out where to look.

Words you will meet

Database: An organised store of data run by a program
Table: One sheet of data with fixed columns
Row: One thing in a table, like one customer
Column: One fact about every row, like email
Query: A statement that asks the database for data
NULL: A missing or unknown value, not zero
Primary key: A column that is different for every row, like a roll number
Transaction: A group of changes that all happen or none do

The steps

1. Start from zero (12 cards): What SQL is, the words you need, and your first query.
2. Core basics (16 cards): Tables, SELECT, WHERE, NULL and changing rows without breaking things.
3. Next steps (16 cards): Grouping, joins, subqueries, CTEs, indexes and transactions you use every week.
4. Go deeper (18 cards): Window functions, query plans, upserts, isolation and safe, fast SQL at scale.

All 12 cards in Start from zero

What is SQL, in one plain sentence?: SQL is a language for asking a database questions and changing what it stores, like 'show me every order from Chennai'.
Before databases and SQL, how did people answer questions about their data?: Data lived in paper registers or many spreadsheets. Answering 'which customers bought twice this month?' meant hours of scrolling and copying; SQL answers it in one statement, even over millions of rows.
Where might you already meet SQL in daily life?: Checking your bank balance, searching a shop's products, or seeing which train seats are free. The app behind each one is very likely running SQL on a database.
What everyday picture fits SQL best?: A library. The database is the building, tables are shelves of index cards, and SQL is the request slip you hand the librarian: you say what you want, and he works out where to look.
What are a 'database', a 'table', a 'row' and a 'column'?: A database is an organised store of data run by a program such as PostgreSQL or SQLite. Data sits in tables, like spreadsheet sheets: each row is one thing, each column one fact about it.
What are a 'statement', a 'query', a 'keyword' and a 'clause'?: A statement is one complete SQL command ending with ;. A query is a statement that asks for data, usually starting with SELECT. Fixed words like SELECT, FROM and WHERE are keywords, and each part they start is a clause.
What is a 'data type', and what does NULL mean?: Each column holds one type of value: whole numbers (bigint), text (text), dates and times (timestamptz) or exact amounts (numeric). NULL means a value is missing or unknown, not zero.
What are a 'primary key' and an 'index'?: A primary key is a column whose value is different for every row, like a roll number, so you can point at exactly one row. An index is a sorted lookup list the database keeps, like the index at the back of a book, to find rows fast.
What is a 'transaction'?: A group of changes that all happen or none do. BEGIN starts it, COMMIT saves it, ROLLBACK undoes it. Like a bank transfer: money leaves one account only if it reaches the other.
What do you need before starting this topic?: Nothing to install just to read. To practise, use SQLite, a tiny database kept in one file that comes with Python and macOS, or a free PostgreSQL. A terminal or a simple database app is enough.
Your first tiny win: what does this query return?: SELECT names the columns you want, FROM names the table, and WHERE keeps only rows that match. You get the name and city of every customer in Pune.
What will you be able to do after this topic?: Ask a database real questions, sort and filter the answers, add, change and delete rows safely, and design simple tables with the right types and keys.

SQL cheatsheet

Read and filter

SELECT id, name FROM t WHERE ... Name columns, filter rows
WHERE ts >= :start AND ts < :end Half-open time range
x IS NULL / IS DISTINCT FROM y NULL-safe tests
ORDER BY score DESC, id Sort with a tie-breaker
LIMIT 50 Cap rows, always with ORDER BY
COALESCE(NULLIF(x, ''), 'n/a') Default for NULL or empty
WHERE EXISTS (SELECT 1 ...) Has a match, NULL-safe

Write safely

BEGIN; ... ROLLBACK; Dry-run a risky change
INSERT ... VALUES (...), (...) Multi-row insert
ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v Upsert (PostgreSQL, SQLite)
ON DUPLICATE KEY UPDATE Upsert (MySQL)
RETURNING id Get changed rows back
SELECT ... FOR UPDATE Lock rows you will change
DELETE ... WHERE id IN (... LIMIT 10000) Batch delete

Join and group

JOIN b ON b.a_id = a.id Matching pairs only
LEFT JOIN b ON ... AND b.flag Keep left rows; right filter in ON
JOIN LATERAL (... LIMIT 1) x ON true Latest child per row
GROUP BY a HAVING COUNT(*) > 1 Find duplicates
COUNT(*) FILTER (WHERE ...) Conditional count
WITH step AS (...) Name a step (CTE)
UNION ALL Stack results, no dedup cost

Windows

ROW_NUMBER() OVER (PARTITION BY g ORDER BY x) Number rows per group
RANK() / DENSE_RANK() Ties with or without gaps
LAG(x) / LEAD(x) OVER (...) Previous or next row
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW True running total
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 7-row moving window
WINDOW w AS (...) Reuse one window definition

Speed and schema

EXPLAIN (ANALYZE, BUFFERS) ... Real plan, timings, I/O
CREATE INDEX ON t (eq_col, range_col) Equality first, range last
CREATE INDEX ON t (lower(email)) Expression index
CREATE INDEX ... WHERE status = 'open' Partial index
WHERE (ts, id) < (:ts, :id) Keyset pagination
numeric(12,2) Money, never float
timestamptz Instants in time (PostgreSQL)