MySQL course
Learn MySQL step by step: 243 lessons in 48 modules with worked examples, quizzes and interview questions. Free on JavaSchoolLab.
MySQL Topics
- Introduction & Database
- Client Server Architecture
- Data Types & Clauses
- Keys & Aggregate Functions
- SQL Command Types
- Table Operations
- Join Types
- Nth Highest Salary
Database Fundamentals
- What is a Database?
- DBMS vs RDBMS
- What is MySQL?
- Database, Table, Row & Column
- Schema
- Primary Key & Foreign Key Basics
- Candidate, Composite & Unique Keys
- Constraints Overview
- One-to-One Relationship
- One-to-Many Relationship
- Many-to-Many Relationship
- Relational Database Concepts
- SQL vs MySQL
MySQL Installation & Environment
- Installing MySQL
- MySQL Server & Client
- MySQL Workbench
- MySQL Shell
- Connecting to MySQL & Basic CLI Commands
- Creating Users
- MySQL Configuration & Data Directory
- Starting & Stopping the MySQL Service
SQL Basics
- Database Operations: CREATE, SHOW, USE & DROP
- Table Operations: CREATE, SHOW & DESCRIBE
- ALTER TABLE
- DROP, TRUNCATE & RENAME
Data Types
- Numeric Types: INT, BIGINT, SMALLINT & TINYINT
- DECIMAL, FLOAT & DOUBLE
- CHAR & VARCHAR
- TEXT, MEDIUMTEXT & LONGTEXT
- DATE, TIME & YEAR
- DATETIME vs TIMESTAMP
- BOOLEAN, JSON & Binary Types
- Storage Size, Range & Type Performance
Constraints
CRUD Operations
SELECT Queries
Operators
- Comparison Operators
- Logical Operators: AND, OR & NOT
- BETWEEN, IN & NOT IN
- LIKE & Pattern Matching
- IS NULL & IS NOT NULL
MySQL Functions
Aggregate Functions
Joins
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- CROSS JOIN
- SELF JOIN
- Multiple Joins & Many-to-Many Joins
- Join vs Subquery & Join Performance
Subqueries
- Scalar & Single-Row Subqueries
- Multi-Row Subqueries with IN
- Correlated Subqueries
- EXISTS Subqueries
- ANY & ALL Subqueries
Set Operations
Relationships & Database Design
- Designing One-to-One Tables
- Designing One-to-Many & Many-to-Many Tables
- Junction (Bridge) Tables
- Referential Integrity
- Cascading Operations
Normalization
Keys
Indexes
- What is an Index?
- B-Tree Indexes
- Clustered vs Secondary Index
- Composite Index & Leftmost Prefix
- Unique & Prefix Indexes
- Full-Text & Spatial Indexes
- Covering Index
- Index Selectivity & Cardinality
Query Optimization
- EXPLAIN
- EXPLAIN ANALYZE
- Full Table Scan vs Index Scan
- Join Algorithms
- Temporary Tables & Filesort in Plans
- Cost, Cardinality & Slow Queries
Transactions
Transaction Isolation Levels
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
- Dirty, Non-Repeatable & Phantom Reads
- Lost Update
Locks & Concurrency
- Shared & Exclusive Locks
- Row-Level vs Table Locks
- Record, Gap & Next-Key Locks
- SELECT ... FOR UPDATE
- Lock Contention & Deadlocks
Views
Stored Procedures
- CREATE PROCEDURE & CALL
- IN, OUT & INOUT Parameters
- Variables, IF & CASE in Procedures
- Loops in Procedures
- Error Handling in Procedures
Stored Functions
Triggers
Cursors
Common Table Expressions
Window Functions
- ROW_NUMBER, RANK & DENSE_RANK
- NTILE
- LAG & LEAD
- FIRST_VALUE & LAST_VALUE
- PARTITION BY & Window Frames
- Running Totals & Moving Averages
- Top-N per Group
JSON in MySQL
- JSON Data Type & JSON_OBJECT / JSON_ARRAY
- JSON_EXTRACT & JSON_VALUE
- JSON Indexes & Generated Columns
- JSON vs Relational Columns
Full-Text Search
- FULLTEXT Indexes
- MATCH ... AGAINST
- Natural Language vs Boolean Mode
- Full-Text vs Elasticsearch/OpenSearch
Temporary Tables
User Management & Security
- CREATE, ALTER & DROP USER
- GRANT, REVOKE & SHOW GRANTS
- Roles & Privileges
- Least Privilege & Application Users
SQL Injection
Backup & Restore
- Logical vs Physical Backup
- mysqldump & Restore
- Backup Strategies: Full & Incremental
- Point-in-Time Recovery
Binary Logs
Replication
- Source/Replica Architecture
- Asynchronous & Semi-Synchronous Replication
- Replication Lag
- Read Replicas & Failover
- GTID
- Replication Monitoring
High Availability
- Failover & Automatic Failover
- RPO & RTO
- Disaster Recovery
- InnoDB Cluster & Group Replication
- MySQL Router
Partitioning
InnoDB Internals
- InnoDB Storage Engine
- Buffer Pool
- Redo Log & Undo Log
- Doublewrite Buffer & Change Buffer
- Adaptive Hash Index
- Tablespaces, Data Pages & Index Pages
MVCC
- What is MVCC?
- Consistent Reads & Read Views
- Undo Logs & Transaction IDs
- Visibility Rules & Snapshot Isolation
MySQL Performance Tuning
- Slow Query Log
- Performance Schema & sys Schema
- Index & Query Tuning
- Buffer Pool & Memory Tuning
- Connections, Threads & Disk I/O
- SHOW PROCESSLIST & SHOW ENGINE INNODB STATUS
MySQL Architecture
MySQL with Backend Applications
- MySQL with Java & Spring Boot
- Connection Pooling
- Prepared Statements in Applications
- Transactions in Applications
- Repository Pattern
- Pagination Strategies
- N+1 Problem
- Lazy vs Eager Loading
- Database Migrations
ORM & MySQL
Database Design for Real Projects
Advanced SQL Interview Problems
- Second & Nth Highest Salary Problems
- Find & Remove Duplicate Records
- Employees Earning More Than Their Manager
- Department-wise Highest Salary & Top 3 per Department
- Running Total & Moving Average Problems
- Consecutive Dates, Missing Numbers & Gaps and Islands
- Employee Hierarchy & Recursive Organizational Tree
- Customers Who Never Ordered
- Most Frequently Purchased Product & Monthly Revenue
- Year-over-Year Growth & Retention Analysis