The Leading Zero Taught Me More

My SQL learning log; from "why is my zero gone" to joining four tables without blinking

A few weeks ago, I ran a simple query:

SELECT * FROM customers;

And stared at this:
711223344
That's not a typo; that's supposed to be 0711223344, a normal Kenyan phone number. SQL had quietly eaten the zero.

No error. No warning. Just... gone.

That one broken row taught me more about how SQL actually thinks than any tutorial had up to that point. So instead of writing another "here's what INNER JOIN means" post, I want to walk through the actual bugs that took me from green to dangerous in SQL, because that's genuinely how I learned.
Not from reading theory. From building real tables, breaking them, and going "wait, why."

Stage 1: Data types are not a formality
Turns out my phone_number column was numeric. And numbers don't have leading zeros; mathematically, 0711223344 and 711223344 are the same number, so SQL "helpfully" dropped it.

Lesson: phone numbers are not numbers. They're identifiers. Same goes for anything that might have leading zeros, + signs, dashes, or extensions.
Wrong

phone_number INT

Right
phone_number VARCHAR(15)
This bug is also where I learned to tell the difference between a real data problem and my SQL client just displaying something weird.

Stage 2: Quote your strings, or SQL goes looking for a table called "Amina"

Next wall: I wrote inserts like this—
VALUES (1, Amina, Wanjiku, F, 2008-03-12, Form3, Nairobi)
and got:
ERROR: column "amina" does not exist

Without quotes, SQL doesn't see a value; it sees what it thinks is a column or table name, and goes hunting for it.
Wrong
VALUES (1, Amina, Wanjiku, F, 2008-03-12, Form3, Nairobi)

Right
VALUES (1, 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form3', 'Nairobi')

Rule that stuck: numbers don't need quotes. Text and dates always do. And a single unclosed quote ('Charlie), instead of 'Charlie'), doesn't just break one value; it can swallow everything after it into one garbled string.

Stage 3: Constraints are guardrails, not decoration

Once the basics worked, I started actually leaning on constraints instead of just adding them.

CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    unit_price   NUMERIC(10,2) NOT NULL CHECK (unit_price > 0),
    stock        INT NOT NULL DEFAULT 0
);

CHECK stops bad prices at the door. DEFAULT means an incomplete insert still lands somewhere sensible. FOREIGN KEY means an order can never point at a customer that doesn't exist.

That last one had a sting in the tail: I couldn't delete a cancelled order without Postgres blocking me, because order_items still had rows pointing at it.

• Delete children first DELETE FROM order_items WHERE order_id = 4; DELETE FROM orders WHERE order_id = 4;

New habit: before deleting anything, ask what still points at it.

Stage 4: WHERE clauses and their many wrong spellings
I went through a phase of getting close but not quite right:
Nope
WHERE unit_price => 60, unit_price =< 200;

Yep
WHERE unit_price BETWEEN 60 AND 200;

SQL syntax doesn't do "close enough." But this is also where BETWEEN, IN, NOT IN, LIKE, and ILIKE stopped being abstract keywords and became daily tools, filtering exam scores by range, finding subjects containing "Studies," and learning the hard way that LIKE is case-sensitive in Postgres but ILIKE isn't

Stage 5: GROUP BY ≠ ORDER BY (a genuinely humbling moment)

I once expected this,

SELECT order_id, customer_id
FROM orders
ORDER BY customer_id;

to also sort order_id neatly. It doesn't. ORDER BY controls display order. It has nothing to do with grouping.
**
GROUP BY + HAVING **forced a real mental shift from "show me rows" to "collapse these rows into one answer per group, then filter the groups":

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

That was the moment aggregation actually clicked.

Stage 6: JOINs and knowing which one
Joining customers → orders → order_items → products into a single query was the milestone that finally made the relational model make sense. But the real lesson wasn't the syntax; it was INNER vs LEFT.

• Only matched rows — perfect for totals

SELECT p.product_name, SUM(oi.quantity) AS total_sold
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_name;

Keeps orders even with zero items.

SELECT o.order_id, oi.quantity
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id;

An INNER JOIN only shows matches on both sides. A LEFT JOIN keeps every row on the left even when there's nothing on the right, filling the gap with NULL. That distinction is how I caught a cancelled order that had zero items attached, before deleting it broke a foreign key.

Stage 7: CASE WHEN — logic, not just filtering

My first CASE attempt had a bug I didn't see coming:

• Always NULL; a column can't equal two things at once WHEN class = 'Form2' AND class = 'Form1' THEN 'Junior' Fixed: WHEN class IN ('Form1', 'Form2') THEN 'Junior' WHEN class IN ('Form3', 'Form4') THEN 'Senior'

By the time I was labelling exam results as 'Distinction', 'Merit', 'Pass', or 'Fail' based on marks, CASE WHEN had gone from confusing to intuitive:

`CASE
    WHEN marks >= 80 THEN 'Distinction'
    WHEN marks >= 60 THEN 'Merit'
    WHEN marks >= 40 THEN 'Pass'
    ELSE 'Fail'
END 
AS performance
FROM greenwood_academy.exam_results
ORDER BY result_id;`

Where I am now
I can design a schema from scratch (tables, keys, constraints, sane defaults), populate it, break it, fix it, and query it with filters, aggregates, four-table joins, and conditional logic. But the bigger shift is how I read errors now:

•Syntax error near WHEN? → missing comma before it.
•Foreign key violation on delete? → something still references that row.
•CASE returning NULL everywhere? → logic bug in the conditions, not a data problem.
None of this came from memorising syntax. It came from building real tables: a shop's customers and orders, a school's students and exam results, breaking them in small, specific ways, and stubbornly asking "why."
If you're early in SQL: don't chase the "correct" query on the first try. Build something real. Watch it fail. Read the error closely. That's genuinely where it sticks.

Currently deepening this into Power BI and data analysis work — if you're on a similar SQL-to-analytics path, I'd love to hear what tripped you up first.