SQL-Engineering-Handbook

SQL Engineering Handbook Banner

SQL Engineering Handbook

A production-style curriculum in structure and examples — progressive modules from fundamentals to analytics engineering, interview prep, and real-world scenarios. See each module README for[...]

Who It's For · Why Different · Roadmap · Structure · Quick Start · Docs · Contributing · FAQ

🌐 Live Link : https://theammarngp-makes.github.io/SQL-Engineering-Handbook

Who This Is For

If you’re an aspiring or practicing Data Analyst or Analytics Engineer who wants a structured path from SQL fundamentals to real business analytics — not another disconnected list of .s[...]

🗂️ Module Guide

This is the part most handbooks . Every module below links straight to its folder and tells you exactly what’s inside before you click.

# Module Contents Status
  Resources All Learning resources including Certifications and hands-on-experience ✅
00 Schema Practice database DDL, seed data, and ERD used by every later module ✅
01 Fundamentals SELECT, WHERE, ORDER BY, LIMIT, aliasing ✅
02 Aggregations COUNT, SUM, AVG, MIN/MAX, GROUP BY, HAVING ✅
03 Joins Inner, left, right, full, cross, self joins + performance audit ✅
04 Subqueries Scalar, correlated, EXISTS, derived tables, subquery-to-join rewrites ✅
05 CASE WHEN Conditional logic and business-rule encoding ✅
06 CTEs Common Table Expressions, recursive CTEs ✅
07 Window Functions ROW_NUMBER, RANK, LAG/LEAD, PARTITION BY ✅
08 Window Business Cases Applied window-function scenarios (running totals, cohorts, rankings) ✅
09 Date Functions Date arithmetic, formatting, range queries ✅
10 String Functions String manipulation and data cleaning ✅
11 NULL Handling & Data Cleaning COALESCE, NULLIF, data-quality patterns ✅
12 Advanced Aggregations Conditional and multi-level aggregation ✅
13 Set Operators UNION, INTERSECT, EXCEPT, reconciliation queries ✅
14 Views Views, security, updatable views, performance ✅
15 Indexes B-Tree, composite, covering indexes, reading EXPLAIN ✅
16 Query Optimization Execution plans, rewrite patterns, anti-patterns ✅
17 Transactions & Concurrency Control ACID, isolation, locking, MVCC and runnable demos ✅ (content present — see module README for verification note[…]
18 SQL Interview Questions Curated question bank with worked answers and a structural validator ✅ (content present; see module README & FINAL_VALIDATION_RE[…]
19 SQL Projects Portfolio-ready guided projects 📋
20 SQL Cheatsheet One-page syntax and pattern reference 📋

Legend: ✅ Complete · 🔄 In Progress · 📋 Planned — live tracking always in ROADMAP.md.

Each module folder itself contains a README.md (concept + business context), one or more paired .sql files, and where relevant an audit or troubleshooting doc — click into any module above t[…]

What Makes It Different

  Typical SQL repo SQL Engineering Handbook
🗄️ Data One generic sample table Real-world-style datasets across HR, e-commerce, sales, finance, healthcare
📖 Context Bare query, no explanation Business problem stated before every solution
⚠️ Failure modes Rarely documented Common mistakes called out per pattern
🎯 Interview angle Absent Dedicated interview-prep module + question bank
📅 Progress Static snapshot Public roadmap, versioned via CHANGELOG.md
🤝 Contribution Solo repo Structured community process via CONTRIBUTING.md

Feature Highlights

### 🏗️ Production SQL Every completed module follows one format: **business context → SQL solution → explanation → common mistakes → interview follow-ups → practice challenge.** ### 📊 Real Datasets Practice is built around real-world-style datasets (HR, e-commerce, sales, finance, healthcare) instead of one toy table, so the SQL transfers directly to a job. ### 🎯 Interview Ready `18_SQL_INTERVIEW_QUESTIONS/` and `exercises/interview/` target the questions that come up most; see Module 18 README and FINAL_VALIDATION_REPORT.md for validation notes.
### 🧠 Analytics Engineering Modules progress from syntax → analytical SQL (windows, CTEs) → engineering concerns (views, indexes, query optimization). ### 🧪 Exercises & Projects `exercises/` (beginner → interview) and `projects/` (HR analytics, e-commerce, pizza sales, Olist, NagpurLens) turn concepts into a portfolio. ### 📚 Documentation Quality Architecture, style guide, roadmap, and changelog are first-class files — not afterthoughts — so the repo stays maintainable as it grows.

Learning Roadmap

flowchart LR
    subgraph Foundations
    A[00 Sample Database] --> B[01 Fundamentals]
    B --> C[02 Aggregations]
    C --> D[03 Joins]
    end
    subgraph Core_SQL[Core SQL]
    D --> E[04 Subqueries]
    E --> F[05 CASE WHEN]
    F --> G[06 CTEs]
    end
    subgraph Analytical_SQL[Analytical SQL]
    G --> H[07 Window Functions]
    H --> I[08 Window Business Cases]
    I --> J[09 Date Functions]
    end
    subgraph Engineering_SQL[Engineering SQL]
    J --> K[10-13 Strings, Nulls, Advanced Aggs, Set Ops]
    K --> L[14-16 Views, Indexes, Query Optimization]
    end
    subgraph Career_Ready[Career Ready]
    L --> M[17 Transactions & Concurrency Control]
    M --> N[18 SQL Interview Questions]
    N --> O[19 Projects]
    O --> P[20 Cheatsheet]
    end
Full module-by-module status
| # | Module | Status | |---|---|---| | 00 | Sample Database | ✅ Complete | | 01 | Fundamentals | ✅ Complete | | 02 | Aggregations | ✅ Complete | | 03 | Joins | ✅ Complete | | 04 | Subqueries | ✅ Complete | | 05 | CASE WHEN | ✅ Complete | | 06 | CTEs | ✅ Complete | | 07 | Window Functions | ✅ Complete | | 08 | Window Function Business Cases | ✅ Complete | | 09 | Date Functions | ✅ Complete | | 10 | String Functions | ✅️ In progress | | 11 | NULL Handling & Data Cleaning | ✅️ In progress | | 12 | Advanced Aggregations | ✅️ In progress | | 13 | Set Operators | ✅️ In progress | | 14 | Views | ✅️ In Progress | | 15 | Indexes | ✅️ In Progress | | 16 | Query Optimization | ✅️ In Progress | | 17 | Transactions & Concurrency Control | ✅️ Complete (see module README for verification notes) | | 18 | SQL Interview Questions | ✅️ Complete (see module README & FINAL_VALIDATION_REPORT.md) | | 19 | SQL Projects | 🔄 In Progress | | 20 | SQL Cheatsheet | 🔄 In Progress | **Legend:** ✅ Complete · 🔄 In Progress · 📋 Planned Live tracking always in [`ROADMAP.md`](/SQL-Engineering-Handbook/ROADMAP.html).

Repository Structure

SQL-Engineering-Handbook/
│
├── README.md                          You are here
├── ROADMAP.md                         Live module-by-module progress
├── CHANGELOG.md                       Version history
├── ARCHITECTURE.md                    Why the repo is organized this way
├── STYLE_GUIDE.md                     Format every module/query follows
├── CONTRIBUTING.md                    How to contribute
├── CODE_OF_CONDUCT.md                 Community standards
├── SECURITY.md                        How to report security concerns
├── FAQ.md                             Common questions
├── LICENSE                            MIT
│
├── .github/                           Issue/PR templates, CI workflows, CODEOWNERS
│   ├── ISSUE_TEMPLATE/
│   ├── workflows/
│   ├── PULL_REQUEST_TEMPLATE.md
│   └── CODEOWNERS
│
├── assets/                            Banners, diagrams, screenshots, logos
│   ├── banners/
│   ├── diagrams/
│   ├── screenshots/
│   ├── logos/
│   └── gifs/
│
├── datasets/                          Real-world practice datasets
│   ├── employee_management/
│   ├── ecommerce/
│   ├── sales/
│   ├── finance/
│   ├── healthcare/
���   └── nagpurlens/
│
├── resources/                         Curated external learning material
│   ├── books.md
│   ├── blogs.md
│   ├── documentation.md
│   ├── youtube.md
│   └── interview-resources.md
│
├── cheatsheets/                        Quick-reference syntax guides
│   ├── joins/
│   ├── ctes/
│   ├── windows/
│   ├── dates/
│   ├── strings/
│   └── aggregation/
│
├── exercises/                          Practice problems by difficulty
│   ├── beginner/
│   ├── intermediate/
│   ├── advanced/
│   └── interview/
│
├── projects/                           End-to-end portfolio projects
│   ├── hr-analytics/
│   ├── ecommerce/
│   ├── pizza-sales/
│   ├── olist/
│   └── nagpurlens/
│
├── 00_Schema/                          ✅ Practice schema + seed data
├── 01_Fundamentals/                    ✅ SELECT, WHERE, ORDER BY, LIMIT
├── 02_Aggregations/                    ✅ GROUP BY, HAVING, aggregate functions
├── 03_Joins/                           ✅ Inner, Left, Right, Full, Cross
├── 04_Subqueries/                      ✅ Scalar, inline, correlated
├── 05_CASE_WHEN/                       ✅ Conditional logic & transforms
├── 06_CTEs/                            ✅ Common Table Expressions & recursion
├── 07_Window_Functions/                ✅ ROW_NUMBER, RANK, LAG/LEAD
├── 08_WINDOW_BUSINESS_CASES/           ✅ Applied window function scenarios
├── 09_Date_Functions/                  ✅ Date arithmetic, formatting, ranges
├── 10_STRING_FUNCTIONS/                ✅️ In progress
├── 11_NULL_HANDLING_AND_DATA_CLEANING/ ✅️ In progress
├── 12_ADVANCED_AGGREGATIONS/           ✅️ In progress
├── 13_SET_OPERATORS/                   ✅️ In progress
├── 14_VIEWS/                           ✅️ In progress
├── 15_INDEXES/                         ✅️ In progress
├── 16_QUERY_OPTIMIZATION/              ✅️ In progress
├── 17_TRANSACTIONS_AND_CONCURRENCY/    ✅️ Complete (see module README for verification notes)
├── 18_SQL_INTERVIEW_QUESTIONS/         ✅️ Complete (see module README & FINAL_VALIDATION_REPORT.md)

Every numbered module is self-contained: read its own README, run its queries against the relevant dataset, then attempt the practice challenge at the end. See [ARCHITECTURE.md](ARCHITECTURE.md[…]

Quick Start

# 1. Clone the repository
git clone https://github.com/theammarngp-makes/SQL-Engineering-Handbook.git
cd SQL-Engineering-Handbook

# 2. Load a practice dataset (start with the core sample database)
mysql -u root -p < 00_Schema/01_CREATE_TABLES.sql

# 3. Start with Fundamentals, or jump to any completed module
cd 01_Fundamentals

Database: MySQL 8.0+. Queries are ANSI-standard where possible, with MySQL-specific notes called out — most run on PostgreSQL with minor syntax changes.

Learning Tracks

📘 Sequential learner — go module by module
Work straight through `00_Schema` → `09_DATE_FUNCTIONS` (currently complete), then continue into `10`–`20` as they release. No prior SQL knowledge assumed.
🎯 Interview sprint — targeted prep
Module 18 (SQL Interview Questions) is published and provides a fast path for interview practice; see its README and FINAL_VALIDATION_REPORT.md for validation notes. Meanwhile, `07_WINDOW_FUNCTIONS/` and `08_WINDOW_BUSINESS_CASES/` cover commonly tested interview material.
📚 Desk reference — search when you need a pattern
Bookmark the repo and jump directly to the numbered module matching the syntax you need on the job.

Sample Query

Salary ranking with window functions (from 07_WINDOW_FUNCTIONS/)

SELECT
    emp_id,
    emp_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank,
    LAG(salary) OVER (ORDER BY salary DESC) AS prev_salary
FROM employees;

Every completed module follows this format: business context → SQL solution → explanation → common mistakes → interview follow-ups → practice challenge.

Documentation Hub

Doc Purpose
ROADMAP.md Live module-by-module progress and what’s next
ARCHITECTURE.md How the repo, datasets, and modules are structured and why
STYLE_GUIDE.md Format every module and query follows
CHANGELOG.md Version history of the handbook
FAQ.md Common questions about setup and usage
CONTRIBUTING.md How to contribute
SECURITY.md How to report security concerns
CODE_OF_CONDUCT.md Community standards

GitHub features: Issues for bugs and requests · Discussions for general conversation.

Contributing

This project is being built module by module, and contributions are genuinely welcome — new queries, dataset additions, exercises, corrections, or documentation improvements.

  1. Fork the repository
  2. Create a feature branch
  3. Follow the format in STYLE_GUIDE.md
  4. Open a pull request using the template in .github/PULL_REQUEST_TEMPLATE.md

Full standards live in CONTRIBUTING.md. Please review the CODE_OF_CONDUCT.md before participating.

FAQ

Is this finished? No — and it says so on purpose. Modules 00–09 are complete and stable. 10–20, plus datasets, exercises, projects, and cheatsheets, are actively being built. Check ROADMAP.md[…]

Do I need MySQL specifically? Queries are ANSI-standard where possible, with MySQL-specific notes called out. Most run on PostgreSQL with minor syntax changes.

Is this beginner-friendly? Yes — start at 01_Fundamentals/. It assumes no prior SQL knowledge.

Can I use this for interview prep only? Module 18 (SQL Interview Questions) is published and intended for interview prep. See its README and FINAL_VALIDATION_REPORT.md for the current coverage and validation status.

More in FAQ.md.

License

Licensed under the MIT License. See LICENSE for details.

✍️ About the Author

Mohammad Ammar — Co-Founder @ Apex Analyticx, Data Analyst, author of the [...]

This handbook is built in public and updated regularly. If it's useful to you, starring the repo helps more learners find it — and following along tracks its progress from here to a full 21-m[...]

Official Resource Citation

This site is open source. Improve this page.