SQL Reference

SELECT query

Retrieve data from one or more columns in a table.

SELECT * FROM users;

WHERE clause

Filter query results to only return rows that match a condition.

SELECT name, email
FROM users

ORDER BY

Sort query results by one or more columns, ascending or descending.

SELECT name, age
FROM users

LIMIT rows

Restrict how many rows a query returns — useful for pagination and top-N queries.

SELECT name, age
FROM users

AND, OR, NOT

Combine multiple filter conditions in a WHERE clause.

SELECT name, age, country
FROM users

IN operator

Match a column against a list of values.

SELECT name, country
FROM users

LIKE pattern matching

Filter rows based on partial string matches using wildcards.

SELECT name, email
FROM users

NULL values

Check for, filter, and handle NULL values correctly.

SELECT name, email
FROM users

BETWEEN operator

Filter rows within a range of values.

SELECT name, age
FROM users

INNER JOIN

Return only rows that have matching records in both tables.

SELECT
  u.name,

LEFT JOIN

Return all rows from the left table, and matching rows from the right table (NULLs if no match).

SELECT
  u.name,

JOIN types overview

Understand the difference between INNER, LEFT, RIGHT, FULL OUTER and CROSS JOIN.

SELECT u.name, o.total
FROM users u

Self-join

Join a table to itself to compare rows or navigate hierarchies.

SELECT
  e.name       AS employee,

COUNT rows

Count rows, non-null values, or distinct values in a table.

SELECT COUNT(*) AS total_users
FROM users;

SUM, AVG, MIN, MAX

Calculate totals, averages, and extremes across rows.

SELECT SUM(total) AS revenue
FROM orders

GROUP BY

Group rows with the same value and apply aggregate functions to each group.

SELECT
  country,

HAVING clause

Filter groups after aggregation — like WHERE but for GROUP BY results.

SELECT
  country,

Subqueries

Use a query inside another query to filter, compute, or transform data.

SELECT name, email
FROM users

EXISTS / NOT EXISTS

Filter rows based on whether a related record exists in another table.

SELECT name, email
FROM users u

INSERT rows

Add one or more new rows to a table.

INSERT INTO users (name, email, age, country)
VALUES ('Alice', 'alice@example.com', 28, 'Kenya');

UPDATE rows

Modify existing data in one or more rows.

UPDATE users
SET country = 'Nigeria'

DELETE rows

Remove one or more rows from a table.

DELETE FROM users
WHERE id = 42;

CREATE TABLE

Define a new table with columns, data types and constraints.

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,

ALTER TABLE

Add, remove or modify columns and constraints on an existing table.

ALTER TABLE users
ADD COLUMN phone TEXT;

Indexes

Speed up queries by creating indexes on frequently queried columns.

CREATE INDEX idx_users_email ON users(email);

CTEs (WITH clause)

Write cleaner, reusable subqueries using Common Table Expressions.

WITH active_users AS (
  SELECT id, name, email

Window functions

Calculate running totals, rankings and moving averages without collapsing rows.

SELECT
  name,

Top N per group

Select the top N records within each category or group.

WITH ranked AS (
  SELECT

CASE expression

Add conditional logic to SQL queries — like an if/else.

SELECT
  name,

UPSERT

Insert a row if it does not exist, or update it if it does.

INSERT INTO users (email, name, country)
VALUES ('alice@example.com', 'Alice', 'Kenya')

EXPLAIN / query performance

Understand how the database executes your query and find performance bottlenecks.

EXPLAIN
SELECT u.name, COUNT(o.id)

Delete duplicate rows

Find and remove duplicate records while keeping one copy.

SELECT email, COUNT(*) AS occurrences
FROM users

JSON queries

Extract, filter and manipulate JSON stored in database columns.

-- Table: events (data JSONB)
-- data: {"user": "alice", "action": "login", "ip": "1.2.3.4"}