Course
SQL & Database Fundamentals
177 lessons across 12 modules
Beginner to advanced, with no prerequisites. The concepts are database-independent and the exercises run on PostgreSQL: table design, filtering, aggregation, joins, subqueries and CTEs, changing data safely, constraints, window functions, indexing and EXPLAIN, and transactions - ending in a learning platform database built from requirements. The road is laid out in full; lessons are being written one at a time.
Database and SQL Fundamentals
What a database is, and a first query against one
What is a Database?
1Organised, persistent data that many people can safely use at once.
Two people editing the same record, and what should happen
Database vs Spreadsheet
2Where a spreadsheet stops - concurrency, integrity, and scale.
A shared sheet with three versions of the same customer
Relational Databases
3Data in tables, connected by the values they share.
Customers and orders linked by an id
What is SQL?
4A declarative language - you say what you want, not how to get it.
One query replacing a loop over every row
SQL vs NoSQL
5Relational against document, key-value, and graph - an honest comparison.
The same data in a table and in a document
Tables, Rows and Columns
6Columns are the shape, rows are the facts.
A users table read row by row
Primary Keys
7The idea - one value that identifies exactly one row.
Two customers named John, told apart by their key
Foreign Keys
8The idea - a column that points at a row in another table.
An order that knows which customer placed it
Relationships
9One-to-one, one-to-many, and many-to-many.
Students and courses joined through enrollments
PostgreSQL Setup
10Installing PostgreSQL locally or running it in a container.
A database running in one command
SQL Clients
11psql and graphical clients, and when each is better.
The same query run in the terminal and in a GUI
Your First SQL Query
12Writing, running, and reading the result of a query.
Listing every row in a table, then just one
Database Design and Tables
Creating tables, and choosing the right type for each column
Creating a Database
13A database as a container for tables, and naming it.
A learning database created and connected to
Creating Tables
14Designing columns before writing any SQL.
A users table sketched on paper first
SQL Data Types
15Why the type matters - storage, validation, and sorting.
Dates stored as text sorting in the wrong order
INTEGER
16Whole numbers, their ranges, and BIGINT for ids.
An id column that ran out of numbers
VARCHAR
17Text with a maximum length, and whether the limit is worth having.
A name truncated by a limit that was too small
TEXT
18Unlimited text - and in PostgreSQL, no slower than VARCHAR.
Choosing between TEXT and VARCHAR for an email
BOOLEAN
19True, false, and the third value, NULL.
An is_active flag that is neither true nor false
DATE
20A calendar day with no time and no time zone.
A birthday that should not shift across time zones
TIMESTAMP
21Date and time - and why TIMESTAMPTZ is usually the right one.
A created_at that is wrong for every user abroad
NUMERIC
22Exact decimals, and never storing money as a float.
0.1 + 0.2 in a float column against NUMERIC
UUID
23Globally unique ids, and their trade-offs against integers.
Ids that can be generated before the row is saved
CREATE TABLE
24The full statement, column by column.
The users table from the sketch, written out
Primary Key
25Declaring a primary key when the table is created.
An id column with PRIMARY KEY and a generated value
Foreign Key
26Declaring a foreign key with REFERENCES.
An orders table that references users
NOT NULL
27Declaring that a column must always have a value.
An email that can never be missing
UNIQUE
28Declaring that no two rows may share a value.
Two accounts with the same email, prevented
DEFAULT
29A value used when an insert does not supply one.
created_at filled in automatically
CHECK
30A rule every row must satisfy.
An age that cannot be negative
SELECT and Filtering
Asking for exactly the rows and columns you want
SELECT
31The statement you will write more than any other.
Reading from a table for the first time
Selecting Specific Columns
32Naming the columns you need, and only those.
Name and email, not the whole row
SELECT *
33Every column - fine for exploring, risky in application code.
A new column breaking code that used SELECT *
Column Aliases
34Renaming a column in the result with AS.
A computed column given a readable name
DISTINCT
35Removing duplicate rows from a result.
Every city customers live in, each listed once
WHERE
36Keeping only the rows that match a condition.
Only the users who signed up this year
Comparison Operators
37Equals, not equals, greater and less than.
Orders over a threshold
AND
38Requiring several conditions at once.
Adults who are also active
OR
39Accepting either condition - and the brackets it needs beside AND.
A missing bracket returning far too many rows
NOT
40Negating a condition, and how NULL complicates it.
A NOT that silently drops rows with NULLs
IN
41Matching any value in a list.
Three statuses in one condition instead of three ORs
BETWEEN
42An inclusive range - and the trap with timestamps.
A date range that misses the whole last day
LIKE
43Pattern matching with % and _.
Every email at one domain
ILIKE
44Case-insensitive matching, specific to PostgreSQL.
A search that finds John, john, and JOHN
IS NULL
45Testing for a missing value - and why = NULL never works.
A WHERE email = NULL that returns nothing
IS NOT NULL
46Keeping only rows that have a value.
Users who have added a phone number
CASE
47Conditional values inside a query.
Scores turned into grades in the result
COALESCE
48The first value that is not NULL.
A display name falling back to the email
Sorting, Grouping and Aggregation
Ordering results, and summarising many rows into few
ORDER BY
49Sorting a result - and results having no order without it.
A query that happened to be sorted until it was not
Ascending vs Descending
50ASC, DESC, sorting on several columns, and where NULLs go.
Newest first, then alphabetical for ties
LIMIT
51Returning only the first rows of a sorted result.
The top ten highest-scoring students
OFFSET
52Skipping rows - the syntax behind page two, three, and four.
Page three of a list, twenty rows per page
COUNT
53COUNT(*) against COUNT(column), and what NULL does to each.
Two counts on the same table that disagree
SUM
54Adding a column up across rows.
Total revenue for the month
AVG
55The mean, and NULLs being skipped rather than counted as zero.
An average that ignored the students with no score
MIN
56The smallest value - numbers, dates, or text.
The earliest signup date
MAX
57The largest value.
The most recent order per customer, as a first attempt
GROUP BY
58One result row per group, instead of one per input row.
Employee count per department
HAVING
59Filtering groups after they have been aggregated.
Departments with more than five employees
Aggregate Functions
60How aggregates treat NULLs and empty groups, all together.
SUM of no rows returning NULL, not zero
Grouping Multiple Columns
61A group for every distinct combination of values.
Revenue per region per month
Filtering Aggregated Data
62WHERE before grouping against HAVING after - and choosing between them.
The same filter in both places, and different answers
Joins
Combining tables through the values they share
Why Joins?
63Data split across tables for good reasons, and put back together.
An order list that needs customer names beside it
Primary Key and Foreign Key Relationships
64How a join walks from a foreign key to the row it points at.
orders.user_id matched to users.id, drawn out
INNER JOIN
65Only the rows that match on both sides.
Users who have placed at least one order
LEFT JOIN
66Every row on the left, matched where possible, NULL where not.
Every user, including those with no orders
RIGHT JOIN
67The mirror of LEFT JOIN - and why most people rewrite it as one.
The same query as a RIGHT and as a LEFT join
FULL OUTER JOIN
68Every row from both sides, matched or not.
Reconciling two lists to find what is missing from each
CROSS JOIN
69Every combination of rows - occasionally useful, often an accident.
Every size paired with every colour
Self Join
70Joining a table to itself.
Each employee listed beside their manager
Multiple Joins
71Chaining joins, and the order they are read in.
Users, their orders, and the products in each order
Joining Multiple Tables
72Many-to-many through a join table.
Students and courses joined through enrollments
Join Conditions
73ON against WHERE, and where a filter belongs in an outer join.
A filter in WHERE that turned a LEFT JOIN into an INNER one
Common Join Mistakes
74Missing conditions, fan-out, and duplicated totals.
A revenue figure doubled by joining through the wrong table
Subqueries and CTEs
Queries inside queries, and naming the steps
What is a Subquery?
75A query used as a value, a list, or a table inside another.
Users who spent more than the average
Scalar Subqueries
76A subquery returning exactly one value - and failing if it returns two.
The latest order date shown on every row
Subqueries with WHERE
77Filtering against a computed list or value.
Products that have never been ordered
Subqueries with FROM
78A subquery acting as a temporary table.
Aggregating, then filtering the aggregate
Correlated Subqueries
79A subquery that refers to the outer row - and runs once per row.
Each customer and their own most recent order
EXISTS
80True as soon as one matching row is found.
Users who have at least one completed enrollment
NOT EXISTS
81The safe anti-join - and why NOT IN breaks on NULLs.
A NOT IN returning nothing because of one NULL
Common Table Expressions
82Naming the intermediate steps of a query.
A four-level nested query rewritten as three named steps
WITH
83The syntax, and reading a CTE top to bottom.
An active_users step used by the final select
Multiple CTEs
84Several steps, each building on the one before.
Filter, aggregate, then rank, as three named stages
Recursive CTEs
85Walking a hierarchy of unknown depth.
Every lesson under a course, however deeply nested
CTE vs Subquery
86Readability, reuse, and what the planner does with each.
The same query both ways, and the plan for each
INSERT, UPDATE and DELETE
Changing data, safely
INSERT
87Adding rows, and naming the columns you are filling.
An insert that breaks when a column is added, and one that does not
Single Row Insert
88One row, and getting its generated id back with RETURNING.
Inserting a user and using the new id straight away
Multiple Row Insert
89Many rows in one statement, and why it is faster.
A thousand rows in one statement against a thousand statements
INSERT ... SELECT
90Inserting the result of a query.
Copying last month rows into an archive table
UPDATE
91Changing values in existing rows.
Marking one order as shipped
Updating Multiple Rows
92Updating by condition, and updating from another table.
Applying a price change to a whole category
DELETE
93Removing rows.
Deleting one expired session
Conditional Delete
94Deleting by condition - and checking the condition first.
Running the WHERE as a SELECT before the DELETE
TRUNCATE
95Emptying a table fast, and what it skips that DELETE does not.
TRUNCATE against DELETE on a million-row table
Upsert
96Insert if new, update if it exists - in one statement.
Recording progress whether or not a row exists yet
PostgreSQL ON CONFLICT
97DO NOTHING against DO UPDATE, and the excluded row.
An upsert that keeps the higher of two scores
Safe Data Modification
98Transactions, RETURNING, and never running a bare UPDATE.
An UPDATE with no WHERE, and how to survive one
Constraints and Data Integrity
Making the database refuse bad data
Primary Keys
99Choosing a key - surrogate against natural, and composite keys.
An email used as a primary key, and the day it had to change
Foreign Keys
100What the database enforces, and what it costs on writes.
An order for a user who does not exist, refused
Unique Constraints
101Adding uniqueness to a table that already has duplicates.
Finding and fixing duplicates before the constraint will apply
Not Null
102Making an existing column required.
Backfilling NULLs before adding the constraint
Check Constraints
103Business rules the database enforces, named so errors are readable.
An end date that must come after the start date
Default Values
104Changing a default, and what happens to rows already stored.
A new default that does not rewrite old rows
Referential Integrity
105No reference ever points at a row that is not there.
Orphaned rows in a database without foreign keys
Cascade
106Letting one change ripple through related rows - deliberately.
Deleting a course and its lessons going with it
ON DELETE
107CASCADE, SET NULL, RESTRICT - choosing per relationship.
Deleting a user without deleting their invoices
ON UPDATE
108Propagating a changed key, and why stable keys avoid the need.
A renamed code updating every row that uses it
Data Validation
109What to validate in the application and what in the database.
A rule enforced in both places, and why
Database-Level Integrity
110The database as the last line of defence for every client.
A second service writing data the first would have rejected
Advanced SQL
Window functions, conditional aggregates, and richer types
Window Functions
111Calculating across related rows without collapsing them into groups.
Each salary shown beside its department average
ROW_NUMBER
112A unique sequence number within each partition.
The latest order per customer, done properly
RANK
113Ranking with gaps after ties.
Two students tied for second, and nobody in third
DENSE_RANK
114Ranking without gaps.
The same tie, with the next student placed third
LEAD
115Reading a value from the next row.
The time until each user next logged in
LAG
116Reading a value from the previous row.
Month-over-month revenue change
Running Totals
117Cumulative sums with a window frame.
Revenue to date, day by day
Partitioning Data with OVER
118PARTITION BY, ORDER BY, and frames inside OVER.
A running total that restarts each month
Conditional Aggregation
119Aggregating only some rows with CASE inside the aggregate.
Completed and abandoned counts in one row
FILTER
120The cleaner syntax for the same thing.
The previous query rewritten with FILTER
STRING_AGG
121Joining many values into one string, in order.
Every tag on a course as one comma-separated list
Date Functions
122Truncating, extracting, and date arithmetic.
Signups grouped by week
String Functions
123Case, trimming, splitting, and concatenation.
Normalising emails before comparing them
JSON Operations
124Reading from and querying JSON columns.
Filtering rows by a value inside a JSON document
PostgreSQL Arrays
125Array columns, and when a separate table is the better choice.
Tags as an array against tags as their own table
Indexing and Query Performance
Making queries fast, and proving that they are
What is an Index?
126A separate sorted structure that lets the database skip most rows.
A book index against reading every page
Why Indexes?
127Faster reads, paid for with slower writes and more storage.
A lookup going from seconds to milliseconds
B-Tree Index
128The default index, and which operators it can serve.
Why an index helps = and < but not a leading-wildcard LIKE
Composite Index
129Several columns, and why their order decides what it can serve.
An index on (a, b) helping a query on a but not on b
Unique Index
130An index that also enforces uniqueness.
The index created behind every UNIQUE constraint
Partial Index
131Indexing only the rows you actually query.
An index over active users only, a tenth of the size
Expression Index
132Indexing a computed value.
An index on lower(email) for case-insensitive lookups
Index Selectivity
133Why an index on a column with three values rarely gets used.
An index on status that the planner ignores
When NOT to Create an Index
134Small tables, write-heavy tables, and indexes nothing uses.
Finding and dropping unused indexes
EXPLAIN
135The plan the database intends to use.
Reading a plan from the innermost node outwards
EXPLAIN ANALYZE
136Running the query and comparing estimates with reality.
A row estimate off by a factor of a thousand
Sequential Scan
137Reading every row - and when that is genuinely the right choice.
A seq scan beating an index on a small table
Index Scan
138Index scans, index-only scans, and bitmap scans.
A covering index answering a query without the table
Query Optimization
139Measure, change one thing, and measure again.
A slow report fixed with one index and one rewrite
N+1 Query Problem
140What it looks like from the database side, and fixing it with a join.
A hundred and one queries in the log for one page
Pagination Performance
141Why deep OFFSET pages slow down, and keyset pagination instead.
Page 5000 taking seconds, then milliseconds
Transactions and Database Programming
Correctness under concurrency, and logic inside the database
What is a Transaction?
142Several statements that succeed or fail as one.
A transfer that debits but never credits
BEGIN
143Opening a transaction, and autocommit when you do not.
Every statement committed on its own by default
COMMIT
144Making the changes permanent and visible to others.
A change invisible to another session until commit
ROLLBACK
145Abandoning every change since BEGIN, and savepoints.
Rolling back half a transaction to a savepoint
ACID
146The four guarantees a transaction makes.
Each guarantee matched to the failure it prevents
Atomicity
147All or nothing, even across a crash.
Power lost halfway through, and nothing half-applied
Consistency
148Every transaction leaving the constraints satisfied.
A transaction rejected for breaking a foreign key
Isolation
149Concurrent transactions not seeing each other half-finished.
Two sessions reading the same row mid-change
Durability
150Committed means it survives a crash.
The write-ahead log, in one diagram
Isolation Levels
151Read committed, repeatable read, serializable - and their anomalies.
A lost update at read committed, prevented at serializable
Deadlocks
152Two transactions each waiting for the other, and avoiding it.
Locking rows in a consistent order
Locks
153Why the database locks, and what waits on what.
A migration blocked by one long-running query
Row-Level Locks
154SELECT FOR UPDATE, and SKIP LOCKED for job queues.
Two workers never picking up the same job
Stored Procedures
155Logic run inside the database, with transaction control.
A procedure that archives and deletes in one call
Functions
156Reusable SQL that returns a value or a table.
A function computing course completion percentage
Triggers
157Code that runs automatically on a change - used sparingly.
An updated_at column that maintains itself
Views
158A saved query that behaves like a table.
A view that hides sensitive columns from reports
Materialized Views
159A stored result, refreshed on your schedule.
An expensive dashboard query computed hourly
Real-World SQL Project
A learning platform database, from requirements to review
Build the API on top: the Node.js course →Analyze Requirements
160Turning a product description into questions the data must answer.
A list of reports the platform will need
Design Entities
161Tenants, users, roles, courses, modules, lessons, and enrollments.
Deciding what is an entity and what is an attribute
Create ER Diagram
162Drawing entities and relationships before any SQL.
The whole schema on one page
Create Database
163The project database, and its roles and permissions.
An application role that cannot drop tables
Create Tables
164Every table, with types chosen deliberately.
A migration file per table
Add Relationships
165Foreign keys, including the many-to-many join tables.
Enrollments linking users to courses
Add Constraints
166Every rule the business relies on, enforced by the database.
A progress value that must stay between 0 and 100
Insert Sample Data
167Realistic seed data, in volume, generated rather than typed.
Ten thousand enrollments from generate_series
Write Basic Queries
168The everyday reads the application needs.
A learner dashboard listing their courses
Write Join Queries
169Queries spanning the course hierarchy.
Every lesson a learner has not yet completed
Build Reporting Queries
170Aggregates the business actually asks for.
Completion rate per course per tenant
Write CTE Queries
171Multi-step reports broken into named stages.
A funnel from enrollment to completion
Write Window Function Queries
172Rankings and trends across the data.
Top learners per course, and weekly growth
Add Indexes
173Indexes chosen from the queries you actually wrote.
One composite index serving three queries
Analyze Queries
174EXPLAIN ANALYZE on every important query.
A plan table for the whole project
Optimize Slow Queries
175Fixing the worst offender, and measuring the result.
The slowest report made fifty times faster
Implement Transactions
176The operations that must be atomic, done correctly.
Enrolling in a course and creating its progress rows together
Final Database Review
177The schema reviewed against the original requirements.
The finished database, and what you would change next time