PostgreSQL, Indexing & Relational Data Modeling

ACID Transactions, B-Tree Indexes, EXPLAIN ANALYZE & Query Tuning

6 modules, 27 lessons: Relational Databases And Simple Queries; Joins And Relationships; Aggregation And Grouping; Advanced Querying; Schema Design And Modification; Performance And Application Integration.

Beginner · 2 hours · 27 lessons · 81 labs

Who it is for

Beginner level.

For anyone who works with data in tables, such as analysts, testers and new developers, and wants to ask a database questions directly. You start from what a table is and why it beats a file.

What you don't need yet:

• No other programming language: SQL is the only code you write
• No database to install: labs run in your browser
• You don't need PostgreSQL yet: practice uses SQLite

What helps:

• Comfort with tables of rows and columns, as in a spreadsheet

Before you start:

• Basic understanding of tabular data

Take next:

• Modern API Architecture: REST, GraphQL & gRPC — Intermediate
• Scalable System Design & Distributed Architecture — Advanced

Learning paths:

• Distributed Systems & Backend Architecture

Nobody needs to know everything before they start. Each course builds on the one before, so a learning path takes you there step by step.

Labs

81 labs, 54 of them graded: you write the code, it runs, and graded labs check the result. The outline lists each lesson's labs.

What is free

Start free: First 3 lessons free with an account. Pro: Every course, lab, handbook, practice exam and certificate.

• Pro monthly — $29 a month. Every course, lab, handbook, practice exam and certificate
• Pro yearly — $249 a year. Every course, lab, handbook, practice exam and certificate

Course outline

Relational Databases And Simple Queries

1. Why Tables And Relations Beat Files (5 labs): You can say what a relational database is, name the three things a flat file cannot do for you, and show a database refusing to store a row that points at a customer who does not exist.
2. Data Types And What To Choose (7 labs): You can choose a column type on purpose: whole numbers for money, text in a sortable date format, and a strict table when you want SQLite to reject the wrong type rather than store it.
3. The SELECT Statement And Projection (6 labs): You can read and write a select statement, choose exactly the columns you want, name and compute new ones with aliases, remove duplicates with distinct, and say why select star belongs at a shell and not in application code.
4. Filtering With The WHERE Clause (7 labs): You can filter rows with comparisons, combine conditions knowing that AND binds tighter than OR, use LIKE, IN and BETWEEN, and handle an empty cell correctly with IS NULL rather than an equals sign.
5. Ordering And Limiting Results (6 labs): You can sort a result by one or several keys, predict where empty cells land, take a page of rows with limit and offset, and say why offset paging drifts while keyset paging does not.

Joins And Relationships

1. Keys, Constraints And Referential Integrity (5 labs): You can declare primary keys, unique, not null, check and foreign key constraints, choose a delete action on purpose, and explain why the database is a better place for these rules than your application code.
2. Inner Joins And Table Aliases (4 labs): You can join two or three tables with an explicit inner join, use table aliases to keep the query readable, qualify every ambiguous column, and say what an inner join drops.
3. What An Outer Join Really Returns (5 labs): You can predict exactly which rows and which nulls a left join produces, write an anti join to find rows with no match, and avoid the two classic mistakes: counting the wrong column and filtering the outer side in the wrong clause.
4. Cross Joins And Cartesian Bugs (4 labs): You can recognise an accidental cartesian product from its row count, name the two ways it happens, and use a deliberate cross join to build a complete grid so that missing combinations report zero instead of disappearing.

Aggregation And Grouping

1. Aggregate Functions (3 labs): You can use count, sum, average, minimum and maximum to turn rows into useful measurements while handling null values deliberately.
2. The GROUP BY Clause (3 labs): You can group rows by a column and calculate one aggregate result for each group.
3. Grouping By Multiple Columns (3 labs): You can form groups from combinations of columns and choose a grain that matches the report question.
4. The Difference Between WHERE And HAVING (3 labs): You can place row filters in where, group filters in having, and explain why moving a condition changes a total.

Advanced Querying

1. Subqueries (3 labs): You can use scalar, list and correlated subqueries to express a question in terms of another query.
2. Common Table Expressions (3 labs): You can name intermediate query results with common table expressions and compose readable multi stage statements.
3. Window Functions: Replacing Application Loops (2 labs): You can add totals and comparisons to each row with window functions instead of fetching rows and looping in application code.
4. Advanced Window Functions: Ranking And Framing (2 labs): You can rank rows within groups and choose a window frame for running or moving calculations.

Schema Design And Modification

1. Normalisation To Third Normal Form (1 lab): You can decompose repeating data into related tables and explain first, second and third normal form.
2. When To Denormalise On Purpose (1 lab): You can recognise deliberate denormalisation and protect duplicated values with a clear source and update strategy.
3. Inserting Data Safely (1 lab): You can insert rows with explicit columns, parameters and constraint checks, and verify the rows written.
4. Updating And Deleting Safely (1 lab): You can preview a target set, update or delete it with a precise predicate, and verify the affected row count.
5. Transactions And ACID Properties (1 lab): You can group related writes in a transaction and explain atomicity, consistency, isolation and durability.
6. Isolation Levels And The Lost Update (1 lab): You can explain a lost update, choose an isolation strategy, and use a conditional write to prevent stale changes.

Performance And Application Integration

1. Indexes And What They Cost (1 lab): You can choose an index for a real predicate and explain its storage, write and maintenance costs.
2. Reading An EXPLAIN Plan (1 lab): You can read a basic SQLite query plan, spot a scan and verify whether an index is being used.
3. The N Plus One Problem (1 lab): You can recognise database side N plus one behaviour and replace per row queries with one set based query.
4. Where PostgreSQL Differs From SQLite (1 lab): You can identify important PostgreSQL differences while keeping runnable practice in SQLite.