SQL Cheatsheet

A practical SQL guide covering queries, joins, filtering, aggregation, window functions, and database workflows.

Resources

What is SQL?

SQL is the language you use to talk to a database. You ask questions, save new data, update old data, and delete what you don't need — all in plain, readable sentences.

how a database is organised

databasethe container

The whole container — like a folder that holds everything.
One app usually has one database.

tablelike a spreadsheet

Like a spreadsheet tab inside the database.
A users table, an orders table — one table per topic.

columnthe headings

Describes what kind of data goes in each slot.
For example: name, email, age.

rowone record

One complete record — one person, one order, one product.
Each row has a value for every column.

the four things SQL does — CRUD

SQL

-- Read: ask for data
SELECT name FROM users;

-- Create: add a new row
INSERT INTO users (name) VALUES ('Alice');

-- Update: change existing data
UPDATE users SET name = 'Bob' WHERE id = 1;

-- Delete: remove a row
DELETE FROM users WHERE id = 1;

key words you'll see constantly

SQL

-- primary key: unique ID for each row
-- foreign key: points to another table's primary key

-- orders.user_id is a foreign key
-- it points to users.id
SELECT *
FROM orders
WHERE user_id = 5;

SELECT Basics

SELECT is the most common SQL command. You'll write it dozens of times a day. It asks the database a question — and the database sends back the answer as rows.

SELECT and FROM

SQL

-- get every column from a table
SELECT * FROM users;

-- get specific columns only
SELECT name, email FROM users;

-- rename a column in the result
-- (the table is not changed)
SELECT
  name AS full_name,
  email AS contact
FROM users;

WHERE — filter which rows you get

SQL

-- only rows where age equals 30
SELECT name FROM users WHERE age = 30;

-- not equal
SELECT name FROM users WHERE age != 30;

-- greater than / less than
SELECT name FROM users WHERE age > 18;
SELECT name FROM users WHERE age <= 65;

-- combine conditions
SELECT name
FROM users
WHERE age > 18
  AND country = 'LK';

SELECT name
FROM users
WHERE country = 'LK'
  OR country = 'SG';

ORDER BY and LIMIT

SQL

-- sort A to Z
SELECT name FROM users ORDER BY name ASC;

-- sort Z to A
SELECT name FROM users ORDER BY name DESC;

-- newest records first
SELECT * FROM orders ORDER BY created_at DESC;

-- only the top 5
SELECT name FROM users
ORDER BY score DESC
LIMIT 5;

-- page 2: skip first 10, get next 10
SELECT name FROM users
ORDER BY name ASC
LIMIT 10
OFFSET 10;

DISTINCT — remove duplicate values

SQL

-- without DISTINCT: shows every country
-- even if 1000 users share the same one
SELECT country FROM users;

-- with DISTINCT: each country appears once
SELECT DISTINCT country FROM users;

-- DISTINCT across multiple columns
-- unique combination of country + city
SELECT DISTINCT country, city FROM users;

Filtering

WHERE is just the start. SQL gives you several tools to filter rows in very specific ways. These come up constantly in real queries.

LIKE — match a text pattern

SQL

-- starts with 'Al'
-- matches: Alice, Albert, Alex
SELECT name FROM users WHERE name LIKE 'Al%';

-- ends with '.lk'
-- matches: [email protected], [email protected]
SELECT email FROM users WHERE email LIKE '%.lk';

-- contains 'ali' anywhere
SELECT name FROM users WHERE name LIKE '%ali%';

-- case-insensitive (PostgreSQL)
SELECT name FROM users WHERE name ILIKE '%alice%';

-- exactly 4 characters
SELECT code FROM products WHERE code LIKE '____';

IN and BETWEEN

SQL

-- IN: match any value in a list
SELECT name
FROM users
WHERE country IN ('LK', 'SG', 'MY');

-- NOT IN: exclude the list
SELECT name
FROM users
WHERE country NOT IN ('LK', 'SG');

-- BETWEEN: inclusive range
-- includes 18 and 30
SELECT name
FROM users
WHERE age BETWEEN 18 AND 30;

-- BETWEEN works on dates too
SELECT *
FROM orders
WHERE created_at
  BETWEEN '2024-01-01' AND '2024-12-31';

IS NULL — check for missing values

SQL

-- find rows with no phone number
SELECT name
FROM users
WHERE phone IS NULL;

-- find rows that DO have a phone number
SELECT name
FROM users
WHERE phone IS NOT NULL;

-- ❌ this never works — returns nothing
SELECT name FROM users WHERE phone = NULL;

-- replace NULL with a fallback value
SELECT
  name,
  COALESCE(phone, 'No phone') AS phone
FROM users;

WHERE phone = NULL will always return zero rows. SQL won't warn you. Use WHERE phone IS NULL instead.

Aggregations

Aggregations crunch many rows down into one number. How many users signed up? What's the average order value? That's what these are for.

COUNT, SUM, AVG, MIN, MAX

SQL

-- how many rows total
SELECT COUNT(*) FROM users;

-- how many have an email address
SELECT COUNT(email) FROM users;

-- total of all order amounts
SELECT SUM(amount) FROM orders;

-- average order amount
SELECT AVG(amount) FROM orders;

-- cheapest and most expensive product
SELECT
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive
FROM products;

GROUP BY — aggregate per group

SQL

-- how many users per country
SELECT
  country,
  COUNT(*) AS total_users
FROM users
GROUP BY country;

-- total sales per product
SELECT
  product_id,
  SUM(amount) AS total_sales
FROM orders
GROUP BY product_id;

-- average order value per customer
SELECT
  user_id,
  AVG(amount) AS avg_order
FROM orders
GROUP BY user_id;

HAVING — filter groups after grouping

SQL

-- only countries with more than 100 users
SELECT
  country,
  COUNT(*) AS total
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

-- customers with more than 5 orders
SELECT
  user_id,
  COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

-- using WHERE and HAVING together
-- WHERE runs first (filter rows)
-- HAVING runs after (filter groups)
SELECT
  country,
  COUNT(*) AS total
FROM users
WHERE age > 18
GROUP BY country
HAVING COUNT(*) > 50;

JOINs

A JOIN combines rows from two tables. Real databases spread data across many tables. JOINs are how you bring that data back together.

INNER JOIN — only matching rows

SQL

-- get each order with the user's name
-- users without orders are excluded
SELECT
  users.name,
  orders.amount
FROM orders
INNER JOIN users
  ON orders.user_id = users.id;

-- use short aliases to save typing
SELECT
  u.name,
  o.amount
FROM orders AS o
INNER JOIN users AS u
  ON o.user_id = u.id;

LEFT JOIN — keep all rows from the left table

SQL

-- every user, even those with no orders
-- orders columns will be NULL for those users
SELECT
  u.name,
  o.amount
FROM users AS u
LEFT JOIN orders AS o
  ON u.id = o.user_id;

-- find users who have NEVER ordered
SELECT u.name
FROM users AS u
LEFT JOIN orders AS o
  ON u.id = o.user_id
WHERE o.id IS NULL;

RIGHT JOIN and FULL OUTER JOIN

SQL

-- RIGHT JOIN: keep all orders
-- even if the user_id doesn't match any user
SELECT
  u.name,
  o.amount
FROM users AS u
RIGHT JOIN orders AS o
  ON u.id = o.user_id;

-- FULL OUTER JOIN: keep everything
-- from both tables
-- NULLs fill in where there's no match
SELECT
  u.name,
  o.amount
FROM users AS u
FULL OUTER JOIN orders AS o
  ON u.id = o.user_id;

joining more than two tables

SQL

-- three tables: orders, users, products
SELECT
  u.name    AS customer,
  p.name    AS product,
  o.amount  AS total
FROM orders AS o
INNER JOIN users AS u
  ON o.user_id = u.id
INNER JOIN products AS p
  ON o.product_id = p.id;

Subqueries

A subquery is a query inside another query. You use the result of the inner query to help answer the outer one. Think of it as answering a smaller question first.

subquery in WHERE

SQL

-- find users who have placed at least one order
-- inner query: get user_ids from orders
-- outer query: find those users
SELECT name
FROM users
WHERE id IN (
  SELECT user_id FROM orders
);

-- find users who ordered MORE than average
SELECT name
FROM users
WHERE total_spent > (
  SELECT AVG(total_spent) FROM users
);

EXISTS — check if matching rows exist

SQL

-- users who have at least one order
SELECT name
FROM users AS u
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.user_id = u.id
);

-- users who have NO orders
SELECT name
FROM users AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.user_id = u.id
);

subquery in FROM — inline table

SQL

-- treat a subquery like a temporary table
-- must always give it an alias
SELECT
  country,
  avg_age
FROM (
  SELECT
    country,
    AVG(age) AS avg_age
  FROM users
  GROUP BY country
) AS country_stats
WHERE avg_age > 30;

Modifying Data

INSERT adds new rows. UPDATE changes existing ones. DELETE removes them. These are permanent — there's no undo button unless you're inside a transaction.

INSERT — add new rows

SQL

-- insert one row
INSERT INTO users (name, email, age)
VALUES ('Alice', '[email protected]', 28);

-- insert multiple rows at once
INSERT INTO users (name, email, age)
VALUES
  ('Bob',   '[email protected]',   32),
  ('Carol', '[email protected]', 25);

-- insert and get back the new row
-- PostgreSQL
INSERT INTO users (name, email)
VALUES ('Dave', '[email protected]')
RETURNING id, name;

UPDATE — change existing rows

SQL

-- change one user's email
UPDATE users
SET email = 'new@example.com'
WHERE id = 5;

-- update multiple columns at once
UPDATE users
SET
  email = 'new@example.com',
  age   = 29
WHERE id = 5;

-- safe pattern: preview first
-- step 1: check what will change
SELECT * FROM users WHERE id = 5;

-- step 2: then update
UPDATE users
SET email = 'new@example.com'
WHERE id = 5;

UPDATE users SET role = 'admin' with no WHERE clause gives every user admin access. Always add WHERE.

DELETE — remove rows

SQL

-- delete one specific row
DELETE FROM users WHERE id = 5;

-- delete rows matching a condition
DELETE FROM orders
WHERE created_at < '2023-01-01';

-- clear all rows (faster than DELETE)
-- cannot use WHERE with TRUNCATE
TRUNCATE TABLE sessions;

DELETE FROM users with no WHERE will empty your entire users table. Run a SELECT with the same WHERE first.

UPSERT — insert or update

SQL

-- PostgreSQL: ON CONFLICT
-- if email already exists, update the name
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

-- if email already exists, do nothing
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice')
ON CONFLICT (email)
DO NOTHING;

Creating & Managing Tables

Before you can store data, you need a table. CREATE TABLE defines what columns exist and what rules each column must follow.

data types — what goes in each column

SQL

-- common column types in PostgreSQL
id          INTEGER
name        TEXT
price       NUMERIC(10, 2)
is_active   BOOLEAN
birthday    DATE
created_at  TIMESTAMP

-- MySQL uses VARCHAR instead of TEXT
name        VARCHAR(255)

CREATE TABLE and constraints

SQL

CREATE TABLE users (
  -- auto-incrementing primary key
  id         SERIAL PRIMARY KEY,

  -- required, must be unique
  email      TEXT NOT NULL UNIQUE,

  -- required
  name       TEXT NOT NULL,

  -- optional (NULL allowed)
  phone      TEXT,

  -- defaults to true automatically
  is_active  BOOLEAN DEFAULT true,

  -- auto-set to now when inserted
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
  id      SERIAL PRIMARY KEY,
  amount  NUMERIC(10, 2) NOT NULL,

  -- links to users table
  -- cannot reference a user that doesn't exist
  user_id INTEGER REFERENCES users(id)
);

ALTER TABLE — change an existing table

SQL

-- add a new column
ALTER TABLE users
ADD COLUMN age INTEGER;

-- remove a column (and all its data)
ALTER TABLE users
DROP COLUMN age;

-- rename a column
ALTER TABLE users
RENAME COLUMN phone TO phone_number;

-- rename the table itself
ALTER TABLE users
RENAME TO members;

-- change a column's type
ALTER TABLE users
ALTER COLUMN age TYPE BIGINT;

DROP COLUMN permanently removes that column and all its data. There is no undo. Always back up first on production databases.

DROP TABLE — delete a table

SQL

-- delete the table if it exists
-- no error if it doesn't
DROP TABLE IF EXISTS sessions;

-- CASCADE: also drop tables
-- that reference this one
DROP TABLE IF EXISTS users CASCADE;

DROP TABLE deletes the table and every row in it permanently. CASCADE also drops any tables that depend on it via foreign keys.

Indexes

An index helps PostgreSQL find rows fast without scanning every row in the table. Think of it like a book's index — you jump straight to the page instead of reading chapter by chapter.

CREATE INDEX and when to use it

SQL

-- basic index on one column
CREATE INDEX idx_users_email
ON users(email);

-- unique index: no duplicate values allowed
-- same effect as UNIQUE constraint
CREATE UNIQUE INDEX idx_users_email_unique
ON users(email);

-- index on multiple columns
-- useful when you filter by both together
CREATE INDEX idx_orders_user_date
ON orders(user_id, created_at);

-- remove an index
DROP INDEX idx_users_email;

EXPLAIN — see if your query uses an index

SQL

-- see the query plan without running it
EXPLAIN
SELECT * FROM users WHERE email = '[email protected]';

-- run the query and show real timings
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

-- look for these in the output:
-- Seq Scan  → no index used (slow)
-- Index Scan → index used (fast)

Transactions

A transaction groups several queries into one all-or-nothing operation. If anything fails halfway through, the whole thing rolls back. Your data stays consistent.

BEGIN, COMMIT, ROLLBACK

SQL

-- start a transaction
BEGIN;

-- step 1: deduct from sender
UPDATE accounts
SET balance = balance - 500
WHERE id = 1;

-- step 2: add to receiver
UPDATE accounts
SET balance = balance + 500
WHERE id = 2;

-- if both worked, save permanently
COMMIT;

-- if something went wrong,
-- undo everything since BEGIN
ROLLBACK;

SAVEPOINT — partial rollback

SQL

BEGIN;

UPDATE inventory SET stock = stock - 1
WHERE product_id = 10;

-- mark a safe point
SAVEPOINT after_inventory;

INSERT INTO orders (product_id, user_id)
VALUES (10, 42);

-- something went wrong with the order
-- roll back only to the savepoint
-- inventory update is kept
ROLLBACK TO after_inventory;

-- continue with other queries...

COMMIT;

Common Table Expressions

A CTE lets you give a subquery a name and use it like a temporary table. Long queries become much easier to read when you split them into named steps.

WITH — write a named subquery

SQL

-- without CTE: hard to read
SELECT name
FROM users
WHERE id IN (
  SELECT user_id
  FROM orders
  WHERE amount > 100
);

-- with CTE: clear and readable
WITH big_orders AS (
  SELECT user_id
  FROM orders
  WHERE amount > 100
)
SELECT name
FROM users
WHERE id IN (SELECT user_id FROM big_orders);

chaining multiple CTEs

SQL

-- chain CTEs with a comma
-- each one can reference the ones above it
WITH
active_users AS (
  SELECT id, name
  FROM users
  WHERE is_active = true
),

user_orders AS (
  SELECT
    u.name,
    COUNT(o.id) AS order_count
  FROM active_users AS u
  LEFT JOIN orders AS o
    ON u.id = o.user_id
  GROUP BY u.name
)

-- final query uses the last CTE
SELECT name, order_count
FROM user_orders
WHERE order_count > 3
ORDER BY order_count DESC;

recursive CTE — walk through a hierarchy

SQL

-- find all employees under a manager
-- including nested levels
WITH RECURSIVE org_chart AS (

  -- anchor: start with the top manager
  SELECT id, name, manager_id
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive: find their direct reports
  SELECT
    e.id,
    e.name,
    e.manager_id
  FROM employees AS e
  INNER JOIN org_chart AS o
    ON e.manager_id = o.id
)
SELECT * FROM org_chart;

Window Functions

Window functions do calculations across related rows — without collapsing them into one row like GROUP BY does. Each row keeps its own result.

OVER — the key to window functions

SQL

-- running total of order amounts
-- every row stays, total grows with each one
SELECT
  id,
  amount,
  SUM(amount) OVER (
    ORDER BY created_at
  ) AS running_total
FROM orders;

-- PARTITION BY: restart the total per user
SELECT
  user_id,
  amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
  ) AS user_running_total
FROM orders;

ROW_NUMBER, RANK, DENSE_RANK

SQL

-- rank users by total spent
SELECT
  name,
  total_spent,
  ROW_NUMBER() OVER (
    ORDER BY total_spent DESC
  ) AS row_num,

  RANK() OVER (
    ORDER BY total_spent DESC
  ) AS rank,

  DENSE_RANK() OVER (
    ORDER BY total_spent DESC
  ) AS dense_rank
FROM users;

-- top 1 order per user (no duplicates)
WITH ranked AS (
  SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY amount DESC
    ) AS rn
  FROM orders
)
SELECT user_id, amount
FROM ranked
WHERE rn = 1;

LAG and LEAD — look at adjacent rows

SQL

-- compare each order amount to the previous one
SELECT
  id,
  amount,

  -- value from the row before
  LAG(amount) OVER (
    ORDER BY created_at
  ) AS previous_amount,

  -- value from the row after
  LEAD(amount) OVER (
    ORDER BY created_at
  ) AS next_amount,

  -- difference from last order
  amount - LAG(amount) OVER (
    ORDER BY created_at
  ) AS change_from_last
FROM orders;

Tips & Good Habits

These are the habits that separate careful SQL writers from the ones who've accidentally deleted a production table. Worth reading before you run anything on real data.

format your queries for readability

SQL

-- ❌ hard to read
select u.name,o.amount from users u inner join orders o on u.id=o.user_id where o.amount>100 order by o.amount desc;

-- ✅ easy to read
SELECT
  u.name,
  o.amount
FROM users AS u
INNER JOIN orders AS o
  ON u.id = o.user_id
WHERE o.amount > 100
ORDER BY o.amount DESC;

NULL gotchas — the most common SQL mistake

SQL

-- NULL equals nothing
SELECT NULL = NULL;   -- returns NULL, not true
SELECT NULL IS NULL;  -- returns true ✅

-- safe fallback with COALESCE
-- returns the first non-NULL value
SELECT COALESCE(phone, 'No phone') FROM users;

-- ❌ broken if any country is NULL
SELECT name FROM users
WHERE country NOT IN ('LK', NULL, 'SG');

-- ✅ safe alternative
SELECT name FROM users
WHERE country NOT IN ('LK', 'SG')
  OR country IS NULL;

NOT IN (...) returns no rows if the list contains even one NULL. Use NOT EXISTS instead when NULLs might be present.

naming conventions

SQL

-- ✅ good naming
CREATE TABLE order_items (
  id          SERIAL PRIMARY KEY,
  order_id    INTEGER REFERENCES orders(id),
  product_id  INTEGER REFERENCES products(id),
  quantity    INTEGER NOT NULL,
  is_gift     BOOLEAN DEFAULT false,
  created_at  TIMESTAMP DEFAULT NOW()
);

-- ❌ inconsistent naming (avoid)
CREATE TABLE OrderItem (
  OrderItemID  SERIAL PRIMARY KEY,
  orderId      INTEGER,
  Qty          INTEGER
);

safe habits before running queries

SQL

-- safe pattern for UPDATE or DELETE

-- step 1: see what will be affected
SELECT * FROM orders
WHERE created_at < '2023-01-01';

-- step 2: wrap in a transaction
BEGIN;

DELETE FROM orders
WHERE created_at < '2023-01-01';

-- step 3: check the result looks right
SELECT COUNT(*) FROM orders;

-- step 4a: looks good — save it
COMMIT;

-- step 4b: something's wrong — undo it
-- ROLLBACK;

handy one-liners worth bookmarking

SQL

-- list all tables in your database (PostgreSQL)
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public';

-- describe a table's columns
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users';

-- count rows in every table (PostgreSQL)
SELECT
  relname AS table_name,
  n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- find duplicate emails
SELECT email, COUNT(*) AS total
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- random sample of 10 rows
SELECT * FROM users
ORDER BY RANDOM()
LIMIT 10;

Was this helpful?

No login required to share feedback

More Cheatsheets

Keep your reference handy

Explore more zero-to-hero cheatsheets for the tools you use daily.

All Cheatsheets TypeScript Git

Resource Hub Guides