SQL Cheat Sheet — Quick Reference Guide for Beginners

1. Basic SQL Commands — Quick Reference

2. SELECT — Retrieving Data

-- Select all columns
SELECT * FROM employees;

-- Select specific columns
SELECT first_name, last_name, salary FROM employees;

-- Distinct values (no duplicates)
SELECT DISTINCT department FROM employees;

-- Limit results (MySQL/PostgreSQL)
SELECT * FROM employees LIMIT 10;

-- Limit results (T-SQL)
SELECT TOP 10 * FROM employees;

-- Sort results
SELECT * FROM employees ORDER BY salary DESC;

-- Sort by multiple columns
SELECT * FROM employees ORDER BY department ASC, salary DESC;

3. WHERE — Filtering Rows

-- Complex filtering example
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 60000
  AND hire_date >= '2024-01-01'
  AND (status = 'active' OR status = 'on_leave')
ORDER BY salary DESC;

4. JOIN — Combining Tables

-- INNER JOIN: employees with their department name
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN: all employees even if no department
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- SELF JOIN: employee and their manager
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

5. GROUP BY — Aggregating Data

-- Count employees per department
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- Aggregate functions
SELECT
  department,
  COUNT(*) AS emp_count,
  AVG(salary) AS avg_salary,
  MAX(salary) AS max_salary,
  MIN(salary) AS min_salary,
  SUM(salary) AS total_payroll
FROM employees
GROUP BY department;

-- Filter groups with HAVING (after aggregation)
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;

-- Common aggregate functions
-- COUNT, SUM, AVG, MAX, MIN, STRING_AGG, ARRAY_AGG

6. Subqueries & CTEs

-- Subquery in WHERE
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery in FROM
SELECT dept, avg_sal
FROM (SELECT department AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY department) sub
WHERE avg_sal > 60000;

-- CTE (Common Table Expression) — clearer than subqueries
WITH dept_stats AS (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
)
SELECT e.name, e.salary, ds.avg_sal
FROM employees e
JOIN dept_stats ds ON e.department = ds.department
WHERE e.salary > ds.avg_sal;

7. Window Functions

-- ROW_NUMBER: rank rows within a partition
SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

-- RANK vs DENSE_RANK (RANK skips ties, DENSE_RANK does not)
SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- LAG / LEAD: access previous/next row
SELECT name, hire_date,
  LAG(name) OVER (ORDER BY hire_date) AS hired_before,
  LEAD(name) OVER (ORDER BY hire_date) AS hired_after
FROM employees;

-- Running total with SUM OVER
SELECT order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

8. DDL — Creating & Modifying Tables

-- Create table with constraints
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  department_id INT REFERENCES departments(id),
  salary DECIMAL(10,2) CHECK (salary > 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add column
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- Drop column
ALTER TABLE employees DROP COLUMN phone;

-- Rename column (PostgreSQL)
ALTER TABLE employees RENAME COLUMN name TO full_name;

-- Create index
CREATE INDEX idx_employees_dept ON employees(department_id);

-- Create unique index
CREATE UNIQUE INDEX idx_employees_email ON employees(email);

9. DML — Insert, Update, Delete

-- Insert single row
INSERT INTO employees (name, email, department_id, salary)
VALUES ('Alice Chen', 'alice@company.com', 3, 75000);

-- Insert multiple rows
INSERT INTO employees (name, email, department_id, salary)
VALUES
  ('Bob Smith', 'bob@company.com', 2, 65000),
  ('Carol Wu', 'carol@company.com', 3, 82000);

-- Insert from SELECT
INSERT INTO employees_archive
SELECT * FROM employees WHERE status = 'terminated';

-- Update rows
UPDATE employees
SET salary = salary * 1.1, updated_at = NOW()
WHERE department_id = 3 AND performance = 'exceeds';

-- Delete rows
DELETE FROM employees
WHERE status = 'terminated' AND updated_at < '2024-01-01';

10. Common Data Types

FAQ