DEV Community: Emilio Ochieng

Object-Oriented Programming Explained

Emilio Ochieng — Fri, 02 Oct 2026 19:25:25 +0000

This article explains OOP through one running example, a simple bank account, in Python.

What is OOP?

OOP is a way of organizing code around objects, which bundle together:

Data (called attributes), such as an account's owner and balance
Behavior (called methods), such as deposit and withdraw

The alternative is writing loose functions that pass data around. That works for small scripts, but as programs grow, it gets hard to see which function is allowed to change which data. OOP keeps related data and behavior in one place.

Classes and objects

A class is a blueprint. An object is something built from that blueprint.

python
class BankAccount:
def init(self, owner, balance=0):
self.owner = owner
self.balance = balance

def deposit(self, amount):
    self.balance += amount

def withdraw(self, amount):
    if amount > self.balance:
        raise ValueError("Insufficient funds")
    self.balance -= amount

ann = BankAccount("Ann", 1000)
ben = BankAccount("Ben")

ann.deposit(500)
print(ann.balance) # 1500
print(ben.balance) # 0

BankAccount is the class. ann and ben are two separate objects, each with their own balance. init is the constructor that runs when you create an object, and self refers to the specific object being worked on.

The four pillars of OOP

Encapsulation

Encapsulation means hiding an object's internal details and controlling access to them. Right now anyone can write ann.balance = 1,000,000, which defeats the purpose of having a withdraw rule.

Python uses a naming convention: a leading underscore means "internal, please don't touch".

python
class BankAccount:
def init(self, owner, balance=0):
self.owner = owner
self._balance = balance

@property
def balance(self):
    return self._balance

def deposit(self, amount):
    if amount <= 0:
        raise ValueError("Deposit must be positive")
    self._balance += amount

Now the balance can be read but not set directly. The only way to change it is through methods that enforce the rules. That is the main payoff of encapsulation: the object protects its own consistency.

2. Abstraction

Abstraction means exposing a simple interface and hiding the complexity behind it. You press a key on your keyboard without knowing how the signal travels through the circuitry.

In code, account.withdraw(200) is the interface. Whether it checks fraud rules, writes to a database or sends an SMS is not the caller's concern, and it can change later without breaking the code that calls it.

3. Inheritance

Inheritance lets a class reuse and extend another class. A savings account is a bank account with some extras:

python
class SavingsAccount(BankAccount):
def init(self, owner, balance=0, rate=0.05):
super().init(owner, balance)
self.rate = rate

def add_interest(self):
    self.deposit(self._balance * self.rate)

SavingsAccount gets deposit, withdraw and balance for free from BankAccount, and adds its own add_interest. This avoids copy-pasting code.

4. Polymorphism

Polymorphism means different objects can respond to the same method call in their own way.

python
class CurrentAccount(BankAccount):
def withdraw(self, amount):
# allows an overdraft of up to 500
if amount > self._balance + 500:
raise ValueError("Overdraft limit exceeded")
self._balance -= amount

accounts = [BankAccount("Ann", 100), CurrentAccount("Ben", 100)]

for acc in accounts:
acc.withdraw(300) # each class applies its own rule

The loop doesn't need to know which kind of account it has. It just calls withdraw, and each object does the right thing. This is what lets you add new account types later without rewriting the code that uses them.

A note on "is-a" versus "has-a"

Inheritance models an "is-a" relationship: a savings account is a bank account. Many beginners overuse it. When the relationship is "has-a", use composition, where one object holds another:

python
class Customer:
def init(self, name):
self.name = name
self.accounts = [] # a customer HAS accounts

A common guideline is to prefer composition over inheritance. Deep inheritance chains become rigid and hard to change.

When OOP is the wrong tool

OOP is useful, but it isn't always the answer:

Small scripts and data pipelines are often clearer as plain functions that transform data from one step to the next.
Over-engineering is a real risk. If a class has one method and no state, it should probably just be a function.
Some problems fit functional approaches better, and many modern codebases mix both styles.

Use OOP when you have things with state and behavior that belong together. Don't use it just because it feels more "professional".

Quick recap
Concept One-line meaning
Class / object Blueprint / the thing built from it
Encapsulation Protect internal data behind methods
Abstraction Show a simple interface, hide the complexity
Inheritance Reuse and extend an existing class
Polymorphism Same method call, different behavior per class

SQL Functions

Emilio Ochieng — Fri, 18 Sep 2026 09:31:19 +0000

What are SQL functions?

A SQL function is a built-in operation that takes some input - a column, a value, or a set of rows - and returns a computed result. They fall into two broad categories:

Aggregate functions - operate across multiple rows and collapse them into a single value: COUNT, SUM, AVG, MIN, MAX.
Scalar functions - operate on a single value at a time and return one result per row: string functions (UPPER, LOWER, CONCAT, LENGTH), numeric functions (ROUND, ABS), and date functions (NOW(), DATE_PART, AGE()).

Common functions

Function	Category	What it does
`COUNT()`	Aggregate	Counts rows (or non-null values in a column)
`SUM()`	Aggregate	Adds up numeric values
`AVG()`	Aggregate	Calculates the mean
`MIN()` / `MAX()`	Aggregate	Smallest / largest value
`UPPER()` / `LOWER()`	String	Changes text case
`CONCAT()`	String	Joins strings together
`ROUND()`	Numeric	Rounds a decimal to a given precision
`NOW()`	Date	Returns the current timestamp

Examples - from the Sunrise Supermarket project

Counting orders per customer - an aggregate function paired with GROUP BY:

SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id;

Adding up total quantity sold per product:

SELECT products.product_name, SUM(order_items.quantity) AS total_quantity
FROM products
INNER JOIN order_items ON products.product_id = order_items.product_id
GROUP BY products.product_name;

Scalar functions applied on top of the same schema — formatting a product name in uppercase for a report, and calculating price including a hypothetical 16% VAT, rounded to two decimal places:

SELECT
    UPPER(product_name) AS product_display,
    ROUND(unit_price * 1.16, 2) AS price_with_vat
FROM products;

When to use them

Aggregate functions are the right tool whenever the question is about a group of rows rather than any single row - "how many," "what's the total," "what's the average." The moment GROUP BY enters a query, there's almost always an aggregate function sitting next to it.

Scalar functions come up constantly for formatting and light computation - cleaning up text for display, converting units, rounding currency values so they don't show absurd precision like 179.999999.

What I understood from this

The thing that clarified aggregate functions for me was realizing they only make sense in the context of grouping - COUNT(order_id) on its own, with no GROUP BY, just counts all the rows in the result set as one number. It's the same function, but GROUP BY customer_id is what turns it from "one total" into "one total per customer." Scalar functions, by contrast, never need that - they just transform each row independently, which is why you'll often see them mixed freely into a SELECT list without touching the rest of the query's logic at all.

Functions

Emilio Ochieng — Fri, 18 Sep 2026 09:22:37 +0000

Defining and calling functions

A function is a self-contained, reusable block of code designed to perform one specific task. Instead of repeating the same instructions throughout a program, you package them once, give them a name, and call that name whenever the task needs doing.

def greet():
    print("Hello there!")

greet()   # calling the function

def declares the function, followed by its name and parentheses. The indented lines beneath it are the function's body - the code that actually runs when the function is called.

Parameters and arguments

A function can accept input through parameters - placeholders defined in the function's signature - which get filled in with actual arguments when the function is called:

def greet(name):          # 'name' is the parameter
    print("Hello, " + name)

greet("Emilio")            # "Emilio" is the argument

Functions can take multiple parameters, and can give them default values so an argument becomes optional:

def greet(name, greeting="Hello"):
    print(greeting + ", " + name)

greet("Emilio")                 # uses the default: "Hello, Emilio"
greet("Emilio", "Welcome back") # overrides it: "Welcome back, Emilio"

Return values

A function can send a result back to whatever called it, using return. This is different from print(), which only displays something - return actually hands the value back so it can be stored, passed along, or used in further calculations.

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

total = add(100, 200)   # total now holds 300
print(total)

Once a return statement runs, the function stops immediately - any code after it in the function body never executes.

Scope

Scope determines where a variable can be seen and used. A variable created inside a function (a local variable) only exists within that function — it disappears once the function finishes running, and code outside the function can't access it. A variable created outside any function (a global variable) can be read from anywhere, including inside functions.

message = "I'm global"

def show_message():
    local_note = "I'm local"
    print(message)      # works fine - global variables are visible inside functions
    print(local_note)

show_message()
print(local_note)       # this line would raise a NameError - local_note doesn't exist out here

Practical examples

A function that takes a list of prices and returns the total - reusable anywhere a total needs calculating, instead of rewriting the summing logic each time:

def calculate_total(prices):
    total = 0
    for price in prices:
        total += price
    return total

cart = [180, 320, 85]
print(calculate_total(cart))   # 585

What I understood from this

The distinction between print() and return was the one that mattered most in practice. Early on I'd write a function that "worked" because it printed the right answer to the screen - but then couldn't figure out why using that function's result in another calculation gave an error. print() only shows a value; it doesn't hand it back to the program. return is what actually makes a function's output usable elsewhere - total = add(100, 200) only works because add returns a value for total to catch. Scope followed a similar lesson: a variable defined inside a function is genuinely gone once that function ends, which is exactly why return exists - it's the sanctioned way to get a value out of that otherwise-sealed local scope.

Python Data Structures

Emilio Ochieng — Fri, 18 Sep 2026 09:11:57 +0000

Python has four built-in structures for holding collections of data, and each one exists because it makes a different trade-off between order, mutability, and uniqueness.

Lists

A list is an ordered, mutable (changeable) collection, written with square brackets. Items can be added, removed, or changed after creation, and duplicates are allowed.

fruits = ["apple", "banana", "apple", "mango"]
fruits.append("orange")
fruits[0] = "pineapple"
print(fruits)   # ['pineapple', 'banana', 'apple', 'mango', 'orange']

Tuples

A tuple is an ordered, immutable (unchangeable) collection, written with parentheses. Once created, its contents can't be modified - no adding, removing, or changing items.

coordinates = (36.8219, -1.2921)
# coordinates[0] = 40   # this would raise an error - tuples can't be modified

Dictionaries

A dictionary stores data as key-value pairs, written with curly braces. Instead of accessing items by position (like a list), you access them by a unique key - which makes lookups fast and the data self-describing.

customer = {
    "name": "Emilio ochieng",
    "city": "Nairobi",
    "loyalty_points": 120
}

print(customer["name"])       # Emilio ochieng
customer["loyalty_points"] += 10

Sets

A set is an unordered collection of unique items - duplicates are automatically removed, and there's no guaranteed order to how items are stored.

cities = {"Nairobi", "Nakuru", "Nairobi", "Mombasa"}
print(cities)   # {'Nairobi', 'Nakuru', 'Mombasa'} - the duplicate is gone

Sets are particularly useful for membership checks ("Nairobi" in cities) and for operations like finding what two collections have in common:

supported_cities = {"Nairobi", "Nakuru", "Mombasa"}
customer_cities = {"Nakuru", "Eldoret"}

print(supported_cities & customer_cities)   # {'Nakuru'} - intersection

When to use each

Structure	Ordered?	Mutable?	Duplicates?	Use it when...
List	Yes	Yes	Allowed	You need a sequence you'll modify — adding, removing, reordering items
Tuple	Yes	No	Allowed	The data shouldn't change — fixed coordinates, a date (year, month, day)
Dictionary	Insertion order (3.7+)	Yes	Keys must be unique	You need to look things up by a meaningful label rather than position
Set	No	Yes	Not allowed	You need uniqueness enforced, or fast membership checks

Practical examples

Modeling something like a single row from the Sunrise Supermarket products table as a dictionary - it maps naturally onto named fields the way a database row does:

product = {
    "product_id": 1,
    "product_name": "Maize Flour 2kg",
    "category": "Groceries",
    "unit_price": 180.00
}

print(f"{product['product_name']} costs KES {product['unit_price']}")

Using a list of those dictionaries to represent an entire table, and a set to quickly answer "what categories exist?":

products = [
    {"product_name": "Maize Flour 2kg", "category": "Groceries"},
    {"product_name": "Cooking Oil 1L", "category": "Groceries"},
    {"product_name": "Bathing Soap", "category": "Toiletries"},
]

categories = {p["category"] for p in products}
print(categories)   # {'Groceries', 'Toiletries'}

What I understood from this

Choosing between these stopped being confusing once I stopped thinking about syntax (brackets vs. braces vs. parentheses) and started thinking about the rule each one enforces. A list says "order matters, and you're allowed to change your mind." A tuple says "order matters, but this is locked." A dictionary says "forget position - find things by name." A set says "I don't care about order, but I will not tolerate duplicates." Once the question became "which rule does my data actually need," the right structure usually picked itself - a database row, with its named fields, is obviously a dictionary; a fixed set of coordinates that should never change is obviously a tuple.

Operators & Conditionals

Emilio Ochieng — Fri, 18 Sep 2026 08:59:16 +0000

Arithmetic operators

These perform basic math: +, -, *, /, % (modulus - the remainder after division), and ** (exponent).

a = 150
b = 200
c = 300
d = 10
e = 3

total = a + b + c   # 650
diff = b - a         # 50
rem = d % e           # 1  (remainder of 10 ÷ 3)
div = c / d           # 30.0
power = e ** 2        # 9

Comparison operators

These compare two values and always evaluate to a boolean (True or False): == (equal to), != (not equal to), >, <, >=, <=.

age = 18
print(age == 18)   # True
print(age != 20)   # True
print(age >= 21)   # False

The most common beginner mistake here is confusing = (assignment) with == (comparison) - age = 18 sets the value; age == 18 checks it.

Logical operators

These combine multiple conditions: and (both must be true), or (at least one must be true), not (flips a boolean).

age = 20
has_id = True

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

Conditionals: if, elif, else

Conditionals let a program make decisions - running different code depending on whether a condition is true:

score = 72

if score >= 90:
    print("Grade: A")
elif score >= 75:
    print("Grade: B")
elif score >= 60:
    print("Grade: C")
else:
    print("Grade: F")

Python checks each condition top to bottom and runs the first block whose condition is True, skipping the rest - elif (else-if) lets you chain as many conditions as needed, and else catches anything that didn't match.

Practical examples

Combining comparison and logical operators inside a conditional - checking eligibility against two conditions at once:

age = 19
id_present = True

if age >= 18 and id_present == True:
    print("You can drive")
else:
    print("Not eligible yet")

A slightly more layered example, using elif to categorize a value into ranges:

temperature = 28

if temperature > 30:
    print("It's hot")
elif temperature >= 20:
    print("It's warm")
else:
    print("It's cold")

What I understood from this

The distinction that mattered most here was between comparison operators (which produce a boolean answer to a single question) and logical operators (which combine several of those boolean answers into one final decision). A condition like age >= 18 and has_id only reads cleanly once you see it as two separate True/False questions being joined by and - Python evaluates each side independently first, then combines them. Once that clicked, writing more complex conditions stopped being about memorizing operator symbols and became about breaking a real-world rule ("you can drive if you're old enough and you have ID") into the exact boolean pieces Python needs.

Data definition language and data manipulation language

Emilio Ochieng — Thu, 17 Sep 2026 07:52:11 +0000

What is DDL?

DDL- Data Definition Language - is the part of SQL that defines and modifies the structure of a database: the tables, their columns, data types, and constraints. It doesn't touch the data living inside those tables; it shapes the containers the data lives in.

The core DDL commands are:

CREATE - build a new table, schema, or other database object
ALTER - change an existing table's structure (add/remove/modify columns)
DROP - delete a table or object entirely, structure and data both
TRUNCATE - empty a table of all its rows while keeping the structure intact

What is DML?

DML - Data Manipulation Language - is what you use once the structure already exists. It reads and changes the data inside the tables DDL created:

INSERT - add new rows
UPDATE - modify existing rows
DELETE - remove rows
SELECT - read rows (some references classify this separately as DQL, Data Query Language, since it only reads and never changes anything - but it's commonly grouped with DML in practice)

The distinction, in plain terms

DDL answers "what does the data look like?" DML answers "what does the data say, right now?" You use DDL once (or occasionally, when the schema evolves) and DML constantly, every time a row needs to be added, changed, or removed.

Practical examples - from the Sunrise Supermarket project

When I built the Sunrise Supermarket database, the very first statements were pure DDL - defining the shape before anything could be stored in it:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone_number VARCHAR(20) UNIQUE NOT NULL,
    city VARCHAR(50) NOT NULL,
    loyalty_points INT DEFAULT 0
);

If I later decided customers needed a signup date, that's DDL too — I'm changing the shape of the table, not its contents:

ALTER TABLE customers
ADD COLUMN signup_date DATE DEFAULT CURRENT_DATE;

Once the structure existed, everything after that was DML. Populating the table:

INSERT INTO customers (customer_id, full_name, email, phone_number, city)
VALUES (1, 'Emilio ochieng', 'emilioochieng44@gmail.com', '0705216347', 'Nairobi');

Changing a row already there:

UPDATE orders
SET status = 'Delivered'
WHERE order_id = 2;

And removing one:

DELETE FROM orders
WHERE order_id = 4;

What I understood from this

The distinction sounds academic until you actually build something - then it becomes obvious that DDL mistakes are expensive (drop the wrong table, and the data's gone with it) while DML mistakes are usually recoverable (a bad UPDATE can be corrected with another UPDATE). That's part of why I lean on constraints - NOT NULL, UNIQUE, CHECK - at the DDL stage: they're a one-time investment that quietly prevents an entire category of bad DML later. A CHECK (unit_price > 0) on the products table means no INSERT or UPDATE can ever sneak a negative price in, no matter who's writing the query.

Loops

Emilio Ochieng — Thu, 17 Sep 2026 07:36:40 +0000

Why loops matter

A loop is a control flow structure that executes a block of code repeatedly as long as a condition holds. They matter for three reasons: they reduce redundancy (no manually retyping the same statement), they save time and space (keeping code short and maintainable), and they handle dynamic data (processing a list or user input of any size without rewriting the code for each possible length).

for loops

Use a for loop when you know exactly what you're iterating over - a list, a range of numbers, the characters in a string:

items = ['sugar', 'salt', 'flour', 'soap', 'oil']
for i in items:
    print(i)

# output
# sugar
# salt
# flour
# soap
# oil

for loops pair naturally with range() when you need to repeat something a fixed number of times rather than iterate over existing items:

for i in range(5):
    print("Attempt number", i)

while loops

Use a while loop when the number of repetitions isn't known in advance and depends on a condition staying true:

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

The loop keeps running as long as count < 5 evaluates to True. Forgetting to update count inside the loop is the classic mistake here - it creates an infinite loop, since the condition never becomes false.

break and continue

break exits the loop immediately, skipping any remaining iterations entirely.
continue skips just the current iteration and moves on to the next one, without exiting the loop.

for num in range(10):
    if num == 5:
        break          # stop the loop entirely once we hit 5
    print(num)
# prints 0, 1, 2, 3, 4

for num in range(10):
    if num % 2 == 0:
        continue        # skip even numbers, keep looping
    print(num)
# prints 1, 3, 5, 7, 9

enumerate()

enumerate() gives you both the index and the value while looping over something - useful whenever you need to know where an item is, not just what it is:

items = ['sugar', 'salt', 'flour']
for index, item in enumerate(items):
    print(index, "->", item)

# output
# 0 -> sugar
# 1 -> salt
# 2 -> flour

Without enumerate(), getting the index would mean manually tracking a separate counter variable and incrementing it yourself each pass through the loop.

Practical examples

Combining several of these — looping through a list, using enumerate() for numbering, and continue to skip an item that doesn't meet a condition:

prices = [180, 320, 85, 60, 0]

for index, price in enumerate(prices):
    if price == 0:
        continue   # skip invalid entries
    print(f"Item {index + 1}: KES {price}")

What I understood from this

break and continue felt interchangeable at first glance, but they solve different problems - break is "I'm done here entirely," continue is "not this one, but keep going." Mixing them up either cuts a loop short when it should have kept processing the rest of the list, or keeps a loop running past a point where it should have stopped. enumerate() was the other thing that changed how I write loops day to day - before I understood it, I was manually creating and incrementing a counter variable alongside every for loop that needed one, which is exactly the kind of repetitive bookkeeping loops are supposed to eliminate in the first place.

Subqueries & CTEs

Emilio Ochieng — Mon, 07 Sep 2026 15:11:35 +0000

What are subqueries?

A subquery is a query nested inside another query - it runs first, and its result is used by the outer query, whether that's as a filter condition, a computed value, or a virtual table to select from. Subqueries can appear almost anywhere: inside a WHERE clause, a SELECT list, or a FROM clause.

There are a few flavors worth knowing:

Scalar subquery - returns a single value, usable anywhere a single value is expected.
Row/column subquery - returns a set of values, often used with IN or ANY/ALL.
Correlated subquery - references a column from the outer query, so it re-runs once per row of the outer query rather than just once overall.

What are CTEs?

A CTE (Common Table Expression) is a named, temporary result set defined with a WITH clause at the start of a query, which you can then reference like a regular table for the rest of that query. It exists only for the duration of that one query.

WITH high_value_orders AS (
    SELECT order_id, customer_id
    FROM orders
    WHERE order_id IN (
        SELECT order_id FROM order_items WHERE quantity > 3
    )
)
SELECT * FROM high_value_orders;

Differences between them

Readability - a CTE gives a name to an intermediate result and reads top-to-bottom like a sequence of steps; a deeply nested subquery reads inside-out, which gets hard to follow fast.
Reuse - a CTE can be referenced multiple times later in the same query; a subquery has to be rewritten (or the query restructured) if you need that same intermediate result twice.
Recursion - CTEs support WITH RECURSIVE, which lets a query reference itself - useful for hierarchical data like an org chart or a category tree. Plain subqueries can't do this.
Performance - in PostgreSQL, this used to be a bigger differentiator (CTEs were historically an "optimization fence"), but modern Postgres treats simple CTEs and subqueries similarly in most cases. The real reason to reach for one over the other is usually clarity, not speed.

Practical examples and use cases

Using the Sunrise Supermarket schema as the example: finding customers whose orders include at least one item with a quantity above 3 - first as a subquery:

SELECT full_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_id IN (
        SELECT order_id FROM order_items WHERE quantity > 3
    )
);

The same logic as a CTE - flatter, and easier to read as a sequence of "first this, then this":

WITH bulk_items AS (
    SELECT order_id FROM order_items WHERE quantity > 3
),
bulk_orders AS (
    SELECT customer_id FROM orders WHERE order_id IN (SELECT order_id FROM bulk_items)
)
SELECT full_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM bulk_orders);

A correlated subquery example - products priced above the average price within their own category, not the overall average:

SELECT product_name, category, unit_price
FROM products p1
WHERE unit_price > (
    SELECT AVG(unit_price)
    FROM products p2
    WHERE p2.category = p1.category
);

What I understood from this

The nested-subquery version and the CTE version return the exact same result - that's the part that took a moment to click. The choice between them isn't about correctness, it's about who has to read the query six months from now. Once a query needs two or three logical steps chained together, I reach for a CTE almost automatically now, because naming each intermediate step (bulk_items, then bulk_orders) makes the query's intent visible, not just its mechanics.

I Built a Supermarket Database in PostgreSQL - Here's What INNER JOIN vs LEFT JOIN Actually Taught Me

Emilio Ochieng — Sun, 06 Sep 2026 19:43:54 +0000

Most SQL tutorials hand you a dataset that's already loaded and ask you to query it. That skips the part that actually teaches you something: designing the schema, living with your own constraints, and finding out the hard way when INNER JOIN and LEFT JOIN give you different answers to the same-looking question.

So I built Sunrise Supermarket - a small PostgreSQL project modeling customers, products, orders, and order items - to work through that whole arc end to end.

The schema

Four tables, in their own schema:

CREATE SCHEMA IF NOT EXISTS Sunrise_Supermarket;
SET search_path TO Sunrise_Supermarket;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone_number VARCHAR(20) UNIQUE NOT NULL,
    city VARCHAR(50) NOT NULL,
    loyalty_points INT DEFAULT 0
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    category VARCHAR(50) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price > 0),
    stock_quantity INT DEFAULT 0
);

Two small things doing real work here: UNIQUE on email/phone stops duplicate customers, and CHECK (unit_price > 0) means the database itself refuses a free or negative price - no application code required to catch that.

The real design decision: orders vs order_items

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'Pending',
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK(quantity > 0),
    CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(product_id)
);

An order by itself can't say what was bought - that's what order_items is for, pairing each order with a product and quantity so one order can hold multiple products.

ON DELETE CASCADE earns its keep almost immediately. After seeding a cancelled order, I just delete it outright:

DELETE FROM orders WHERE order_id = 4;

No manual cleanup of order_items needed - the cascade handles it.

INNER JOIN vs LEFT JOIN, side by side

This is the part that actually clicked for me. First, an inner join between customers and orders:

SELECT customers.full_name, orders.order_id, orders.status
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

This only returns customers who have placed an order. Zero orders = you don't show up in this result at all.

Now compare that to a left join checking order completeness:

SELECT orders.order_id, orders.order_date, order_items.product_id, order_items.quantity
FROM orders
LEFT JOIN order_items
ON orders.order_id = order_items.order_id;

This keeps every order, even one with no matching line items - filling in NULL where nothing exists. Same join shape, opposite intent. Pick the wrong one and you'll either silently drop rows you needed, or silently include rows you didn't expect.

Pushing it to four tables

Once that clicked, joining across the whole schema wasn't a big leap:

SELECT customers.full_name, orders.order_id, products.product_name, order_items.quantity
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;

And aggregating across that same join to get total quantity sold per product:

SELECT products.product_name, SUM(order_items.quantity) AS total_quantity
FROM products
INNER JOIN order_items ON products.product_id = order_items.product_id
GROUP BY products.product_name;

What I'd tell someone doing this next

Don't skip the insert/update/delete step. It's tempting to seed your tables once and go straight to writing SELECT queries, but running real UPDATE/DELETE statements against your own constraints (and watching ON DELETE CASCADE actually fire) is what proves your schema holds up - not just that it compiles.

And when you hit your first join, write the INNER and LEFT versions of the same query side by side before moving on. Seeing the row count actually differ is worth more than any explanation of the difference.

Repo's on git@github.com:emilioochieng/sql-database-projects.git if you want to see the full thing, including the aggregation queries and the rest of the filtering (BETWEEN, IN, LIKE) .

Building a CV Builder With Live Preview and Zero-Backend Persistence

Emilio Ochieng — Fri, 04 Sep 2026 16:00:46 +0000

Most resume builders make you sign up before you've even decided if the tool is any good. I wanted the opposite: open a page, start typing, and watch a CV take shape in real time — no account, no paywall.

That became CV Builder — and it's gone through two clear phases.

Phase 1: A dumb-simple live preview

The whole app is built around one function. Every keystroke — name, summary, a bullet point — fires the same render call, which rebuilds the preview from scratch based on whatever's in the form:

function renderCV() {
  const name = document.getElementById('f-name').value.trim() || 'Your Name';
  // ...read every field...
  document.getElementById('cv-page').innerHTML = `
    <div class="cv-header">
      <div class="cv-name">${esc(name)}</div>
      ...
    </div>
  `;
}

No diffing, no state library — just "read the form, redraw the page." It sounds too simple, but that's exactly what makes it feel instant.

Two other decisions mattered:

Repeatable sections — Experience and Education are dynamically generated blocks you can add/remove, each wired to the same render call.
One clean template — no tables, no icons, no columns. Just a layout an ATS parser and a human recruiter can both read.

PDF export skips a PDF library entirely — the "Download" button just calls window.print(), with print-only CSS that hides the form and leaves only the CV page. Zero dependencies, and the output always matches the screen exactly.

Phase 2: The obvious problem

Refresh the page, and everything's gone. For a tool you might spend twenty minutes tuning, that's a real cost — and it meant you could only ever work on one CV, in one sitting.

Adding persistence without standing up a backend

Instead of spinning up a server + database + auth, I used a built-in per-user key-value storage layer available directly in the browser context this app runs in. From the app's perspective, it behaves like a small backend: data survives a refresh, survives closing the tab, scoped privately per user.

async function performSave() {
  const data = collectFormData();
  await window.storage.set('cv-builder:' + currentId, JSON.stringify(data), false);
  // update an index of {id, name, updatedAt} so saved CVs are listable
}

What this unlocked:

Auto-save — once a CV's been saved once, edits save in the background on a debounce, with a status dot + timestamp confirming it.
Multiple saved CVs — a dropdown lists everything you've saved, most recent first, so you can keep a different version tailored per role.
Clean delete — removes both the stored data and its index entry, with a confirm step.

The honest limitations

It's personal storage, not multi-tenant — no login system separating one person's data from another's.
It's tied to the environment it runs in — porting this to a fully independent, self-hosted site would mean swapping this for a real database.
No version history — saving overwrites the previous draft.

None of these are hard walls, just the next milestones — real auth + Postgres + a small REST API is the natural v3 if this ever needs to serve more than one person's desk.

The takeaway

Solve the problem in front of you with the smallest tool that does it honestly. A live-preview editor didn't need a framework. A save feature didn't need a server yet. Build the version that's true to what you actually need right now.

I f interested you can have a look and give a feedback on what's need to added git@github.com:emilioochieng/CV_BUILDER.git

Why Python? An Introduction for New Programmers

Emilio Ochieng — Thu, 03 Sep 2026 12:28:48 +0000

Python is a language people learn to code in - and for good reason. What starts as a simple print("Hello World") can grow into a skill used across nearly every industry, from e-commerce and logistics to firmware design and machine learning. This article walks through what makes Python approachable for beginners, and introduces the core building blocks you'll use in almost every Python program you write.

What Makes Python Easy to Learn?

A few things set Python apart as a beginner-friendly language:

Simple, readable syntax. Python code reads almost like plain English, which makes it easier to understand what a program is doing without memorizing a lot of symbols and rules.
A huge ecosystem of libraries. Whether you need to do arithmetic, deploy a machine learning model, or build a web application, there's very likely already a Python library for it.
A large, active community. Thousands of developers build and maintain these libraries, which means help, documentation, and examples are never far away.

Your First Line of Python

The most fundamental piece of code most programmers write first is the print() function:

print("Hello World")

The print() function sends data to the standard output device - usually your terminal or console - so you can see the result of your code.

Variables: Storing Data

A variable is a named storage location in your computer's memory that temporarily holds data while a program runs. Every variable has three key properties:

The name (identifier) - the label you use to refer to the data in your code.
The value - the actual data stored inside it.
The data type - the category of data being stored.

Python has four basic data types you'll encounter constantly:

Type	Description	Example
Integer (`int`)	A whole number	`age = 20`
Float / Double	A decimal number	`height = 1.93`
String (`str`)	Text	`greeting = "Hello World"`
Boolean (`bool`)	`True` or `False`	`under 18 = false`

In each of these examples, the equal sign (=) is the assignment operator - it takes the value on the right and stores it in the variable name on the left.

Operators: Doing Something With Data

An operator is a symbol or keyword that tells the computer to perform a mathematical, relational, or logical operation on data. Python has four common categories:

Arithmetic Operators

Used for basic math: +, -, *, /, %

a = 100
b = 200
c = 300
d = 10
e = 3

total = a + b + c   # 600
diff = b - a         # 100
rem = d % e           # 1
div = c / d           # 100

Assignment Operators

Used to assign or update the value stored in a variable: =, +=, -=, *=

score += count   # equivalent to: score = score + count

Comparison / Relational Operators

Used to compare conditions: and, or, not

if age == 18 and id_present == True:
    print("You can vote")

Loops: Repeating Actions Efficiently

A loop is a control flow structure that executes a block of code repeatedly as long as a condition is met. Loops matter because they:

Reduce redundancy - you don't have to manually repeat the same statement over and over.
Save time and space - they keep your codebase short, clean, and efficient.
Handle dynamic data - they let you process lists or user input of any size without rewriting your code.

The `for` Loop

Use a for loop when you know exactly how many times - or over exactly what items - you need to repeat an action:

items = ['Mercedes', 'BMW', 'Subaru', 'Mazda', 'Tesla']
for i in items:
    print(i)

# output
# Mercedes 
# BMW
# Subaru
# Mazda
# Tesla

The `while` Loop

Use a while loop when the number of repetitions is unknown and depends on a condition remaining true:

while True:
    if i < 10:
        i += 1
    else:
        break

Functions: Packaging Reusable Logic

A function is a self-contained, reusable block of code designed to perform one specific task. Instead of repeating the same instructions throughout your program, you package them inside a function, give it a meaningful name, and call it whenever you need it.

def sum(a, b):              # declaration & parameters
    result = a + b          # processing (body)
    return result            # return value

total = sum(100, 50)       # calling the function -> 150

Breaking that down:

Name & parameters (inputs) - a and b are placeholders for the data you pass into the function.
The body - the indented lines below the function name that carry out the actual logic.
The return value - the return statement sends the final result back to the main program and ends the function.
The function call - writing the function's name followed by parentheses, like sum(100, 200), tells Python to run that block of code and hand back the result.

Conclusion

Every program starts as an idea. Before you write a single line of code, you can sketch that idea in pseudocode: a plain-language outline of what your program should do, step by step. The real work is turning that outline into code the computer can run, and this is where Python shines.

Python reads almost like the outline you started with. You need fewer lines and less time to go from "here's what I want to build" to "it works," and you spend your energy solving the problem instead of fighting the syntax. That's what makes it such a great first language.

So don't wait until you feel ready. Pick a small idea, write it out in plain steps, and translate it into Python. It won't be perfect, and that's fine. Every developer you admire started with a messy first script.

Now it's your turn: what's the first thing you want to build with Python? A calculator, a to-do list, a simple game? Share your idea in the comments. You might inspire another beginner to start too, and I'd love to hear what you come up with.

Introduction to SQL: Understanding Databases, Data Types, Constraints, and Core SQL Concepts

Emilio Ochieng — Mon, 20 Jul 2026 16:44:03 +0000

SQL (Structured Query Language) is one of the most fundamental skills for anyone interested in Data Analytics, Data Engineering, Software Development, or Database Administration. Whether you're building a web application or analyzing business data, SQL enables you to communicate with relational databases efficiently.

What is Data?

Data refers to raw, unorganized facts and figures. It can exist in many forms, including:

Numbers
Text
Images
Audio
Videos
Dates and times

On its own, data has little meaning until it is organized and processed.

What is a Database?

A database is an organized collection of data that is structured for easy access, management, and updating.

Instead of storing information in multiple spreadsheets, databases keep related information together, making it easier to search, retrieve, and maintain.

For example, a school database may contain:

Students
Teachers
Subjects
Exam Results
Understanding Database Architecture

A database system is organized into different layers.

1. Server

The server is the top-most level.

It is the actual software process (such as PostgreSQL or MySQL) running on a computer.

Think of the server as the entire building.

2. Database

Inside a server are one or more databases.

A database acts like a separate floor within the building, storing data for a specific application or organization.

3. Schema

A schema organizes database objects inside a database.

Think of a schema as rooms on a floor, helping separate tables based on their purpose.

For example:

Server
│
├── Greenwood Academy Database
│ ├── Students Schema
│ ├── Finance Schema
│ └── Library Schema

What is a DBMS?

A Database Management System (DBMS) is software that enables users to create, manage, update, and interact with databases.

Popular DBMSs include:

PostgreSQL
MySQL
Oracle Database
Microsoft SQL Server

A DBMS provides the tools needed to store, organize, and retrieve information efficiently.

What is SQL?

SQL (Structured Query Language) is the standard language used to communicate with relational databases.

Think of SQL as the language you use to "talk" to your database.

Using SQL, you can:

Create databases
Create tables
Insert data
Update records
Delete records
Retrieve information
Manage users and permissions
Types of SQL Commands

SQL is divided into several categories depending on the task being performed.

*1. DDL (Data Definition Language)
*
DDL focuses on creating and modifying database structures.

Common commands include:

CREATE
ALTER
DROP

Example:

CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50)
);
2. DML (Data Manipulation Language)

DML works with the data stored inside existing tables.

Common commands include:

*INSERT
UPDATE
DELETE
*
Example:

INSERT INTO students
VALUES (1, 'Amina');
*3. DQL (Data Query Language)
*
DQL is used to retrieve information from a database.

The primary command is:

SELECT

Example:

SELECT *
FROM students;

This is the command you'll use most frequently when analyzing data.

*4. DCL (Data Control Language)
*
**DCL **controls access to the database.

Examples include granting or revoking user permissions.

Common commands:

GRANT
REVOKE *5. TCL (Transaction Control Language) * TCL manages database transactions.

Common commands include:

COMMIT
ROLLBACK
SAVEPOINT These commands ensure data consistency when multiple operations are performed.

Understanding Data Types

Every column in a database must specify the type of data it will store.

Choosing the correct data type improves performance, accuracy, and storage efficiency.

Numeric Data Types
INT

Stores whole numbers.

Example:

25
100
500
DECIMAL / NUMERIC

Stores numbers with fixed decimal places.

Ideal for:

Prices
Salaries
Financial records Example:

2500.75
99.99
SERIAL

Automatically generates sequential numbers.

Commonly used for:

Primary Keys
IDs
Text Data Types
CHAR(n)

Stores text with an exact number of characters.

Example:

CHAR(10)

Useful for fixed-length values like codes.

VARCHAR(n)

Stores text with a maximum length.

Example:

VARCHAR(50)

Suitable for names and addresses.

TEXT

Stores large amounts of text without a predefined limit.

Useful for:

Descriptions
Articles
Comments
Date and Time Data Types
DATE

Stores only the date.

Example:

2025-07-20
TIME

Stores only the time.

Example:

14:30:00
TIMESTAMP

Stores both the date and time.

Example:

2025-07-20 14:30:00
BOOLEAN

A Boolean column stores only two possible values:

TRUE
FALSE

Example:

is_active = TRUE
Understanding Constraints

Constraints are rules applied to tables or columns to ensure data integrity and prevent invalid information from entering the database.

NOT NULL

Ensures a column cannot be left empty.

Example:

Every student must have a first name.

DEFAULT

Provides a default value when none is supplied.

Example:

status DEFAULT 'Active'
UNIQUE

Ensures duplicate values are not allowed.

Example:

Two users cannot register with the same email address.

PRIMARY KEY

Uniquely identifies each row in a table.

A Primary Key:

Cannot be NULL
Must be unique

Example:

student_id
FOREIGN KEY

Links one table to another.

For example:

Students

student_id

Exam Results

student_id

The student_id in the Exam Results table must already exist in the Students table.

This relationship prevents invalid references.

CHECK

Applies custom validation rules.

Example:

CHECK (marks >= 0)

CHECK (price > 0)

The database will reject values that violate these conditions.

Why These Concepts Matter

Before writing complex SQL queries involving joins, aggregations, or window functions, it's essential to understand:

How databases are organized.
The purpose of schemas and tables.
Choosing the correct data types.
Using constraints to maintain clean and reliable data.
The different categories of SQL commands.

JOINs – Combining Data from Multiple Tables

In a relational database, information is often split across multiple tables to reduce redundancy and improve organization. JOINs allow you to combine related data from these tables into a single result.

For example, imagine a school database with three tables:

Students
Subjects
Exam Results

Instead of storing all information in one table, the database links them using keys.
A JOIN lets you answer questions like:

Which subjects is each student taking?
What marks did each student score?
Who teaches each subject?

Common types of JOINs include:

INNER JOIN – Returns only matching records from both tables.
LEFT JOIN – Returns all records from the left table and matching records from the right table.
RIGHT JOIN– Returns all records from the right table and matching records from the left table.
FULL OUTER JOIN – Returns all records from both tables, whether they match or not.

JOINs are among the most frequently used SQL operations because real-world databases almost always store information across multiple related tables.

2. Aggregate Functions – Summarizing Data

Aggregate functions calculate values across multiple rows and return a single result. Instead of viewing individual records, aggregates help summarize and analyze data.

Some common aggregate functions include:

COUNT() – Counts the number of records.
SUM() – Calculates the total.
*AVG() *– Finds the average.
*MIN() *– Returns the smallest value.
MAX() – Returns the largest value. For example, a school administrator might want to know:

How many students are enrolled?
What is the average exam score?
Which student scored the highest mark?
How many students are in each class?

Aggregate functions are widely used in business intelligence, reporting, and dashboard development.

3. Subqueries – Queries Within Queries

A subquery is a SQL query nested inside another SQL query. It allows you to use the result of one query as input for another.

Subqueries are useful when solving more complex problems, such as:

Finding students who scored above the class average.
Identifying products with sales higher than the average.
Listing employees earning more than their department's average salary.

Instead of performing multiple separate queries, SQL can handle everything in one statement.

Subqueries make SQL more flexible and allow you to solve sophisticated analytical problems with minimal code.

4. Common Table Expressions (CTEs) – Writing Cleaner SQL

As SQL queries become longer, they can become difficult to read and maintain.

Common Table Expressions (CTEs) help organize complex queries by breaking them into logical sections.

A CTE acts like a temporary named result set that exists only during the execution of a query.

Benefits of using CTEs include:

Improved readability.
Easier debugging.
Better organization of complex logic.
Simplified maintenance. Instead of writing deeply nested subqueries, you can separate each logical step into its own CTE, making your SQL easier for both you and your teammates to understand.

CTEs are especially useful in reporting, analytics, and data engineering workflows.

Window Functions – Performing Advanced Analytics

Window functions are among the most powerful features in SQL. Unlike aggregate functions, which reduce multiple rows into one result, window functions perform calculations across related rows while keeping every individual row in the output.

This makes them ideal for analytical tasks such as:

Ranking students by exam score.
Comparing a student's mark to the class average.
Calculating running totals.
Finding previous or next values.
Identifying top-performing products or employees. .

Common window functions include:

**-ROW_NUMBER()

RANK()
DENSE_RANK()
LAG()
LEAD()
FIRST_VALUE()
LAST_VALUE()**

Window functions are heavily used in business intelligence, financial reporting, customer analytics, and machine learning data preparation.

How These Concepts Work Together

Consider a school management system.

You might:

Use JOINs to combine student, subject, and exam data.
Apply aggregate functions to calculate average marks.
Use a subquery to identify students scoring above average.
Organize the logic using a CTE for better readability.
Apply window functions to rank students from highest to lowest. Each concept builds upon the previous one, enabling increasingly sophisticated analysis.

Why Every Data Professional Should Learn Advanced SQL

Modern organizations rely heavily on data-driven decision-making. Advanced SQL skills enable professionals to:

Build interactive dashboards.
Generate business reports.
Analyze customer behavior.
Monitor financial performance.
Prepare datasets for machine learning.
Design efficient data pipelines.
Support business intelligence initiatives.

Whether you're working in healthcare, finance, agriculture, education, or e-commerce, these SQL techniques are essential for extracting meaningful insights from data.

Conclusion

Mastering advanced SQL is a natural progression after learning the fundamentals. Concepts like databases, data types, constraints, SQL command categories ,JOINs, aggregate functions, subqueries, Common Table Expressions (CTEs), and window functions enable you to move beyond basic queries and solve complex, real-world data challenges.

These skills are not only valuable for writing efficient SQL—they are also core competencies for careers in Data Analytics, Data Engineering, Business Intelligence, Database Administration, and Software Development.

Every advanced SQL expert started with the basics. By practicing these concepts consistently and applying them to real projects, you'll build the confidence and expertise needed to work with large datasets, develop insightful reports, and create data-driven solutions that make a meaningful impact.

DEV Community: Emilio Ochieng

Object-Oriented Programming Explained

SQL Functions

What are SQL functions?

Common functions

Examples - from the Sunrise Supermarket project

When to use them

What I understood from this

Functions

Defining and calling functions

Parameters and arguments

Return values

Scope

Practical examples

What I understood from this

Python Data Structures

Lists

Tuples

Dictionaries

Sets

When to use each

Practical examples

What I understood from this

Operators & Conditionals

Arithmetic operators

Comparison operators

Logical operators

Conditionals: if, elif, else

Practical examples

What I understood from this

Data definition language and data manipulation language

What is DDL?

What is DML?

The distinction, in plain terms

Practical examples - from the Sunrise Supermarket project

What I understood from this

Loops

Why loops matter

for loops

while loops

break and continue

enumerate()

Practical examples

What I understood from this

Subqueries & CTEs

What are subqueries?

What are CTEs?

Differences between them

Practical examples and use cases

What I understood from this

I Built a Supermarket Database in PostgreSQL - Here's What INNER JOIN vs LEFT JOIN Actually Taught Me

The schema

The real design decision: orders vs order_items

INNER JOIN vs LEFT JOIN, side by side

Pushing it to four tables

What I'd tell someone doing this next

Building a CV Builder With Live Preview and Zero-Backend Persistence

Phase 1: A dumb-simple live preview

Phase 2: The obvious problem

Adding persistence without standing up a backend

The honest limitations

The takeaway

Why Python? An Introduction for New Programmers

What Makes Python Easy to Learn?

Your First Line of Python

Variables: Storing Data

Operators: Doing Something With Data

Arithmetic Operators

Assignment Operators

Comparison / Relational Operators

Loops: Repeating Actions Efficiently

The for Loop

The while Loop

Functions: Packaging Reusable Logic

Conclusion

Introduction to SQL: Understanding Databases, Data Types, Constraints, and Core SQL Concepts

What is Data?

What is a Database?

A database system is organized into different layers.

What is a DBMS?

The `for` Loop

The `while` Loop