DEV Community: Young Odhiambo

Python Basics, Operators and Conditional Statements

Young Odhiambo — Wed, 23 Sep 2026 16:26:59 +0000

Introduction

Python is a high-level programming language with a simple and readable syntax. It uses an intuitive syntax that closely resembles English, making it the perfect starting point for beginners.

Common Uses

Python is a versatile, high-level programming language used widely for artificial intelligence, web development, data analysis, and task automation:

Web Development: Building server-side applications with frameworks like Django and FastAPI.
Data Science & Machine Learning: Processing numbers and training models using Pandas, NumPy, and PyTorch.
Automation: Writing simple scripts to handle repetitive daily file management or data entry tasks

Getting Started

Print()

print() is a built-in function that outputs text or variables to the screen

print("Hello world")

Output

Hello world

Comments in Python

Comments are lines in a program that are not executed by the interpreter. They are used to explain code and make it easier to read and understand.

# Single line comment

"""
This is a 
multi- line comment
or doctrines

"""

# : Denotes a single-line comment.
""" """ or ''' ''' : Triple quotes are used for multi-line comments or docstrings.

Variables

Variables are names that store data in memory. They are created when a value is assigned, with the name referencing that value. It can be text, integer or a float.

# create variables
name = 'Mosses Njuguna'
age = 25
city = 'Nairobi'

# use the variable in print
print(name)
print(age)
print(city)

Output

Mosses Njuguna
25
Nairobi

Data Types in Python

Data types define the kind of data a variable can hold and determine the operations that can be performed on it.

Example of data types in python are:

1. string (str)

Strings are surrounded by either single quatation marks or double quatation.

name = 'Fatuma Waridi'
city = 'Nairobi'

2. integer (int)

Whole numbers, no quattion

age = 22
score = 87

3. float

Float are numbers containing a decimal point, also they are not put inside quatations.

height = 1.68
balance = 15750.50

4. Boleans (bol)

These are True of False values. The are also not put inside the quatations and the first letters must be capital.

is_student = True
has_id_card = False

Type Casting in Python

Type casting is the process of changing a variable from one data type to another in Python.

# converting string to integer
text_number = '42'
real_number = int(text_number)
print(real_number + 8)

# converting number to a string
age = 24
age_str = str(age)
print(age_str)

# convert a string to a float
txt_price = '99.99'
price = float(txt_price)
print(price)

F-String

F-strings (formatted string literals) allow you to embed variables and expressions directly inside strings.

name = 'Mosses'
age = 29
city = 'Nairobi'

print(f'{name} is {age} years old and lives in {city}')

Input()

The input() function is used to take input from the user. By default, the value entered by the user is stored as a string.

# ask the user for their name

name = input('what is your name')
print(f'Hello {name}')

Python Operators

Operators in Python are symbols used to perform operations on values and variables, such as calculations, comparisons, and logical checks.

1. Arithmetic Operators

These are used to perform basic mathematical operations like addition, subtraction, multiplication, and division.

a = 15
b = 4

print("Addition:      ", a + b)
print("Subtraction:   ", a - b)
print("Multiplication:", a * b)
print("Division:      ", a / b)
print("Floor division:", a // b)
print("Modulus:       ", a % b)
print("Power:         ", a ** b)

Output

Addition:       19
Subtraction:    11
Multiplication: 60
Division:       3.75
Floor division: 3
Modulus:        3
Power:          50625

2. Comparison Operators

These are used to compare two values. They return a Boolean value either True or False depending on whether the comparison is correct.

a = 10
b = 20

print(a == b)   # False, because 10 is not equal to 20
print(a != b)   # True, because 10 is not equal to 20
print(a > b)    # False, 10 is not greater than 20
print(a < b)    # True, 10 is less than 20
print(a >= b)   # False, 10 is not greater than or equal to 20
print(a <= b)   # True, 10 is less than or equal to 20

Output

False
True
False
True
False
True

3. Logical Operators

It perform Logical AND, Logical OR and Logical NOT operations. It is used to combine conditional statements.
Types of logical operators are: AND, OR, NOT.

a = True
b = False
print(a and b) 
print(a or b) 
print(not a)

Output

False
True
False

4. Membership Operators

Used to test if a sequence (like a string, list, or tuple) is present in an object.
in : Returns True if a sequence with the specified value is present in the object (e.g., 'a' in 'apple' → True).

not in : Returns True if a sequence with the specified value is not present in the object.

Python If Else Statements - Conditional Statements

If-Else statements are conditional statements used to perform decision-making in a program. They allow different blocks of code to execute based on whether a condition is True or False.

1. If Statement

The if statement is used to execute a block of code only when a given condition is True. If the condition is False, the code inside the if block is skipped.

age = 20

if age >= 18:
    print('Your an adult') # runs because the condition is True

2. if-Else Statement

The if-else statement is used to execute one block of code when a condition is True and another block when the condition is False.

age = 16

if age >= 18:
    print("You're eligible to vote")

else:
    print("You're not eligible to vote")  # runs the esle statement because the condition is False

3. Nested If-Else Statement

A nested if-else statement is an if-else structure placed inside another if or else block. It is used to check multiple conditions step by step and execute code based on those conditions.

age = 20
citizenship = 'Kenyan'

if citizenship == 'Kenyan':
    print('Can own a kenyan ID')

    if age >= 18:
        print("You're eligible to have an ID")
    else:
        print("You've not attained the minimum age to have an ID")

else:
    print("You can't own a Kenyan ID")

Conclusion

Understanding Python basics, variables, data types, input/output, operators, and conditional statements provides a strong foundation for programming. These concepts make it possible to write programs that store and process information, perform calculations, make decisions, and respond to user input.

SQL Window Functions 101

Young Odhiambo — Fri, 11 Sep 2026 16:02:01 +0000

Introduction.

Window functions in SQL are powerful tools used to perform calculations across a specific "window" of rows related to the current row. Unlike aggregate functions (like SUM(), AVG(), COUNT()), which collapse multiple rows into a single result, window functions retain individual rows while adding calculated values.

They are commonly used for tasks like aggregates, rankings and running totals. The OVER clause defines the “window” of rows for the calculation.

It can:

PARTITION BY: It divides the data into groups using PARTITION BY.
ORDER BY: It specifies the order of rows within each group using ORDER BY. With this, functions such as SUM(), AVG(), ROW_NUMBER(), RANK() and DENSE_RANK() can be applied in a controlled way.

Types of Window Functions

SQL window functions are mainly of two types:

Aggregate window functions
Ranking window functions.

1. Ranking window functions

These functions provide rankings of rows within a partition based on specific criteria.
Common ranking functions include:

ROW_NUMBER() Assigns a unique sequential number to each row.

select 
    t.driver_id, 
    t.trip_id,
    t.trip_date, 
    row_number() over(partition by driver_id) as fare_rank
from safari.trips t;

RANK()

Assigns a rank, with ties receiving the same rank and gaps appearing.

select
      d.driver_id,
      d.driver_name,
      rank() over(partition by driver__id) as driver_rank
from safari.drivers d;

DENSE_RANK()

Similar to RANK(), but without gaps in ranking.

select
      name, 
      department, 
      salary,
      dense_rank() over (partition by department order by salary desc) as emp_dense_rank
from employee;

NTILE(n)

Distributes rows into n buckets.

select
      t.rider_id,
      sum(t.fare) as total_spend
      ntile(4) over (order by total_spend desc) as quartile
from safari.trips t
group by
      t.rider_id;

2. Aggregate Window Functions

Aggregate window functions calculate aggregates over a window of rows while retaining individual rows.
Common aggregate functions include:

SUM()

Running total within a partition.

select 
      t.rider_id,
      t.trip_id, 
      t.fare,
      sum(fare) over(partition by rider_id) as rider_spend
from safari.trips t;

AVG()

Rolling average.

select
    t.rider_id,
    t.trip_id,
    t.fare,
    avg(t.fare) over(partition by t.rider_id) as rider_avg_fare
from safari.trips t ;

COUNT()

Count of rows within a partition.

select 
    t.trip_id,
    t.fare,
    count(*) over() as driv_tt_trip_count
from safari.trips t 
where t.driver_id = 2
order by t.trip_id;

3. Value-Based Functions

These functions return values from other rows in the window.

LAG()

Retrieves a value from a previous row.

select
    t.trip_date,
    t.fare,
    lag(t.fare) over(partition by t.driver_id order by t.trip_date ) as prev_fare
from safari.trips t
order by t.trip_date asc;

LEAD()

Retrieves a value from a subsequent row.

select 
    t.trip_id,
    t.trip_date,
    t.rider_rating,
    lead(t.rider_rating) over(partition by t.rider_id order by t.trip_date) as next_trip
from safari.trips t 
where t.rider_id = 2;

FIRST_VALUE()

Returns the first value in the window.

LAST_VALUE()

Returns the last value in the window

select 
    t.driver_id,
    t.trip_id,
    t.trip_date,
    t.fare,
    first_value(t.fare) over(partition by t.driver_id order by t.trip_date) as first_fare,
    last_value(t.fare) over(partition by t.trip_id order by t.trip_date ) as last_fare
from safari.trips t
where t.driver_id in (2,8);

Conclusion

SQL window functions enhance analytical capabilities without reducing row count. They are commonly used in reporting, trend analysis, and ranking operations.

Mastering SQL Functions: A Beginner’s Guide to Cleaner Code

Young Odhiambo — Fri, 11 Sep 2026 14:25:32 +0000

Introduction

SQL functions are built-in operations that perform calculations, manipulate data, and return results in queries, making data handling simpler without writing complex code.

Common SQL Functions

1. Aggregate Functions
Aggregate functions operate on a set of values and return a single result. They are typically used in conjunction with GROUP BY to aggregate data by groups.

COUNT() Counts the number of rows or non-NULL values in a column.

SELECT COUNT(*) FROM orders;

SUM()

Calculates the total sum of a numeric column.

SELECT SUM(order_total) FROM orders WHERE order_date >= '2026-09-01';

AVG()

Calculates the average value of a numeric column.

SELECT AVG(order_total) FROM orders;

MIN() and MAX()

MIN() returns the smallest value, and MAX() returns the largest value in a column.

SELECT MIN(order_total) AS cheapest, MAX(order_total) AS priciest
FROM orders;

2. String Functions

String functions are used to manipulate and process text data.

CONCAT()

Concatenates two or more strings into one.

select first_name,last_name,
concat(first_name,' ',last_name) as full_name
from students;

SUBSTRING()

Extracts a substring from a string starting at a specified position.

select first_name,
substring(first_name,1,3)
from students;

UPPER() and LOWER()

Converts a string to uppercase or lowercase.

-- syntax UPPER(text)

select first_name,
upper(first_name) as upper_fisrt_name
from students;

-- syntax LOWER(text)

select last_name,
lower(last_name) as lower_last_name
from students;

LENGTH()

Returns the length of a string.

select subject_name,
length(subject_name)as length_subject
from subjects
order by length(subject_name) asc;

REPLACE()

Replace text that matches a regular expression pattern.

-- Remove all non-numeric characters from phone numbers
SELECT phone_number,
       REGEXP_REPLACE(phone_number, '[^0-9]', '') AS clean_number
FROM contacts;

3. Date and Time Functions

Date and time functions are used to manipulate and calculate dates and times.

CURRENT_TIMESTAMP() and NOW()

Returns the current date or time.

-- CURRENT_TIMESTAMP(), NOW() - current day and time 
select now();
select current_timestamp;

EXTRACT(), DATE_PART()

Pulls a single field (year,month,etc) out of a date

select 
    first_name,
    extract(year from date_of_birth) as birth_year,
    extract(month from date_of_birth) as birth_month
from greenwood_academy.students;

3. Numeric Functions

Numeric functions perform operations on numeric data.

ROUND()

Rounds a numeric value to a specified number of decimal places.

select round(45.456,2);

CEIL() and FLOOR()

CEIL() always round up to the next whole number/integer while FLOOR() rounds down up to the next whole number/integer

select 
    result_id,
    marks,
    round(marks/10.0,1)as round_out_of_ten,
    ceil(marks/10.0) as ceil_out_of_ten,
    floor(marks/10.0)as floor_out_of_ten
from greenwood_academy.exam_results er;

ABS()

Returns the absolute value of a number.

SELECT ABS(column_name) AS AbsoluteValue FROM table_name;

Conclusion

SQL functions aren't complicated once you see them as small, single-purpose helpers. Aggregate functions turn many rows into one number. String functions clean up and shape text. Date functions make "when" usable. Window functions let you rank and compare rows without losing the details.

CTE vs. Subquery: The Ultimate Battle for Your Database's Sanity

Young Odhiambo — Tue, 08 Sep 2026 19:23:28 +0000

Introduction

Ever opened a SQL file and felt like you needed a map just to find where the actual query starts? Nested logic is one of the quickest ways to turn clean database code into an unreadable mess.

When you need intermediate calculations, two heavy hitters usually step into the ring: Subqueries and Common Table Expressions (CTEs). While both solve the problem of querying temporary sets of data, they approach it from completely different angles—one by nesting logic inside your clauses, and the other by breaking it out into neat, named steps.

In this guide, we’ll break down what each tool does best, highlight their key differences, and walk through practical examples so you know exactly when to nest and when to use WITH.

What Is a Subquery?

A subquery is a query nested inside another query. The inner query runs first, its result is used by the outer query. Think of it as using the answer to one question as the input to another question.
Subqueries show up in one of three places:

In a WHERE clause, filters rows based on a computed value:

-- Find all orders above the average order value
SELECT order_id, amount
FROM orders
WHERE amount > (
  SELECT AVG(amount) FROM orders
);

The inner query SELECT AVG(amount) FROM orders runs first and returns a single number (say, 450). The outer query then uses that number: WHERE amount > 450

In a FROM clause, acting as a temporary, unnamed table:

-- Find cities where average order value > 500
SELECT city, avg_value
FROM (
  SELECT city, AVG(amount) AS avg_value
  FROM orders
  GROUP BY city
) AS city_stats
WHERE avg_value > 500;

The subquery in FROM creates a temporary table (city_stats) that the outer query treats like a regular table.

In Select clause, Used to compute additional values for each row.

SELECT 
    e1.student_id,
    e1.marks,
    (
        SELECT AVG(e2.marks)
        FROM exam_results e2
        WHERE e2.student_id = e1.student_id
    ) AS student_avg
FROM exam_results e1;

Types of Subqueries

1. Single Row Subqueries:

They return a single row and a single column.
Typically used with comparison operators like =,<,>,…

SELECT *
FROM Clients
WHERE revenue > (SELECT AVG(revenue) FROM Clients);

2. Multiple Row Subqueries:

Return multiple rows and a single column.
They are used with operators like IN, ANY, ALL.

SELECT *
FROM Clients
WHERE country IN (SELECT country FROM Clients GROUP BY country HAVING COUNT(*) > 5);

3. Multiple Column Subqueries

Return multiple columns.
Often used in the FROM clause or with IN or EXISTS.

SELECT client_id, name
FROM Clients
WHERE (country, sales_rep_id) IN
(SELECT country, sales_rep_id FROM Clients WHERE client_id = 1);

4. Correlated Subqueries

A subquery that references columns from the outer query.
They are re-evaluated for each row processed by the outer query.

SELECT c1.client_id, c1.name, c1.revenue
FROM Clients c1
WHERE c1.revenue > (SELECT AVG(c2.revenue) FROM Clients c2 WHERE c2.country = c1.country);

What is a CTE?

A CTE(Common Table Expression) is a temporary named result set defined at the top of your query using WITH, and referenced later as a table for the rest of your query.
It does the same job as a subquery but is written before the main query — making complex logic much easier to read and debug.

Example of a CTE

-- Same query as above, rewritten as a CTE
WITH city_stats AS (
  SELECT
    city,
    AVG(amount) AS avg_value,
    COUNT(*) AS order_count
  FROM orders
  GROUP BY city
)
SELECT city, avg_value, order_count
FROM city_stats
WHERE avg_value > 500
ORDER BY avg_value DESC;

Chaining Multiple CTEs

You can define multiple CTEs and reference earlier ones in later ones:

WITH
monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
),
revenue_with_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
  FROM monthly_revenue
)
SELECT *
FROM revenue_with_growth
WHERE month >= '2026-01-01';

Conclusion

In conclusion, both Common Table Expressions (CTEs) and subqueries are invaluable tools in SQL. CTEs provide a structured and readable way to handle complex queries and recursive data structures, making them essential for advanced SQL operations. Subqueries, on the other hand, offer a flexible and often performance-efficient method for embedding queries within queries. Understanding when and how to use these features can significantly enhance your SQL capabilities.

SQL Joins: How to Connect Tables Without Losing Your Mind

Young Odhiambo — Tue, 08 Sep 2026 18:19:12 +0000

Introduction

Working with databases often means dealing with multiple tables of information. To extract meaningful insights, we need a way to combine related data. That’s where SQL joins come in.

Join is an operation in DBMS(Database Management System) that combines the rows of two or more tables based on related columns between them. The main purpose of join is to retrieve the data from multiple tables in other words Join is used to perform multi-table queries. It is denoted by ⨝.

In this post, we’ll break down the four main types of SQL joins — Inner Join, Left Join, Right Join, and Full Outer Join — using a simple example and visual references.

1. Inner Join

The Inner Join returns only the rows where there is a match in both tables. If the key exists in both Table A and Table B, it will be included in the result.

SELECT A.Key, A.Val_A, B.Val_B
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key;

Result: Only the common keys from both tables.

2. Left Join

The Left Join keeps all the rows from the left table (Table A), and matches them with rows from the right table (Table B). If there’s no match, you’ll see NULL values for the right table’s columns.

SELECT A.Key, A.Val_A, B.Val_B
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key;

Result: All rows from Table A, matched where possible, otherwise NULL.

3. Right Join

The Right Join is the mirror opposite of the Left Join. It keeps all rows from the right table (Table B) and matches them with Table A. Missing values from the left side will show as NULL.

SELECT A.Key, A.Val_A, B.Val_B
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key;

Result: All rows from Table B, matched where possible, otherwise NULL.

4. Full Outer Join

The Full Outer Join returns all rows from both tables, regardless of whether there is a match. If a key exists in only one table, the other side will be filled with NULL.

SELECT A.Key, A.Val_A, B.Val_B
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key;

Result: All keys from both tables, matched where possible, otherwise NULL.

Why SQL Joins Matter?

SQL Joins are fundamental for:

Data analysis
Business reporting
Building APIs
Working with relational databases

Conclusion

Think of SQL joins as the “glue” that binds your data together. Once you understand how they work, you’ll have the power to combine and analyze information across multiple sources with ease.

Building a School Database with PostgreSQL: A Beginner-Friendly SQL Project

Young Odhiambo — Wed, 22 Jul 2026 21:17:50 +0000

Introduction

As part of my SQL learning journey, I built a simple school database called Greenwood Academy using PostgreSQL. The goal of this project was to practice the most important SQL concepts, including database design, inserting data, filtering records, using operators, counting records, and applying conditional logic with CASE WHEN.

In this article, I’ll walk through the project step by step and explain what I learned.

Why This Project?

Real-world organizations store large amounts of structured data. A school, for example, needs to manage:

Students
Subjects
Exam results
Teachers
Academic departments

By modeling this information in a relational database, we can query and analyze it efficiently.

Lets dive in

Step 1 : Creating the Database Schema (DDL)
I started by creating a schema named Greenwood_academy. A schema helps group related database objects together.

create  schema greenwood_academy;
set search_path to greenwood_academy;

Then I defined the three main tables:

Students

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

Subjects

create table greenwood_academy.subjects(
subject_id serial primary key,
subject_name varchar(100) unique,
department varchar (50),
teacher_name varchar (100),
credits int
);

Exam Results Table

create table greenwood_academy.exam_results(
result_id serial primary key,
student_id int not null,
subject_id int not null,
marks int not null,
exam_date date,
grade varchar (2),
foreign key(student_id) references greenwood_academy.students(student_id),
foreign key (subject_id) references greenwood_academy.subjects(subject_id)
);

To make the tables relational we use a Foriegn Keys.

What I learned

SERIAL automatically generates unique IDs.
PRIMARY KEY uniquely identifies each record.
FOREIGN KEY creates relationships between tables.
NOT NULL ensures important fields cannot be left empty.

Modifying tables with ALTER
I also practiced modifying existing tables using the ALTER TABLE statement. In this project, I renamed a column, added a column and dropped a column, which helped me understand how database schemas can be updated as requirements change.

-- Alter table changing type
alter table greenwood_academy.students
add column phone_number varchar(20);

-- Renaming a column
alter table greenwood_academy.subjects
rename column credits to credit_hours;

-- dropping a column
alter table greenwood_academy.students
drop column phone_number;

Step 2 : Inserting Data (DML)
With the structure in place, it's time to add data. INSERT statements can take multiple rows at once, which keeps things concise.

Inserting values into the Students table

insert into greenwood_academy.students(first_name,last_name,
gender,date_of_birth,class,city)
values ('Amina','Wanjiku','F','2008-03-12','Form 3','Nairobi'),
('Brain','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'),
('Esther','Akinyi','F','2009-09-14','Form 2','Nakuru'),
('Felix','Otieno','M','2009-09-14','Form 2','Eldoret'),
('Grace','Mwangi','F','2008-01-22','Form 3','Nairobi'),
('Hassan','Abdi','M','2007-04-09','Form 4','Mombasa'),
('Ivy','Chebet','F','2009-12-01','Form 2','Nakuru'),
('James','Kariuki','M','2008-08-17','Form 3','Nairobi');

Inserting values into the Subjects table

insert into greenwood_academy.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),
('Kiswahili','Languages','Ms. Nduta',3),
('Physics','Sciences','Mr. Kamande',4),
('Geography','Humanities','Ms. Chebet',3),
('Chemistry','Sciences','Ms. Muthoni',4),
('Computer Studies','Sciences','Mr. Oduya',3),
('Business Studies','Humanities','Ms. Wangari',3);

Inserting values into the Exam Results table

insert into greenwood_academy.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'),
(3,2,49,'2024-03-16','D'),
(3,4,71,'2024-03-18','B'),
(4,1,88,'2024-03-14','A'),
(4,6,63,'2024-03-19','C'),
(5,5,39,'2024-03-20','F'),
(6,9,95,'2024-03-21','A');

Once the tables are populated, a quick sanity check confirms everything landed correctly.

select * from greenwood_academy.students;
select * from greenwood_academy.subjects;
select * from greenwood_academy.exam_results;

Data isn't static, though. People move, mistakes happen, and records get cancelled. That's where UPDATE and DELETE come in.

-- Updating Esther Akinyi city from Nakuru to Nairobi. 

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

-- updating the exam_results table
update greenwood_academy.exam_results
set marks = 59, grade = 'C'
where result_id = 5;

-- Deleting result_id 9 
delete from greenwood_academy.exam_results
where result_id = 9;

What I learned

How to insert multiple rows in a single statement.
The importance of matching the order of columns and values.
Using realistic sample data for testing queries

Step 3 : Querying the Data
I used SQL queries to retrieve and filter relevant data from the database for analysis and reporting.

-- all students in form 4
select first_name, last_name
from greenwood_academy.students
where class = 'Form 4';

-- subjects in the sciences department
select subject_name
from greenwood_academy.subjects
where department = 'Sciences';

-- exams results where marks is greater then of equal to 70
select *
from greenwood_academy.exam_results
where marks >=70;

-- Finding all females
select first_name,last_name
from greenwood_academy.students
where gender = 'F';

You can combine conditions with AND and OR to ask more specific questions.

-- students in form 3 and from Nairobi
select first_name,last_name
from greenwood_academy.students
where class = 'Form 3' and city = 'Nairobi';

-- students that are either in form 2 or form 4
select first_name,last_name
from greenwood_academy.students
where class = 'Form 2' or class = 'Form 4';

What I learned

WHERE filters records.
Comparison operators (=, >=, <=) are essential for analysis.
Using AND and OR operators helps understand how to combine multiple conditions to create more accurate and flexible queries.
Selecting only the columns you need improves readability.

Step 4 : Range, Membership & Search Operators
The BETWEEN operator is used to retrieve values that fall within a specified range.

-- marks between 50 and 80 (inclusive)
select *
from greenwood_academy.exam_results
where marks between 50 and 80;

-- exam date between 15th march 2024 and 18th march 2024
select * 
from greenwood_academy.exam_results
where exam_date between '2024-03-15' and '2024-03-18';

IN and NOT IN for membership checks much cleaner than a chain of OR

-- students who live in nairobi,mombasa,or Kisumu
select first_name,last_name
from greenwood_academy.students
where city in ('Nairobi','Mombasa','Kisumu');

-- students not in form 2 or form 3
select first_name,last_name
from greenwood_academy.students
where class not in ('Form 2', 'Form 3');

LIKE for pattern matching % is a wildcard for any characters.

-- students whose first name starts with a or e
select first_name
from greenwood_academy.students
where first_name like 'A%' or first_name like 'E%';

-- subject that contain the word 'studies'
select subject_name
from greenwood_academy.subjects
where subject_name like '%Studies%';

What I Learned

BETWEEN simplifies range filtering.
IN is cleaner than multiple OR conditions.
LIKE is useful for pattern matching.

Step 5 : Counting Records
Counting helps turn raw records into summaries.

-- number of students from form 3
select count(*) as num_students
from greenwood_academy.students
where class = 'Form 3';

-- number of exams with marks 70 and above
select count(*) as num_marks_above_70
from greenwood_academy.exam_results
where marks >=70;

What I learned

COUNT(*) returns the number of matching rows.

Step 6 : Categorizing Data with CASE WHEN
The CASE statement is used to add conditional logic inside SQL queries. It checks conditions one by one and returns a value as soon as a matching condition is found.

Grading Exams Performance

select 
    result_id,
    marks,
case 
    when marks >= 80 then 'Distinction'
    when marks >= 60 then 'Merit'
    when marks >= 40 then 'Pass'
    else 'Fail'
end as class_performance
from greenwood_academy.exam_results;

Classifying Class Level

select 
    first_name,
    last_name,
    class,
case 
    when class = 'Form 3' or class = 'Form 4' then 'Senior'
    when class = 'Form 2' or class = 'Form 1' then 'Junior'
end as student_level
from greenwood_academy.students;

What I learned

CASE WHEN works like an IF-ELSE statement inside SQL.
CASE WHEN helps categorize data or transform values dynamically.
CASE WHEN can be used in SELECT, UPDATE, ORDER BY and other clauses.

Conclusion

Building the Greenwood Academy database gave me practical experience in designing relational databases, inserting and querying data, using operators such as AND and OR, applying aggregate functions, and implementing conditional logic with CASE WHEN. This project helped me move beyond SQL theory and understand how databases are structured and analyzed in real-world scenarios

Don't Keep Your Dashboard a Secret ; A Guide to Embedding and Publishing Power BI Reports

Young Odhiambo — Wed, 15 Jul 2026 19:52:10 +0000

Introduction

Think of your Power BI report as a movie. Creating it is just production the real magic happens when people can actually watch it. How do you show it off without sending everyone a dozen screenshots? Well that's where Publishing and Embedding comes in to get your dashboard onto the big screen, whether that's a website, an app, or your organization's portal.
It's time to let your report out into the world, whether you want to share it on a website, embed it into an application, or make it accessible to your team, this step-by-step guide will walk you through the entire process of publishing and embedding your Power BI reports. By the end, you'll know exactly how to make your dashboards interactive, accessible, and ready for your audience, no more screenshots required.

Here are a couple of terms that will be mentioned in this article, feel free to skim through them.

Power BI Desktop ; A free Windows program you install on your computer. Used for building reports. You connect to data, clean it, and make charts.

Power BI Service ; A website in the cloud (Power BI service) Used for Sharing your reports with your team.

Publishing ; Publishing is the initial step where you upload a report from Power BI Desktop to the cloud (Power BI Service).

Embedding ; Embedding is the secondary step of taking that published report and displaying it directly inside an external website, portal, or custom application

What you will need before you start:

Power BI Desktop App
Power BI Pro or Premium license (for secure sharing)
VsCode or any other editor
Website, SharePoint, Teams, or application for embedding (optional)

1. Publishing Report

Publishing Power BI Desktop reports to the Power BI Service enables online access and collaboration. The steps below explain how to complete this process efficiently.

step 1 : Create an account in Power BI service

step 2 : Create a workspace in Power BI service to hold your project e.g I created one named 'Maji Ndogo'

step 3 : On your Power BI Desktop click on the Publish button in the Home Tab

step 4 : Sign in to Power BI using your email address used in the Power BI Service.

Note: Power BI only accepts work or organizational email IDs (e.g., @gfg, @iiitu.ac.in) public emails like Gmail or Yahoo cannot be used.

step 5 : Select a Workspace, in which you want to publish your report, and click on the select button.

step 6 : Now the report is being published to the Power BI online service. It may take some time to finish.

step 7 : The report has successfully been published to the Power BI online service. Click on the Open '.pbix' in Power BI to open the report under online service.

step 8 : The report is successfully shown in the Power BI online service.

2. Embedding Reports

Embedding allows you to integrate the report into your application, website, or portal. This allows end-users to analyze data without leaving their familiar workflows or logging into the external Power BI service

step 1 : Open your published report, navigate to the file option and hover over the option “Embed Report”.

step 2 : Select the embedding options
You can select the embedding option according to your requirement. After selecting an option, you’ll see a link to embed in your website and an HTML tag of your report which you can embed in your code. Below is the embedding option for websites and portals.

step 3 : Copy the HTML code.
step 4 : Run the HTML using VsCode or any other editor

Here is a dashboard we have embedded on our website:

Conclusion

Just like that, you can share your powerBi reports. Publishing and embedding reports in Power BI democratizes your data by transitioning static files into dynamic, cloud-based experiences. It bridges the gap between raw analysis and actionable business insights, allowing stakeholders to easily access, filter, and consume live data directly within their daily workflows.

Connecting Power BI to SQL Databases: A Practical Guide

Young Odhiambo — Sat, 04 Jul 2026 09:23:03 +0000

Introduction

In most modern organizations, data is one of the most valuable assets. Companies collect large amounts of information from sales systems, websites, customer platforms, and operational databases. To make sense of this information, businesses use tools that can transform this raw data into clear insights. One of the most widely used tools for this purpose is the Microsoft Power BI platform.

What is Power BI?

Power BI is a business intelligence and data visualization tool developed by Microsoft. It allows users to connect to multiple data sources, transform raw data into meaningful insights, and present results through interactive dashboards and reports.

Businesses use Power BI to monitor performance, identify trends, and support decision-making in areas such as sales, marketing and operations. Instead of relying on static spreadsheets, Power BI enables analysts to work with real-time data, ensuring reports remain accurate and up to date.

Most organizations store their operational and analytical data in SQL databases. SQL databases are designed to store large amounts of structured data in tables. They allow users to query, filter, update, and analyze data efficiently using Structured Query Language (SQL). SQL databases are reliable, secure, and scalable, hence they are widely used in business systems such as sales platforms, customer management systems, and inventory systems.

Connecting Power BI to a database allows analysts to access this stored data directly. Instead of manually exporting data into spreadsheets, Power BI can retrieve the data automatically, refresh it when the database changes, and build dashboards that always reflect the latest information.

This article discuss how to connect Power BI to SQL databases, connecting Power BI to local PostgreSQL database, how to connect to cloud database such as Aiven PostgreSQl, and how loaded data are used for analysis.

Understanding Power BI

Before connecting to a databases its important to understand the power BI interface. Power BI Desktop is the main application for building reports and dashboards.

Power BI Desktop contains a user-friendly interface designed to help build reports efficiently:

Ribbon: Contains tabs like Home, Insert, and Modeling with tools for data and visuals.
Report canvas: The main area where you build and arrange your visuals.
Fields pane: Displays the tables and columns loaded into Power BI.
Visualizations pane: Used to select and customize charts.

You can get the Power BI Desktop here Power BI Desktop

Connecting Power BI Desktop to a Local PostgreSQL

PostgreSQL is a free and open-source database management system used to store, organize and manage data efficiently. The steps below explain how to connect Power BI to a local PostgreSQL database.

Step 1 : Open Power BI Desktop
Start by opening Power BI Desktop on your computer.
When the application opens, a blank report canvas appears. This is where you will build your report after loading the data.

Step 2 : Get Data
On the Home tab of the ribbon, click Get Data.
This button opens a list of available data sources. Power BI supports many data sources including:

Excel
SQL Server
PostgreSQL
Web APIs The Get Data feature is the starting point for connecting Power BI to any data source.
Other data sources are as shown on the image.

Step 3 : Select PostgreSQL Database
From the list of available data connectors, click more to view more options. Scroll down, select PostgreSQL Database and click Connect.

Step 4 : Enter Database Connection Details
After selecting PostreSQL Database and clicking connect, Power BI opens a window requiring to enter the Database connection details.

Server : The location of the database server. If the database is on your computer, use localhost.

Database : The name of the database you want to connect to.

Step 5 : Provide Logins Details

After a connection is made Power BI will ask for authentication details.
You'll need to provide:

Username
Password

These credentials were set up during installation of the PostgreSQL and allows Power BI to securely access the database.

Once the credentials are entered, click Connect.

Step 6 : Select Tables To Import

After connecting successfully, Power BI opens the Navigator Window which displays all available tables in the database.
You can preview the contents of each table before loading them.
There are two options:

Load : Import the data directly.
Transform Data : Clean or modify the data before loading it.

Connecting Power BI To Cloud Database (Aiven PostgreSQL)

Cloud platforms like Aiven provide managed PostgreSQL databases that can be accessed remotely.

Connecting Power BI to PostgreSQL allows users to analyze real-time database data directly inside dashboards.

Step 1 : Get Database Connection Details from Aiven
Login to Aiven and inside your dashboard you'll find connection details fo your PostgreSQL service. These details are used by Power BI to locate and connect to your database.

Step 2 : Download and Install the SSL Certificate
In Cloud Databases customer data is often encrypted when stored in database tables, temporary files, and backups. External connections can be encrypted by using SSL.
SSL encryption ensures;

Data transferred between Power BI and the database is encrypted.
Unauthorized users cannot intercept the connection.
The database server identity is verified.

In Aiven download the CA Certificate from the connection.

Search for Manage User Certificates in your Desktop and choose Trusted Root Certification to import your Certificate connection.

Browse the files to select the certificate and click next until the importation is finished

Step 3 : Connect to Power BI
Open Power BI desktop as before, click Get Data, click more, scroll down and select PostgreSQL Database.
Copy the Server Name from the service URL(host_name:port_number) on Connection Information and paste on Power BI.
Input the name of your database and click OK.

Copy the username and password from Aiven, input them on the Power BI credentials window that opens and click connect.
Select the tables you want to work with and click on load or transform data depending on what you wish to do with the data.

Successfully loaded data displays on the data pane as shown on figure below.

Conclusion

Power BI is a powerful tool that helps organizations transform raw data into meaningful insights. By connecting directly to SQL databases, Power BI allows analysts to access structured data stored in business systems and convert it into interactive dashboards and reports.

Data Modeling, Joins, Relationships and Schemas

Young Odhiambo — Mon, 22 Jun 2026 20:46:52 +0000

Data Modeling

Data modeling is the process of creating a structured and visual representation of data, defining how data elements relate to one another within a system. It helps translate business requirements into organized data structures that support accurate analysis and effective decision-making.

What is a Data Model?

A data model is a visual and logical representation of an organization data elements and the relationships between them.

Importance of Data Modeling

Improved data organization.
Better data integrity.
Enhanced data security.
Increased scalability.

Types of Data Models.

There are three main types of data models:

Conceptual data models.

They are also referred to as domain models and offer a high-level view of what the system will contain, how it will be organized, and which business rules are involved. Conceptual models are created as part of the process of gathering initial project requirements.

Purpose: Organize and define business problems, rules and concepts.
Focus: High-level overview of data such as customer data, market data and purchase data.
Audience: Business stakeholders and analysts.
Example Use: Understanding the relationship between customers and their orders before designing the database structure.

Logical data models.

Shows how data entities are related and describes the data from a technical perspective.

Purpose: Define tables, columns, relationships and constraints that form the data structure.
Focus: Structure of the data without depending on any specific database management system (DBMS).
Audience: Data architects and analysts.
Example Use: Outlining the schema, relationships and rules for customer and order data, which later guides the physical database design.

Physical data models.

Guides the implementation of a database with a detailed representation of a database design.

Purpose: Define every element needed to construct a database, including tables, columns, keys and constraints (primary key, foreign key, NOT NULL, etc.).
Focus: Actual implementation of the database using queries and the chosen DBMS features.
Audience: Developers and database administrators (DBAs).
Example Use: Creating the database schema and ensuring that all constraints and relationships are enforced in the physical database.

Data Modeling Techniques

Here we describe the primary techniques used to develop data models.

Hierarchical data models: Represent one-to-many relationships in a treelike format. In this type of model, each record has a single root or parent, which maps to one or more child tables.

Entity-relationship (ER) models: Are a variation of relational models and they provide a graphical representation of entities, their attributes, and the relationships between them.

Relational Model: Relational Mode represent the links between tables by representing data as rows and columns in tables. It is frequently utilised in database design and is strongly related to relational database management systems (RDBMS).
Object-oriented data models: Models are similar to ER models in that they represent data as objects with attributes and methods, but object-oriented models abstract entities into objects.
Dimensional data models: Models are based on the concept of dimensions, which are used to categorize and organize data, and facts, which are the numeric measurements or values associated with the dimensions.

Ralationship in a Database

A relationship in a DBMS exists when a variable has a connection with the properties stored in different tables. Such relationships help the organization of entities intertwined with each other, ultimately enabling efficient data processing. They're exhibited usually via keys in a table, which is either columns or fields that specify a distinctive arrangement for each record.

Key features of database relationships.

Primary keys: Unique identifiers for each record in a table, ensuring that no two rows are identical.
Foreign keys: Links between tables, pointing to primary keys in another table to establish relationships.
Data integrity: Relationships ensure consistency and prevent errors, such as associating an order with a non-existent customer.

Types of Database Relationships.

Database relationships can be broadly categorized into four types, each serving a specific purpose in connecting data.

One-to-one (1:1) relationships

A one-to-one relationship connects one record in a table to exactly one record in another table. This is often used when splitting data into multiple tables for better organization or security.

One-to-many (1:N) or many-to-one (N:1) relationships

In a one-to-many relationship, a single record in one table can be associated with multiple records in another table. This is one of the most common relationship types in relational databases.

Many-to-many (N: N) relationships

A many-to-many relationship occurs when multiple records in one table are related to multiple records in another table. This is implemented using a junction table to manage the connections.

Self-referencing relationships

In a self-referencing relationship, records in a table are linked to other records within the same table.

Database Schemas

A database schema defines the structure and organization of data within a database. It outlines how data is logically stored, including the relationships between different tables and other database objects.

What is a Schema?

A schema is the blueprint or structure that defines how data is organized and stored in a database.

Types of Database Schemas

Conceptual Database Schema

A conceptual schema describes the overall structure of the entire database at a high level.
It focuses on the meaning of data and captures how different entities relate to each other—without worrying about how data is stored physically.

Physical Database Schema

The physical schema defines how the data is actually stored on disk.
Specifies files, indexes, partitions, storage blocks, access paths, etc.
It represents the lowest level of abstraction, focusing on performance, storage optimization, and data retrieval efficiency.

Logical Database Schema

The logical schema describes the logical structure of data as it appears to the database designers and developers. Defines tables, columns, primary keys, foreign keys, relationships, and integrity rules. This schema represents a higher level of abstraction and is independent of physical storage details.

Difference between Logical and Physical Database Schema

Database Schema Designs

Flat Model

A flat model schema is a 2-D array in which every column contains the same type of data/information and the elements with rows are related to each other. It is just like a table or a spreadsheet.

Star Schema

The star schema is a simple and commonly used schema in data warehousing. It consists of a central fact table surrounded by dimension tables, forming a star-like pattern.

Use Cases: Star schemas are ideal for straightforward reporting and querying. They are efficient for read-heavy operations, making them suitable for dashboards and summary reports.

The star schema does not necessarily have to be in the shape of a star. Below is also a star schema and is a preferred arrangement for many experts as in this arrangement is easier to visualize filter propagation from dimension to fact tables.

Snowflake Schema

The snowflake schema is a normalized version of the star schema. In this design, dimension tables are further divided into related tables, resulting in a more complex structure.

Use Cases: Snowflake schemas are used in scenarios requiring detailed data models and efficient storage. They are beneficial when dealing with large datasets where data redundancy needs to be minimized.

Joins in data modeling

Joins are used to combine data from multiple tables into a single view.

Types of Joins

Inner Join

An inner join returns only the records that have matching values in both tables being joined. It combines rows from both tables based on a common column known as the join key. The result of an inner join will only include rows where there is a match in both tables.

Left Outer Join

This type of join returns all the rows from the left table and matching rows from the right table. If there are no matching rows in the right table, it returns NULL values for the right table columns.

Right Outer Join

This type of join returns all the rows from the right table and matching rows from the left table. If there are no matching rows in the left table, it returns NULL values for the left table columns.

Full Outer Join

This type of join returns all the rows from both tables with NULL values in the columns where there is no match.

Summary

Here's the beginner-friendly recap:

Data Modeling is the process of organizing and connecting your data tables so Power BI knows how they relate to each other.

Schemas are the blueprint of your model. The Star Schema is your best friend in Power BI — one fact table surrounded by dimension tables. The Snowflake Schema exists but adds complexity without much benefit in Power BI.

Relationships are the lines between tables. They're built on Primary Keys and Foreign Keys, and they define how filters flow through your model. The most common type is One-to-Many (1:N).

Joins (like Inner Join and Left Join) are how Power BI physically combines rows from different tables when calculating your visuals.

How Excel is Used in Real-World Data Analysis

Young Odhiambo — Fri, 05 Jun 2026 10:54:36 +0000

Introduction

What is Excel?

Excel is a spreadsheet application developed by Microsoft that allows users to organize data into rows and columns, helping users analyze data, visualize data, and handle tasks efficiently.

Excel is Important because it's;

Simplifies data handling from basic lists to complex analysis.
Widely used for reports, project tracking and decision-making.
Provides formulas, functions, and tools that help identify trends, patterns, and relationships in data.
Saves time and increases efficiency when handling data-related tasks.

Real-World Uses of Excel in Data Analysis

Microsoft Excel is a widely used tool in the real world for managing, analyzing, and reporting data. It helps professionals organize information, perform calculations, and create reports using features like formulas, pivot tables, and charts.

Key Uses of Excel in Data Analysis

Data Analysis and Reporting
Excel is a powerful tool for analyzing data using formulas, pivot tables, and charts. it turns raw data into actionable insights, supporting data-driven decision-making
Financial Planning and Budgeting
Excel simplifies financial planning by allowing organizations to track expenses, create budgets, and calculate financial metrics. The use of built in functions ensures financial accuracy and precision.
Business Decision Making
Excel enables businesses to predict market trends, assess risks, and make data-driven decisions. By using historical data and forecasting models, businesses can plan for future growth and financial stability.

Excel Features and Formulas

SUM Function - Adds all numeric values in a selected range. Commonly used to calculate totals.
=SUM(number1,[number2],...)
Average Function - Calculates the mean range of values
=AVERAGE(number1,[number2],...)
MAX Function - Returns the highest value from a range.
=MAX(number1,[number2],...)
MIN Function - Finds the lowest value in a range
=MIN(number1,[number2],...)
COUNT Function - Counts how many cells in a range contain numeric values.
=COUNT(number1,[number2],...)
SUMIF Function - Adds values that meet a specific condition.
=SUMIF(range, criteria, [sum_range])
AVERAGEIF Function - Calculates the average of values that satisfy a condition.
=AVERAGEIF(range, criteria, [average_range])

Personal Reflection

Learning Excel has significantly changed the way I view data. Previously, I saw data as a collection of numbers and records. However, I now understand that data can reveal important patterns, trends, and insights when analyzed properly. Excel has taught me how to organize information efficiently, perform calculations accurately, and present findings in a clear and professional manner. As a result, I now see data as a powerful resource that can support decision-making, improve performance, and drive success in both academic and professional settings.