DEV Community: Shadrack Tesot

Building a School Database from Scratch: A Practical Introduction to SQL

Shadrack Tesot — Sun, 12 Apr 2026 21:03:37 +0000

Building a school database from scratch

There’s a certain kind of satisfaction that comes from building something structured out of nothing. No shortcuts, no pre-built templates, just a blank database and a problem to solve.

That was the starting point for this project: designing and managing a database for Nairobi Academy, a secondary school. What began as a simple assignment quickly turned into a practical lesson in how data systems are built, maintained, and questioned.

The Two Sides of SQL: Structure vs Action

Every SQL workflow sits on two pillars: DDL (Data Definition Language) and DML (Data Manipulation Language).

DDL is about structure. It is where you define the blueprint by creating schemas, tables, and columns.

For example:

CREATE SCHEMA nairobi_academy;

USE nairobi_academy;

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

DML is about action. Once the structure is in place, DML handles everything that happens inside it.

INSERT INTO students (student_id, first_name, last_name, gender, class, city)
VALUES (1, 'Alice', 'Wanjiku', 'F', 'Form 4', 'Nairobi');

The distinction becomes clearer in practice:

DDL defines the system
DML works with the data inside it

Building the System and Adjusting It Along the Way

Creating the tables with CREATE felt straightforward at first. But once requirements started shifting, things became more realistic.

At one point, I had to modify the structure:

ALTER TABLE students
ADD phone_number VARCHAR(20);

Then later:

ALTER TABLE subjects
RENAME COLUMN credits TO credit_hours;

And eventually:

ALTER TABLE students
DROP COLUMN phone_number;

This back-and-forth made it clear that databases are not static. They evolve with changing needs.

Bringing Data to Life

With the structure in place, the next step was filling it.

INSERT INTO exam_results (result_id, student_id, subject_id, marks, exam_date)
VALUES (1, 1, 101, 78, '2024-03-15');

Once data was in place, I needed to maintain it.

Updating a student’s record:

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

Fixing incorrect marks:

UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

Removing a cancelled record:

DELETE FROM exam_results
WHERE result_id = 9;

These operations made the database feel dynamic. Data wasn’t just stored, it was actively managed.

Filtering Data with WHERE

The WHERE clause is where SQL becomes more than storage. It becomes a tool for asking focused questions.

For example, finding all Form 4 students:

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

Finding high-performing students:

SELECT *
FROM exam_results
WHERE marks >= 70;

Using ranges:

SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

Using multiple values:

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

Using patterns:

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

At this stage, SQL starts to feel less like writing commands and more like working through logic.

Turning Data into Meaning with CASE WHEN

Raw data gives numbers, but interpretation gives insight.

Using CASE WHEN, I transformed exam results into performance categories:

SELECT student_id, marks,
       CASE 
           WHEN marks >= 80 THEN 'Distinction'
           WHEN marks >= 60 THEN 'Merit'
           WHEN marks >= 40 THEN 'Pass'
           ELSE 'Fail'
       END AS performance
FROM exam_results;

And classified students:

SELECT first_name, last_name, class,
       CASE
           WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
           ELSE 'Junior'
       END AS student_level
FROM students;

This made the data easier to interpret and more useful for decision-making.

Reflection

The most challenging part of this assignment was not the syntax. It was the thinking behind it.

Combining conditions correctly took practice. Small mistakes could completely change the output. Remembering to always use WHERE with UPDATE and DELETE was especially important.

What stood out the most was how quickly SQL shifts from technical to analytical. Once the basics are in place, it becomes less about writing queries and more about asking better questions.

Final Thoughts

This project started as a database exercise but turned into something broader.

Building tables, inserting data, and running queries are all important steps. But the real value comes from understanding how everything connects. Structure supports data, and data supports decisions.

SQL, at its core, is not just about managing information. It is about making that information useful.

Understanding Data Modeling in Power BI: Joins, Relationships, and Schemas Explained

Shadrack Tesot — Sun, 29 Mar 2026 20:16:49 +0000

When I started learning data analysis, I thought Power BI was mostly about dashboards and visuals. Clean charts, nice colors, maybe a few filters.

Then I hit data modeling.

That’s when things got real.

Because no matter how good your visuals look, if your data model is wrong, your insights will be wrong too. And in the real world wrong insights cost money.

What is Data Modeling?

Data modeling is how you structure, connect, and organize your data so that it can be analyzed correctly and efficiently.

In Power BI, data modeling happens after loading your data, and it determines:

How tables relate to each other
How filters behave
How calculations work

SQL Joins (The Foundation of Data Combination)

Before Power BI relationships, you need to understand joins. These are done in Power Query.

1. INNER JOIN

Returns only matching records in both tables.

Example:

Orders table
Customers table → Only customers who have placed orders

2. LEFT JOIN

Returns all records from the left table, and matching ones from the right.

Example:
All orders, even if customer details are missing:**

3. RIGHT JOIN

Same as LEFT JOIN but reversed. Returns all records from the right table, and matching ones from the right.

Example:
All customers, even if they haven’t placed orders.

4. FULL OUTER JOIN

Returns everything from both tables.

Example:

All customers
All orders
Whether they match or not

5. LEFT ANTI JOIN

Returns rows in the left table that don’t exist in the right.

Example:
Orders without matching customers.

6. RIGHT ANTI JOIN

Returns rows in the right table that don’t exist in the left.

Where to Create Joins in Power BI

Go to Home → Transform Data (opens Power Query)
Select your table
Click Merge Queries
Choose:

Second table
Matching column
Join type (Inner, Left, etc.)
1. Expand the merged column

That’s how joins are actually done.

Power BI Relationships

This is where most beginners get confused.

Joins combine tables into one.
Relationships keep tables separate but connected.

Types of Relationships

1. One-to-Many (1:M)

Most common.

Example:

Customers (1)
Orders (Many)

One customer → many orders

2. Many-to-Many (M:M)

Both sides have duplicates.

Example:

Students and Courses

3. One-to-One (1:1)

Rare.

Used when splitting data for performance or organization.

Active vs Inactive Relationships

Active → used by default
Inactive → only used when explicitly called (via DAX like USERELATIONSHIP)

Example:
Order Date vs Delivery Date
Only one can be active at a time

Cardinality

Defines how tables relate:

Cross Filter Direction

Controls how filters move between tables.

Single direction → safer, recommended
Both directions → more flexible, but risky if misused

Where to Create Relationships in Power BI

Method 1: Model View

Go to Model View (diagram icon)
Drag one column onto another
Set:

Cardinality
Cross-filter direction
Active/inactive

Method 2: Manage Relationships

Go to Home → Manage Relationships
Click New
Select tables and columns
Configure settings

Joins vs Relationships (Critical Difference)

Joins	Relationships
Done in Power Query	Done in Model View
Combine tables	Keep tables separate
Static	Dynamic
Used for shaping data	Used for analysis

Simple way to remember:

Joins = data preparation
Relationships = data analysis

Fact vs Dimension Tables

This is the backbone of good data models.

Fact Tables

Contain measurable data.

Examples:

Sales amount
Quantity sold
Revenue

Dimension Tables

Contain descriptive data.

Examples:

Customer name
Product category
Date

Example

Fact Table: Orders

Order ID
Product ID
Customer ID
Revenue

Dimension Tables:

Customers
Products
Dates

Data Modeling Schemas

1. Star Schema (Best Practice)

Central fact table connected to dimension tables.

Structure:
Fact → Dimensions (no dimension-to-dimension links)

Why it’s powerful:

Fast
Clean
Easy to understand

Use case:
Most business dashboards (sales, marketing, finance)

2. Snowflake Schema

Dimensions are normalized (split further).

Example:
Product → Category → Department

Pros:

Reduces redundancy

Cons:

More complex
Slower queries

3. Flat Table (DLAT)

Everything in one table.

Pros:

Simple

Cons:

Redundant data
Poor performance at scale

Use case:
Small datasets or quick prototypes

Role-Playing Dimensions

A single dimension used multiple times.

Example:
Date table used as:

Order Date
Delivery Date

In Power BI:

Duplicate the Date table
Create separate relationships

Common Data Modeling Mistakes

From experience, these will mess you up:

Using flat tables for large datasets
Too many many-to-many relationships
Bi-directional filters everywhere
Not using a proper Date table
Mixing joins and relationships incorrectly

Data modeling is not the flashy part of Power BI.

But it’s the part that determines whether your analysis is:

Accurate
Scalable
Trustworthy

Right now at LuxDev, I’m starting to see that this is the real shift from “using tools” to actually thinking like a data analyst

How Excel is Used in Real World Data Analysis

Shadrack Tesot — Fri, 27 Mar 2026 20:15:41 +0000

If you had told me a year ago that Excel would become one of the most powerful tools in my journey into data, I probably would have thought of it as just a place to store numbers. Something basic. Something entry-level.

I was wrong.

As I’ve worked with different clients and now upskilling at LuxDev, I’ve seen Excel shift from a simple spreadsheet tool to something closer to a control center for business decisions. It’s not just about data entry. It’s about understanding what’s happening in a business and being able to act on it.

Let’s break that down.

At its core, Excel is a spreadsheet software used to organize, analyze, and visualize data. But that definition barely scratches the surface.

In real-world use, Excel becomes:

A database (storing structured data)
A calculator (processing numbers at scale)
A reporting tool (summarizing insights)
A decision-making system (guiding actions)

When I was working with one of my client, we had a Google Sheets system (which works almost exactly like Excel). Orders were coming in from different channels like WhatsApp, Instagram, and the shop. Everything needed to be tracked: product type, quantity, price, status, and fulfillment.

Now imagine doing that manually every day. It would be chaos.

This is where Excel comes in.

We structured the sheet so that:

Each row represented an order
Columns captured key details like product, quantity, and price
We used formulas to automatically calculate totals and track progress

That’s real-world data analysis. Not some abstract dataset. Actual business operations.

Here are some of the most useful things I’ve learned and actually used.

1. IF Statements (Decision Logic)

The IF function lets you create logic inside your data.

Example:

=IF(K2>1000, "High Value", "Low Value")

In a real scenario, this helps categorize orders. This kind of logic can quickly tell you which customers are worth prioritizing or which orders need special attention.

You’re basically teaching your spreadsheet how to think.

2. VLOOKUP / XLOOKUP (Finding Data Fast)

Let’s say you have two sheets:

One with order IDs
Another with customer details

Instead of manually matching them, you use:

=VLOOKUP(A2, Customers!A:D, 2, FALSE)

This pulls customer information into your order sheet automatically.

In real life, this is how you connect different parts of a business. Sales data, customer data, inventory. Everything becomes linked.

3. SUMIF / COUNTIF (Targeted Analysis)

These formulas help you answer specific questions.

Example:

=SUMIF(B:B, "Instagram", K:K)

This tells you total revenue from Instagram orders.

Now imagine doing that across multiple channels. You can quickly see which platform is performing best. That’s not just data, that’s strategy.

4. Pivot Tables (Turning Raw Data into Insight)

This is where things get powerful.

A pivot table lets you take thousands of rows and summarize them instantly.

For example:

Total sales by product
Orders per month
Revenue by channel

Instead of scanning raw data, you get a clean summary in seconds.

In my experience, this is what turns you from someone who “uses Excel” into someone who actually analyzes data.

The Bigger Picture

What I’m learning now at LuxDev is that Excel is just the starting point. Eventually, you move into tools like Python, SQL, and machine learning.

But here’s the truth most people don’t say:

If you can’t analyze data in Excel, you won’t magically become good at data science.

Excel builds your thinking:

How to structure data
How to ask the right questions
How to turn numbers into decisions

Excel in the real world is not about memorizing formulas. It’s about solving problems.

It helps streamline operations.
At LuxDev, it’s forming the foundation for deeper data skills.

So if you’re starting out, don’t underestimate Excel. It’s not basic. It’s fundamental.

And if you use it right, it can take you a lot further than you think.