DEV Community: Mary Ngure

SQL- Window Functions

Mary Ngure — Wed, 23 Sep 2026 12:59:44 +0000

If you've ever needed to calculate a running total, rank rows within groups, or compare a row to the one before it without collapsing your result set with GROUP BY , window functions are the tool for the job. They're one of the most powerful (and underused) features in SQL, and once they click, you'll reach for them constantly.

What Are Window Functions?

A window function performs a calculation across a set of rows that are related to the current row, called the "window", without reducing the number of rows returned. This is the key difference from aggregate functions like SUM() or AVG() used with GROUP BY, which collapse multiple rows into one.

The basic syntax looks like this:

SELECT
  column1,
  column2,
  SOME_FUNCTION() OVER (
    PARTITION BY column_to_group_by
    ORDER BY column_to_order_by
  ) AS result
FROM table_name;

Three parts matter here:

The function — what calculation to run (SUM, RANK, LAG, etc.)
PARTITION BY — splits rows into groups the function operates on independently (optional)
ORDER BY — defines the order rows are processed in, which matters for ranking and running calculations (optional, but required for many functions)

Categories of Window Functions

1. Aggregate window functions

These are your familiar aggregates (SUM, AVG, COUNT, MIN, MAX) applied over a window instead of collapsing rows.

Example: Running total of sales per day

SELECT
  sale_date,
  daily_sales,
  SUM(daily_sales) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Example: Each order's amount vs. that customer's average order

SELECT
  customer_id,
  order_id,
  order_amount,
  AVG(order_amount) OVER (PARTITION BY customer_id) AS customer_avg
FROM orders;

Every row keeps its detail, but now carries context about the group it belongs to — useful for spotting outliers (e.g. an order well above a customer's usual average).

2. Ranking functions

These assign a rank or row number within a partition.

ROW_NUMBER() — unique sequential number, no ties
RANK() — same rank for ties, next rank skips (1, 2, 2, 4)
DENSE_RANK() — same rank for ties, no gap (1, 2, 2, 3)
NTILE(n) — splits rows into n roughly equal buckets

Example: Top 3 highest-paid employees per department

SELECT *
FROM (
  SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
  FROM employees
) ranked
WHERE salary_rank <= 3;

This is one of the most common real-world patterns: get the "top N per group" — impossible to do cleanly with a plain GROUP BY.

3. Value/offset functions

These let you look at other rows relative to the current one — no self-joins required.

LAG(column, n) — value from n rows before
LEAD(column, n) — value from n rows after
FIRST_VALUE() / LAST_VALUE() — first or last value in the window

Example: Month-over-month revenue change

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

This replaces a clunky self-join and is the standard way to build period-over-period comparisons.

Practical Use Cases

Running totals / cumulative sums — cash flow tracking, YTD metrics
Top-N per category — top products per region, highest scorers per team
Deduplication — use ROW_NUMBER() partitioned by a key, then filter WHERE row_num = 1 to keep the first (or latest) record per group
Period-over-period comparisons — month-over-month, day-over-day trends using LAG/LEAD
Moving averages — smoothing noisy time series data with a bounded frame
Percent of total — each row's share of its group's total, using SUM() OVER (PARTITION BY ...) in the denominator

Example: Deduplication — keep only the latest record per customer

SELECT *
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM customer_records
) t
WHERE rn = 1;

Example: Moving average (3-row window)

SELECT
  sale_date,
  daily_sales,
  AVG(daily_sales) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3day
FROM sales;

The ROWS BETWEEN ... AND ... clause defines a custom frame — here, the current row plus the two before it, instead of the whole partition.

A Common Pitfall

Window functions run after WHERE, GROUP BY, and HAVING, but before ORDER BY and LIMIT in the logical query execution order. This means you can't filter on a window function's result directly in the same SELECT's WHERE clause, that's why the top-N and deduplication examples above wrap the query in a subquery (or CTE) and filter in an outer layer.

Why They're Worth Learning

Before window functions, these problems typically required self-joins, correlated subqueries, or client-side post-processing, all slower and harder to read. Window functions let the database engine do this work in one pass, and they read closer to how you'd describe the problem out loud: "rank each employee within their department" or "compare this month to last month."

SQL- Subqueries & CTEs

Mary Ngure — Thu, 17 Sep 2026 06:42:45 +0000

If you've spent any time writing SQL beyond basic SELECT statements, you've run into situations where a single query just isn't enough on its own, you need to query the results of another query.
That's where subqueries and Common Table Expressions (CTEs) come in.

Both let you break a complex problem into smaller pieces, but they behave differently, read differently, and shine in different situations. This article walks through what each one is, how they compare, and where you'd actually reach for one over the other.

What Is a Subquery?

A subquery (or inner query) is a query nested inside another SQL statement. It runs first, and its result is used by the outer query.

Subqueries can appear in several places:

In the WHERE clause (to filter rows)
In the FROM clause (as a derived table)
In the SELECT clause (to return a single value per row)
Inside INSERT, UPDATE, or DELETE statements

Example: Subquery in a WHERE clause

Say you have an orders table and want to find customers who placed an order above the average order value.

SELECT customer_id, order_id, amount
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
);

Here, the inner query calculates the average order amount, and the outer query filters orders against that single value.

Example: Subquery in a FROM clause (derived table)

SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_averages
WHERE avg_salary > 60000;

The inner query produces a temporary result set (dept_averages), which the outer query then treats like any other table.

Example: Correlated subquery

Unlike the examples above, a correlated subquery references a column from the outer query, so it runs once per row of the outer query rather than just once overall.

SELECT e.employee_name, e.salary, e.department
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e.department
);

This finds employees earning more than the average salary in their own department — the inner query depends on e.department from the outer query, so it can't be evaluated independently.

What Is a CTE?

A Common Table Expression (CTE) is a named, temporary result set defined using a WITH clause, which you can then reference like a table within the main query.

WITH dept_averages AS (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT dept, avg_salary
FROM dept_averages
WHERE avg_salary > 60000;

This produces the exact same result as the derived-table subquery example above — but notice how much easier it is to read top to bottom. You define dept_averages once, give it a clear name, and then use it.

Multiple CTEs

You can chain several CTEs together, and later ones can reference earlier ones:

WITH dept_averages AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
high_paying_depts AS (
    SELECT department
    FROM dept_averages
    WHERE avg_salary > 60000
)
SELECT e.employee_name, e.department, e.salary
FROM employees e
JOIN high_paying_depts h ON e.department = h.department;

This kind of step-by-step logic is where CTEs really start to pay off — each block does one clear job, and the final query just pulls it together.

Recursive CTEs

CTEs also support recursion, which subqueries cannot do. This is invaluable for hierarchical or graph-like data i.e org charts, category trees, bill-of-materials structures, and so on.

WITH RECURSIVE org_chart AS (
    -- Anchor: top-level managers (no manager)
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: employees reporting to someone already in org_chart
    SELECT e.employee_id, e.employee_name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart
ORDER BY level, employee_name;

This walks down the reporting hierarchy level by level — something you simply can't express with a standard subquery.

Key Differences

	Subquery	CTE
Syntax	Nested inside another query (`WHERE`, `FROM`, `SELECT`, etc.)	Declared upfront with `WITH ... AS (...)`
Readability	Can get hard to follow when nested deeply	Reads top-to-bottom; named and self-documenting
Reusability	Must be rewritten/repeated if needed more than once in the same query	Can be referenced multiple times within the same query
Recursion	Not supported	Supported via `WITH RECURSIVE`
Scope	Local to the clause it's written in	Available to the entire query that follows it
Multiple steps	Awkward — requires nesting subqueries within subqueries	Natural — chain CTEs one after another

When to Use Which

Reach for a subquery when:

You need a quick, one-off filter or calculation (e.g., "greater than the average")
The logic is simple enough that naming it separately would be overkill
You're writing something inside an IN, EXISTS, or scalar comparison

-- Simple, self-contained — a subquery is perfectly fine here
SELECT product_name
FROM products
WHERE product_id IN (
    SELECT product_id FROM order_items WHERE quantity > 100
);

Reach for a CTE when:

You have multiple logical steps that build on each other
You need to reference the same intermediate result more than once
You're working with hierarchical/recursive data
Readability and maintainability matter — especially in a query someone else (or future you) will need to debug
You're prototyping a complex query and want to test each step in isolation (you can run just the CTE block to sanity-check it)

-- Multi-step logic — a CTE keeps this readable and debuggable
WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total
    FROM orders
    GROUP BY 1
),
sales_growth AS (
    SELECT month, total,
           LAG(total) OVER (ORDER BY month) AS prev_month_total
    FROM monthly_sales
)
SELECT month, total, prev_month_total,
       ROUND(((total - prev_month_total) / prev_month_total) * 100, 2) AS pct_growth
FROM sales_growth
WHERE prev_month_total IS NOT NULL;

Practical Use Cases

Data cleaning/validation: Use a CTE to isolate flagged or invalid rows before joining them back for review — a common pattern in data quality work where you want each validation rule to be its own named step.
Deduplication: Combine a CTE with ROW_NUMBER() to identify and remove duplicate records.

WITH ranked_rows AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM customers
)
SELECT * FROM ranked_rows WHERE rn = 1;

Existence checks: Subqueries with EXISTS are a clean, efficient way to check for related records without pulling their data.

SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Hierarchical reporting: Recursive CTEs for org charts, category trees, or any parent-child structure.
Building a reporting pipeline step by step: Chained CTEs that mirror the way you'd naturally reason through a problem — aggregate, then filter, then rank, then present.

Wrapping Up

Subqueries and CTEs often solve the same underlying problem; needing the result of one query inside another but they differ in readability, reusability, and capability. Subqueries are great for quick, self-contained logic. CTEs shine when a query has multiple steps, needs to be readable months later, or requires recursion.

In practice, many data professionals default to CTEs for anything beyond a trivial filter, simply because breaking a query into named, logical steps makes it far easier to write, debug, and hand off. But understanding both and knowing when a simple subquery is genuinely the cleaner choice, makes you a more versatile SQL writer.

SQL - Joins Explained

Mary Ngure — Tue, 15 Sep 2026 11:26:35 +0000

Introduction

Relational databases spread data across multiple tables to avoid duplication - customers in one table, orders in another, products in a third.
A join is how SQL brings that data back together in a single query, matching rows from two or more tables based on a related column.
If you've ever needed to answer a question like "which customers placed orders last month?" or "which products have never been sold?", you needed a join.
This article breaks down what joins are, the main types available in SQL, when to reach for each one, and practical examples using PostgreSQL syntax.

We'll use two simple tables throughout:

CREATE TABLE customers (
    customer_id   SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INT REFERENCES customers(customer_id),
    order_total   NUMERIC(10, 2)
);

INSERT INTO customers (customer_name) VALUES
    ('Amina Otieno'),
    ('Brian Kamau'),
    ('Cynthia Wanjiru');

INSERT INTO orders (customer_id, order_total) VALUES
    (1, 1500.00),
    (1, 750.00),
    (2, 300.00);
    -- Note: Cynthia (customer_id 3) has no orders
    -- Note: there's also an order with a customer_id that doesn't exist, added below
INSERT INTO orders (customer_id, order_total) VALUES (99, 200.00);

What Is a Join?

A join combines rows from two tables based on a related column between them. Usually a primary key in one table and a foreign key in the other. The database matches rows where the join condition is true and returns the combined result as a single set of rows.

The type of join you choose determines what happens to rows that don't have a match on the other side.

INNER JOIN

What it does: Returns only the rows where there's a match in both tables. Any row without a corresponding match on either side is excluded.

When to use it: When you only care about records that exist on both sides of the relationship. For example, customers who have actually placed at least one order.

SELECT c.customer_name, o.order_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Result: Amina and Brian appear (they have orders). Cynthia is excluded because she has no matching order, and the order with customer_id = 99 is excluded because there's no matching customer.

LEFT JOIN (LEFT OUTER JOIN)

What it does: Returns all rows from the left table, plus matching rows from the right table. Where there's no match, the right table's columns show as NULL.

When to use it: When you want to keep every record from your "main" table regardless of whether a related record exists. For example, listing all customers and showing their orders if they have any, including customers who haven't ordered yet.

SELECT c.customer_name, o.order_total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Result: Amina and Brian appear with their order totals. Cynthia still appears, but order_total is NULL since she has no orders.

Common pattern — finding unmatched rows: Combine LEFT JOIN with a WHERE ... IS NULL check to find records with no match at all:

SELECT c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This returns only Cynthia — the customer with zero orders.

RIGHT JOIN (RIGHT OUTER JOIN)

What it does: The mirror image of LEFT JOIN. Returns all rows from the right table, plus matching rows from the left table. Unmatched left-side columns show as NULL.

When to use it: Less common in practice - most people restructure the query as a LEFT JOIN by swapping table order instead, since it reads more intuitively. But it's useful when you want to keep the original table order in your FROM/JOIN clauses while still preserving all rows from the second table.

SELECT c.customer_name, o.order_total
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Result: All orders appear, including the one with customer_id = 99, which shows NULL for customer_name since no matching customer exists.

FULL JOIN (FULL OUTER JOIN)

What it does: Returns all rows from both tables. Where a match exists, columns are combined; where it doesn't, the missing side shows NULL.

When to use it: When you need a complete picture of both tables - matched and unmatched rows on either side. Useful for data quality checks, like finding orphaned records in either direction at once.

SELECT c.customer_name, o.order_total
FROM customers c
FULL JOIN orders o ON c.customer_id = o.customer_id;

Result: Amina and Brian's orders appear, Cynthia appears with NULL order total, and the orphaned order (customer_id = 99) appears with NULL customer name — all in one result set.

CROSS JOIN

What it does: Returns the Cartesian product of two tables - every row from the first table paired with every row from the second, with no matching condition at all.

When to use it: Rarely needed for typical reporting, but useful for generating combinations - like pairing every product with every size/color variant, or every day in a date range with every store location for a template table.

SELECT c.customer_name, o.order_id
FROM customers c
CROSS JOIN orders o;

Result: Every customer is paired with every order (3 customers × 4 orders = 12 rows), regardless of whether they're actually related.

SELF JOIN

What it does: Not a distinct join type, but a technique - joining a table to itself, usually to compare rows within the same table (e.g., employees and their managers, both stored in one employees table).

When to use it: Hierarchical or relational data within a single table.

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name        VARCHAR(100),
    manager_id  INT REFERENCES employees(employee_id)
);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

This matches each employee to their manager, who is just another row in the same table.

Choosing the Right Join: Quick Reference

Join Type	Keeps unmatched rows from...	Typical use case
`INNER JOIN`	Neither side	Only records that exist in both tables
`LEFT JOIN`	Left table	All records from the main table, matches optional
`RIGHT JOIN`	Right table	Same as LEFT JOIN, tables reversed
`FULL JOIN`	Both sides	Complete picture, including orphaned records
`CROSS JOIN`	N/A (no condition)	Generating all possible combinations
`SELF JOIN`	Depends on join type used	Comparing rows within the same table

Practical Example: Combining Concepts

A common real-world need: list every customer, their total spend, and flag those with no orders at all.

SELECT
    c.customer_name,
    COALESCE(SUM(o.order_total), 0) AS total_spent,
    CASE WHEN COUNT(o.order_id) = 0 THEN 'No orders' ELSE 'Active' END AS status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

This uses a LEFT JOIN so every customer appears, COALESCE to turn NULL totals into 0, and a CASE statement to flag customers with no matching orders - a pattern that comes up constantly in reporting and dashboard work.

Key Takeaways

Joins combine data from multiple tables based on a related column.
INNER JOIN gives you only matched rows; outer joins (LEFT, RIGHT, FULL) preserve unmatched rows from one or both sides as NULL.
LEFT JOIN is the most commonly used outer join in practice. It's intuitive to reason about since you control which table's rows are always kept.
CROSS JOIN and SELF JOIN solve specific problems (combinations and same-table relationships) rather than everyday reporting needs.
Pairing LEFT JOIN with WHERE ... IS NULL is a reliable pattern for finding unmatched or orphaned records, useful for data quality checks.

Once joins feel natural, most real-world SQL work becomes a matter of picking the right join for the shape of the answer you need.

SQL - Intro,DDL, DML

Mary Ngure — Tue, 15 Sep 2026 09:29:13 +0000

Introduction

If you've worked with data you've probably come across SQL.

SQL (Structured Query Language) is the structured language used to interact with relational databases. Relational databases include PostgreSQL, MySQL, SQL Server, and Oracle.

SQL is used to:

retrieve data
insert new data
update existing data
delete data
define database structures
control access to data

SQL works by allowing users to ask structured questions from data and receive results.

Two of the most fundamental categories are:

DDL (Data Definition Language) - commands that define and modify the structure of database objects (tables, schemas, indexes).
DML (Data Manipulation Language) - commands that work with the data stored inside those structures (inserting, updating, deleting, querying rows).

Understanding the difference between "shaping the container" (DDL) and "working with what's inside it" (DML) is one of the first things that makes SQL click.

Part 1: DDL - Data Definition Language

DDL commands define, modify, and remove the structure of database objects. These changes affect the schema itself, not the data inside it. Most DDL statements in PostgreSQL are auto-committed, meaning they take effect immediately.

CREATE

Defines a new database object - a table, schema, index, or view.

CREATE TABLE employees (
    employee_id     SERIAL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    department      VARCHAR(50),
    hire_date       DATE DEFAULT CURRENT_DATE,
    salary          NUMERIC(10, 2) CHECK (salary >= 0)
);

This creates a table with a primary key, required fields, a default value, and a constraint that prevents negative salaries.

ALTER

Modifies the structure of an existing object: adding, dropping, or changing columns and constraints.

-- Add a new column
ALTER TABLE employees ADD COLUMN email VARCHAR(100);

-- Change a column's data type
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12, 2);

-- Add a constraint after the table already exists
ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

-- Drop a column
ALTER TABLE employees DROP COLUMN department;

DROP

Permanently removes an object and all its data. There's no undo — use with care, especially in production.

DROP TABLE employees;

-- Safer version: won't error if the table doesn't exist
DROP TABLE IF EXISTS employees;

TRUNCATE

Removes all rows from a table instantly, but keeps the table structure intact. Faster than DELETE for clearing a whole table because it doesn't scan row by row.

TRUNCATE TABLE employees;

RENAME

Renames an existing table or column.

ALTER TABLE employees RENAME TO staff;
ALTER TABLE staff RENAME COLUMN first_name TO given_name;

Part 2: DML - Data Manipulation Language

DML commands work with the actual rows of data inside tables — reading, adding, changing, and removing them. Unlike most DDL, DML changes typically need to be committed (COMMIT) or can be rolled back (ROLLBACK) within a transaction.

INSERT

Adds new rows to a table.

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Amina', 'Otieno', 'Finance', 85000.00);

-- Insert multiple rows in one statement
INSERT INTO employees (first_name, last_name, department, salary)
VALUES
    ('Brian', 'Kamau', 'Sales', 62000.00),
    ('Cynthia', 'Wanjiru', 'Marketing', 58000.00);

SELECT

Retrieves data from one or more tables. Technically part of DQL (Data Query Language) in strict definitions, but it's used constantly alongside DML and is essential to understand here.

-- Basic retrieval
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Sales';

-- With sorting and filtering
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY avg_salary DESC;

UPDATE

Modifies existing rows that match a condition. Always use a WHERE clause - an UPDATE without one changes every row in the table.

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';

-- Update multiple columns at once
UPDATE employees
SET department = 'Business Development', salary = 70000.00
WHERE employee_id = 3;

DELETE

Removes rows that match a condition. Like UPDATE, omitting WHERE deletes every row in the table.

DELETE FROM employees
WHERE department = 'Marketing';

-- Delete a single record by primary key
DELETE FROM employees
WHERE employee_id = 5;

DDL vs. DML: Quick Reference

Aspect	DDL	DML
Affects	Structure (schema, tables, columns)	Data (rows, values)
Common commands	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`	`INSERT`, `SELECT`, `UPDATE`, `DELETE`
Auto-committed?	Usually yes	No — can be rolled back in a transaction
Reversible?	Rarely (`DROP` is permanent)	Yes, if wrapped in a transaction before commit

Practical Example: Putting It Together

Here's a small end-to-end sequence showing how DDL and DML work together when standing up a new table:

-- 1. DDL: Define the structure
CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    order_total   NUMERIC(10, 2),
    order_date    DATE DEFAULT CURRENT_DATE
);

-- 2. DML: Populate it with data
INSERT INTO orders (customer_name, order_total)
VALUES
    ('Wafula Auto Parts', 1250.00),
    ('Green Valley Traders', 430.50);

-- 3. DML: Query it
SELECT * FROM orders WHERE order_total > 500;

-- 4. DDL: Evolve the structure as requirements change
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- 5. DML: Update existing rows to reflect the new column
UPDATE orders SET status = 'shipped' WHERE order_id = 1;

This pattern; define, populate, query, evolve - is the everyday rhythm of working with a relational database.

Key Takeaways

DDL shapes the database: CREATE, ALTER, DROP, TRUNCATE.
DML works with the data inside: INSERT, SELECT, UPDATE, DELETE.
DDL changes are structural and usually immediate; DML changes affect rows and can typically be rolled back within a transaction.
Always pair UPDATE and DELETE with a WHERE clause unless you genuinely intend to affect every row.

Mastering these two command families is the foundation for everything else in SQL - from writing analytical queries to designing full database schemas.

Python- String Methods and File Handling

Mary Ngure — Sun, 13 Sep 2026 11:26:06 +0000

Two things you'll do constantly in almost any Python program are working with text and working with files - cleaning up user input, formatting output, or reading and writing data to disk.

Part 1: String Methods

Strings in Python come with a large set of built-in methods for inspecting, cleaning, and transforming text.
None of these methods change the original string - strings are immutable, so every method returns a new string.

Changing case

name = "Mary Wanjiku"

print(name.upper())       # MARY WANJIKU
print(name.lower())       # mary wanjiku
print(name.title())       # Mary Wanjiku
print(name.capitalize())  # Mary wanjiku

Removing whitespace

.strip() removes leading and trailing whitespace - useful when cleaning up user input or data read from a file.

raw_input = "   mary@example.com   "
cleaned = raw_input.strip()
print(f"'{cleaned}'")  # 'mary@example.com'

print("  hello".lstrip())  # "hello" (left only)
print("hello  ".rstrip())  # "hello" (right only)

Searching and checking content

text = "Data Analyst - Nairobi"

print(text.startswith("Data"))   # True
print(text.endswith("Nairobi"))  # True
print("Analyst" in text)         # True
print(text.find("Nairobi"))      # 14 (index where it starts)
print(text.find("Manager"))      # -1 (not found)

Splitting and joining

.split() breaks a string into a list; .join() does the reverse.

csv_row = "Mary,Data Analyst,Nairobi"
fields = csv_row.split(",")
print(fields)  # ['Mary', 'Data Analyst', 'Nairobi']

words = ["Python", "is", "fun"]
sentence = " ".join(words)
print(sentence)  # Python is fun

Checking the content type of a string

Python has several methods that check what kind of characters a string contains, returning True or False. These are especially useful for validating input.

print("12345".isdigit())     # True  -> all characters are digits
print("Hello".isdigit())     # False

print("Hello".isalpha())     # True  -> all characters are letters
print("Hello123".isalpha())  # False -> contains digits

print("Hello123".isalnum())  # True  -> all characters are letters or digits
print("Hello 123".isalnum()) # False -> contains a space

print("   ".isspace())       # True  -> all characters are whitespace
print("  a ".isspace())      # False -> contains a non-space character

A practical use for these is validating simple user input before processing it:

user_input = "A1234"

if user_input.isalnum():
    print("Valid: letters and numbers only")
else:
    print("Invalid: contains other characters")

Counting occurrences

.count() tells you how many times a substring appears in a string:

text = "data analyst, data engineer, data scientist"
print(text.count("data"))      # 3
print(text.count("engineer"))  # 1

Replacing text

message = "Hello World"
print(message.replace("World", "Python"))  # Hello Python

Formatting strings

f-strings are the modern, readable way to build strings that include variable values:

name = "Mary"
role = "Data Analyst"
print(f"{name} works as a {role}.")  # Mary works as a Data Analyst.

price = 1234.5
print(f"Total: ${price:.2f}")  # Total: $1234.50

Practical example: cleaning a list of raw entries

raw_entries = ["  MARY  ", "james ", " Ali", "GRACE"]

cleaned_entries = [entry.strip().title() for entry in raw_entries]
print(cleaned_entries)  # ['Mary', 'James', 'Ali', 'Grace']

This kind of cleanup — stripping whitespace and standardizing case — comes up constantly when working with messy, real-world text data.

Part 2: File Handling

File handling lets your program read from and write to files on disk, so data can persist beyond a single run of the program.

Opening a file

The open() function opens a file and returns a file object. The most common modes are:

Mode	Meaning
`"r"`	Read (default) — file must exist
`"w"`	Write — creates the file, overwrites if it exists
`"a"`	Append — adds to the end of the file
`"r+"`	Read and write

Using `with` to handle files safely

The recommended way to work with files is the with statement, because it automatically closes the file for you, even if an error occurs partway through:

with open("notes.txt", "w") as file:
    file.write("This is my first line.\n")
    file.write("This is my second line.\n")

Once the with block ends, the file is closed automatically — no need to call file.close() yourself.

Reading a file

with open("notes.txt", "r") as file:
    content = file.read()
print(content)
# This is my first line.
# This is my second line.

Reading line by line

For larger files, reading line by line is more memory-efficient than loading the whole file at once:

with open("notes.txt", "r") as file:
    for line in file:
        print(line.strip())  # .strip() removes the trailing newline

You can also get all lines as a list with .readlines():

with open("notes.txt", "r") as file:
    lines = file.readlines()
print(lines)  # ['This is my first line.\n', 'This is my second line.\n']

Appending to a file

with open("notes.txt", "a") as file:
    file.write("This is a third line, added later.\n")

Handling missing files safely

Trying to read a file that doesn't exist raises a FileNotFoundError. Wrapping the operation in a try/except block lets your program handle that gracefully instead of crashing:

try:
    with open("missing_file.txt", "r") as file:
        content = file.read()
except FileNotFoundError:
    print("The file does not exist.")

Practical example: counting words in a file

with open("notes.txt", "r") as file:
    text = file.read()

word_count = len(text.split())
print(f"Word count: {word_count}")

Practical example: reading a simple CSV-style file

# Assume "employees.txt" contains:
# Mary,Data Analyst,Nairobi
# James,Developer,Mombasa

employees = []

with open("employees.txt", "r") as file:
    for line in file:
        name, role, city = line.strip().split(",")
        employees.append({"name": name, "role": role, "city": city})

print(employees)
# [{'name': 'Mary', 'role': 'Data Analyst', 'city': 'Nairobi'},
#  {'name': 'James', 'role': 'Developer', 'city': 'Mombasa'}]

This example combines string methods (.strip(), .split()) with file handling to turn plain text into structured data — a very common real-world pattern before that data gets passed on to further processing.

Key Takeaways

String methods like .upper(), .lower(), .strip(), .split(), .join(), and .replace() cover most everyday text cleaning and formatting tasks, and none of them modify the original string.
f-strings are the clearest way to build strings that include variable values.
Use open() with the with statement to read and write files safely — it closes the file automatically even if something goes wrong.
Read modes ("r"), write modes ("w"), and append modes ("a") each serve a different purpose — pick the one that matches what you're trying to do to the file.
Wrapping file operations in try/except protects your program from crashing when a file is missing or unreadable.

String methods and file handling often show up together in practice, cleaning up text as it's read in, or formatting it before writing it back out, so it's worth being comfortable combining the two.

Python - Data Structures

Mary Ngure — Sun, 13 Sep 2026 11:04:16 +0000

Once you're comfortable writing functions, the next question is usually: how do I organize the data I'm passing around?
Python gives you four built-in structures for this: lists, tuples, dictionaries, and sets , and each one is suited to a different kind of problem. This article walks through what each structure is, how to use it, and when to reach for it, with practical examples throughout.

1. Lists

A list is an ordered, changeable collection of items. It's the most commonly used data structure in Python because it's flexible — you can add, remove, and reorder items freely.

fruits = ["apple", "banana", "cherry"]
print(fruits)  # ['apple', 'banana', 'cherry']

Accessing and modifying items

Lists are indexed starting at 0:

print(fruits[0])   # apple
print(fruits[-1])  # cherry (last item)

fruits[1] = "blueberry"
print(fruits)  # ['apple', 'blueberry', 'cherry']

Adding and removing items

fruits.append("mango")       # add to the end
fruits.insert(1, "grape")    # insert at a specific position
fruits.remove("cherry")      # remove by value
popped = fruits.pop()        # remove and return the last item

print(fruits)  # ['apple', 'grape', 'blueberry', 'mango'] (order may vary based on steps above)

Looping through a list

prices = [120, 85, 60, 200]

for price in prices:
    print(f"Price: {price}")

Practical example: filtering data

scores = [45, 88, 92, 34, 67, 78]

passing_scores = [score for score in scores if score >= 50]
print(passing_scores)  # [88, 92, 67, 78]

Use a list when you need an ordered collection that might change over time; adding, removing, or reordering items and when duplicate values are allowed.

2. Tuples

A tuple is an ordered collection just like a list, but it's immutable — once created, it can't be changed. Tuples are defined with parentheses instead of square brackets.

coordinates = (6.5244, 3.3792)
print(coordinates)  # (6.5244, 3.3792)

Why immutability matters

Because tuples can't be modified after creation, they're useful for representing fixed data — values that should stay constant throughout the program.

coordinates[0] = 10  # TypeError: 'tuple' object does not support item assignment

Unpacking tuples

A common and convenient tuple pattern is unpacking values directly into variables:

latitude, longitude = coordinates
print(latitude)   # 6.5244
print(longitude)  # 3.3792

Practical example: returning multiple values from a function

Tuples are the natural fit when a function needs to return more than one related value:

def get_min_max(numbers):
    return min(numbers), max(numbers)

low, high = get_min_max([12, 45, 3, 67, 21])
print(f"Lowest: {low}, Highest: {high}")  # Lowest: 3, Highest: 67

Use a tuple when the data represents a fixed collection that shouldn't change — like coordinates, RGB values, or a set of values returned together from a function.

3. Dictionaries

A dictionary stores data as key-value pairs. Instead of accessing items by position (like a list), you access them by a unique key, which makes dictionaries ideal for representing structured, labeled data.

employee = {
    "name": "Mary",
    "role": "Data Analyst",
    "city": "Nairobi"
}
print(employee["name"])  # Mary

Adding, updating, and removing entries

employee["years_experience"] = 3       # add a new key
employee["role"] = "Senior Data Analyst"  # update an existing key
del employee["city"]                   # remove a key

print(employee)
# {'name': 'Mary', 'role': 'Senior Data Analyst', 'years_experience': 3}

Looping through a dictionary

for key, value in employee.items():
    print(f"{key}: {value}")

Safe access with `.get()`

Accessing a missing key with square brackets raises an error. .get() lets you provide a fallback instead:

print(employee.get("department", "Not specified"))  # Not specified

Practical example: counting occurrences

Dictionaries are a natural fit for tallying or grouping data:

words = ["apple", "banana", "apple", "orange", "banana", "apple"]

counts = {}
for word in words:
    counts[word] = counts.get(word, 0) + 1

print(counts)  # {'apple': 3, 'banana': 2, 'orange': 1}

Use a dictionary when you need to look up values by a meaningful label rather than a numeric position, records with named fields, counts, mappings, or configuration settings.

4. Sets

A set is an unordered collection of unique items. Sets automatically remove duplicates and are optimized for checking whether an item exists.

colors = {"red", "green", "blue", "red"}
print(colors)  # {'red', 'green', 'blue'}  -> duplicate "red" is dropped

Adding and removing items

colors.add("yellow")
colors.remove("green")
print(colors)  # {'red', 'blue', 'yellow'} (order is not guaranteed)

Set operations

Sets support mathematical set operations, which are useful for comparing collections:

team_a = {"Mary", "James", "Ali", "Sam"}
team_b = {"Ali", "Sam", "Grace"}

print(team_a & team_b)   # intersection -> {'Ali', 'Sam'}
print(team_a | team_b)   # union -> {'Mary', 'James', 'Ali', 'Sam', 'Grace'}
print(team_a - team_b)   # difference -> {'Mary', 'James'}

Practical example: removing duplicates from a list

emails = ["a@mail.com", "b@mail.com", "a@mail.com", "c@mail.com"]

unique_emails = list(set(emails))
print(unique_emails)  # order not guaranteed, but no duplicates

Practical example: fast membership checks

Checking membership in a set is much faster than in a list, especially as the collection grows:

allowed_users = {"mary", "james", "ali"}

username = "james"
if username in allowed_users:
    print("Access granted")
else:
    print("Access denied")

Use a set when you need to guarantee uniqueness, don't care about order, or need to quickly check membership or compare collections against each other.

Choosing the Right Structure

Structure	Ordered	Changeable	Duplicates Allowed	Access By
List	Yes	Yes	Yes	Index (position)
Tuple	Yes	No	Yes	Index (position)
Dictionary	Yes (insertion order)	Yes	Keys must be unique	Key
Set	No	Yes	No	Membership only

A rough way to decide:

Need an ordered, editable collection? → List
Need fixed data that shouldn't change? → Tuple
Need to label and look up data by name? → Dictionary
Need to guarantee uniqueness or do fast membership checks? → Set

Putting It All Together

Here's a small example that combines all four structures to process a batch of customer orders, using plain loops instead of anything more advanced:

orders = [
    {"customer": "Mary", "item": "Laptop", "price": 800},
    {"customer": "James", "item": "Mouse", "price": 20},
    {"customer": "Mary", "item": "Keyboard", "price": 45},
    {"customer": "Ali", "item": "Laptop", "price": 800},
]

# Dictionary: total spend per customer
totals = {}
for order in orders:
    customer = order["customer"]
    totals[customer] = totals.get(customer, 0) + order["price"]

# Set: unique items ordered
unique_items = set()
for order in orders:
    unique_items.add(order["item"])

# Find the customer with the highest total spend
top_customer = None
top_amount = 0
for customer, amount in totals.items():
    if amount > top_amount:
        top_customer = customer
        top_amount = amount

# Tuple: fixed record pairing the top customer with their spend
top_customer_record = (top_customer, top_amount)

# List: customers who spent above 100
big_spenders = []
for customer, amount in totals.items():
    if amount > 100:
        big_spenders.append(customer)

print(totals)                # {'Mary': 845, 'James': 20, 'Ali': 800}
print(unique_items)          # {'Laptop', 'Mouse', 'Keyboard'}
print(top_customer_record)   # ('Mary', 845)
print(big_spenders)          # ['Mary', 'Ali']

Each structure is doing the job it's best suited for: a dictionary for labeled totals, a set for unique items, a tuple for a fixed pair of values (the top customer and their spend), and a list for collecting the customers who meet a condition — all built using simple loops and conditionals.

Key Takeaways

Lists are ordered and changeable - the default choice for a general-purpose collection.
Tuples are ordered but immutable - good for fixed data and multi-value returns.
Dictionaries map keys to values - ideal for labeled, structured data and lookups.
Sets hold unique, unordered items - best for deduplication, membership checks, and comparing collections.

Picking the right structure isn't just a style choice, it affects how readable your code is and how efficiently it runs, so it's worth pausing to ask which one actually fits the shape of the data you're working with.

Python - Functions: Defining, Calling, Parameters, Return Values & Scope

Mary Ngure — Sun, 13 Sep 2026 10:39:04 +0000

Functions are one of the first "real" programming concepts you hit in Python, and they're also one of the most important.
Once you understand how to package logic into a function, you stop repeating yourself and start building code that's actually reusable and testable.

1. Defining and Calling Functions

A function is a named block of code that runs only when you call it. You define one with the def keyword:

def greet():
    print("Hello, welcome!")

This creates a function called greet, but nothing happens yet, defining a function just stores the instructions. To actually run it, you call it by using its name followed by parentheses:

greet()  # Output: Hello, welcome!

You can call the same function as many times as you want:

greet()
greet()
greet()

Each call executes the function's body from the top.

2. Parameters and Arguments

Most useful functions need input to work with.
Parameters are the placeholders you define in the function signature; arguments are the actual values you pass in when calling the function.

def greet(name):
    print(f"Hello, {name}!")

greet("Mary")   # "name" is the parameter, "Mary" is the argument
greet("James")

Multiple parameters

def describe_dataset(name, rows, columns):
    print(f"{name}: {rows} rows, {columns} columns")

describe_dataset("Sales Data", 5000, 12)

Default parameter values

You can give a parameter a default value, so it's optional when calling the function:

def describe_dataset(name, rows, columns=1):
    print(f"{name}: {rows} rows, {columns} columns")

describe_dataset("Sales Data", 5000)          # uses default columns=1
describe_dataset("Sales Data", 5000, 12)      # overrides the default

Keyword arguments

Arguments can be passed by position or by name. Using names (keyword arguments) makes calls clearer, especially with several parameters:

describe_dataset(rows=5000, columns=12, name="Sales Data")

Order doesn't matter when you use keyword arguments — Python matches them by name.

`*args` and `**kwargs`

Sometimes you don't know in advance how many arguments will be passed. *args collects extra positional arguments into a tuple, and **kwargs collects extra keyword arguments into a dictionary:

def sum_values(*args):
    return sum(args)

print(sum_values(1, 2, 3))       # 6
print(sum_values(10, 20, 30, 40)) # 100

def print_info(**kwargs):
    for key, value in kwargs.items():
        print(f"{key}: {value}")

print_info(name="Mary", role="Data Analyst", city="Nairobi")

3. Return Values

A function that only prints something is limited, the result disappears once printed.
The return statement sends a value back to wherever the function was called, so you can store it, reuse it, or pass it to another function.

def add(a, b):
    return a + b

result = add(4, 5)
print(result)  # 9

Without return, a function implicitly returns None:

def greet(name):
    print(f"Hello, {name}!")

value = greet("Mary")  # prints "Hello, Mary!"
print(value)            # None

Returning multiple values

Python lets you return more than one value at once, packed as a tuple:

def get_min_max(numbers):
    return min(numbers), max(numbers)

low, high = get_min_max([4, 9, 1, 7, 3])
print(low, high)  # 1 9

A practical example combining parameters and return values

def calculate_average(numbers):
    if len(numbers) == 0:
        return 0
    return sum(numbers) / len(numbers)

scores = [85, 90, 78, 92, 88]
average = calculate_average(scores)
print(f"Average score: {average}")  # Average score: 86.6

Here, the function takes data in (numbers), does the work, and hands a usable result back out via return, rather than just printing it and losing it.

4. `return` vs. `print`: What's the Difference?

New Python learners often mix these up because both can show a value on the screen but they do fundamentally different things.

print() displays a value to the console. It's for humans watching the program run. Once printed, that value is gone — it isn't stored anywhere the program can use again.
return sends a value back out of the function to whatever code called it. It's for the program itself. The returned value can be stored in a variable, passed to another function, or used in further calculations.

def add_print(a, b):
    print(a + b)

def add_return(a, b):
    return a + b

result1 = add_print(5, 3)   # prints "8" to the console
result2 = add_return(5, 3)  # returns 8, nothing printed

print(result1)  # None  -> add_print never returned a value
print(result2)  # 8     -> add_return handed the value back

add_print shows 8 on screen, but as far as the rest of the program is concerned, it produced nothing — result1 ends up None.
add_return, on the other hand, actually hands the value 8 back, so result2 holds a usable number you can do more with:

total = add_return(5, 3) + add_return(10, 2)
print(total)  # 20

Trying the same thing with add_print would fail, since add_print(5, 3) evaluates to None, and you can't add None to a number.

Rule of thumb: use print() when you just want to see a value while debugging or communicating with a user; use return when the function's result needs to be used elsewhere in your code.
In most real programs, functions should return values rather than print them, so the caller decides what to do with the result — whether that's printing it, saving it, or feeding it into another function.

5. Scope

Scope determines where in your code a variable can be accessed. Understanding scope prevents a lot of confusing bugs, especially "why isn't this variable available here?" moments.

Local scope

Variables created inside a function only exist inside that function. They are local to it and disappear once the function finishes running.

def calculate_total(price, quantity):
    total = price * quantity  # local variable
    return total

print(calculate_total(100, 3))  # 300
print(total)  # NameError: total is not defined

total was created inside calculate_total, so it can't be accessed outside of it.

Global scope

Variables defined outside any function are global and can be read from inside functions:

tax_rate = 0.16  # global variable

def calculate_total(price, quantity):
    return price * quantity * (1 + tax_rate)

print(calculate_total(100, 3))  # 348.0

Modifying a global variable inside a function

By default, assigning to a variable inside a function creates a new local variable, even if a global one has the same name. To actually modify the global variable, you need the global keyword:

counter = 0

def increment():
    global counter
    counter += 1

increment()
increment()
print(counter)  # 2

Without global counter, Python would raise an error or create a separate local variable instead of updating the outer one. In general, it's good practice to avoid relying heavily on global state, passing values in as parameters and getting results back via return keeps functions predictable and easier to test.

Putting It All Together

Here's a small example that uses everything above
Parameters with a default value, a return value, and local scope — to clean a simple list of numeric entries:

def clean_numbers(values, default=0):
    """Replace non-numeric entries with a default value and return the cleaned list."""
    cleaned = []  # local variable
    for value in values:
        if isinstance(value, (int, float)):
            cleaned.append(value)
        else:
            cleaned.append(default)
    return cleaned

raw_data = [10, "N/A", 25, None, 30]
result = clean_numbers(raw_data, default=0)
print(result)  # [10, 0, 25, 0, 30]

This function takes a list and an optional default value as parameters, processes the data using a local variable (cleaned), and returns a new list, a pattern you'll use constantly once you start working with real-world, messy data.

Key Takeaways

Define a function with def; call it with parentheses to run it.
Parameters are placeholders in the definition; arguments are the actual values passed at call time. Defaults, keyword arguments, *args, and **kwargs all give you flexibility in how a function accepts input.
return sends a value back to the caller so it can be reused, without it, a function returns None.
Scope controls where variables live: local variables exist only inside their function, while global variables are accessible everywhere (though modifying them from inside a function requires the global keyword).

Functions are the basic unit of logic in Python, once you're comfortable defining them, passing data in and out, and knowing where your variables live, you have the building blocks to organize almost any piece of code into something clear and reusable.

Python: Loops

Mary Ngure — Sun, 13 Sep 2026 09:44:34 +0000

One thing I've started noticing as I learn Python is that computers are really good at doing repetitive tasks.

Imagine being asked to print the numbers from 1 to 100 manually. Or process the scores of 50 students one by one.

That would be exhausting for a human.

For Python, however, repeating a task is exactly what loops are designed for.

Loops allow us to run a block of code multiple times without having to write the same code over and over again.

What is a Loop?

A loop tells Python:

"Keep doing this until we've finished."

For example, instead of writing:

print(1)
print(2)
print(3)
print(4)
print(5)

We can use a loop:

for number in range(1, 6):
    print(number)

Output:

Much cleaner.

`for` Loops

A for loop is useful when we want to go through a sequence of items. It repeats KNOWN number of times, or over a collections of items.

That could be:

numbers
strings
lists
other collections of data

For example:

names = ["Mary", "John", "Ann"]

for name in names:
    print(name)

Output:

Mary
John
Ann

Python takes each item from the list, stores it temporarily in name, and runs the indented code.

Using `range()`

range() is particularly useful when working with numbers.

for number in range(1, 6):
    print(number)

This prints numbers from 1 to 5.

One thing to remember is that the ending number is not included.
It stops before the number

So:

range(1, 6)

means:

1, 2, 3, 4, 5

`while` Loops

A while loop works a little differently.

Instead of going through a known sequence, it keeps running as long as a condition is true.

For example:

count = 1

while count <= 5:
    print(count)
    count += 1

Output:

Here, Python keeps asking:

Is count <= 5?

As long as the answer is True, the loop continues.

The line:

count += 1

is important because it changes the value of count.

Without it, the condition would remain true and we'd create an infinite loop.

When Should I Use `for` vs `while`?

A simple way I'm thinking about it is:

Use a for loop when you know what you're going through.

for name in names:
    print(name)

Use a while loop when you want to continue until a condition changes.

while savings < goal:
    savings += monthly_savings

Both repeat code, but they are useful in different situations.

`break`: Stop the Loop

Sometimes we don't want a loop to continue all the way through.

That's where break comes in.

break immediately stops the loop.

For example:

for number in range(1, 11):
    if number == 6:
        break
    print(number)

Output:

When Python reaches 6, the break statement stops the loop.

A practical example is asking a user to enter scores until they type "done":

scores = []

while True:
    score = input("Enter score or done: ")

    if score.lower() == "done":
        break

    scores.append(int(score))

print(scores)

Here, while True creates a loop that keeps running, while break gives us a way to stop it.

`continue`: Skip an Iteration

continue is different from break.

Instead of stopping the loop completely, continue tells Python:

"Skip this one and move to the next iteration."

For example, suppose we want to print numbers from 1 to 5 but skip 3:

for number in range(1, 6):
    if number == 3:
        continue
    print(number)

Output:

The loop didn't stop. It simply skipped the iteration where number was 3.

So a useful way to remember them is:

break → Stop the loop

continue → Skip this iteration

`enumerate()`: Get the Position and the Value

Another useful concept I've come across is enumerate().

When looping through a list, sometimes we want both:

The item
Its position in the list

For example:

names = ["Mary", "John", "Ann"]

for index, name in enumerate(names):
    print(index, name)

Output:

0 Mary
1 John
2 Ann

By default, enumerate() starts counting from 0.

We can change that by specifying start=1:

names = ["Mary", "John", "Ann"]

for number, name in enumerate(names, start=1):
    print(number, name)

Output:

1 Mary
2 John
3 Ann

This is especially useful when creating reports, rankings, menus, or numbered lists.

Practical Example: Student Scores

Let's bring these concepts together.

Suppose we have a list of student scores and want to generate a simple report:

scores = [85, 72, 64, 91, 48]

for number, score in enumerate(scores, start=1):
    if score >= 50:
        result = "Pass"
    else:
        result = "Fail"

    print(f"Student {number}: {score} - {result}")

Output:

Student 1: 85 - Pass
Student 2: 72 - Pass
Student 3: 64 - Pass
Student 4: 91 - Pass
Student 5: 48 - Fail

Here we're combining loops, enumerate(), conditionals, and f-strings in one small program.

Practical Example: Savings Goal

A while loop can also be useful for something like tracking savings.

goal = float(input("What is your savings goal? "))
monthly_savings = float(input("How much can you save per month? "))

total = 0
months = 0

while total < goal:
    total += monthly_savings
    months += 1

print(f"It will take you {months} months to reach your goal.")
print(f"You will have saved {total:.2f}.")

The loop continues adding the monthly savings until the total reaches the goal.

This is a good example of why while loops are useful: we don't necessarily know how many times the loop needs to run beforehand.

A Simple Mental Model

I'm finding it helpful to think about loops like this:

Start → Repeat → Check → Repeat or Stop

For a for loop:

Take an item → Do something → Take the next item → Continue

For a while loop:

Check condition → Do something → Check again → Stop when false

And then we have:

break    → Stop completely
continue → Skip this round
enumerate() → Get the position + the item

Key Takeaways

The main things I'm taking away from loops are:

for loops are useful for going through sequences of items.
while loops repeat code while a condition remains true.
break stops a loop completely.
continue skips the current iteration.
enumerate() gives us both the position and the item when looping through a sequence.
Loops become even more powerful when combined with conditionals, lists, and functions.

The more I practice loops, the more I see how much repetitive work Python can handle for me. Instead of writing the same instructions repeatedly, I can define the process once and let the computer do the repetition.

Python - Operators & Conditionals

Mary Ngure — Sun, 13 Sep 2026 08:15:50 +0000

In my first Python session, I learned the basics: variables, data types, input(), print(), and basic arithmetic.

But programs don't just calculate things. They also need to make decisions.

For example:

Is the user old enough?
Is the number even or odd?
Did the student pass?
Should a discount be applied?

This is where operators and conditionals come in.

Arithmetic Operators

Arithmetic operators are used to perform calculations.

Operator	Meaning	Example
`+`	Addition	`5 + 3`
`-`	Subtraction	`5 - 3`
`*`	Multiplication	`5 * 3`
`/`	Division	`5 / 3`
`//`	Floor division	`5 // 3`
`%`	Modulo (remainder)	`5 % 3`
`**`	Exponent	`5 ** 3`

For example, a simple shopping calculation:

price = 500
quantity = 3

total = price * quantity

print(total)

The output is:

The Modulo Operator `%`

One operator I found particularly useful is %.

It gives us the remainder after division.

print(10 % 3)

Output:

This becomes useful when checking whether a number is even or odd:

number = 10

print(number % 2)

If the result is 0, the number is even.

Comparison Operators

Comparison operators allow Python to compare values.

They return either True or False.

Operator	Meaning
`==`	Equal to
`!=`	Not equal to
`>`	Greater than
`<`	Less than
`>=`	Greater than or equal to
`<=`	Less than or equal to

Example:

age = 25

print(age == 25)
print(age > 18)
print(age < 18)

Output:

True
True
False

`=` vs `==`

This is important for beginners.

= is used to assign a value:

age = 25

== is used to compare values:

age == 25

Think of it this way:

= means "store this."

== means "are these equal?"

Logical Operators

Logical operators allow us to combine conditions.

There are three main ones:

`and`

Both conditions must be True.

age = 25
has_id = True

print(age >= 18 and has_id)

Result:

True

`or`

At least one condition must be True.

has_email = True
has_phone = False

print(has_email or has_phone)

Result:

True

`not`

not reverses a Boolean value.

is_raining = True

print(not is_raining)

Result:

False

Conditional Statements

Now we get to the part that allows Python to make decisions.

Python mainly uses:

if
elif
else

`if`

An if statement tells Python:

"If this condition is true, do this."

age = 20

if age >= 18:
    print("You are an adult.")

Notice the indentation. The code inside the if statement must be indented.

`else`

else tells Python what to do when the condition is false.

age = 16

if age >= 18:
    print("You are an adult.")
else:
    print("You are a minor.")

`elif`

elif means "else if."

It allows us to check multiple conditions.

For example, grading a student:

score = 75

if score >= 80:
    print("Grade A")
elif score >= 70:
    print("Grade B")
elif score >= 60:
    print("Grade C")
elif score >= 50:
    print("Grade D")
else:
    print("Grade F")

Nested if Statements

Sometimes one decision depends on another decision.

This is where nested if statements come in.

A nested if is simply an if statement inside another if statement.

For example:

age = 20
has_id = True

if age >= 18:
    if has_id:
        print("You can enter.")
    else:
        print("You need an ID.")
else:
    print("You are too young to enter.")

Here, Python first checks whether the person is 18 or older.

Only if that condition is True does it check the second condition: whether they have an ID.

The structure looks like this:

if first condition:
if second condition:
do something
else: do something else
else: do something different

Nested if statements are useful when you have a decision within a decision.

However, sometimes the same logic can be written more simply using and:

if age >= 18 and has_id:
print("You can enter.")

Both approaches can work. As programs become more complex, choosing the simpler and clearer structure becomes important.
Python checks the conditions from top to bottom and stops when it finds one that is True.

A Practical Example: Even or Odd

Let's combine what we've learned.

number = int(input("Enter a number: "))

if number % 2 == 0:
    print(f"{number} is even.")
else:
    print(f"{number} is odd.")

Here we're using:

input() to get information from the user
int() to convert it to an integer
% to find the remainder
== to compare
if and else to make a decision
an f-string to display the result

That's a lot of Python concepts coming together in a small program.

Another Example: Student Grade

We can also combine input(), conditionals, and f-strings to create a simple grading program:

name = input("Student name: ")
score = int(input("Score: "))

if score >= 80:
    grade = "A"
elif score >= 70:
    grade = "B"
elif score >= 60:
    grade = "C"
elif score >= 50:
    grade = "D"
else:
    grade = "F"

print(f"{name}, your grade is {grade}.")

For example:

Student name: Mary
Score: 75
Mary, your grade is B.

My Mental Model

I'm finding it helpful to think about these concepts as:

Calculate → Compare → Decide → Act

For example:

Calculate → 75
Compare  → Is 75 >= 70?
Decide   → Yes
Act      → Print "Grade B"

This simple way of thinking makes conditionals much easier to understand.

Key Takeaways

The main things I'm taking away from this session are:

Arithmetic operators help Python calculate.
Comparison operators help Python ask questions.
Logical operators help combine questions.
if, elif, and else allow Python to make decisions.
% is useful for checking remainders, including whether numbers are even or odd.
= assigns a value, while == compares values.

The more I practice these small concepts, the more I realize that programming isn't about memorizing everything at once. It's about understanding the building blocks, putting them together, and gradually becoming more confident in solving problems with code.

Python- Basics, Fundamental concepts

Mary Ngure — Sun, 13 Sep 2026 07:32:29 +0000

What is Python?

First things first, Python is a high level programming language that allows us to give instructions to a computer in a way that is relatively easy for humans to read and understand.

A simple way of defining programming is:
Programming is like writing a recipe. You provide clear, step-by-step instructions, and the computer follows those instructions exactly.

We break a problem into instructions that a computer can understand and execute.

Python makes this process easier for beginners because its syntax is relatively readable.

For example:

print("Hello, Python!")

Even if you're completely new to programming, you can probably guess what this does.

And that's one of Python's biggest advantages.

Why Python?

Python is beginner-friendly, but that's not the only reason it's popular.

Python is used for:

Data analysis
Artificial intelligence and machine learning
Web development
Automation
Scientific computing
General software development

It's also free and open source.

So learning Python isn't just about learning a programming language.

It's learning a tool that can eventually be applied to many different areas of technology.

But before getting there, let's start with the basics.

1. print() — Getting Python to Talk

The first thing I learned was print().

print() is Python's way of displaying something on the screen.

Think of it as telling Python:

"I want you to show this."

For example:

print("Hello, World!")

Python responds:

Hello, World!

Anything we want Python to display goes inside the parentheses.

Text needs quotation marks

print("I am learning Python")

The quotation marks tell Python:

This is text.

We can also use single quotes:

print('I am learning Python')

Both work.

Numbers don't need quotation marks

print(25)
print(100)
print(3.14)

But there's an important difference between:

print(25)

and:

print("25")

The first is a number.

The second is text.

They may look identical when printed, but Python treats them differently.

And that brings us to our next concept.

2. Variables — Giving Information a Name

Imagine I have a box.

Inside the box is the name:

Fatuma

Instead of carrying that box around and saying "the box containing Fatuma," I can put a label on it:

name

That's essentially what a variable does.

name = "Fatuma"

I've created a variable called name and stored "Fatuma" in it.

Now I can use the variable instead of repeatedly writing the name:

print(name)

Output:

Fatuma

We can store many different things in variables:

name = "Fatuma"
age = 25
salary = 50000
city = "Nairobi"

Then:

print(name)
print(age)
print(salary)
print(city)

This is one of the fundamental ideas behind programming:

Store information → work with that information → produce an output.

A Small Detail About =

One thing beginners often misunderstand is the equals sign.

In:

age = 25

= doesn't mean "age is mathematically equal to 25."

It means:

Assign the value 25 to the variable age.

This is called assignment.

Naming Variables in Python

Python has a few rules for variable names.

This is valid:

first_name = "Fatuma"
age = 25
monthly_salary = 50000

This isn't:

2name = "Fatuma"

because a variable cannot start with a number.

This also isn't valid:

first name = "Fatuma"

because spaces aren't allowed in variable names.

Instead:

first_name = "Fatuma"

Python variable names are also case-sensitive.

That means:

age
Age
AGE

are three different variable names.

3. f-Strings — A Cleaner Way to Display Variables

Once we have variables, we'll often want to combine them with text.

For example:

name = "Fatuma"
age = 25

print("My name is", name, "and I am", age, "years old.")

This works, but Python gives us a cleaner and more readable option: f-strings.

An f-string allows us to insert variables directly inside a string.

We add an f before the quotation mark and place the variable inside curly braces {}.

name = "Fatuma"
age = 25

print(f"My name is {name} and I am {age} years old.")

Output:

My name is Fatuma and I am 25 years old.

The {name} tells Python:

Insert the value stored in the name variable here.

And {age} tells Python:

Insert the value stored in age here.

Why are f-strings useful?

They make output easier to read and make our code cleaner, especially when we're working with several variables.

Compare:

print("Hello", name, "your balance is", balance)

with:

print(f"Hello {name}, your balance is {balance}.")

The second version reads much more naturally.

We can also put expressions inside f-strings

For example:

price = 100
quantity = 3

print(f"Total cost: {price * quantity}")

Output:

Total cost: 300

This becomes particularly useful when building interactive programs.

4. Data Types — Python Cares About What Your Data Is

Here's something important I learned:

Not all data is the same.

For example:

age = 25

and:

age = "25"

look almost identical.

But Python sees them differently.

The first is a number.

The second is text.

Python has different data types that tell it what kind of information it's dealing with.

Four basic types beginners should know are:

str — String

Text.

name = "Mary"

int — Integer

Whole numbers.

age = 25

float — Floating-point number

Numbers containing decimals.

price = 99.99

bool — Boolean

Either True or False.

is_logged_in = True

We can even ask Python what type something is:

age = 25

print(type(age))

Python gives us: <class 'int'>

Why Data Types Matter

This is where things get interesting.

Python can add numbers:

10 + 5

Result:

15

But strings behave differently.

"Hello" + "World"

Result:

HelloWorld

Here, + doesn't mean mathematical addition.

It joins the two pieces of text together.

This is called string concatenation.

But what happens here?

5 + "Hello"

Python raises a TypeError.

Why?

Because we're trying to perform an operation between an integer and a string.

Python needs to know what we're working with before it can determine what operation makes sense.

And this becomes particularly important when we start taking information from users.

5. input() — Making Programs Interactive

Until now, we've been telling Python exactly what information to use.

But what if we want the user to provide the information?

That's where input() comes in.

For example:

name = input("What is your name? ")

When the program runs, Python asks:

What is your name?

The user might enter:

Mary

Python stores that answer in the variable name.

We can then use it with an f-string:

print(f"Hello {name}!")

Output:

Hello Mary!

This is much cleaner than manually joining several pieces of text.

⚠️ The input() Trap Beginners Should Know

There's an important detail here.

input() returns a string.

Consider:

age = input("How old are you? ")

If the user enters:

25

Python sees:

"25"

not:

25

That matters if we want to perform calculations.

For example:

age = input("How old are you? ")

print(age + 5)

This will cause an error.

Why?

Because Python is essentially trying to do:

"25" + 5

Text + number = problem.

The solution is type conversion.

age = int(input("How old are you? "))

Now the input is converted from a string into an integer.

For decimal numbers, we can use float():

price = float(input("Enter the price: "))

6. Arithmetic — Turning Python Into a Calculator

Python can perform basic mathematical operations.

Addition

total = 10 + 5

Subtraction

difference = 10 - 5

Multiplication

product = 10 * 5

Division

result = 10 / 5

Exponents

result = 2 ** 3

Modulo %

The % operator gives us the remainder after division.

result = 10 % 3

print(result)

Output:

1

Because 10 divided by 3 leaves a remainder of 1.

Modulo becomes particularly useful later when working with conditions and loops.

Putting Everything Together

Now let's combine the concepts we've learned into a small savings calculator.

name = input("What is your name? ")
monthly_savings = float(input("How much do you save per month? "))
months = int(input("How many months will you save? "))

total_savings = monthly_savings * months

print(f"\nHello {name}!")
print(f"If you save {monthly_savings} per month for {months} months,")
print(f"your total savings will be {total_savings}.")

A possible interaction:

What is your name? Mary
How much do you save per month? 5000
How many months will you save? 6

Hello Mary!
If you save 5000.0 per month for 6 months,
your total savings will be 30000.0.

Now we've combined:

input() to collect information
Variables to store information
Data types to represent different kinds of values
Type conversion using int() and float()
Arithmetic to calculate the result
f-strings to create readable output
print() to display the result

That's a lot of programming concepts packed into a very small program.

🧠 The Mental Model I'm Taking Away

One thing I'm realizing while learning Python is that programming isn't really about memorising syntax.

It's about learning how to break a problem down.

When faced with a programming problem, I can ask:

What information do I need?

Maybe a name, price, age, score, or quantity.

Where will I store it?

Variables.

What type of information is it?

str, int, float, or bool.

What do I need to do with it?

Maybe calculate, compare, combine, or transform it.

What should the user see?

print() and f-strings can help create the output.

This gives me a simple programming pattern:

Input → Store → Process → Output

And that pattern is much more valuable than memorising isolated Python commands.

The code gets longer as you progress, but the fundamental idea remains the same:

Give the computer clear instructions, one step at a time.

How I connected PowerBI to PostgreSQL locally and on Aiven

Mary Ngure — Sun, 05 Jul 2026 14:12:28 +0000

Data rarely lives inside Excel forever. As datasets grow, they're typically stored in databases where they can be managed efficiently and accessed by multiple applications. That's why learning to connect Power BI directly to SQL databases is an essential skill for every data analyst.

This week, I explored two different ways of connecting Power BI to PostgreSQL databases:

A local PostgreSQL database
A cloud-hosted PostgreSQL database using Aiven with SSL encryption

For this exercise, I used PostgreSQL, DBeaver for database management, Aiven for cloud database hosting, and Power BI Desktop for data visualization.

Although both connections ultimately bring data into Power BI, the setup process is quite different. Here's what I learned.

Why Connect Power BI to a Database?

Instead of importing CSV files every time new data becomes available, Power BI can retrieve data directly from a database. This makes reporting more scalable, reliable, and suitable for production environments.

Some benefits include:

Centralized data storage
Improved security
Easier collaboration
Faster report updates
Support for larger datasets

Part 1: Connecting Power BI to a Local PostgreSQL Database

Before working with a cloud-hosted database, I first set up a local PostgreSQL environment. Using DBeaver, I connected to my local PostgreSQL instance and imported the JCars Logistics dataset. DBeaver provided a convenient interface for managing the database and verifying that the data had been imported successfully.

With the dataset in place, I connected Power BI to the local PostgreSQL database by entering the server details and authentication credentials. Once the connection was established, Power BI loaded the tables, making them available for analysis and reporting.

This local setup served as a useful foundation before connecting to a cloud-hosted PostgreSQL database on Aiven, where additional security measures such as SSL certificate configuration were required.
Using Dbeaver, I connected Postgresql and uploaded my data.

Part 2: Connecting Power BI to an Aiven PostgreSQL Database

Unlike a local database, Aiven hosts PostgreSQL in the cloud. This means the connection requires additional security through SSL certificates.

Step 1: Import the Dataset into Aiven Using DBeaver

Instead of importing the dataset directly through the Aiven web interface, I used DBeaver as my database management tool. After connecting DBeaver to my Aiven PostgreSQL instance using the connection details provided by Aiven, I imported the JCars Logistics dataset into the target database.

Using DBeaver made it easier to manage the database, verify that the import was successful, and inspect the tables before connecting them to Power BI.

DBeaver connected to the Aiven PostgreSQL database and JCars Logistics tables successfully imported and visible in DBeaver.

Step 2: Obtain the SSL Certificate

Because Aiven encrypts database connections, Power BI requires the CA SSL certificate before it can establish a secure connection.

I downloaded the CA certificate from the Aiven service dashboard and saved it locally for use during the connection process.

SSL certificate download page in Aiven.

Confirmed that the SSL was downloaded and in my certificates management.

Step 3: Connect Power BI to Aiven

Inside Power BI, I again selected PostgreSQL Database, but this time entered the cloud connection details provided by Aiven, including the hostname, port, database name, username, and password.

I then configured the SSL certificate, allowing Power BI to establish a secure encrypted connection to the cloud database.

PostgreSQL connection with PowerBI.

Step 4: Load the Tables

Once the connection was authenticated, Power BI displayed all available tables from my Aiven database. I selected the required tables and loaded them into the report for analysis.

Navigator showing tables from the Aiven PostgreSQL database.

What I learned

Connecting Power BI to a local database is mostly about getting the server
name and credentials right. Connecting to a cloud database adds two extra
considerations: the non-standard host/port from your provider's dashboard,
and SSL, which cloud providers enforce by default for security. Once you've
done it once, the pattern is the same for any provider; Aiven, AWS RDS,
Azure Database, or Google Cloud SQL all follow this same shape: get
connection details from the console, ensure SSL is respected, connect via
Power BI's native connector, and choose Import mode so your report doesn't
depend on the database staying reachable.

Key Takeaways

This exercise showed how Power BI can connect seamlessly to both local and cloud-hosted PostgreSQL databases. While local databases are simpler to configure, cloud platforms like Aiven introduce additional security measures such as SSL certificates that are common in production environments.

Using DBeaver alongside Aiven also simplified database management by making it easy to import the dataset, inspect tables, and verify that everything was ready before connecting to Power BI.

As organizations continue moving their data infrastructure to the cloud, understanding how to connect business intelligence tools like Power BI to secure cloud databases is becoming an essential skill for every data professional.

PowerBI; Relationships, Schemas & Dashboards

Mary Ngure — Mon, 29 Jun 2026 06:06:38 +0000

When I started learning Power BI, I thought dashboards were mostly about choosing the right charts.

Then I discovered data modeling.

Everything clicked.

I realized that dashboards aren't powered by visuals, they're powered by relationships.

The Mistake I Used to Make

Coming from Excel, I was used to keeping everything in one large table.

It worked... until the data became larger and more complex.

Duplicate information increased.

Performance slowed down.

And maintaining the dataset became frustrating.

Power BI introduced me to a completely different way of thinking.

Instead of one giant spreadsheet, data is organized into related tables.

Understanding Relationships

Relationships allow tables to communicate using common fields, often called keys.

For example:

A Sales table stores transactions.
A Products table stores product information.
A Customers table stores customer details.

Rather than repeating product names thousands of times, Power BI connects the tables using Product IDs.

This keeps the model cleaner, faster, and easier to maintain.

Discovering Star Schema

One concept that completely changed my understanding was the Star Schema.

Instead of random tables connected everywhere, the model is organized into:

Fact Table

Contains measurable business events such as:

Sales
Revenue
Quantity Sold
Profit

Dimension Tables

Contain descriptive information like:

Customers
Products
Locations
Dates

The fact table sits in the center, while the dimension tables surround it—forming a star.

It's surprisingly elegant.

And it's one of the reasons Power BI performs so efficiently.

Why Good Models Create Better Dashboards

Once relationships were configured correctly, dashboard creation became much easier.

Instead of manually combining data for every visual, Power BI automatically understood how everything connected.

Slicers filtered multiple visuals correctly.

Measures returned accurate results.

Cross-filtering worked naturally.

It felt like the model was doing half the work for me.

Dashboard Design Is More Than Pretty Colors

Building dashboards taught me another important lesson.

A dashboard isn't a decoration.

It's a decision-making tool.

Now, whenever I design one, I ask:

What question should this answer?
What KPI matters most?
What action should someone take after seeing this visual?

Those questions have become more important than choosing between a bar chart or a line chart.

My Growing Power BI Workflow

As I continue learning, my process has become surprisingly consistent:

Understand the business problem.
Clean the data.
Build relationships.
Design a star schema.
Create measures.
Build visuals.
Refine the dashboard until it tells a clear story.

Each step builds on the previous one.

Skipping one almost always creates problems later.

Final Thoughts

Power BI has taught me that great dashboards aren't created in the report view.

They're created through thoughtful data modeling.

The visuals may be what users notice first, but relationships are what make those visuals meaningful.

I'm still learning, but every project reinforces the same lesson:

Clean data is important.

Well-designed models are powerful.

And dashboards are simply the final chapter of a much bigger story.

DEV Community: Mary Ngure

SQL- Window Functions

What Are Window Functions?

Categories of Window Functions

1. Aggregate window functions

2. Ranking functions

3. Value/offset functions

Practical Use Cases

A Common Pitfall

Why They're Worth Learning

SQL- Subqueries & CTEs

What Is a Subquery?

Example: Subquery in a WHERE clause

Example: Subquery in a FROM clause (derived table)

Example: Correlated subquery

What Is a CTE?

Multiple CTEs

Recursive CTEs

Key Differences

When to Use Which

Practical Use Cases

Wrapping Up

SQL - Joins Explained

Introduction

What Is a Join?

INNER JOIN

LEFT JOIN (LEFT OUTER JOIN)

RIGHT JOIN (RIGHT OUTER JOIN)

FULL JOIN (FULL OUTER JOIN)

CROSS JOIN

SELF JOIN

Choosing the Right Join: Quick Reference

Practical Example: Combining Concepts

Key Takeaways

SQL - Intro,DDL, DML

Introduction

Part 1: DDL - Data Definition Language

CREATE

ALTER

DROP

TRUNCATE

RENAME

Part 2: DML - Data Manipulation Language

INSERT

SELECT

UPDATE

DELETE

DDL vs. DML: Quick Reference

Practical Example: Putting It Together

Key Takeaways

Python- String Methods and File Handling

Part 1: String Methods

Changing case

Removing whitespace

Searching and checking content

Splitting and joining

Checking the content type of a string

Counting occurrences

Replacing text

Formatting strings

Practical example: cleaning a list of raw entries

Part 2: File Handling

Opening a file

Using with to handle files safely

Reading a file

Reading line by line

Appending to a file

Handling missing files safely

Practical example: counting words in a file

Practical example: reading a simple CSV-style file

Key Takeaways

Python - Data Structures

1. Lists

Accessing and modifying items

Adding and removing items

Looping through a list

Practical example: filtering data

2. Tuples

Why immutability matters

Unpacking tuples

Using `with` to handle files safely

Safe access with `.get()`

`*args` and `**kwargs`

4. `return` vs. `print`: What's the Difference?

`for` Loops

Using `range()`

`while` Loops

When Should I Use `for` vs `while`?

`break`: Stop the Loop

`continue`: Skip an Iteration

`enumerate()`: Get the Position and the Value

The Modulo Operator `%`

`=` vs `==`

`and`

`or`

`not`

`if`

`else`

`elif`