SQL Engineering Handbook

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

Typing SVG

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`](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](Resources/) | All Learning resources including Certifications and hands-on-experience | ✅ | | 00 | [Schema](00_Schema/) | Practice database DDL, seed data, and ERD used by every later module | ✅ | | 01 | [Fundamentals](01_Fundamentals/) | `SELECT`, `WHERE`, `ORDER BY`, `LIMIT`, aliasing | ✅ | | 02 | [Aggregations](02_Aggregations/) | `COUNT`, `SUM`, `AVG`, `MIN`/`MAX`, `GROUP BY`, `HAVING` | ✅ | | 03 | [Joins](03_Joins/) | Inner, left, right, full, cross, self joins + performance audit | ✅ | | 04 | [Subqueries](04_Subqueries/) | Scalar, correlated, `EXISTS`, derived tables, subquery-to-join rewrites | ✅ | | 05 | [CASE WHEN](05_CASE_WHEN/) | Conditional logic and business-rule encoding | ✅ | | 06 | [CTEs](06_CTEs/) | Common Table Expressions, recursive CTEs | ✅ | | 07 | [Window Functions](07_Window_Functions/) | `ROW_NUMBER`, `RANK`, `LAG`/`LEAD`, `PARTITION BY` | ✅ | | 08 | [Window Business Cases](08_WINDOW_BUSINESS_CASES/) | Applied window-function scenarios (running totals, cohorts, rankings) | ✅ | | 09 | [Date Functions](09_Date_Functions/) | Date arithmetic, formatting, range queries | ✅ | | 10 | [String Functions](10_STRING_FUNCTIONS/) | String manipulation and data cleaning | ✅ | | 11 | [NULL Handling & Data Cleaning](11_NULL_HANDLING_AND_DATA_CLEANING/) | `COALESCE`, `NULLIF`, data-quality patterns | ✅ | | 12 | [Advanced Aggregations](12_ADVANCED_AGGREGATIONS/) | Conditional and multi-level aggregation | ✅ | | 13 | [Set Operators](13_SET_OPERATORS/) | `UNION`, `INTERSECT`, `EXCEPT`, reconciliation queries | ✅ | | 14 | [Views](14_VIEWS/) | Views, security, updatable views, performance | ✅ | | 15 | [Indexes](15_INDEXES/) | B-Tree, composite, covering indexes, reading `EXPLAIN` | ✅ | | 16 | [Query Optimization](16_QUERY_OPTIMIZATION/) | Execution plans, rewrite patterns, anti-patterns | ✅ | | 17 | [SQL Interview Questions](17_SQL_INTERVIEW_QUESTIONS/) | Curated question bank with worked answers | 📋 | | 18 | [SQL Business Case Studies](18_SQL_BUSINESS_CASE_STUDIES/) | End-to-end analytics scenarios across domains | 📋 | | 19 | [SQL Projects](19_SQL_PROJECTS/) | Portfolio-ready guided projects | 📋 | | 20 | [SQL Cheatsheet](20_SQL_CHEATSHEET/) | One-page syntax and pattern reference | 📋 | Legend: ✅ Complete · 🔄 In Progress · 📋 Planned — live tracking always in [`ROADMAP.md`](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`](CHANGELOG.md) | | 🤝 Contribution | Solo repo | Structured community process via [`CONTRIBUTING.md`](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 ```mermaid 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`](ROADMAP.md).

--- ## Repository Structure ```text 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`](ARCHITECTURE.md) for the reasoning behind this layout. --- ## Quick Start ```bash # 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/`)* ```sql 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`](ROADMAP.md) | Live module-by-module progress and what's next | | [`ARCHITECTURE.md`](ARCHITECTURE.md) | How the repo, datasets, and modules are structured and why | | [`STYLE_GUIDE.md`](STYLE_GUIDE.md) | Format every module and query follows | | [`CHANGELOG.md`](CHANGELOG.md) | Version history of the handbook | | [`FAQ.md`](FAQ.md) | Common questions about setup and usage | | [`CONTRIBUTING.md`](CONTRIBUTING.md) | How to contribute | | [`SECURITY.md`](SECURITY.md) | How to report security concerns | | [`CODE_OF_CONDUCT.md`](CODE_OF_CONDUCT.md) | Community standards | **GitHub features:** [Issues](https://github.com/theammarngp-makes/SQL-Engineering-Handbook/issues) for bugs and requests · [Discussions](https://github.com/theammarngp-makes/SQL-Engineering-Handbook/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. 1. Fork the repository 2. Create a feature branch 3. Follow the format in [`STYLE_GUIDE.md`](STYLE_GUIDE.md) 4. Open a pull request using the template in [`.github/PULL_REQUEST_TEMPLATE.md`](.github/PULL_REQUEST_TEMPLATE.md) Full standards live in [`CONTRIBUTING.md`](CONTRIBUTING.md). Please review the [`CODE_OF_CONDUCT.md`](CODE_OF_CONDUCT.md) before participating. - 🐛 Found a bug? [Open an issue](https://github.com/theammarngp-makes/SQL-Engineering-Handbook/issues) - 💬 Question about a query? Start a [Discussion](https://github.com/theammarngp-makes/SQL-Engineering-Handbook/discussions) - 🔐 Security concern? See [`SECURITY.md`](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`](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. More in [`FAQ.md`](FAQ.md). --- ## License Licensed under the **MIT License**. See [`LICENSE`](LICENSE) for details. --- ## ✍️ About the Author

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

[![Website](https://img.shields.io/badge/Website-theammarngp--makes.loveable.app-2563EB?style=for-the-badge&logo=googlechrome&logoColor=white)](https://theammarngp.lovable.app) [![LinkedIn](https://img.shields.io/badge/LinkedIn-mohammad--ammar--ngp-0A66C2?style=for-the-badge&logo=linkedin&logoColor=white)](https://www.linkedin.com/in/mohammad-ammar-ngp/) [![X](https://img.shields.io/badge/X-@theammarngp-000000?style=for-the-badge&logo=x&logoColor=white)](https://x.com/theammarngp) [![Gmail](https://img.shields.io/badge/Email-theammarngp@gmail.com-EA4335?style=for-the-badge&logo=gmail&logoColor=white)](mailto:theammarngp@gmail.com) --- ---

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.

### Official Resource Citation - **Project Name:** SQL Engineering Handbook - **Author & Creator:** Mohammad Ammar (Data Analyst) - **Organization:** Apex Analyticx - **Official Portfolio & Updates:** https://theammarngp.lovable.app