Databases

Hands-on database skills: SQL, schema design, indexing, and working with an ORM.

Beginner

  1. What is a Database?: A system that stores your data in an organized shape you can search, filter, and change directly — not just a file you save data into.
  2. Tables, Rows & Columns: The basic grid shape — like a spreadsheet — that relational databases use to organize data.
  3. Primary Keys & Foreign Keys: How a database uniquely identifies one specific row, and how rows in different tables point to each other.
  4. Basic SQL Queries: The four core commands — SELECT, INSERT, UPDATE, DELETE — used to read and change data in a database.
  5. Filtering & Sorting: Narrowing a query down to the rows you actually want with WHERE, and controlling the order results come back in with ORDER BY.

Intermediate

  1. Joins: Combining matching rows from two or more tables into a single set of results, using the foreign keys that link them.
  2. Aggregation: Collapsing many rows down into a single summary value — a count, a total, an average — instead of listing every individual row.
  3. Indexes: A separate lookup structure the database maintains so it can jump straight to matching rows instead of scanning the whole table.
  4. Normalization: Organizing tables so each piece of information is stored in exactly one place, instead of copied and repeated everywhere it's needed.
  5. Constraints: Rules attached directly to a table's columns that the database itself enforces, so invalid data can never be saved in the first place.
  6. Transactions & ACID: Grouping several database operations into one all-or-nothing unit, so a failure partway through never leaves data half-changed.
  7. ORMs: A library that lets application code work with database rows as regular objects, generating SQL behind the scenes instead of you writing it by hand.
  8. PostgreSQL in Depth: The PostgreSQL-specific features beyond standard SQL — richer data types, auto-incrementing ids, and the psql command-line client.
  9. Subqueries & CTEs: Nesting one query inside another, or naming a step with WITH, to break a complex question into smaller, readable pieces.
  10. Pagination: Returning a large result set in smaller pages instead of all at once, using LIMIT/OFFSET or a keyset approach.

Advanced

  1. Database Migrations: Small, versioned scripts that change a database's schema over time, so every environment ends up with the same structure in the same order.
  2. Query Optimization: Reading what a database actually plans to do to execute a query, and using that to figure out why it's slow and how to speed it up.
  3. Connection Pooling: Keeping a small set of already-open database connections ready to reuse, instead of opening (and closing) a brand-new one for every request.
  4. NoSQL Data Modeling: Designing the shape of a document to match how it'll actually be read, often by embedding related data together instead of splitting it across normalized tables.
  5. Window Functions: Calculations across a set of related rows — like a running total or a rank — without collapsing them into a single row the way GROUP BY does.
  6. Upsert & Conflict Handling: Inserting a row, or updating it instead if it already exists, in a single atomic statement.