MySQL Database Course

Lesson 1 • Installing and Configuring MySQL

Guides students through platform-specific installation and initial server configuration. Ensures a working local environment for all subsequent exercises.

Lesson 2 • MySQL Architecture and Components

Explains how MySQL processes queries, manages storage engines, and handles client connections. Provides the mental model needed to troubleshoot and optimize later.

Lesson 3 • Connecting to MySQL with Client Tools

Introduces the MySQL command-line client and MySQL Workbench as primary interfaces. Students practice connecting, authenticating, and issuing basic commands.

Lesson 1 • Data Modeling Concepts

Introduces entity-relationship modeling and translating business requirements into logical schemas. Provides the design vocabulary used in every subsequent schema decision.

Lesson 2 • Constraints and Referential Integrity

Covers PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY constraints with cascade options. Ensures students can enforce data integrity at the schema level.

Lesson 3 • Normalization Principles

Covers first through third normal forms and the rationale for eliminating redundancy. Students apply normalization rules to refine raw schemas before implementation.

Lesson 4 • Creating Databases and Tables with DDL

Teaches CREATE DATABASE, CREATE TABLE, and column definition syntax. Students build the physical schema that supports all data manipulation exercises ahead.

Lesson 1 • Deleting Records Safely

Explains DELETE and TRUNCATE with their behavioural differences and risks. Students apply safe-delete patterns to avoid accidental data loss.

Lesson 2 • Updating Existing Records

Teaches UPDATE with WHERE clauses, expressions, and multi-table updates. Students learn to modify targeted rows safely without unintended side effects.

Lesson 3 • REPLACE and INSERT ON DUPLICATE KEY

Introduces upsert patterns for handling duplicate key conflicts gracefully. Students implement idempotent data-loading routines common in ETL workflows.

Lesson 1 • Working with NULL Values

Explains NULL semantics, IS NULL, IS NOT NULL, and COALESCE for safe NULL handling. Prevents common query bugs caused by misunderstanding three-valued logic.

Lesson 2 • Built-in String, Date, and Math Functions

Surveys essential MySQL functions for transforming strings, dates, and numbers inline. Students enrich query output without requiring application-layer post-processing.

Lesson 3 • Basic SELECT Syntax and Filtering

Covers SELECT, FROM, WHERE, and comparison operators for targeted data retrieval. Establishes the query-writing foundation all advanced SELECT techniques build upon.

Lesson 4 • Aggregate Functions and GROUP BY

Introduces COUNT, SUM, AVG, MIN, MAX, and GROUP BY for summarising data. Students generate summary reports that answer common business questions.

Lesson 1 • INNER JOIN and Equi-Joins

Covers INNER JOIN syntax for returning only matching rows from both tables. Students practice the most common join type used in production queries.

Lesson 2 • Understanding JOIN Fundamentals

Explains how MySQL matches rows across tables using join conditions and keys. Provides the conceptual model that prevents Cartesian products and incorrect results.

Lesson 3 • Self-Joins and Cross Joins

Introduces joining a table to itself and generating all row combinations with CROSS JOIN. Students solve hierarchical and combinatorial query problems.

Lesson 4 • Outer Joins: LEFT, RIGHT, and FULL

Teaches LEFT JOIN and RIGHT JOIN for preserving unmatched rows from one side. Students identify missing relationships and handle optional associations correctly.

Lesson 1 • Analysing Queries with EXPLAIN

Teaches reading EXPLAIN and EXPLAIN ANALYZE output to identify full table scans. Students use execution plans to pinpoint and fix inefficient queries.

Lesson 2 • Slow Query Log and Profiling

Explains enabling the slow query log and using SHOW PROFILE for deep analysis. Students establish a monitoring routine to catch regressions before they reach production.

Lesson 3 • Query Optimization Techniques

Covers covering indexes, index hints, and query rewriting strategies for speed. Students apply a systematic optimisation workflow to real slow-query scenarios.

Lesson 4 • How MySQL Indexes Work

Explains B-tree and hash index structures and how the optimizer uses them. Builds the mental model required to choose and design effective indexes.

Lesson 1 • Transaction Fundamentals and ACID

Defines atomicity, consistency, isolation, and durability with MySQL examples. Establishes why transactions are essential for data integrity in multi-step operations.

Lesson 2 • Isolation Levels and Read Phenomena

Covers dirty reads, non-repeatable reads, and phantom reads across isolation levels. Students select the appropriate isolation level for each application workload.

Lesson 3 • Locking Mechanisms in InnoDB

Explains row-level, gap, and next-key locks and when MySQL acquires them. Students predict locking behaviour to design queries that minimise contention.

Lesson 1 • Views, Stored Procedures, and Functions

Covers creating views, writing stored procedures, and defining user-defined functions. Students encapsulate reusable logic that simplifies application queries and enforces rules.

Lesson 2 • User Management and Privileges

Explains CREATE USER, GRANT, REVOKE, and role-based access control in MySQL 8. Students configure least-privilege accounts for applications and administrators.

Lesson 3 • Backup and Recovery Strategies

Covers logical backups with mysqldump, binary log backups, and point-in-time recovery. Students design and test a backup plan that meets recovery time objectives.

Lesson 4 • Triggers and Events

Teaches BEFORE and AFTER triggers and the MySQL Event Scheduler for automation. Students implement audit logging and scheduled maintenance without application changes.