SQL Reference

Retrieve all columns from a table.

SELECT * FROM table_name;

Retrieve specific columns only.

SELECT col1, col2 FROM table_name;

Filter rows based on a condition.

SELECT * FROM users WHERE age > 18;

Sort results by a column (ASC or DESC).

SELECT * FROM products ORDER BY price DESC;

Paginate results — skip 20 rows, return next 10.

SELECT * FROM posts LIMIT 10 OFFSET 20;

Add a new row to a table.

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

Modify existing rows. Always use WHERE to avoid updating all rows.

UPDATE users SET email = 'new@example.com' WHERE id = 1;

Remove rows from a table. Always use WHERE!

DELETE FROM users WHERE id = 1;

Insert or update on conflict (PostgreSQL syntax).

INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

Return rows where both tables have matching values.

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

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

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

Group rows and aggregate with COUNT, SUM, AVG, MIN, MAX.

SELECT department, COUNT(*) as total
FROM employees
GROUP BY department;

Filter aggregated groups (like WHERE but for grouped results).

SELECT department, COUNT(*) as total
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Nest a query inside another query.

SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

Common Table Expressions make complex queries readable.

WITH high_value AS (
  SELECT user_id FROM orders WHERE total > 100
)
SELECT name FROM users WHERE id IN (SELECT user_id FROM high_value);

Rank rows within partitions without collapsing groups.

SELECT name, salary,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as rank
FROM employees;

Define a new table with columns and constraints.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email TEXT UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

Add a new column to an existing table.

ALTER TABLE users ADD COLUMN age INTEGER;

Speed up queries on frequently searched columns.

CREATE INDEX idx_users_email ON users(email);

Pattern matching: % matches any string, _ matches one char.

SELECT * FROM products WHERE name LIKE '%shirt%';

Match any value in a list.

SELECT * FROM users WHERE status IN ('active', 'trial');

Match values within an inclusive range.

SELECT * FROM orders WHERE total BETWEEN 50 AND 200;

Find rows with missing values.

SELECT * FROM users WHERE last_login IS NULL;

Return first non-NULL value from the list.

SELECT COALESCE(nickname, name, 'Anonymous') FROM users;

Conditional logic in SQL (like if/else).

SELECT name,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 70 THEN 'B'
    ELSE 'C'
  END as grade
FROM students;