SQL Engineering Handbook

A production-style SQL curriculum — from your first SELECT to analytics engineering, interview prep, and real business case studies.

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 .sql files — this handbook is built for you.

Honest status: Modules 00–09 (Foundations → Date Functions) are complete and stable today. Modules 10–20, plus datasets/, projects/, exercises/, and cheatsheets/, are actively being built module by module. Live status always lives in ROADMAP.md — this README won't claim more than what's actually shipped.

🗂️ 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	SQL Interview Questions	Curated question bank with worked answers	📋
18	SQL Business Case Studies	End-to-end analytics scenarios across domains	📋
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 to see its own contents list.

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 `17_SQL_INTERVIEW_QUESTIONS/` and `exercises/interview/` target the questions that come up most. Until they ship, `08_WINDOW_BUSINESS_CASES/` is the closest match.
🧠 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 CASE WHEN]
    E --> F[05 Subqueries]
    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 Interview Questions]
    M --> N[18 Business Case Studies]
    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	✅ Complete
11	NULL Handling & Data Cleaning	✅️ Complete
12	Advanced Aggregations	✅️ Complete
13	Set Operators	✅️ Complete
14	Views	✅️ In Progress
15	Indexes	✅️ In Progress
16	Query Optimization	✅️ In Progress
17	SQL Interview Questions	🔄 In Progress
18	SQL Business Case Studies	🔄 In Progress
19	SQL Projects	🔄 In Progress
20	SQL Cheatsheet	🔄 In Progress

Legend: ✅ Complete · 🔄 In Progress · 📋 Planned

Live tracking always in ROADMAP.md.

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_SAMPLE_DATABASE/                 ✅ 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_CASE_WHEN/                       ✅ Conditional logic & transforms
├── 05_SUBQUERIES/                      ✅ Scalar, inline, correlated
├── 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_SQL_INTERVIEW_QUESTIONS/         🔄 In progress
├── 18_SQL_BUSINESS_CASE_STUDIES/       🔄 In progress
├── 19_SQL_PROJECTS/                    🔄 In progress
└── 20_SQL_CHEATSHEET/                  🔄 In progress

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 for the reasoning behind this layout.

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_SAMPLE_DATABASE/schema.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_SAMPLE_DATABASE → 09_DATE_FUNCTIONS (currently complete), then continue into 10–20 as they release. No prior SQL knowledge assumed.

🎯 Interview sprint — targeted prep

Once live, 17_SQL_INTERVIEW_QUESTIONS/ and exercises/interview/ will be the fastest path. Until then, 07_WINDOW_FUNCTIONS/ and 08_WINDOW_BUSINESS_CASES/ cover the most commonly tested interview topic.

📚 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 questions about a specific query or module.

Contributing

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

Fork the repository
Create a feature branch
Follow the format in STYLE_GUIDE.md
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.

🐛 Found a bug? Open an issue
💬 Question about a query? Start a Discussion
🔐 Security concern? See SECURITY.md

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 for live status.

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? That's the goal of 17_SQL_INTERVIEW_QUESTIONS/ and exercises/interview/ once live; until then, 08_WINDOW_BUSINESS_CASES/ is the closest match.

License

Licensed under the MIT License. See LICENSE for details.

✍️ About the Author

Mohammad Ammar — Co-Founder @ Apex Analyticx, Data Analytics Engineer, author of the SQL Engineering Handbook (20+ modules). Based in Nagpur, India.

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-module release.

FilesExpand file tree

README.md

Latest commit

History

README.md

File metadata and controls