This document defines the standards for every SQL file, markdown document, and code block in the SQL Engineering Handbook. All contributors must follow these standards. These rules exist to maintain consistency, readability, and professionalism across the entire repository.
Standards exist to:
This guide is the single source of truth. Do not invent new standards; follow this guide.
Every style choice reflects these core principles:
All SQL keywords are uppercase. This provides visual distinction from table/column names.
✅ Correct
SELECT employee_id, employee_name
FROM employees
WHERE hire_date > '2020-01-01'
ORDER BY salary DESC;
❌ Incorrect
select employee_id, employee_name
from employees
where hire_date > '2020-01-01'
order by salary desc;
Use 4 spaces for indentation. Never use tabs.
✅ Correct
SELECT
e.employee_id,
e.employee_name,
d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.hire_date > '2020-01-01'
ORDER BY e.salary DESC;
❌ Incorrect
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.hire_date > '2020-01-01'
ORDER BY e.salary DESC;
Each major clause (SELECT, FROM, WHERE, JOIN, GROUP BY, ORDER BY) starts on a new line.
✅ Correct
SELECT
employee_id,
employee_name,
salary
FROM employees
WHERE department_id = 3
ORDER BY salary DESC;
❌ Incorrect
SELECT employee_id, employee_name, salary FROM employees WHERE department_id = 3 ORDER BY salary DESC;
Table aliases are abbreviated but recognizable. Never use single letters like a, b, t1.
✅ Correct
SELECT
emp.employee_id,
emp.employee_name,
dept.department_name
FROM employees emp
JOIN departments dept ON emp.dept_id = dept.dept_id;
❌ Incorrect
SELECT
e.employee_id,
e.employee_name,
d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
Column aliases should be descriptive and use AS.
✅ Correct
SELECT
employee_id,
salary * 12 AS annual_salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
❌ Incorrect
SELECT
employee_id,
salary * 12 total_sal,
RANK() OVER (ORDER BY salary DESC) rank
FROM employees;
Comments explain why, not what. The code shows the what.
✅ Correct
-- Calculate monthly sales commission (5% of sales above $10k threshold)
SELECT
sales_rep_id,
SUM(CASE
WHEN amount > 10000 THEN amount * 0.05
ELSE 0
END) AS commission
FROM sales
WHERE sales_date >= '2024-01-01'
GROUP BY sales_rep_id;
❌ Incorrect
-- Sum the amount if it's greater than 10000, multiply by 0.05
SELECT
sales_rep_id,
SUM(CASE
WHEN amount > 10000 THEN amount * 0.05
ELSE 0
END) AS commission
FROM sales
WHERE sales_date >= '2024-01-01'
GROUP BY sales_rep_id;
Common Table Expressions use descriptive names that explain their purpose.
✅ Correct
WITH active_employees AS (
SELECT
employee_id,
employee_name,
department_id
FROM employees
WHERE termination_date IS NULL
),
employee_salaries AS (
SELECT
employee_id,
SUM(salary) AS total_compensation
FROM active_employees
GROUP BY employee_id
)
SELECT
ae.employee_name,
es.total_compensation
FROM active_employees ae
JOIN employee_salaries es ON ae.employee_id = es.employee_id
ORDER BY es.total_compensation DESC;
❌ Incorrect
WITH cte1 AS (
SELECT * FROM employees WHERE termination_date IS NULL
),
cte2 AS (
SELECT employee_id, SUM(salary) AS total_comp FROM cte1 GROUP BY employee_id
)
SELECT * FROM cte1 JOIN cte2 ON cte1.employee_id = cte2.employee_id;
Specify the join type explicitly (INNER, LEFT, RIGHT, FULL). Use the ON clause clearly.
✅ Correct
SELECT
emp.employee_id,
emp.employee_name,
dept.department_name
FROM employees emp
INNER JOIN departments dept ON emp.dept_id = dept.dept_id
WHERE emp.hire_date > '2020-01-01';
❌ Incorrect
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id AND e.hire_date > '2020-01-01';
Window functions are indented to show their structure.
✅ Correct
SELECT
employee_id,
employee_name,
salary,
RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank_in_dept,
LAG(salary) OVER (
PARTITION BY department_id
ORDER BY hire_date
) AS previous_salary
FROM employees
ORDER BY department_id, salary DESC;
❌ Incorrect
SELECT
employee_id,
employee_name,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank,
LAG(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS prev_sal
FROM employees;
CASE statements show the structure with consistent indentation.
✅ Correct
SELECT
employee_id,
employee_name,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 75000 THEN 'Mid-level'
WHEN salary >= 50000 THEN 'Junior'
ELSE 'Entry-level'
END AS salary_band
FROM employees;
❌ Incorrect
SELECT employee_id, employee_name, CASE WHEN salary >= 100000 THEN 'Senior' WHEN salary >= 75000 THEN 'Mid-level' ELSE 'Entry-level' END AS salary_band FROM employees;
Specify ASC or DESC explicitly. Never rely on defaults.
✅ Correct
SELECT
employee_id,
employee_name,
salary
FROM employees
ORDER BY salary DESC, employee_name ASC;
❌ Incorrect
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC, employee_name;
Every non-aggregated column in SELECT must appear in GROUP BY. List columns in the same order as SELECT.
✅ Correct
SELECT
department_id,
employee_name,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id, employee_name
ORDER BY avg_salary DESC;
❌ Incorrect
SELECT
department_id,
employee_name,
COUNT(*),
AVG(salary)
FROM employees
GROUP BY department_id;
Queries should be ANSI-standard SQL where possible. When using database-specific syntax, add a comment.
✅ Correct
-- MySQL SUBSTRING function
SELECT
employee_id,
employee_name,
SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS email_prefix
FROM employees;
-- NOTE: In PostgreSQL, use SUBSTR instead of SUBSTRING
Use a single H1 (#) per file. Use H2 (##) for major sections. Use H3 (###) for subsections.
✅ Correct
# SQL Fundamentals
## Overview
...
## Learning Objectives
...
### Prerequisites
...
❌ Incorrect
# SQL Fundamentals
### Overview
# Learning Objectives
✅ Correct
## Overview
This module covers the SELECT statement.
## Examples
Here are some examples:
```sql
SELECT * FROM employees;
Always specify the language. Use triple backticks.
✅ Correct
```sql
SELECT employee_id, employee_name
FROM employees;
```
❌ Incorrect
```
SELECT employee_id, employee_name FROM employees;
```
Use pipes and dashes for tables. Align columns for readability.
✅ Correct
| Clause | Purpose | Example |
|--------|---------|---------|
| SELECT | Choose columns | SELECT employee_id, name |
| WHERE | Filter rows | WHERE salary > 50000 |
| ORDER BY | Sort results | ORDER BY salary DESC |
❌ Incorrect
| Clause | Purpose |
| SELECT | Choose columns |
| WHERE | Filter rows |
Use hyphens for unordered lists, numbers for ordered lists.
✅ Correct
**Best practices:**
- Always use meaningful column aliases
- Format SQL for readability
- Add comments explaining business logic
**Steps to complete:**
1. Read the concept explanation
2. Run the example queries
3. Modify and experiment
Include context in the link text. Never use “click here.”
✅ Correct
For more information, see [CONTRIBUTING.md](/SQL-Engineering-Handbook/CONTRIBUTING.md) for the process.
❌ Incorrect
For more information, [click here](/SQL-Engineering-Handbook/CONTRIBUTING.md).
Include descriptive alt text.
✅ Correct
❌ Incorrect
Use blockquotes for important notes, warnings, or key insights.
✅ Correct
> **Key concept:** WHERE filters rows *before* aggregation. HAVING filters groups *after*.
Use shields.io badges for module metadata at the top of module READMEs.
✅ Correct
[]()
[]()
Every numbered module (00–20) must contain:
NN_MODULE_NAME (e.g., 01_FUNDAMENTALS)NN_TOPIC_NAME.md and NN_TOPIC_NAME.sql✅ Correct
01_FUNDAMENTALS/
├── README.md
├── 01_SELECT.md
├── 01_SELECT.sql
├── 02_WHERE.md
├── 02_WHERE.sql
├── 03_ORDER_BY.md
├── 03_ORDER_BY.sql
└── PRACTICE.md
❌ Incorrect
01-Fundamentals/
├── readme.md
├── SELECT.md
├── select.sql
├── WhereClause.md
├── where.sql
Every module README must include these sections in order:
# NN — Module Name
> Description of what this module teaches
[]()
[]()
[]()
[]()
Use markdown links to section anchors.
## 📑 Table of Contents
- [Overview](#-overview)
- [Learning Objectives](#-learning-objectives)
- [Topics Covered](#-topics-covered)
- [Folder Structure](#-folder-structure)
- [Recommended Learning Order](#-recommended-learning-order)
- [Skills Developed](#-skills-developed)
- [Real-World Applications](#-real-world-applications)
- [Best Practices](#-best-practices)
- [Prerequisites](#-prerequisites)
- [How to Use This Module](#-how-to-use-this-module)
- [Next Section](#-next-section)
One paragraph explaining what the module teaches and why it matters.
## 🔎 Overview
This module covers [broad concept]. Students will learn [specific skills]. This is important because [business/career relevance].
Use checkbox format so learners can track progress.
## 🎯 Learning Objectives
By the end of this module, you will be able to:
- [ ] Objective one
- [ ] Objective two
- [ ] Objective three
Table with topic number, name, description, and file links.
## 📖 Topics Covered
| No. | Topic | Description | Files |
|----|-------|--------------|-------|
| 01 | **Topic One** | Description | [`01_TOPIC_ONE.md`](./01_TOPIC_ONE.md) · [`01_TOPIC_ONE.sql`](./01_TOPIC_ONE.sql) |
Show the actual directory layout.
## 📂 Folder Structure
NN_MODULE_NAME/ │ ├── README.md ├── 01_TOPIC.md ├── 01_TOPIC.sql └── …
Explain why topics are in this sequence.
## 📌 Recommended Learning Order
Each topic builds on the previous one:
List the practical capabilities gained.
## 🧠 Skills Developed
Working through this module strengthens your ability to:
- Skill one
- Skill two
- Skill three
Show who uses these skills and how.
## 💼 Real-World Applications
These skills are used daily by:
`Data Analysts` · `Analytics Engineers` · `Backend Developers`
**Typical use cases:**
- Use case one
- Use case two
Common approaches and anti-patterns.
## 💡 Best Practices
- ✅ Do this because...
- ❌ Don't do this because...
What must be completed first.
## 🎯 Prerequisites
Completion of [Module Name](../NN_MODULE/) or equivalent knowledge.
Step-by-step instructions.
## 🛠 How to Use This Module
1. Read the `.md` file for a topic
2. Run the matching `.sql` file
3. Modify and experiment
4. Attempt the practice problems
Link to the following module.
## 🚀 Next Section
Once you've completed this module, continue to:
➡️ **[Module Name](../NN_MODULE/)** — description
Every SQL file must have:
-- =============================================================================
-- MODULE: NN - Module Name
-- CONCEPT: Concept Name
-- BUSINESS CONTEXT: What real problem does this solve?
-- =============================================================================
-- DATASET: Assumed to be loaded in the current database
-- TABLES USED: employees, departments, locations
-- SCENARIO: Find employees earning above the company average
-- BUSINESS OBJECTIVE: Identify high-earner retention risks
-- QUESTIONS:
-- 1. How many employees earn above the company average salary?
-- 2. What is the average salary by department for these high earners?
SELECT
dep.department_name,
COUNT(emp.employee_id) AS high_earner_count,
AVG(emp.salary) AS avg_high_earner_salary
FROM employees emp
INNER JOIN departments dep ON emp.dept_id = dep.dept_id
WHERE emp.salary > (
-- Subquery: Calculate company-wide average
SELECT AVG(salary) FROM employees
)
GROUP BY dep.department_name
ORDER BY avg_high_earner_salary DESC;
-- EXPECTED OUTPUT:
-- department_name | high_earner_count | avg_high_earner_salary
-- Sales | 5 | 95000.00
-- Engineering | 8 | 105000.00
-- Management | 3 | 120000.00
-- PERFORMANCE NOTES:
-- - Ensure salary column is indexed for subquery performance
-- - This query scans the entire employees table once; acceptable for <1M rows
-- - For larger tables, materialize the average in a CTE
-- INTERVIEW FOLLOW-UPS:
-- Q: What if we wanted employees earning above their department average?
-- A: Move the subquery into a window function (see Module 07)
-- Q: How would you handle NULL salaries?
-- A: Add WHERE emp.salary IS NOT NULL
-- Q: What's the performance impact of the subquery?
-- A: One full table scan; consider materializing if running repeatedly
-- PRACTICE VARIATIONS:
-- 1. Find employees below the company average salary
-- 2. Find employees above their department's average salary
-- 3. Show the difference between each employee's salary and the average
Never use artificial examples like:
users table with 5 rowsproducts table with generic “Product A”, “Product B”Each example should represent a real business domain:
| Industry | Example Scenario | Tables |
|---|---|---|
| HR | Employee hiring, retention, compensation | employees, departments, salaries |
| E-commerce | Customer orders, repeat purchases | customers, orders, order_items, products |
| Sales | Sales reps, territories, quotas | sales_reps, territories, deals |
| Finance | Budget planning, expense tracking | budgets, expenses, cost_centers |
| Healthcare | Patient treatments, provider networks | patients, providers, treatments |
Every query should start with a business question, not syntax.
✅ Correct
## Scenario
You work for a mid-market e-commerce company. The VP of Sales wants to identify
customers who have purchased in the last 6 months but are at risk of churning
(no purchases in the last 30 days). Marketing needs to run a retention campaign.
## Question
Which customers purchased in the last 6 months but have not purchased in the
last 30 days? Show their customer ID, total spend, and most recent purchase date.
## Solution
```sql
SELECT
cust.customer_id,
cust.customer_name,
SUM(ord.order_total) AS total_spend_6m,
MAX(ord.order_date) AS most_recent_purchase
FROM customers cust
INNER JOIN orders ord ON cust.customer_id = ord.customer_id
WHERE ord.order_date >= DATEADD(month, -6, GETDATE())
GROUP BY cust.customer_id, cust.customer_name
HAVING MAX(ord.order_date) < DATEADD(day, -30, GETDATE())
ORDER BY total_spend_6m DESC;
❌ **Incorrect**
```markdown
## SELECT with WHERE and GROUP BY
```sql
SELECT customer_id, SUM(amount) FROM orders WHERE date > '2024-01-01' GROUP BY customer_id;
---
## Naming Conventions
### Folders
- **Numbered modules**: `NN_MODULE_NAME` (zero-padded, uppercase after number)
- ✅ `00_SAMPLE_DATABASE`, `01_FUNDAMENTALS`, `17_SQL_INTERVIEW_QUESTIONS`
- ❌ `0_fundamentals`, `001_Fundamentals`, `01_fundamentals`
- **Support directories**: lowercase with underscores
- ✅ `datasets`, `resources`, `cheatsheets`, `exercises`, `projects`
- ❌ `DataSets`, `Resources`, `Cheat-sheets`
### Files
- **Module concept files**: `NN_TOPIC_NAME.md` and `NN_TOPIC_NAME.sql`
- ✅ `01_SELECT.md`, `02_WHERE.sql`, `17_INTERVIEW_QUESTIONS.md`
- ❌ `01_select.md`, `Select.md`, `module_select.sql`
- **Dataset files**: `schema.sql`, `seed_data.sql`, `README.md`
- **Workflow files**: kebab-case (`.github/workflows/validate-sql.yml`)
### SQL Identifiers
#### Tables
- **Names**: Plural, lowercase, underscores
- ✅ `employees`, `order_items`, `customer_payments`
- ❌ `employee`, `order items`, `Customer_Payments`
#### Columns
- **Names**: Singular, lowercase, underscores, descriptive
- ✅ `employee_id`, `order_date`, `customer_name`, `total_amount`
- ❌ `empid`, `odate`, `cname`, `amt`
- **Special types**:
- IDs: suffix with `_id` (`employee_id`, `customer_id`)
- Dates: prefix with verb or suffix with `_date` (`hire_date`, `start_date`, `created_at`)
- Flags/booleans: prefix with `is_` or `has_` (`is_active`, `has_license`)
- Money: suffix with `_amount` (`salary_amount`, `total_amount`)
#### Table Aliases
- **Format**: 2–3 letter abbreviation, not single letters
- ✅ `emp` for `employees`, `cust` for `customers`, `ord` for `orders`
- ❌ `e`, `c`, `o`
- ❌ `employee_alias`, `t1`, `a`
#### CTEs and Subqueries
- **Names**: Descriptive noun or action, lowercase with underscores
- ✅ `active_employees`, `monthly_totals`, `high_value_customers`
- ❌ `cte1`, `sub_query`, `tmp`
### Comments and Annotations
- Use full sentences, not fragments
- Start with a capital letter
- Explain the *why*, not the *what*
✅ **Correct**
```sql
-- Calculate 5% commission only for orders exceeding $10k threshold
SELECT employee_id, amount * 0.05 AS commission
FROM sales
WHERE amount > 10000;
❌ Incorrect
-- multiply by 0.05
SELECT employee_id, amount * 0.05 AS commission
FROM sales
WHERE amount > 10000;
File-level comments explain the module, concept, and business context.
-- =============================================================================
-- MODULE: 07 - Window Functions
-- CONCEPT: ROW_NUMBER() and Ranking
-- BUSINESS CONTEXT: Employee salary ranking and salary band analysis
-- =============================================================================
Before each query, explain what it does.
-- Identify the highest-paid employee in each department
SELECT
department_id,
employee_id,
employee_name,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;
Note performance implications for scalability.
-- WARNING: This subquery scans the entire employees table.
-- For tables > 1M rows, consider materializing this in a CTE.
SELECT
employee_id,
salary,
(SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;
Explain how NULLs, duplicates, or special values are handled.
-- NOTE: Uses IS NULL instead of = NULL
-- NULL comparisons require IS NULL; = NULL always returns unknown
SELECT
employee_id,
employee_name,
commission_rate
FROM employees
WHERE commission_rate IS NULL;
Note when syntax is database-specific.
-- MySQL SUBSTRING function
-- In PostgreSQL, use SUBSTR(email, 1, POSITION('@' IN email) - 1)
SELECT
employee_id,
SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS email_prefix
FROM employees;
✅ Correct
The WHERE clause filters individual rows before aggregation. If you need to filter groups after aggregation, use HAVING instead.
❌ Incorrect
WHERE is awesome for filtering! HAVING is for grouping (sort of). They’re kinda similar but different lol.
Use active voice. State the subject performing the action.
✅ Correct
The query returns employees hired in 2024.
❌ Incorrect
Employees hired in 2024 are returned by the query.
Use present tense for explanations.
✅ Correct
SELECT retrieves columns from a table. WHERE filters rows based on conditions.
❌ Incorrect
SELECT will retrieve columns from a table. WHERE would filter rows.
✅ Correct
This approach is more efficient because it avoids a full table scan.
❌ Incorrect
This amazing approach is INCREDIBLY efficient because it avoids a full table scan!!!
Use the same term for the same concept throughout.
Assume readers know SQL syntax after Module 01, but do not assume understanding of concepts.
✅ Correct
Window functions apply an aggregate function to a subset of rows (the “window”) while keeping individual row results, unlike GROUP BY which collapses rows.
❌ Incorrect
Window functions are like GROUP BY but they don’t collapse rows.
Before opening a pull request, verify:
Before starting work on a module or contribution:
Maintainers use this checklist when reviewing PRs:
Following this style guide ensures:
When in doubt, review similar completed modules or ask in the PR.
This guide is the single source of truth for standards. For questions or proposed changes, open an issue or start a discussion.