SQL basics

The core of SQL: reading, filtering, joining, grouping and changing rows.

25 cardsBeginnerCode

Open the downloaded file on your iPhone or iPad and choose StackCards. It is also in the app: Library tab, then Add to my library.

What is inside

Read every column from a tableThe star means all columns. Handy for a look, rarely right for production.

SELECT * FROM users;

Pick specific columnsName only what you need.

SELECT name, email FROM users;

Filter rowsWHERE keeps the rows where the condition is true.

SELECT * FROM users WHERE age >= 18;

Sort the resultORDER BY sorts ascending; add DESC for the reverse.

SELECT * FROM users ORDER BY created_at DESC;

Limit the rowsReturns only the first few rows.

SELECT * FROM users LIMIT 10;

Remove duplicatesDISTINCT returns each different value once.

SELECT DISTINCT country FROM users;

Match several valuesIN checks against a list.

SELECT * FROM users WHERE country IN ('MX', 'US');

Match a pattern% stands for any run of characters.

SELECT * FROM users WHERE name LIKE 'Ma%';

Check for missing valuesUse IS NULL; = NULL never matches.

SELECT * FROM users WHERE phone IS NULL;

Count rowsCOUNT(*) counts rows, including ones with NULLs.

SELECT COUNT(*) FROM orders;

Total and averageAggregate functions collapse many rows into one value.

SELECT SUM(total), AVG(total) FROM orders;

Group rowsGROUP BY makes one result row per group.

SELECT country, COUNT(*) FROM users GROUP BY country;

Filter after groupingHAVING filters groups; WHERE filters rows before grouping.

SELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*) > 5;

INNER JOINKeeps only rows that have a match in both tables.

SELECT u.name, o.total FROM users u INNER JOIN orders o ON o.user_id = u.id;

LEFT JOINKeeps every row from the left table, with NULLs where there is no match.

SELECT u.name, o.total FROM users u LEFT JOIN orders o ON o.user_id = u.id;

Rename a column or tableAS gives a name that is easier to read.

SELECT COUNT(*) AS total_users FROM users;

Insert a rowAdds one row with the values you list.

INSERT INTO users (name, email) VALUES ('Ana', 'ana@example.com');

Update rowsAlways add a WHERE, or every row changes.

UPDATE users SET age = 30 WHERE id = 7;

Delete rowsAlways add a WHERE, or the table empties.

DELETE FROM users WHERE id = 7;

Create a tableDefines columns, types and constraints.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

What is a primary key?A column, or set of columns, that uniquely identifies each row and can't be NULL.

What is a foreign key?A column that points to the primary key of another table and keeps the link consistent.

Why add an index?It makes lookups and sorts on a column much faster, at the cost of slower writes and more space.

WHERE vs HAVINGWHERE filters rows before grouping. HAVING filters the groups after aggregation.

What does a transaction guarantee?A group of statements either all happen or none do, so the data never ends up half changed.