Create relational tables and query them using MySQL syntax and tools.

Before starting: Basic SQL queries and a disposable local database.

Practice project: Build a constrained schema and compare expected and actual query results.

1. MySQL Architecture & Storage Engines (InnoDB vs MyISAM)

  1. MySQL Introduction: Client-Server Architecture, SQL Standard & MySQL 8.x
  2. Storage Engines: InnoDB vs MyISAM, Memory & the Pluggable Engine Architecture
  3. InnoDB Internals: Buffer Pool, Redo Log, Undo Log & Doublewrite Buffer

2. MySQL Data Types & Storage Architecture

  1. Numeric Data Types: INT, BIGINT, UNSIGNED & Fixed-Point DECIMAL Arithmetic
  2. String Types & Charsets: VARCHAR vs CHAR, TEXT vs BLOB & utf8mb4 Collation
  3. Temporal Data Types: DATE, DATETIME vs TIMESTAMP & Automated Timezone Conversions
  4. Native JSON Data Type: Binary Storage, Operators (->, ->>) & JSON Functions

3. DDL: Database, Table Creation & Constraints

  1. DDL Statements: CREATE, ALTER, DROP & MySQL 8 Atomic DDL Architecture
  2. Constraints & Data Integrity: PRIMARY KEY, FOREIGN KEY (CASCADE/RESTRICT), UNIQUE & CHECK

4. DML: High-Performance Data Insertion & Mutation

  1. High-Performance DML: Multi-Row Bulk INSERT & ON DUPLICATE KEY UPDATE (Upsert)
  2. Safe UPDATE & DELETE: LIMIT Clamping, ORDER BY & Multi-Table Joins

5. Querying & Filtering with Advanced Operators

  1. String Pattern Matching: LIKE, ESCAPE & MySQL 8 ICU-Compliant REGEXP_LIKE
  2. Three-Valued Logic: NULL Semantics, IS NULL, COALESCE & the Null-Safe <=> Operator

6. Sorting, Grouping & Analytical Aggregations

  1. Aggregations & Grouping: GROUP BY, HAVING, Aggregate Functions & ONLY_FULL_GROUP_BY

7. Relational Joins & Set Operations

  1. Relational Joins: INNER JOIN, LEFT/RIGHT OUTER JOIN, CROSS JOIN & Join Algorithms

8. Subqueries, Derived Tables & Common Table Expressions (CTEs)

  1. Subqueries & Modern CTEs: Scalar Subqueries, EXISTS vs IN & Recursive WITH CTEs

9. Window Functions & Analytical SQL

  1. Window Functions: OVER(), PARTITION BY, ROW_NUMBER(), RANK() & DENSE_RANK()
  2. Value & Cumulative Windows: LEAD(), LAG(), Running Totals & Moving Averages

10. Views & Virtual/Generated Columns

  1. Views & Virtual Columns: Standard Views, Updatable Views & VIRTUAL vs STORED Generated Columns

11. Stored Procedures, Functions & Control Flow

  1. Stored Procedures & Stored Functions: IN/OUT Parameters, Control Flow & Delimiters

12. Triggers & Event Scheduler Automation

  1. Triggers: BEFORE/AFTER Events, OLD & NEW Pseudorecords & Audit Logging
  2. MySQL Event Scheduler: Automated Recurring Cron Jobs & Table Maintenance

13. Indexing Architecture & EXPLAIN Optimization

  1. Indexing Architecture: B+Tree Anatomy, Clustered vs Secondary Indexes
  2. Composite Indexes: Column Ordering & the Crucial Leftmost Prefix Rule
  3. Covering Indexes: Achieving Zero-Disk-Lookup 'Using Index' Performance
  4. Query Optimization: EXPLAIN Plan Interpretation, Join Types & EXPLAIN ANALYZE
  5. Advanced Indexing: Index Condition Pushdown (ICP) & MySQL 8 Multi-Valued Indexes for JSON

14. ACID Transactions, Locks & Concurrency Control

  1. ACID Properties & InnoDB Architecture: Atomicity, Consistency, Isolation & Durability
  2. Transaction Isolation Levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ & SERIALIZABLE
  3. MVCC Architecture: Multi-Version Concurrency Control, Read Views & Consistent Non-Locking Reads
  4. Locking Mechanisms: Record Locks, Gap Locks, Next-Key Locks & Intent Locks
  5. Deadlock Detection: Wait-For Graphs, Deadlock Logging & Application Retry Patterns

15. Security, Backups, Replication & High Availability

  1. Security & Access Control: Users, Roles, caching_sha2_password & Least Privilege
  2. Backup & Disaster Recovery: Logical (mysqldump) vs Physical Backups (XtraBackup/Enterprise)
  3. Replication Architecture: Asynchronous vs Semi-Sync, GTIDs & Source-Replica Topologies
  4. High Availability: MySQL InnoDB Cluster, Group Replication & MySQL Shell Router
  5. Performance Profiling: Performance Schema, sys Schema & Bottleneck Diagnostics

5. Querying & Filtering with Advanced Operators

  1. Full-Text Search: FULLTEXT Indexes, MATCH() AGAINST() & Boolean Search Modes

3. DDL: Database, Table Creation & Constraints

  1. Table Partitioning: RANGE, LIST, HASH & KEY Partitioning Strategies
  2. Partition Maintenance: Instant Data Purging with DROP PARTITION & REORGANIZE

2. MySQL Data Types & Storage Architecture

  1. Character Sets & Collations: utf8mb4_0900_ai_ci vs _bin & Case-Sensitivity Rules

5. Querying & Filtering with Advanced Operators

  1. Built-in String Functions: CONCAT_WS, SUBSTRING, REPLACE, TRIM & LPAD Formatting
  2. Date & Time Math: DATE_ADD(), DATEDIFF(), TIMESTAMPDIFF() & DATE_FORMAT()
  3. Conditional Logic: Searched CASE Expressions, Simple CASE & the IF() Shortcut
  4. Mathematical & Cryptographic Functions: ROUND, CEIL, MOD, SHA2 & AES_ENCRYPT

15. Security, Backups, Replication & High Availability

  1. Information Schema: Database Introspection, Table Sizing & Dynamic DDL Metaprogramming

1. MySQL Architecture & Storage Engines (InnoDB vs MyISAM)

  1. Connection Architecture: Thread Pooling, max_connections & Wait Timeouts
  2. Memory Optimization: Sizing the InnoDB Buffer Pool, Log Buffers & Flush Strategies

13. Indexing Architecture & EXPLAIN Optimization

  1. Slow Query Log: Profiling Long Queries, pt-query-digest & Optimization Workflows

15. Security, Backups, Replication & High Availability

  1. Production Hardening: CIS Benchmark Security Checklist, Encryption & Audit Vault

Check SQL queries with a tiny known dataset

A query that runs can still return the wrong rows. Build a small fixture whose expected result you can calculate by hand. Include a boundary value, a missing value, and an unmatched row when testing joins. Keep fixtures separate from production data. SQLite syntax below is deliberately explicit about its dialect.

SQLite 3; run only in a disposable learning database

CREATE TABLE scores (name TEXT, score INTEGER);
INSERT INTO scores VALUES ('Asha', 50), ('Ravi', 49), ('Mira', NULL);

SELECT name, score
FROM scores
WHERE score >= 50
ORDER BY name;
Check the output
name | score
Asha | 50

Add a score of 100 and another NULL. Explain why NULL does not pass score >= 50. Adapt the table types when practicing another SQL dialect.

Test database constraints before adding application code

Test both accepted and rejected writes. In a relational database, a primary key prevents duplicate identifiers and a NOT NULL constraint prevents missing required values. Application validation improves feedback but does not replace database constraints. MongoDB uses different mechanisms, including unique indexes and collection validators; do not copy SQL statements into its shell.

SQLite 3 example; concept also applies to other databases using their own syntax

CREATE TABLE lessons (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);
INSERT INTO lessons (id, title) VALUES (1, 'Loops');
SELECT id, title FROM lessons;
Check the output
id | title
1 | Loops

Write a checklist for valid insert, duplicate key, missing required field, and update. Translate the checks into your database dialect.

Open the MySQL learning path on DevStudyHub · Play coding games on this blog

logoblog

No comments:

Post a Comment

Please do not enter any spam link in the comment box.