DEV Community: Steve Mwangi

A practical journey into SQL

Steve Mwangi — Mon, 20 Jul 2026 10:00:57 +0000

What is SQL (Structured Query Language)

SQL is the standard programming language used to manage and query data in relational database management systems (RDBMS).

SQL enables users to: retrieve data, insert new data, update existing data, delete data, create database structures, modify database structures, control access to data and manage transactions.

Users utilize SQL through commands.

SQL commands

SQL commands are grouped into 5 categories

DDL (Data Definition Language):
Used to manage database structure.

Key commands include:
CREATE used to create tables/databases,
ALTER used to modify tables,
DROP used to delete tables,
TRUNCATE used to remove all data but keeps structure.

DML (Data Manipulation Language):
Used to manage data within tables.

Key commands include:
INSERT used to add data into rows,
UPDATE used to make edits to existing data,
DELETE used to remove data from the database.

DQL (Data Query Language):
Used to retrieve data.

The primary command is SELECT, often used with clauses like
WHERE used to apply filters,
GROUP BY used to aggregate data,
ORDER BY used to sort data,
JOIN used to combines tables.

DCL (Data Control Language):
Used to control access permissions.

Key commands include:
GRANT used to give users data access permissions,
REVOKE used to remove data access permissions.

TCL (Transaction Control Language):
Used to manage transactions.

Key commands include:
COMMIT used to saves changes made to the data,
ROLLBACK used to undo changes made to the data,
SAVEPOINT used to mark a point for rollback.

SQL data types

When using SQL, each column can only hold one type of data thus it is important to define the columns' data type.

Data types define the kind of data that can be stored in each column of a table.

Data types are declared when creating a table, where each columns data type has to be set.

They include
Numeric data types

Data Type	Description
SMALLINT	Stores small whole numbers
INTEGER / INT	Stores standard whole numbers
BIGINT	Stores very large whole numbers
SERIAL	Auto-incrementing integer
BIGSERIAL	Auto-incrementing large integer

Character or string data types

Data Type	Description
CHAR(n)	Fixed-length text
VARCHAR(n)	Variable-length text with a maximum limit
TEXT	Variable-length text with no strict short limit

Date and time data types

Data Type	Description
DATE	Stores date only
TIME	Stores time only
TIMESTAMP	Stores date and time
TIMESTAMPTZ	Stores date and time with time zone

Boolean data type
Used to store True or False values.

Importance of SQL data types:
Using the correct data type enables one to:

Ensure correct data entry
Prevent invalid values
Improve storage efficiency
Improve query performance
Support accurate calculations
Maintain consistency across the database

SQL constraints

A SQL constraints are rules applied to database columns or tables to enforce data integrity, accuracy, and reliability by limiting the type of data that can be inserted, updated, or deleted.

SQL constraints are set for the column during table creation along with the data type.

Common SQL constraints include:

Constraint	Description
NOT NULL	Ensures a column cannot have a `NULL` (empty) value.
UNIQUE	Ensures all values in a column are distinct, preventing duplicates.
PRIMARY KEY	Uniquely identifies each row in a table.
FOREIGN KEY	Establishes a link between data in two tables by referencing a primary key in another table, ensuring referential integrity.
CHECK	Validates that values in a column satisfy a specific condition.
DEFAULT	Sets a default value for a column if no value is specified during insertion.

Bringing it all together:

In order to illustrate how SQL commands, data types and constraints merge together to work with data we will use SQL to create a database for a school and perform some SQL queries.

Greenwood Academy

SECTION A - Building the Database (DDL)

The project starts with creating the database schema named greenwood-academy that will contain all our tables and setting the search path to our schema to ensure all our commands are executed in it.

Creating the schema

create schema greenwood_academy;

set search_path to greenwood_academy;

Creating the tables

Now we create our students, subjects and exam results tables while setting the data types and the needed constraints for each column.

Creating students table:

create table greenwood_academy.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)
    );

Creating subjects table:

create table greenwood_academy.subjects(
    subject_id      INT          primary key,
    subject_name    VARCHAR(100) not null unique,
    department      VARCHAR(50),
    teacher_name    VARCHAR(100),
    credits         INT
    );

Creating exam results table:

create table greenwood_academy.exam_results(
    result_id INT primary key,
    student_id INT not null references greenwood_academy.students(student_id),
    subject_id INT not null references greenwood_academy.subjects (subject_id),
    marks INT not null,
    exam_date DATE,
    grade VARCHAR(2)
);

Modifying the table structure

We can use some DDL commands to alter the tables after creation, such as renaming the credits column in the subjects table to credit_hours.

alter table greenwood_academy.students 
add column phone_number VARCHAR(20);

SECTION B - Filling the Database (DML)

Now that we have our tables with the columns set, we need to insert data into the tables.

Inserting data into the tables

Inserting data into students table:

insert into greenwood_academy.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 the subjects table:

insert into greenwood_academy.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 data into the exam results table:

insert into greenwood_academy.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')
;

Modifying table contents
We can use SQL commands to modify data in our tables, such as updating the location of student Esther Akinyi, whose student ID is 5, from Nakuru to Nairobi.

update greenwood_academy.students
set city = 'Nairobi'
where student_id = 5;

SECTION C - Querying the Data

Now that we have all the data we need, we can proceed to utilize SQL to query and pull insights from the data through filters.

Such as:
Using a query to find all the students who are in form 4.

select s.first_name, s.last_name, s."class" 
from greenwood_academy.students s 
where class = 'Form 4';

Using a query to find all the exam results where marks are greater than or equal to 70.

select * 
from greenwood_academy.exam_results er 
where er.marks >= 70
order by marks asc;

SECTION D - Range, Membership & Search Operators

We can further use SQL operators to filter data based on certain conditions.
Such as:
Using a query to find all exam results where marks are between 50 and 80 (inclusive).

select er.result_id, er.marks, er.grade 
from greenwood_academy.exam_results er 
where er.marks between 50 and 80;

Using a query to find all students who live in Nairobi, Mombasa, or Kisumu

select s.first_name, s.last_name, s.city  
from greenwood_academy.students s 
where city in ('Nairobi', 'Mombasa', 'Kisumu')
order by s.student_id ;

SECTION E - COUNT

We can use SQL to count based on certain criteria.
Such as:
Using a query to find out how many students are currently in Form 3.

select count (*) as form_three_students_count
from greenwood_academy.students s 
where class = 'Form 3';

SECTION F - CASE WHEN

We can futher use SQL to create labels for data based on certain conditions.
Such as:
Using a query to label each exam result with a grade description:

select subject_id, marks, grade ,
case 
    when marks < 40 then 'Fail'
    when marks between 40 and 60 then 'Pass'
    when marks between 61 and 79 then 'Merit'
    else 'Distinction'
end
as performance
from greenwood_academy.exam_results er 
order by marks asc;

Conclusion

Understanding how SQL works is important and working with the basics provides key skills in anyone who wants to work with data. The Greenwood academy project provides a simple example on the basics on SQL and explores the SQL commands, data types and constraints illustrating how they are used to create a relational database.

I have provided a repository for the Greenwood Academy project on my GitHub page here, where further SQL Queries have been performed with a readme file that outlines all the queries in the project.

How to connect to a local vs online PostgreSQL database. A quick guide.

Steve Mwangi — Sun, 05 Jul 2026 15:01:00 +0000

In this article I will provide a step by step guide on how to connect to a local PostgreSQL database and a PostgreSQL database hosted on an online service, in particular I will be using Aiven to host and connect to the online database.
I will use powerBI to illustrate how to connect and pull data from both the local and online database.

This guide presumes one is familiar with PostgreSQL, DBeaver, powerBI and Aiven. You must have PostgreSQL, DBeaver and powerBI installed and set up properly in your device, as well as a running Aiven service.

Connecting to local database

Connecting to the local PostgreSQL database:

step 1
Start by creating a new database connection through DBeaver and select PostgreSQL from the dropdown menu.

step 2
Enter the connection details prompted ensuring you enter the correct username and password that you set up when installing PostgreSQL in your device.
For a local PostgreSQL database the host is localhost and the port number 5432.

Ensure you test your connection before you finish setting up your connection.

step 3
With the connection established, it it time to create a new schema in your database.
To view the schemas on your database open through:

postgres *(localhost:5432)> Databases > postgres > Schemas

When data is imported into a postgres database with no schema specified, the data will be pushed to the default public schema.
In order to avoid this and to ensure our data is properly arranged, we will create a new schema where we will store our data.
Right click on Schemas and select create new schema.

step 4
To import data into our schema, right click on the created schema and select import data.

Browse through your local files, and select the file you want to import into your local database, then click proceed until the importing process completes.

Connecting powerBI to local PostgreSQL database

Now that we have created a new local database connection and imported data into it, we can open up powerBI and pull the data in our database into powerBI where we can clean, model and perform analysis on it.

step 5
Start with opening a blank report on powerBI and select get data from the HOME ribbon.

Search for PostgreSQL in the data source, and select PostgreSQL database.

step 6
Fill in the connection details in the connect to data source page. Ensure these details match with the details you used when making the database connection through DBeaver.

At this point you should have successfully connected your local database to powerBI and a powerBI should display a list of all the schemas in your local database, from where you can select the file you want to work on and load it into powerBI.

Connecting to an online database.

To connect to a database hosted on an online service, I will start by guiding on how to create a PostgreSQL database connection to an existing Aiven service.
For any of the steps from this point to work, you must note that your Aiven service must be powered on and running.

step A
Begin with creating a connection to the running Aiven service through DBeaver. This process is similar to Step 2 when connecting to a local database, however, it differs in that you must use the connection details provided by your Aiven service.

Copy and paste the connection details from your Aiven service into the required slots, then test your connection to ensure it functions properly before finishing setting up the connection.

step B
With your connection established, you can use the steps 3 and step 4 shown above when discussing working with the local database to create a new schema and import data into your online database.

step C
Before we attempt to connect powerBI to our database, we must ensure we install our certificate, to ensure our connection works properly.
Go to your Aiven service overview and download the CA certificate provided.

Go to the search bar on your windows task bar and search for manage user certificates and open it.

Open the Trusted Root Certification Authorities folders, and right click on the Certificates folder. Click on the ALL Tasks on the pop up then click on Import.

Click next, then browse. Change the file type to all types on the file explore and select the CA certificate you had downloaded, click next until the process completes.

step D
Now its time to access our data through powerBI.
Similar to step 5 open a blank report in powerBI and select get data from the HOME ribbon.
Search for PostgreSQL in the data source, and select PostgreSQL database.

step E
This time you will insert the connection details provided on your Aiven service when setting up the connection settings.

NOTE:
The server detail is filled by inserting the host provided in Aiven, adding a full colon, then adding the port number provided by Aiven.

HOST:port number

Failure to setup the host in this manner leads to a connection failure.
Ensure not to add any blank spaces in the server details.

AT this point powerBI should list the schemas in your Aiven database, you can now select a file to import into powerBI.

The fundamentals of data modelling through Power BI

Steve Mwangi — Thu, 02 Jul 2026 15:53:52 +0000

Power BI is a suite of business analytics and data visualization tools developed by Microsoft. It allows you to connect to hundreds of data sources, clean and model raw data, and transform it into interactive, shareable dashboards and reports.

Data Modelling

A data model is a collection of tables, relationships and calculations that represent the underlying structure of the data. The data model defines how data is stored, how different data entities relate to each other and how calculations are performed.

Reasons for data modelling

Supports Data Exploration - Data modeling enables users to create hierarchies and drill-down paths, which support efficient data exploration.
Affects Performance - How a data model is designed directly impacts the speed and efficiency of data retrieval.
Promotes More Accurate Reports - When a data model is designed correctly, it can guarantee the accuracy, consistency, and dependability of the data used in reports.
Easier Future Maintainability - Creating reusable components, improving documentation, and standardizing data can also ensure that reports are easy to maintain and update over time.

There are three common data models:

1. FLAT TABLE
This is a data model where all the all the data is contained in a single table.
This is the most basic data model, and only recommended for one-off analysis.
Using a flat table can result in functional issues especially when the data scales in volume.

2. STAR SCHEMA
The star schema is a well-established data modeling technique. This approach requires breaking down the data into a facts table and related dimension tables. All of the dimension tables must have a direct relationship to the fact table.

Facts table is the table that stores the transactions that occurred in the data set.
It answers the question "What happened?"
Dimension table is a table that contains explanatory data of the information being presented through the transactions.

The facts table and dimension tables are linked through primary and foreign keys.
A primary key is a unique, non-blank column that identifies the rows in a table, whereas a foreign key is a column that references the primary key of a different table.

The star schema is considered better than a flat table since:

It reduces repetition of data therefore less resource intensive.
Clearly outlines relationships among the data entities thus easier to understand and perform operations on the data.

3. SNOWFLAKE SCHEMA
This model is a further breakdown of the star schema where not all dimension tables have a direct connection to the facts table.
The dimension tables where data is further repeated are broken down further into logical sub-dimensions.

I have mentioned that the facts and dimension tables are connected and it is now time to explore what these connections are and how they work.

HOW RELATIONSHIPS WORK IN POWER BI

Relationship is the connection between tables through common columns, i.e. foreign keys.
Relationships in Power BI are illustrated through cardinality i.e. how rows in one table relate to rows in another table.
These are:
ONE TO ONE (1:1)
This is where column in one table has only one instance of a value and the other related table has only one instance of the value, such that each row in a table matches exactly one row in the other table.

ONE TO MANY (1:*)
This is the most common relationship. It occurs when one value in the first table can be related to many records in the second table, such that one value in a dimension table matches to multiple values in the facts table.

MANY TO MANY (:)
This relationship occurs when multiple records in the first table can be related to many records in the second table. This relationship indicates the data model needs to be restructured properly.

Cross-filter direction
Cross-filter direction determines how filters move between tables.
Single direction means filters flow from the dimension table to the fact table.
Both directions allow filters to flow both ways between tables.
Single direction is recommended in most models because it is:

easier to understand
less error-prone
aligned with star schema logic

Bidirectional filtering can be useful in specific scenarios, but it also increases the risk of ambiguous filter paths and unexpected results.

Active vs inactive relationships
Power BI allows multiple relationships between the same tables, but only one can be active at a time.
Active relationships are used automatically by visuals whereas inactive relationships exist in the model but are ignored unless explicitly activated in a measure.

MERGE QUERIES

Data modelling also involves operating on the structure of the tables in order to end up with an efficient model. This can be achieved through merge queries.

A Power BI merge query is an operation that joins two existing tables together based on matching values from one or more columns.

Types of merge queries

INNER JOIN
This operation returns only the matching values form both tables based on the foreign key.

FULL JOIN/FULL OUTER JOIN
This operation returns all the rows from both tables and fills null values where there is no match.

LEFT OUTER JOIN
This operation returns all rows from the table placed on the left and returns only the matching rows from the table placed on the right.

RIGHT OUTER JOIN
This operation returns all the rows from the table placed on the right and returns only the matching rows from the table placed on the left.

LEFT ANTI JOIN
This operation brings in only the row form the table placed on the left that do not have any matching rows from the table placed on the right.

RIGHT ANTI JOIN
This operation brings in only the row form the table placed on the right that do not have any matching rows from the table placed on the left.

CONCLUSION

Data modelling is the foundation for any data analysis. A proper model simplifies the data into an easily understandable structure that can be used to perform calculations, perform accurate analysis and generate reports.
One has to master the foundations such as differences between data models, how to select the proper model for a dataset, how cardinality works and how relationships can be best used within a model and how to use merge queries in order to properly utilize data.

How Excel is Used in Real-World Data Analysis

Steve Mwangi — Sat, 06 Jun 2026 15:06:41 +0000

I have begun my journey as a beginner in the data analysis field by learning excel. I have previously used excel to store data but never really considered put my mind on how excel is used in the broader perspective of data analysis and the process it takes to collect, organize, validate and process data to extract information.

This journey began with a simple question.

What is Excel?

Excel is an electronic spreadsheet software created by Microsoft. A spreadsheet is an application software used to organize, analyze and present data in an intersecting two-dimensional grid of rows and columns.

Excel has become an industry standard for handling data as it provides a variety of tools and features for data collection, organization, visualization and analysis.

Excel features

Excel provides for one to operate on data through functions/formulas.
A function is a special routine built into the worksheet that enable one to perform complex tasks in a standardized manner.

Form of an excel function

An excel function is composed of four parts:

Equal sign (=) - Used to initialize the formula.
Function name - It is a descriptive term that identifies the operation to be performed.
Parenthesis () - used to contain the function arguments.
Arguments - Outlines the range of cells the formula will act on and the conditions to be imposed.

  =Function name(arguments)

Excel functions can be classified into:

Statistical functions
Used to perform statistical computations such as mean, median and mode.

=COUNT(Range)
=COUNTIF(Range, "criteria")
=AVERAGE(Range)
=AVERAGEIF(Criteria range, "criteria", Average range)

Aggregate functions
Used to perform arithmetic, conditional sums and products, exponents and logarithms operations.

=SUM(Sum cell range)
=PRODUCT(Product cell range)
=SQRT(Square root cell range)

Text functions
Used to perform operations on text data.

=LOWER(Range)
=UPPER(Range)
=CLEAN(Range)

Excel as a data analytic tool

After getting a proper understanding of what excel is and how to use it, the next step is to learn how we use these tools to perform data analysis. However, we must begin with knowing what data analysis is.

Data analysis is the process of inspecting, cleaning, transforming and modelling data with the goal of discovering useful information, informing conclusions and supporting decision making in an organization.

Excel is utilized to achieve this in a variety of sectors such as the healthcare, financial, marketing environment, etc. to perform various data analytic operations such as:

Descriptive Analysis
This is analysis that is done to figure out what happened. This is used in the health sector in situations where it is necessary to track the infection rates of communicable diseases in a region region, such as during the covid-19 pandemic where infection records were tracked on a daily basis.

Diagnostic analysis
This is the use of excel functions to go through data to determine why something occurred. This is used in the business sector to outline changes in the business operations, such as changes in the volume of sales, through excel data presentation tools such as charts and graphs.

Predictive Analysis
This involves the use of excel to form projections about the future based on the data. This is used in financial sector to produce possible future investment predictions in a market that is being considered. This is achieved in excel through What-If analysis and Forecast sheets.

Personal reflection

I have spent a week going through excel and learning on how to use it as more than a spreadsheet to hold data. This has led me to discovering the various ways excel provides to engage with data through formulas, sorts, merges and filters to view and produce information for my needs. I have learnt how to validate and clean data so as to ensure data meets the analytic standards.

I believe understanding the use of excel as a data analysis tool is a valuable skill for me as I continue my journey as a data analyst and will provide me with a solid foundation to work with other data tools and platforms in the future.