Let me set the scene.

A room full of people who had never written a single line of SQL in their lives. Some came from accounting. Some from operations. One person told me they still used Excel to track hospital records manually. And they were all looking at me like I was about to teach them magic.

Honestly? I kind of was.

This is my retrospective on our SQL journey - what we covered, what broke people's brains (in a good way), and the moments I realised teaching this stuff is genuinely one of the most fun things I do.

Where We Started: "What Even Is a Database?"

Before I could show anyone a SELECT statement, I had to answer the most fundamental question first: why does this exist?

My go-to analogy: a database is like a very organised filing cabinet. Each drawer is a table. Each folder inside the drawer is a row. Each label on the folder is a column. SQL is just the language you use to ask the filing cabinet questions.

Once that clicked, the first query felt almost anticlimactic - in a good way.

SELECT first_name, last_name
FROM students
WHERE grade = 'A';

"Wait… that's it?" Yes. That's it. The magic is in building from there.

Week 1: The Foundation (And the First Confused Faces)

We started with the basics - SELECT, WHERE, ORDER BY, GROUP BY, HAVING. We used a school dataset called Nairobi Academy (fictional Nairobi school - felt very local and relatable) and a hospital dataset called City Hospital.

The moment that got everyone: understanding the difference between WHERE and HAVING.

-- WHERE filters rows BEFORE grouping
SELECT department, COUNT(*) AS total_staff
FROM employees
WHERE status = 'Active'
GROUP BY department;

-- HAVING filters AFTER grouping
SELECT department, COUNT(*) AS total_staff
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

I used the analogy of a matatu route: WHERE is the conductor deciding who even boards the matatu. HAVING is checking at the destination who actually made it the whole way. That one landed really well.

The Thing That Broke Everyone (In the Best Way): JOINs

I won't lie - JOINs are where beginner SQL classes either soar or crash. I've seen it go both ways.

We covered:

• INNER JOIN - only rows that match in both tables
• LEFT JOIN - all rows from the left, matched where possible
• RIGHT JOIN - all rows from the right
• SELF JOIN - a table joining itself (yes, this is a thing)
• LEFT JOIN + IS NULL - finding orphaned records

The self-join is always the one that causes dramatic pauses.

-- "Who is each employee's manager?" - same table, different roles
SELECT 
    e.first_name AS employee,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

"Wait… it's joining to itself?"

Yes. Yes it is. Welcome to SQL.

Window Functions: When SQL Starts Feeling Like a Superpower

This is the week I could see people's eyes light up.

Window functions let you do calculations across rows without collapsing them into a group. We covered ROW_NUMBER, RANK, DENSE_RANK, NTILE, running averages with AVG OVER, and time-travel queries with LAG and LEAD.

-- Rank students by score within each class
SELECT 
    student_name,
    class,
    score,
    RANK() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
FROM results;

And LAG - the one that made the data analysts in the room go quiet for a moment before saying "oh wow":

-- Compare this month's sales to last month's
SELECT 
    month,
    total_sales,
    LAG(total_sales) OVER (ORDER BY month) AS previous_month,
    total_sales - LAG(total_sales) OVER (ORDER BY month) AS difference
FROM monthly_sales;

No subquery. No join. Just clean, readable SQL. This is the moment people stop thinking of SQL as "just for pulling data" and start seeing it as an analytical tool.

CTEs: Writing SQL Like a Human Being

Before CTEs, students were writing nested subqueries that looked like abstract art. Beautiful in theory, impossible to read three days later.

Common Table Expressions (WITH ... AS) changed everything.

-- Find students who scored above the class average
WITH class_averages AS (
    SELECT class, AVG(score) AS avg_score
    FROM results
    GROUP BY class
)
SELECT r.student_name, r.class, r.score
FROM results r
JOIN class_averages ca ON r.class = ca.class
WHERE r.score > ca.avg_score;

The rule I drilled in: write your CTEs like you're telling a story. First set up your averages, then filter against them. Each CTE is a chapter.

Data Cleaning: Where Reality Hits

Every SQL class eventually gets to the uncomfortable truth: real data is messy. Dirty, inconsistent, full of rogue spaces and names typed in ALL CAPS at 4pm on a Friday.

We built a dedicated dirty dataset and went to town with:

-- Standardise names that arrived inconsistently
UPDATE employees
SET 
    first_name = INITCAP(TRIM(first_name)),
    last_name = INITCAP(TRIM(last_name));

-- Strip non-numeric characters from phone numbers
UPDATE contacts
SET phone = REGEXP_REPLACE(phone, '[^0-9]', '', 'g');

-- Fill in missing values rather than leaving NULLs
SELECT 
    COALESCE(department, 'Unassigned') AS department
FROM employees;

Golden rule we repeated every cleaning session: always run a SELECT first before an UPDATE or DELETE. See exactly what you're about to change. Only then pull the trigger.

The Capstone: Tembo Hotel

The final project was set in Tembo Hotel - a fictional Nairobi hotel with ~285 rows of genuinely messy booking data. Students worked in groups to clean it, analyse it, and answer real business questions:

• Which room types generate the most revenue?
• Which months have the highest no-show rates?
• Who are our repeat guests?

Watching groups go from "this data is a disaster" to producing clean queries with CTEs and window functions across a few sessions was something else.

What I Learned Teaching This

A few honest takeaways from being on the teaching side:

1. Analogies are not optional. Every concept needs a real-world anchor before any code appears. If you skip the analogy and go straight to syntax, you lose half the room.

2. Local context matters more than I expected. Using Nairobi names, M-Pesa examples, matatu analogies - students were visibly more engaged when the data felt like their world.

3. PostgreSQL is the right call for beginners. Clean syntax, no shortcuts, no backtick weirdness. If you learn PostgreSQL first, everything else makes sense later.

4. The moment someone writes their first CTE that actually works, they look different. Slightly smug. Entirely justified.

What's Next?

SQL was the foundation. Now we've moved into Python - same students, same energy, new language. We're building toward a full data programme where they'll split into Data Science and Data Engineering tracks.

I'll be writing about that journey too. Follow along if you're building something similar, learning from scratch yourself, or just enjoy watching beginners turn into dangerous analysts one query at a time.

See you next week.

I'm a data engineer in Nairobi. I write weekly about what I'm teaching, what's working, and what surprised me in the classroom. If you're a fellow trainer or a self-taught analyst, let's talk in the comments.