Nexus Learn

SQL Cheat Sheet

The SQL commands you need most, with a short example for each. The examples use a table called students with columns id, name, city and marks.

Reading data

CommandWhat it does
SELECT * FROM students;Get every column and every row.
SELECT name, marks FROM students;Get only the columns you name.
SELECT DISTINCT city FROM students;Get each city once, with no repeats.
SELECT * FROM students ORDER BY marks DESC;Sort from highest to lowest. Leave out DESC for lowest first.
SELECT * FROM students LIMIT 10;Get only the first 10 rows (MySQL, PostgreSQL, SQLite).

Filtering rows with WHERE

CommandWhat it does
WHERE marks > 60Compare with =, <>, <, >, <= or >=.
WHERE city = 'Lahore' AND marks >= 50Both conditions must be true.
WHERE city = 'Lahore' OR city = 'Multan'At least one condition must be true.
WHERE city IN ('Lahore', 'Multan')Match any value in a list.
WHERE marks BETWEEN 50 AND 70Match a range, including both ends.
WHERE name LIKE 'A%'Match a pattern. % means any characters, _ means one character.
WHERE city IS NULLFind missing values. Use IS NULL, never = NULL.

Totals with aggregate functions

CommandWhat it does
SELECT COUNT(*) FROM students;Count all rows.
SELECT AVG(marks) FROM students;Average of a column. Also SUM, MIN and MAX.
SELECT city, COUNT(*) FROM students GROUP BY city;One result row per city.
... GROUP BY city HAVING COUNT(*) > 5;Keep only groups that pass a test. HAVING filters groups, WHERE filters rows.

Joining tables

CommandWhat it does
FROM a INNER JOIN b ON a.id = b.a_idOnly rows that match in both tables.
FROM a LEFT JOIN b ON a.id = b.a_idAll rows from a, with matches from b (or NULL).
FROM a RIGHT JOIN b ON a.id = b.a_idAll rows from b, with matches from a (or NULL).
FROM a FULL OUTER JOIN b ON a.id = b.a_idAll rows from both. MySQL does not support it.

Changing data

CommandWhat it does
INSERT INTO students (name, city) VALUES ('Ali', 'Lahore');Add a new row.
UPDATE students SET marks = 80 WHERE id = 3;Change rows. Always use WHERE, or every row changes.
DELETE FROM students WHERE id = 3;Remove rows. Always use WHERE, or every row goes.

Tables

CommandWhat it does
CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50), marks INT);Make a new table.
ALTER TABLE students ADD COLUMN city VARCHAR(30);Add a column to an existing table.
TRUNCATE TABLE students;Remove all rows quickly but keep the table.
DROP TABLE students;Delete the table and all its data.

The order you write a query

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. The database works through them in a different order (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT), which is why you cannot use a column alias from SELECT inside WHERE.

Frequently asked questions

What is the difference between WHERE and HAVING?

WHERE filters individual rows before they are grouped. HAVING filters the groups after GROUP BY, so it can use aggregates such as COUNT(*).

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes the rows you choose. TRUNCATE removes every row but keeps the table. DROP removes the whole table.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN keeps only matching rows. LEFT JOIN keeps every row from the left table, filling in NULL where the right table has no match.

Test yourself: practice questions on this topic. More cheat sheets: Git Cheat Sheet · Python Cheat Sheet · Linux Commands Cheat Sheet · Big-O cheat sheet