SQL Cheat Sheet: The Most Common Commands

-- Basic query
SELECT column1, column2 FROM table_name;

-- All columns
SELECT * FROM table_name;

-- Distinct values
SELECT DISTINCT column_name FROM table_name;

-- Aliases
SELECT column_name AS alias FROM table_name;

-- Calculated column
SELECT price * quantity AS total FROM orders;

-- Comparison operators
SELECT * FROM users WHERE age > 18;
SELECT * FROM products WHERE price BETWEEN 10 AND 50;

-- Text matching
SELECT * FROM users WHERE name LIKE 'J%';     -- starts with J
SELECT * FROM users WHERE name LIKE '%son%';  -- contains 'son'

-- IN clause
SELECT * FROM users WHERE country IN ('US', 'CA', 'UK');

-- NULL checks
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

SELECT * FROM products ORDER BY price ASC;     -- low to high
SELECT * FROM products ORDER BY price DESC;    -- high to low
SELECT * FROM users ORDER BY last_name, first_name;  -- multiple columns
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;  -- latest 10

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

-- Multiple rows
INSERT INTO users (name, email) VALUES
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');

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

-- Always use WHERE — without it, all rows are updated!

DELETE FROM users WHERE id = 1;

-- Delete all rows (but keep the table)
DELETE FROM users;

-- Truncate (faster, removes all rows)
TRUNCATE TABLE users;

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

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

-- Using aliases is standard practice

SELECT
  COUNT(*) AS total_users,
  AVG(age) AS average_age,
  MAX(price) AS most_expensive,
  MIN(price) AS cheapest,
  SUM(quantity) AS total_items
FROM orders;

-- Group By
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING user_count > 10;

-- WHERE subquery
SELECT * FROM products
WHERE category_id IN (
  SELECT id FROM categories WHERE active = true
);

-- FROM subquery
SELECT avg_order.total
FROM (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id) AS avg_order;

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INTEGER CHECK (age >= 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users RENAME COLUMN phone TO phone_number;
ALTER TABLE users RENAME TO customers;

-- PostgreSQL
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '7 days';
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) FROM orders GROUP BY month;

-- MySQL
SELECT * FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);