DEV Community: Reinhard Bonnke

SQL Building Blocks: How Subqueries and CTEs Shape Your Data

Reinhard Bonnke — Mon, 27 Apr 2026 22:04:46 +0000

Unless you are new to SQL, at some point you have written a query that begins to resemble a puzzle box query, query within query, difficult to read, more difficult to debug. CTEs fix that.

So, What is a CTE?

CTE is an abbreviation that means Common Table Expression. It is simply a means of assigning value to a query in order to be able to employ it later in the same statement like a table.
It is the keyword WITH.

What is the point of using one?

Readability. Your SQL is top down rather than inwards.
Reusability. You need not rewrite the query, you can refer to the same CTE more than once.
Easier debugging. You can even do CTE alone which would check what it is returning.

What is the Difference Between CTEs and Subqueries?

The two are similar in that they allow you to work on a derived result set. But they feel very different to write and read.
The following query expressed in either direction:
Subquery:

There are one thing subqueries that can do but not CTEs.
A correlated sub query is able to reference the outer query. CTEs can't do that.

Here, the e.department_id is the reference that is correlatedand recalculated on a per row basis. CTEs are determined in advance of the main query, and thus can do nothing to do this.

Side-by-Side Comparison

	CTE	Subquery
Readability	Reads top to bottom	Nested, harder to follow
Reusability	Reference it multiple times	Have to rewrite it each time
Recursion	✅ Supported	❌ Not possible
Correlated queries	❌ Can't reference outer query	✅ Can reference outer query
Works everywhere	All modern databases	Universal

Subqueries can appear in 3 different Clauses

In SELECT: adds a computed column. Runs once per row, so can be slow on large data.
In FROM: creates a temporary table. Always give it an alias.
In WHERE: filters based on another query's result.

When to Use Which:

Use a CTE when:
There are several steps to your query.
You want your SQL serving as easy to read and maintain.
You must cite the same finding more than twice.

Use a sub query in circumstances where:
It is a fast and easy filter.
You must have a correlated query (which cites the outer query).
You are filtering in a WHERE or HAVING statement.

Reflection

The CTEs and subqueries were indeed one of the more difficult things I have learned so far. Originally, the syntax was foreign particularly where each segment resides and why. Queries that contained a subquery and nested queries were especially difficult to reason and read. CTEs simplified it because it dawned on me that you are just giving a query a name and calling it a table. The most difficult was not writing the queries but understanding which tool to consult and why. It was beginning to trade sense after I had practiced and had gone through examples. They are a work in progress, but the comprehension is approaching.

SQL Basics: DDL, DML, Filtering & Data Transformation

Reinhard Bonnke — Mon, 13 Apr 2026 10:41:11 +0000

SQL is a standard language that is used to communicate with relational databases. In simple terms, it is the way in which you speak to a database to get data, write new data, alter existing data, delete data, grant access, and determine the structure of your database.
SQL statements can be classified into groups of what they do:

Category	Purpose
DQL – Data Query Language	Retrieve data
DML – Data Manipulation Language	Modify data
DDL – Data Definition Language	Define database structure
DCL – Data Control Language	Control access
TCL – Transaction Control Language	Manage transactions

This week, I focused on two of these: DDL and DML.
What’s the Difference Between DDL and DML?
DDL is about structure where, it defines and sets up the database itself. Think of it as building a house before moving the furniture in.
DML is about data; It is what you use once the structure exists to add, change, or remove information inside it.

	DDL	DML
What it does	Defines structure	Modifies data
Commands	Create, Drop, Alter, Rename, Truncate	Insert, Update, Delete
Affects	Tables and Schemas	Rows/Records

How I Used DDL and DML in My Assignment
CREATE: Building the Table (DDL)
The first thing I did was create a table to hold the data. CREATE sets up the table and defines what columns it will have and what type of data each column accepts.
For example, after having created my schema, I set the path to the name of my current schema and then proceeded to create the table using command ‘Create.’

INSERT: Adding Data (DML)
Once the table existed, I populated it with records using INSERT.

UPDATE: Editing Existing Records (DML)
UPDATE lets you change data that is already in the table without deleting and re-entering everything.
For example, changing the City to Nairobi having made an entry of City as Mombasa;

DELETE: Removing Records (DML)
DELETE removes specific rows from the table. Used carefully, it's a clean way to get rid of records you no longer need.

Filtering with WHERE
The WHERE clause lets you target specific rows instead of affecting or retrieving everything. Here are some common operators I used:

Operator	What it does	Example
`=`	Exact match	`WHERE city = 'Nairobi'`
`>`	Greater than	`WHERE marks > 70`
`BETWEEN`	Within a range	`WHERE marks BETWEEN 50 AND 80`
`IN`	Matches a list	`WHERE status IN ('Active', 'Pending')`
`LIKE`	Partial match	`WHERE name LIKE 'A%'`

For example, take a look of the following;

CASE WHEN: Transforming Data on the Fly
CASE WHEN works like an IF statement. It lets you create a new column based on conditions, without changing the original data.
For example, you can label students as "Senior" or “Junior” based on their class right inside your query.

My Reflection
The section which was of most interest to me was CASE WHEN. It was as though SQL had ceased to be simply about data storage, and began to become about processing that data. You are actually making choices within a query that seems strong considering something so simplistic.
I was challenged by getting WHERE conditions correct which were particularly the combination of multiple operators. Even a minor syntax error, a lost quote, an incorrect operator and the entire query fails. However, that is what made it satisfying as well when it did finally work.
SQL is beginning to disintegrate into a programming language and more of a logic of the data.

How Excel is Used in Real-World Data Analysis

Reinhard Bonnke — Fri, 27 Mar 2026 13:52:13 +0000

How Excel is Used in Real-World Data Analysis

We all have heard the word Excel a lot in job descriptions, co-workers and maybe even in your data course. But what does it actually do? And how come that so many data-related positions trust in it?

Let us reduce it down to simple, practical and real illustrations

What is Excel?

Excel is a spreadsheet program provided by Microsoft. Consider it as a huge, intelligent table within which it is possible to store data, calculate and identify patterns, and construct reports all in a single table.
Accountants, analysts, and marketers as well as HR teams and any person, who has to deal with numbers and lists on a regular basis use it.

The Excel Interface: What you are looking at.

The view you will get on opening excel is as follows:

Ribbon: The toolbar at the top. Everything lives here: formatting, formulas, charts, etc.
Formula Bar: Shows what's inside the cell you've selected.
Name Box: Tells you which cell is selected (e.g., A1).
Columns, Rows & Cells: The building blocks of your spreadsheet
Worksheet Tabs: Different sheets within the same file.

The 3 Types of Data in Excel:

Before you do anything, you need to understand what kind of data you're working with:

Data Type	Examples	Alignment
Text	"John", "Nairobi"	Left
Dates	01/01/2024	Right
Numbers	100, 35, 3.5	Right

The alignment detail is significant, a fast method to check if something has been typed in wrong (e.g. a date appearing left-aligned is actually a text, not a date).

Data Cleaning

Step 1: Cleaning the Data First

Real-world data is messy. You clean it before it is analyzed. This is referred to as data cleaning, involcing repairing errors, blanks as well as inconsistencies in order to make data actually useful.

Here is what that looks like in practice:

Eliminate duplicates: Excel has got an inbuilt option.

Handling missing values: In some cases, it is good to delete a row or column of more than 60% of the data is missing; or replace it with mean/mode/median. Also, you can replace it with “Unknown” or “Not Provided.”

Correct errors: n case one row contains the word nairobi and another contains Nairobi, excel considers them different. Standardize with Find and replace or text tools.
Eliminate blatant mistakes like when the majority of a column are 50-80 and one reflects a 2 it is worth examining rather than trying to put in your analysis.

Step 2: Sorting & Filtering

After data is clean, you should then find your way through it. Filtering and sorting allow you to narrow down on what is important.
Sort numbers smallest → largest, dates oldest → newest, or text A → Z
Filter to show only rows that match a condition (e.g., only sales from Q1, only customers from Nairobi)

Step 3: Using Functions

This is where Excel gets powerful. Functions are built-in formulas that do the heavy lifting for you.

Basic Math Function

=SUM(B2:B10) - Adds up a range
=AVERAGE(B2:B10) - Finds the average
=MAX(B2:B10) - Finds the highest value
=MIN(B2:B10) - Finds the lowest value
=COUNT(B2:B10) - Counts how many entries exist
=COUNTIF(B2:B10, ">50") - Counts entries above 50

Text Functions (Super useful for cleaning)

=UPPER("nairobi") - NAIROBI
=LOWER("NAIROBI") - nairobi
=PROPER("nairobi") - Nairobi
=TRIM(" hello ") - hello (removes extra spaces)
=LEN("hello") - 5 (counts characters)

Date Functions

=TODAY() - Today's date
=YEAR(A2) - Extracts the year from a date
=DAY(A2) - Extracts the day
=DATEDIF(A2,B2,"D") - Number of days between two dates

Logical Functions

=IF(C2>50, "Pass", "Fail") - Simple condition
=IF(AND(C2>50, D2="Paid"), "OK", "Check") - Multiple conditions

Lookup Functions

VLOOKUP lets you search for a value in one column and pull related data from another. Think of it like a search engine within your spreadsheet.

Step 4: Conditional Formatting

Conditional formatting changes how a cell looks based on its value. For example, cells below a threshold turn red, top performers turn green.
The goal is simple: see data, understand it, act on it, without staring at rows of numbers. For example, look at the column below and spot the difference;

Pivot Tables

An overview of massive data can be created in a few seconds by a Pivot Table. You do not need to write up complicated formulas, you simply use your mouse and move around fields to group, count or add data.
For example: You are having 500 rows of sales knowledge. You can immediately see the total sales by region, sales by month or product without even data entry into a formula with a Pivot table.

Take a look at this:

Charts: Telling the Story Visually

Numbers make sense to analysts. Charts make sense to everyone else.

Common chart types:

Bar/Column: Compare categories
Line: Show trends over time
Pie: Show proportions (use sparingly)

Here is an example of a Pie chart:

Also, here is an example of Bar Graph:

My Personal Reflection

Honestly, prior to learning Excel, I believed that the process of data analysis happened in dreamy software with incomprehensible code. Excel transformed that presumption within a short period.
I was most shocked by the extent to which you can accomplish without analysis having begun, simple cleaning of data, error checks, harmonization of formats. That was something that did not seem to me before, and now I notice it everywhere. And once a report or a spreadsheet is presented I unconsciously begin to ask myself: Is the data clean? Are there missing values? Does this make sense?
The learning process similar to those of COUNTIF, IF and VLOOKUP also changed my thinking pattern.
The most significant thing that Excel has provided me is that mental change of going to do things manually instead of thinking in a systematic manner.
It is not just a tool. It is a mental process towards information!