DEV Community: Gabriel Mbuva

From Zero to Querying Data: Building My First PostgreSQL Database

Gabriel Mbuva — Sun, 19 Jul 2026 11:12:51 +0000

If you're just getting started with SQL, this project is a great way to learn the fundamentals. In this article, I'll walk you through how I created a simple school database using PostgreSQL, inserted data, and wrote queries to retrieve meaningful information.

Introduction

When learning SQL, one of the best ways to improve is by building a real database instead of just reading about commands. Recently, I completed an assignment where I had to create a database for a fictional secondary school called Greenwood Academy.

The goal wasn't just to write SQL queries; it was to understand how databases are created, how data is stored, and how SQL is used to retrieve and manipulate that data.

By the end of the project, I had learned how to:

Create databases and tables
Insert, update and delete records
Filter data using different SQL operators
Count records using aggregate functions
Use CASE WHEN to categorize data

Step 1: Creating the Database

The first thing I did was create a schema called greenwood_academy.

Schemas help organize database objects such as tables. Then I switched into the schema before creating my tables.

Step 2: Creating the Tables

Next, I created three tables.

Students Table

This table stores information about students.

Some of the columns include:

Student ID
First name
Last name
Gender
Date of Birth
Class
City

Subjects Table

This table stores the subjects taught in the school together with:

Subject name
Department
Teacher
Credit hours

Exam Results Table

Finally, I created a table that stores each student's exam performance.

It contains:

Student ID
Subject ID
Marks
Grade
Exam date

This table connects students with the subjects they took.

Step 3: Modifying the Database

Databases often change as requirements change.

In this assignment, I practiced modifying tables using ALTER TABLE.

First, I added a phone number column, then later, I removed the same column because it was no longer needed.
I also renamed the credits column to credit_hours.

These exercises helped me understand that databases can evolve over time without starting from scratch.

Step 4: Inserting Data

Once the tables were ready, I populated them with data.

The assignment provided:

10 students
10 subjects
10 exam results

Using INSERT INTO, I added each record into its respective table.

After inserting the data, I confirmed everything had been inserted correctly using:

SELECT * FROM students;
I repeated this for the other two tables.

Step 5: Updating and Deleting Data

One thing I learned is that data isn't always permanent.

Sometimes information changes.

For example:

A student moved from Nakuru to Nairobi.
An exam mark was entered incorrectly.
One exam record had to be deleted.

Commands such as delete and update allow us to maintain accurate data without recreating the database.

Step 6: Querying Data

This was probably the most interesting part of the assignment. Using SELECT and WHERE, I could answer questions about the stored data.Simple queries like these are the foundation of SQL.

Step 7: Using SQL Operators

After learning basic filtering, I explored more powerful operators.

BETWEEN

Used to find values within a range.

Example:

Find students who scored between 50 and 80.

IN

Instead of writing multiple OR conditions, IN makes queries cleaner.

NOT IN

Returns values that don't belong to a specified list.

LIKE

Used for pattern matching.

For example:

sql
WHERE first_name LIKE 'A%'

This returns students whose names begin with the letter A.

Learning these operators made my queries much shorter and easier to read.

Step 8: Counting Records

Sometimes you don't need the actual records—you only need the total number.

SQL provides aggregate functions for this.

For example:

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

This tells us how many students are currently in Form 3.

Aggregate functions become very useful when working with larger datasets.

Step 9: Categorizing Data with CASE WHEN

One of my favorite parts of the assignment was learning about CASE WHEN. It works similarly to an if-else statement in programming. Instead of just displaying marks, I could classify students into different performance levels.

For example:

Distinction
Merit
Pass
Fail

This makes query results much easier to understand.

I also used CASE WHEN to classify students as either:

Junior
Senior

based on their class.

What I Learned

This assignment helped me understand the basic workflow of working with relational databases.

I learned how to:

Design tables
Store data efficiently
Modify existing tables
Retrieve information using SQL queries
Filter records using different operators
Count records using aggregate functions
Use conditional logic with CASE WHEN

Although the database was small, the concepts are the same ones used in much larger real-world systems such as banking applications, hospital management systems, school management systems, and e-commerce platforms.

If you're new to SQL, I highly recommend building small projects like this instead of only watching tutorials. Writing queries yourself helps reinforce the concepts much faster.

This Greenwood Academy project gave me hands-on experience with the core SQL skills every beginner should know. I'm looking forward to building more complex databases involving joins, grouping, subqueries, and database normalization as I continue learning.

If you've just started learning SQL, keep practicing. Every query you write brings you one step closer to becoming confident with databases.

Connecting Power BI to Databases: A Quick Start Guide

Gabriel Mbuva — Sun, 05 Jul 2026 09:39:23 +0000

Power BI is a data visualization software that helps users connect to a wide variety of data sources without undergoing tedious manual extraction processes. Databases are one of the most used forms of data storage. Connecting them directly to Power BI helps organizations ensure accuracy across reports, establish a secure data stream, and deliver real-time insights.

Types of Databases

Before making a connection, it is important to understand the two main types of databases:

Local Database- It stores data directly on a user's device, allowing applications to work offline without needing an active internet connection.

Cloud-based Database- It stores data on remote internet servers, allowing multiple users to securely access, sync, and update the exact same data from anywhere.

How to connect Power BI to a database

Before connecting Power BI to any database, ensure that the database server is running, you have the required connection details, and Power BI Desktop is installed on your computer. The connection process differs slightly depending on whether you're using a local database or a cloud-hosted database, but the overall workflow remains similar.

How to Connect Power BI to a Local SQL Database

Connecting Power BI to a local SQL database enables you to analyze data stored on your computer or within your organization's network. Instead of exporting data into Excel every time you need to create a report, Power BI can connect directly to the database, making reporting more efficient and reducing the risk of using outdated data.

Step 1: Open Power BI Desktop

Launch Power BI Desktop and click Get Data on the Home ribbon.

Step 2: Select SQL Server Database

From the list of available data sources, search for SQL Server Database, select it, and click Connect.

This connector allows Power BI to communicate directly
with Microsoft SQL Server databases.

Step 3: Enter the Database Details

In the connection window, provide your SQL Server details:

Server: The name or IP address of your SQL Server instance.
Database: Specify the database you want to connect to. If left blank, Power BI will display all databases you have permission to access.

Next, choose the connectivity mode:

Import – Imports the data into Power BI, providing better performance for most reports.

DirectQuery – Retrieves data directly from the database whenever a report is refreshed, making it suitable for frequently updated datasets.

Click OK once you've entered the required information.

Step 4: Authenticate

Choose the appropriate authentication method for your SQL Server.

Most users will use either:

Windows Authentication, which uses your Windows credentials.
Database Authentication, which requires a database username and password.

After entering the necessary credentials, click Connect.

Step 5: Load the Data

Once the connection is successful, the Navigator window displays all available tables and views.

Select the tables you want to analyze and choose one of the following options:

Load to import the data directly into Power BI.
Transform Data to clean, filter, or reshape the data in Power Query before loading it into your report. Your local SQL database is now connected to Power BI and ready for analysis.

How to Connect Power BI to a Cloud-Based SQL Database

Cloud-hosted databases work similarly to local databases but require an internet connection and, in many cases, additional security measures such as SSL encryption. The following steps demonstrate how to connect Power BI to a cloud-hosted PostgreSQL database securely.

Step 1: Import the Dataset into Your Cloud Database

Before connecting Power BI, ensure your dataset has already been imported into your cloud-hosted PostgreSQL database.

Using a database management tool such as DBeaver, connect to your database, create the required tables if necessary, and import your dataset.

Once the import is complete, verify that the data appears correctly in the database.

Step 2: Download the SSL Certificate

Many cloud database providers secure connections using SSL encryption.

To obtain the required certificate:

Open your cloud database provider's dashboard.
Navigate to your PostgreSQL database service.
Download the SSL (CA) certificate provided for your database.

This certificate enables Power BI to verify the identity of the database server and establish a secure encrypted connection.

Step 3: Connect Power BI to PostgreSQL

Open Power BI Desktop and click Get Data.

Search for PostgreSQL Database, select it, and click Connect.

Enter your:

Hostname
Port
Database name

These connection details can be found in your cloud database provider's dashboard.

Step 4: Configure SSL

When prompted, configure the SSL settings by selecting the downloaded SSL (CA) certificate.

Using SSL encrypts all communication between Power BI and your PostgreSQL database, helping protect sensitive business data during transmission.

Step 5: Authenticate

Choose Database Authentication and enter the PostgreSQL username and password for your database.

Click Connect.

Step 6: Select and Load Your Tables

After Power BI successfully establishes a secure connection, the Navigator window will display all available tables.

Select the required tables and click Load.

If your data needs cleaning or transformation, choose Transform Data instead.

Your cloud-hosted PostgreSQL database is now connected to Power BI, allowing you to build reports using centralized data that can be updated without repeatedly importing files.

Benefits of Connecting Power BI Directly to SQL Databases

Connecting Power BI directly to a SQL database offers several advantages over manually importing spreadsheets:

It minimizes manual data handling and reduces human error.
Reports can be refreshed whenever the underlying database changes.
Data remains centralized, ensuring consistency across multiple reports.
Secure authentication and SSL encryption help protect sensitive information.
Organizations can build scalable reporting solutions that grow alongside their data.

Conclusion

Whether your data is stored locally or in the cloud, Power BI makes it easy to connect directly to SQL databases and transform raw data into meaningful insights. Local databases are ideal for offline or internal environments, while cloud-hosted databases provide secure, centralized storage that supports collaboration, remote access, and real-time reporting across multiple users.

By connecting Power BI directly to your database instead of relying on exported files, you create a more reliable reporting workflow, improve data accuracy, and make it easier to generate real-time dashboards that support better business decisions.

Power BI: Modelling, Schemas, Joins and Relationships

Gabriel Mbuva — Mon, 29 Jun 2026 06:03:40 +0000

Power BI is a data visualization and business intelligence tool designed for business professionals with varying levels of data knowledge. Power BI's dashboard can be used to report and visualize data in a wide range of styles, including charts, graphs and more. It allows everyone from beginners to experts to easily see what is happening in their business and make faster, smarter decisions.

Data Modelling

Data modelling is the process of organising, structuring, and defining relationships between your data table. It uses diagrams, symbols, and textual definitions to visually represent how data is captured, stored and used.

Reasons for data modelling
Seamless Data Exploration: Builds clear hierarchies and drill-down paths. This lets report consumers quickly navigate tables to uncover hidden trends.

Maximum Query Performance: Directly dictates how fast your reports load. Clean modeling prevents redundant data and messy relationships from delaying visual renders.

Guaranteed Report Accuracy: Secures the consistency, dependability, and integrity of your source records. This ensures decision-makers are always working with precise insights.

Simplified DAX Writing: Keeps your Data Analysis Expressions short and clean. Well-structured tables eliminate the need for complex, unmaintainable override formulas.

Schemas

Schemas refer to the logical structure and organization of your data model. In Power BI we have two types of schemas; Star and snowflake schema.

Star Schema: Is the most widely recommended and optimal design in Power BI. The center of the star is a Fact Tables which contains measurable, quantitative data, for example, sales revenue, quantity sold, or transaction amounts. The fact tables contain foreign keys that link to dimension tables. Dimension tables form points of the star. They provide descriptive context for the facts. Dimension tables contain primary keys and are used to filter and group data in your reports.

Snowflake Schema: Is an extension of the star schema. In a snowflake schema, dimension tables are normalized, which means they are broken down into further sub-dimension tables. It reduces data redundancy but requires more complex relationships between tables, which can decrease query performance in Power BI.

Joins

A join is a method used to merge tables together to combine information based on a shared column.
Different types of joins in Power BI include:

Left Outer: Returns all rows from the left table and matching rows from the right table.

Right Outer: Returns all rows from the right table and matching rows from the left table.

Full Outer: Returns all rows from both tables, regardless of whether there is a match.

Inner: Returns only the matching rows present in both tables.

Left Anti: Returns only rows from the left table that have no match in the right table.

Right Anti: Returns only rows from the right table that have no match in the left table.

Relationships

Relationships are logical connections between tables based on shared columns. They are the foundation for a data model, enabling visuals to slice, filter, amd aggregate data seamlessly across multiple data sets.

Types of Relationship Cardinality
Cardinality defines the nature of the relationship between two tables:

One-to-Many: The most common and recommended relationship. One row on the "one" side relates to multiple rows on the "many" side . Filters flow strictly from the "one" side to the "many" side.

Many-to-Many: Occurs when both tables have multiple rows matching the shared key. These are generally not recommended because they create unpredictable filter paths, unreliable totals, and performance bottlenecks.

One-to-One: Both tables contain only one unique row for the related key. Used infrequently; usually suggests the data should have been merged into a single table initially.

How Excel is Used in Real-World Data Analysis

Gabriel Mbuva — Fri, 05 Jun 2026 06:42:53 +0000

Introduction

Excel is one of the most used tools for data analysis. It allows beginners like myself to easily clean, organize, analyze and visualize data.Excel enables users to work with large datasets and extract meaningful insights without requiring advanced technical skills.

What is Excel

Excel is a spreadsheet that allows you to collect, organize, analyze, calculate, and visualize data efficiently.Despite the emergence of other data analysis tools like SQL and Power BI, Excel remains one of the most widely used tools for both personal and professional data management.This can be credited to its ease of access, learning, and use.

Ways Excel is used in real-world data analysis

This week, I had the opportunity to explore how Excel is used in real-world data analysis.I discovered that Excel is not just a basic spreadsheet tool, but a powerful application that helps make sense of data and support decision-making.

Data organization and cleaning
Excel is used to structure raw data, remove duplicates, and fix errors. This improves data quality, making it easier to analyze and more reliable for decision-making.This improves data quality, making it easier to analyze and more reliable for decision-making.

Financial reporting
Excel is commonly used in finance to create budgets, calculate profits and losses, and monitor expenses.It helps organizations keep accurate financial records and understand their financial situation.

Business decision-making
Businesses use Excel to track sales, compare performance over time, and identify trends.This helps managers understand what is working well and what needs improvement.

Excel features and formulas

In just a week, I have learned several Excel formulas that simplify data management and make working with data more efficient.

SUM function
The SUM function is used to add a range of values together in Excel, making it one of the most essential tools for quick calculations.It's used to automatically add a range of numerical values together, eliminating the need for manual calculations. This not only saves time but also reduces the chances of errors when working with large datasets.

Data validation
Data validation in Excel is a feature used to control what type of data can be entered into a cell. It helps ensure that only correct and meaningful information is added to a dataset, reducing errors and improving data quality.

Conditional formatting
Conditional formatting is a useful Excel feature that automatically changes how cells look based on their values. It helps highlight important information in a dataset, making patterns and trends easier to see at a glance.

IF and IFS functions
The IF function in Excel is used to make decisions based on a condition. It checks whether something is true or false and then returns a result.The IFS function is used when there are multiple conditions.Instead of using many IF statements, it allows you to test several conditions in one formula.

Reflection

Learning Excel has completely changed how I understand and interact with data.At first, I used to see spreadsheets as simple grids filled with numbers that didn’t really mean much.Now, I view them as powerful tools that reveal patterns and support decision-making when used correctly.As I worked more with Excel, I became more comfortable cleaning datasets, organizing information, and drawing meaningful insights from raw data. Features like formulas, data validation, and conditional formatting made it easier for me to structure data properly and quickly identify important trends. I also learned to approach problems more logically by breaking down datasets into smaller, manageable parts during analysis.Excel has given me a strong foundation in data analysis and it has motivated me to keep improving as I move toward more advanced tools and techniques in the field.