SQL Zero to Hero

A structured teaching repository covering SQL from first queries through to window functions, normalisation, and transaction control. Each module is a runnable SQL file with inline commentary. Part of the Zero to Hero course family.

---

Who Is This For?

• Developers or analysts learning SQL for the first time
• Delegates on instructor-led SQL or data fundamentals courses
• Anyone who needs to query, design, or manage relational databases

No prior SQL experience is assumed. Basic understanding of what a database is helps.

---

Prerequisites

Tool	Minimum Version
MySQL	8.0+ (tested on 8.4.11)
Python (optional, to run and check the tasks)	3.13

mysql --version

Any MySQL-compatible client works: MySQL Workbench, DBeaver, the CLI, or a cloud instance.

---

Getting Started

# Clone the repository
git clone git@github.com:Edrzapi/SQL_zero_hero.git
cd SQL_zero_hero

# Connect to your MySQL instance and run the setup script
mysql -u your_user -p < sample_data/setup.sql

This creates the training_db database with 5 tables and sample data. All module files use this database. The tasks use their own library database (see Tasks).

---

Repository Structure

basics/                # What SQL is, terminology, language categories
datatypes/             # Data types and constraints
ddl/                   # CREATE, ALTER, DROP
dml/                   # INSERT, UPDATE, DELETE
dql/                   # SELECT, WHERE, ORDER BY, LIMIT
aggregation/           # COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING
joins/                 # INNER, LEFT, RIGHT, SELF, UNION
subqueries/            # Nested queries, derived tables, views
normalisation/         # 1NF, 2NF, 3NF
dcl/                   # Users, privileges, GRANT, REVOKE
transactions/          # COMMIT, ROLLBACK, SAVEPOINT, ACID
window_functions/      # RANK, ROW_NUMBER, LAG, LEAD, NTILE
debugging/             # Reading error messages, finding and fixing broken queries
sample_data/           # Setup script with the training database schema
practice/              # Short exercises and solutions on the training database (they used to live in tasks/)
tasks/                 # The Library Manager tasks (see Tasks)

---

Topics Covered

Foundations

• Introduction: relational databases, tables, keys, SQL language categories.
• Data Types: numeric, text, date/time, boolean types. Constraints: NOT NULL, UNIQUE, DEFAULT, PRIMARY KEY, FOREIGN KEY, CHECK.

Schema Management (DDL)

• CREATE, ALTER, DROP: building and modifying tables, foreign key relationships, TRUNCATE vs DELETE.

Data Operations (DML)

• INSERT, UPDATE, DELETE: adding, modifying, and removing records. Safe practices.

Querying (DQL)

• SELECT: column selection, aliasing, calculated fields, DISTINCT.
• WHERE: filtering with comparison, BETWEEN, IN, LIKE, IS NULL, AND/OR.
• ORDER BY, LIMIT: sorting and pagination.

Analysis

• Aggregate Functions: COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING.
• Joins: INNER, LEFT, RIGHT, self joins, multi-table joins, UNION.
• Subqueries and Views: nested SELECT, derived tables, correlated subqueries, EXISTS, CREATE VIEW.
• Window Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, running totals, PARTITION BY.

Design and Administration

• Normalisation: 1NF, 2NF, 3NF with practical examples. When to denormalise.
• Transactions: COMMIT, ROLLBACK, SAVEPOINT, ACID principles.
• Users and Privileges: GRANT, REVOKE, principle of least privilege.

Debugging

• Debugging SQL: reading error messages, common mistakes, and a strategy for narrowing down a broken query.

---

Tasks

The tasks/ folder holds the Library Manager, one practical project shared by every Zero to Hero repo: 12 tasks and a bonus in Python, Java, C#, C++ and JavaScript, and eight tasks (S1 to S8) in SQL's own order on the same database. Every repo uses the same briefs, the same sample data and the same expected output; only the code differs.

• One task per session. Each task is the end-of-session check and builds on the one before, so by the end the learner has one working program.
• Three levels: Core (everyone), Stretch (for those who finish early) and Challenge (the strongest learners, or homework).
• Every task folder has the brief (README.md), the exact expected output (expected.txt), a starter/ that runs as it is (unfinished parts say "not implemented") and a solution/ with Core and Stretch.
• One checker: tasks/check/check.py (Python 3.10 or later, standard library only) runs a program and prints PASS, or FAIL with the first line that differs from expected.txt. On Windows, py works in place of python.

tasks/README.md has the session to task map, the 3-day and 5-day selections, and the run and check command for every task. To check the first task's solution, from the repo root:

python tasks/check/check.py S1 -- python tasks/run_sql.py tasks/S1_select/solution/core.sql

run_sql.py runs the task on SQLite, with no server needed. tasks/run_mysql.py runs the same file on a MySQL 8 server and prints the same layout, so the same check works there too (see Checking on MySQL).

---

Teaching Approach

• Each module is a standalone SQL file you can run top to bottom
• Concepts are explained in comments alongside working queries
• All module examples use the same training_db database for consistency
• Topics progress from basic SELECT through to advanced window functions
• No ORMs, no frameworks: pure SQL

---

Tech Stack

Technology	Purpose
MySQL 8.0+	Database engine
SQL	Query language
Python 3.13 and SQLite	Running and checking the tasks without a MySQL server

Every module file, the practice solutions, sample_data/setup.sql and all task solutions have been run on MySQL 8.4.11 (the mysql:8.4 Docker image) with no errors, apart from the statements in tasks S7 and S8 that are meant to fail. All 16 task checks pass on MySQL 8.4.11 and on SQLite.

Compatible with most MySQL clients. Queries use standard SQL where possible; notes are included where MySQL-specific syntax is used.

---

What's Next?

This course covers SQL fundamentals through advanced querying. For the same structured approach applied to programming languages, see the companion repositories:

• Python Zero to Hero
• Java Zero to Hero
• C# Zero to Hero