DEV Community: Faith Njenga

SQL Is Surviving, Franklin: Now Rows Are Competing

Faith Njenga — Tue, 15 Sep 2026 13:33:06 +0000

If you have survived SELECT, WHERE, JOIN, GROUP BY, subqueries, and CTEs, congratulations.

Franklin has now discovered window functions.

And window functions are where SQL starts doing something that feels a little suspicious.

They can look at other rows without collapsing the rows you already have.

You can have:

An employee's salary and their department's average salary
A student's score and their rank
A month's sales and the running total
Today's sales and yesterday's sales

All in the same result.

Franklin has questions.

"Wait. SQL can look at other rows and still give me my original row?"

Yes, Franklin.

Welcome to window functions.

First, What Problem Are We Actually Solving?

Let's start with a tiny table.

employee	department	salary
Amina	Data	80000
Brian	Data	100000
Carol	HR	70000
David	HR	90000

Now imagine your manager asks:

"Show me every employee, their salary, and the average salary of their department."

You might think:

GROUP BY has entered the chat.

And you'd be right. Sort of.

We could calculate the department averages:

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

The result:

department	avg_salary
Data	90000
HR	80000

Great.

Except...

Where did the employees go?

Amina disappeared.

Brian disappeared.

Carol disappeared.

David disappeared.

GROUP BY grouped the rows together to calculate one result per department.

But our question wanted both:

The individual employee
Information about the employee's group

This is where window functions walk in.

Meet `OVER()`: The Part That Makes the Window

Before we start throwing PARTITION BY, ORDER BY, and other SQL vocabulary at Franklin, let's understand the most important piece:

OVER()

OVER() tells SQL:

"Perform this calculation across a set of rows, but keep the individual rows in the result."

For example:

SELECT
    employee,
    salary,
    AVG(salary) OVER () AS average_salary
FROM employees;

The result could look like this:

employee	salary	average_salary
Amina	80000	85000
Brian	100000	85000
Carol	70000	85000
David	90000	85000

Let's break this down.

AVG(salary)

means:

"Calculate the average salary."

Then:

OVER()

means:

"Calculate it across the rows available to this query, but don't combine those rows into one."

That's the big difference.

Without a window function, an aggregate like AVG() can give us one overall result.

With AVG() OVER(), we can show the average alongside every individual row.

Think of OVER() as the doorway into window-function territory.

Once we step inside, we can tell SQL more specifically which rows it should consider.

`PARTITION BY`: Franklin, Pick Your Group

What if we don't want the average salary for everyone?

What if we want the average salary within each department?

That's where PARTITION BY comes in.

SELECT
    employee,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS department_avg
FROM employees;

The result:

employee	department	salary	department_avg
Amina	Data	80000	90000
Brian	Data	100000	90000
Carol	HR	70000	80000
David	HR	90000	80000

Let's break down the important part:

PARTITION BY department

This tells SQL:

"Separate the rows into groups based on department, but don't collapse them."

So SQL can mentally create:

Data

employee	salary
Amina	80000
Brian	100000

employee	salary
Carol	70000
David	90000

Then AVG() works within each group.

This is one of the most important differences to remember:

GROUP BY collapses rows into groups.

PARTITION BY creates groups for the window calculation while keeping the original rows.

Franklin can breathe again.

`ORDER BY`: Now Franklin Needs an Order

PARTITION BY answers:

"Which rows belong together?"

Sometimes we also need to answer:

"In what order should SQL consider these rows?"

That's where ORDER BY inside OVER() comes in.

For example, suppose we want to rank employees by salary.

SELECT
    employee,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Here:

PARTITION BY department

means:

"Rank employees separately within each department."

And:

ORDER BY salary DESC

means:

"Put the highest salary first."

So OVER() is the container, while PARTITION BY and ORDER BY give SQL more instructions about the rows involved.

Ranking: Apparently, Everyone Needs a Position

Now let's look at three ranking functions.

We'll use this table:

employee	salary
Amina	100000
Brian	100000
Carol	80000
David	70000

`ROW_NUMBER()`: Everyone Gets a Number

SELECT
    employee,
    salary,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC
    ) AS row_number
FROM employees;

Result:

employee	salary	row_number
Amina	100000	1
Brian	100000	2
Carol	80000	3
David	70000	4

Notice something important.

Amina and Brian have the same salary, but they still receive different numbers.

ROW_NUMBER() gives every row a unique position.

Think:

"I don't care about ties. Just number every row."

`RANK()`: Ties Are Allowed

Now let's use RANK().

SELECT
    employee,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Result:

employee	salary	salary_rank
Amina	100000	1
Brian	100000	1
Carol	80000	3
David	70000	4

Amina and Brian both receive rank 1 because they have the same salary.

But notice what happened next.

There is no rank 2.

The next person gets rank 3.

Think:

"Ties get the same rank, and the next rank can have a gap."

`DENSE_RANK()`: Ties Without the Gap

Now:

SELECT
    employee,
    salary,
    DENSE_RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Result:

employee	salary	salary_rank
Amina	100000	1
Brian	100000	1
Carol	80000	2
David	70000	3

Amina and Brian still tie for first.

But this time, Carol gets rank 2.

No gap.

So Franklin's ranking dictionary is:

Function	What it does
`ROW_NUMBER()`	Gives every row a unique number
`RANK()`	Ties get the same rank, and gaps can appear
`DENSE_RANK()`	Ties get the same rank, but no gaps appear

Three functions.

Three slightly different personalities.

Choose based on what the question actually asks.

`LAG()` and `LEAD()`: Looking Behind and Ahead

Now suppose we have monthly sales:

month	sales
January	100
February	200
March	300
April	400

And someone asks:

"How much did sales change compared with the previous month?"

We could try to join the table to itself.

Or we could use LAG().

SELECT
    month,
    sales,
    LAG(sales) OVER (
        ORDER BY month
    ) AS previous_month
FROM sales;

Result:

month	sales	previous_month
January	100	NULL
February	200	100
March	300	200
April	400	300

LAG() looks backward.

For February, it finds January.

For March, it finds February.

For April, it finds March.

The first row has nothing before it, so SQL gives us NULL.

And LEAD() does the opposite.

LEAD(sales) OVER (
    ORDER BY month
)

LEAD() looks at the row after the current row.

So:

LAG() → "What happened before me?"
LEAD() → "What happens after me?"

Franklin is now looking into the past and the future.

Running Totals: When `SUM()` Gets a Memory

Now someone asks:

"Show me the sales for each month and the total sales accumulated so far."

Our table:

month	sales
January	100
February	200
March	300
April	400
May	500

We can write:

SELECT
    month,
    sales,
    SUM(sales) OVER (
        ORDER BY month
    ) AS running_total
FROM sales;

And the result can be:

month	sales	running_total
January	100	100
February	200	300
March	300	600
April	400	1000
May	500	1500

But wait.

Franklin has a very reasonable question:

"Hold on. I only wrote SUM(sales) and ORDER BY month. How did SQL know that February should include January, and March should include January and February?"

Excellent question.

There is something happening behind the scenes.

When an aggregate window function such as SUM() has an ORDER BY but no frame explicitly specified, the database uses a default window frame.

For this simple example, that gives us the running-total behaviour we want.

Conceptually, SQL is looking at:

January
→ January

February
→ January + February

March
→ January + February + March

April
→ January + February + March + April

May
→ January + February + March + April + May

So:

100
100 + 200 = 300
100 + 200 + 300 = 600
100 + 200 + 300 + 400 = 1000
100 + 200 + 300 + 400 + 500 = 1500

Now we can make those boundaries explicit:

SELECT
    month,
    sales,
    SUM(sales) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales;

The result is:

month	sales	running_total
January	100	100
February	200	300
March	300	600
April	400	1000
May	500	1500

This version tells SQL exactly what we mean:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

In human language:

"Start from the very first row and keep including rows until the row I'm currently calculating."

And now Franklin asks:

"Okay... what exactly are ROWS, BETWEEN, UNBOUNDED PRECEDING, and CURRENT ROW?"

Franklin.

You just opened the window frame.

Meet the Window Frame: Franklin, We Need Boundaries

A window is the set of rows that a window function can work with.

A window frame goes one step further.

It tells SQL:

"From that window, these are the exact rows I want you to use for this calculation."

This is where you might see:

CURRENT ROW
PRECEDING
FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
ROWS BETWEEN

They sound complicated.

They're really just instructions about where to look.

`CURRENT ROW`: Where Am I?

CURRENT ROW means:

"The row I'm currently calculating."

If SQL is calculating the result for March:

January
February
March     <- CURRENT ROW
April
May

March is the current row.

Simple.

`PRECEDING`: Look Behind Me

PRECEDING means rows before the current row.

For example:

2 PRECEDING

means:

"Go back two rows."

If we're on March:

January   <- 2 PRECEDING
February  <- 1 PRECEDING
March     <- CURRENT ROW
April
May

`FOLLOWING`: Look Ahead

FOLLOWING means rows after the current row.

For example:

2 FOLLOWING

means:

"Look two rows ahead."

If we're on March:

January
February
March     <- CURRENT ROW
April     <- 1 FOLLOWING
May       <- 2 FOLLOWING

Franklin has now learned that SQL can look backward and forward.

`ROWS BETWEEN`: Tell SQL Exactly Where to Look

Suppose we want a 3-month moving average.

For each month, we want:

Current month + the two months before it.

We can write:

SELECT
    month,
    sales,
    AVG(sales) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM sales;

Let's translate the important part.

ROWS BETWEEN

means:

"Define the exact rows I want to use."

2 PRECEDING

means:

"Start two rows before me."

CURRENT ROW

means:

"Stop at the row I'm currently calculating."

So for March, the frame is:

January   <- 2 PRECEDING
February  <- 1 PRECEDING
March     <- CURRENT ROW

Those three rows are used to calculate March's moving average.

The result:

month	sales	moving_avg
January	100	100
February	200	150
March	300	200
April	400	300
May	500	400

Notice that January doesn't have two previous rows.

SQL doesn't invent them.

It simply uses the rows that actually exist.

`UNBOUNDED PRECEDING`: Start From the Beginning

Now imagine we want a running total.

Instead of saying:

"Go back two rows."

We want:

"Go all the way back to the first row."

That's:

UNBOUNDED PRECEDING

So we can write:

SELECT
    month,
    sales,
    SUM(sales) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales;

In plain English:

"Start at the very first row and keep going until the row I'm currently calculating."

So when we're calculating April:

January   <- start here
February
March
April     <- CURRENT ROW
May

SQL adds January through April.

The result is:

month	sales	running_total
January	100	100
February	200	300
March	300	600
April	400	1000
May	500	1500

That's why the running total keeps growing.

`UNBOUNDED FOLLOWING`: Go All the Way to the End

There is also:

UNBOUNDED FOLLOWING

This means:

"Keep going until the last row."

For example:

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

means:

"Start at my current row and continue all the way to the end."

If we're on March:

January
February
March     <- CURRENT ROW
April
May       <- UNBOUNDED FOLLOWING

The frame is:

March + April + May

You don't need to memorize every possible combination right away.

Start with the vocabulary.

Franklin's Window Frame Dictionary

SQL	Human translation
`CURRENT ROW`	The row I'm on
`1 PRECEDING`	One row before me
`2 PRECEDING`	Two rows before me
`1 FOLLOWING`	One row after me
`2 FOLLOWING`	Two rows after me
`UNBOUNDED PRECEDING`	Start from the beginning
`UNBOUNDED FOLLOWING`	Go all the way to the end

And:

ROWS BETWEEN A AND B

basically means:

"Use the rows from A through B for this calculation."

That's the idea you want to carry with you.

`WHERE` Comes First: Sorry, Window Functions

Here's another rule that actually matters.

Suppose we calculate employee rankings:

SELECT
    employee,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Now Franklin says:

"Cool. Give me only employees ranked number 1."

Naturally, he tries:

SELECT
    employee,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees
WHERE salary_rank = 1;

SQL says:

"Nope."

Why?

Because, in the simplified logical processing order, WHERE is handled before the window function result is available.

A simplified order looks like this:

FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
WINDOW FUNCTIONS
↓
ORDER BY

So when SQL is evaluating WHERE, salary_rank isn't available yet.

The solution is to give SQL another query layer.

WITH ranked_employees AS (
    SELECT
        employee,
        salary,
        RANK() OVER (
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)

SELECT *
FROM ranked_employees
WHERE salary_rank = 1;

The CTE calculates the rank first.

Then the outer query receives that result.

Now WHERE can filter it.

Franklin survives.

Again.

Can Window Functions Be Nested?

Franklin has gained confidence.

Perhaps too much confidence.

He tries this:

AVG(
    RANK() OVER (
        ORDER BY salary DESC
    )
) OVER ()

No.

Window functions cannot be directly nested inside another window function.

If you need to use the result of one window calculation in another calculation, use another query layer.

For example:

WITH ranked AS (
    SELECT
        employee,
        salary,
        RANK() OVER (
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)

SELECT
    employee,
    salary,
    salary_rank
FROM ranked;

Think of it this way:

One query layer calculates. Another query layer uses the result.

This is another place where CTEs and subqueries become useful.

Can Window Functions Work With `GROUP BY`?

Yes.

This is another common misconception.

Window functions and GROUP BY can absolutely appear in the same query.

Suppose we first calculate the average salary for each department:

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

Now we have one row per department.

We can rank those department averages:

SELECT
    department,
    AVG(salary) AS avg_salary,
    RANK() OVER (
        ORDER BY AVG(salary) DESC
    ) AS department_rank
FROM employees
GROUP BY department;

The important idea is:

GROUP BY determines the grouped rows first, and the window function can then work with those resulting rows.

So don't memorize:

"Window functions and GROUP BY don't mix."

That's not true.

Instead, ask:

"What rows exist at the point where my window function is being calculated?"

That question will save you from a lot of SQL confusion.

Franklin's Window Function Survival Guide

Before Franklin walks away pretending he understood everything on the first try, let's recap.

Need to keep individual rows?

Think:

OVER()

Need calculations within groups?

Think:

PARTITION BY

Need the rows considered in a particular order?

Think:

ORDER BY

Need rankings?

Think:

ROW_NUMBER()
RANK()
DENSE_RANK()

Need the previous row?

Think:

LAG()

Need the next row?

Think:

LEAD()

Need a running total?

Think:

SUM() OVER (ORDER BY ...)

Need a moving calculation?

Think:

ROWS BETWEEN ...

Need to look backward?

Think:

PRECEDING

Need to look forward?

Think:

FOLLOWING

Need to start from the first row?

Think:

UNBOUNDED PRECEDING

Need to go to the last row?

Think:

UNBOUNDED FOLLOWING

Need to filter a window-function result?

Don't put it directly in WHERE.

Use a:

CTE or subquery

Need one window function to use another window function's result?

Don't nest them directly.

Use another query layer.

So, What Exactly Did We Survive?

Window functions aren't really about doing "complicated calculations."

They're about answering questions that involve relationships between rows without throwing those rows away.

You can ask:

"Who is the highest-paid employee in each department?"

"How does this month's sales compare with last month's?"

"What's the running total so far?"

"What's the three-month moving average?"

"Where does each student rank in their class?"

"What was the previous transaction?"

"What comes next?"

And suddenly SQL isn't just asking:

"What rows do you want?"

It's asking:

"How should these rows understand each other?"

That's the magic of window functions.

Franklin came in thinking SQL only knew how to look at one row at a time.

He leaves knowing SQL can look across groups, backward, forward, and through carefully defined frames.

He just shouldn't try to put a window function inside another window function.

SQL has boundaries.

Even Franklin needs some.

What's Next?

We've survived:

SELECT
Filtering
GROUP BY
Joins
Subqueries
CTEs
Window functions

And somehow, Franklin is still here.

So apparently, surviving SQL was never the finish line.

It was just the beginning.

SQL Is Surviving, Franklin: When One Query Isn't Enough

Faith Njenga — Mon, 14 Sep 2026 04:47:42 +0000

Franklin survived SQL JOINs.

Barely.

Last time, we made tables talk to each other. We connected students to classes, classes to teachers, and somehow convinced Franklin that duplicate rows were not a database malfunction.

He was beginning to feel confident.

Then someone asked:

"Which students scored above the average?"

Franklin smiled.

Easy.

He opened his laptop and started typing.

Then he stopped.

Wait.

What is the average?

He needs to calculate the average before he can compare the students' scores to it.

And suddenly, SELECT doesn't feel so simple anymore.

Because sometimes...

one query isn't enough.

Welcome back to SQL survival.

Today, we're talking about subqueries and CTEs.

Previously in SQL...

In the first article, we survived the basics:

DDL
DML
DQL
SELECT
INSERT
UPDATE
DELETE

Then came JOINs.

We learned how to combine information from multiple tables using:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN

We also learned about primary keys, foreign keys, aliases, and relationships.

Basically, Franklin learned how to get tables to communicate.

But now we have a different problem.

Sometimes the information we need isn't simply sitting in another table.

Sometimes we need to calculate something first, then use that result to answer another question.

That's where subqueries come in.

So... What Is a Subquery?

A subquery is simply a query inside another query.

That's it.

A query...

inside another query.

SQL looked at one query and thought:

"You know what would make this more interesting?"

Another query.

For example, suppose Greenwood Academy has this table:

`scores`

student_id	student_name	subject	score
1	Brian	Mathematics	85
2	Mercy	Mathematics	72
3	Kevin	Mathematics	91
4	Sarah	Mathematics	64
5	Jane	Mathematics	78

Now the principal asks:

"Which students scored above the average?"

We could first calculate the average:

SELECT AVG(score)
FROM scores;

Suppose SQL gives us:

Now we need:

SELECT student_name, score
FROM scores
WHERE score > 78;

But there's a problem.

We don't want to manually type 78.

What if the data changes?

What if another student gets added?

What if the average becomes 79.4?

We want SQL to calculate the average for us.

So we put one query inside another.

Our First Subquery

SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT AVG(score)
    FROM scores
);

Look closely.

We have:

SELECT AVG(score)
FROM scores

inside:

SELECT
    student_name,
    score
FROM scores
WHERE score > (...)

The inner query calculates the average.

The outer query finds students whose scores are higher than that average.

So conceptually:

              INNER QUERY
                   ↓
          Calculate the average
                   ↓
                 78
                   ↓
              OUTER QUERY
                   ↓
       Find scores greater than 78

That's a subquery.

Franklin has officially taught SQL to ask itself a question.

How Does SQL Execute This?

At a simplified level, you can think of it like this:

Step 1

SQL runs:

SELECT AVG(score)
FROM scores;

Result:

Step 2

SQL effectively uses that result here:

WHERE score > 78

Step 3

It returns:

student_name	score
Brian	85
Kevin	91

And Franklin looks at the result like:

"Oh."

"That actually worked."

Subqueries in `WHERE`

One of the most common places you'll see a subquery is inside a WHERE clause.

Let's try another example.

Suppose we want students who scored the highest score.

First, we could find the highest score:

SELECT MAX(score)
FROM scores;

Suppose the result is:

Then we could find the student:

SELECT
    student_name,
    score
FROM scores
WHERE score = 91;

But again, we can let SQL do both jobs:

SELECT
    student_name,
    score
FROM scores
WHERE score = (
    SELECT MAX(score)
    FROM scores
);

The inner query finds the highest score.

The outer query finds the student who achieved it.

The Inner Query vs The Outer Query

This terminology is worth remembering.

In:

SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT AVG(score)
    FROM scores
);

The query inside the parentheses is the:

subquery / inner query

The query surrounding it is the:

outer query

Think of it like:

OUTER QUERY
┌───────────────────────────────┐
│                               │
│   WHERE score >               │
│          ↓                    │
│      ┌───────────────┐        │
│      │  SUBQUERY     │        │
│      │               │        │
│      │ AVG(score)    │        │
│      └───────────────┘        │
│                               │
└───────────────────────────────┘

A query inside a query.

SQL nesting dolls.

Subqueries With `IN`

Subqueries aren't limited to returning one value.

Sometimes the inner query returns multiple values.

That's where IN becomes useful.

Suppose we have:

`students`

student_id	student_name	class_id
1	Brian	101
2	Mercy	101
3	Kevin	102
4	Sarah	103

And:

`classes`

class_id	class_name
101	Form 4A
102	Form 3B
103	Form 2C

Now imagine we want:

"Show me students who are in Form 4A or Form 3B."

The inner query can find the relevant class IDs:

SELECT class_id
FROM classes
WHERE class_name IN ('Form 4A', 'Form 3B');

That gives us:

101
102

Then the outer query can use those values:

SELECT
    student_name
FROM students
WHERE class_id IN (
    SELECT class_id
    FROM classes
    WHERE class_name IN ('Form 4A', 'Form 3B')
);

The subquery returns multiple values:

101
102

And the outer query asks:

"Is this student's class_id in that list?"

`IN` vs `=`

This is an important distinction.

If your subquery returns one value, you might use:

For example:

WHERE score = (
    SELECT MAX(score)
    FROM scores
);

But if your subquery can return multiple values, you can use:

IN

For example:

WHERE class_id IN (
    SELECT class_id
    FROM classes
    WHERE class_name IN ('Form 4A', 'Form 3B')
);

Think:

=    → I expect one value

IN   → I can work with a list of values

And this distinction matters.

If your subquery returns multiple rows and you use =, SQL may complain.

And Franklin does not need another reason to panic.

Subqueries in `FROM`

Here's where things get a little more interesting.

A subquery can also appear inside the FROM clause.

For example:

SELECT *
FROM (
    SELECT
        student_name,
        score
    FROM scores
) AS student_scores;

Here, the subquery creates a temporary result that the outer query can work with.

You can think of it as:

"Run this query first, then treat its result like a table."

For example, suppose we want students who scored above 80.

We could create a temporary result:

SELECT
    student_name,
    score
FROM scores
WHERE score > 80;

Then use it:

SELECT *
FROM (
    SELECT
        student_name,
        score
    FROM scores
    WHERE score > 80
) AS high_scorers;

The AS high_scorers part gives the temporary result a name.

That's called an alias.

And yes, SQL is once again asking you to name things.

SQL loves naming things.

Why Would We Do That?

You might be wondering:

"Why not just write the original query?"

Good question.

For a simple example like this, there isn't much benefit.

The point is to understand the concept.

Subqueries in FROM become more useful when the inner query performs some calculation or transformation that the outer query needs to work with.

For example:

SELECT
    class_id,
    AVG(score) AS average_score
FROM scores
GROUP BY class_id;

Now imagine we want to work with those class averages in another query.

A subquery can give us that intermediate result.

This is where queries can start getting complicated.

And eventually, Franklin starts staring at his screen like:

"Who wrote this?"

He did.

Three hours ago.

Subqueries in `SELECT`

Yes.

We can even put a subquery inside the SELECT list.

For example:

SELECT
    student_name,
    score,
    (
        SELECT AVG(score)
        FROM scores
    ) AS average_score
FROM scores;

This can produce something like:

student_name	score	average_score
Brian	85	78
Mercy	72	78
Kevin	91	78
Sarah	64	78
Jane	78	78

The average is calculated by the subquery and displayed alongside each student.

Now, for a simple example like this, there are often better ways to solve the problem.

But it's useful to understand that a subquery can appear in different parts of a SQL statement.

Then Things Get Complicated

Subqueries are powerful.

But you can absolutely take them too far.

For example:

SELECT ...
FROM (
    SELECT ...
    FROM (
        SELECT ...
        FROM (
            SELECT ...
        ) AS something
    ) AS something_else
) AS another_thing;

At some point, Franklin isn't writing SQL anymore.

He's opening a portal.

This is one reason readability matters.

A query can be technically correct and still be painful for another human being to understand.

And that brings us to our next character.

Enter: The CTE

CTE stands for:

Common Table Expression

Don't let the name scare you.

A CTE is essentially a named temporary result that you define before the main query.

You create it using:

WITH

For example:

WITH average_score AS (
    SELECT AVG(score) AS avg_score
    FROM scores
)
SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT avg_score
    FROM average_score
);

At first glance, that might look like more SQL than our original solution.

And technically, it is.

But the structure is much easier to understand.

We're saying:

"First, calculate the average score."

Then:

"Now use that result to find students who scored above it."

Breaking Down a CTE

Let's separate the pieces.

WITH average_score AS (
    SELECT AVG(score) AS avg_score
    FROM scores
)

This creates a CTE called:

average_score

Inside it, we calculate:

AVG(score)

and give that result the name:

avg_score

Then comes the main query:

SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT avg_score
    FROM average_score
);

So conceptually:

WITH
   ↓
Create a temporary named result
   ↓
average_score
   ↓
Use it in the main query

It's much easier to read when your query becomes more complicated.

CTEs Are Like Giving Your Query Steps

This is probably the easiest way to think about them.

Instead of writing:

"SQL, do all of this giant thing and good luck."

You can say:

"First, do this."

Then:

"Now do this."

Then:

"Finally, give me this."

For example:

WITH average_score AS (
    SELECT AVG(score) AS avg_score
    FROM scores
)
SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT avg_score
    FROM average_score
);

The query now has a visible structure.

Step 1 → Calculate average

Step 2 → Compare student scores to average

Step 3 → Return students above average

Franklin can breathe again.

Multiple CTEs

Now we're getting somewhere.

You can define more than one CTE.

Suppose Greenwood Academy wants to calculate:

The average score for each class
Then find classes whose average is above 80 as the top students

We could start with:

WITH class_averages AS (
    SELECT
        class_id,
        AVG(score) AS average_score
    FROM scores
    GROUP BY class_id
)
SELECT
    class_id,
    average_score
FROM class_averages
WHERE average_score > 80;

The CTE handles the first task:

Calculate class averages

The main query handles the second:

Find averages above 80

This is where CTEs start becoming really useful.

Multiple CTEs Can Build on Each Other

You can also have one CTE build on another.

For example:

WITH student_averages AS (
    SELECT
        student_id,
        AVG(score) AS average_score
    FROM scores
    GROUP BY student_id
),
top_students AS (
    SELECT
        student_id,
        average_score
    FROM student_averages
    WHERE average_score >= 80
)
SELECT *
FROM top_students;

Look at what happened.

First:

student_averages

calculates each student's average.

Then:

top_students

uses that result.

Then the final query retrieves the students we want.

This is much easier to follow than trying to cram everything into one enormous statement.

Subquery vs CTE

Now comes the question:

"So which one should I use?"

The answer isn't:

"CTEs are always better."

That would be too easy.

Both have their place.

Use a subquery when:

the logic is simple
the result is only needed once
the query remains readable
you don't need to reuse the intermediate result

For example:

SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT AVG(score)
    FROM scores
);

That's perfectly reasonable.

Use a CTE when:

the query has multiple logical steps
you want to make the query easier to read
an intermediate result is reused
the query is becoming difficult to understand

For example:

WITH student_averages AS (
    SELECT
        student_id,
        AVG(score) AS average_score
    FROM scores
    GROUP BY student_id
)
SELECT *
FROM student_averages
WHERE average_score >= 80;

The important thing isn't:

"Always use CTEs."

It's:

Choose the structure that makes your logic easiest to understand and maintain.

A Realistic Greenwood Academy Problem

Let's give Franklin an actual assignment.

The principal walks in and says:

"Find all students whose average score is higher than the overall average score."

Now we're dealing with two different calculations.

We need:

Each student's average
The overall average

Then we compare them.

Franklin starts sweating.

But we can break it down.

Step 1: Calculate each student's average

SELECT
    student_id,
    AVG(score) AS student_average
FROM scores
GROUP BY student_id;

This gives us something like:

student_id	student_average
1	85
2	72
3	91
4	64
5	78

Step 2: Calculate the overall average

SELECT AVG(score)
FROM scores;

Suppose the result is:

Now we need to compare the two.

This is where a CTE makes the logic easier to follow.

WITH student_averages AS (
    SELECT
        student_id,
        AVG(score) AS student_average
    FROM scores
    GROUP BY student_id
)
SELECT
    student_id,
    student_average
FROM student_averages
WHERE student_average > (
    SELECT AVG(score)
    FROM scores
);

Now we're saying:

"Give me students whose average is greater than the overall average."

That's a much more interesting question than simply:

"Who scored above 80?"

And this is the kind of problem where subqueries and CTEs start becoming genuinely useful.

What About Correlated Subqueries?

Now we're stepping into slightly more advanced territory.

A correlated subquery is a subquery that depends on a value from the outer query.

Unlike our earlier subqueries, the inner query isn't completely independent.

For example, imagine we have:

`employees`

employee_id	employee_name	department_id	salary
1	Brian	10	80000
2	Mercy	10	95000
3	Kevin	20	70000
4	Sarah	20	85000

We want:

"Find employees earning more than the average salary in their own department."

Now the average depends on the employee's department.

We could write:

SELECT
    e.employee_name,
    e.department_id,
    e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

Notice this:

WHERE e2.department_id = e.department_id

The inner query refers to the outer query's e.department_id.

That's what makes it correlated.

The inner query needs information from the current row of the outer query.

Why Correlated Subqueries Matter

The previous example asks a more specific question.

Not:

"Who earns more than the average salary?"

But:

"Who earns more than the average salary for their own department?"

That's an important difference.

The comparison changes depending on the employee.

Conceptually:

Brian
  ↓
Find average salary in Brian's department
  ↓
Compare Brian's salary

Mercy
  ↓
Find average salary in Mercy's department
  ↓
Compare Mercy's salary

Kevin
  ↓
Find average salary in Kevin's department
  ↓
Compare Kevin's salary

The subquery is connected to the current row.

That's the idea behind correlation.

Don't Use a CTE Just Because You Can

Now that you've discovered CTEs, you may feel tempted to put WITH before everything.

Please don't.

This:

WITH students AS (
    SELECT *
    FROM students
)
SELECT *
FROM students;

doesn't make you look advanced.

It just makes Franklin tired.

If a simple query solves the problem clearly, use the simple query.

The goal isn't to use the most complicated SQL possible.

The goal is to write SQL that is:

correct
readable
maintainable
appropriate for the problem

Fancy SQL is not automatically good SQL.

Common Subquery Mistakes

Let's talk about the ways Franklin can still suffer.

1. Returning multiple rows when one value is expected

Suppose you write:

WHERE score = (
    SELECT score
    FROM scores
);

The inner query may return multiple scores.

But = expects one value.

That's a problem.

If the subquery returns multiple values, you might need something like:

IN

instead.

2. Forgetting the alias

When using a subquery in FROM, you generally need to give the derived table an alias.

For example:

SELECT *
FROM (
    SELECT
        student_name,
        score
    FROM scores
) AS student_scores;

Here:

student_scores

is the alias.

Don't leave your temporary result wandering around without a name.

3. Making the query impossible to read

This is probably the biggest danger.

You can technically nest queries inside queries inside queries.

But just because SQL lets you doesn't mean you should.

If your query looks like something Franklin wrote at 3:47 a.m. with one eye open...

stop.

Consider breaking the logic into a CTE.

4. Forgetting what your subquery returns

Before using a subquery, ask:

Does it return one value?

Or:

Does it return multiple rows?

Or:

Does it return an entire table-like result?

That determines how you can use it.

For example:

One value
→ =, >, <, etc.

Multiple values
→ IN, ANY, ALL, etc.

Table-like result
→ FROM / JOIN

Understanding the shape of your result is just as important as writing the syntax.

Subqueries vs JOINs

This is where people often get confused.

You might look at a problem and think:

"Should I use a JOIN or a subquery?"

The answer depends on what you're trying to accomplish.

A JOIN is primarily about combining related data from different tables.

For example:

SELECT
    s.student_name,
    c.class_name
FROM students AS s
JOIN classes AS c
    ON s.class_id = c.class_id;

You're bringing related information together.

A subquery is often about using the result of one query to help answer another question.

For example:

SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT AVG(score)
    FROM scores
);

You're using one calculation to filter another result.

So think:

JOIN
→ "Bring these related tables together."

SUBQUERY
→ "Use this query's result to answer another question."

Sometimes there are multiple valid ways to solve the same problem.

That's normal.

SQL gives you options.

Franklin gives himself headaches by trying all of them.

A Quick Mental Model

When you see a SQL question, ask yourself:

Do I need information from another table?

Think:

JOIN

Do I need the result of another query?

Think:

SUBQUERY

Is the logic becoming a sequence of steps?

Think:

CTE

For example:

"Show students and their class names."

JOIN

"Show students who scored above the average."

SUBQUERY

"Calculate student averages, filter them, then compare them with another result."

CTE + possibly a subquery

That mental distinction will save you a lot of confusion.

Franklin's Survival Guide

Let's make this painfully simple.

Subquery

A query inside another query.

SELECT ...
WHERE score > (
    SELECT AVG(score)
    FROM scores
);

Think:

One query needs another query's answer.

`IN`

Useful when the subquery returns multiple values.

WHERE class_id IN (
    SELECT class_id
    FROM classes
);

Think:

Is my value somewhere in this result?

CTE

A named temporary result created with WITH.

WITH average_scores AS (
    SELECT AVG(score) AS avg_score
    FROM scores
)
SELECT *
FROM average_scores;

Think:

Let's give this step a name.

Correlated subquery

A subquery that depends on the outer query.

Outer row
   ↓
Inner query uses information from that row
   ↓
Result

Think:

The inner query needs to know what the outer query is currently looking at.

Final Thoughts

So...

Franklin can now make tables talk.

He can JOIN them.

He can filter them.

And apparently, he can now make SQL ask itself questions.

We've gone from:

SELECT

to:

SELECT
    ...
WHERE something > (
    SELECT ...
);

And then to:

WITH something AS (
    SELECT ...
)
SELECT ...

Which is a pretty big leap from where we started.

But the most important thing isn't memorizing every variation of subqueries and CTEs.

It's learning how to break a problem down.

Ask yourself:

What do I need first?

Then:

What do I need to do with that result?

That little habit will take you surprisingly far.

Because SQL isn't really about memorizing hundreds of commands.

It's about learning how to turn a question into a series of smaller questions...

and then convincing the database to answer them.

Franklin is beginning to understand.

He's no longer just surviving SQL.

He's starting to think in SQL.

Which is great.

Until someone introduces him to window functions.

Because apparently, the rows need to start competing now.

But that's a problem for another day.

SQL Is Surviving, Franklin: When Tables Start Talking

Faith Njenga — Tue, 08 Sep 2026 13:09:53 +0000

So, you survived the basics of SQL.

You learned how to create tables, insert data, retrieve records, update rows, and hopefully avoid accidentally updating the entire database.

Because if you’ve ever run a DELETE statement without a WHERE clause and survived the experience, congratulations. You’ve already developed character.

But eventually, SQL gives you another problem: “Cool. Now get information from multiple tables.”

And suddenly, SELECT * FROM students; doesn’t feel so powerful anymore.

Welcome to JOINs.

Previously in SQL…

In Part 1, we looked at the basic categories of SQL commands:

DDL - Data Definition Language
DML - Data Manipulation Language
DQL - Data Query Language

We also worked with individual tables. But real databases rarely keep everything in one giant table.

Imagine trying to store an entire school database in one table:

student_name	class	teacher	subject	score
Brian	Form 4A	Mr. Kamau	Mathematics	78
Mercy	Form 4A	Mr. Kamau	Mathematics	91
Kevin	Form 3B	Ms. Achieng	Biology	84

At first, this looks convenient. Until you realize that you’re repeating class, teacher, subject, and student information over and over again.

That’s where relational databases come in.

Why Do We Have Multiple Tables?

A good database tries to avoid unnecessary repetition. Instead of putting everything into one massive table, we separate related information.

Let’s say our school is called Greenwood Academy. We could split our data into three clean tables:

`students`

student_id	student_name	class_id
1	Brian	101
2	Mercy	101
3	Kevin	102

`classes`

class_id	class_name	teacher_id
101	Form 4A	501
102	Form 3B	502

`teachers`

teacher_id	teacher_name	subject
501	Mr. Kamau	Mathematics
502	Ms. Achieng	Biology

Now the information is nicely separated. But there’s a problem. If someone asks: “Show me each student’s name, their class, and their teacher.” - no single table contains all three pieces of information.

So how do we bring them together? You guessed it. JOINs.

Before JOINs: Primary Keys and Foreign Keys

Before we start joining tables like we’re collecting Pokémon, we need to understand what connects them. That connection usually comes from primary keys and foreign keys.

Primary Key

A primary key uniquely identifies each record in a table. For example, student_id might uniquely identify every student. No two students should ever share the same student_id.

Foreign Key

A foreign key is a column that refers to a primary key in another table.

For example, look at the relationship between our students and classes tables:

students                 classes
-----------              -----------
student_id               class_id  <--- (Primary Key)
student_name                ↑
class_id    ────────────────┘ 
   │
   └─> (Foreign Key)

That’s the exact relationship SQL uses to connect the tables. Think of it as the database saying: “I don’t have the whole story here, but I know exactly where the rest of it lives.”

So… What Exactly Is a JOIN?

A JOIN allows you to combine rows from two or more tables based on a related column.

SELECT
    students.student_name,
    classes.class_name
FROM students
JOIN classes
    ON students.class_id = classes.class_id;

Here, we’re saying: “Take the students table, find the class that belongs to each student, and bring the information together side-by-side.”

The magic happens right here: ON students.class_id = classes.class_id. That is the structural bridge we are using to link them.

Meet the JOIN Family

SQL has several types of JOINs. The main ones you’ll encounter are:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN

Let’s break down how they behave differently.

1. INNER JOIN

INNER JOIN returns only the records that have a match in both tables. Think of it as: “Only show me people who are actually on both guest lists.”

SELECT
    students.student_name,
    classes.class_name
FROM students
INNER JOIN classes
    ON students.class_id = classes.class_id;

Imagine our dataset looks like this:

Students Table:

student_id	student_name	class_id
1	Brian	101
2	Mercy	101
3	Kevin	102
4	Sarah	999

Classes Table:

class_id	class_name
101	Form 4A
102	Form 3B

Sarah’s class_id is 999. Since there is no class with a class_id of 999 in the classes table, Sarah disappears from the results entirely.

The INNER JOIN basically says: “If we can’t find a perfect match, drop the row.”

2. LEFT JOIN

A LEFT JOIN returns all records from the left table, and the matching records from the right table. If there is no match, SQL fills the right-side columns with NULL.

SELECT
    students.student_name,
    classes.class_name
FROM students
LEFT JOIN classes
    ON students.class_id = classes.class_id;

Using the same data from before, Sarah would now appear in our results:

student_name	class_name
Brian	Form 4A
Mercy	Form 4A
Kevin	Form 3B
Sarah	NULL

Because the students table is written first (on the left side of the JOIN keyword), the LEFT JOIN mandates: “I don’t care if you find a match or not. Everybody from my table is coming along.”

This is incredibly useful when you want to look for missing or unlinked records. For example:

SELECT
    students.student_name,
    classes.class_name
FROM students
LEFT JOIN classes
    ON students.class_id = classes.class_id
WHERE classes.class_id IS NULL;

Now we are specifically filtering for students who don’t have a matching class. SQL has officially entered detective mode.

3. RIGHT JOIN

RIGHT JOIN is the exact opposite of LEFT JOIN. It keeps all records from the right table, even if there isn’t a matching record in the left table.

SELECT
    students.student_name,
    classes.class_name
FROM students
RIGHT JOIN classes
    ON students.class_id = classes.class_id;

This states: “Every class stays. If a student belongs to it, bring them along. If a class has zero students, display the class name anyway and fill the student column with NULL.”

That said, RIGHT JOIN isn’t used as often in the real world. Why? Because you can always rewrite it as a LEFT JOIN simply by flipping the order of your tables in the query:

SELECT
    students.student_name,
    classes.class_name
FROM classes
LEFT JOIN students
    ON students.class_id = classes.class_id;

Same idea, different direction. Keeping everything as a LEFT JOIN usually makes scripts much easier to read from top to bottom.

4. FULL OUTER JOIN

A FULL OUTER JOIN goes completely all-in. It keeps matching records, unmatched records from the left table, and unmatched records from the right table.

Essentially: “Bring everybody to the party. We’ll figure out the missing pieces later.”

SELECT
    students.student_name,
    classes.class_name
FROM students
FULL OUTER JOIN classes
    ON students.class_id = classes.class_id;

This query will simultaneously surface:

Students assigned to classes.
Students without classes (like Sarah).
Dynamic classes that currently have no students assigned to them.

Important Note: Not every database system supports FULL OUTER JOIN directly out of the box. For example, MySQL does not support it natively (you have to fake it using a UNION of a Left and Right Join), whereas engines like PostgreSQL do. Always verify which database system you're building for.

JOIN Cheat Sheet

If you forget everything else, keep this quick mental framework handy:

JOIN Type	What does it keep?	Quick Analogy
`INNER JOIN`	Only matching records	The intersection only
`LEFT JOIN`	Everything from the left table + matches	Left table gets full VIP treatment
`RIGHT JOIN`	Everything from the right table + matches	Right table gets full VIP treatment
`FULL OUTER JOIN`	Everything from both tables	Universal invite list

The ON Clause: Where the Magic Happens

You’ve probably noticed this piece of syntax repeating: ON students.class_id = classes.class_id.
The ON clause tells SQL exactly how the tables are related.

JOINing Three Tables

What if we want to bridge all three of our tables together to find out which teacher is teaching which student?

You aren't limited to joining just two tables at a time. You can chain JOIN statements sequentially to build broader horizons. SQL will process them linearly, using the cumulative dataset from the first join to connect to the next table.

Here is how you link students to classes, and then connect those classes to their respective teachers:

SELECT 
s.student_name,
c.class_name,
t.teacher_name,
t.subject
FROM students sINNER JOIN classes cON s.class_id = c.class_idINNER JOIN teachers tON c.teacher_id = t.teacher_id;

Pro-Tip: Table Aliasing

Notice the letters s, c, and t right after the table names? Those are aliases. They save you from having to type out long table names like students. student_name repeatedly. By declaring FROM students s, you tell SQL: "For the rest of this query, I'll just use s as a shorthand for this table." It keeps your multi-table joins beautifully organized and readable!

Surviving SQL Is Winning, Franklin

Faith Njenga — Mon, 27 Jul 2026 08:28:43 +0000

I was given a PostgreSQL assignment.

The brief said:

You are now the DBA for Greenwood Academy.

No handover meeting. No existing documentation. No senior DBA saying, “Here is what happened last quarter.”

Just a school, some messy data, and a list of tasks.

So I opened DBeaver.

For anyone new to this: I was using DBeaver to connect to PostgreSQL and run my SQL queries. PostgreSQL is the database system. DBeaver is the tool I used to work with it.

I wrote the instructions in DBeaver.

PostgreSQL did the actual work.

And, as I would soon discover, PostgreSQL was very committed to doing exactly what I told it to do.

SQL Has Departments

Before touching Greenwood Academy, I needed to understand the different types of SQL commands.

SQL commands are grouped according to what they do. Think of them as different departments in the same organisation. They all work with the database, but they have different jobs.

Category	Full Name	Main Job	Examples
DDL	Data Definition Language	Creates and changes database structures	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`
DML	Data Manipulation Language	Adds, changes, and removes data	`INSERT`, `UPDATE`, `DELETE`
DQL	Data Query Language	Retrieves data	`SELECT`
DCL	Data Control Language	Manages permissions and access	`GRANT`, `REVOKE`
TCL	Transaction Control Language	Manages database transactions	`COMMIT`, `ROLLBACK`, `SAVEPOINT`

The quick version:

DDL → Build the structure
DML → Change the data
DQL → Ask questions
DCL → Control access
TCL → Decide whether changes stay

We will not use every command in this article. Greenwood Academy did not require me to manage database permissions or recover from a transaction disaster.

This assignment focused mainly on:

DDL → CREATE, ALTER
DML → INSERT, UPDATE, DELETE
DQL → SELECT

We will also use WHERE, AND, OR, BETWEEN, IN, NOT IN, LIKE, COUNT(*), and CASE WHEN.

So, with the departments introduced, it was time to start with the construction department.

SECTION A: DDL - Building Greenwood Academy

DDL stands for Data Definition Language.

DDL is about the structure of the database.

Before asking:

Which students are in Form 3?

we need somewhere to store the students.

Before storing exam results, we need somewhere to put the results.

So the first task was to create a schema:

CREATE SCHEMA greenwood_academy;

A schema helps organise database objects. I wanted the Greenwood Academy tables grouped together instead of scattered around the database like files named:

final.sql
final2.sql
final_final.sql
final_final_use_this_one.sql

Then I set the schema as the working location:

SET search_path TO greenwood_academy;

Now PostgreSQL knew where I wanted to work.

Creating the Tables

The students table needed a unique ID, names, gender, date of birth, class, and city:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender VARCHAR(1),
    date_of_birth DATE,
    class VARCHAR(10),
    city VARCHAR(50)
);

The student_id is the PRIMARY KEY, meaning each student must have a unique identifier.

Because names are not always enough.

You can have:

Brian Ochieng
Brian Ochieng
Brian Ochieng

But their IDs should still be different.

The database does not care which Brian sits near the window. The database wants the ID.

Next came the subjects table:

CREATE TABLE subjects (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL UNIQUE,
    department VARCHAR(50),
    teacher_name VARCHAR(100),
    credits INT
);

The UNIQUE constraint on subject_name helps prevent duplicate subject names.

Because we probably do not need:

Mathematics
Mathematics
Mathematics

unless Mathematics has somehow become three different departments.

Finally, the exam results:

CREATE TABLE exam_results (
    result_id INT PRIMARY KEY,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    marks INT NOT NULL,
    exam_date DATE,
    grade VARCHAR(2)
);

Instead of repeatedly storing the student's full name and every other detail in every exam record, we use IDs.

That keeps the data more organised and saves us from trying to determine whether these are four different people:

Amina Wanjiku
Amina Wanjiku 
AMINA WANJIKU
Amina Wanjiku N.

Sometimes the database problem is not the database.

Sometimes it is the person who entered the data.

Then the Requirements Changed

Once the tables were created, someone realised the students table needed a phone number.

So we added one:

ALTER TABLE students
ADD COLUMN phone_number VARCHAR(20);

Then the credits column needed a new name:

ALTER TABLE subjects
RENAME COLUMN credits TO credit_hours;

Then the phone number was no longer needed:

ALTER TABLE students
DROP COLUMN phone_number;

Added.

Renamed.

Removed.

The database had barely settled down.

This is a realistic introduction to working with requirements. Someone says:

“We need this column.”

You add it.

Then:

“Actually, we don't need it.”

You remove it.

Then, three months later:

“Why doesn't the column exist anymore?”

This is why database work requires technical skills and the ability to remain calm while requirements perform a complete Nairobi matatu route change.

SECTION B: DML - Putting Data Into the Tables

DDL built the structure.

Now we needed data.

DML stands for Data Manipulation Language.

The main commands are:

INSERT
UPDATE
DELETE

This is where the database starts becoming useful.

It is also where you start reading your queries twice.

Maybe three times.

`INSERT`: Adding the Data

The first student:

INSERT INTO students
(student_id, first_name, last_name, gender, date_of_birth, class, city)
VALUES
(1, 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi');

Then the remaining students were inserted.

The data included students from Nairobi, Mombasa, Kisumu, Nakuru, and Eldoret.

Then came the subjects.

Then the exam results.

At this point, the database had data.

Which meant it was time for the data to be wrong.

Esther Moved From Nakuru to Nairobi

Esther Akinyi had moved from Nakuru to Nairobi.

The database still had her old city.

So:

UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

The WHERE clause tells PostgreSQL exactly which student to update.

Student number 5.

Esther.

Not everyone.

Now look at this:

UPDATE students
SET city = 'Nairobi';

That updates every student.

Amina moves to Nairobi.

Brian moves to Nairobi.

Cynthia moves to Nairobi.

Everyone moves to Nairobi.

Mombasa is finished.

Nakuru is finished.

Eldoret is now a historical concept.

This is why a missing WHERE clause can ruin your career.

The database does not stop and ask:

“Are you sure you meant all 10 students?”

It does exactly what you wrote.

Which is admirable.

Until you realise what you wrote.

The 49 That Was Actually a 59

Result number 5 had the wrong mark.

The database said 49.

The correct mark was 59.

So:

UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

One record.

One correction.

Done.

Now imagine this:

UPDATE exam_results
SET marks = 59;

Every exam result is now 59.

The student who scored 95?

59.

The student who scored 78?

59.

The student who scored 49?

Also 59.

We have not corrected the data.

We have created a very strange grading policy.

This is the moment you start appreciating the humble WHERE clause.

The Canceled Exam

Result number 9 had been canceled:

DELETE FROM exam_results
WHERE result_id = 9;

One result removed.

The dangerous version:

DELETE FROM exam_results;

That deletes every exam result.

No warning.

No popup.

No person from IT appearing beside you asking:

“What exactly are you doing?”

The database trusts you.

Perhaps too literally.

A good habit is to check the row before changing or deleting it:

SELECT *
FROM exam_results
WHERE result_id = 9;

If the correct row appears, then delete it.

The same principle applies to updates:

SELECT *
FROM students
WHERE student_id = 5;

Then:

UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

First, inspect.

Then, change.

Because fixing one student's city is a normal database task.

Explaining why the entire school now lives in Nairobi is a different kind of meeting.

SECTION C: DQL - Asking the Database Questions

Now we get to SELECT.

DQL stands for Data Query Language.

The database has the data.

We ask questions.

To find Form 4 students:

SELECT *
FROM students
WHERE class = 'Form 4';

To find subjects in the Sciences department:

SELECT *
FROM subjects
WHERE department = 'Sciences';

To find exam results with marks of 70 or above:

SELECT *
FROM exam_results
WHERE marks >= 70;

To find female students:

SELECT *
FROM students
WHERE gender = 'F';

This is the basic idea behind WHERE.

You describe what you want.

The database checks the rows.

It returns what matches.

No manually scanning through hundreds of spreadsheet rows and hoping you did not accidentally leave a filter active from yesterday.

`AND`: When Both Conditions Must Be True

The assignment asked us to find students who were in Form 3 and from Nairobi:

SELECT *
FROM students
WHERE class = 'Form 3'
AND city = 'Nairobi';

Both conditions must be true.

A Form 3 student from Kisumu does not qualify.

A Nairobi student in Form 4 does not qualify.

You can add more conditions:

SELECT *
FROM students
WHERE gender = 'F'
AND class = 'Form 3'
AND city = 'Nairobi';

Now all three conditions must be true.

`OR`: When Either Condition Works

Now we want students in Form 2 or Form 4:

SELECT *
FROM students
WHERE class = 'Form 2'
OR class = 'Form 4';

Form 2?

Include.

Form 4?

Include.

Form 3?

Not this time.

The difference is:

AND → all conditions must be true
OR  → at least one condition must be true

Two small words.

Very different results.

SECTION D: Better Ways to Filter Data

Sometimes the question is not:

Is this value exactly equal to that value?

Sometimes we need a range, a list, or a pattern.

That is where BETWEEN, IN, NOT IN, and LIKE become useful.

`BETWEEN`: Working With a Range

To find exam results between 50 and 80:

SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

This includes both boundaries:

50 → included
65 → included
80 → included
49 → excluded
81 → excluded

You could also write:

WHERE marks >= 50
AND marks <= 80;

That works.

But BETWEEN is cleaner when working with a range.

It can also be used with dates:

SELECT *
FROM exam_results
WHERE exam_date BETWEEN '2024-03-15' AND '2024-03-18';

`IN`: Checking Against a List

Suppose we want students from Nairobi, Mombasa, or Kisumu.

We could write:

WHERE city = 'Nairobi'
OR city = 'Mombasa'
OR city = 'Kisumu';

Or:

SELECT *
FROM students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

IN is useful when you have a list of acceptable values.

It is shorter, easier to read, and saves you from writing a query that looks like it is negotiating with itself.

`NOT IN`: Excluding Values

Now find students who are not in Form 2 or Form 3:

SELECT *
FROM students
WHERE class NOT IN ('Form 2', 'Form 3');

This excludes those two classes.

Everyone else remains eligible.

Sometimes the easiest way to describe what you want is to describe what you do not want.

SQL understands that too.

`LIKE`: Searching for Patterns

Suppose we want students whose first name starts with A or E:

SELECT *
FROM students
WHERE first_name LIKE 'A%'
OR first_name LIKE 'E%';

The % is a wildcard.

It means there can be anything after this.

The assignment also asked for subjects containing the word Studies:

SELECT *
FROM subjects
WHERE subject_name LIKE '%Studies%';

The % appears on both sides because the word can occur anywhere in the subject name.

This is cleaner than manually writing:

WHERE subject_name = 'Computer Studies'
OR subject_name = 'Business Studies'
OR subject_name = 'Social Studies';

and then remembering another subject later.

The wildcard does the searching.

We do not have to manually list every possible value.

SECTION E: `COUNT(*)` - Let the Database Do the Counting

How many students are currently in Form 3?

We could count them manually.

But we have a database:

SELECT COUNT(*)
FROM students
WHERE class = 'Form 3';

How many exam results have marks of 70 or above?

SELECT COUNT(*)
FROM exam_results
WHERE marks >= 70;

COUNT(*) counts the number of rows that match the condition.

This is one of those moments where using a computer is a sensible decision.

The database is already sitting there with the rows.

Let it count them.

You have other things to do.

SECTION F: `CASE WHEN` - Giving Raw Data Some Meaning

The database stores marks.

People like categories.

For example:

80 and above → Distinction
60–79        → Merit
40–59        → Pass
Below 40     → Fail

We can write:

Now the query can give us:

Marks	Performance
85	Distinction
72	Merit
59	Pass
39	Fail

The important part is that we did not change the original marks.

If the database stores:

it still stores:

The query simply creates a new label while displaying the results.

That means we can change the classification rules later without rewriting the original marks.

The raw data stays as it is.

The interpretation can change.

We can do the same thing with student classes:

A student in Form 3 is still in Form 3.

We are simply creating another way to describe that student.

No rewriting the original data.

No creating another spreadsheet.

The query handles it.

The Part I Found Most Important

The assignment covered a lot of SQL.

But the part I kept thinking about was the difference between:

UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

and:

UPDATE students
SET city = 'Nairobi';

One updates Esther.

The other relocates the entire school.

The same applies to:

DELETE FROM exam_results
WHERE result_id = 9;

versus:

DELETE FROM exam_results;

One removes a canceled exam.

The other removes every exam result.

SQL is not difficult because the database is trying to trick you.

SQL is difficult because the database is perfectly willing to do exactly what you told it to do.

Even when what you told it to do was a terrible idea.

So before running an UPDATE or DELETE, especially on important data, check what you are about to affect:

SELECT *
FROM students
WHERE student_id = 5;

Then make the change.

Same condition.

First, inspect.

Then, change.

This is not advanced database engineering.

It is simply a good habit.

And good habits are useful when you are tired, it is 2:00 AM, and somebody has just said:

“It is a very small change.”

What Greenwood Academy Taught Me About SQL

The assignment started with an empty database.

By the end, I had:

Created the greenwood_academy schema.
Created the students table.
Created the subjects table.
Created the exam_results table.
Added a phone_number column.
Renamed credits to credit_hours.
Removed the phone_number column.
Inserted student, subject, and exam result records.
Updated Esther's city from Nakuru to Nairobi.
Corrected a mark from 49 to 59.
Deleted a canceled exam result.
Filtered data using WHERE.
Combined conditions using AND and OR.
Used BETWEEN for ranges.
Used IN and NOT IN for lists.
Used LIKE with wildcards.
Counted records using COUNT(*).
Created performance labels using CASE WHEN.

That sounds like a lot.

But when broken down, SQL was asking me to do a few very direct things:

CREATE → Make something
INSERT → Put something in
UPDATE → Change something
DELETE → Remove something
SELECT → Show me something

The difficult part was not simply knowing that these commands exist.

The difficult part was being precise about what they should affect.

That is where the WHERE clause quietly becomes one of the most important things in the entire assignment.

You can learn the syntax.

You can memorise the commands.

But at some point, you have to ask:

What exactly will this query change?

That question is probably more useful than memorising another SQL keyword.

Final Thoughts

I started with an empty PostgreSQL database and a list of questions.

By the end, I had a better understanding of how the different parts fit together.

DDL built the structure.

DML put data into it and changed it.

DQL let me ask questions about it.

BETWEEN, IN, NOT IN, and LIKE made those questions more specific.

COUNT(*) handled the counting.

CASE WHEN let me add useful labels without changing the original data.

And WHERE reminded me that SQL is extremely literal.

The database will not rescue you from your own query.

It will not stop and say:

“I think you meant one row.”

It will execute.

So I have taken one practical rule away from Greenwood Academy:

Before I run an UPDATE or a DELETE, I want to know exactly which rows I am touching.

Because fixing Esther's city is a normal database task.

Moving every student in the school to Nairobi is a different kind of meeting.

And I am not trying to attend that meeting.

Surviving SQL is winning, Franklin.

Commitment Issues: Tracking Changes, Romantic Promises, and Surviving the Git Staging Crate

Faith Njenga — Sun, 12 Jul 2026 20:29:09 +0000

Introduction

Commitment is a funny thing.

Some people date for years before making it official. Some rush into relationships they regret two weeks later. And then there are those people who keep saying, “Let’s just see where this goes,” until everyone involved is emotionally exhausted.

Believe it or not, Git has the exact same personality.

If you’ve never heard of Git before, think of it as a really smart time machine for your code. It remembers every meaningful change you make, lets you revisit older versions when things go wrong, and makes it possible for multiple developers to work on the same project without accidentally overwriting each other’s work. In short, Git is a version control system and one of the most important tools you’ll use as a developer.

But Git doesn’t commit to every little change you make.

Every time you edit a file, Git looks at your changes and asks, “So… are we serious, or are we just experimenting?” If you’re not ready to commit, your changes wait patiently in the staging area,the software equivalent of “It’s complicated.” If you are ready, Git wraps everything up into a commit: a permanent promise that says, “Yes, I meant to do this.”

The problem is that most beginners are taught Git like they’re preparing for a spelling bee.

git add
git commit
git push

Memorize the commands.
Don’t ask questions.
Hope nothing catches fire.

Then, the first time they accidentally commit the wrong files, forget to stage something important, or stare at git status like it’s judging their life choices, panic sets in.

Here’s the secret: Git isn’t complicated because the commands are difficult. It’s confusing because no one explains why they exist.

So in this article, we’re going to stop memorizing magic words and start thinking like Git. We’ll open the mysterious staging crate, decide which changes deserve commitment, rescue a few questionable life decisions, and discover why version control is less about remembering commands and more about making good choices.

Because in Git-as in relationships not everything you change deserves a lifelong commitment.

Episode 1: "So... What Are We?" (`git init`)

Picture this.

You've met someone amazing. You've exchanged numbers. You've talked for hours. You've even started imagining your future together.

But then a friend asks,

"Wait... are you two actually together?"

Cue the awkward silence.

Because until someone makes it official, you're just... existing in each other's lives.

Your project has the same identity crisis.

You create a new folder. You add a few files. Maybe you've already written hundreds of lines of code. It looks like a software project, smells like a software project, and behaves like a software project.

Git, however, looks at it, shrugs, and says,

"I've literally never seen this project before."

That's where this little command changes everything.

git init

The moment you hit Enter, your project finally introduces itself.

Git quietly creates a hidden folder called .git a place you'll almost never open but one you'll rely on every single day.

Think of it as the relationship archive.

Every promise you keep (commits), every detour you take (branches), every reunion after a disagreement (merges), and every embarrassing decision you'd rather pretend never happened... Git stores it all in there.

It's basically that friend who remembers everything.

"Remember that tiny CSS change you made three weeks ago at 2:17 a.m.? Yeah... I still have it."

And if one day you decide to delete the .git folder?

Congratulations.

You didn't delete your project.

You just convinced Git that the two of you never met.

Episode 2: The Honest Bestie (`git status`)

Every relationship has that one best friend.

The one who somehow knows everything before you tell them.

"Girl... I saw what happened."

"You're leaving out the important part."

"Who exactly is this?"

They're not being nosy.

They're just trying to save you from making questionable decisions.

Git is that best friend.

Ever since you made things official with git init, Git has been quietly paying attention to your project. It isn't changing anything or making decisions for you it simply watches, remembers, and waits for your next move.

So naturally, the first thing you should ask your bestie is,

"Okay... what's the situation?"

git status

Git takes one look around your project and gives you the unfiltered truth.

"I noticed you've brought some new people into your life..."

"...care to introduce me?"

Those red filenames aren't errors.

They're untracked files files that Git has noticed but hasn't officially been introduced to yet.

Think of it this way.

You've started hanging out with new friends, but you've never brought them around your bestie. So when your best friend sees them for the first time, they're naturally like,

"Wait... who are these?"

Until you make those introductions, Git won't remember those files or include them in your project's history. If they disappear tomorrow, Git can't help you because, as far as it's concerned...

"I've never met them."

Now, let's address the one person who always shows up uninvited.

Take a look at the list again.

See .ipynb_checkpoints/?

If you use Jupyter Notebooks, you've probably met this little troublemaker. Every time you save your notebook, Jupyter quietly creates checkpoint files behind the scenes as a backup.

It's useful.

It's harmless.

But it has absolutely no business following you to GitHub.

It's like bringing your best friend to meet your family... and they show up with their cousin, their neighbor, and someone's emotional support hamster.

Nobody asked for all that.

Episode 3: "Before You Make It Official..." (`git add`)

You know what happens after you've introduced someone to your best friend?

The interrogation begins.

"So... do you actually like them?"

"Are they staying?"

"Should I bother learning their name?"

"Is this another one of your 'it's different this time' situations?"

Your bestie isn't being dramatic.

They're just trying to figure out who deserves a permanent place in your life.

Git has reached that exact moment.

In the last episode, git status showed Git everyone hanging around your project. It noticed the new files, gave you the side-eye, and asked,

"Okay... who exactly are these people?"

Now it's waiting for your answer.

This is where the staging area comes in.

Think of it as the beautifully crafted wooden crate sitting on your desk.

It's not the commitment box.

Not yet.

It's simply where you place the files you're considering committing.

Inside the crate are envelopes labelled:

Maybe later
Not ready yet

Sound familiar?

That's because staging isn't commitment.

It's intention.

It's you telling Git,

"These are the changes I'm thinking about making official."

Maybe you're confident and want to stage everything.

git add --all

Git happily picks up every new file, every modified file, and every deleted file, placing them neatly into the staging crate.

You'll also see developers use the shorter version.

git add -A

Same result.

Just fewer keystrokes.

Now suppose you're standing inside your project and simply want Git to gather everything from right here.

That's where the famous little dot comes in.

git add .

That tiny . simply means,

"Start from this folder and bring everything below it."

If you're already at your project's root directory, git add . will usually give you the same result as the previous commands.

Now imagine your bestie peeks inside the staging crate.

"Okay... these are the people you're serious about?"

You nod.

Your bestie smiles.

"Cool."

"Just remember... putting them in the crate doesn't mean you're engaged."

Exactly.

Nothing has been committed yet.

The letters are written.

The envelopes are packed.

The crate is full.

But the promise hasn't been made.

That... happens in the next episode.

Episode 4: The Plot Twist - "Wait... They're Coming Too?"

You've spent all this time deciding who belongs in the staging crate.

Your bestie helped.

The letters are neatly packed.

Everything looks perfect.

So, just before making the biggest commitment of your Git journey, you ask one last question.

"Bestie... can you check the crate one more time?"

Git happily obliges.

git status

Everything is green.

At first, you're relieved.

"Yes! We did it!"

Then your bestie squints at the list.

"...Hold on."

"Why is that coming with us?"

You lean over the crate.

Inside, alongside your Python scripts and notebooks, you spot...

A massive software installer ZIP file.
An MP3 music file.
.ipynb_checkpoints/ quietly pretending it belongs.

Awkward.

This is what happens when you stage everything without checking what's inside the crate.

It's the Git equivalent of sending wedding invitations to everyone in your contacts list...

...including your ex.

...your nail-tech.

...and the plumber who fixed your sink three years ago.

Technically, they'll all show up.

But should they?

Absolutely not.

If you commit these files and push them to GitHub, your repository becomes unnecessarily large, cloning takes longer, and anyone reviewing your project will immediately wonder,

"Did they actually mean to include all of this?"

The good news?

Git understands that we all make questionable decisions.

It even gives you a graceful way to unpack the entire staging crate without throwing away your actual files.

git rm -r --cached .

Think of this command as your best friend standing at the door collecting invitations.

"Nope... you're not coming."

"You either."

"Definitely not you."

The files stay safely on your computer.

Git simply removes them from the staging area, giving you the chance to pack the crate again but this time with a little more intention.

Because here's the thing about commitment...

Changing your mind before you make the promise is a lot easier than trying to explain yourself afterward.

Episode 5: "So... Are We Official Now?" (`git commit`)

The crate is packed.

Your bestie has checked the guest list twice.

The random ZIP file has been politely uninvited.

.ipynb_checkpoints/ finally got the hint.

Everything inside the staging crate is exactly where it should be.

Your bestie looks at you.

"Last chance."

"Are you absolutely sure these are the changes you want to keep forever?"

You take one final look.

You smile.

"Yeah... I'm ready."

This is the moment Git has been waiting for.

git commit -m "First commit"

With a single command, Git carefully closes the staging crate.

It seals every envelope with wax.

It writes today's story into its diary.

And just like that...

Your changes become part of history.

This is called a commit.

Think of a commit as a photograph.

Not of what your project will become...

...but of exactly what it looked like at this very moment.

No matter how many changes you make tomorrow, next week, or six months from now, Git can always return to this snapshot.

That's why commits are so powerful.

They're promises to your future self.

They're your way of saying,

"I know this version works."

"I know why I made these changes."

"If I ever mess things up, I can always come back here."

And before you start thinking,

"Great! My code is finally on GitHub!"

Not so fast.

Your commit lives only on your computer.

It's like writing a heartfelt love letter, sealing it in an envelope, and placing it safely inside your desk drawer.

The promise is real.

It's official.

But nobody else knows about it...

Yet.

Episode 6: The "We Need to Talk" Conversation (`git switch`, `git diff` & `git restore`)

Every healthy relationship has one.

The conversation.

Not the breakup.

Not the proposal.

The one that starts with,

"Can we talk?"

Maybe someone wants to rephrase a promise.

Maybe someone realizes they could have said something better.

Maybe they're just trying to figure things out.

The good news?

In Git, you're allowed to have those conversations without ruining the original relationship.

That's exactly what branches are for.

Think of a branch as an alternate timeline.

Instead of rewriting history, Git lets you create a safe little universe where you can experiment, make mistakes, and change your mind, all without touching your stable project.

So let's create one.

git switch -c remove_promise

Your bestie raises an eyebrow.

"So... you're telling me you're about to edit the promise?"

Relax.

We're not changing the original.

We're just exploring a "What if?" scenario.

Inside our index.html, we tweak the heartfelt letter.

Nothing dramatic.

Just enough to make Git notice.

Before doing anything else, let's ask our honest bestie what they think.

git status

Git immediately notices the change.

"I saw that."

"You changed something... but you haven't told me to remember it yet."

That's all git status is doing.

It's keeping you honest.

Now imagine you stare at your edit for a minute and suddenly think,

"What was I even trying to say?"

We've all been there.

Fortunately, Git doesn't judge questionable decisions.

It simply offers an escape route.

git restore index.html

Think of git restore as crumpling up the draft letter before anyone reads it.

The edits disappear.

Your last committed version remains untouched, as if the conversation never happened.

But let's pretend we actually like our new wording.

How do we see exactly what changed?

That's where git diff becomes your bestie's favorite gossip tool.

git diff index.html

Git opens the receipts.

No guessing.

No "I think I changed this."

Just the facts.

The red line, marked with a -, shows what you're saying goodbye to.

The green line, marked with a +, shows the new version waiting to take its place.

In our case, we upgraded the promise by adding the word "always."

One tiny word.

One huge difference.

And before we make that promise official, Git gives us one final chance to review every single change.

Because the strongest relationships and the strongest codebases aren't built by never making mistakes.

They're built by being able to see exactly what changed before making another commitment.

Episode 7: The Reunion (`git merge`)

Every couple reaches this moment.

You've had the difficult conversation.

You've rewritten the letter.

You've taken some time apart to figure things out.

Now comes the big question.

"Can we bring this back into the relationship?"

Remember, all of our edits have been living safely on a separate branch. Think of it as taking a weekend getaway to clear your head. The main branch has been patiently waiting at home, completely untouched.

Before the reunion can happen, we need to go back home.

git switch main

Your bestie smiles.

"Welcome back."

Now it's time to bring everything we worked on back into the main story.

git merge remove_promise

Git takes one look at both branches.

Then it shrugs.

"Well... that was easy."

Since nobody touched the main branch while we were away, Git doesn't have to play relationship counselor.

There are no arguments.

No awkward conversations.

No one saying,

"That's not what I meant."

Instead, Git simply moves the main branch forward until it catches up with your new commit.

This is called a fast-forward merge.

Think of it like coming back from a weekend trip.

Your family didn't repaint the house.

Nobody rearranged the furniture.

Everything is exactly where you left it.

So fitting back in is effortless.

Your upgraded promise is now part of the main story, and Git quietly records another successful chapter in its diary.

Your bestie closes the diary with a satisfied smile.

"Perfect."

"Everything is finally where it belongs."

You smile back...

Then your bestie asks one last question.

"...So, are you planning to keep this relationship a secret?"

Because right now, everything we've done exists only on your computer.

Our story is complete.

Our commits are safe.

Our branches are merged.

But the rest of the world still has no idea our project exists.

Let's change that.

Episode 8: Going Public (`git push`)

So far, our entire love story has been wonderfully... private.

We've met Git.

We've introduced the important files.

We've packed the staging crate.

We've made our commitment.

We've even survived a little relationship drama and reunited stronger than before.

But there's one tiny problem.

Everything we've done still lives only on our laptop.

It's like getting engaged...

...and forgetting to tell your family.

Before we can share our project with the world, Git needs to know where the world is.

So head over to GitHub, create a repository, and copy its URL.

Then introduce your local repository to its new home.

You'll notice we give GitHub a nickname:

origin

Think of it as saving someone's contact in your phone as "Bestie" instead of memorizing their entire phone number.

origin is simply a friendly alias for your GitHub repository, making it much easier to communicate with later.

Now comes the moment we've been building toward.

git push -u origin main

With one command, your local story leaves your laptop for the very first time.

Your commits travel to GitHub.

Your repository finally has an online home.

And your project is no longer just your story.

It's a story others can now see, clone, learn from, and contribute to.

Our relationship is officially public.

But don't get too comfortable...

Because sharing your work with the world introduces a whole new kind of drama.

Pull requests.

Remote repositories.

Authentication.

Merge conflicts.

And teammates who somehow edit the exact same line you were working on.

We'll save that tea for the next chapter.

Is It a Crime to Want a Flawless Connection? Mastering Local and Cloud Databases in Power BI

Faith Njenga — Tue, 07 Jul 2026 00:34:24 +0000

INTRODUCTION

When you first start learning Power BI, life is relatively simple. You click "Get Data," select a spreadsheet or a CSV file sitting on your desktop, and start building charts. It’s a comfortable bubble.
But as you start working on bigger projects, you quickly realize a hard truth: in the real world, most data doesn't live in loose spreadsheets on your desktop. It lives inside database servers.

The moment you try to step out of your comfort zone and connect Power BI to a database, the setup process can feel incredibly intimidating. Connecting to a database running right on your own computer is usually pretty smooth. But the second you try to connect to a secure cloud platform like Aiven PostgreSQL, Power BI will often drop a massive roadblock right in your face: The SSL Certificate Error.
Suddenly, you are staring at a scary security alert, wondering why a cloud server won't let you access your data. If you are tired of hitting connection walls and just want your tools to talk to each other, don't panic. You don't need to be a senior database engineer or a coding expert to fix this.
In this beginner-friendly guide, we are going to break down the exact, click-by-click steps to handle simple local connections and show you the ultimate Windows trick to make your computer automatically trust your cloud database.

Part 1: Moving Data into the Database (DBeaver Import)

Before Power BI can turn your data into visual charts, we need to upload our file into our database. We will use a free tool called DBeaver to act as our database manager. Think of DBeaver as a file explorer, but specifically built for databases. It lets us drag, drop, and view our tables without writing a single line of code.

Step 1.1: Logging Into Your Database

Whether your database is running quietly on your own laptop or hosted online via Aiven, DBeaver needs to know where to find it.

Open DBeaver and click the Plug Icon (New Database Connection) in the top-left corner.
Select PostgreSQL from the list of database types and click Next.
Look at your database settings (either your local installation details or your Aiven Console dashboard) and fill in the blanks:
Host: Use localhost or 127.0.0.1 for your own computer, or paste the long server link provided by Aiven.Port: Type 5432 for local setups, or 24306 for Aiven cloud instances.
Database: Type the name of your target database (the default local one is usually called postgres).
Username & Password: Type in your database login keys.
Click Test Connection. Once a little box pops up with a green success message, click Finish.

Step 1.2: Creating a Folder and Importing Your File

To keep our data organized, we don't want to dump our tables into a messy public folder. Let's create a clean, dedicated space called a Schema. In DBeaver’s left sidebar, expand your database connection.

Right-click on the word Schemas. Select Create New Schema.
Right-click your brand-new schema folder. Select Import Data. Input file and click Next.
Click the folder icon, browse your computer, and select the data file you want to upload.
Click Next through the settings, DBeaver will automatically look at your file and fit the right column structures for you. Click Proceed to execute the upload.

Part 2: The Easy Road (Connecting to a Local Database)

Now, let's open Power BI Desktop. We will start with the easiest, most stress-free connection scenario: linking up to a database running natively right on your own machine (localhost). Because this traffic never leaves your computer, Windows automatically trusts it.

Launch Power BI Desktop and open a blank report canvas.
On the top Home ribbon, click the Get Data button -> select More -> choose PostgreSQL database.
In the Server input box, type your database location using a host:port (for a local setup, this is usually localhost:5432or 127.0.0.1:5432). Type your database name in the box below it.
Choose Import as your data connectivity mode (this pulls a copy of the data into Power BI so your charts load incredibly fast) and click OK.
Switch to the Database credentials tab on the left sidebar, enter your database username and password, and click Connect.

Your tables will instantly pop up in the Navigator window, ready to be loaded into your report

Part 3: The Cloud Roadblock (Bypassing Aiven's Security Alert)

Now, try taking your long Aiven cloud host string, pasting it into that exact same Power BI connection box, and hitting connect. It will fail instantly.
Instead of seeing your tables, Power BI will flash a frustrating SSL/Security Error on your screen.
Why does this happen? Cloud databases like Aiven sit on the internet outside your home network. To protect your information, they force everything to be strictly encrypted. Power BI relies entirely on your Windows operating system to check if a cloud connection is safe. Because Windows doesn't recognize Aiven out-of-the-box, it panics and blocks the pipeline.
To break through this wall, we just need to download a digital security passport (a CA Certificate) from Aiven and slide it into your Windows security vault. Once Windows trusts it, Power BI will too.

Step 3.1: Downloading the Security Passport

Open your web browser and log into your Aiven.io console.
Click on your running PostgreSQL service page.
Scroll down to the Connection Information panel on your dashboard.
Locate the CA Certificate block and click the Download button. This saves a small file called ca.pem to your machine.
Pro-Tip: Don't leave this file in your temporary Downloads folder. Move it to a permanent folder on your computer, because Windows needs to look at this file in the background every single time your Power BI charts refresh.

Step 3.2: Slipping the Certificate into the Windows Vault

Now, we explicitly tell Windows that this cloud database is completely safe.

Click your Windows taskbar search tool, type "Manage computer certificates" (or press Win + R, type certlm.msc, and press Enter) to open the Certificate Manager.
On the left side panel, locate and expand the folder named Trusted Root Certification Authorities.
Right-click the underlying Certificates sub-folder -> Go to All Tasks -> Click Import....
Step into the wizard and click Next. When the file browser opens, change the extension dropdown in the bottom-right corner from "X.509" to "All Files (.)" otherwise, your ca.pem file will stay invisible
Select your ca.pem file, click Next through the default locations, and click Finish. Click Yes on the dramatic Windows security warning pop-up.

Part 4: The Flawless Cloud Connection

With your Windows operating system now validating the security handshake in the background, the connection barrier completely disappears.

Launch Power BI Desktop and open a blank report canvas.
On the top Home ribbon, click the Get Data button -> select More -> choose PostgreSQL database.
Input your Aiven cloud host and port details into the server field.
The Cloud Switch: Because your machine now holds the matching validation key, check the "Use encrypted connection" box.
Click OK, pivot to the Database credentials tab on the left sidebar, enter your administrative username (avnadmin), and paste your cloud service password.
Hit Connect. The data gates open instantly. The Navigator interface will open up beautifully, displaying your custom cloud schema and your loaded tables completely free of security alerts and ready to be turned into stunning dashboards.

Conclusion

Stepping up from basic flat files to structured database servers can feel like a steep learning curve, but it is one of the most valuable skills you can learn. Local connections are fantastic for practicing and testing things quickly on your own machine. But learning how to work with certificates and cloud encryption layers is what prepares you for real-world data environments. You don't need to be a coding genius to build secure pipelines, you just need to know how to configure your security lanes

Relationships Are Hard (Except in Power BI Data Modeling)

Faith Njenga — Tue, 30 Jun 2026 15:04:34 +0000

INTRODUCTION

Power BI is mainly associated with the creation of beautiful, interactive dashboards and reports, but did you know that it has more to it than that? Have a seat as I explain its immense power and what great works it can achieve.

As always, I am here to break it down to a very digestible form.

For the high performance of the reports we see, solid data models are vital. Designing a model correctly ensures fast calculation speed, accurate DAX data analysis, and easy report maintenance.

Data Schemas

schema is the structural blueprint of your data model. It dictates how your data tables are organized and connected.
Before deciding on a schema, you typically break your data into two types of tables:

Fact Tables: The Actions (The Numbers)
Think of a Fact Table as a continuous digital receipt log. It only cares about tracking business events and recording the raw numbers.
Example (Streaming App): Every time someone presses play on Netflix, it logs the event. It records Numeric Data like Duration_Minutes (45) and Pause_Count (2), alongside IDs to link to details.
The Goal: It answers "How much?" or "How many?"
Dimension Tables: The Context (The Text)A Dimension Table is your lookup list. It holds all the descriptive details that give meaning to your raw numbers.
Example (Streaming App): Instead of repeating the movie title, director, release year, and genre on every single play receipt, you store it once in a Movies lookup table next to a unique Movie_ID.
The Goal: It answers "Who?", "What?", or "Where?"

Choosing your Blueprint(Schema)

Now that you have known your Facts from your Dimensions, how do you arrange them in Power BI?
There are 3 schema types. Namely:

Star
Snowflake

Star Schema

The Star schema gets its name because of its shape. You place the number-heavy Fact Table in the center and surround it with your descriptive Dimension Tables like the points of a star.

Why use it: It provides the best query performance, reduces data redundancy, and uses one-to-many relationships that are easy for Power BI to process.

Snowflake Schema

The Snowflake Schema happens when you split your dimension tables into even smaller lookup tables(e.g., a "Product" table branching out into separate "Brand" and "Category" tables).

Why use it: It is useful when you need to optimize storage or map highly complex hierarchical relationships, though it can slow down report rendering.

Flat Schema

This represents a single, massive, consolidated table where all information lives together. This happens automatically when you load a single CSV or flat Excel file into Power BI.

Why use it: It requires no modeling effort and is perfectly fine for small, simple datasets. However, it leads to heavy data redundancy and poor performance on large datasets. Here is a visual representation of the three.

Relationships

The 3 Core Settings of a Relationship
Every relationship you build in Power BI is defined by three main settings:

1. Cardinality (The Type of Connection)

One-to-Many (1:*): The most common and efficient type. One row in your lookup table (e.g., Customers) connects to many rows in your transaction table (e.g., Sales).
Many-to-One (*:1): The same as one-to-many, just viewed from the opposite direction.
One-to-One (1:1): Links single unique rows between two tables. It is rarely used and usually means the tables should just be merged.
Many-to-Many (:): Links non-unique values on both sides. It should be avoided when possible because it can cause unpredictable calculations.

2. Cross Filter Direction (How the Data Flows)

Single Direction: The filter flows only one way—usually from the Dimension table down to the Fact table. This is the safest and fastest setting.
Both Directions (Bi-directional): Filters flow both ways. While powerful, it can severely slow down your reports and create ambiguous data paths.

3. Relationship Status

Active: The primary path Power BI uses to calculate data between two tables. Only one active relationship can exist between any two tables at a time.
Inactive: Secondary paths. They sit in the background and are only triggered when you specifically call them using DAX formulas (like USERELATIONSHIP). Active vs. Inactive Example If your Sales table has a Order_Date and a Ship_Date, you can only link one of them actively to your Calendar table. You would make Order_Date active for your daily sales reports, and keep Ship_Date inactive, activating it only when calculating shipping backlogs.

Power Query Joins: Merging Tables

In Power BI, joins are used to merge two tables permanently into a single, wider table during the data preparation phase.

While relationships connect separate tables in your visual model, joins physically combine your data rows in the Power Query Editor using the Merge Queries feature.

The 6 Types of Joins in Power BI

Power BI offers six different ways to combine your datasets based on a matching column:

Left Outer: Keeps all rows from the first table, and only matching rows from the second table. This is the most common join.
Right Outer: Keeps all rows from the second table, and only matching rows from the first table.
Full Outer(The collector): Keeps all rows from both tables. Unmatched rows display null values for the missing data.
Inner(The matchmaker): Keeps only rows that match in both tables. Any unmatched data is completely discarded.
Left Anti: Keeps rows only present in the first table that do not have a match in the second. It is perfect for finding missing records.
Right Anti: Keeps rows only present in the second table that do not have a match in the first.

Quick Comparison Matrix

Join Type	Records Kept from Table 1 (Left)	Records Kept from Table 2 (Right)	Best Used For
Left Outer	All	Only Matches	Adding descriptive columns to transactions
Right Outer	Only Matches	All	Inverting data priorities
Full Outer	All	All	Combining disparate budgets or targets
Inner	Only Matches	Only Matches	Filtering for strict overlap
Left Anti	Only Unmatched	None	Finding customers who haven't bought yet
Right Anti	None	Only Unmatched	Finding orphaned data in secondary tables

Join vs. Relationship: What's the difference?

It is common to confuse Power Query joins with Data Model relationships. Here is how to differentiate them:

Joins (Power Query): Happen during data ingestion. They alter the physical structure of your data by flattening tables together, which consumes more memory.
Relationships (Data Model): Happen after the data is loaded. They keep tables separate and link them virtually, maximizing Power BI’s performance engine.

Into the Bashlands: Surviving the Big Black Screen Without an Undo Button

Faith Njenga — Mon, 29 Jun 2026 21:44:39 +0000

Introduction

It's not talked about enough: the fear of the terminal window, or in other words, the big black screen.
Where there are no folder icons to double click on, their creation is not just a right-click, no buttons to undo mistakes and a single mistake can trigger an error message that looks like a loud, red alarm. Well, I want you to take a deep breath. The creeping of a sudden wave of imposter syndrome might not be news.
You are not alone in this
I decided to dive into the unknown and become a Data Engineer. If you do not know Linux in this line of Duty is a very crucial tool. Just like you I always thought that the command line was reserved for a certain category that I like call 'Big Boys' also known as cybersecurity experts or people who have memorized every shortcut since 1995. But over the last few weeks I have decided to get my hands dirty with real-world server deployment and yes, I can attest that: The terminal is not a monster. It is just a conversation.
Every time the terminal throws a scary Fatal error to you, it is not judging you but simply pointing out a missing configuration step in plain text. In this article, I am going to take my time and explain the nitty-gritty of what I learned in setting up a remote cloud database from scratch. We will look at my exact real-world configuration, demystify database permissions and show how visual tools like Dbeaver can come to the rescue all these in simple and friendly terms.

Remote Access and Navigating

Because a remote cloud server does not have a monitor or a keyboard attached to it in a physical room, we have to access its brain over the internet. The tool we use is SSH(Secure Shell). This of SSH as a secure, encrypted digital tunnel that stretches from your home laptop keyboard straight into the host operating system.
For my recent project, our remote Ubuntu cloud server was hosted at the public IP address 159.65.222.96

The login Banner
When you first connect via SSH using the administrative root user account, the server greets you with a system status banner:

Don't let this numbers overwhelm you. Look at them as a health dashboard for your remote computer.

usage of /: 4.2% means your server's storage drive is mostly empty. Your data pipelines have plenty of room to breathe.
Memory usage:28% tells you that more that two-thirds of your server's RAM is sitting open and ready to process heavy computational tasks.

Creating an Isolated User & The privilege shift

To practice good security habits and protect our environment, we need to move away from the root account. I created my own isolated user account (faithn) using standard Linux configuration tools.
Here is exactly how I built that user account and gave it administrative sudo privileges while logged in as root:

#1. Create the new user account (The system will prompt you to set a password)
root@assignmentServer:~# adduser faithn

#2. Grant the new user access to administrative powers ('sudo' group)
root@assignmentServer:~# usermod -aG sudo faithn

Securely switch over to the new personal account by nesting a new connection

Look at that prompt change:
'faithn@assignmentServer:~$'.The dollar sign($) means I am now standing safely inside my personal use sandbox. I have my own room, my own security, and I can't accidentally break the rest of the server.

Finding Your Way Around Without a Mouse

Now that we are inside our isolated faithn user account, how do we look around? Because you cannot see any folders or icons, you have to use your keyboard as your eyes. Here are some commands that helped me navigate my new digital apartment:

pwd (Print Working Directory): Tells you exactly where you are standing. The exact location where you are at.

ls -la (List All): This prints out every file and folder in your current directory, including hidden system files that control your settings.

mkdir (Make Directory)- This is equivalent of right-clicking on a desktop screen and selecting New> Folder.
cd (Change Directory)- This is the physical act of walking into a folder.

touch- This instantly lays blank sheets of paper on your desk. It is a fresh text file ready for configuration details or pipeline scripts.

Unmasking the PostgreSQL Prompt

The next major conceptual hurdle for any beginner data engineer is understanding Context Switching.

Once, I confirmed that our database software is installed by running

It was time to log in.

The right way to Login In

Once your user roles and permissions are properly set up the server, logging into your personal database should be clean and direct. You run this command into your regular Linux terminal prompt:

If everything is configured correctly, the server will bypass all administrative barriers and drop you straight into your destination. You will see a clean welcome message and responsive prompt ready for your data queries:

psql (16.14 (Ubuntu 16.14-0ubuntu0.24.04.1))
Type "help" for help.

faithn=#

The First Trap: Getting Stuck in the Prompts

However, when you are first figuring things out, it is incredibly easy to take a wrong turn and find yourself stuck like I was.
I tried to log straight into my database but I instead I fell directly into a classic terminal context trap that puzzles almost every single beginner:

Let's dissect this line by line, because understanding this sequence will instantly remove your fear of database environments.

Decoding the Prompt Symbols ($, #, and -)

The trailing symbols on your prompt lines are visual road signs showing which program is listening to your keyboard :
faithn@assignmentServer:~$ The $ means you are in the Linux Shell. You run standard system commands here like ls or mkdir.
postgres=#The # means you crossed a portal into the PostgreSQL Engine. Linux commands will not work here; the server only accepts SQL code.
postgres-# The dash (-) means PostgreSQL is waiting for you to finish a SQL statement. Typing psql twice triggers this because it isn't a valid SQL command, and the engine thinks you are still typing your sentence.
The Fix: If you ever get trapped on a line with a dash (-#) and your keyboard feels locked, press Ctrl + C. This breaks the waiting loop and returns you to a clean postgres=# prompt.

The Second Trap: The "Role Does Not Exist" Block

Once you exit the prompt loops and confidently try to log into your database using your custom name:

bash/usr/lib/postgresql/16/bin/psql -d faithn

PostgreSQL flatly rejects you with a fatal message:
FATAL: role "faithn" does not exist
This error makes your stomach drop. You think, "But wait, My Linux username is faithn, and I am typing it correctly! Why does it say I don't exist?"

Breaking Down the Trap

This trap happens because Linux users and Database users are completely isolated from each other. Just because the server has a user profile named faithn doesn't mean the PostgreSQL application automatically knows who you are. PostgreSQL tracks its own separate guest list of users, which it calls Roles.
Think of your Linux account as your security badge to get into the main university building. The Database Role is a completely separate guest list held by a second guard standing right outside the locked database room. When you first try to connect, that second guard checks his specific list, doesn't see your name, and locks you out,.
The Clean Solution
To fix this trap, we use the Master Database Admin identity (postgres=#) to officially add your name to the database guest list and create your file storage container:

sql
CREATE ROLE faithn WITH LOGIN SUPERUSER;
CREATE DATABASE faithn OWNER faithn;

By explicitly running these, the two separate systems are perfectly linked. Now when you type sudo -u postgres psql faithn, the database guard sees your name on the list and lets you right in.

Creating the Schema and Importing Data with DBeaver

Now that our database boundaries are unlocked, we can construct our ingestion environment. In data engineering, we use a Schema as a logical container to organize our tables. For this project, we are building a landing bucket called staging.
We log into our database and run a simple, clean SQL script to lay down our structure:

Bypassing Terminal Fatigue

At this point, a classic beginner trap is trying to write massive INSERT INTO statements directly in the command line for dozens of records. It is a massive waste of time, and a single missing comma will break the entire thing. Real-world data engineers don't do everything the hard way, we balance terminal power with graphical comfort. This is the exact moment where DBeaver becomes your ultimate rescue tool. Because we configured our remote cloud server (159.65.222.96) to allow external network connections on port 5432, we can hook DBeaver directly into our backend using our personal faithn credentials. Instead of typing endless lines of code to force our data into the black box of the terminal, DBeaver peels back the scary layer of the command line and gives us a friendly, visual import wizard. I simply selected the mock student CSV spreadsheet generated on my local desktop, clicked through the visual mapping guide, and let the software automatically parse and load all records into our remote cloud database in seconds.

It gives you the absolute best of both worlds: your data is safely running on an enterprise Linux server in the cloud, but you get to interact with it using a beautiful, visual, human-friendly application.
To verify that DBeaver successfully uploaded your records from your local PC to your remote cloud database, you can switch back to your Linux server terminal (faithn@assignmentServer) and run this standard SQL query:

SELECT * FROM staging.students LIMIT 10;

What You Will See on Your Screen

Running that command will print your data cleanly inside your terminal layout, showing you the exact rows you just uploaded through DBeaver:

age |      course      | admission_number | grade | student_id |   gender   |    major    
-----+------------------+------------------+-------+------------+------------+-------------
  22 | Computer Science |      4083        | F     |        1   | Male       | Physics     
  22 | Business         |      7444        | F     |        2   | Female     | Physics     
  18 | Biology          |      4103        | C     |        3   | Female     | Physics     
  26 | Biology          |      7590        | B     |        4   | Female     | Chemistry   
  30 | Psychology       |      1978        | F     |        5   | Female     | Chemistry   
  30 | Biology          |      9396        | C     |        6   | Female     | English     
  24 | Biology          |      5989        | C     |        7   | Non-binary | Mathematics 
  25 | Biology          |      8362        | B     |        8   | Non-binary | Physics     
  25 | Business         |      3275        | D     |        9   | Non-binary | Chemistry   
  21 | Business         |      6182        | B     |       10   | Female     | Physics

DEV Community: Faith Njenga

SQL Is Surviving, Franklin: Now Rows Are Competing

First, What Problem Are We Actually Solving?

Meet OVER(): The Part That Makes the Window

PARTITION BY: Franklin, Pick Your Group

ORDER BY: Now Franklin Needs an Order

Ranking: Apparently, Everyone Needs a Position

ROW_NUMBER(): Everyone Gets a Number

RANK(): Ties Are Allowed

DENSE_RANK(): Ties Without the Gap

LAG() and LEAD(): Looking Behind and Ahead

Running Totals: When SUM() Gets a Memory

Meet the Window Frame: Franklin, We Need Boundaries

CURRENT ROW: Where Am I?

PRECEDING: Look Behind Me

FOLLOWING: Look Ahead

ROWS BETWEEN: Tell SQL Exactly Where to Look

UNBOUNDED PRECEDING: Start From the Beginning

UNBOUNDED FOLLOWING: Go All the Way to the End

Franklin's Window Frame Dictionary

WHERE Comes First: Sorry, Window Functions

Can Window Functions Be Nested?

Can Window Functions Work With GROUP BY?

Franklin's Window Function Survival Guide

Need to keep individual rows?

Need calculations within groups?

Need the rows considered in a particular order?

Need rankings?

Need the previous row?

Need the next row?

Need a running total?

Need a moving calculation?

Need to look backward?

Need to look forward?

Need to start from the first row?

Need to go to the last row?

Need to filter a window-function result?

Need one window function to use another window function's result?

So, What Exactly Did We Survive?

What's Next?

SQL Is Surviving, Franklin: When One Query Isn't Enough

Previously in SQL...

So... What Is a Subquery?

scores

Our First Subquery

How Does SQL Execute This?

Step 1

Step 2

Step 3

Subqueries in WHERE

The Inner Query vs The Outer Query

Subqueries With IN

students

classes

IN vs =

Subqueries in FROM

Why Would We Do That?

Subqueries in SELECT

Then Things Get Complicated

Enter: The CTE

Breaking Down a CTE

CTEs Are Like Giving Your Query Steps

Multiple CTEs

Multiple CTEs Can Build on Each Other

Subquery vs CTE

Use a subquery when:

Use a CTE when:

A Realistic Greenwood Academy Problem

Step 1: Calculate each student's average

Step 2: Calculate the overall average

What About Correlated Subqueries?

employees

Why Correlated Subqueries Matter

Don't Use a CTE Just Because You Can

Common Subquery Mistakes

1. Returning multiple rows when one value is expected

2. Forgetting the alias

3. Making the query impossible to read

4. Forgetting what your subquery returns

Subqueries vs JOINs

Meet `OVER()`: The Part That Makes the Window

`PARTITION BY`: Franklin, Pick Your Group

`ORDER BY`: Now Franklin Needs an Order

`ROW_NUMBER()`: Everyone Gets a Number

`RANK()`: Ties Are Allowed

`DENSE_RANK()`: Ties Without the Gap

`LAG()` and `LEAD()`: Looking Behind and Ahead

Running Totals: When `SUM()` Gets a Memory

`CURRENT ROW`: Where Am I?

`PRECEDING`: Look Behind Me

`FOLLOWING`: Look Ahead

`ROWS BETWEEN`: Tell SQL Exactly Where to Look

`UNBOUNDED PRECEDING`: Start From the Beginning

`UNBOUNDED FOLLOWING`: Go All the Way to the End

`WHERE` Comes First: Sorry, Window Functions

Can Window Functions Work With `GROUP BY`?

`scores`

Subqueries in `WHERE`

Subqueries With `IN`

`students`

`classes`

`IN` vs `=`

Subqueries in `FROM`

Subqueries in `SELECT`

`employees`

`IN`

`students`

`classes`

`teachers`

`INSERT`: Adding the Data

`AND`: When Both Conditions Must Be True

`OR`: When Either Condition Works

`BETWEEN`: Working With a Range

`IN`: Checking Against a List

`NOT IN`: Excluding Values

`LIKE`: Searching for Patterns

SECTION E: `COUNT(*)` - Let the Database Do the Counting

SECTION F: `CASE WHEN` - Giving Raw Data Some Meaning

Episode 1: "So... What Are We?" (`git init`)

Episode 2: The Honest Bestie (`git status`)

Episode 3: "Before You Make It Official..." (`git add`)

Episode 5: "So... Are We Official Now?" (`git commit`)

Episode 6: The "We Need to Talk" Conversation (`git switch`, `git diff` & `git restore`)

Episode 7: The Reunion (`git merge`)

Episode 8: Going Public (`git push`)