SQL & MySQL
MySQL for backend developers: fundamentals and architecture, CRUD, filtering, functions, NULLs, transactions and ACID, every kind of JOIN, grouping, subqueries, CTEs, window functions, keys and constraints, normalization, indexes, views, stored procedures, set operators and performance tuning.
Database Fundamentals and MySQL Architecture
Why databases exist, DBMS vs RDBMS, how MySQL executes a query, InnoDB vs MyISAM, data types, constraints, keys and AUTO_INCREMENT.
DDL and CRUD Operations
SQL command categories, CREATE and ALTER TABLE, DELETE vs TRUNCATE vs DROP, INSERT, UPDATE, DELETE, SELECT, pagination and the SQL that Spring Data JPA generates.
Filtering Data
The heart of SQL — WHERE, comparison and logical operators, operator precedence, BETWEEN, IN, LIKE, REGEXP, NULL handling, LIMIT/OFFSET and logical execution order.
Sorting and Built-in Functions
ORDER BY with multiple columns, NULL ordering and custom CASE sorting, plus the string, numeric and date functions used in almost every production query.
Conditional Logic and NULL Handling
If-else inside SQL — understanding NULL, CASE (searched and simple), IF(), IFNULL(), NULLIF(), COALESCE(), nested CASE, conditional sorting and conditional aggregation.
Transactions, ACID and TCL
All-or-nothing units of work — ACID properties, COMMIT, ROLLBACK, SAVEPOINT, auto-commit, and how Spring's @Transactional maps onto database transactions.
Week 1 Revision and SQL Problem Solving
Recap of fundamentals, CRUD, filtering, functions, conditional logic and transactions — plus 30 graded practice problems with solutions, interview questions and a mini project.
INNER JOIN
The most important SQL topic for backend developers — why joins exist, PK/FK relationships, syntax and aliases, how MySQL executes joins, multi-table joins, JOIN vs multiple queries and JPA.
LEFT JOIN
Keeping every left-side row — NULLs for missing matches, the anti-join pattern for missing records, ON vs WHERE filters, duplicates from one-to-many, multiple optional joins and JPA fetch joins.
SELF JOIN
Joining a table to itself — employee/manager and category hierarchies, why aliases are essential, INNER vs LEFT self joins, multi-level joins and self-referencing JPA entities.
Many-to-Many Relationships and Junction Tables
Relationship types, why M:N can't be stored directly, junction tables, composite vs surrogate keys, junction tables with business data, @ManyToMany in JPA and indexing mapping tables.
JOIN Optimization and Production-Level JOINs
How MySQL executes joins, choosing the right join, join order, why joins get slow, Cartesian products, duplicate-looking rows, an introduction to EXPLAIN, N+1 in Hibernate and join vs separate queries.
GROUP BY and Aggregate Functions
Summarizing millions of rows into business answers — COUNT, SUM, AVG, MIN, MAX, grouping by several columns, COUNT(*) vs COUNT(column), NULL behaviour and ONLY_FULL_GROUP_BY.
HAVING and Advanced Group Filtering
Filtering aggregated groups — WHERE vs HAVING, HAVING with COUNT/SUM/AVG/MIN/MAX, combining both, HAVING without GROUP BY, HAVING with JOINs and the cardinality trap.
ORDER BY, LIMIT and Ranking Queries
Deterministic sorting, sorting aggregates, LIMIT/OFFSET pagination, custom CASE ordering, Top-N and Nth-highest queries, keyset pagination and Spring Data paging.
CASE WHEN, IF(), COALESCE(), IFNULL() and NULL Handling
Decision-making SQL in depth — searched vs simple CASE, WHEN order, three-valued logic, NULL in comparisons, arithmetic and NOT IN, IFNULL vs COALESCE, and conditional aggregation for dashboards.
MySQL Date and Time Functions
DATE, DATETIME and TIMESTAMP, extracting parts, DATE_ADD/DATE_SUB, DATEDIFF vs TIMESTAMPDIFF, DATE_FORMAT, daily/monthly/quarterly reports, half-open ranges, sargable date filters and time zones.
MySQL String Functions and Text Analytics
CONCAT vs CONCAT_WS, LENGTH vs CHAR_LENGTH, case and trimming, SUBSTRING/LEFT/RIGHT, REPLACE, LOCATE vs INSTR, SUBSTRING_INDEX, LIKE wildcards, NULL behaviour, collations and search performance.
Subqueries
Queries inside queries — single-row and multi-row subqueries, IN, NOT IN and the NULL trap, EXISTS and NOT EXISTS, ANY and ALL, scalar subqueries, derived tables, nesting, second-highest salary and subquery vs JOIN.
Correlated Subqueries
Subqueries that depend on the outer row — department averages, row-by-row evaluation, EXISTS/NOT EXISTS, correlated UPDATE and DELETE in MySQL, indexing, and JOIN or window-function rewrites.
Common Table Expressions (CTEs)
Named temporary result sets with WITH — single, multiple and chained CTEs, recursive CTEs for sequences and hierarchies, CTE vs subquery, temporary table and view, and materialization.
Window Functions
OVER(), PARTITION BY and ORDER BY in windows, ROW_NUMBER vs RANK vs DENSE_RANK, Top-N per group, department aggregates beside every row, running totals and window frames, and filtering window results.
Advanced Window Functions
LAG, LEAD, FIRST_VALUE, LAST_VALUE and NTH_VALUE — previous/next-row comparisons, month-over-month and year-over-year growth, moving averages, running max/min, window frames and ROWS vs RANGE.
Advanced Analytical Queries
Running and rolling aggregates, NTILE, PERCENT_RANK, CUME_DIST, revenue contribution, leaderboards, KPI dashboards, funnels, customer segmentation and Pareto analysis.
Keys, Foreign Keys & Constraints
Primary, candidate, alternate, super, composite, natural and surrogate keys; UNIQUE, NOT NULL, DEFAULT and CHECK; foreign keys, referential integrity and CASCADE / SET NULL / RESTRICT.
Database Normalization
Redundancy and update/insert/delete anomalies, 1NF, 2NF, 3NF and BCNF with worked examples, normalization vs denormalization, and real-world schema design.
Indexes & Query Performance
How B-Tree indexes work, clustered vs secondary indexes, composite indexes and the leftmost-prefix rule, covering, unique and full-text indexes, selectivity and reading EXPLAIN.
Views & Materialized Views
Creating, replacing and dropping views, updatable vs non-updatable views, security views, WITH CHECK OPTION, materialized-view concepts and the MySQL summary-table alternative.
Stored Procedures & Functions
Creating and calling procedures, IN / OUT / INOUT parameters, variables, IF and CASE, WHILE / REPEAT / LOOP, handlers and RESIGNAL, transactional procedures, stored functions and calling them from Spring Boot.
Performance Tuning & Interview Masterclass
Query optimization, EXPLAIN and EXPLAIN ANALYZE, the slow query log, join and pagination strategies, partitioning, common production problems, the top 20 interview questions and a revision roadmap.
Set Operators — UNION & UNION ALL
UNION vs UNION ALL, the column-count and type rules, output column names, ORDER BY on combined results, source columns, UNION vs JOIN, and INTERSECT / EXCEPT in MySQL.