DEV Community: Ian Githu

From Empty Schema to Real Insights: A Two-Week Walkthrough of PostgreSQL

Ian Githu — Sun, 19 Jul 2026 09:06:37 +0000

Two weeks ago, if you had asked me to explain the difference between a WHERE clause and a HAVING clause, I probably wouldn't have had an answer. Fast forward to today, and I can build a relational database from scratch, fill it with real data, break it on purpose, fix it, and write queries that turn raw data into meaningful insights. I'll walk you through that journey using a school database for Greenwood Academy, complete with students, subjects, and exam results.

We'll build the database step by step, explore the key PostgreSQL concepts, write practical queries that solve real problems, and provide practical examples you can follow along.

What is a database?
A database is an organized collection of related data that is stored electronically and designed to make it easy to store, retrieve, update, and manage information.

What is a Database Management System (DBMS)?
It is a software that enables us to create, manage, and query databases.
Some common examples include: MySQL, PostgreSQL, SQLite

Types of Databases

1. Relational Databases
A relational database stores data in tables (rows and columns).
The tables are related to each other using primary and foreign keys.
2. Non-Relational Databases
They store data in flexible formats rather than fixed tables, which are best suited for handling large volumes of unstructured data.

Why do we use databases?

Efficient storage of large amounts of data.
Easy updates without affecting unrelated data.
Reduced duplication through a proper design.
Data security with controlled access.
Support for multiple users working simultaneously.

Creating a Relational Database

SQL(Structured Query Language) is the standard language used to interact with relational databases.

SQL Data Types and Constraints

Data types define the kind of data that can be stored in each column of a table. They help maintain accuracy and consistency.
Constraints are rules applied to columns or tables to control what data can be stored.

Categories of Data Types include:
1. Numeric data types

**INT** - stores standard whole numbers
**SERIAL** - stores auto-incrementing integers like IDs
**DECIMAL** - used for exact decimal values

2. Character/strings
- Char(n) - stores fixed-length text
- Varchar(n) - variable length text with a maximum limit, e.g., varchar(50)
- Text - variable-length text with no strict limit

3. Date and Time
- Date - stores date only
- Time - stores time only
- Timestamp - stores both date and time

Constrains
NOTNULL - ensures a column must have a value
NULL - allows a column to have no value
DEFAULT - provides a value automatically if none is given
PRIMARY KEY - uniquely identifies each row
UNIQUE - ensures all values in a column are different
FOREIGN KEY - it references a primary key from another table
CHECK - ensures a value meets a specific condition

The setup: Structure of the dataset we're working with

- students-student_id, first_name, last_name, gender, date_of_birth, class, city
- subjects-subject_id, subject_name, department, teacher_name, credit_hours
- exam_results-result_id, student_id, subject_id, marks, exam_date, grade

1. DDL - Designing the structure

DDL(Data Definition Language) refers to the set of commands that create and modify database objects such as schemas and tables. Common commands include CREATE SCHEMA, CREATE TABLE, and ALTER TABLE.
Tables are the primary structures used to store data in a relational database.
A schema is a logical container used to group related tables.

Schema Creation

CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;

This creates a schema called greenwood_academy and sets it as the working environment.

Creating the students table

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)
);

This creates a table called students, with the student_id as the primary key. The NOT NULL constraint is used to ensure the database rejects incomplete data.

Creating 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),
    credit_hours    INT
);

This creates a table called subjects, with the subject_id as the primary key.

Creating the exam_results table

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

The exam_results table is created with the result_id as the primary key. The references clause is introduced to showcase the relationship between the exam_results table, the students, and the subjects table.
The student_id and subject_id columns are foreign keys in the exam_results table; they reference the primary keys in their respective tables.

ALTER TABLE - used to modify an existing table

ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);
ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;
ALTER TABLE students DROP COLUMN phone_number;

Alter Table allowed us to add the phone_number column, rename the credits column to credit_hours, and drop the phone_number column we had created.

2. DML - Putting Data In, Taking it out

DML(Data Manipulation Language) consists of commands used to add, modify, and remove data from tables. The most common commands are INSERT, UPDATE, and DELETE.
INSERT - adds new records to a table, whether it is a single row or multiple rows in a single statement, by listing several sets of values.

Inserting data into students table

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'),
(2, 'Brian', 'Ochieng', 'M', '2007-07-25', 'Form 4', 'Mombasa')
(3,  'Cynthia', 'Mutua',   'F', '2008-11-05', 'Form 3', 'Kisumu'),
(4,  'David',   'Kamau',   'M', '2007-02-18', 'Form 4', 'Nairobi'),
(5,  'Esther',  'Akinyi',  'F', '2009-06-30', 'Form 2', 'Nakuru'),
(6,  'Felix',   'Otieno',  'M', '2009-09-14', 'Form 2', 'Eldoret'),
(7,  'Grace',   'Mwangi',  'F', '2008-01-22', 'Form 3', 'Nairobi'),
(8,  'Hassan',  'Abdi',    'M', '2007-04-09', 'Form 4', 'Mombasa'),
(9,  'Ivy',     'Chebet',  'F', '2009-12-01', 'Form 2', 'Nakuru'),
(10, 'James',   'Kariuki', 'M', '2008-08-17', 'Form 3', 'Nairobi');

Inserting data into subjects table

insert into subjects (subject_id, subject_name, department, teacher_name, credit_hours) values 
(1,  'Mathematics',       'Sciences',  'Mr. Njoroge',  4),
(2,  'English',           'Languages', 'Ms. Adhiambo', 3),
(3,  'Biology',           'Sciences',  'Ms. Otieno',   4),
(4,  'History',           'Humanities','Mr. Waweru',   3),
(5,  'Kiswahili',         'Languages', 'Ms. Nduta',    3),
(6,  'Physics',           'Sciences',  'Mr. Kamande',  4),
(7,  'Geography',         'Humanities','Ms. Chebet',   3),
(8,  'Chemistry',         'Sciences',  'Ms. Muthoni',  4),
(9,  'Computer Studies',  'Sciences',  'Mr. Oduya',    3),
(10, 'Business Studies',  'Humanities','Ms. Wangari',  3);

Inserting into exam_results

insert into exam_results (result_id, student_id, subject_id, marks, exam_date, grade) values  
(1,  1, 1, 78, '2024-03-15', 'B'),
(2,  1, 2, 85, '2024-03-16', 'A'),
(3,  2, 1, 92, '2024-03-15', 'A'),
(4,  2, 3, 55, '2024-03-17', 'C'),
(5,  3, 2, 49, '2024-03-16', 'D'),
(6,  3, 4, 71, '2024-03-18', 'B'),
(7,  4, 1, 88, '2024-03-15', 'A'),
(8,  4, 6, 63, '2024-03-19', 'C'),
(9,  5, 5, 39, '2024-03-20', 'F'),
(10, 6, 9, 95, '2024-03-21', 'A');

UPDATE - It changes existing records.
Always filter by something specific, ideally the primary key, to avoid updating every row in the table.

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

DELETE - removes rows from a table, but maintains the structure of the table. It is accompanied by the where clause to filter the table; otherwise, you risk emptying the whole table.

DELETE FROM exam_results
WHERE result_id = 9;

This removes the row with the student_id of 9.

3. Filtering with WHERE - involves asking the database a question
The WHERE clause is used to filter rows based on a condition, and AND/OR lets you combine conditions.
AND means every condition must be met:

SELECT first_name, last_name, class, city
FROM students
WHERE class = 'Form 3'
  AND city = 'Nairobi';

This returns all the students who are in form 3, and come from Nairobi city.

OR means any one of them must hold

SELECT first_name, last_name, class
FROM students
WHERE class = 'Form 2'
   OR class = 'Form 4';

This returns either a student who is in form 2 or form 3.

4. Range, Membership & Search operators
This involves filtering the rows with the WHERE clause and the range operators BETWEEN, IN, NOT IN, LIKE

BETWEEN
It checks a range inclusive of both ends.

SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

The 50 and 80 marks are included in the result.

IN
It replaces a chain of OR when matching against a list of values.

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

NOT IN
It excludes a list instead of matching it

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

This returns students who are either in form 1 or form 4.

5. AGGREGATION - COUNT, SUM, GROUP BY, HAVING, LIMIT
Aggregation functions are used to get a meaningful summary of how many and how much in total.

COUNT
COUNT(*) - It is paired with the WHERE clause, and it counts the matching rows

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

This returns the total number of students in Form 3.

SUM
SUM() Adds up a numeric column across matching rows

SELECT SUM(credit_hours) AS total_credit_hours
FROM subjects
WHERE department = 'Sciences';

It sums up the total credit_hours in the science department only.

GROUP BY
It aggregates per category, not just overall; it categorises rows by a shared column first, then runs the aggregate per category.

SELECT
    subject_id,
    COUNT(*)       AS results_recorded,
    AVG(marks)      AS average_marks
FROM exam_results
GROUP BY subject_id;

It will group subject_id, then return the average marks and count of results per subject_id grouping.

HAVING
It filters the rows after grouping and aggregation is done.

SELECT
    subject_id,
    COUNT(*)       AS results_recorded,
    AVG(marks)      AS average_marks
FROM exam_results
GROUP BY subject_id
HAVING COUNT(*) > 1;

This returns only subjects that have more than one recorded exam result.

LIMIT
It mostly follows the ORDER BY clause that arranges rows either in ascending or descending order, and it specifies how many rows are returned.

SELECT
    subject_id,
    COUNT(*)       AS results_recorded,
    AVG(marks)      AS average_marks
FROM exam_results
GROUP BY subject_id
HAVING COUNT(*) > 1
ORDER BY average_marks DESC
LIMIT 3;

This returns only the top 3 average_marks.

6. CASE WHEN
It evaluates conditions top to bottom and returns the label for the first one that matches.
The order of the conditions matters because the first WHEN that evaluates true wins, broader conditions have to come last, not first.

SELECT
    result_id,
    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;

When you place the condition marks >= 40 above marks >= 80, then every top score gets mislabeled as a "Pass".

7. JOINS
A join connects related tables using the relationships you've created using primary and foreign keys, allowing PostgreSQL to combine the data into a single result set. In our case, we can answer a single question that requires all three tables, as joins allow separate pieces of information to work together as if they are stored in a single table.

INNER JOIN
It returns only the rows that have a match in both tables.

SELECT s.first_name, s.last_name, sub.subject_name, e.marks
FROM students s
INNER JOIN exam_results e ON s.student_id = e.student_id
INNER JOIN subjects sub ON e.subject_id = sub.subject_id;

If a student has no exam results, they won't appear in this output.

LEFT JOIN
A LEFT JOIN returns every row from the left table and matching rows from the right table.
If there is no match, the columns on the right will return a NULL

SELECT s.first_name, s.last_name, e.marks
FROM students s
LEFT JOIN exam_results e ON s.student_id = e.student_id;

Any student who hasn't sat a single exam still shows up, with NULL in the marks column, highlighting the missing results.

RIGHT JOIN
It returns all the rows from the right table and matching rows from the left table.
If there is no match, the columns from the left return NULL

SELECT sub.subject_name, e.marks
FROM exam_results e
RIGHT JOIN subjects sub ON e.subject_id = sub.subject_id;

It returns the subjects with no recorded marks.

SELF JOIN
It joins a table to itself, thus helping to compare rows within the same table.

SELECT
    a.first_name AS student_a,
    b.first_name AS student_b,
    a.city
FROM students a
INNER JOIN students b
    ON a.city = b.city
   AND a.student_id < b.student_id;

This pairs students who live in the same city, without matching a student against themselves or listing every pair twice. The clause a.student_id < b.student_id ensures no duplicate pairing.

Conclusion

What This Two-Week Sprint Actually Taught Me

Looking back, the biggest lesson wasn't learning a particular SQL command; it was understanding how everything fits together.

Before you can query data, you need somewhere to store it, which is why DDL comes first. Once the structure is in place, you use DML to fill your tables with meaningful data. Only then does it make sense to start asking questions with SELECT, filtering with WHERE, summarizing with GROUP BY and HAVING, and finally connecting related tables with joins.

At first, I wondered why we didn't jump straight into joins. But after spending time querying individual tables, it became obvious why they're so important. A single table can only tell part of the story. The real power of a relational database comes from storing data in separate, well-organized tables and then bringing it together whenever you need to answer a question.

That realization completely changed how I think about SQL. It stopped feeling like a long list of commands to memorize and started feeling like a language for exploring data. Whether you're asking how many, how much, which students performed best, or what trends exist over time, SQL gives you the tools to find those answers.

I'm looking forward to learning more SQL concepts in the weeks ahead.

Data Modelling, Joins, Relationships and Schemas in Power Bi.

Ian Githu — Tue, 23 Jun 2026 20:58:40 +0000

Often not, most beginners assume that having clean data, visually appealing charts, and well-thought-out dashboards is the most important part of data analysis, only to realize that the quality of analysis largely depends on the organization of the underlying data. You could still get the wrong numbers if the data model is broken.

Power BI is a very powerful tool. What you'll see on a report page is the output of the charts and KPI cards. The action is in the model: what you are doing with your tables, how they interact, and how filters flow through them.

That is where concepts such as modelling, joins, schemas, and relationships come in. This article will help us understand how they relate to one another.

What is Data Modelling

Data modelling is the process of structuring data into tables, defining how they connect, and ensuring that Power BI can accurately filter data from all the tables. It is basically creating a blueprint for your data before building dashboards and doing reports. A good data model ensures:

Reports are easy to build
Calculations return accurate results
Improved performance

Data models are usually viewed under the Model View of Power BI.

Power Query handles data preparation: cleaning, shaping, and joining raw data.
The Data Model is where structure and relationships thrive.
Reports are just the window you look through to see the result.

Schemas

A Schema is defined as the structure of the tables: the number of tables present, the type of tables, and their arrangement. There are two commonly used schemas: a star schema and a snowflake schema.

Star Schema

It consists of one fact table at the centre, containing measurable quantitative data(sales figures, order IDs) and several dimension tables containing descriptive data (customer names, locations, and products).

The fact table is usually wide with many rows, foreign keys and numeric values. The dimension tables are narrow with few rows and descriptive attributes.

Snowflake Schema

A Snowflake schema is an extension of the star schema where the dimension tables are further broken down into additional tables. They are normalised, hence eliminating redundancy.

Joins

A join combines two tables based on a common column.
In Power BI, joins are used when combining tables in Power Query through the merge query feature.

Types of joins

Assume you have the following two tables;

Join Type	Description	Example Result
Inner Join	Returns only rows that have matching values in both tables.	Customers with orders only.
Left Outer Join	Returns all rows from the first (left) table and matching rows from the second table. Non-matching rows from the second table appear as null.	All customers, including those with no orders.
Right Outer Join	Returns all rows from the second (right) table and matching rows from the first table. Non-matching rows from the first table appear as null.	All orders, even if customer information is missing.
Full Outer Join	Returns all rows from both tables. Matching rows are combined, and non-matching rows contain null values where data is missing.	Complete list of customers and orders.
Left Anti Join	Returns rows from the first table that do not have matching values in the second table.	Customers who have never placed an order.
Right Anti Join	Returns rows from the second table that do not have matching values in the first table.	Orders with no corresponding customer records.

Understanding relationships in Power BI

Relationships inform Power BI how tables are connected. They allow information stored in different tables to work together. They are the connections you define between tables in the model view.

For example, a customer may have multiple orders, and each order may contain different products.
Relationships help connect with multiple data sources using cardinality.
Cardinality specifies how the rows in one table are related to the rows in another table.

One-to-many relationship

It is a connection between two tables in a data model where one unique value in one table can be associated with multiple values in the other table. For example, customers and products: each customer can make multiple purchases of a product, so there is a one-to-many relationship between them, but each purchase(many) is associated with only one customer(one).

Many-to-many relationship

Many-to-many relationships exist when both sides of a join can have multiple matching rows.

One-to-One relationship

Usually appears when you've deliberately split a large table for performance reasons.
One record corresponds to exactly one record in another table.

Conclusion

It's tempting to treat data modelling as a box to tick before you get to the "real" work of building visuals. But the truth is, your model is the real work. Everything else is built on top of it.

Schemas give your model structure. A well-designed star schema keeps your DAX simple and your queries fast. As your dataset grows, a clean schema won't collapse under the pressure.

Joins in Power Query let you shape and integrate data at the source, before it ever enters the model. Understanding the difference between join types means you pull exactly the data you need.

Relationships are the connections that make your report interactive. When a user clicks a year on a timeline and every visual updates accordingly, that's relationships doing their job. Get them wrong, wrong cardinality, wrong direction, and your filters either don't work or produce numbers that look right but aren't.

Cross-filter direction might seem like a minor configuration choice, but it has real consequences for how filter context propagates through your model and, by extension, how every single measure in your report behaves.

Together, these four concepts form the foundation of effective data analysis in Power BI. As I continue learning Power BI, I have come to appreciate that good analysis starts long before creating charts; it begins with building the right foundation.

How Excel is used in Real-World Data Analysis

Ian Githu — Sat, 06 Jun 2026 14:45:56 +0000

Every once in a while, you will hear the catchy phrase "data is the new gold." This is because organizations and businesses are using data to make decisions, solve problems, and develop actionable insights. Often, the available datasets are messy. Programs such as Microsoft Excel exist to transform the raw, messy data into meaningful insights that can be used to make data-driven decisions.

As a beginner learning data analytics, I have encountered Excel as one of the tools required for my day-to-day activities. I have gained valuable knowledge on cleaning datasets, analyzing, and uncovering trends. In this article, I'll share what I've learnt in my first week, showcase the various uses of Excel in real-world data analysis, features and formulas that I have learnt so far, and give a personal reflection of how learning Excel has changed the way I see data.

What is Excel?
Microsoft Excel is a spreadsheet software tool that organizes data into rows and columns, making it easier to manage large amounts of information. It allows users to collect, organize, perform calculations, analyze the available trends, and create visualizations.

How Excel is used in real-world data analysis
1. Supporting business decisions
Businesses encounter large amounts of data from customer information, sales records, and inventory entries. Excel helps businesses to track sales performance and monitor the inventory stock levels. By tracking sales, they can analyze customer behavior and identify trends and patterns common to their customers. For example, an electronic retail business can analyze its monthly sales data to identify which products are fast-moving, which generate the most revenue, and which need improvement. These insights help the owners make informed business decisions.

2. Financial reporting
Companies use Excel for financial analysis; they track income and expenses, prepare monthly financial reports, monitor cash flow, compare the actual spending against budgets, and predict future revenues. This analysis helps organizations prepare sensible budgets, understand how changes in pricing may affect profitability, identify overspending, and make adjustments accordingly.

3. Measuring market performance
Digital marketers collect data from websites, social media platforms, and online ads. They use Excel to organize this information and analyze campaign performance. They track website visits, customer sign-ups, advertising costs, and conversion rates. The insights are then used to identify trends and compare various campaign results to determine which campaigns yield the highest return on investment.

4. Project management
Excel is used to plan timelines, allocate tasks, and monitor progress. With a feature like conditional formatting, one can easily highlight overdue tasks. Projects are handled more efficiently.

Features and Formulas I've learned so far

During my first week, I explored some important Excel features that help with data cleaning and organization:
. Excel interface-understanding Excel's structure: rows, columns, cells
. Entering and Editing Data-understanding the supported data types, which include: Text, Numbers, Dates, Formulas
. Data sorting-which means arranging data in a specific order, either ascending(A-Z or smallest to largest) or descending (Z-A or largest to smallest).
. Data filtering-It allows one to display only the rows that meet certain criteria and hide the rest temporarily.*_
*. Data validation-It allows one to restrict the type of data users can input into a cell.
. Freezing panes-Freeze panes are used to keep headers or important columns in view as you scroll, especially for large data sets.
. Data formatting-Number formatting changes how numbers display without altering their actual value. It improves readability and meaning.
Conditional formatting Highlights cells automatically based on rules or criteria, helping spot trends or outliers.
. Removing duplicates-Duplicate data leads to errors in analysis.

Formulas learnt so far
SUM()
The SUM function adds a range of numbers together
May be used to calculate the total sales or revenue in a business
AVERAGE()
It calculates the mean value of a dataset
Used to find employee performance score
MIN()
It finds the smallest number
Can be used to find the youngest employee in an organization
MAX()
It finds the largest number
Can be used to find the age of the oldest employee in a company
COUNT()
Counts how many numbers are in a range
COUNTBLANK()
It counts the empty cells
Used to identify missing data
COUNTIF() and COUNTIFS()
These functions count records that meet specific conditions
Example: counting employees in a department who are of a certain gender or age
MODE()
It identifies the most frequently occurring value
MEDIAN()
It returns the middle value in a dataset
It helps in understanding data distributions
SUMIF and SUMIFS
These functions add values based on the specified criteria.
Can be used to calculate the total sales of a particular product based on its location

My personal reflection: How learning Excel changed the way I see data
Excel has helped me understand that every data set contains valuable information. Simple actions such as identifying missing values and calculating the sum, averages, mean, and mode can reveal meaningful insights. It has made me understand the importance of cleaning, standardizing, and validating data before doing analysis. Errors in a dataset can lead to inaccurate conclusions. I understand that data can tell stories, reveal patterns, and support real-world decisions. Excel is indeed a powerful tool that transforms raw data into actionable insights.

Indeed, Excel has been an eye-opener on how I view data analysis; it's not just about numbers but asking the important questions and making better decisions. I'm looking forward to diving deeper into Excel and exploring this powerful tool.