Forem: Jessica Aki

SQL Stored Procedures: From Hard‑Coded Logic to Reusable SQL

Jessica Aki — Sun, 01 Feb 2026 23:04:56 +0000

When I first learned SQL, everything was very… static.

I wrote queries. They ran. They returned results. End of story.

Then I hit stored procedures.

Suddenly SQL wasn’t just about asking questions anymore, it was about telling the database to do work. And honestly, that shift confused me more than I expected.

This post is not a perfect or advanced guide. It’s the explanation I wish I had when stored procedures first stopped making sense, especially when CASE and parameters entered the picture.

What Finally Clicked: Stored Procedures Are Just Reusable Logic

The mistake I made early on was treating stored procedures as something exotic.

They’re not.

The simplest way this finally made sense to me:

A stored procedure is just a named SQL logic that lives in the database and can accept inputs.

That’s it.

If you already understand:

functions
parameters
if / else logic

Then stored procedures are not a new concept, just a new name in a new environment.

Why Plain SQL Started Feeling Limited

Imagine this very normal requirement:

“Give me all orders above a certain amount.”

In plain SQL, I might write:

select *
from orders
where total_amount > 1000;

That works… but it’s hard-coded.

If tomorrow the rule changes to 1500, I’m editing the query.
If another report needs 500, I’m copying the query.

That repetition is where stored procedures start to make sense.

My First Useful Stored Procedure

This was my first step:

create or replace procedure high_value_orders
as
begin
  select *
  from orders
  where total_amount > 1000;
end;

Now I could just run:

exec high_value_orders;

This felt nice… but still flawed. The rule is still hard-coded.

The Real Upgrade: Parameters

This is where things finally started feeling powerful. Instead of fixing the value inside the procedure, I pass it in.

create or replace procedure high_value_orders (
  p_min_amount number
)
as
begin
  select *
  from orders
  where total_amount > p_min_amount;
end;

Now I can reuse the same logic:

exec high_value_orders(500);
exec high_value_orders(1500);

This was a big moment for me. I wasn’t just writing SQL anymore rather I was writing configurable behaviour.

Where CASE Entered the Picture

CASE confused me at first because I kept seeing it explained abstractly.

What helped was using it to encode business rules.

Example requirement:

“Classify customers based on how much they’ve spent.”

select
  customer_id,
  total_spent,
  case
    when total_spent >= 5000 then 'VIP'
    when total_spent >= 2000 then 'Regular'
    else 'Occasional'
  end as customer_type
from customers;

This finally clicked:

CASE is SQL’s version of if / else.

Just conditions evaluated top to bottom.

Combining CASE With Stored Procedures

This is where everything came together.

Let’s say the business wants:

“Show me customers, but label them differently depending on the threshold I care about.”

create or replace procedure customer_segments (
  p_vip_threshold number
)
as
begin
  select
    customer_id,
    total_spent,
    case
      when total_spent >= p_vip_threshold then 'VIP'
      else 'Non-VIP'
    end as segment
  from customers;
end;

Now the logic adapts:

exec customer_segments(5000);
exec customer_segments(3000);

Same procedure. Different behavior.

That’s when stored procedures stopped feeling academic and started feeling useful.

What I Was Doing Wrong Before

Looking back, my mistakes were simple:

I treated stored procedures like advanced SQL syntax
I avoided parameters and hard-coded everything
I saw CASE as a trick, not logic

Once I reframed them as:

reusable logic
configurable inputs
conditional rules

Everything settled.

Why This Matters Beyond Syntax

SQL is not just querying data, but:

enforcing rules
centralizing logic
reducing duplication
making behaviour explicit

Stored procedures aren’t always the answer but understanding them changes how you think about databases.

Closing

If stored procedures or CASE ever felt heavy or unnecessary, I get it.

They only make sense once you stop thinking:

“How do I write this query?”

and start thinking:

“How do I make this logic reusable?”

That shift took me a while but once it happened, SQL stopped feeling shallow.

— Jessica Aki
Data & Database Engineering Enthusiast

I’m documenting my journey learning SQL, databases, and the systems behind real-world data platforms in public.

ROLLUP vs CUBE: How I Finally Understood SQL Subtotals

Jessica Aki — Mon, 26 Jan 2026 01:36:08 +0000

When I first ran into ROLLUP and CUBE in SQL, I genuinely thought I understood them.

I didn’t.

I knew the syntax. I could write the queries. But every time I looked at the results, they felt… random. And whenever SQL feels or seems random, it usually means I’m missing something important.

This post is me being honest about what confused me at first, where my assumptions were wrong and what finally made these concepts click

The Setup: A Real Sales Table

Let’s use something realistic: sales data.

create table sales (
  region    varchar2(20),
  product   varchar2(20),
  amount    number
);

insert into sales values ('West', 'Laptop', 1200);
insert into sales values ('West', 'Phone', 800);
insert into sales values ('West', 'Tablet', 600);
insert into sales values ('East', 'Laptop', 1100);
insert into sales values ('East', 'Phone', 700);
insert into sales values ('North', 'Laptop', 1000);

Here’s what the data actually looks like:

region	product	amount
West	Laptop	1200
West	Phone	800
West	Tablet	600
East	Laptop	1100
East	Phone	700
North	Laptop	1000

You can think of this as:

Dimensions → region, product
Measure → sum(amount)

Where I Was Comfortable: Basic GROUP BY

select region, sum(amount)
from sales
group by region;

Result:

region	sum(amount)
West	2600
East	1800
North	1000

Clear rule that made sense early on:

Every column in SELECT that isn’t aggregated must appear in GROUP BY.

No drama here.

Where Things Started to Break: Subtotals

Then I met this:

group by rollup (region, product)

And immediately, I made an assumption.

My First Wrong Assumption

I thought:

“ROLLUP gives me all possible totals.”

So I expected:

totals per (region, product)
totals per region
totals per product
grand total

That sounds reasonable right? Well, It’s also wrong.

That actually describes CUBE, not ROLLUP.

What ROLLUP Actually Does

ROLLUP follows a hierarchy, and order matters.

select region, product, sum(amount)
from sales
group by rollup (region, product);

SQL processes this from right to left, producing:

(region, product) → detailed rows
(region) → region subtotals
() → grand total

That’s it.

No product-only totals. No extra combinations.

What the Results Really Mean

The output looks something like this:

region	product	sum(amount)
West	Laptop	1200
West	Phone	800
West	Tablet	600
West	NULL	2600
East	Laptop	1100
East	Phone	700
East	NULL	1800
North	Laptop	1000
North	NULL	1000
NULL	NULL	5400

This part tripped me up for a while:

NULL here does not mean missing data.
It means this column was rolled up into a subtotal.

Once I understood that, the results stopped looking random.

A Subtle Query That Forced Clarity

This one confused me more than the first:

group by region, rollup (product)

I expected a grand total again.

But that’s not what happens.

Because:

ROLLUP only affects the columns inside it.

Generated groupings:

(region, product)
(region)

So the totals are per region, not for the whole table. That detail finally made the rules feel consistent.

Where CUBE Finally Made Sense

Once ROLLUP stopped feeling mysterious, CUBE became straightforward as it was my first misunderstanding of ROLLUPs.

select region, product, sum(amount)
from sales
group by cube (region, product);

CUBE generates every possible combination.

For two columns, that means:

(region, product)
(region)
(product)
()

Which gives you:

detailed rows
region totals
product totals
grand total

This is what I originally (and incorrectly) thought ROLLUP did.

How I Now Tell Them Apart

ROLLUP

follows a hierarchy
order matters
structured summaries

If you have N columns, you getN + 1 grouping levels.

Great for reports like:

day → month → year
region → country → global

CUBE

every combination
no hierarchy
more rows, more flexibility

If you have N columns, you get 2ⁿgrouping levels.

Best for:

analytics
slicing data in multiple ways
pivot-style reporting

Closing Thought

If ROLLUP or CUBE ever feel confusing, it’s usually because you’re asking:

“Why didn’t SQL give me this total?”

And the answer is almost always that column wasn’t rolled up or you actually wanted CUBE

Feeling stuck here is normal. So know, you're not the only one.

— Jessica Aki
Data & Database Engineering Enthusiast

I’m documenting my journey learning SQL, databases, and the systems behind real-world data platforms honestly, slowly, and in public.

Old Oracle Joins vs ANSI JOINs: Why I Chose One and Stuck With It

Jessica Aki — Fri, 23 Jan 2026 14:31:00 +0000

When I first encountered Oracle’s old join syntax using the (+) operator, I was confused — not because joins were new to me, but because the way they were written felt unintuitive compared to what I had already learned with ANSI SQL.

So instead of treating this as “just another syntax,” I decided to properly compare old Oracle joins vs ANSI joins, understand why both exist, and then make a deliberate choice about which one I would personally stick with.

This post is that comparison.

The Two Ways to Write Joins in Oracle

Oracle supports:

Old-style Oracle joins using the (+) operator
ANSI-standard joins using LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN

Both work. Both return results. But they feel very different when you read and maintain them.

Example Scenario

Let’s say we have:

clients → people who place orders
orders → what those clients buy

Some clients may not have placed any orders yet.

Old Oracle LEFT OUTER JOIN `(+)`

SELECT c.client_id, c.name, o.order_id
FROM clients c, orders o
WHERE c.client_id = o.client_id(+);

What’s happening here?

The (+) tells Oracle to preserve rows from clients
orders is optional
The join logic is mixed into the WHERE clause

The problem

It’s not visually obvious which table is optional
Adding filters can silently turn your outer join into an inner join
This syntax only works in Oracle

ANSI LEFT JOIN (The Same Query)

SELECT c.client_id, c.name, o.order_id
FROM clients c
LEFT JOIN orders o
  ON c.client_id = o.client_id;

Why this reads better

Join logic is separated from filters
You can instantly see which table is optional
The query reads almost like English

“Give me all clients, and their orders if they exist.”

FULL OUTER JOIN: The Big Difference

ANSI syntax

SELECT c.client_id, c.name, o.order_id
FROM clients c
FULL OUTER JOIN orders o
  ON c.client_id = o.client_id;

Clear. Explicit. Done.

Old Oracle syntax

Oracle doesn’t support FULL OUTER JOIN with (+).
You must simulate it using:

a LEFT JOIN
a RIGHT JOIN
and a UNION

This quickly becomes verbose and error-prone.

Portability Matters

ANSI joins:

Work in PostgreSQL, MySQL, SQL Server, Oracle, BigQuery
Are easier to transfer between jobs and databases
Are what most modern documentation and examples use

Old Oracle joins:

Lock you into Oracle
Appear mostly in legacy codebases
Are harder for non-Oracle developers to read

Why I Personally Chose ANSI JOINs

After using both, I made a deliberate choice:

ANSI joins are universal
They are easier to read, write, and reason about
They scale better as queries grow
They reduce accidental bugs caused by misplaced filters

Even when working with Oracle, I stick to ANSI syntax unless I’m forced to read legacy code.

Consistency matters more than preference — and ANSI gives me consistency across databases.

Final Thought

Knowing the old Oracle syntax is useful — especially when dealing with legacy systems.
But for new queries, learning projects, and cross-database skills, ANSI joins are the clear winner for me.

Learning SQL isn’t just about knowing what works — it’s about choosing what scales with you.

Jessica Aki
Data & Database Engineering Enthusiast

How SQL JOINs Finally Clicked for Me (and How They Can for You Too)

Jessica Aki — Thu, 22 Jan 2026 18:51:24 +0000

Today's post is not a “perfect” explanation of SQL joins. It’s the explanation I wish I had when joins first confused and kept me in place for some time.

And I’m writing this from the perspective of someone who:

Understood tables and primary keys
Had an understanding of foreign keys
Knew joins were important
Understood Normalization and Denormalization
But absolutely did not understand what was actually happening during a join

If SQL joins have ever made you feel lost, overwhelmed this is for you.

The Big Block That Held Me Back from Understanding

At first, joins felt like this:

“Some magic SQL thing where tables are smashed together somehow.”

Truly that's how it felt at the time. I understood the idea of what it was supposed to do. Merge tables together based on a common column.

I assumed:

The database “knew” how to join tables
JOIN meant “merge tables”
LEFT and RIGHT were arbitrary keywords

All of that was wrong. All assumptions from simply just seeing the syntax and expecting my brain to follow along.

The real problem?

I didn’t understand that joins are just comparisons between rows.

It wasn't magic. It was just row-by-row matching with rules.

The Setup: Two Simple Tables

Let's take a very a very simple and normal business scenario: customers placing orders.

Customers

select * from customers;

customer_id	customer_name
1	Alice
2	Bob
3	Charlie

Orders

select * from orders;

order_id	customer_id	product
101	1	Laptop
102	2	Phone
103	2	Headphones
104	4	Monitor

In this table we have:

Customer 4 does not exist and Customer 3 has no orders

CROSS JOIN — Why This One Feels Wrong (and That’s Normal)

Cross joins confused me at first but after actually applying it and seeing what it does. I finally understood that it:

Pairs every row in the first table with every row in the second.
This is something called a Cartesian Product.
For example:

select *
from customers
cross join orders;

This query produces 12 rows: How?

3 customers × 4 orders = 12 rows

INNER JOIN — The Join That made Joins To Finally Make Sense

This was my biggest breakthrough.

The rule

Only keep rows where the join condition matches in both tables

select *
from customers
inner join orders
on customers.customer_id = orders.customer_id;

What happens

Alice → has an order → keep
Bob → has orders → keep
Charlie → no orders → gone
Order with customer_id 4 → gone

INNER JOIN is strict. So if there's no match, the row is completely dropped.

LEFT JOIN

LEFT JOIN clicked faster for me.

Rule

Keep everything from the left table.

In this case it's the customer table that's on the left. So we keep the left table in place and match the customer_id of both tables and then join them into one table. So in this join table, any order that doesn't match the left table is filled in with Nulls

select *
from customers
left join orders
on customers.customer_id = orders.customer_id;

Result

All customers appear
Orders appear if they exist
No order? → NULL values

Charlie finally shows up — with NULLs.

LEFT JOIN answers questions like: “Show me all customers, even those who haven’t ordered yet.”

RIGHT JOIN

RIGHT JOIN is just LEFT JOIN from the other direction. So it keeps the right table in place, matches based on the join condition and joins the table and any row on the left that doesn't match the condition is filled out with Nulls.

select *
from orders
left join customers
on customers.customer_id = orders.customer_id;

FULL OUTER JOIN

This is where both Sides Matter. FULL JOIN keeps all matching rows and all unmatched rows from both tables.

select *
from customers
full join orders
on customers.customer_id = orders.customer_id;

This can be useful for data reconciliation or finding missing or orphaned records

JOIN Conditions Are Not Always Equality

This surprised me as most of the examples I'd seen were equalities. Rather I discovered you can join on:

Equal values
Ranges
Expressions

Example:

select *
from customers c
join orders o
on length(c.customer_name) = length(o.product);

Everything came together but there was a Mistake I Discovered I Was Making

So I kept practicing and learning and I started thinking of filtering results from the join tables and well I discovered how a lack of understanding of how SQL works could cause silent bugs.

The mistake

select *
from customers c
left join orders o
on c.customer_id = o.customer_id
where o.product = 'Laptop';

This turns your LEFT JOIN into an INNER JOIN.

This is because the WHERE runs after the join and the rows with NULL orders are filtered out. But that was not what I was trying to query from my data.

The fix I learnt

select *
from customers c
left join orders o
on c.customer_id = o.customer_id
and o.product = 'Laptop';

Unmatched customers are preserved.
Adding the filtering to the join ensures that I get the left join needed but I also filter for the data need.

My Conclusions

Joins are not scary. I kept pushing my learning of SQL at joins because most of the tutorials I used weren't explaining it in a way I understood. But after getting over my fears, I'm so happy that I was able to fully understand this concept so well.

They are simply:

Row-by-row comparisons with rules about what to keep.

Quick Summary

CROSS JOIN → everything × everything
INNER JOIN → only matches
LEFT JOIN → keep left, fill NULLs
RIGHT JOIN → keep right, fill nulls
FULL JOIN → keep everything

If joins ever made you feel lost, that’s normal. It doesn’t mean you’re bad at SQL. It means you’re finally learning how it actually works.

— Jessica Aki
Data and Database Engineering Enthusiast

I’m documenting my journey into data engineering learning SQL, databases, and the systems behind modern data platforms. I would love you to join me on this.

SQL Dates Finally Made Sense: NOW, AGE, EXTRACT, and Why Time Is Weird

Jessica Aki — Wed, 21 Jan 2026 09:20:00 +0000

Up until now, SQL felt very… solid.

Numbers are numbers. Strings are strings. GROUP BY either works or it doesn’t.

Dates?
Dates humbled me.

Today I spent time learning how SQL handles time and I finally understand why people say dates are one of the hardest parts of databases.

This post is me documenting what clicked, what confused me, and what I’ll definitely need to revisit.

The First Surprise: Dates Are Not Just Numbers or strings

At first, I thought dates were just formatted numbers .

Turns out they’re not.
Dates come with:

time zones
hidden time components
built-in functions that behave differently depending on context

Once I accepted that, things started to make more sense.

NOW() — The Current Moment

The simplest place to start was NOW().

SELECT NOW();

This returns the current timestamp**, that is, it's not just the date, but the exact moment the query runs.

What clicked for me:

NOW() includes date and time, including the time zone
It’s commonly used for logs, audits, and tracking events

Seeing a real timestamp come back made SQL feel a lot more like a backend system, not just a learning exercise.

AGE() — Time Between Two Dates

This function confused me at first, mostly because of the example usage i saw.

SELECT AGE(date_of_birth);

This example was where a users date of birth was passed into the function and the age was produced, but what I didn't understand at the time was that it's more flexible than just getting the difference between now and that day.

SELECT AGE(DATE '2025-10-10', '2021-01-10'); 
-- The amount of time I spent in Univeristy

What AGE() actually does:

Returns the time difference between two timestamps
The result is an interval, not a number

This is important.

Instead of giving me days or seconds, it returns something like:

4 years 9 mons

This made sense for things like:

How old an account is
How long ago something happened
Time since last activity

EXTRACT() — Pulling Out Pieces of Time

This is where things started clicking properly.

Sometimes you don’t want the full date.
You just want part of it.

SELECT EXTRACT(YEAR FROM  AGE(DATE '2025-10-10', '2021-01-10')); --4
SELECT EXTRACT(MONTH FROM  AGE(DATE '2025-10-10', '2021-01-10')); --9 
SELECT EXTRACT(DAY FROM  AGE(DATE '2025-10-10', '2021-01-10')); --0
SELECT EXTRACT(DOW FROM  '2025-10-10'); --5 (Friday)
-- DOW is Day of the week, it returns a value between 0 and 6 to represent the day of the week with Sunday being 0

What finally made sense:

EXTRACT doesn’t change the date
It reads a specific part of it

This is useful for:

grouping by year or month
filtering specific periods
reporting trends over time

Once I saw it as “reading metadata from time”, not transforming it, it felt less magical.

Intervals — Time as a Data Type

This part surprised me.

Time differences aren’t just numbers, rather SQL treats them as intervals.

SELECT NOW() - INTERVAL '7 days';
SELECT NOW() + INTERVAL '1 month';

That means SQL understands:

days
months
years
hours, minutes, seconds

And it handles calendar logic for you.

No manual math. No guessing.

What Still Feels Weird

I’m being honest, some things still feel slippery:

Time zones (I know this will bite me later)
Comparing dates vs timestamps
Understanding when time gets truncated or rounded

But compared to yesterday, I’m no longer scared of date functions.

That’s progress.

Conclusion

Dates are usually taught quickly, like:

“Here’s NOW(). Here’s EXTRACT(). Moving on.”

But as a beginner, this isn’t about syntax.
It’s about understanding how databases think about time.

If dates confused you too, you’re not behind.
They confuse everyone.

I’m learning SQL slowly, properly, and in public and documenting both the clarity and the confusion.

If you’re on the same path, you’re not alone. You can join me on this series and learn with me.

— Jessica Aki
Data and Database Engineering Enthusiast

I’m documenting my journey into data engineering learning SQL, databases, and the systems behind modern data platforms.

SQL Nulls Finally Made Sense: COALESCE, NULLIF, and Safer Calculations

Jessica Aki — Tue, 20 Jan 2026 16:52:44 +0000

This was one of those learning days where SQL didn’t look hard… but kept quietly breaking my queries.

Not with errors.

With NULLs.

Today I focused on understanding:

COALESCE
NULLIF
basic arithmetic operations
and how functions like SUM, ROUND, and MOD behave when NULL is involved

These are things I’ve seen mentioned casually in tutorials, but I didn’t really feel why they mattered until I started doing real calculations.

The Problem I Kept Running Into

I’d write what looked like a simple query:

calculate totals
divide one column by another
show numeric results

And suddenly…

values disappeared
calculations returned NULL
or worse everything broke because of a divide-by-zero

That’s when I realised:

SQL is extremely strict about missing or unsafe data.

And honestly? That’s a good thing.

COALESCE

COALESCE was the first function that really changed how I thought about NULLs.

What it does is simple:

Return the first non-null value in a list.

Example:

SELECT COALESCE(email, 'Email Unavailable')
FROM users;

What this means in plain English:

If email exists → show it
If email is NULL → replace it with 'Email Unavailable'

This was the first time SQL felt… practical.

Instead of letting missing data break my output, I could control what users actually see.

That mental shift mattered.

NULLIF (The Function I Didn’t Understand at First)

At first, NULLIF felt pointless.

Why would I turn something into NULL on purpose?

Then I hit this problem:

Dividing by zero.

SQL doesn’t allow it. And it shouldn’t.

This is where NULLIF finally made sense.

SELECT 10 / NULLIF(0, 0);

What’s happening here:

NULLIF(0, 0) → returns NULL
10 / NULL → returns NULL

No crash. No error. Just a safe result.

Now combine it with COALESCE:

SELECT COALESCE(10 / NULLIF(0, 0), 0);

This says:

Try the calculation
If it fails (returns NULL)
Fallback to 0

That’s when it clicked.

NULLIF prevents bad math. COALESCE cleans up the result.

They work together, not separately.

Arithmetic Operations

I also revisited basic arithmetic in SQL:

+ addition
- subtraction
* multiplication
/ division
% or MOD() for remainders
! factorial

What stood out wasn’t the syntax — it was the behaviour.

Any arithmetic with NULL results in NULL.

That means:

SELECT price * quantity;

Will quietly return NULL if either column is missing.

Which again reinforces why COALESCE matters so much.

SUM, ROUND, and MOD

Once I handled NULLs properly, aggregate and numeric functions finally behaved the way I expected.

SUM

SELECT SUM(amount)
FROM payments;

Works but only if you understand how NULLs are treated.

ROUND

SELECT ROUND(AVG(score), 2)
FROM results;

This was satisfying. Seeing clean, rounded values made the output feel presentation-ready.

MOD

SELECT MOD(10, 3);

Returns 1.

Simple, but useful when you actually need it.

Today's Takeaway

The big takeaway from today wasn’t memorising functions.

It was this:

SQL assumes missing data is dangerous unless you explicitly say otherwise.

COALESCE lets you decide what “missing” means
NULLIF lets you avoid invalid operations
arithmetic becomes predictable once NULLs are handled intentionally

What Still Feels Weird

I’m still adjusting to:

remembering that NULL isn’t zero
spotting where calculations might silently fail
knowing when to fix data vs fix the query

But compared to before? Huge progress.

Conclusion

NULL-handling is usually glossed over in beginner tutorials.

But in real databases?

It’s everywhere.

If you’re learning SQL and your queries sometimes technically work but return confusing results, this might be why.

What’s Next

Next up, I’m diving into dates and time:

DATE vs TIMESTAMP
AGE()
INTERVAL
EXTRACT

If you’re learning alongside me, feel free to follow the journey and check out the other posts in this series.

— Jessica Aki
Data and Database Engineering Enthusiast

I’m documenting my journey into data engineering learning SQL, databases, and the systems behind modern data platforms.

Querying & Filtering in Oracle Databases: What Actually Clicked for Me As a Beginner

Jessica Aki — Mon, 19 Jan 2026 22:59:00 +0000

Today felt like one of those quiet but important SQL days. No new tables. No fancy joins. Just learning how to ask better questions of the data I already have.

This lesson was all about querying and filtering rows basically learning how to tell the database exactly what I want back, and nothing more.

I learnt this through a simple toys table, which honestly helped a lot. Oracle courses teach in a weird but funny manner which allows you to learn and have fun while you do. They make very daunting topics look less intimidating by approaching them with easy to understand and relatable concepts.

create table toys (
  toy_name varchar2(100),
  colour   varchar2(10),
  price    number(10, 2)
);

insert into toys values ( 'Sir Stripypants', 'red', 0.01 );
insert into toys values ( 'Miss Smelly_bottom', 'blue', 6.00 );
insert into toys values ( 'Cuteasaurus', 'blue', 17.22 );
insert into toys values ( 'Mr Bunnykins', 'red', 14.22 );
insert into toys values ( 'Baby Turtle', 'green', null );

commit;

Selecting Rows (and Why `SELECT *` Is a Trap)

The very first thing was learning that SELECT really has two jobs:

FROM → where the data lives
SELECT → what columns I actually want back

At first, SELECT * FROM toys; is very convenient but only when your database is small. Imagine a bigger database with over 10000 rows. A select * isn't going to help you find Mr BunnyKins in there.

select toy_name, price
from toys;

This forces you to think about what you actually need, and it also:

Sends less data over the network
Breaks less when columns change

That alone already changed how I write queries. Be specific and effective.

Filtering Rows with `WHERE`

So to start being more effective instead of getting everything in the table, you can start asking questions like:

“Only show me the red toys”

select *
from toys
where colour = 'red';

Or:

“Give me just one specific row”

select *
from toys
where toy_name = 'Sir Stripypants';

Simple, but this is the foundation of almost every real query.

Combining Conditions: `AND`, `OR`, and Confusion

This part tripped me up more than I expected.

At first glance, this feels logical:

where toy_name = 'Mr Bunnykins'
or toy_name = 'Baby Turtle'
and colour = 'green';

But the results weren’t what I expected.

That’s when I understood that:

AND runs before OR

Which means SQL doesn’t read conditions left to right the way my brain wants it to.

The fix?

Use parentheses `()`. Always.

where ( toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle' )
and colour = 'green';

After that, the query does exactly what it looks like it should do. This alone has saved me from future bugs.

Lists of Values with `IN`

Instead of writing this:

where colour = 'red'
or colour = 'green'
or colour = 'blue'
or colour = 'yellow'

You can write this:

where colour in ( 'red', 'green', 'blue', 'yellow' );

Much cleaner. Much easier to read and very effective. This feels like one of those features you don’t appreciate until you really need it. Imagine the 100,000 rows in the table and I want just a handful that meet some conditions. it's more effective to use the IN than to write multiple OR statements.

Ranges with `<`, `>=`, and `BETWEEN`

where price between 6 and 20;

Important detail I learned:

BETWEEN includes both ends
If you want strict boundaries, you must write them yourself

where price > 6
and price <= 20;

It's the small details that make the big differences.

Wildcards and Pattern Matching (`LIKE`)

where colour like 'b%';

Finds anything starting with b.

where toy_name like '%B%';

Finds toy names containing uppercase B.

So wildcards:

_ matches exactly one character
% matches zero or more characters

And if you actually want to search for _ or % themselves… you need ESCAPE.

That’s one of those things you won’t know until it breaks something.

`NULL` Is… Weird (But Makes Sense)

This line returning nothing:

where price = null;

Turns out:

NULL isn’t a value — it’s unknown

So you must write:

where price is null;

And the opposite:

where price is not null;

Negation: Saying “NOT This”

You can flip most conditions using NOT:

where not colour = 'green';

Or by using <>:

where colour <> 'green';

But again — NULL is special.

To exclude nulls, you must use:

where colour is not null;

There’s no shortcut here.

Final Thoughts

This lesson didn’t feel flashy but it felt important.

Everything else in SQL builds on this:

Aggregations
Joins
Subqueries
Real-world analytics

If you can’t filter data confidently, everything else feels fragile.

I’m learning to slow down, be explicit, and write queries that are readable and effective.

If you’re also learning SQL and sometimes feel silly getting tripped up by WHERE clauses… you’re not alone.

I’ll keep documenting this journey, the confusion, clarity, and all.

— Jessica Aki
Data and Database Engineering Enthusiast

I’m documenting my journey into data engineering learning SQL, databases, and the systems behind modern data platforms.

Table Design: Database Fundamentals to Know as a Beginner

Jessica Aki — Mon, 19 Jan 2026 13:51:33 +0000

This post is part of my Learning Databases Beyond SQL journey. I’m starting to realize that writing SQL queries is only half the story and how tables are designed matters just as much.

This is my beginner-friendly walkthrough of data modelling: how you go from vague requirements to actual database tables.

1. Introduction

Data modelling is the process of taking real-world requirements and translating them into database tables.

I quickly learned this is a huge topic and easily a full course on its own. This post isn’t meant to make anyone an expert. It’s just a clear, high-level view of the steps involved and why they matter.

2. Overview: The Big Picture

Designing a database usually follows these steps:

Capture requirements
Build a conceptual model
Design a logical model
Create the physical model

One thing that stood out to me:

this is not a one-pass process

It is iterative and you constantly go back and adjust things as you understand the problem better, ideally while checking in with the people who will actually use the system.

3. Capturing Requirements

This step is about answering one question:

What information does the system need to store?

You get this by talking to users , that is the customers, staff, or anyone who will interact with the application.

Example: Hospital Appointment System

Imagine we’re building a system that lets patients:

View available appointment times
Book an appointment
View upcoming appointments

To support this, we need to store:

Appointment date and time
Clinic location
Patient name
Consultant name

These are functional requirements, these describe what the system must do.

There are also non-functional requirements, like:

Pages must load in under two seconds

These don’t describe features, but they still affect how the database is designed. They describe how the system should behave while performing its functions.

4. Conceptual Model

The conceptual model is a high-level view of the data. At this stage, we only care about the main entities , that is the real-world things the system tracks.

For the hospital system, those entities are:

Patient
Consultant
Appointment

No columns yet. Just identifying what exists.

5. Logical Model

This is where things start to feel more “database-like”.

The logical model:

Defines the attributes of each entity
Shows relationships between entities
Is often drawn as an Entity Relationship Diagram (ERD)

First pass

We might start with:

Patient

name

Consultant

name

Appointment

date & time
clinic name & address
patient
consultant

Spotting problems (normalization begins)

This is where I dove a bit deeper into the art and science of designing database and tables.

Every appointment at the same clinic will have the same address. Storing the address in every appointment row means:

duplicated data
higher chance of errors

So instead, we introduce a new entity:

Clinic

clinic name
address

And now Appointment just references the clinic.

This process of removing duplicated data is called normalization.

At this stage, we also:

choose data types
define constraints (e.g. appointment dates must be in the future)
identify primary keys

6. Normalization (At a High Level)

Normalization in simple terms means:

Store each fact once, and only once.

Normalization means storing each real-world fact in one place and linking everything else to it and there are different normal forms (1NF, 2NF, 3NF, etc.). Each one has rules that reduce redundancy and anomalies.

A full deep dive is out of scope here, but the key takeaway for me was:

normalization improves data quality
it makes systems easier to maintain

7. Physical Model

The physical model is where the design turns into actual SQL.

This includes:

CREATE TABLE statements
data types
performance decisions (indexes, partitions, etc.)

Example tables might look like:

create table consultants (
  consultant_id   integer,
  consultant_name varchar2(100)
);

create table patients (
  patient_id   integer,
  patient_name varchar2(100)
);

create table clinics (
  clinic_name varchar2(30),
  address     varchar2(1000)
);

create table appointments (
  appointment_id       integer,
  appointment_datetime date,
  clinic_name          varchar2(30),
  consultant_id        integer,
  patient_id           integer
);

This is where non-functional requirements (like performance) really start to matter.

8. Supertypes and Subtypes

At this point, something interesting shows up.

Both patients and consultants are people — and people have names.

If someone can be both a patient and a consultant, storing their name in two tables can cause inconsistencies and makes querying the data a bit more complex. Imagine you wanted to edit the name of a patient who is also a doctor, you'd have to edit both tables and ensure that there are no inconsistencies.

Introducing a supertype

So we can create a people table and generalize the data:

create table people (
  person_id integer,
  full_name varchar2(100)
);

Now:

people is the supertype
patients and consultants are subtypes

Subtypes store only what’s specific to that role.

This structure:

avoids duplication
keeps shared data consistent

Whether you need supertypes depends on your requirements and that decision often changes over time.

9. Relational vs. Document Storage

It can be tempting to skip all of this and store everything as a single JSON document. These formats for me are more familiar due to my previous experiences.

Example:

{
  "appointmentDatetime": "2018-09-01 10:00",
  "location": {
    "name": "PHYSIO",
    "address": "1 Hospital Way"
  },
  "consultant": {
    "name": "Doctor Awesome"
  },
  "patient": {
    "name": "Miss Sick"
  }
}

This looks convenient but it has downsides:

harder to query
duplicated data
inconsistent values

Over time, these shortcuts usually create more problems than they solve.

Relational design takes longer upfront, but it tends to produce systems that are:

easier to query
easier to maintain
more flexible long-term

Final Thoughts

What stood out to me most is that good database design is about thinking, not syntax. SQL is just the final expression of decisions made much earlier.

I’m still very early in learning this, but even this high-level understanding already changes how I think about tables.

— Jessica Aki
Data and Database Engineering Enthusiast

I’m documenting my journey into data engineering learning SQL, databases, and the systems behind modern data platforms.

Columns & Data Types in Oracle: What Actually Stuck on Day 2 (As a Beginner)

Jessica Aki — Sat, 17 Jan 2026 23:24:00 +0000

Day 2 of learning Oracle SQL was less about writing fancy queries and more about understanding what I’m actually storing in a database.

Columns and data types sound basic — and they are — but this was one of those topics where I realised I’d been memorising names without really thinking about why they matter.

This post is me documenting what finally clicked, what confused me, and what I think beginners should actually focus on.

Columns Are Rules, Not Just Labels

Before now, I thought of columns like this:

“Just names for values in a table. Or features of data.”

But in Oracle, columns are more like rules:

What kind of data is allowed here?
How big can it be?
Can it be empty?
Will Oracle try to convert it automatically?

Once I started seeing columns as constraints on reality, data types suddenly felt more important than before.

What I Practiced Today

I spent most of the day:

Creating tables manually
Choosing data types on purpose
Seeing what breaks when I choose the wrong one

Simple things, but very revealing.

Common Oracle Data Types

VARCHAR2

This is the one I’ll probably use the most.

name VARCHAR2(50)

What clicked for me:

VARCHAR2 is variable-length text
50 means maximum, not fixed size
Oracle doesn’t waste space if the value is shorter

So emails, names, cities are all perfect for VARCHAR2.

NUMBER

At first, I assumed numbers had to be very specific.

But Oracle surprised me.

salary NUMBER
age NUMBER(3)
balance NUMBER(10,2)

What stuck:

NUMBER alone is very flexible
NUMBER(10,2) means:
- 10 total digits (Significant figures)
- 2 after the decimal (Decimal places)

This suddenly explained why money fields behave the way they do.

DATE

date_of_birth DATE

I expected it to store just a date.

But Oracle DATE actually stores:

Date
Time (hours, minutes, seconds)

Even if you don’t see the time, it’s there. Which explains why date comparisons can behave oddly if you’re not careful.

CHAR

gender CHAR(1)

What I learnt:

CHAR is fixed length
Oracle pads unused space with blanks

It’s useful for things like flags (Y/N), but for most text, VARCHAR2 just makes more sense.

Creating a Table (Putting It Together)

This was one of the tables I created while practicing:

CREATE TABLE users (
  user_id NUMBER PRIMARY KEY,
  email VARCHAR2(100),
  age NUMBER(3),
  date_joined DATE
);

Seeing this helped me understand that Columns define structure and Data types define behavior

What Still Feels Weird

I’m still adjusting to:

Choosing the right size for text fields
Knowing when precision actually matters
Remembering Oracle-specific names (VARCHAR2 instead of VARCHAR, though both exist as datatypes)

But compared to Day 1? This already feels more intentional.

Why I’m Sharing This

Columns and data types are usually taught as:

“Here are the types. Memorise them.”

But as a beginner, this is about thinking ahead:

Future queries
Data integrity
Avoiding silent bugs

If you’re new to Oracle and this topic felt boring — same here at first.
But it’s one of those foundations that quietly makes everything else easier.

What’s Next

I’m continuing my slow, honest approach to learning databases one concept at a time.

If you’re also learning SQL from scratch, you’re not alone. I’m documenting every step, confusion included.

— Jessica Aki
Data and Database Engineering Enthusiast

I’m documenting my journey into data engineering learning SQL, databases, and the systems behind modern data platforms.

Learning Databases Beyond SQL: My First Day with Oracle Tables

Jessica Aki — Fri, 16 Jan 2026 23:12:56 +0000

Well today didn’t go the way I expected.

I planned to continue my usual PostgreSQL + SQL practice, but I ended up starting a free Oracle course for developers instead. I only completed the first module, but it introduced me to something I hadn’t really thought about before:

Tables are not just tables.

Same word. Very different behaviors.

This post is me documenting what I learned about table types in Oracle, what made sense immediately, and what feels advanced but interesting.

Tables Aren’t Always Just “A Place to Store Rows”

Before today, my mental model of a table was simple:

You create it → insert rows → query it.

Oracle kind of blew that up right in front of me.

Apparently, how a table stores data physically matters a lot, and Oracle gives you several options.

Heap Tables

The Default one everyone's familiar with. Heap tables are what I’m already used to. This basically means:

Rows go wherever there’s free space
No guaranteed order
General-purpose, most common type

create table toys_heap (
  toy_name varchar2(100)
) organization heap;

Honestly, this feels like the “normal” table I’ve always used, just with a formal name now.

Index-Organized Tables

Another familiar one but now having a formal name to identify by. Where the heap table store rows randomly, the index-organized tables on the other hand have rows where everything is physically sorted by the primary key.

create table toys_iot (
  toy_id   integer primary key,
  toy_name varchar2(100)
) organization index;

This is useful when:

You always search by primary key
You want fast lookups without jumping between table + index

External Tables

This one surprised me as they're tables That Aren’t Really tables. External tables let you query files as if they were tables like CSVs (Comma Separated Values).

create table toys_ext (
  toy_name varchar2(100)
) organization external (
  default directory tmp
  location ('toys.csv')
);

Important thing I learned:

The file lives on the database server
You’re not importing data
You’re reading it in place

Temporary Tables

For Session-Only Data. There are two types:

Global Temporary Tables Everyone can see the table but only you can see the rows you insert.

create global temporary table toys_gtt (
  toy_name varchar2(100)
);

What confused me at first for this type was that the table definition is permanent but the data is session-specific. So in the environment, if you query the list of user tables, it is displayed but it you terminate the session, the data goes with the session.

Private Temporary Tables

This one feels almost secretive because even the table is private. Only your session can see the table while other sessions don’t even know it exists. Oracle doesn’t store it in the data dictionary

create private temporary table ora$ptt_toys (
  toy_name varchar2(100)
);

Partitioned Tables

Partitioning was the most “enterprise” concept today. The idea is simple:

Split one big table into smaller ones based on a key.

Oracle supports:

Range (e.g. dates)
List (specific values)
Hash (even distribution)

Example:

create table books_hash (
  toy_name varchar2(100)
) partition by hash ( toy_name ) partitions 4;

What clicked here was that queries can scan only the relevant partition and this matters a lot at scale.

Table Clusters

Storing Related Tables Together. This one felt advanced but fascinating. You can physically store rows from different tables together if they share a key.

create cluster toy_cluster (
  toy_name varchar2(100)
);

Then:

create table toys_cluster_tab (
  toy_name varchar2(100)
) cluster toy_cluster ( toy_name );

What I Took Away From Today's Session

Today wasn’t about writing complex queries. It was about learning and realizing core concepts.

Databases care deeply about how data is stored and tables are a design decision, not just syntax and SQL is only part of the story.

Some of this felt familiar and Some of it felt intimidating but All of it felt useful.

What’s Next

Continue the Oracle database for developers modules. Keep grounding concepts in why they exist. Slowly connect this to PostgreSQL and data engineering concepts.

I’m still very much a beginner, but days like this make the database world feel bigger and more interesting.

If you’re also learning databases and everything feels like “too much” sometimes, you’re not alone. I’m figuring it out one module at a time.

— Jessica Aki
Data and Database Engineering Enthusiast

I’m documenting my journey into data engineering learning SQL, databases, and the systems behind modern data platforms.

SQL Aggregations Finally Made Sense: GROUP BY, HAVING, MIN, MAX, AVG

Jessica Aki — Tue, 13 Jan 2026 23:02:48 +0000

Today was one of those SQL days where things look simple… until you actually write the query.

I focused on GROUP BY, HAVING, and the basic aggregate functions: MIN, MAX, and AVG. These are things I’ve heard about for a long time, but I realised I didn’t truly understand them until I tried using them with real questions.

This post is me documenting what finally made sense, and what confused me at first.

The Mental Shift: From Rows to Groups

Up until now, most of my SQL queries felt like this:

“Show me rows that match X condition”

But GROUP BY changes the game. Instead of thinking about rows, you start thinking about groups of rows. That was the first uncomfortable part. SO let me show you what I did.

What I Practiced Today

I worked with queries that asked questions like:

How many users per country?
What’s the average salary per department?
Which department has the highest average score?

All of these queries required grouping. Not a simple get rows that fulfil this conditions.

GROUP BY (What Finally Clicked)

At first, I kept getting errors like:

column must appear in the GROUP BY clause or be used in an aggregate function

That error message annoyed me… until I started thinking more and trying to understand the message and task better.

What helped me:

If a column is not inside an aggregate function, it must be in the GROUP BY.

Example:

SELECT country, COUNT(*) 
FROM users
GROUP BY country;

This works because:

country → used for grouping
COUNT(*) → summarizes each group

But this does NOT work:

SELECT country, email
FROM users
GROUP BY country;

Because now SQL is like:

“Which email do you want me to pick for each country??”

That was my first real “okay… fair enough” moment.

MIN, MAX, and AVG (Straightforward but Powerful)

Once grouping made sense, these felt more natural.

MIN

SELECT department, MIN(salary)
FROM employees
GROUP BY department;

→ lowest salary per department

MAX

SELECT department, MAX(salary)
FROM employees
GROUP BY department;

→ highest salary per department

AVG

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

→ average salary per department

Nothing fancy but seeing real numbers come out per group made SQL feel more real-world.

HAVING (The Part I Confused with WHERE)

This is where I stumbled a bit.

At first, I kept writing queries like this:

SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 50000
GROUP BY department;

And SQL basically said nope.

The Difference That Finally Stuck:

WHERE filters rows before grouping

HAVING filters groups after grouping

So the correct version was supposed to be:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Once I saw HAVING as “WHERE for grouped data”, it stopped feeling magical though.

Order of Execution

I then learnt the order of execution of SQL queries and understanding the order made everything clearer:

FROM
WHERE (filter rows)
GROUP BY (create groups)
HAVING (filter groups)
SELECT
ORDER BY

This explains why HAVING exists, WHERE simply runs too early.

What Still Feels Weird

I’m still adjusting to:

Thinking in groups instead of rows
Knowing when I actually need GROUP BY
Reading grouped queries without mentally getting lost

But compared to yesterday? This is progress.

Why I’m Sharing This

GROUP BY and HAVING are usually taught quickly, like:

“Here’s the syntax, move on.”
But as a beginner, this is a mental shift, not just syntax.

If you’re also learning SQL and GROUP BY felt confusing at first, same here.

What’s Next

I’m learning SQL slowly, properly, and honestly and I’m documenting the process so other beginners don’t feel like they’re the only ones struggling.

If you’re on the same path, feel free to follow along.

— Jessica Aki
Data and Database Engineering Enthusiast

I’m documenting my journey into data engineering learning SQL, databases, and the systems behind modern data platforms.

I Built a PostgreSQL User Analytics System — Here’s What I Learned About Real SQL

Jessica Aki — Fri, 09 Jan 2026 23:24:34 +0000

Today, I built a User Analytics Database in PostgreSQL. The goal was to simulate a real-world business scenario where we need to answer questions like:

How many users registered in the last month?
Which countries have the most users?
How many users use Gmail, Yahoo, or corporate emails?
Who are the youngest and oldest users?

The project helped me combine database design, SQL query practice, and realistic dataset handling into one hands-on exercise.

Tech Stack

PostgreSQL
SQL
Mockaroo (to generate realistic user data)

Database Schema — The users Table

Column Name Data Type   Constraint
id BIGSERIAL PRIMARY KEY, NOT NULL
first_name VARCHAR(50)  NOT NULL
last_name VARCHAR(50)   
email VARCHAR(150)  UNIQUE
gender VARCHAR(10)  
country VARCHAR(50) 
date_of_birth DATE  
registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

This single table already allowed me to answer dozens of meaningful business questions.

Key Queries I Practiced

Total Number of Users

SELECT COUNT(*) AS "Total Users" FROM users;

Top 10 Countries by Users

SELECT country, COUNT(*) AS "Total Users"
FROM users
GROUP BY country
ORDER BY COUNT(country) DESC
LIMIT 10;

Gender Distribution

SELECT gender, COUNT(gender) AS "Total Users"
FROM users
GROUP BY gender
ORDER BY gender;

Users Registered in Last 30 Days

SELECT *
FROM users
WHERE registered_at >= NOW() - INTERVAL '30 days';

Oldest & Youngest Users

-- Oldest
SELECT first_name, last_name, EXTRACT(YEAR FROM AGE(NOW(), date_of_birth)) AS age
FROM users
WHERE date_of_birth = (SELECT MIN(date_of_birth) FROM users);

-- Youngest
SELECT first_name, last_name, date_of_birth
FROM users
WHERE date_of_birth = (SELECT MAX(date_of_birth) FROM users);

Email Provider Analysis

SELECT COUNT(email)
FROM users
WHERE email ILIKE '%gmail%' OR email ILIKE '%yahoo%' OR email ILIKE '%facebook%';

Users Born Between 1990 and 2000

SELECT *
FROM users
WHERE date_of_birth BETWEEN DATE '1990-01-01' AND '2000-12-31';

Countries with More Than 50 Users

SELECT country, COUNT(*)
FROM users
GROUP BY country
HAVING COUNT(*) > 50;

Most Recent 20 Registrations

SELECT * FROM users ORDER BY registered_at DESC LIMIT 20;

What I Learned This Week

Importing real datasets into PostgreSQL using \i and COPY.
Executing queries in correct order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
Using intervals and EXTRACT to calculate ages and filter by date ranges.
Reinforcing constraints, primary keys, and uniqueness for data integrity.
Understanding practical SQL for real business insights — not just exercises.

Screenshots / Proof

The Schema
\d users table schema
Total Users

Total Users in the top 10 countries

Gender Distribution

Users Registered in the last 30 days

Oldest User

Youngest User

How to Run This Project Yourself

Clone the repository.
Open PostgreSQL and connect to your server.
Run schema.sql to create the database and table.
Run data_import.sql to load Mockaroo-generated users.
Run queries.sql to execute analytics queries.

Explore your own analysis and see what insights you can generate.

Final Thoughts

What surprised me most is how extendable this project is. Without changing much, you can:

Add an orders or transactions table
Link it with a foreign key (user_id)
Start answering questions like:
- Which countries generate the most revenue?
- Do younger users register more frequently?
- How long after registration do users become active?

The foundation is already there.

That’s when you realize:

Even “simple” SQL projects can grow into real systems.

Conclusion

This project isn’t advanced and that’s the point. It proves that:

You don’t need complex schemas to practice real SQL
You don’t need fancy tools to get meaningful insights
You can build something useful earlier than you think

I’m continuing to build, extend, and document this system as I learn, one step at a time. I hope to you see you on this journey and I would love to hear your opinions.

— Jessica Aki
Data and Database Engineering Enthusiast

I’m documenting my journey into data engineering learning SQL, databases, and the systems behind modern data platforms.

Forem: Jessica Aki

SQL Stored Procedures: From Hard‑Coded Logic to Reusable SQL

What Finally Clicked: Stored Procedures Are Just Reusable Logic

Why Plain SQL Started Feeling Limited

My First Useful Stored Procedure

The Real Upgrade: Parameters

Where CASE Entered the Picture

Combining CASE With Stored Procedures

What I Was Doing Wrong Before

Why This Matters Beyond Syntax

Closing

ROLLUP vs CUBE: How I Finally Understood SQL Subtotals

The Setup: A Real Sales Table

Where I Was Comfortable: Basic GROUP BY

Where Things Started to Break: Subtotals

My First Wrong Assumption

What ROLLUP Actually Does

What the Results Really Mean

A Subtle Query That Forced Clarity

Where CUBE Finally Made Sense

How I Now Tell Them Apart

ROLLUP

CUBE

Closing Thought

Old Oracle Joins vs ANSI JOINs: Why I Chose One and Stuck With It

The Two Ways to Write Joins in Oracle

Example Scenario

Old Oracle LEFT OUTER JOIN (+)

What’s happening here?

The problem

ANSI LEFT JOIN (The Same Query)

Why this reads better

FULL OUTER JOIN: The Big Difference

ANSI syntax

Old Oracle syntax

Portability Matters

Why I Personally Chose ANSI JOINs

Final Thought

How SQL JOINs Finally Clicked for Me (and How They Can for You Too)

The Big Block That Held Me Back from Understanding

The Setup: Two Simple Tables

Customers

Orders

CROSS JOIN — Why This One Feels Wrong (and That’s Normal)

INNER JOIN — The Join That made Joins To Finally Make Sense

The rule

What happens

LEFT JOIN

Rule

Result

RIGHT JOIN

FULL OUTER JOIN

JOIN Conditions Are Not Always Equality

Everything came together but there was a Mistake I Discovered I Was Making

The mistake

The fix I learnt

My Conclusions

Quick Summary

SQL Dates Finally Made Sense: NOW, AGE, EXTRACT, and Why Time Is Weird

The First Surprise: Dates Are Not Just Numbers or strings

NOW() — The Current Moment

AGE() — Time Between Two Dates

EXTRACT() — Pulling Out Pieces of Time

Intervals — Time as a Data Type

What Still Feels Weird

Conclusion

SQL Nulls Finally Made Sense: COALESCE, NULLIF, and Safer Calculations

These are things I’ve seen mentioned casually in tutorials, but I didn’t really feel why they mattered until I started doing real calculations.

The Problem I Kept Running Into

COALESCE

NULLIF (The Function I Didn’t Understand at First)

Arithmetic Operations

SUM, ROUND, and MOD

SUM

ROUND

MOD

Today's Takeaway

What Still Feels Weird

Conclusion

What’s Next

Old Oracle LEFT OUTER JOIN `(+)`

Selecting Rows (and Why `SELECT *` Is a Trap)

Filtering Rows with `WHERE`

Combining Conditions: `AND`, `OR`, and Confusion

Use parentheses `()`. Always.

Lists of Values with `IN`

Ranges with `<`, `>=`, and `BETWEEN`

Wildcards and Pattern Matching (`LIKE`)

`NULL` Is… Weird (But Makes Sense)