MySQL Fundamentals

Databases, Tables, and SQL

A database is just a very organized set of spreadsheets, and SQL is the language you use to ask it questions and give it instructions.

Creating Tables & Choosing Data Types

Before a table can hold any data, CREATE TABLE has to define its columns up front — including what TYPE of value each column is allowed to hold.

Inserting & Querying Data

INSERT is how you add new rows to a table, and SELECT is how you read them back — with WHERE to narrow down which rows you want, and ORDER BY to put them in a sensible order.

Filtering & Sorting: WHERE, LIKE, IN, BETWEEN, NULL

Ask precise questions of your data: combine conditions, search text with patterns, match lists and ranges, handle missing values, and sort and page the results.

Updating & Deleting Data

UPDATE changes existing rows and DELETE removes them entirely — both are permanent the instant they run, and both are genuinely dangerous without a WHERE clause.

Relating Tables with JOIN

Real-world data almost never fits in one giant table — JOIN is how you pull matching rows from two separate, related tables back together into a single result.

JOIN Types in Depth: INNER, LEFT, and RIGHT

The earlier JOIN lesson showed matching rows from two tables — but what happens to rows that DON'T have a match on the other side depends entirely on which type of JOIN you choose.

Aggregate Functions & GROUP BY

COUNT, SUM, AVG and friends squash many rows down into a single summary number — GROUP BY simply runs that same summary separately for each distinct category instead of for the whole table at once.

Business Reports with GROUP BY & HAVING

Turn raw orders into the reports managers ask for: sales per customer, per category, per month, and only the groups that matter.

SQL Functions & CASE: Transforming Values

Clean and reshape data inside the query: text functions, number rounding, dates, and CASE for if/else logic.

Subqueries & Common Table Expressions

Use the result of one query inside another: compare to an average, find rows with no match, and write readable step-by-step queries with WITH.

Window Functions: Rankings & Running Totals

Rank rows, number them within groups and calculate running totals, without collapsing rows like GROUP BY does.

Views: Saving a Query as a Virtual Table

A VIEW lets you save a complicated SELECT under a simple name and query it as though it were an ordinary table — the query itself only has to be written once, correctly, and then reused everywhere.

Indexes and Why They Matter

An index lets MySQL find rows without scanning the entire table one row at a time — the same trick as a book's index letting you jump straight to a page instead of reading it cover to cover.

Transactions: COMMIT and ROLLBACK

A transaction groups several SQL statements into one all-or-nothing unit — either every single one of them succeeds together, or none of them take effect at all, which matters enormously the moment money or related records are involved.

Database Normalization Basics: 1NF, 2NF, 3NF

Normalization is a set of practical guidelines for splitting data into well-organized tables so nothing gets duplicated and nothing gets stuck in an inconsistent state — 1NF, 2NF and 3NF are just three checkpoints along that path, each building on the one before.

Constraints & Foreign Keys: Let the Database Protect Your Data

Make bad data impossible with NOT NULL, UNIQUE, CHECK, DEFAULT and foreign keys with ON DELETE rules.

Changing Tables Safely: ALTER TABLE & Migrations

Add, rename and change columns on a live database without losing data, and keep every change in versioned migration files.

Users, Permissions & Backups

Give each app only the access it needs, and make sure you can restore your data when (not if) something goes wrong.

Project: A School Fees Database

Design and query a complete database for school fees: students, fee structures and M-Pesa payments, with the reports a bursar actually needs.