The Ultimate SQL Cheat Sheet: From Beginner to Advanced

Whether you’re taking your first steps in data analysis or you’re an experienced engineer optimizing complex queries, having a robust reference is essential. This cheat sheet is designed not just to show you how to write SQL, but why it works the way it does, complete with real-world examples.

1. Core Concepts & Execution Order

Before memorizing syntax, it’s crucial to understand how a SQL engine actually reads your query. You write SQL in a logical order, but the database engine executes it in a completely different sequence.

Lexical Order (How you write it): SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT

Logical Execution Order (How the engine processes it):

FROM (and JOIN) - Choose and join tables
WHERE - Filter rows
GROUP BY - Aggregate data
HAVING - Filter aggregated data
SELECT - Return desired columns
ORDER BY - Sort the results
LIMIT / OFFSET - Restrict the output

2. DDL: Data Definition Language

DDL commands define the schema and structure of your database.

Creating Tables & Constraints

CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT CHECK (age >= 18),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active'
);

Tip: Always use constraints (NOT NULL, UNIQUE, CHECK) to enforce data integrity at the database level rather than relying solely on your application backend.

Modifying Tables

-- Add a new column
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

-- Rename a column
ALTER TABLE users RENAME COLUMN status TO account_status;

-- Modify a column's data type
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

-- Drop a table entirely (Careful!)
DROP TABLE users CASCADE;

3. DML: Data Manipulation Language

DML commands handle the actual data living inside your tables.

Inserting Data

-- Insert a single row
INSERT INTO users (username, email, age) 
VALUES ('johndoe', 'john@example.com', 28);

-- Insert multiple rows
INSERT INTO users (username, email, age) 
VALUES 
    ('janedoe', 'jane@example.com', 32),
    ('bobb', 'bob@example.com', 21);

-- Insert from another table
INSERT INTO active_users (username, email)
SELECT username, email FROM users WHERE account_status = 'active';

Updating Data

-- Always use a WHERE clause! Without it, you update every row.
UPDATE users 
SET account_status = 'inactive', last_login = CURRENT_TIMESTAMP
WHERE last_login < CURRENT_DATE - INTERVAL '1 year';

Deleting Data

-- Delete specific rows
DELETE FROM users WHERE account_status = 'banned';

-- Delete all rows (but keep the table structure)
TRUNCATE TABLE users; 
-- TRUNCATE is faster than DELETE because it doesn't log individual row deletions.

4. DQL: Data Query Language (The Basics)

Selecting and Filtering

-- Select specific columns and alias them
SELECT username AS user, email AS contact
FROM users
WHERE age BETWEEN 18 AND 30
  AND account_status IN ('active', 'pending')
  AND username LIKE 'j%'; -- Starts with 'j'

Pattern Matching (`LIKE` vs `ILIKE`)

%: Represents zero, one, or multiple characters.
_: Represents a single character.
LIKE: Case-sensitive match.
ILIKE: Case-insensitive match (PostgreSQL specific).

5. The Art of Joining Tables

Relational databases shine when combining data. Understanding joins is non-negotiable.

The Join Types

INNER JOIN: Returns only rows that have matching values in BOTH tables.
LEFT JOIN: Returns all rows from the left table, and matched rows from the right. Unmatched right rows return NULL.
RIGHT JOIN: Returns all rows from the right table, and matched rows from the left.
FULL OUTER JOIN: Returns all rows when there is a match in either table.
CROSS JOIN: Returns the Cartesian product (every row combined with every row).

Join Example

SELECT 
    o.order_id, 
    u.username, 
    p.product_name, 
    o.total_amount
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
LEFT JOIN products p ON o.product_id = p.product_id
WHERE o.status = 'completed';

6. Aggregation and Grouping

Aggregation condenses multiple rows into a single summary row.

SELECT 
    department_id,
    COUNT(*) AS total_employees,
    AVG(salary) AS average_salary,
    MAX(salary) AS highest_salary
FROM employees
WHERE hire_date > '2020-01-01' -- Filters BEFORE grouping
GROUP BY department_id
HAVING COUNT(*) > 5 -- Filters AFTER grouping
ORDER BY average_salary DESC;

7. Advanced: Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row, without collapsing them into a single output row like GROUP BY does.

Syntax

FUNCTION_NAME() OVER (PARTITION BY col1 ORDER BY col2)

Common Window Functions

ROW_NUMBER(): Unique sequential integer per row within the partition.
RANK(): Rank of the current row with gaps (e.g., 1, 1, 3).
DENSE_RANK(): Rank with no gaps (e.g., 1, 1, 2).
LAG(col, offset): Access data from a previous row.
LEAD(col, offset): Access data from a subsequent row.

Example: Running Total and Ranking

SELECT 
    employee_id,
    department_id,
    salary,
    -- Rank employees by salary within their department
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_salary_rank,
    -- Calculate a running total of salaries for the whole company
    SUM(salary) OVER (ORDER BY hire_date) as running_total_salary
FROM employees;

8. Advanced: Common Table Expressions (CTEs)

CTEs (WITH clauses) allow you to create temporary result sets that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement. They drastically improve query readability compared to nested subqueries.

WITH HighValueCustomers AS (
    SELECT user_id, SUM(total_amount) as lifetime_value
    FROM orders
    GROUP BY user_id
    HAVING SUM(total_amount) > 10000
),
RecentOrders AS (
    SELECT user_id, MAX(order_date) as last_order
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.username, 
    hvc.lifetime_value, 
    ro.last_order
FROM users u
INNER JOIN HighValueCustomers hvc ON u.user_id = hvc.user_id
INNER JOIN RecentOrders ro ON u.user_id = ro.user_id;

9. Performance Optimization Basics

Writing a query that works is step one. Writing a query that runs fast on 10 million rows is step two.

Avoid SELECT *: Only request the columns you actually need to reduce memory and network IO.
Use Indexes: An index is a data structure (usually a B-Tree) that improves the speed of data retrieval.
```
CREATE INDEX idx_users_email ON users(email);
```
Analyze Your Query: Use EXPLAIN or EXPLAIN ANALYZE before your query to see the database’s execution plan.
```
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@test.com';
```

Ready to Master SQL?

A cheat sheet provides the theory, but muscle memory comes from practice.

👉 Start practicing interactively on our platform today! We offer hands-on SQL labs, interactive sandboxes, and complex real-world schemas where you can write and execute these queries directly in your browser.