DEV Community: Nelly Mogere

SQL Intermediate Project: Build a Greenwood Academy Database with PostgreSQL

Nelly Mogere — Wed, 22 Jul 2026 16:44:47 +0000

Intermediate SQL Portfolio Project: Master Key Skills

If you already know basic SQL and want a project that feels more practical, this Greenwood Academy database is a good next step. It starts with simple tables, then moves into real database work: inserting data, updating records, filtering results, using operators, counting records, formatting dates, writing CASE WHEN logic, and using a subquery for a more advanced question.

This project is labelled as intermediate SQL because it goes beyond only creating tables and selecting rows. It shows how SQL can help organize school data and turn it into information that teachers, administrators, students, and technical teams can understand.

What this project is about

Greenwood Academy is a fictional school database. The database stores students, subjects, and exam results.

Instead of working with random examples, the project follows a story. A school needs to keep student details, know which subjects are taught, record exam marks, correct mistakes, remove cancelled results, and create simple reports.

The database has three main tables:

Table	What it stores	Example use
`students`	Student names, gender, date of birth, class, and city	Find Form 3 students from Nairobi
`subjects`	Subject names, departments, teachers, and credit hours	Find all Science subjects
`exam_results`	Student results, marks, grades, and exam dates	Find results above 70 marks

The main idea is simple: first create a clean structure, then add data, then ask useful questions.

1. Creating the schema

The project starts by creating a schema called greenwood_academy. A schema helps group related database objects together.

CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;

This keeps the school database objects organized in one place.

2. Creating the students table

The first table stores student information.

CREATE TABLE students (
    student_id SERIAL 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 most important column here is student_id. It is the primary key, which means every student gets a unique identifier.

For a non-technical reader, this is like giving every learner a school admission number. Even if two students have the same name, the database can still tell them apart.

3. Creating the subjects table

The second table stores the subjects offered by Greenwood Academy.

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

The UNIQUE rule on subject_name prevents the same subject from being entered more than once with the exact same name.

4. Creating the exam results table

The third table connects students to subjects through exam performance.

CREATE TABLE exam_results (
    result_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    subject_id INT NOT NULL,
    marks INT NOT NULL,
    exam_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE CASCADE
);

This is where the project becomes relational. The exam_results table does not store the full student name or full subject details. It stores student_id and subject_id, then uses foreign keys to connect back to the correct records.

That means one student can have many exam results, and one subject can appear in many exam results.

5. Altering tables after creation

A database design often changes as the project grows. In this project, I added a phone number column, renamed a subject column, removed the phone number column, and added a grade column.

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 exam_results ADD COLUMN grade VARCHAR(1);

This part is important because real databases are not always designed perfectly on the first attempt. ALTER TABLE lets you adjust the structure while continuing to build.

6. Inserting student data

After creating the tables, I inserted sample students into the database.

INSERT INTO students (
    first_name,
    last_name,
    gender,
    date_of_birth,
    class,
    city
)
VALUES
    ('Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
    ('Brian', 'Ochieng', 'M', '2007-07-25', 'Form 4', 'Mombasa'),
    ('Cynthia', 'Mutua', 'F', '2008-11-05', 'Form 3', 'Kisumu'),
    ('David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi');

Sample output from the students table:

student_id	first_name	last_name	class	city
1	Amina	Wanjiku	Form 3	Nairobi
2	Brian	Ochieng	Form 4	Mombasa
3	Cynthia	Mutua	Form 3	Kisumu
4	David	Kamau	Form 4	Nairobi

7. Inserting subjects and exam results

The subjects table stores what the school teaches.

INSERT INTO subjects (
    subject_name,
    department,
    teacher_name,
    credit_hours
)
VALUES
    ('Mathematics', 'Sciences', 'Mr. Njoroge', 4),
    ('English', 'Languages', 'Ms. Adhiambo', 3),
    ('Biology', 'Sciences', 'Ms. Otieno', 4),
    ('History', 'Humanities', 'Mr. Waweru', 3);

The exam results table then records marks for specific students and subjects.

INSERT INTO exam_results (
    student_id,
    subject_id,
    marks,
    exam_date,
    grade
)
VALUES
    (1, 1, 78, '2024-03-15', 'B'),
    (1, 2, 85, '2024-03-16', 'A'),
    (2, 1, 92, '2024-03-15', 'A'),
    (2, 3, 55, '2024-03-17', 'C');

This is where the three tables start working together.

8. Updating and deleting records

In a real system, data changes. A student may move, a mark may be corrected, or an exam result may be cancelled.

-- Esther Akinyi moved from Nakuru to Nairobi
UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

-- Correct marks for result_id 5 from 49 to 59
UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

-- Remove a cancelled exam result
DELETE FROM exam_results
WHERE result_id = 9;

This section shows that SQL is not only for reading data. It is also used to maintain accurate records.

9. Filtering students and subjects

Filtering is one of the most useful SQL skills. It allows you to ask focused questions.

-- Find all students in Form 4
SELECT *
FROM students
WHERE class = 'Form 4';

Expected output:

first_name	last_name	class	city
Brian	Ochieng	Form 4	Mombasa
David	Kamau	Form 4	Nairobi
Hassan	Abdi	Form 4	Mombasa

Another example finds all subjects in the Sciences department.

-- Find all subjects in the Sciences department
SELECT *
FROM subjects
WHERE department = 'Sciences';

Expected output:

subject_name	department	teacher_name
Mathematics	Sciences	Mr. Njoroge
Biology	Sciences	Ms. Otieno
Physics	Sciences	Mr. Kamande
Chemistry	Sciences	Ms. Muthoni
Computer Studies	Sciences	Mr. Oduya

10. Combining conditions with AND and OR

SQL becomes more useful when conditions are combined.

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

Expected output:

first_name	last_name	class	city
Amina	Wanjiku	Form 3	Nairobi
Grace	Mwangi	Form 3	Nairobi
James	Kariuki	Form 3	Nairobi

The AND keyword means both conditions must be true.

The OR keyword is used when either condition can be true.

-- Find students who are in Form 2 or Form 4
SELECT *
FROM students
WHERE class = 'Form 2'
   OR class = 'Form 4';

11. Using BETWEEN, IN, NOT IN, and LIKE

Intermediate SQL includes operators that make queries cleaner.

Operator	Meaning	Example use
`BETWEEN`	Match values inside a range	Marks from 50 to 80
`IN`	Match any value in a list	Students from Nairobi, Mombasa, or Kisumu
`NOT IN`	Exclude values in a list	Students not in Form 2 or Form 3
`LIKE`	Match a text pattern	Subjects containing Studies

-- Find exam results where marks are between 50 and 80
SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

-- Find students who live in selected cities
SELECT *
FROM students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

-- Find subjects whose name contains Studies
SELECT *
FROM subjects
WHERE subject_name LIKE '%Studies%';

These operators reduce repetition and make the question easier to read.

12. Working with string functions

String functions help format text without changing the original stored data.

-- Show city names in uppercase and lowercase
SELECT
    city,
    UPPER(city) AS upper_city,
    LOWER(city) AS lower_city
FROM students;

Expected output:

city	upper_city	lower_city
Nairobi	NAIROBI	nairobi
Mombasa	MOMBASA	mombasa
Kisumu	KISUMU	kisumu

Another useful string function is CONCAT, which joins text values together.

-- Combine subject name and teacher name
SELECT CONCAT(subject_name, ' - ', teacher_name) AS subject_teacher
FROM subjects;

Expected output:

subject_teacher
Mathematics - Mr. Njoroge
English - Ms. Adhiambo
Biology - Ms. Otieno

13. Working with dates

Dates are very important in school data because students have birth dates and exams have exam dates.

-- Extract year, month, and day from each student's date of birth
SELECT
    first_name,
    date_of_birth,
    EXTRACT(YEAR FROM date_of_birth) AS birth_year,
    EXTRACT(MONTH FROM date_of_birth) AS birth_month,
    EXTRACT(DAY FROM date_of_birth) AS birth_day
FROM students;

Expected output:

first_name	date_of_birth	birth_year	birth_month	birth_day
Amina	2008-03-12	2008	3	12
Brian	2007-07-25	2007	7	25

The project also formats exam dates into a friendlier format.

-- Format each exam date as a friendly date
SELECT
    result_id,
    exam_date,
    TO_CHAR(exam_date, 'Day, DDth Month YYYY') AS friendly_date
FROM exam_results;

14. Important gotcha: hidden spaces in TO_CHAR day names

This was one of the most important small lessons I noticed while working with dates.

When TO_CHAR(exam_date, 'Day') is used, PostgreSQL pads the day name with extra spaces so all day names line up with the longest day name, Wednesday. That means the output may look correct on the screen, but the value can contain hidden trailing spaces.

Format used	What it looks like	What may actually be returned
`TO_CHAR(exam_date, 'Day')`	`Saturday`	`'Saturday '` with hidden padding
`TO_CHAR(exam_date, 'FMDay')`	`Saturday`	`'Saturday'` without padding

This matters because hidden spaces can break conditional logic. A value that looks like Saturday may not equal the text 'Saturday' if the database actually returned 'Saturday '.

-- This can fail because 'Day' may return padded text such as 'Saturday '
SELECT
    result_id,
    TO_CHAR(exam_date, 'Day') AS exam_day,
    CASE
        WHEN TO_CHAR(exam_date, 'Day') IN ('Saturday', 'Sunday') THEN 'Weekend'
        ELSE 'Weekday'
    END AS day_type
FROM exam_results;

The safer version from this project uses FMDay. The FM modifier means fill mode, and it removes the blank padding from the formatted result.

-- Safer version: FMDay removes hidden padding from the day name
SELECT
    result_id,
    TO_CHAR(exam_date, 'FMDay') AS exam_day,
    CASE
        WHEN TO_CHAR(exam_date, 'FMDay') IN ('Saturday', 'Sunday') THEN 'Weekend'
        ELSE 'Weekday'
    END AS day_type
FROM exam_results;

Another safe option is to wrap the formatted value with TRIM().

-- Alternative fix: TRIM removes leading and trailing spaces
SELECT
    result_id,
    TRIM(TO_CHAR(exam_date, 'Day')) AS exam_day,
    CASE
        WHEN TRIM(TO_CHAR(exam_date, 'Day')) IN ('Saturday', 'Sunday') THEN 'Weekend'
        ELSE 'Weekday'
    END AS day_type
FROM exam_results;

The lesson is simple: when using TO_CHAR() for day names in conditions, use FMDay or TRIM(). Otherwise, the query may look correct but still classify weekends as weekdays because of invisible spaces.

15. Counting records

Counting helps turn raw records into summaries.

-- Count how many students are currently in Form 3
SELECT
    class,
    COUNT(student_id) AS total_students
FROM students
WHERE class = 'Form 3'
GROUP BY class;

Expected output:

class	total_students
Form 3	4

Another count checks exam results with marks of 70 or above.

-- Count exam results with marks of 70 or above
SELECT COUNT(result_id) AS total_results
FROM exam_results
WHERE marks >= 70;

This is where the database starts to behave like a reporting tool.

16. Using CASE WHEN for readable labels

CASE WHEN is useful when raw values need to become meaningful labels.

-- Label each exam result with a grade description
SELECT
    result_id,
    marks,
    CASE
        WHEN marks >= 80 THEN 'Distinction'
        WHEN marks >= 60 THEN 'Merit'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS result_grade
FROM exam_results;

Expected output:

result_id	marks	result_grade
1	78	Merit
2	85	Distinction
3	92	Distinction
4	55	Pass
5	59	Pass

17. Using CASE WHEN with class labels

The project also labels students as Senior or Junior based on their class.

-- Label students as Senior or Junior
SELECT
    first_name,
    last_name,
    class,
    CASE
        WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
        WHEN class IN ('Form 2', 'Form 1') THEN 'Junior'
    END AS class_label
FROM students;

Expected output:

first_name	last_name	class	class_label
Amina	Wanjiku	Form 3	Senior
Brian	Ochieng	Form 4	Senior
Esther	Akinyi	Form 2	Junior

This shows how SQL can add meaning to stored values.

18. Finding upcoming birthdays with a subquery

The most advanced query in this project finds students whose birthdays are coming up in the next 30 days.

The challenge is that date_of_birth includes the original year of birth. To compare birthdays with the current date, the query first calculates each student's birthday in the current year, then filters from that calculated result.

-- Find students whose birthday is coming up in the next 30 days
SELECT
    student_id,
    first_name,
    last_name,
    date_of_birth,
    birthday_this_year - CURRENT_DATE AS days_until_birthday
FROM (
    SELECT
        *,
        (
            date_of_birth
            + (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM date_of_birth))
            * INTERVAL '1 year'
        )::date AS birthday_this_year
    FROM students
) sub
WHERE birthday_this_year BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days';

This is a strong SQL example because it solves a problem in two stages:

Stage	What happens
Inner query	Calculates each student's birthday in the current year
Outer query	Filters only birthdays between today and the next 30 days

The lesson here is that SQL queries can be built step by step. When a question is too complex, create the missing calculated value first, then filter from it.

What I learned from this project

This project helped me see SQL as more than a language for writing commands. It is a way to organize a real-world situation into clear tables and useful questions.

I practiced:

Creating schemas and tables
Using primary keys and foreign keys
Inserting sample data
Updating and deleting records
Filtering with WHERE, AND, and OR
Using BETWEEN, IN, NOT IN, and LIKE
Working with string and date functions
Counting records with COUNT
Grouping records with GROUP BY
Creating labels with CASE WHEN
Solving a multi-step question with a subquery

Final thoughts

Greenwood Academy is a small database project, but it follows the same thinking used in larger systems. First, you decide what information matters. Then you store it in the right tables. After that, SQL helps you ask better questions and produce answers that people can use.

That is what makes this an intermediate SQL project. It is not only about syntax. It is about using SQL to turn raw data into structured, readable, and meaningful information.

SQL Beginner Project: Build a Real-World Supermarket Database with PostgreSQL

Nelly Mogere — Sat, 18 Jul 2026 23:10:22 +0000

SQL Beginner Project: Build a Real-World Supermarket Database with PostgreSQL

If you are learning SQL and want a project that feels practical, this Sunrise Supermarket database is a great place to start. Instead of only reading theory, this project shows how to design tables, insert real sample data, clean and update records, filter business data, summarize results, and connect tables with joins.

In this article, I will walk through the SQL concepts I practiced while building a small supermarket database from scratch.

What is SQL?

SQL stands for Structured Query Language. It is the language used to communicate with relational databases. With SQL, you can create database structures, store data, update records, delete records, and ask questions about the data using queries.

SQL is widely used in software development, data analysis, backend engineering, business intelligence, and reporting. For example, a supermarket can use SQL to manage customers, products, orders, stock levels, and sales reports.

SQL Areas Covered in This Project

This project covers the following beginner-friendly SQL topics:

Database creation and schema selection
Creating tables with primary keys and foreign keys
Using constraints such as UNIQUE, CHECK, and DEFAULT
Inserting data into related tables
Altering table structure with ALTER TABLE
Updating and deleting records
Filtering data with WHERE, IN, BETWEEN, and LIKE
Sorting and limiting results with ORDER BY and LIMIT
Grouping data with GROUP BY and HAVING
Joining multiple tables with INNER JOIN and LEFT JOIN

Project Scenario: Sunrise Supermarket

Sunrise Supermarket wants to move its records into a proper database. The database needs four connected tables:

customers
products
orders
order_items

These tables help answer questions like:

Which customers placed orders?
Which products cost more than a certain amount?
Which orders are still pending?
What products were ordered and in what quantity?
How can customer, order, and product data be joined together?

1. Creating the Database Schema

A schema helps organize database objects such as tables. In this project, I created a schema called sunrise and set it as the active search path.

-- Create the schema for the supermarket database
CREATE SCHEMA sunrise;

-- Use the sunrise schema for the rest of the project
SET search_path TO sunrise;

2. Creating the Customers Table

The customers table stores customer details. Each customer has a unique customer_id, and the email and phone number should not be repeated.

-- Create the customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    full_name VARCHAR(50),
    email VARCHAR(50) UNIQUE,
    phone_number VARCHAR(50),
    county VARCHAR(50)
);

-- Add a unique constraint to phone_number
ALTER TABLE customers
    ALTER COLUMN phone_number TYPE VARCHAR(50),
    ADD CONSTRAINT unique_phone UNIQUE (phone_number);

3. Inserting Customers

After creating the table, I inserted four customer records.

-- Insert customer records
INSERT INTO customers (full_name, email, phone_number, county)
VALUES
    ('Grace Wambui', 'grace.wambui@gmail.com', '0711223344', 'Nairobi'),
    ('Kevin Mutiso', 'kevin.mutiso@gmail.com', '0722334455', 'Nakuru'),
    ('Faith Chebet', 'faith.chebte@gmail.com', '0733445566', 'Eldoret'),
    ('Ibrahim Noor', 'ibrahim.noor@gmail.com', '0744556677', 'Mombasa');

-- View all customers
SELECT * FROM customers;

Expected output:

customer_id	full_name	email	phone_number	county
1	Grace Wambui	grace.wambui@gmail.com	0711223344	Nairobi
2	Kevin Mutiso	kevin.mutiso@gmail.com	0722334455	Nakuru
3	Faith Chebet	faith.chebte@gmail.com	0733445566	Eldoret
4	Ibrahim Noor	ibrahim.noor@gmail.com	0744556677	Mombasa

4. Creating the Products Table

The products table stores supermarket products. The unit_price must be greater than zero, and stock starts at 0 if no value is provided.

-- Create the products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(50),
    category VARCHAR(50),
    unit_price NUMERIC(10,2) CHECK (unit_price > 0),
    stock INT DEFAULT 0
);

5. Inserting Products

-- Insert product records
INSERT INTO products (product_name, category, unit_price, stock)
VALUES
    ('Maize Flour 2kg', 'Groceries', 180, 50),
    ('Cooking Oil 1L', 'Groceries', 320, 30),
    ('Bathing Soap', 'Toiletries', 85, 100),
    ('Notebook A4', 'Stationery', 60, 200);

-- View all products
SELECT * FROM products;

Expected output:

product_id	product_name	category	unit_price	stock
1	Maize Flour 2kg	Groceries	180.00	50
2	Cooking Oil 1L	Groceries	320.00	30
3	Bathing Soap	Toiletries	85.00	100
4	Notebook A4	Stationery	60.00	200

6. Creating Orders and Order Items

The orders table connects each order to a customer. The order_items table connects each order to the products bought.

-- Create the orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    status VARCHAR(50) DEFAULT 'Pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

-- Create the order_items table
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT CHECK (quantity > 0),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

7. Inserting Orders and Order Items

Because orders references customers, customers must already exist before orders are inserted. Because order_items references both orders and products, both tables must already contain data.

-- Insert order records
INSERT INTO orders (customer_id, order_date, status)
VALUES
    (1, '2024-03-01', 'Delivered'),
    (2, '2024-03-02', 'Pending'),
    (1, '2024-03-03', 'Delivered'),
    (3, '2024-03-04', 'Cancelled');

-- Insert order item records
INSERT INTO order_items (order_id, product_id, quantity)
VALUES
    (1, 1, 2),
    (1, 3, 1),
    (2, 2, 1),
    (3, 4, 5);

8. Altering Table Structure

After creating the first version of the tables, I made three structure changes:

Renamed stock to stock_quantity
Added loyalty_points to customers
Increased product_name to VARCHAR(150)

-- Rename stock column to stock_quantity
ALTER TABLE products RENAME COLUMN stock TO stock_quantity;

-- Add loyalty_points to customers with a default value of 0
ALTER TABLE customers ADD COLUMN loyalty_points INT DEFAULT 0;

-- Allow longer product names
ALTER TABLE products ALTER COLUMN product_name TYPE VARCHAR(150);

9. Updating and Deleting Data

The project also required changing an order status and deleting a cancelled order.

-- Update order_id 2 so its status becomes Delivered
UPDATE orders
SET status = 'Delivered'
WHERE order_id = 2;

-- Delete the cancelled order
DELETE FROM orders
WHERE order_id = 4;

10. Filtering Products and Customers

Filtering is one of the most common SQL skills. Here are examples using comparison operators, IN, BETWEEN, and LIKE.

Products priced above 100

-- Show every product priced above 100
SELECT product_name, unit_price
FROM products
WHERE unit_price > 100;

Expected output:

product_name	unit_price
Maize Flour 2kg	180.00
Cooking Oil 1L	320.00

Customers not based in Nairobi

-- Show every customer not based in Nairobi
SELECT full_name, county
FROM customers
WHERE county <> 'Nairobi';

Expected output:

full_name	county
Kevin Mutiso	Nakuru
Faith Chebet	Eldoret
Ibrahim Noor	Mombasa

Products priced between 60 and 200

-- Show products priced between 60 and 200, inclusive
SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 60 AND 200
ORDER BY unit_price;

Expected output:

product_name	unit_price
Notebook A4	60.00
Bathing Soap	85.00
Maize Flour 2kg	180.00

Customers in selected counties

-- Show customers who live in Nairobi, Nakuru, or Mombasa
SELECT full_name, county
FROM customers
WHERE county IN ('Nairobi', 'Nakuru', 'Mombasa');

Expected output:

full_name	county
Grace Wambui	Nairobi
Kevin Mutiso	Nakuru
Ibrahim Noor	Mombasa

Products with Oil in the name

-- Show products whose name contains Oil
SELECT product_name
FROM products
WHERE product_name LIKE '%Oil%';

Expected output:

product_name
Cooking Oil 1L

11. Sorting and Limiting Results

Sorting helps organize query results, while LIMIT controls how many rows are returned.

-- Show orders that are still Pending, sorted by earliest order_date first
SELECT status, order_date
FROM orders
WHERE status = 'Pending'
ORDER BY order_date ASC;

Expected output after updating order 2 to Delivered:

status	order_date

-- Show the two most expensive products
SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
LIMIT 2;

Expected output:

product_name	unit_price
Cooking Oil 1L	320.00
Maize Flour 2kg	180.00

12. Grouping and Aggregates

Aggregate functions summarize data. In this project, I used grouping to count how many orders each customer placed.

-- Count how many orders each customer has placed
SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id;

Expected output after deleting order 4:

customer_id	total_orders
1	2
2	1

-- Show only customers who placed more than one order
SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

Expected output:

customer_id	total_orders
1	2

13. Joining Tables

Joins are used to combine related information from multiple tables.

Inner join customers with orders

-- Show each customer's name next to their order_id and status
SELECT c.full_name, o.order_id, o.status
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Expected output:

full_name	order_id	status
Grace Wambui	1	Delivered
Kevin Mutiso	2	Delivered
Grace Wambui	3	Delivered

Left join orders with order items

-- Show every order with its order items
SELECT *
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id;

Join order items with products

-- Show each order item with the product name and quantity
SELECT oi.order_item_id, p.product_name, oi.quantity
FROM order_items oi
LEFT JOIN products p ON oi.product_id = p.product_id;

Expected output:

order_item_id	product_name	quantity
1	Maize Flour 2kg	2
2	Bathing Soap	1
3	Cooking Oil 1L	1
4	Notebook A4	5

Join all four tables

This query combines customers, orders, order items, and products to show who ordered what.

-- Join all four tables to show one row per item ordered
SELECT c.full_name, o.order_id, p.product_name, oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

Expected output:

full_name	order_id	product_name	quantity
Grace Wambui	1	Maize Flour 2kg	2
Grace Wambui	1	Bathing Soap	1
Kevin Mutiso	2	Cooking Oil 1L	1
Grace Wambui	3	Notebook A4	5

14. Total Quantity Ordered Per Product

The final challenge was to group the joined data and calculate total quantity ordered per product.

-- Show total quantity ordered per product across all customers
SELECT p.product_name, SUM(oi.quantity) AS total_quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_name;

Expected output:

product_name	total_quantity
Maize Flour 2kg	2
Bathing Soap	1
Cooking Oil 1L	1
Notebook A4	5

Key Lessons from This SQL Project

This project helped me practice how relational databases work in a real-world example. The biggest lessons were:

Tables should be created in the correct order when foreign keys are involved.
Constraints help protect data quality.
ALTER TABLE is useful when database requirements change.
Filtering helps answer specific business questions.
Aggregates and grouping turn raw records into summaries.
Joins are powerful because they connect data from multiple related tables.

Final Thoughts

Building the Sunrise Supermarket database was a helpful beginner SQL project because it covered both database design and practical querying. By the end, I had practiced creating tables, inserting data, updating records, filtering results, summarizing data, and joining tables together.

If you are learning SQL, I recommend building a small project like this because it helps you understand how SQL is used in real business situations.

Analysing 8 Years of Femicide in Kenya with Excel - A Data Story

Nelly Mogere — Sat, 04 Jul 2026 01:48:46 +0000

Content Warning: This article discusses femicide, the murder of women and girls. The data covers real cases of gender-based violence. If this is a sensitive topic for you, please take care.

Femicide isn't just a statistic. Every row in the dataset I worked with was someone's daughter, sister, wife, or friend.
When I came across the Femicide in Kenya (2016-2023) dataset on Kaggle, I knew I wanted to do more than scroll through it. I wanted to let the data speak, clearly and loudly. So I built a full analysis pipeline in Excel: cleaning, enriching, analysing, and visualising 507 documented cases over eight years.

This is the story of how I did it, what I found, and why it matters.

Where the Data Came From

The dataset lives on Kaggle and was compiled from multiple verified public sources:

Investigative journalism and news media
Activist platforms and gender-based violence tracking organisations
Social media reports and community alerts

Each row represents one unique femicide case. The columns cover:

Field	What it tells us
Date	When it was reported
Victim Name / Age	Who was targeted
County / Town	Where it happened
Suspect Relationship	Who did it
Type of Femicide	Intimate or Non-intimate
Mode of Killing	How it was done
Murder Scene	Where specifically
Case / Court Status	What happened legally

Before I could analyse any of this, though, the data needed serious cleaning.

Cleaning the Data in Excel Power Query

I loaded the raw file into Excel's Power Query Editor, my go-to for structured, repeatable data transformations. Here's the exact cleaning process I followed, step by step.

Step 1: Remove Duplicates

The first thing I always do. Removed all duplicate rows upfront so every case was counted once and only once.

Step 2: Standardise the Author Name Column

The author names had inconsistent casing, some all caps, some all lowercase. I applied the PROPER function to fix this, so JOHN DOE and john doe both become John Doe.

Step 3: Clean the Medium (News Source) Column

Replaced all null and empty values with "Not Provided".
Removed the (Kenya) suffix that was appended to source names. Since every entry is already Kenya-specific, this was just visual clutter making filters messy.

Step 4: Fix the Type of Murder Column

This one was subtle but important. The column had both femicide and Femicide as separate values - Excel treats them as distinct, which would split my pivot counts in two. I standardised everything to Femicide.

Step 5: Clean the Victim Name Column

Some entries had "unnamed" as the victim's name, others were blank. Both were replaced with "Not Provided" for consistency.

Step 6: Fix the Suspect Relationship Column

Two entries had trailing noise:

Stranger had "/Unknown relationship" appended - redundant. Removed it.
Friend had "/known to the victim" appended - also redundant. Removed it.

These kinds of notes make sense in a notes field, not in a category column you'll filter on.

Step 7: Fill Remaining Nulls Across Text Columns

A blanket step - replaced all remaining null and empty cells in text columns with "Not Provided". This ensures no blank filter entries appear in pivot slicers.

Step 8: Apply PROPER to More Columns

Repeated the PROPER text function across other columns (county names, relationship types, etc.) to prevent capitalisation variants from appearing as duplicate filter options.

Data Enrichment - Adding a Region Column

Once the data was clean, I needed to go one level higher than county for geographic analysis. Kenya has 47 counties - grouping them into the 8 administrative regions would give cleaner, more readable charts.

I built a separate Mapping sheet with each county mapped to its region, then used this XLOOKUP to create a new Region column:

=XLOOKUP(U2, Mapping!$A$2:$A$48, Mapping!$B$2:$B$48, "Unknown County")

This had a bonus effect: it doubled as a spell-checker for county names. Any misspelled county would return "Unknown County" - which flagged it for correction in the source data. A clean, self-validating approach.

Building the Dashboard

With clean, enriched data, I built Pivot Tables for every question I wanted to answer, then assembled everything into a single interactive Excel dashboard with slicers for filtering by county, region, murder scene, relationship type, and more.

Here's what the final dashboard looked like:

The Numbers at a Glance

KPI	Value
Total Cases	507
Most Affected County	Nairobi
Counties Affected	45 out of 47
Intimate Partner Cases	83%
Most Vulnerable Age Range	26-36 years
Minor Victims	20
Most Common Method	Stabbing
Most Common Scene	Home

What the Data Revealed

1. The Home Is the Most Dangerous Place

This was the finding that hit hardest. The majority of femicide victims were killed at home, not on the streets, not in unknown locations, at home.

We talk about home as a place of safety. The data dismantles that assumption.

2. 83% of Cases Involved Intimate Partners

Boyfriends. Husbands. Ex-partners.

8 in 10 femicide cases in this dataset were committed by someone the victim was in, or had been in, a relationship with. Intimate partner violence isn't just a contributor to femicide in Kenya. It is, overwhelmingly, the cause.

3. The Most Targeted Age Group Is 26-36

These are women in the prime of their adult lives, building careers, raising families, living independently. The data shows they are the most at-risk group.

Twenty victims were minors. Twenty children.

4. Stabbing and Strangulation Dominate

The two most recorded methods of killing were stabbing and strangulation. Both require physical proximity. This is not random or opportunistic violence - it is violence by someone close to the victim, in spaces familiar to her.

5. This Is a National Crisis, Not a Nairobi Story

Yes, Nairobi had the highest case count. But 45 of Kenya's 47 counties recorded at least one femicide case in this dataset. The geographic spread is a clear signal: this is a nationwide systemic problem.

6. The Justice System Is Failing These Women

When I looked at the court verdict breakdown, the majority of cases never reached a guilty verdict. Many were ongoing. Some suspects were never brought to trial at all. The impunity is visible in the data.

7. Arguments Over Food

I'll be honest, this stopped me in my tracks. Going through case descriptions, several murders were triggered by domestic arguments. One recurring trigger: food.

Arguments over food. Women losing their lives over arguments about food.

That's not just a data point. That's a reflection of what happens when control, anger, and access to violence exist in the same home.

Why I Built This

I'm a data analyst. My tools are spreadsheets, formulas, and charts. But tools don't choose what they illuminate, we do.

I chose to work on this dataset because the story it tells is important, and because too often data on gender-based violence gets buried in PDFs or left uncleaned on data platforms. I wanted to take it out of the raw and make it readable, visual, and undeniable.

If you're a researcher, journalist, policymaker, or just someone who cares, I hope this analysis gives you something useful.

Tools I Used

Microsoft Excel - Power Query for cleaning, XLOOKUP for enrichment, Pivot Tables for aggregation, Charts for visualisation, Dashboard for presentation

That's it. No Python. No SQL. No Power BI. Just Excel used thoughtfully and thoroughly.

Access the Files

The full project, including the raw dataset, cleaned file, and dashboard, is on GitHub:

(https://github.com/pinkleather221/kenya-femicide-analysis.git)

Final Note

Behind every row in this dataset is a name. A face. A life that was ended by violence that could have been prevented.

If this analysis can contribute, even a small amount, to better policy, stronger advocacy, or clearer public understanding of the femicide crisis in Kenya, then it was worth building.

If you found this useful or have questions about the methodology, drop a comment below. And if you're working on similar social impact data projects, I'd love to connect.

From Dirty Data to Business Intelligence: Building an End-to-End Power BI Analytics Solution with PostgreSQL, Power Query, DAX, and Star Schema

Nelly Mogere — Fri, 03 Jul 2026 12:44:39 +0000

How I transformed a messy education dataset into an interactive Business Intelligence solution using PostgreSQL, Power Query, Data Modeling, DAX, and Power BI.

Introduction

Every dashboard tells a story.

But before the charts, KPIs, and colorful visuals come into existence, there's usually something much less exciting a spreadsheet full of inconsistent, duplicated, incomplete, and poorly structured data.
That was exactly the challenge my team received during our Power BI training.
Instead of being handed a perfectly structured dataset ready for visualization, we were given what many data professionals encounter in real organizations: a single flat dataset filled with inconsistencies that needed to be transformed into a reliable source of business intelligence.
Our assignment wasn't simply to create attractive charts.
Our assignment wasn't simply to create attractive charts.

We were expected to complete the entire Business Intelligence workflow, including:

Extracting data from a PostgreSQL database
Establishing a secure database connection using SSL certificates
Cleaning messy data using Power Query
Designing a dimensional data model
Creating relationships between tables
Writing DAX measures
Building interactive dashboards
Producing meaningful business insights

Although this was an educational project, the workflow closely mirrors what Business Intelligence developers and Data Analysts do every day.

In this article, I'll walk through that journey from start to finish.

Understanding the Business Problem

The dataset represented an education management system containing information about:

Students
Schools
Teachers
Subjects
Academic performance
Payments
Locations

At first glance, everything appeared to exist inside one large table. While this may seem convenient, storing everything in a single table quickly creates problems.

Some of the issues included:

Duplicate information
Repeated school names and teacher details
Inconsistent county names and spelling
Mixed capitalization
Missing values
Incorrect data types and invalid numerical values

These are exactly the kinds of data quality issues that reduce trust in business reports. Our brief required us to clean the data, normalize it into logical tables, build relationships, and answer business questions through an interactive Power BI dashboard.

Why We Used PostgreSQL Instead of Importing a CSV

Many Power BI tutorials begin by importing an Excel or CSV file. While this works for demonstrations, most organizations do not store operational data inside spreadsheets.

Instead, business data is typically stored inside relational databases such as PostgreSQL, SQL Server, MySQL, or Oracle.

For this project, our dataset was hosted inside a PostgreSQL database, allowing us to simulate a real production environment where Power BI retrieves data directly from a database rather than from manually exported files.

This approach offered several advantages:

A centralized source of truth
Better data integrity
Easier scalability
Simplified data refreshes
A more realistic enterprise workflow

Configuring PostgreSQL for Secure Connections

One of the most interesting parts of this project happened before opening Power BI.

To establish a secure connection to PostgreSQL, I first had to install and configure the required SSL certificates. Many cloud-hosted PostgreSQL instances enforce encrypted connections to protect data while it travels across the network. Without the correct certificates, Power BI cannot establish a trusted connection.

The process involved:

Installing the PostgreSQL SSL certificate
Configuring the database connection
Verifying encrypted communication
Testing the connection before importing data

Although this step often receives little attention in beginner tutorials, it reflects what happens in production systems where database security is a priority.

Key takeaway: Understanding secure connectivity is just as important as understanding visualization, because no dashboard can exist without reliable access to trusted data.

Connecting PostgreSQL to Power BI

With the secure connection established, I opened Power BI Desktop and selected Get Data > PostgreSQL Database.

After providing the server name, database name, and authentication credentials, Power BI connected directly to PostgreSQL and displayed the available tables through the Navigator window.

Rather than importing every available object, I selected only the tables required for analysis. This is a small but important optimization, because importing unnecessary tables increases model complexity and refresh times.

First Look at the Dataset

The initial inspection confirmed what the assignment description had warned about. The dataset contained numerous quality issues, including duplicate records, null values, incorrect spellings, mixed casing, inconsistent location names, invalid numerical values, and poorly formatted dates.

Important lesson: At this point, jumping directly into building charts would have produced misleading results. A dashboard is only as reliable as the data behind it.

For this reason, data preparation became the next priority.

Cleaning the Data with Power Query

Power Query is one of Power BI's most powerful features. Instead of modifying the source database, Power Query allows analysts to transform incoming data while keeping the original source untouched. This creates a repeatable Extract, Transform, and Load (ETL) process that can be refreshed whenever new data becomes available.

Our cleaning process involved several key transformations:

Standardizing Text

Some records used uppercase values, others used lowercase, and some contained abbreviations or spelling mistakes. For example, a county could appear in several different forms, causing Power BI to treat them as different locations. We standardized these values to ensure consistency across the entire dataset.

Correcting Data Types

Columns representing numbers had occasionally been interpreted as text, and date columns contained inconsistent formats. Power Query made it possible to convert each column into its correct data type before analysis.

Why this matters: Proper data types are essential because DAX calculations depend on them. A number stored as text cannot be summed or averaged.

Handling Missing Values

Several fields contained null values. Rather than blindly deleting records, we evaluated each case individually. Depending on the context, we either replaced missing values, kept them for transparency, or excluded unusable records where appropriate. Documenting these decisions is an important part of responsible data preparation because every transformation influences the final analysis.

Removing Duplicates

Repeated records inflate KPIs and distort business insights. Duplicate students, schools, or teachers could easily lead to incorrect counts. Power Query enabled us to identify and remove duplicate records while preserving unique entities.

Renaming Columns

Clear naming conventions improve model readability. Instead of cryptic or inconsistent column names, we renamed fields using descriptive, business-friendly names. This makes DAX formulas easier to understand and simplifies collaboration.

Creating a Repeatable Transformation Pipeline

One of Power Query's greatest strengths is that every transformation becomes an Applied Step. Instead of manually repeating the cleaning process each time new data arrives, Power BI simply replays the transformation steps automatically during refresh. This makes the cleaning process reproducible, auditable, and significantly less error-prone.

Why We Did Not Keep Everything in One Flat Table

Once the data was clean, an important question emerged: should everything remain inside one giant table?

Technically, it could. Practically, it should not.

Flat tables work for small datasets, but as projects grow they become difficult to maintain. Repeated information wastes storage, complicates updates, and slows analytical queries. Instead of keeping one massive table, we normalized the data into a dimensional model that better represents how business entities relate to one another.

This decision laid the foundation for the next and arguably most important phase of the project: building a Star Schema.

Building a Scalable Data Model with a Star Schema

At this point, our data had been cleaned, standardized, and prepared for analysis. However, one major challenge still remained: the dataset still represented an operational system rather than an analytical one.

Operational databases are designed to store information efficiently. Business Intelligence systems are designed to analyze information efficiently. Those are two very different objectives.

If we had continued using one massive flat table, we would have encountered several problems:

The same school information repeated hundreds of times
Teacher information duplicated for every student
Location information stored repeatedly
Larger file sizes and slower report performance
More complex DAX calculations
Higher maintenance costs whenever data changed

Instead, we redesigned the model using one of the most widely adopted dimensional modeling techniques in Business Intelligence: the Star Schema.

Why Star Schema?

Before building the model, we briefly considered the three most common approaches:

Flat Table: Stores everything in one place. While it appears simple at first, it becomes increasingly difficult to maintain because every entity is repeated multiple times. Imagine a school with 800 students. The school's name would appear 800 separate times in the table.

Snowflake Schema: Introduces additional normalization by breaking hierarchical data into separate tables (Country > Region > County > Sub-county). This reduces redundancy further but also increases relationship complexity. For our assignment, this introduced unnecessary overhead.

Star Schema: Strikes an excellent balance between simplicity, performance, and scalability. The design consists of one central Fact Table surrounded by multiple Dimension Tables. Microsoft also recommends Star Schema as the preferred modeling approach for Power BI because it simplifies filtering, improves query performance, and makes DAX calculations easier to write.

Designing the Fact Table

The fact table became the heart of our analytical model. Unlike dimension tables, which describe entities, the fact table stores measurable events such as:

Student scores
Fee payments
Attendance records
Performance metrics

Every row in a fact table represents an event that can be analyzed. For example:

Notice that the fact table references other entities rather than storing all their descriptive information repeatedly. Instead of storing the teacher's county, school address, and contact details every time, it simply stores a key pointing to the Teacher table. This dramatically reduces redundancy.

Building the Dimension Tables

Once the fact table was identified, we separated descriptive information into dedicated dimension tables:

Student Dimension contained: Student ID, Student Name, Gender, Age, School, County

Teacher Dimension contained: Teacher ID, Teacher Name, Assigned School, Subject

School Dimension contained: School ID, School Name, Category, Location

Subject Dimension contained: Subject ID, Subject Name

Location Dimension centralized county and region information into a reusable table. This allows both schools and teachers to reference the same geographical information while maintaining consistency.

Understanding Primary Keys and Foreign Keys

One concept that finally clicked for me during this project was the relationship between primary and foreign keys.

A Primary Key uniquely identifies each record within a table. For example:

School ID	School Name
101	Green Hills Academy
102	Sunrise School

The School ID is unique. No two schools share the same identifier.

A Foreign Key references the primary key of another table. For example:

Student	School ID
Alice	101
Brian	102

Rather than writing the school name repeatedly, each student simply stores the corresponding School ID. Power BI then uses relationships to retrieve the descriptive information whenever required. This is one of the principles that makes relational databases so efficient.

Creating Relationships in Power BI

Once the tables had been separated, we connected them using relationships. Power BI supports several relationship types:

One-to-One: Every record matches exactly one record in another table. Valid but relatively uncommon in analytical models.

One-to-Many: This became the dominant relationship in our project. For example, one school has many students; one teacher has many student records; one subject has many assessment records. This relationship type reflects how educational data naturally behaves.

Many-to-One: Power BI often displays the same relationship from the opposite perspective. Many students belong to one school. The underlying relationship remains identical.

Understanding Cardinality

Cardinality determines how Power BI filters information across related tables. If relationships are configured incorrectly, visuals may return duplicated values or incorrect totals. Because our model followed a proper Star Schema, defining cardinality became much more straightforward, with most relationships naturally following a One-to-Many structure.

Filter Direction

Power BI allows filters to flow between related tables. Whenever possible, we maintained single-direction filtering, allowing filters to flow from the dimension tables toward the fact table. For example, selecting a county automatically filtered schools, students, teachers, and performance records. This simplified report behavior while reducing ambiguity during DAX calculations.

Why Normalization Matters

Dividing one table into several smaller tables might appear to make the project more complicated. Ironically, the opposite is true. Normalization makes the model easier to maintain, easier to understand, faster to query, less repetitive, and more scalable. As datasets grow, these benefits become increasingly significant.

Turning Data into Insights with DAX

After building the data model, the next step was teaching Power BI how to answer business questions. This is where Data Analysis Expressions (DAX) became essential.

Think of DAX as the language that teaches Power BI how to think. While relationships organize data, DAX transforms that organized data into business metrics.

Calculated Columns vs. Measures

One key lesson from this project was understanding the difference between these two approaches:

Calculated Columns compute values for every row and store the result in the model
Measures calculate results dynamically based on the filters applied by the user

Since dashboards are interactive, measures are generally the better choice because they recalculate automatically as users interact with slicers and filters.

Measures We Created

Total Students
Total Schools
Total Teachers
Average Student Score
Student Distribution
School Distribution

Where the dataset lacked complete information, such as revenue or outstanding balances, we intentionally avoided creating misleading measures. Instead, we documented the limitation and focused our analysis on the data that was available.

Important principle: Good analysts never invent data to satisfy a report.

Applying Business Logic with DAX

Beyond simple aggregations, DAX can encode business rules. Functions such as IF(), AND(), and OR() allow multiple conditions to be evaluated, while SWITCH(TRUE()) provides a cleaner alternative to long nested IF statements.

For example, a revenue classification measure could be written as:

Revenue Category =
SWITCH(
    TRUE(),
    ISBLANK([Revenue]), "Not Provided",
    [Revenue] > 500000, "High Revenue",
    [Revenue] > 100000, "Medium Revenue",
    [Revenue] > 0, "Low Revenue"
)

We also learned how CALCULATE() changes the filter context, enabling measures such as total students in a specific county or average scores for selected schools. This flexibility is one of the reasons DAX is such a powerful analytical language.

Designing the Interactive Dashboard

With the model and measures complete, the final step was presenting the insights.

Our dashboard combined several visualizations to answer the questions outlined in the project brief:

KPI Cards for key metrics
Bar and Column Charts for comparisons
Donut Charts for distributions
Slicers for filtering by school, county, teacher, subject, and gender
Tables for detailed records

Rather than filling the report with as many visuals as possible, we focused on clarity. Each chart answered a specific business question, and together they formed a cohesive analytical story.

Key Insights from the Project

Building the dashboard highlighted how quickly insights become accessible once data is properly prepared. Users could identify:

Schools with the highest student populations
Student distribution across locations
Gender distribution
Teacher allocation
Subject performance
Academic trends across schools and regions

More importantly, the project demonstrated that the quality of these insights depended entirely on the quality of the underlying data model.

Beyond Power BI Desktop

One topic we covered during training was what happens after a report is built.

Publishing a report to the Power BI Service allows organizations to:

Share reports securely with stakeholders
Schedule automatic data refreshes
Create email subscriptions that send report snapshots at scheduled intervals
Embed reports into web applications and organizational portals

We also explored Microsoft Fabric, Microsoft's unified analytics platform that brings together data engineering, data warehousing, real-time analytics, and business intelligence into a single ecosystem. Although our project was developed in Power BI Desktop, understanding how it fits within the broader Microsoft analytics platform provided valuable context for enterprise-scale solutions.

Lessons I Took Away

This project changed the way I think about dashboards.

Before, I assumed the most important skill in Power BI was creating attractive visualizations. I now realize that visualization is only the final stage of a much larger process.

The real work happens long before the first chart is created:

Connecting securely to data sources
Cleaning inconsistent data
Designing scalable data models
Building meaningful relationships
Writing reusable DAX measures
Delivering insights that decision-makers can trust

A visually impressive dashboard built on poor-quality data is still a poor dashboard.

Final Thoughts

What began as a classroom assignment became an excellent introduction to the complete Business Intelligence workflow.

Over the course of the project, I learned how to connect Power BI to a PostgreSQL database using secure SSL connections, transform messy data with Power Query, redesign a flat dataset into a Star Schema, create relationships between fact and dimension tables, write DAX measures, and communicate insights through an interactive dashboard.

More importantly, I learned that Business Intelligence is not about creating charts. It is about transforming raw, imperfect data into information that supports better decisions.

As I continue exploring data engineering and analytics, this project serves as a reminder that every reliable dashboard begins with a well-designed data pipeline, a thoughtful data model, and a commitment to data quality.

Resources

If you are learning Power BI, I would recommend exploring:

Connect With Me

If you are working on Power BI, data engineering, or analytics projects, I would love to connect and exchange ideas.

GitHub: https://github.com/pinkleather221/

How Excel is Used in Real-World Data Analysis

Nelly Mogere — Sat, 06 Jun 2026 06:57:21 +0000

Introduction

In today’s digital world, data is everywhere. Businesses, schools, hospitals and organizations collect large amounts of information every day. One of the most commonly used tools for organizing and analyzing this data is Microsoft Excel.

Before starting my data analytics journey, I viewed Excel as a simple spreadsheet application mainly used for typing tables and performing calculations. However, after learning more about it during my training at LuxDevHQ, I now understand how powerful and important it is in real-world data analysis.

What is excel

Microsoft Excel is a spreadsheet software developed by Microsoft that helps users:

Organize data
Perform calculations
Analyze information
Create charts and reports
Visualize trends and patterns

Excel is widely used because it is easy to learn, flexible, and useful in many industries.

Real-World application of excel in data analysis

1. Business decision-making
Businesses use Excel to track and analyze important information such as:

Sales performance
Inventory records
Employee performance
Customer trends

For example, a company can use Excel to identify which products sell the most and which products are underperforming. This helps managers make better business decisions.

2. Financial Reporting and Budgeting
Excel is heavily used in finance and accounting. Companies use it to:

Calculate profits and losses
Track expenses
Prepare budgets
Forecast future revenue

Functions like SUMIF and AVERAGEIF help analysts quickly calculate totals and averages based on specific conditions.

Excel Features and Formulas I Have Learned

So far, I have learned several useful Excel features and formulas that are commonly used in data analysis.

Features

Freeze Panes
This feature keeps important rows or columns visible while scrolling through large datasets.

Remove Duplicates
This helps clean data by removing repeated information, making datasets more accurate and organized.

Formulas

SUMIF
Used to calculate totals based on a condition.
Example:
Calculating total sales from a specific region.

AVERAGEIF
Used to find averages that meet certain conditions.
Example:
Finding the average marks of students in one class.

COUNTIFS
Used to count values that meet multiple conditions.
Example:
Counting customers from a certain city who purchased a specific product.

My Personal Reflection

At first, I thought Excel was something simple and easy. However, after attending classes and practicing different features and formulas, I now realize that Excel is a powerful analytical tool used in real-world industries.

Even with the few classes I have attended so far, I already feel more confident working with data. I can now:

Find insights from datasets
Calculate totals and averages
Clean messy data
Visualize information clearly

This learning experience has made me more interested in data analytics and I look forward to learning more advanced skills in the future.

Conclusion

Excel plays a major role in real-world data analysis because it helps people organize, analyze and understand data efficiently. From business reporting to financial analysis and marketing insights, Excel continues to be one of the most valuable tools in the world of data.

As a beginner in data analytics, learning Excel has given me confidence and a better understanding of how data can be used to solve real-world problems.

I Built a Cloud Platform That Does Absolutely Nothing (And It Works Perfectly)

Nelly Mogere — Mon, 06 Apr 2026 05:31:11 +0000

This is a submission for the DEV April Fools Challenge

What I Built

I’ve spent a ridiculous amount of time learning cloud architecture, Kubernetes, scaling systems… all the serious stuff.
So naturally, I decided to build something completely useless.
I present: Cloud Architect Simulator™ a platform that successfully deploys absolutely nothing… at scale.
You can:
Deploy apps that don’t exist
Auto-scale to infinity (with 0 users 💀)
Optimize your infrastructure using AI (which deletes everything for better performance)
Brew coffee via API (spoiler: I’m a teapot ☕)
There’s even a region selector with options like Mars (Beta) and Some Guy’s Laptop because… why not?
The whole idea was to poke fun at how complex and over-engineered cloud systems can get except here, all that complexity leads to absolutely no outcome.
And somehow… it still works perfectly.

Demo

pinkleather221.github.io

Everything is working exactly as it shouldn’t

Code

pinkleather221 / Cloud-Architect-Simulator-Deploying-Nothing-at-Scale-

Cloud Architect Simulator™ is a fake cloud platform where you deploy apps that don’t exist, scale to infinity with zero users, and optimize everything into nothing. Built for the DEV April Fools Challenge, it’s a playful take on over-engineered systems.

Cloud-Architect-Simulator-Deploying-Nothing-at-Scale-

Cloud Architect Simulator™ is a fake cloud platform where you deploy apps that don’t exist, scale to infinity with zero users, and optimize everything into nothing. Built for the DEV April Fools Challenge, it’s a playful take on over-engineered systems.

View on GitHub

It’s a simple HTML, CSS and JavaScript setup nothing fancy, just pure intentional nonsense layered on top of clean structure.

How I Built It

I kept the stack intentionally simple:
HTML for structure
CSS for styling (dark mode because we’re engineers obviously)
Vanilla JavaScript for all the chaotic interactions
Most of the logic is just fake state changes, random values and delayed responses to make it feel real.
The fun part was designing interactions that look legitimate at first… and then slowly fall apart.
For example:
Fake deployment logs that end in “No application found”
Billing that spikes randomly then pretends nothing happened
AI optimization that aggressively removes everything
It’s basically a real cloud dashboard… if it had an existential crisis.

Prize Category

I had to pay respect to the legendary 418 I’m a teapot energy.
The “Brew Coffee API” feature is my tribute it goes through a whole dramatic process before ultimately refusing to cooperate because… it’s a teapot.
It felt very on-brand for a project that commits fully to doing the wrong thing in the most elaborate way possible.