MySQL Fundamentals Course

Connecting to a Database Server for the First Time

This chapter introduces MySQL/MariaDB, explains what relational databases are and why they exist, and walks through connecting to the server with the mysql CLI, listing databases, inspecting table structure with DESCRIBE, and querying the information_schema metadata database.

Designing the Readify Schema: Databases and Tables

This chapter creates the complete Readify database schema — four tables with proper data types (INT, DECIMAL, VARCHAR, DATE, DATETIME, ENUM), constraints (NOT NULL, DEFAULT, AUTO_INCREMENT, UNIQUE, FOREIGN KEY), and referential integrity, with detailed explanation of every design decision.

Populating the Catalogue: INSERT and SELECT

This chapter populates the Readify books and customers tables using multi-row INSERT statements and introduces SELECT — reading specific columns, renaming them with AS aliases, and filtering rows with WHERE using equality and comparison operators.

Precise Retrieval: Filtering and Sorting Data

This chapter covers the full WHERE toolkit — AND, OR, NOT, BETWEEN, LIKE pattern matching, IN membership tests, IS NULL checks — plus ORDER BY for deterministic sorting with multiple columns and LIMIT/OFFSET for paginating results.

Modifying and Removing Records: UPDATE and DELETE

This chapter covers UPDATE and DELETE — modifying existing rows with calculated expressions, safely scoping changes with WHERE, understanding the Rows matched output, and contrasting DELETE with TRUNCATE including their different effects on AUTO_INCREMENT counters.

Summarising Data: Aggregate Functions and GROUP BY

This chapter covers COUNT, SUM, AVG, MIN, and MAX aggregate functions, GROUP BY for per-group summaries, and HAVING for filtering aggregated results — applied to the Readify catalogue and orders data for real revenue and inventory reports.

Connecting Tables: Joins

This chapter teaches INNER JOIN and LEFT JOIN across the Readify four-table schema — joining orders to customers, chaining three and four tables together, finding customers with no orders using the LEFT JOIN + IS NULL pattern, and combining joins with GROUP BY for customer spending totals.

Making Queries Fast: Indexes and EXPLAIN

This chapter explains B-tree indexes, how to read EXPLAIN query plans to identify full table scans and sort operations, how CREATE INDEX changes execution from type:ALL to type:ref, and the write-cost trade-off that determines when indexes are worth adding.

Data Integrity Under Pressure: Transactions

This chapter covers ACID transactions — START TRANSACTION, COMMIT, ROLLBACK, and SAVEPOINT — demonstrated by placing a multi-step order atomically, rolling back on a foreign key violation, and using savepoints for partial rollback within a long transaction.

Reusable Query Logic: Subqueries and Views

This chapter covers subqueries in WHERE (scalar comparisons, IN, EXISTS), derived tables in FROM for multi-level aggregation, and CREATE VIEW to encapsulate reusable join and aggregation logic as a named, always-current database object.

MySQL Fundamentals: Course Summary

Final MySQL Assessment

Ten multiple-choice questions covering the core MySQL concepts from this course: data types, AUTO_INCREMENT, GROUP BY vs HAVING, INNER vs LEFT JOIN, NULL testing, EXPLAIN, transactions, DELETE safety, LIKE patterns, and TRUNCATE vs DELETE.