DEV Community: Gideon Kiprono

Python Data structures

Gideon Kiprono — Thu, 22 Oct 2026 17:02:18 +0000

Introduction

When you go to a grocery store, you don't throw eggs, milk, and apples loosely into a single giant box. You instead put the eggs in a carton, the milk in a jug, and the apples in a bag.
In programming, data structures are those containers. They are specialized ways of organizing and storing data in your computer's memory so that you can access, sort, and modify your information efficiently.

1. The Core Four Data Structures

Python has four built-in data structures that handle almost every data organization task you will encounter.

Lists

A list is an ordered, mutable collection of items enclosed in square brackets [], with elements separated by commas.

Ordered: Items maintain their position and can be accessed using an index, starting from 0.
Mutable: Items can be added, removed, or modified after the list is created.
Allows duplicates: A list can contain repeated values.
Supports different data types: A list can contain strings, integers, floats, booleans, and other objects.

Code Snippet:


# =====================================
# UNDERSTANDING LISTS IN PYTHON
# =====================================

# 1. Creating a list
shopping_list = ["Milk", "Eggs", "Bread"]

print("Original List:", shopping_list)

# 2. ORDERED - Accessing items using indexes
print("\nFirst Item:", shopping_list[0])
print("Second Item:", shopping_list[1])

# 3. MUTABLE - Adding a new item
shopping_list.append("Butter")
print("\nAfter Adding Butter:", shopping_list)

# 4. MUTABLE - Modifying an existing item
shopping_list[1] = "Cheese"
print("After Replacing Eggs:", shopping_list)

# 5. MUTABLE - Removing an item
shopping_list.remove("Bread")
print("After Removing Bread:", shopping_list)

# 6. ALLOWS DUPLICATES
shopping_list.append("Milk")
print("\nAfter Adding Duplicate Milk:", shopping_list)

# 7. Checking the number of items
print("Total Items:", len(shopping_list))

Output:

Common List Operations in Python

Operation	Python Code	Purpose
Create	`items = ["A", "B"]`	Creates a list.
Access	`items[0]`	Gets the first item.
Add	`items.append("C")`	Adds an item at the end of the list.
Insert	`items.insert(1, "C")`	Inserts an item at a specified index.
Modify	`items[0] = "D"`	Changes an existing item.
Remove	`items.remove("B")`	Removes the first matching item.
Delete	`items.pop()`	Removes and returns the last item.
Count	`len(items)`	Returns the number of items in the list.

Key Takeaway: Python uses zero-based indexing, meaning the first item is at position 0, not 1.

Tuples

A tuple is an ordered, immutable collection of items, typically enclosed in parentheses () and separated by commas.

Unlike lists, tuples cannot be modified after creation. This means you cannot add, remove, or replace individual elements within a tuple.

Key characteristics of tuples:

Ordered: Elements maintain their position and can be accessed using indexes, starting from 0.
Immutable: Elements cannot be added, removed, or reassigned after the tuple is created.
Allows duplicates: Tuples can contain repeated values.
Supports different data types: A tuple can store strings, integers, floats, and other Python objects.
Supports unpacking: Values can be assigned to individual variables.

Code Snippet:


# =====================================
# UNDERSTANDING TUPLES IN PYTHON
# =====================================

# 1. Creating a tuple
# Fixed GPS coordinates (Latitude, Longitude)
nairobi_coords = (-1.218, 36.889)

print("Original Coordinates:", nairobi_coords)

# 2. ORDERED - Accessing elements by index
print("\nLatitude:", nairobi_coords[0])
print("Longitude:", nairobi_coords[1])

# 3. UNPACKING - Assigning values to variables
latitude, longitude = nairobi_coords

print("\nUnpacked Latitude:", latitude)
print("Unpacked Longitude:", longitude)

# 4. IMMUTABLE - Attempting to modify a tuple
try:
    nairobi_coords[0] = -1.300

except TypeError:
    print("\nERROR: Tuples cannot be modified!")

# 5. LENGTH - Counting elements
print("\nTotal Elements:", len(nairobi_coords))

Output:

Lists vs Tuples in Python

Feature	List `[]`	Tuple `()`
Ordered	Yes	Yes
Mutable	Yes	No
Allows duplicates	Yes	Yes
Indexing	Yes	Yes
Supports different data types	Yes	Yes
Add/remove elements	Yes	No
Common use	Shopping lists, inventory, changing data	Coordinates, fixed configuration values, records

Key Takeaway:

Lists: Use when you need an ordered collection of items that can be modified.
Tuples: Use when you need an ordered collection whose elements should remain unchanged.

Dictionaries

A dictionary is a mutable collection of data stored in key-value pairs, enclosed in curly braces {}.

Unlike lists and tuples, which primarily access elements using numerical indexes, dictionaries allow you to retrieve values using unique keys.

Each key acts as an identifier that maps to a corresponding value, similar to how a word in a physical dictionary maps to its definition.

Key characteristics of dictionaries:

Key-value pairs: Each element consists of a key and its associated value.
Mutable: You can add, update, or remove key-value pairs after creation.
Unique keys: Duplicate keys are not allowed. Assigning a value to an existing key replaces its previous value.
Insertion ordered: Dictionaries preserve the order in which keys are inserted (Python 3.7+).
Flexible data types: Values can contain strings, numbers, lists, tuples, or even other dictionaries.
Fast lookups: Dictionaries typically provide efficient access to values through their keys.

Code snippet:


# =====================================
# UNDERSTANDING DICTIONARIES IN PYTHON
# =====================================

# 1. Creating a dictionary
user_profile = {
    "username": "dev_alex",
    "age": 28,
    "clearance": "admin",
    "active": True
}

print("Original Profile:", user_profile)

# 2. ACCESSING VALUES USING KEYS
print("\nUsername:", user_profile["username"])
print("Clearance:", user_profile["clearance"])

# 3. SAFE ACCESS USING .get()
print("Department:", user_profile.get("department", "Not Assigned"))

# 4. MUTABLE - Adding a new key-value pair
user_profile["department"] = "Engineering"
print("\nAfter Adding Department:", user_profile)

# 5. MUTABLE - Updating an existing value
user_profile["clearance"] = "super_admin"
print("Updated Clearance:", user_profile["clearance"])

# 6. MUTABLE - Removing a key-value pair
user_profile.pop("age")
print("\nAfter Removing Age:", user_profile)

# 7. CHECKING IF A KEY EXISTS
if "username" in user_profile:
    print("\nUsername exists in the dictionary")

# 8. ITERATING THROUGH KEY-VALUE PAIRS
print("\n--- USER PROFILE DETAILS ---")

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

# 9. COUNTING KEY-VALUE PAIRS
print("\nTotal Entries:", len(user_profile))

Output:

Lists vs Tuples vs Dictionaries in Python

Feature	List `[]`	Tuple `()`	Dictionary `{}`
Ordered	Yes	Yes	Yes (insertion order)
Mutable	Yes	No	Yes
Allows duplicates	Yes	Yes	Values: Yes; Keys: No
Access elements	Numerical index	Numerical index	Unique keys
Supports mixed data types	Yes	Yes	Yes
Add/remove elements	Yes	No	Yes
Common use	Shopping lists, inventory	Coordinates, fixed records	User profiles, configurations, structured records

Key takeaway:

Dictionaries are ideal when you want to store, organize, and retrieve related information using meaningful keys rather than numerical positions.
They are widely used in Python applications, JSON processing, APIs, data engineering, and configuration management.

Sets

A set is an unordered, mutable collection of unique elements, typically enclosed in curly braces {}.

Unlike lists and tuples, sets do not allow duplicate values. If an element appears more than once, Python automatically keeps only one occurrence.

Sets are particularly useful for removing duplicates, checking membership, and performing mathematical operations such as unions, intersections, and differences.

Key characteristics of sets:

Unordered: Elements do not have fixed positions or numerical indexes.
Mutable: You can add or remove elements after creating a set.
Unique elements: Duplicate values are automatically eliminated.
No indexing: You cannot access elements using positions such as my_set[0].
Hashable elements: Set elements must be hashable, such as integers, strings, or tuples containing hashable values. Lists and dictionaries cannot be direct set elements.
Efficient membership testing: Sets are useful for quickly checking whether an item exists in a collection.

Code snippet:


# =====================================
# UNDERSTANDING SETS IN PYTHON
# =====================================

# 1. CREATING A SET
# Duplicate employee ID 101 is automatically removed
unique_ids = {101, 102, 103, 101}

print("Original Set:", sorted(unique_ids))

# 2. MUTABLE - Adding a new element
unique_ids.add(104)
print("\nAfter Adding 104:", sorted(unique_ids))

# 3. UNIQUE ELEMENTS - Adding a duplicate
unique_ids.add(102)
print("After Adding Duplicate 102:", sorted(unique_ids))

# 4. MUTABLE - Removing an element
unique_ids.remove(103)
print("\nAfter Removing 103:", sorted(unique_ids))

# 5. MEMBERSHIP TESTING
if 101 in unique_ids:
    print("\nEmployee ID 101 exists in the system.")

# 6. SET OPERATIONS
morning_shift = {101, 102, 105}
evening_shift = {102, 104, 106}

# UNION - All unique employees across both shifts
print("\nUnion:", sorted(morning_shift | evening_shift))

# INTERSECTION - Employees in both shifts
print("Intersection:", sorted(morning_shift & evening_shift))

# DIFFERENCE - Employees only in the morning shift
print("Difference:", sorted(morning_shift - evening_shift))

# 7. COUNTING UNIQUE ELEMENTS
print("\nTotal Unique IDs:", len(unique_ids))

Output:

Note: I used sorted() to display the elements in ascending order, making the output easier to follow. It returns a sorted list without changing the original set. Sets themselves do not guarantee an iteration order.

Comparison of Python Collections

Feature	List `[]`	Tuple `()`	Dictionary `{}`	Set `{}`
Ordered	Yes	Yes	Yes	No
Mutable	Yes	No	Yes	Yes
Allows Duplicates	Yes	Yes	Keys: No, Values: Yes	No
Supports Indexing	Yes	Yes	Key-based access	No
Add/Remove Elements	Yes	No	Yes	Yes
Common Use	Shopping lists, inventory	Coordinates, fixed records	User profiles, configurations	Unique IDs, removing duplicates

Key takeaway: Use sets when you need to store unique values, remove duplicates, perform membership checks efficiently, or compare collections using mathematical set operations.

For example, in data analytics, sets can help identify customers appearing in two datasets, detect unique product IDs, and compare records between different data sources.

2. Cheat Sheet: When to Use Each One

Choosing the right structure keeps your program fast and free of bugs. Use this simple rule of thumb:

Data Structure	Best Used For...	Example
List `[]`	Collections of data that need to stay in order and might change later.	A list of task names in a to-do app.
Tuple `()`	Fixed, read-only data that must stay safe from accidental changes.	Days of the week or config settings.
Dictionary `{}`	Complex data that needs descriptive labels for fast lookups.	A product page with price, color, and size.
Set `{}`	Finding unique values or running math checks like finding overlaps.	Tracking unique visitors to a website.

3. Practical Example: The Event Registration Manager

Let's bring all four data structures together into a single program that manages guest attendance for a tech conference.

Code snippet:

# ------------Event Registration Manager--------------------
# 1. TUPLE: Fixed event information that should never change
EVENT_INFO = ("Global Tech Summit", "Main Arena", "Day 1")

# 2. LIST: The active queue of arriving VIPs (we need order and flexibility)
vip_queue = ["Sarah", "Michael", "David"]
vip_queue.append("Elena")  # A new VIP arrives and is added to the end

# 3. DICTIONARY: Attendee profiles for quick lookup by ticket ID
attendee_profiles = {
    "TK-890": {"name": "Sarah", "type": "VIP", "checked_in": True},
    "TK-432": {"name": "John", "type": "Standard", "checked_in": False},
    "TK-112": {"name": "Michael", "type": "VIP", "checked_in": True}
}

# 4. SET: Tracking unique industries represented (no duplicates allowed!)
industries_present = {"Finance", "Healthcare", "Tech", "Finance"} # 'Finance' is repeated

# --- PROCESSING THE EVENT DATA ---
print(f"--- WELCOME TO THE {EVENT_INFO[0].upper()} ---")
print(f"Location: {EVENT_INFO[1]} | Schedule: {EVENT_INFO[2]}\n")

# Looking up a profile using a Dictionary Key
search_ticket = "TK-890"
guest_name = attendee_profiles[search_ticket]["name"]
print(f"Ticket Lookup: Ticket {search_ticket} belongs to {guest_name}.")

# Displaying the VIP order from our List
print(f"Next VIP in line to speak: {vip_queue[0]}")

# Displaying our Set (Notice how the duplicate 'Finance' is automatically gone)
print(f"Unique industries attending today: {industries_present}")

Output:

Tuple Protection: EVENT_INFO guarantees that nobody can accidentally overwrite the event name or location mid-program.
List Flexibility: vip_queue preserves the order of arrivals and lets us pull or add speakers dynamically.
Dictionary Speed: attendee_profiles allows us to instantly pull up a specific person's profile using their ticket code without scanning the entire database.
Set Cleanliness: industries_present cleans our metadata automatically, ensuring we only see a list of unique industries without any repeating clutter.

Conclusion

Data structures are the architectural backbone of clean code. By mastering lists, tuples, dictionaries, and sets, you no longer just write code instructions—you map out data intentionally. This structural organization is exactly how massive platforms like Spotify manage playlists (lists), handle user accounts (dictionaries), and eliminate duplicate song recommendations (sets).

Python functions

Gideon Kiprono — Thu, 22 Oct 2026 14:57:48 +0000

Introduction

Think about a coffee machine. You do not need to know how the inner water tubes heat up or how the grinder operates every time you want an espresso. You simply press a button, give it coffee beans, and wait for your cup. In programming, functions are your code's buttons. They allow you to bundle up a complex block of code, give it a simple name, and reuse it whenever you want without typing it out over and over again.

1. Defining and calling functions

To use a function, you must complete a two-step process: you build the button (define it), and then you press the button (call it).

Defining: You use the keyword def to tell Python you are making a function, followed by the name of your choice and a colon (:).

Calling: To run that saved code later, you type the function's name followed by parentheses ().

Code Snippet:

# Defining the function
def send_alert():
    print("System warning: Storage space low!")

# Calling the function
send_alert()  # This runs the code inside send_alert

Output: System Warning: Storage space low!

2. Parameters and arguments

Functions become incredibly powerful when you can pass data into them, allowing them to do different work depending on the inputs.

Parameters: These are the placeholder variables you write inside the parentheses when defining the function. Think of them as the slots on the machine.
Arguments: These are the actual, real values you pass into those slots when calling the function.

Code Snippet:

# 'username' is the parameter (the slot)
def greet_user(username):
    print(f"Welcome back, {username}!")

# "Alex" is the argument (the actual data filling the slot)
greet_user("Alex")

Output: Welcome back, Alex!

3. Return values

Sometimes, you don’t just want a function to print text to the screen. You want it to calculate an answer and hand that answer back to the main program so you can use it somewhere else. We do this using the return keyword.

Rule: Once a function hits a return statement, it hands back the value and stops running immediately.

Code Snippet:

def calculate_tax(amount):
    return amount * 0.08

# Saving the handed-back answer into a new variable
final_tax = calculate_tax(100)
print(f"Tax due: ${final_tax}")

Output: Tax due: $8.0

4. Scope

Where you create a variable determines where you are allowed to use it. This concept is called scope.

Local Scope: Variables created inside a function belong exclusively to that function. They disappear as soon as the function finishes running, and the outside world cannot see them.
Global Scope: Variables created outside all functions belong to the main program file. Any function can look at them.

Code Snippet:


# =====================================
# UNDERSTANDING VARIABLE SCOPE IN PYTHON
# =====================================

# GLOBAL VARIABLE
global_msg = "I am a GLOBAL variable!"

def sample_function():

    # LOCAL VARIABLE
    local_msg = "I am a LOCAL variable!"

    print("--- INSIDE THE FUNCTION ---")

    # Accessing a global variable
    print("Global:", global_msg)

    # Accessing a local variable
    print("Local:", local_msg)


# 1. Call the function
sample_function()

# 2. Access variables outside the function
print("\n--- OUTSIDE THE FUNCTION ---")

# Global variable works
print("Global:", global_msg)

# Local variable causes an error
try:
    print("Local:", local_msg)

except NameError:
    print("ERROR: local_msg is not accessible outside the function!")

Output:

Note:

Trying to access a local variable outside its function causes a NameError.
Notice that the try-except block prevents the program from crashing while demonstrating that local_msg is inaccessible outside the function.

5. Practical example : The Automated Shipping Fee Calculator

Let's combine definitions, parameters, return values, and scope rules into a practical project: an e-commerce shipping manager.

Code Snippet:

# --- GLOBAL SCOPE ---
# This variable lives outside and is accessible by any function below
COMPANY_TAX_RATE = 0.05

# 1. Defining a function with parameters and a return value
def calculate_shipping(weight, destination):
    # --- LOCAL SCOPE ---
    # This 'base_fee' variable only exists inside this specific function
    if destination.lower() == "international":
        base_fee = 15.00
    else:
        base_fee = 5.00

    weight_surcharge = weight * 1.50
    total_shipping = base_fee + weight_surcharge
    return total_shipping  # Handing the calculated answer back

# 2. Defining another function that wraps everything together
def generate_invoice(item_name, price, item_weight, destination):
    shipping_cost = calculate_shipping(item_weight, destination)
    tax_cost = price * COMPANY_TAX_RATE  # Using our global variable safely
    grand_total = price + shipping_cost + tax_cost

    print("\n--- OFFICIAL INVOICE ---")
    print(f"Product    : {item_name}")
    print(f"Item Price : ${price:.2f}")
    print(f"Shipping   : ${shipping_cost:.2f}")
    print(f"Tax Amount : ${tax_cost:.2f}")
    print(f"Grand Total: ${grand_total:.2f}")
    print("------------------------")

# 3. Calling our main function with real arguments
generate_invoice("Mechanical Keyboard", 120.00, 3.5, "international")
generate_invoice("Coffee Mug", 15.00, 0.8, "domestic")

Output:

Note:

Modularity: Instead of writing complex shipping math twice, we isolated it in calculate_shipping(). The invoice function simply "calls" it to get the answer.
Scope Protection: The base_fee calculation remains contained inside the shipping loop, keeping the rest of the application clean and conflict-free.
Flexibility: By passing different arguments ("international" vs "domestic"), the identical logic yields customized invoices automatically.

Conclusion

Functions turn your code from long, disorganized scrolls of instructions into crisp, manageable toolboxes. By structuring your applications around defined blocks, parameters, and clean scope rules, you can scale your projects effortlessly without drowning in repetitive code blocks.

Python loops

Gideon Kiprono — Thu, 22 Oct 2026 13:59:18 +0000

Introduction

Imagine having to manually type out a thousand welcome emails or review a million lines of financial transactions one by one. It would be exhausting. Computers excel at doing repetitive tasks without getting tired, and in Python, we use loops to unlock this automation. Loops tell your program to run a specific block of code over and over again instantly, transforming manual data entry into a single click.

1. The Two Types of Loops

Python gives us two primary ways to repeat code, depending on whether we know how many times we need to loop beforehand.

The for Loop

You use a for loop when you have a definite starting and ending point, like a list of items or a specific count. It goes through a collection one item at a time until it reaches the end.

Code snippet:

# Looping through a pre-made list
fruits = ["Apple", "Banana", "Cherry"]
for fruit in fruits:
    print(f"Processing: {fruit}")

Output

The while Loop

You use a while loop when you don’t know exactly how many times you will need to repeat, but you know you want to keep going while a certain condition remains True.

Warning: You must make sure the condition eventually becomes False, or your program will get stuck in an "infinite loop" and freeze!

Code snippet:

countdown = 3
while countdown > 0:
    print(countdown)
    countdown = countdown - 1  # Updates the condition
print("Blast off!")

Output

2. Upgrading Your Loops

Sometimes you need finer control over how a loop behaves while it is running. Python provides built-in tools to change a loop's path or count its steps.

break and continue (The Steering Wheel)

break: This completely stops the loop immediately and jumps out of it, even if the loop wasn't finished. It is perfect for stopping a search early once you find what you need.

Code snippet:

while True:
    name = input('Enter your name (or type quit): ')

    if name == 'quit':
        break

print(f'Hello, {name}')

Output: This loop will still run until you type the input quit.

continue: This skips the rest of the current turn and jumps straight to the start of the next turn. It is useful for skipping over bad or unneeded data.

Code snippet:

for number in:
    if number == 3:
        continue  # Skip 3
    print(number)  #Output ; 1,2,3,4,5 #3 is skipped

Output:

Difference between `break` and `continue`

Feature	`break`	`continue`
Definition	Immediately terminates the entire loop.	Skips the current iteration and moves to the next iteration.
Purpose	Stop a loop completely when a condition is met.	Skip a particular item without stopping the loop.
Effect on loop	Exits the loop immediately.	Keeps the loop running.
Next iteration	Does not execute.	Executes if another iteration is available.
Execution afterward	Continues with the first statement after the loop.	Returns to the loop to process the next iteration.
Common use case	Stop searching when an item is found or a critical condition occurs.	Skip invalid, unwanted, or irrelevant data.
Example condition	`if badge == "STOLEN_BADGE": break`	`if "Guest" in badge: continue`
Effect in the badge example	Stops processing at Entry #4 when a stolen badge is detected.	Skips normal employee logging for Entry #2 (`Guest_Pass`).
Employee_Jordan (Entry #5)	Not processed after `break` at Entry #4.	Would be processed if the stolen badge condition used `continue` instead.
Works with	Both `for` and `while` loops.	Both `for` and `while` loops.

enumerate() (The Step Counter)

Normally, a for loop only gives you the item itself. If you also need to know the tracking number or position (index) of that item, you wrap your list inside enumerate().

Note: Python starts counting positions at 0 instead of 1.

Code snippet:

tasks = ["Email client", "Update spreadsheet", "Call manager"]
for index, task in enumerate(tasks):
    print(f"Task #{index + 1}: {task}")

Output:

3. Practical Example: The Automated Security Badge Scanner

Let's bring all of these concepts together. This program simulates an office security checkpoint. It loops through arriving employees, tracks their entry order, handles guest badges, and completely shuts down if a security threat is detected.

Code snippet:

# --- AUTOMATED CHECKPOINT SCANNER ---

# A list representing badges scanned at the door in order
scanned_badges = ["Employee_Alex", "Guest_Pass", "Employee_Sam", "STOLEN_BADGE", "Employee_Jordan"]

print("--- STARTING DAILY SECURITY LOG ---")

# Using enumerate() to track the entry number alongside the badge name
for entry_number, badge in enumerate(scanned_badges):

    # 1. Using 'break' to instantly halt everything for a severe threat
    if badge == "STOLEN_BADGE":
        print(f"\nCRITICAL WARNING at Entry #{entry_number + 1}!")
        print("Stolen badge detected. Locking down all exit doors immediately.")
        break  # The loop stops dead in its tracks here

    # 2. Using 'continue' to skip standard employee logs for guest routing
    if "Guest" in badge:
        print(f"Entry #{entry_number + 1}: Guest badge detected. Rerouting to front desk...")
        continue  # Skips the normal logging step below and moves to the next badge

    # 3. Normal loop execution for regular employees
    print(f"Entry #{entry_number + 1}: {badge} verified. Access granted.")

print("\n--- SECURITY CHECKPOINT SYSTEM PAUSED ---")

Output:

The forLoop: It automatically moves from the first badge to the last badge without needing manual navigation. -enumerate(): It gives us entry_number, allowing us to print clean logs like "Entry #1" and "Entry #2" dynamically.
break: When it hits the security threat, it completely prevents the rest of the queue (Employee_Jordan) from being evaluated. -** continue**: When it hits the guest pass, it handles the visitor routing but skips printing the "Access granted" line meant only for employees.

Conclusion

Loops are the heavy lifters of programming. By combining for and while loops with steering tools like break, continue, and enumerate(), you can instantly process massive collections of data with minimal code. You have evolved from writing single-file instructions to building powerful, automated pipelines.

Operators and Conditionals

Gideon Kiprono — Wed, 21 Oct 2026 19:01:02 +0000

Introduction

In Python, operators and conditionals are the brain. They allow your code to do math, compare facts, and make independent choices. Instead of just running straight from top to bottom, your program gains the power to adapt dynamically based on the information it receives.

1. Operators

We can simply ay that operators are tools of logic.
Operators are symbols that tell Python to perform specific actions on your data. They fall into three primary groups:

Arithmetic Operators

These are your standard math symbols. They let you perform calculations instantly.

+ (Addition)
- (Subtraction)
* (Multiplcation)
/ (Division)

Code Snippet:

x = 2
y = 3
sum_of_x_and_y = x + y
subtraction_of_x_and_y = x - y
division_of_x_and_y = x / y
multiplication_of_x_and_y = x * y

Comparison Operators

These symbols compare two values and always answer with a Boolean (True or False)

> (Greater than)
< (Less than)
== (Equal to Note: Python uses two equals signs to compare things, since a single equals sign is already used to assign variables!)
!= (Not equal to)

Code snippet:

# Comparison operators
age = 20
score = 75

print(age == 20) #True
print(age==25)   #False
print(age!=18)    #True

print(score>70)    #True
print(score<50)    #False
print(score>=75)   #True
print(score<=74)   #FAlse

Logical Operators

These let you combine multiple comparisons together to check for complex conditions.

and:Returns True only if all pieces are true (e.g., You can log in if your username and password are correct).
or: Returns True if at least one piece is true (e.g., You get a discount if it is Tuesday or if you have a coupon).
not: Flips the answer completely (turns True to False, and vice versa).

2. Making Choices: If, Elif, and Else

Conditionals act like a digital crossroads in your code. They tell Python:

"If this condition is true, run this specific block of code. Otherwise, look at the next option."

Python handles these paths using three simple structural keywords:

if: The initial starting point. If the statement after it is true, Python runs the indented code directly below it.
elif (Short for "Else If"): The backup check. You can use as many elif statements as you want to inspect alternative scenarios if the first if fails.
else: The safety net. It catches everything else if none of the previous conditions were met. It does not require any test condition.

code snippet:

# if / elif/ else(many paths)

score = int(input('Enter score: '))

if score>=80:
    print('Grade A - Excellent')
elif score >= 70:
    print('Grade B - Good')
elif score>=60:
    print('Grace C - Average')
elif score >=50:
    print('Grade D - Below average')
else:
    print('Grade F - Fail')

#Note: Python stops at the fist True condition . The order matters

Remember our syntax rule: The code inside each path must be cleanly indented so Python knows exactly which instruction goes with which choice.

3. Practical Example: The Automated Smart Thermostat

Let's combine all of these operators and conditions into a practical program that mimics a modern smart home system. It checks the current indoor temperature and the home's occupancy status to decide how to manage the HVAC unit.

# --- SMART THERMOSTAT CONTROLLER ---

# 1. Gathering Environmental Data
current_temp = float(input("Enter current room temperature (°F): "))
is_home_empty_input = input("Is the house currently empty? (yes/no): ")

# Convert text input into a true/false Boolean state
is_home_empty = is_home_empty_input.lower() == "yes"

# 2. Evaluating Conditions Using Operators & Conditionals
print("\n--- SYSTEM STATUS REPORT ---")

# Rule 1: Energy saving mode if no one is home
if is_home_empty:
    print("Status: Eco-Mode Active (House Empty). System powered down.")

# Rule 2: It's crowded/occupied AND too hot
elif current_temp >= 75 and not is_home_empty:
    print("Status: Room is too hot. Turning on the Air Conditioning.")

# Rule 3: It's crowded/occupied AND too cold
elif current_temp <= 64 and not is_home_empty:
    print("Status: Room is too cold. Turning on the Central Heating.")

# Rule 4: Temperature is perfectly fine
else:
    print("Status: Temperature is optimal. System idling.")

print("----------------------------")

Arithmetic & Comparisons: It evaluates current_temp >= 75 and matches strings to set is_home_empty.
Logical Operators: It uses and not to ensure the system only runs heaters or coolers when people are actively in the building.
Conditionals: The if-elif-else structure prevents the system from conflicting with itself, selecting exactly one correct mode of operation.

Conclusion

By mastering operators and conditionals, your Python code transitions from a basic calculator to an active decision-maker. You can now write programs that analyze dynamic data, filter user parameters, and adapt on the fly.

Python Basics

Gideon Kiprono — Wed, 21 Oct 2026 17:56:04 +0000

Introduction

Data is the new oil, but Python is the combustion engine. While traditional enterprise tools crash under the weight of modern datasets, Python enables leaders and future professionals to scale operations through automation, predictive analytics, and custom machine learning pipelines.
It is no longer just a programming language, it is the core infrastructure powering algorithmic decision-making in the modern economy.

In this article, we will cover the basics of Python.
This includes;
- Variables
- Data Types
- Input & Output
- Basic Python syntax
- Practical Example

Variables (The Digital Storage Units)

Think of variables as labeled storage tags for data. When you write programs, you constantly need to save information. For example a price, a username, or a calculation result. Variables give you a way to name that information so you can easily find and use it later.
In Python, creating a variable is incredibly straightforward. You simply write the name you want to use, followed by an equals sign (=), and then the data you want to save.

price = 19.09 
username = "Gideon Kiprono"

How It Works

While it is easy to think of a variable as a box holding your data, Python actually treats them like sticky notes.
When you write,
price = 19.99,
Python creates the number 19.99 in the computer's memory, and then sticks the label price onto it. If you decide to change that value later, for example,
price = 24.99,
Python doesn't erase the old number. It simply moves the price label to the new number.

Dynamic Typing

In many other programming languages, you have to tell the computer exactly what kind of data a variable will hold before you can even use it. You have to explicitly state, "This variable will only hold whole numbers," or "This variable will only hold text."
Python is much smarter. It uses a concept called dynamic typing. This means Python looks at the data you provide and figures out what type of information it is automatically.

price = 19.09 #Python automatically knows this is a float number
username = "Gideon Kiprono" #string

This flexibility makes writing code much faster and heavily reduces the amount of setup code you have to type. You can focus purely on solving the problem at hand, rather than managing structural rules.

Data types (The Building Blocks of Information)

Just like we use different tools for different tasks, Python organizes information into different categories called data types. Because Python needs to know how to handle your data, like adding numbers together or combining pieces of text, it assigns a specific type to every piece of information you create.
Here are the four most common data types you will use every day:

Text (Strings)

A string is simply any text data wrapped in quotation marks. It can be a single letter, a full sentence, or even a password. The quotes tell Python to treat the contents as literal text rather than code instructions.

welcome_message = "Welcome to Python!"

Whole Numbers (Integers)

An integer is any whole number without a decimal point. These can be positive or negative. You use integers whenever you need to count distinct items, like items in a shopping cart or website visits.

items = 3

Decimals (Floats)

A floating-point number (or float) is a number that includes a decimal point. You use floats whenever you need precision, such as dealing with money, percentages, or GPS coordinates.

tax_rate = 0.08

True/False States (Booleans)

A boolean represents binary logic and can only have one of two values: True or False. (Note the capital letters!). These act like digital light switches and are vital for helping your program make decisions.

is_logged_in = True

Input and Output (Talking to Your Program )

A program becomes truly useful when it can interact with the outside world. It needs a way to take in information from a user (Input) and a way to display results back to them (Output). Python makes this exchange incredibly easy using two built-in commands.

Output: Showing Results with `print()`

To display text or variables on the screen, you use the print() function. It is the tool your program uses to speak to the screen.

Modern Python uses a feature called f-strings (formatted strings) to easily mix text and variables together. You simply put an f outside the quotation marks and place your variables inside curly braces {}.

name = "Alex"
print(f"Hello, {name}!")  # Displays: Hello, Alex!

Input: Gathering Data with `input()`

To ask a user for information, you use the input() function. This command pauses your program and waits for the user to type something and press Enter.

favorite_color = input("What is your favorite color? ")
print(f"Cool! I like {favorite_color} too.")

Note:

Python always captures user input as text (a string).
If you ask a user for a number to do math with, Python will treat that number like text letters. To fix this, you must wrap the input inside a conversion command like int() (for whole numbers) or float() (for decimals). This process is called type casting.

# Converting text input into a whole number so we can do math
age = int(input("Enter your age: "))
next_year = age + 1
print(f"Next year you will be {next_year} years old.")

Basic Python Syntax

Every language has grammar rules, and programming languages are no different. The rules that govern how you write code are called syntax. Python is famous for having one of the cleanest, most minimalist syntax designs in the world, making it look almost like plain English.
Here are the basic rules you need to know to write valid Python code:

The Power of Whitespace (`Indentation`)

In many programming languages, you have to use messy curly braces {} or keywords like END to show where a block of code begins and finishes. Python simplifies this by using spaces or tabs (called indentation).
By indenting your code, you visually show Python which lines belong together.

if user_is_happy:
    print("Keep doing what you're doing!")  # Indented line belongs to the 'if' statement

Note: Keeping your spacing consistent is vital. If your lines do not align correctly, Python will pause and show an IndentationError.

Case Sensitivity Matters

Python reads code very literally and pays close attention to capitalization. To Python, a lowercase letter and an uppercase letter are entirely different.

username = "Alex"
Username = "Sam"  # Python treats this as a completely separate variable!

Comments

Sometimes you want to leave notes inside your code to explain how a certain part works. You can do this by using the hashtag symbol (#). Anything you type after a # is completely ignored by the computer.

# This is a comment. Python will skip this line entirely.
price = 10.00  # You can also add comments at the end of a line

Practical example

Now that we have covered variables, data types, inputs, outputs, and basic syntax, let’s bring them all together into a real, working program.

The Digital Receipt Calculator

This simple script acts as a receipt calculator. It asks for a product's price, calculates the sales tax, determines the grand total, and prints a clean summary for the user.

# --- DIGITAL RECEIPT CALCULATOR ---

# 1. Inputs & Data Types
# We ask the user for data. We convert the price to a float (decimal) 
# and the quantity to an int (whole number) so we can do math.
product_name = input("Enter the item name: ")
price = float(input("Enter the item price: $"))
quantity = int(input("Enter the quantity purchased: "))

# 2. Variables & Basic Math
# We create variables to calculate and store the totals.
subtotal = price * quantity
tax_rate = 0.08  # Representing an 8% sales tax
calculated_tax = subtotal * tax_rate
grand_total = subtotal + calculated_tax

# 3. Basic Python Syntax (Decision Making)
# We use an 'if' statement with indentations to check for a discount.
if grand_total > 100.00:
    discount_applied = True
    grand_total = grand_total * 0.90  # Apply a 10% discount
else:
    discount_applied = False

# 4. Output
# We use f-strings to display a cleanly formatted receipt back to the user.
print("\n--- FINAL RECEIPT ---")
print(f"Item: {product_name}")
print(f"Subtotal: ${subtotal:.2f}")
print(f"Tax (8%): ${calculated_tax:.2f}")

if discount_applied:
    print("A 10% discount was applied to your order!")

print(f"Grand Total: ${grand_total:.2f}")
print("---------------------")

Code explanation:

Variables: product_name, price, and grand_total hold our information as it moves through the program.
Data Types: We mix strings (text names), floats (prices and taxes), integers (quantities), and booleans (True/False for the discount status).
Input/Output: input() captures what the user typed, and print() cleanly formats and shares the mathematical results.
Basic Syntax: We use hashtags for comments to explain the code, capital letters for True and False, and strict indentation under the if and else lines to control exactly when the discount rules should trigger.

Conclusion

Writing your first lines of Python code is more than just an academic exercise, it is the moment you stop merely consuming technology and start creating it. By understanding how to manipulate variables, manage data types, handle inputs, and structure clean syntax, you have unlocked the foundational blueprint used by software engineers, data scientists, and creators worldwide.
The beauty of Python lies in its scalability. The exact same basic logic you used to build a simple receipt calculator is what powers complex artificial intelligence models, massive data pipelines, and global web applications.

You now have the keys to the language. The best way to master it is to keep building, experimenting, and turning your everyday ideas into working code.

Happy programming!

KIOSK_MANAGER : End to end python project

Gideon Kiprono — Mon, 19 Oct 2026 16:38:51 +0000

This is a write-up on how we built a real stock-and-sales system for a Nairobi kiosk using only the Python basics: variables, conditionals, loops, functions, data structures, strings and files

The problem

A small kiosk in Nairobi tracks everything in a notebook: how many loaves of bread are left, what was sold today, how much money came in. Notebooks get lost, pages get smudged, and adding up a day's sales by hand is slow and error-prone.

Our group was asked to replace the notebook with a program. The owner needs to:

See what is in stock.
Restock items or add new ones.
Sell products and record each sale.
See a sales report.
Search for a product.
Keep everything saved between days.

The twist is that we could only use what we had learned in six sessions of Python: no classes, no try/except, no csv module. That constraint makes it a great end-to-end project, because every basic skill has to earn its place.

Project Scope

Skill	How it shows up in Kiosk Manager
Basics	`input()` prompts, correct data types, f-string output
Conditionals	Is there enough stock? Is the menu choice valid?
Loops	A `while True` menu; `for` loops over sales and stock
Functions	Every feature is its own function, called from a clean `main()`
Data structures	Dictionary for stock, list of tuples for sales, set of unique items sold
Strings and files	Cleaning input with `.strip()`, `.title()`, `.lower()`; saving and loading `.txt` files

Step 1: Design the data

Before writing any code, we decided how the data would look. Good structure makes everything after it easier.

Stock is a dictionary of dictionaries. The product name is the key, and the value holds price and quantity:

Looking up inventory["Pen"]["quantity"] is instant and readable, and checking whether a product exists is just name in inventory.

Sales are a list of tuples. Each sale never changes after it happens, which is exactly what a tuple is for:

Unique products sold are a set. A set ignores duplicates, so adding "Pen" twice still counts it once:

Step 2: Welcome and setup

The program opens by asking for the kiosk's name and the owner's name. Raw input() is never trusted: .strip() removes stray spaces and .title() fixes the capitalisation, so " navas herbert " becomes "Navas Herbert".

We split this into two small functions. One collects the information and returns two values; the other prints the banner.

The file names sit at the top as constants. If we ever rename a file, we change it in one place.

Step 3: Saving and loading data

Data that vanishes when the program closes is useless to a shopkeeper, so we used plain text files with one comma-separated record per line. This is what Dictionary.txt looks like:

On startup, os.path.exists() decides whether to load that file or begin with a small default inventory. Each line is turned back into a dictionary entry with .split(",").

to come back here!!!!

Saving uses three small functions, and the file mode matters:

Mode "w" overwrites, which is right for inventory because the file should always hold today's stock.
Mode "a" appends, which is right for sales because yesterday's sales must never be erased.

Step 4: Viewing stock

Aligned columns come from f-string padding: {product:<15} left-aligns a value in a 15-character column.

Step 5: Adding or restocking a product

One function handles both jobs. If the product exists, we add units to it; if not, we ask for a price and a starting quantity. Every input is checked with .isdigit() before converting with int(), so typing letters can never crash the program.

# ================function to add/ restock a product========================
def add_or_restock_product():

    product = input("Enter product name: ").strip().title()

    # Validate product name
    if product == "" or product.isdigit():
        print("Invalid product name. Please enter a valid product name.")
        return

    # Product already exists
    if product in inventory:

        quantity = input("Enter quantity to add: ").strip()

        if not quantity.isdigit():
            print("Quantity must be a number.")
            return

        quantity = int(quantity)

        if quantity <= 0:
            print("Quantity must be greater than 0.")
            return

        inventory[product]["quantity"] += quantity

        save_inventory()

        print(f"{product} has been restocked successfully.")

    # Brand-new product
    else:

        price = input("Enter product price: ").strip()
        quantity = input("Enter product quantity: ").strip()

        if not price.isdigit() or not quantity.isdigit():
            print("Price and quantity must be numbers.")
            return

        price = int(price)
        quantity = int(quantity)

        if price <= 0 or quantity <= 0:
            print("Price and quantity must be greater than 0.")
            return

        inventory[product] = {
            "price": price,
            "quantity": quantity
        }

        save_inventory()

        print(f"{product} has been added successfully.")

The repeated return statements are _"guard clause_s": the moment something is wrong, the function stops. The successful path stays flat and easy to read.

Step 6: Selling a product

This is where the business logic lives. A sale only goes through if the product exists, the quantity is a positive whole number, and there is enough stock.

#=============== Function to Sell a Product================
def sell_product():
    product = input('Which product to sell: ').strip().title()

    # Validate product name
    if product == "" or product.isdigit():
        print("Invalid product name. Please enter a valid product name.")
        return
    if product not in inventory:
        print(f"{product} is not available in the inventory.")
        return

    if product in inventory:
        quantity = input('Enter the units to be sold: ').strip()

        if not quantity.isdigit():
            print('Quantity must be a digit')
            return
        quantity = int(quantity)

        if quantity <= 0:
            print('Quantity should be greater than 0')
            return

        if quantity > inventory[product]["quantity"]:
            print(
            f"Not enough stock. Only "
            f"{inventory[product]['quantity']} units available.")
            return

        price = inventory[product]['price']
        total = quantity * price

        inventory[product]["quantity"] -= quantity

        sale = (product, quantity, total)

        sales_log.append(sale)

        sold_products.add(product)

        save_inventory()
        save_sales_log()
        save_sold_products()

        print(f"Sold {quantity} units of {product} sucessfuly for {total}")

    print("Sale successful.")
    print(f"Product: {product}")
    print(f"Quantity: {quantity}")
    print(f"Total: KES {total}")

Nothing changes until every check has passed. That is why a refused sale never leaves the stock half-updated.

Step 7: The sales report

One loop does three jobs:
- it prints each sale,
- adds to a revenue accumulator,
- builds a dictionary of units sold per product.
After the loop, max() picks the best seller.

#===============Function to print the sales report==============================

def sales_report():
    if not sales_log:
        print("No sales yet this session.")
        return

    print("\nSALES REPORT")
    print("-" * 40)
    print(f"{'Product':<15}{'Quantity':<10}{'Total (KES)':<12}")
    print("-" * 40)

    revenue = 0
    units_by_product = {}
    for product, quantity, total in sales_log:
        print(f"{product:<15}{quantity:<10}{total:<12}")
        revenue += total
        units_by_product[product] = units_by_product.get(product, 0) + quantity

    best = max(units_by_product, key=units_by_product.get)
    print("-" * 40)
    print(f"Total revenue: KES {revenue}")
    print(f"Unique products sold: {len(sold_products)}")
    print(f"Best seller: {best} ({units_by_product[best]} units)\n")

.get(product, 0) returns 0 the first time a product appears, which avoids a missing-key error. We define "best seller" as most units sold, and stay consistent with that.

Step 8: Searching products

A forgiving search lowercases both the search term and the product name, then uses the in operator, so typing calc finds Calculator.

# ===============Function to search products====================
def search_products():
    term = input("Search for a product: ").strip().lower()
    if term == "":
        print("Please type something to search for.")
        return

    found = False
    for product, details in inventory.items():
        if term in product.lower():
            print(f"{product:<15}KES {details['price']:<8}Qty: {details['quantity']}")
            found = True
    if not found:
        print("No matches found.")

The found flag is a classic pattern: assume nothing matched, flip it to True on the first match, and print "No matches found" only if it never flipped.

Step 9: The main program

Because every feature is its own function, the main flow reads almost like plain English: ask, welcome, load, then loop until the user exits.

kiosk_name, owner_name = ask_kiosk_info()
show_owner_details(kiosk_name, owner_name)
print()

inventory = load_inventory()
sales_log = []
sold_products = set()

while True:
    print('==============MAIN MENU============== ')
    print('1. View Stock')
    print('2. Add/ Restock a Product')
    print('3. Sell a Product')
    print('4. View Sales Report')
    print('5. Search Products')
    print('6. Exit')

    choice = input('Enter your choice (Between 1 and 6): ').strip()

    if not choice.isdigit():
        print('Invalid choice. Please enter a number between 1 and 6')
        continue
    choice = int(choice)
    if choice < 1 or choice > 6:
        print('Invalid choice. Please enter a number between 1 and 6')
        continue

    if choice == 1:
        view_inventory()
    elif choice == 2:
        add_or_restock_product()
    elif choice == 3:
        sell_product()
    elif choice == 4:
        sales_report()
    elif choice == 5:
        search_products()
    else:
        save_inventory()
        save_sales_log()
        save_sold_products()
        print(f'Exiting. Goodbye, {owner_name}!')
        break

The menu choice is validated in two steps: .isdigit() first (is it a number at all?), then a range check (is it 1 to 6?). continue sends the loop straight back to the menu instead of crashing.

Conclusion

Kiosk Manager is small, but it is a complete program: it takes input, applies business rules, stores data, reports results and survives a restart. That is the full loop of real software, and if every group member can explain every function above, we built something a real shopkeeper could use tomorrow morning.

Understanding Subqueries and CTE's

Gideon Kiprono — Sat, 26 Sep 2026 10:02:34 +0000

Introduction

Imagine you are a data analyst working for a retail company. Your manager asks you to identify customers who spend more than the average customer, employees earning above their department's average salary, or products generating the highest revenue.

You already know how to retrieve data using SELECT, filter records using WHERE, and combine tables using JOIN. However, some business questions require you to perform multiple calculations before arriving at the final answer.

For example, how would you identify employees earning above the company's average salary without knowing the average salary beforehand?

You would first need to calculate the average salary and then use that result to identify employees whose salaries exceed it.

This is where SQL Subqueries and Common Table Expressions (CTEs) come in.

Both techniques allow you to break down complex analytical problems into smaller, more manageable queries. They help you perform intermediate calculations, filter records based on the results of other queries, and organize your SQL code more effectively.

In this article, we will explore what subqueries and CTEs are, how they work, their key differences, and how to apply them to real-world data analysis problems.

SQL Subqueries

A subquery, also known as a nested query or inner query, is a SQL query written inside another SQL query.

The inner query produces a result that the outer query uses to perform another operation.

In simple terms, a subquery allows you to answer one question and use that answer to solve another question within the same SQL statement.

Basic Syntax of a Subquery

SELECT column_name
FROM table_name
WHERE column_name operator (
SELECT column_name
FROM table_name
);

The query inside the parentheses is called the inner query or subquery, while the query surrounding it is called the outer query.

For a simple, independent subquery, SQL can evaluate the inner query and use its result in the outer query. However, the actual execution strategy depends on the database engine and the type of subquery.

Let's understand this concept using a practical example.

Example 1: Finding Employees Earning Above the Average Salary

Suppose we have an employees table containing the following information:

employee_id	employee_name	department	salary
1	John	IT	80000
2	Mary	HR	55000
3	Peter	IT	70000
4	Jane	Finance	45000
5	David	Finance	50000

The HR manager wants to identify employees earning above the company's average salary.

To solve this problem, we need to calculate the average salary and then compare each employee's salary against that average.

Step 1: Calculate the average salary.

SELECT AVG(salary) AS average_salary
FROM employees;

The AVG() function calculates the average salary across all employees.

The result is:

average_salary: 60000

Step 2: Use the average salary to identify employees earning above it.

Instead of manually entering 60000 into another query, we can combine both operations using a subquery.

SELECT
employee_name,
department,
salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);

How does this query work?

The inner query:

SELECT AVG(salary)
FROM employees;

calculates the company's average salary.

The outer query:

SELECT
employee_name,
department,
salary
FROM employees
WHERE salary > (...);

retrieves employees whose salaries exceed the value returned by the inner query.

The final result is:

employee_name	department	salary
John	IT	80000
Peter	IT	70000

Notice that we did not need to enter the average salary manually. The query calculates it directly from the data.

If the employee salaries change, running the query again will use the updated average salary.

This makes subqueries particularly useful when working with dynamic data.

Types of SQL Subqueries

Subqueries can be classified according to the number of values they return and how they interact with the outer query.

1. Scalar Subqueries

A scalar subquery returns a single value, such as an average salary, maximum price, or total revenue.

For example:

SELECT
employee_name,
salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);

Here, the subquery returns one value: the average salary.

The outer query uses that value to filter employees.

2. Multiple-Row Subqueries

A multiple-row subquery returns more than one row.

These subqueries are commonly used with operators such as IN, ANY, and ALL.

For example, suppose we have a departments table containing department names and locations.

We want to identify employees working in departments located in Nairobi.

SELECT
employee_name,
department
FROM employees
WHERE department IN (
SELECT department_name
FROM departments
WHERE location = 'Nairobi'
);

The inner query retrieves the names of departments located in Nairobi.

The outer query then selects employees whose department appears in that list.

3. Correlated Subqueries

A correlated subquery is a subquery that references a column from the outer query.

Unlike an independent subquery, it depends on values supplied by the outer query.

For example, suppose we want to identify employees earning above the average salary in their respective departments.

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

In this example, the subquery calculates the average salary for the department associated with each employee in the outer query.

The condition:

WHERE e2.department = e.department****

connects the inner query to the outer query.

This allows each employee's salary to be compared against the average salary of their own department rather than the company-wide average.

Correlated subqueries are useful when performing comparisons within groups, such as comparing individual customer spending against the average spending of customers in the same region.

3. Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary named result set defined within a SQL statement using the WITH keyword.

A CTE allows you to write a query, give its result a meaningful name, and reference that result in the main SQL statement.

Think of a CTE as creating a temporary working table that helps you organize a complex query into smaller, logical steps.

Unlike a permanent database table, a CTE does not remain available for use by later, separate SQL statements.

Basic Syntax of a CTE

The basic structure of a Common Table Expression looks like this:

WITH cte_name AS (
    SELECT column_name
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

Let's break this down.

The WITH keyword tells SQL that we are defining a Common Table Expression.

cte_name is the name we give to the result produced by the query inside the parentheses.

The main query can then reference the CTE using that name.

In simple terms:

WITH
    ↓
Create temporary result
    ↓
Give it a name
    ↓
Use that name in the main query

Let's apply this to our employee data.

Example 2: Finding Employees Earning Above the Average Salary Using a CTE

Earlier, we solved this problem using a subquery:

SELECT
    employee_name,
    department,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

We can solve the same problem using a CTE.

First, we create a CTE that calculates the company's average salary.

WITH average_salary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT
    e.employee_name,
    e.department,
    e.salary
FROM employees AS e
CROSS JOIN average_salary AS a
WHERE e.salary > a.avg_salary;

Let's understand what is happening.

The CTE:

WITH average_salary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)

calculates the average salary and gives the result the name average_salary.

Conceptually, it produces:

avg_salary
60000

The main query then uses that result:

SELECT
    e.employee_name,
    e.department,
    e.salary
FROM employees AS e
CROSS JOIN average_salary AS a
WHERE e.salary > a.avg_salary;

The final result is:

employee_name	department	salary
John	IT	80000
Peter	IT	70000

We have therefore answered the same question using two different approaches.

The subquery places the average calculation directly inside the WHERE condition, while the CTE calculates the average first, gives the result a meaningful name, and then references that result in the main query.

For a simple problem like this, the subquery may be shorter. However, as queries become more complex, CTEs can make SQL code easier to read and maintain.

Using a CTE to Summarize Data

CTEs become particularly useful when we need to perform an intermediate calculation before carrying out further analysis.

Suppose we have a sales table containing:

sale_id	customer_id	amount
1	101	15000
2	102	25000
3	101	30000
4	103	10000
5	102	20000
6	104	5000

Management wants to identify customers who have spent more than KSh 30,000 in total.

Before filtering the customers, we first need to calculate the total amount spent by each customer.

We can use a CTE:

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM sales
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_spent
FROM customer_spending
WHERE total_spent > 30000;

The CTE produces an intermediate result similar to:

customer_id	total_spent
101	45000
102	45000
103	10000
104	5000

The main query then filters this result:

SELECT
    customer_id,
    total_spent
FROM customer_spending
WHERE total_spent > 30000;

giving us:

customer_id	total_spent
101	45000
102	45000

This demonstrates an important advantage of CTEs: we can perform one analytical step, give the result a meaningful name, and then continue our analysis using that result.

Using Multiple CTEs

We are not limited to creating only one CTE.

SQL allows us to define multiple CTEs within the same statement by separating them with commas.

The general structure is:

WITH cte_one AS (
    SELECT ...
),
cte_two AS (
    SELECT ...
)
SELECT ...
FROM cte_one
JOIN cte_two
    ON ...;

This can be particularly useful when solving a problem that contains several analytical steps.

Suppose we want to:

Calculate the total amount spent by each customer.
Calculate the average customer spending.
Identify customers who spent more than the average customer.

We can break the problem into logical steps.

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM sales
    GROUP BY customer_id
),
average_spending AS (
    SELECT
        AVG(total_spent) AS avg_spent
    FROM customer_spending
)
SELECT
    cs.customer_id,
    cs.total_spent
FROM customer_spending AS cs
CROSS JOIN average_spending AS a
WHERE cs.total_spent > a.avg_spent;

Notice how the second CTE can reference the result produced by the first CTE.

Conceptually, our query follows this process:

Raw Sales Data
      ↓
Calculate spending per customer
      ↓
Calculate average customer spending
      ↓
Compare each customer with the average
      ↓
Return above-average customers

Instead of trying to solve the entire problem in one deeply nested query, CTEs allow us to express the analysis as a sequence of understandable steps.

This is one of the main reasons CTEs are popular when writing analytical SQL.

Subqueries vs CTEs

At this point, you may be wondering:

If both subqueries and CTEs can produce intermediate results, which one should I use?

The answer depends on the problem you are solving.

Consider our original employee example.

Using a subquery:

SELECT
    employee_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Using a CTE:

WITH average_salary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT
    e.employee_name,
    e.salary
FROM employees AS e
CROSS JOIN average_salary AS a
WHERE e.salary > a.avg_salary;

Both can produce the same result.

The main difference here is how the query is organized.

Feature	Subquery	CTE
Location	Nested inside another query	Defined before the main query using WITH
Readability	Good for simple queries	Often clearer for complex queries
Nesting	Can become difficult to read when heavily nested	Can break complex logic into named steps
Reuse within the same statement	Often requires repeating the subquery	A CTE can often be referenced multiple times
Recursive queries	Not normally used for recursion	Recursive CTEs can handle hierarchical problems
Lifetime	Exists only as part of its containing statement	Exists only for the statement in which it is defined

It is important to note that a CTE is not automatically faster than a subquery.

Modern database engines have query optimizers that determine how SQL statements should actually be executed. Depending on the database system and the query, a CTE may be inlined, materialized, or optimized in another way.

Therefore, CTEs should not automatically be chosen because they are assumed to improve performance.

Their biggest advantage is often clarity and organization.

When Should You Use a Subquery?

Subqueries work particularly well when the intermediate calculation is relatively simple and is needed in only one place.

For example:

SELECT
    product_name,
    price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

The logic is straightforward:

Find the average price and return products priced above it.

Writing a separate CTE may add unnecessary complexity to such a simple query.

Subqueries are commonly useful when working with:

WHERE
HAVING
SELECT
FROM
IN
EXISTS
NOT EXISTS
Comparison operators such as >, <, and =

When Should You Use a CTE?

CTEs become especially useful when:

A query contains several logical steps.
The same intermediate result needs to be referenced more than once.
Nested subqueries are becoming difficult to understand.
You want to give intermediate calculations meaningful names.
You are working with hierarchical or recursive data.

For example, names such as:

customer_spending
monthly_revenue
department_average
top_customers
regional_sales

immediately communicate what each part of the query represents.

Compare that with several layers of unnamed nested queries.

A well-structured CTE can make complex analytical SQL read almost like a sequence of instructions.

Common Mistakes When Using Subqueries and CTEs

1. Returning Multiple Values Where One Is Expected

Consider:

SELECT *
FROM employees
WHERE salary = (
    SELECT salary
    FROM employees
    WHERE department = 'IT'
);

If the IT department contains several employees with different salaries, the subquery may return multiple rows.

But = expects a single value.

Depending on the intended question, an operator such as IN may be more appropriate:

SELECT *
FROM employees
WHERE salary IN (
    SELECT salary
    FROM employees
    WHERE department = 'IT'
);

Always think about whether your subquery is expected to return one value, one row, or multiple rows.

2. Making Subqueries Unnecessarily Deep

A query containing many levels of nested subqueries can quickly become difficult to understand.

For example:

Query
 └── Subquery
      └── Subquery
           └── Subquery

When the logic becomes difficult to follow, consider restructuring the query using CTEs.

3. Forgetting That a CTE Is Temporary

A CTE is available only to the SQL statement immediately associated with it.

For example:

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM sales
    GROUP BY customer_id
)
SELECT *
FROM customer_spending;

After this statement finishes, you cannot run a separate query such as:

SELECT *
FROM customer_spending;

and expect the CTE still to exist.

If you need to permanently store a result, you may need a table, view, or another database object depending on your requirements.

4. Assuming CTEs Are Always Faster

CTEs are excellent for organizing SQL, but they do not automatically improve query performance.

Performance depends on factors such as:

The database management system
Available indexes
Table sizes
Join conditions
Filtering
Aggregations
Query execution plans

Use CTEs primarily when they make your SQL logic clearer, and investigate the execution plan when performance matters.

Putting Everything Together

Subqueries and CTEs ultimately help us solve the same fundamental problem:

How can we use the result of one query as part of a larger analytical question?

A subquery places one query inside another:

Outer Query
     ↓
(Subquery)

A CTE takes a more step-by-step approach:

WITH intermediate_result AS (...)
              ↓
         Main Query

Neither approach is universally better.

For a short calculation that is used once, a subquery may be the simplest solution.

For an analysis containing several intermediate calculations, a CTE can make the logic significantly easier to follow.

Conclusion

As SQL queries become more advanced, business questions rarely require just one simple SELECT statement.

You may need to calculate an average before filtering records, summarize customer transactions before comparing spending patterns, or perform several intermediate calculations before producing the final result.

This is where subqueries and Common Table Expressions (CTEs) become valuable.

Subqueries allow us to place one query inside another and use the result as part of a larger SQL operation. They are particularly useful for concise calculations and filtering based on dynamically generated values.

CTEs, on the other hand, allow us to break more complex SQL logic into named, logical steps using the WITH keyword. This can make analytical queries easier to read, understand, debug, and maintain.

The easiest way to remember the distinction is:

A subquery nests the logic, while a CTE names and organizes the logic.

As a data analyst, you do not need to choose one approach for every situation. The important skill is understanding the problem, recognizing the shape of the intermediate result you need, and selecting the approach that expresses your logic most clearly.

Once you are comfortable with subqueries and CTEs, you are ready to tackle more advanced SQL techniques such as window functions, recursive CTEs, ranking, and multi-step analytical queries.

WHAT IS SQL JOINS

Gideon Kiprono — Sun, 20 Sep 2026 16:03:04 +0000

Introduction

Imagine walking into a car dealership and asking the manager:

“Which customers bought which cars, how much did they spend, and which salesperson handled each sale?”

The information needed to answer this question might already exist in the company's database — but there is a catch.

It is not necessarily stored in one place.

Customer information might be stored in a customers table:

customer_id	customer_name	county
101	James Mwangi	Nairobi
102	Faith Chebet	Nakuru
103	Mary Atieno	Kisumu

Information about sales might be stored separately in a sales table:

sale_id	customer_id	car_id	amount
S001	101	C10	3,500,000
S002	103	C15	2,800,000
S003	102	C12	4,100,000

And information about the cars might exist in yet another table:

car_id	make	model
C10	Toyota	Hilux
C12	Ford	Ranger
C15	Mazda	CX-5

Each table tells us something useful, but none of them can answer our original question on its own.

The customers table knows who the customers are.

The sales table knows what transactions occurred.

The cars table knows which vehicles were sold.

What connects these pieces of information?

SQL JOINs.

A JOIN allows us to combine related information stored across different tables by identifying columns that connect those tables.

In our example, customer_id connects customers to their sales:

customers                         sales
─────────                         ─────
customer_id ───────────────────► customer_id

Similarly, car_id connects a sale to information about the vehicle:

sales                             cars
─────                             ────
car_id ─────────────────────────► car_id

Once these relationships are understood, SQL can bring the information together and allow us to answer questions such as:

Which customer purchased each vehicle?
Which cars generated the most revenue?
Which customers have never made a purchase?
Which vehicles have never been sold?
How much has each customer spent?

This is one of the reasons JOINs are such an important SQL skill. Real-world databases rarely store everything in one giant table. Instead, data is often separated into related tables, and analysts need to know how to bring the right information together.

In this article, we will explore the major types of SQL JOINs — INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN — using practical examples to understand not only how they work, but when and why we use them.

Let's connect the tables.

Before We Join: Understanding Keys and Table Relationships

Before writing our first SQL JOIN, we need to understand one important question:

How does SQL know which rows from one table should be connected to rows in another table?

The answer lies in keys.

Primary Keys

A primary key is a column, or combination of columns, that uniquely identifies each record in a table.

Consider our customers table:

customer_id	customer_name	county
101	James Mwangi	Nairobi
102	Faith Chebet	Nakuru
103	Mary Atieno	Kisumu

Here, customer_id is the primary key.

Each customer has a unique ID:

James Mwangi  → 101
Faith Chebet  → 102
Mary Atieno   → 103

Even if two customers happen to have the same name, their customer_id values should still be different.

A table can be created with a primary key like this:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    county VARCHAR(50)
);

The PRIMARY KEY constraint tells the database that customer_id should uniquely identify each customer.

Foreign Keys

A foreign key is a column in one table that references a primary key, or another unique key, in another table.

Consider our sales table:

sale_id	customer_id	car_id	amount
S001	101	C10	3,500,000
S002	103	C15	2,800,000
S003	102	C12	4,100,000

The customer_id column appears here again.

But this time, it is being used to identify which customer made each purchase.

We can visualize the connection like this:

CUSTOMERS                         SALES

customer_id                      customer_id
───────────                      ───────────
101 James Mwangi  ────────────► 101
102 Faith Chebet  ────────────► 102
103 Mary Atieno   ────────────► 103

In the customers table:

customer_id = Primary Key

In the sales table:

customer_id = Foreign Key

This relationship allows us to connect customer information with sales information.

Understanding the Relationship

One customer can make several purchases.

For example:

Customers

customer_id	customer_name
101	James Mwangi
102	Faith Chebet

Sales

sale_id	customer_id	amount
S001	101	3,500,000
S002	101	2,200,000
S003	102	4,100,000

Notice that customer_id 101 appears twice in the sales table.

That is perfectly acceptable because James has made two purchases.

The relationship can therefore be described as:

CUSTOMERS                     SALES

    ONE                        MANY

     101  ────────────────┬── S001
                          │
                          └── S002

This is known as a one-to-many relationship.

One customer can be associated with many sales, while each sale in this example belongs to one customer.

The JOIN Condition

Now that we know how the tables are related, we can tell SQL how to connect them.

This is done using the ON condition.

For example:

SELECT *
FROM customers
INNER JOIN sales
    ON customers.customer_id = sales.customer_id;

The most important part for now is:

ON customers.customer_id = sales.customer_id

We are essentially telling SQL:

Match a customer with a sale whenever their customer_id values are equal.

SQL compares the values:

customers.customer_id       sales.customer_id

        101        =               101       ✓
        102        =               102       ✓
        103        =               103       ✓

Once SQL finds the matching values, it can combine information from both tables.

Why Not Join Using Customer Names?

You might wonder why we cannot simply write:

ON customers.customer_name = sales.customer_name

Names are not always reliable identifiers.

Two customers could have the same name, names may be misspelled, or a customer's name could change.

IDs are generally more reliable for establishing relationships between tables.

That is why relational databases commonly use primary and foreign keys to connect related records.

Our Database Relationship

For the examples in this article, we will work with three tables:

CUSTOMERS
──────────────
customer_id (PK)
customer_name
county
       │
       │ customer_id
       ▼
SALES
──────────────
sale_id (PK)
customer_id (FK)
car_id (FK)
amount
       │
       │ car_id
       ▼
CARS
──────────────
car_id (PK)
make
model

PK represents a Primary Key, while FK represents a Foreign Key.

Now that we understand how the tables are connected, we are ready to start joining them.

The first and perhaps most fundamental type of JOIN we will explore is the INNER JOIN.

1. INNER JOIN — Finding Matching Records

Now that we understand how tables are connected using keys, let's look at our first type of JOIN: the INNER JOIN.

An INNER JOIN returns only the records that have matching values in both tables.

In simple terms:

If a record does not have a match in both tables, it will not appear in the result.

Let's look at an example.

Our Customers Table

Suppose we have the following customers:

customer_id	customer_name	county
101	James Mwangi	Nairobi
102	Faith Chebet	Nakuru
103	Mary Atieno	Kisumu
104	Brian Otieno	Mombasa

Our Sales Table

Now consider the sales records:

sale_id	customer_id	amount
S001	101	3,500,000
S002	103	2,800,000
S003	102	4,100,000

Notice something important.

Customers 101, 102, and 103 have corresponding records in the sales table.

However, customer 104, Brian Otieno, does not have a sales record.

If we use an INNER JOIN, SQL will return only customers who have matching sales records.

INNER JOIN Syntax

We can join the two tables using:

```sql id="e78n4k"
SELECT
customers.customer_name,
customers.county,
sales.sale_id,
sales.amount
FROM customers
INNER JOIN sales
ON customers.customer_id = sales.customer_id;




The most important part is:



```sql id="6a3c2f"
ON customers.customer_id = sales.customer_id

This tells SQL to compare the customer_id column in the customers table with the customer_id column in the sales table.

Conceptually, SQL is looking for matches:

```text id="z4ub62"
CUSTOMERS SALES

101 James Mwangi ────────────── 101 S001 ✓
102 Faith Chebet ────────────── 102 S003 ✓
103 Mary Atieno ────────────── 103 S002 ✓
104 Brian Otieno No match ✗




Because Brian does not have a matching record in `sales`, he will not appear in the result.

### Result

The query produces:

| customer_name | county  | sale_id |    amount |
| ------------- | ------- | ------- | --------: |
| James Mwangi  | Nairobi | S001    | 3,500,000 |
| Faith Chebet  | Nakuru  | S003    | 4,100,000 |
| Mary Atieno   | Kisumu  | S002    | 2,800,000 |

Only records with matches in **both tables** have been returned.

---

### Using Table Aliases

Writing the complete table names repeatedly can make SQL queries unnecessarily long.

Instead of:



```sql id="8m4qu7"
SELECT
    customers.customer_name,
    customers.county,
    sales.sale_id,
    sales.amount
FROM customers
INNER JOIN sales
    ON customers.customer_id = sales.customer_id;

we can assign shorter names called aliases:

```sql id="qk7m9p"
SELECT
c.customer_name,
c.county,
s.sale_id,
s.amount
FROM customers AS c
INNER JOIN sales AS s
ON c.customer_id = s.customer_id;




Here:



```text id="5o8by4"
c → customers
s → sales

The query performs exactly the same operation, but it is shorter and easier to read.

Aliases become particularly useful when joining several tables.

Joining More Than Two Tables

What if we also want to know which car each customer purchased?

Our sales table contains a car_id, which connects it to the cars table.

We can therefore join all three tables:

```sql id="6v8q3f"
SELECT
c.customer_name,
c.county,
ca.make,
ca.model,
s.amount
FROM sales AS s
INNER JOIN customers AS c
ON s.customer_id = c.customer_id
INNER JOIN cars AS ca
ON s.car_id = ca.car_id;




Now SQL follows the relationships:



```text id="79om1b"
CUSTOMERS
    │
    │ customer_id
    ▼
  SALES
    │
    │ car_id
    ▼
   CARS

The result could look like:

customer_name	county	make	model	amount
James Mwangi	Nairobi	Toyota	Hilux	3,500,000
Faith Chebet	Nakuru	Ford	Ranger	4,100,000
Mary Atieno	Kisumu	Mazda	CX-5	2,800,000

We can now answer a much more meaningful business question:

Which customer purchased which car, and how much did they spend?

This demonstrates the real power of SQL JOINs. Information that was separated across multiple tables can now be analysed together.

When Would You Use an INNER JOIN?

An INNER JOIN is useful when you are interested only in records that have corresponding information in both tables.

For example:

Customers who have made purchases
Employees assigned to departments
Products that have been ordered
Students enrolled in courses
Drivers who have completed trips

A simple way to remember it is:

INNER JOIN = Give me the matches.

But what happens if we want to see all customers, including those who have never purchased anything?

An INNER JOIN cannot give us that complete picture.

For that, we need our next JOIN: the LEFT JOIN.

2. LEFT JOIN — Keeping Everything from the Left Table

In the previous section, we saw that an INNER JOIN returns only records that have matching values in both tables.

But sometimes we want a different answer.

Suppose management asks:

“Show me all our customers, including those who have never made a purchase.”

An INNER JOIN would not work because customers without a matching sales record would be excluded.

This is where a LEFT JOIN becomes useful.

A LEFT JOIN returns:

All records from the left table
Matching records from the right table
NULL where a record from the left table has no corresponding match in the right table

A simple way to remember this is:

LEFT JOIN = Keep everything on the left and bring matching information from the right.

Our Two Tables

Let's continue using the customers and sales tables.

Customers

customer_id	customer_name	county
101	James Mwangi	Nairobi
102	Faith Chebet	Nakuru
103	Mary Atieno	Kisumu
104	Brian Otieno	Mombasa

Sales

sale_id	customer_id	amount
S001	101	3,500,000
S002	103	2,800,000
S003	102	4,100,000

Notice again that Brian Otieno (customer_id = 104) does not have a corresponding record in the sales table.

With an INNER JOIN, Brian was excluded.

Let's see what happens with a LEFT JOIN.

LEFT JOIN Syntax

```sql id="w5w92z"
SELECT
c.customer_id,
c.customer_name,
c.county,
s.sale_id,
s.amount
FROM customers AS c
LEFT JOIN sales AS s
ON c.customer_id = s.customer_id;




Here, `customers` is the **left table** because it appears before `LEFT JOIN`.



```text id="mmx2r8"
customers                     sales
   LEFT                       RIGHT
     │                          │
     └──────── LEFT JOIN ───────┘

SQL keeps every record from customers and then looks for matching sales information.

Conceptually:

```text id="77dtb4"
CUSTOMERS SALES

101 James Mwangi ────────────── 101 S001 ✓
102 Faith Chebet ────────────── 102 S003 ✓
103 Mary Atieno ────────────── 103 S002 ✓
104 Brian Otieno No match




The important difference is that Brian is **not removed** from the result.

### Result

| customer_id | customer_name | county  | sale_id |    amount |
| ----------: | ------------- | ------- | ------- | --------: |
|         101 | James Mwangi  | Nairobi | S001    | 3,500,000 |
|         102 | Faith Chebet  | Nakuru  | S003    | 4,100,000 |
|         103 | Mary Atieno   | Kisumu  | S002    | 2,800,000 |
|         104 | Brian Otieno  | Mombasa | NULL    |      NULL |

Because Brian has no matching sale, SQL cannot provide a `sale_id` or `amount`.

Instead, those values appear as:



```text id="0n3m8g"
NULL

In SQL, NULL represents a missing or unknown value.

It is not the same as:

```text id="cf1e11"
0




and it is not the same as an empty string.

In this example, the `NULL` values tell us something useful: **Brian has no matching sales record.**

---

## Finding Customers Who Have Never Purchased

LEFT JOIN becomes especially useful when we combine it with `IS NULL`.

Suppose management asks:

> **“Which customers have never made a purchase?”**

We can write:



```sql id="xxo5ox"
SELECT
    c.customer_id,
    c.customer_name,
    c.county
FROM customers AS c
LEFT JOIN sales AS s
    ON c.customer_id = s.customer_id
WHERE s.customer_id IS NULL;

The result would be:

customer_id	customer_name	county
104	Brian Otieno	Mombasa

This technique is extremely useful in real-world analysis.

For example, we could use it to find:

Customers who have never purchased
Products that have never been sold
Employees who have not been assigned to a department
Vehicles that have never completed a trip
Students who have not registered for a course

What Happens When One Customer Has Multiple Sales?

Suppose James makes another purchase.

Our sales table becomes:

sale_id	customer_id	amount
S001	101	3,500,000
S002	103	2,800,000
S003	102	4,100,000
S004	101	2,000,000

When we run our LEFT JOIN, James appears twice:

customer_name	sale_id	amount
James Mwangi	S001	3,500,000
James Mwangi	S004	2,000,000
Faith Chebet	S003	4,100,000
Mary Atieno	S002	2,800,000
Brian Otieno	NULL	NULL

This is not SQL accidentally creating a duplicate.

James has two matching records in the sales table, so SQL returns one result row for each match.

This is an important concept when working with one-to-many relationships.

INNER JOIN vs LEFT JOIN

We can now clearly see the difference between the first two JOINs.

INNER JOIN	LEFT JOIN
Returns matching records only	Returns all left-table records
Unmatched left records disappear	Unmatched left records remain
Useful when only matches matter	Useful when missing matches also matter

Consider Brian:

```text id="97cx92"
INNER JOIN LEFT JOIN

Brian Otieno ✗ ✓
Sales record ✗ NULL




So if the question is:

**“Which customers have purchased something?”**

an `INNER JOIN` may be appropriate.

But if the question is:

**“Show me every customer and their purchases, if they have any,”**

a `LEFT JOIN` is more appropriate.

---

## A Common LEFT JOIN Mistake

Suppose we want all customers but only sales above KSh 3,000,000.

Consider:



```sql id="47e0f3"
SELECT
    c.customer_name,
    s.amount
FROM customers AS c
LEFT JOIN sales AS s
    ON c.customer_id = s.customer_id
WHERE s.amount > 3000000;

The WHERE condition removes rows where s.amount is NULL. As a result, customers without matching sales disappear, which can make the result behave more like an INNER JOIN for this condition.

If our intention is to retain all customers while matching only sales above KSh 3,000,000, we can place the condition inside the ON clause:

```sql id="8oy89a"
SELECT
c.customer_name,
s.amount
FROM customers AS c
LEFT JOIN sales AS s
ON c.customer_id = s.customer_id
AND s.amount > 3000000;




This preserves all customers from the left table while restricting which sales records qualify as matches.

Understanding where filtering happens is important when working with outer joins.

---

## When Should You Use a LEFT JOIN?

Use a `LEFT JOIN` when you want to keep **all records from your main table**, regardless of whether corresponding information exists in another table.

For a data analyst, LEFT JOIN is particularly useful when investigating **missing activity**.

For example:



```text id="6dq1k4"
All Customers
      ↓
LEFT JOIN
      ↓
Sales
      ↓
Customers with purchases
+
Customers without purchases

A simple rule to remember is:

INNER JOIN asks: “Which records match?”
LEFT JOIN asks: “Give me everything on the left, whether it matches or not.”

We have now seen what happens when we preserve the table on the left.

But what if we want to preserve every record from the table on the right instead?

That brings us to the RIGHT JOIN.

3. RIGHT JOIN — Keeping Everything from the Right Table

A RIGHT JOIN works similarly to a LEFT JOIN, but instead of keeping every record from the table on the left, it keeps every record from the table on the right.

A RIGHT JOIN returns:

All records from the right table
Matching records from the left table
NULL values where a right-table record does not have a corresponding match in the left table

A simple way to remember it is:

RIGHT JOIN = Keep everything on the right and bring matching information from the left.

Let's continue with our customers and sales example.

Customers Table

customer_id	customer_name	county
101	James Mwangi	Nairobi
102	Faith Chebet	Nakuru
103	Mary Atieno	Kisumu
104	Brian Otieno	Mombasa

Sales Table

This time, imagine our sales table contains:

sale_id	customer_id	amount
S001	101	3,500,000
S002	103	2,800,000
S003	102	4,100,000
S004	105	2,500,000

Notice something unusual.

The sale S004 references customer_id = 105, but customer 105 does not appear in our customers table.

This could represent a data-quality problem, imported historical data, or—in a database without an enforced foreign-key constraint—an unmatched record.

Let's see what happens when we use a RIGHT JOIN.

RIGHT JOIN Syntax

```sql id="7d4n23"
SELECT
c.customer_id,
c.customer_name,
c.county,
s.sale_id,
s.amount
FROM customers AS c
RIGHT JOIN sales AS s
ON c.customer_id = s.customer_id;




Here:



```text id="32j8fk"
customers                     sales
   LEFT                       RIGHT
     │                          │
     └──────── RIGHT JOIN ──────┘

Because sales is the table on the right, every sales record will be retained.

SQL looks for matching customers:

```text id="yk39du"
CUSTOMERS SALES

101 James Mwangi ────────────── 101 S001 ✓
102 Faith Chebet ────────────── 102 S003 ✓
103 Mary Atieno ────────────── 103 S002 ✓
105 S004 ✗




The sale for customer `105` does not have a matching customer record.

However, because we are using a `RIGHT JOIN`, the sale is still included.

### Result

| customer_id | customer_name | county  | sale_id |    amount |
| ----------: | ------------- | ------- | ------- | --------: |
|         101 | James Mwangi  | Nairobi | S001    | 3,500,000 |
|         103 | Mary Atieno   | Kisumu  | S002    | 2,800,000 |
|         102 | Faith Chebet  | Nakuru  | S003    | 4,100,000 |
|        NULL | NULL          | NULL    | S004    | 2,500,000 |

Notice the final row.

The sales information exists:



```text id="79hwha"
S004 → KSh 2,500,000

but SQL cannot find corresponding customer information.

Therefore:

```text id="i1a68m"
customer_name → NULL
county → NULL




---

## Finding Sales Without Matching Customers

This can be useful when checking data quality.

For example:



```sql id="dhkqz5"
SELECT
    s.sale_id,
    s.customer_id,
    s.amount
FROM customers AS c
RIGHT JOIN sales AS s
    ON c.customer_id = s.customer_id
WHERE c.customer_id IS NULL;

The result would be:

sale_id	customer_id	amount
S004	105	2,500,000

This tells us that sale S004 references a customer that cannot be found in the customers table.

As a data analyst, this could be something worth investigating before performing further analysis.

RIGHT JOIN vs LEFT JOIN

The main difference is simply which table must be preserved.

```text id="3ew49g"
LEFT JOIN

CUSTOMERS ← Keep everything
│
└──────── SALES




Compared with:



```text id="rrk6mm"
RIGHT JOIN

CUSTOMERS ────────┐
                  │
              SALES ← Keep everything

We can summarize them as:

LEFT JOIN	RIGHT JOIN
Keeps all rows from the left table	Keeps all rows from the right table
Matches information from the right	Matches information from the left
Missing right-side values become `NULL`	Missing left-side values become `NULL`

Can a RIGHT JOIN Be Written as a LEFT JOIN?

Yes.

This is an important observation.

Consider:

```sql id="tg0sgr"
SELECT *
FROM customers AS c
RIGHT JOIN sales AS s
ON c.customer_id = s.customer_id;




We could simply reverse the table order:



```sql id="32frst"
SELECT *
FROM sales AS s
LEFT JOIN customers AS c
    ON s.customer_id = c.customer_id;

Both queries express the same basic requirement:

Keep every sales record and bring in customer information where a matching customer exists.

Because of this, many SQL developers and analysts prefer LEFT JOIN for readability and consistency and simply change which table appears first.

Nevertheless, understanding RIGHT JOIN is important because you will encounter it in existing SQL queries and technical assessments.

When Should You Use a RIGHT JOIN?

A RIGHT JOIN can be useful when:

Every record from the right table must be retained.
You want to identify records on the right that have no corresponding match on the left.
The structure of an existing query makes keeping the right table convenient.

A simple way to remember the first three joins is:

INNER JOIN: Keep the matches.
LEFT JOIN: Keep everything on the left.
RIGHT JOIN: Keep everything on the right.

But there is still one question left:

What if we want to keep everything from both tables — whether the records match or not?

For that, we use a FULL OUTER JOIN.

4. FULL OUTER JOIN — Keeping Everything from Both Tables

So far, we have seen that:

INNER JOIN keeps only matching records.
LEFT JOIN keeps all records from the left table.
RIGHT JOIN keeps all records from the right table.

But what happens when we want to keep all records from both tables, regardless of whether they have a match?

This is where the FULL OUTER JOIN comes in.

A FULL OUTER JOIN returns:

Records that match in both tables
Unmatched records from the left table
Unmatched records from the right table

Where no corresponding record exists, SQL returns NULL for the missing values.

A simple way to remember it is:

FULL OUTER JOIN = Give me everything from both tables and match what you can.

Our Customers Table

customer_id	customer_name	county
101	James Mwangi	Nairobi
102	Faith Chebet	Nakuru
103	Mary Atieno	Kisumu
104	Brian Otieno	Mombasa

Our Sales Table

sale_id	customer_id	amount
S001	101	3,500,000
S002	103	2,800,000
S003	102	4,100,000
S004	105	2,500,000

There are two important unmatched records:

```text id="m7n3f8"
Customer 104 → No sale
Sale S004 → Customer 105 is not in customers




A `FULL OUTER JOIN` allows us to see both.

### FULL OUTER JOIN Syntax



```sql id="w4j2nv"
SELECT
    c.customer_id AS customer_record_id,
    c.customer_name,
    c.county,
    s.sale_id,
    s.customer_id AS sales_customer_id,
    s.amount
FROM customers AS c
FULL OUTER JOIN sales AS s
    ON c.customer_id = s.customer_id;

SQL first matches records where the customer_id exists in both tables:

```text id="zt28oc"
CUSTOMERS SALES

101 James Mwangi ────────────── 101 S001 ✓
102 Faith Chebet ────────────── 102 S003 ✓
103 Mary Atieno ────────────── 103 S002 ✓

104 Brian Otieno No match
105 S004
No match




Unlike an `INNER JOIN`, neither unmatched record is discarded.

### Result

| customer_record_id | customer_name | county  | sale_id | sales_customer_id |    amount |
| -----------------: | ------------- | ------- | ------- | ----------------: | --------: |
|                101 | James Mwangi  | Nairobi | S001    |               101 | 3,500,000 |
|                102 | Faith Chebet  | Nakuru  | S003    |               102 | 4,100,000 |
|                103 | Mary Atieno   | Kisumu  | S002    |               103 | 2,800,000 |
|                104 | Brian Otieno  | Mombasa | NULL    |              NULL |      NULL |
|               NULL | NULL          | NULL    | S004    |               105 | 2,500,000 |

The result gives us three different situations.

### 1. Matching Records

For customers `101`, `102`, and `103`, SQL found matching records in both tables.

Their customer and sales information therefore appears together.

### 2. Customer Without a Sale

Brian Otieno (`customer_id = 104`) exists in the `customers` table but has no corresponding sale.

His sales information therefore appears as:



```text id="9k1qxr"
sale_id → NULL
amount  → NULL

3. Sale Without a Customer

Sale S004 references customer_id = 105, but that customer cannot be found in the customers table.

Therefore, the customer information appears as NULL.

This makes FULL OUTER JOIN particularly useful when comparing datasets and looking for discrepancies.

Finding Only the Unmatched Records

Sometimes we are not interested in the matches at all.

Instead, we want to ask:

Which records exist in one table but do not have a corresponding record in the other?

We can combine FULL OUTER JOIN with a WHERE condition:

```sql id="h6v4np"
SELECT
c.customer_id,
c.customer_name,
s.sale_id,
s.customer_id AS sales_customer_id
FROM customers AS c
FULL OUTER JOIN sales AS s
ON c.customer_id = s.customer_id
WHERE c.customer_id IS NULL
OR s.customer_id IS NULL;




This would identify:



```text id="7d2u9b"
Brian Otieno → Customer without a sale
S004         → Sale without a matching customer

This type of query can be very useful during data validation, reconciliation, and data-quality checks.

Comparing the Four Main JOINs

We can now bring together everything we have learned.

JOIN	Matching Rows	Unmatched Left Rows	Unmatched Right Rows
`INNER JOIN`	✓	✗	✗
`LEFT JOIN`	✓	✓	✗
`RIGHT JOIN`	✓	✗	✓
`FULL OUTER JOIN`	✓	✓	✓

Another simple way to remember them is:

```text id="21a5hx"
INNER JOIN
→ Keep only matches

LEFT JOIN
→ Keep matches + everything from the left

RIGHT JOIN
→ Keep matches + everything from the right

FULL OUTER JOIN
→ Keep everything from both sides




The JOIN you choose should therefore depend on the **question you are trying to answer**, not simply on which syntax you remember.

For example:

**“Which customers have made purchases?”**

→ `INNER JOIN`

**“Show me all customers, including those who have never purchased.”**

→ `LEFT JOIN`

**“Keep every sales transaction, even if customer information is missing.”**

→ `RIGHT JOIN` — or equivalently restructure the query using a `LEFT JOIN`.

**“Show me all records from both datasets and highlight where information does not match.”**

→ `FULL OUTER JOIN`

Understanding these four JOINs gives you the foundation for combining related tables in SQL.

## Conclusion

SQL JOINs are an essential skill when working with relational databases because real-world data is often distributed across multiple related tables. Instead of storing customers, sales, products, employees, and other information in one large table, relational databases organize this information into separate tables that can be connected when needed.

In this article, we explored four important types of SQL JOINs:

* **INNER JOIN** — returns records that have matching values in 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 have a match or not.

The key to choosing the correct JOIN is to first understand the **question you are trying to answer**.

If you only need customers who have made purchases, an `INNER JOIN` may be appropriate. If you need all customers, including those who have never purchased anything, a `LEFT JOIN` would be more suitable. If you need to compare two datasets and identify both matching and unmatched records, a `FULL OUTER JOIN` can provide a more complete picture.

A simple way to remember the four JOINs is:



```text
INNER JOIN      → Keep the matches

LEFT JOIN       → Keep everything on the left

RIGHT JOIN      → Keep everything on the right

FULL OUTER JOIN → Keep everything from both sides

However, understanding JOIN syntax is only part of the skill. It is equally important to understand primary keys, foreign keys, table relationships, NULL values, and the level of detail in each table. These concepts help ensure that tables are joined correctly and that the results of an analysis are reliable.

For a data analyst, JOINs are particularly valuable because they allow data from different parts of a business to be brought together. A sales transaction can be connected to a customer, a customer to a location, a sale to a product, or an employee to a department. Once these connections are made, seemingly separate pieces of information can become meaningful business insights.

The best way to become comfortable with SQL JOINs is through practice. Create a few related tables, experiment with each JOIN, examine the results, and ask yourself why certain records appeared while others did not.

Ultimately, SQL JOINs are about one simple idea:

Connecting related pieces of data to reveal a more complete story.

INTRODUCTION TO SQL (DML & DDL

Gideon Kiprono — Sun, 20 Sep 2026 15:23:53 +0000

Introduction

Every day, businesses and applications generate large amounts of data. Think about an online shop that needs to keep track of its customers, products, orders, and payments. This information needs to be stored in an organized way so that it can easily be accessed, updated, and analyzed. One common way of doing this is by using a relational database.

A relational database organizes data into tables, which consist of rows and columns. For example, a company may have a customers table containing customer information and an orders table containing details about purchases made by those customers.

To communicate with these databases, we use SQL (Structured Query Language).

SQL is a language used to interact with relational databases. It allows us to perform tasks such as creating tables, adding new records, retrieving information, updating existing data, and deleting data that is no longer required.

For example, imagine we have a table called customers. If we wanted to see all the customers stored in that table, we could write:

SELECT *
FROM customers;

This simple SQL statement asks the database to return all the records from the customers table.

However, not all SQL commands perform the same type of task. Some commands are used to define and change the structure of a database, while others are used to work with the data stored inside those structures.

SQL commands are therefore commonly grouped into categories according to their purpose. Two important categories that every SQL beginner should understand are:

DDL — Data Definition Language, which is used to define and manage database structures.
DML — Data Manipulation Language, which is used to add, modify, and remove data stored within those structures.

In this article, we will explore DDL and DML using practical examples and learn how they work together when building and managing a relational database.

Data Definition Language (DDL)

Before we can store customer details, sales transactions, products, or any other information in a relational database, we first need to create a structure that will hold that data.

This is where Data Definition Language (DDL) comes in.

DDL (Data Definition Language) is a category of SQL commands used to create, define, modify, and remove database objects and structures.

Database objects can include:

Databases
Tables
Schemas
Views
Indexes
Sequences

For a beginner, the easiest place to understand DDL is by working with tables.

Imagine we are developing a database for a car dealership. Before we can add information about the cars available for sale, we need to create a table that defines what information should be stored.

For example, we might want to store:

Car ID
Make
Model
Year of manufacture
Price
Status

DDL allows us to create this structure before inserting the actual car records.

The main DDL commands we will explore are:

CREATE — creates a new database object.
ALTER — changes the structure of an existing database object.
TRUNCATE — removes all records from a table while retaining its structure.
DROP — removes a database object completely.

1. CREATE — Creating Database Objects

The CREATE command is used to create new database objects.

One of its most common uses is creating a table.

The general syntax is:

```sql id="8o7rta"
CREATE TABLE table_name (
column_name data_type constraints
);




Let's create a table called `cars`:



```sql id="o5ryy6"
CREATE TABLE cars (
    car_id SERIAL PRIMARY KEY,
    make VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    year_of_manufacture INT,
    price DECIMAL(12,2),
    status VARCHAR(30)
);

At this point, we have created the structure of the table, but we have not added any car records.

We can think of it like creating an empty spreadsheet:

car_id	make	model	year_of_manufacture	price	status

The columns exist, but there is no data yet.

Understanding the CREATE statement

Let's break down some important parts.

```sql id="jj9e0h"
car_id SERIAL PRIMARY KEY




`car_id` is the name of the column.

`SERIAL` is a PostgreSQL feature commonly used to generate sequential integer values automatically.

`PRIMARY KEY` means that the column uniquely identifies each record in the table.

Next:



```sql id="o7i5fc"
make VARCHAR(50) NOT NULL

VARCHAR(50) allows text values with a maximum length of 50 characters.

NOT NULL means that this column must contain a value.

And:

```sql id="5c9yte"
price DECIMAL(12,2)




allows us to store decimal numbers, making it suitable for values such as prices.

Choosing appropriate **data types and constraints** is an important part of database design because they determine what kind of information can be stored in each column.

---

### 2. ALTER — Modifying an Existing Table

Database requirements can change over time.

Suppose we created our `cars` table but later realized that we also need to record the colour of each vehicle.

We do not necessarily need to delete and recreate the entire table.

Instead, we can use `ALTER TABLE`.



```sql id="8nmzoh"
ALTER TABLE cars
ADD COLUMN colour VARCHAR(30);

Our table structure now becomes:

car_id	make	model	year_of_manufacture	price	status	colour

ALTER TABLE can be used for several structural changes.

For example, we can rename a column:

```sql id="r62ec9"
ALTER TABLE cars
RENAME COLUMN colour TO car_colour;




We can also remove a column:



```sql id="81vzi4"
ALTER TABLE cars
DROP COLUMN car_colour;

Therefore, ALTER is useful when the structure of an existing database object needs to change.

3. TRUNCATE — Removing All Records

Suppose our cars table contains hundreds of records and we want to remove all the rows while keeping the table itself.

We can use:

```sql id="m0xcr7"
TRUNCATE TABLE cars;




After executing the command, the table still exists:

| car_id | make | model | year_of_manufacture | price | status |
| ------ | ---- | ----- | ------------------- | ----- | ------ |
|        |      |       |                     |       |        |

The **structure remains**, but the records have been removed.

This distinction is important because `TRUNCATE` does not mean the same thing as `DROP`.

---

### 4. DROP — Removing a Database Object

The `DROP` command removes a database object completely.

For example:



```sql id="adwsm2"
DROP TABLE cars;

This removes the cars table itself.

After executing the statement, we can no longer query the table because its definition has been removed from the database.

A simple way to remember the difference is:

TRUNCATE

```text id="tv5mdv"
Table
├── Structure ✓
└── Records ✗




**DROP**



```text id="ezcn2w"
Table
├── Structure  ✗
└── Records    ✗

Because commands such as DROP and TRUNCATE can remove large amounts of data or entire database objects, they should be used carefully, particularly in production environments.

Putting DDL Together

Let's look at the lifecycle of our table.

First, we create it:

```sql id="dl1qv5"
CREATE TABLE cars (
car_id SERIAL PRIMARY KEY,
make VARCHAR(50),
model VARCHAR(50),
price DECIMAL(12,2)
);




Later, our requirements change, so we **alter** it:



```sql id="w1m2lg"
ALTER TABLE cars
ADD COLUMN status VARCHAR(30);

If we want to remove all its records but keep the structure, we can use:

```sql id="a3ic11"
TRUNCATE TABLE cars;




And if the table itself is no longer required:



```sql id="n4nbbw"
DROP TABLE cars;

The four commands can therefore be summarized as:

Command	Purpose	Table Structure	Data
`CREATE`	Creates a new object	Created	Empty initially
`ALTER`	Changes an existing object's structure	Modified	Usually retained
`TRUNCATE`	Removes all table records	Retained	Removed
`DROP`	Removes the database object	Removed	Removed

DDL therefore provides the structure or foundation of our database.

Once that structure exists, we can begin adding and working with actual records. This brings us to the next category of SQL commands: Data Manipulation Language (DML).

Data Manipulation Language (DML)

Once a database and its tables have been created, the next step is to work with the data stored inside those tables. This is where Data Manipulation Language (DML) comes in.

DML (Data Manipulation Language) refers to SQL commands used to add, modify, and remove data stored in database tables.

Think of a database table like a spreadsheet. The table structure defines the columns available, while DML allows us to work with the individual records stored in the rows.

For example, suppose we have the following customers table:

customer_id	customer_name	county	email
1	John Kamau	Nairobi	john@example.com
2	Mary Wanjiku	Kiambu	mary@example.com
3	Brian Kiptoo	Nakuru	brian@example.com

The main DML commands we will explore are:

INSERT — adds new records to a table.
UPDATE — modifies existing records.
DELETE — removes records from a table.

We will also look at SELECT, which retrieves data from a database. SELECT is sometimes taught alongside DML, although some SQL classifications place it in a separate category called DQL (Data Query Language).

1. INSERT — Adding Data

The INSERT statement is used when we want to add new records to a table.

The basic syntax is:

```sql id="5um3sm"
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);




Suppose a new customer named Alice joins our business. We could add her to the `customers` table using:



```sql id="98hspj"
INSERT INTO customers (customer_name, county, email)
VALUES ('Alice Njeri', 'Nairobi', 'alice@example.com');

Breaking this statement down:

INSERT INTO tells SQL that we want to add a new record.
customers identifies the table receiving the data.
The columns in parentheses specify where the values should be stored.
VALUES contains the actual information being inserted.

After executing the statement, our table might contain:

customer_id	customer_name	county	email
1	John Kamau	Nairobi	john@example.com
2	Mary Wanjiku	Kiambu	mary@example.com
3	Brian Kiptoo	Nakuru	brian@example.com
4	Alice Njeri	Nairobi	alice@example.com

We can also insert several records in a single statement:

```sql id="i4n3rx"
INSERT INTO customers (customer_name, county, email)
VALUES
('Peter Otieno', 'Kisumu', 'peter@example.com'),
('Faith Chebet', 'Kericho', 'faith@example.com'),
('David Mwangi', 'Nyeri', 'david@example.com');




This is known as inserting **multiple rows**.

---

### 2. UPDATE — Modifying Existing Data

Data stored in a database does not always remain the same. A customer might change their email address, move to another county, or update other personal details.

The `UPDATE` statement allows us to modify existing records.

Its basic syntax is:



```sql id="17l8gf"
UPDATE table_name
SET column_name = new_value
WHERE condition;

Suppose Alice moves from Nairobi to Nakuru. We could update her record using:

```sql id="e21cbr"
UPDATE customers
SET county = 'Nakuru'
WHERE customer_id = 4;




The statement can be understood as:



```text id="8pn4ow"
UPDATE customers
        ↓
Which table?

SET county = 'Nakuru'
        ↓
What should change?

WHERE customer_id = 4
        ↓
Which record should change?

The WHERE clause is extremely important because it determines which rows are affected.

Consider the following statement:

```sql id="r1v61y"
UPDATE customers
SET county = 'Nakuru';




Because there is no `WHERE` condition, SQL will attempt to change the county to `Nakuru` for **every row in the table**.

Therefore, before executing an `UPDATE`, always check whether your `WHERE` condition identifies the intended records.

You can even check the affected records first:



```sql id="4ppfl4"
SELECT *
FROM customers
WHERE customer_id = 4;

Once you are satisfied that the correct record has been identified, you can execute the UPDATE.

3. DELETE — Removing Data

The DELETE statement removes existing records from a table.

Its basic syntax is:

```sql id="rfdjhu"
DELETE FROM table_name
WHERE condition;




For example, suppose we want to remove the customer whose ID is `4`:



```sql id="97gexx"
DELETE FROM customers
WHERE customer_id = 4;

The WHERE condition tells the database exactly which record should be removed.

As with UPDATE, you must be careful when using DELETE.

Consider:

```sql id="b1f44m"
DELETE FROM customers;




Without a `WHERE` clause, this statement targets **all rows in the table**.

The table itself still exists, but its records will be removed.

This is different from:



```sql id="c68daw"
DROP TABLE customers;

DROP TABLE removes the table itself, including its structure, whereas DELETE removes records from an existing table.

4. SELECT — Retrieving Data

Although SELECT does not change the data stored in a table, it is one of the SQL commands you will use most frequently.

SELECT is used to retrieve information from a database.

To retrieve every column from the customers table:

```sql id="syf0pz"
SELECT *
FROM customers;




The `*` means **all columns**.

If we only need customer names and counties, we can specify those columns:



```sql id="xy7fyq"
SELECT customer_name, county
FROM customers;

We can also use WHERE to retrieve only records matching a particular condition:

```sql id="rd3jgg"
SELECT customer_name, county
FROM customers
WHERE county = 'Nairobi';




This asks the database to return customers whose county is Nairobi.

`SELECT` becomes much more powerful when combined with SQL features such as:

* `WHERE` for filtering
* `ORDER BY` for sorting
* `GROUP BY` for grouping
* Aggregate functions such as `SUM()`, `COUNT()`, `AVG()`, `MIN()`, and `MAX()`
* `JOIN` for retrieving related information from multiple tables

These concepts can be explored separately as you progress beyond basic DML.

---

## Bringing the DML Commands Together

We can think about the commands in terms of the lifecycle of a record.

A record is first **created**:



```sql id="j18qnd"
INSERT INTO customers (customer_name, county, email)
VALUES ('Alice Njeri', 'Nairobi', 'alice@example.com');

It can later be read:

```sql id="os2xvw"
SELECT *
FROM customers
WHERE customer_name = 'Alice Njeri';




If something changes, it can be **updated**:



```sql id="c39tn2"
UPDATE customers
SET county = 'Nakuru'
WHERE customer_name = 'Alice Njeri';

Finally, if the record is no longer required, it can be deleted:

```sql id="vz7ij9"
DELETE FROM customers
WHERE customer_name = 'Alice Njeri';




These four operations are often described using the acronym **CRUD**:

| CRUD Operation | SQL Command | Purpose                |
| -------------- | ----------- | ---------------------- |
| Create         | `INSERT`    | Add new data           |
| Read           | `SELECT`    | Retrieve existing data |
| Update         | `UPDATE`    | Modify existing data   |
| Delete         | `DELETE`    | Remove existing data   |

CRUD operations form the foundation of how many applications interact with relational databases.

For example, when a customer creates an account on an online platform, the application may use an `INSERT` operation behind the scenes. When the customer views their profile, the application retrieves their information. Updating their address modifies the existing record, while deleting an account may involve removing or deactivating the associated data.

Understanding DML therefore goes beyond learning SQL syntax. It helps us understand how applications create, retrieve, modify, and manage the data that organizations use every day.

##Conclusion

Understanding DDL (Data Definition Language) and DML (Data Manipulation Language) is an important foundation for anyone learning SQL and relational databases.

DDL focuses on the structure of the database. Commands such as CREATE, ALTER, TRUNCATE, and DROP allow us to create and manage database objects such as tables.

DML, on the other hand, focuses on the data stored within those structures. Commands such as INSERT, UPDATE, and DELETE allow us to add, modify, and remove records, while SELECT is commonly used to retrieve and explore the stored data.

DIVING DEEPER INTO POWER BI

Gideon Kiprono — Wed, 16 Sep 2026 23:58:27 +0000

Introduction

If you’ve ever packed a backpack for a long trip, you know that just throwing everything into one giant pocket makes a total mess. When you urgently need your passport, you have to dig past tangled headphones, loose change, and extra clothes. Power BI is no different. If you dump all your data into one big, messy spreadsheet, your reports will break and run painfully slow. Mastering Power BI is simply the art of packing your data into the right compartments so that finding answers is instant and effortless.

What is Power BI

Power BI is a Microsoft's business analytics and data visualization platform. It lets you connect to various data sources, clean and transform raw information, and build interactive dashboards that reveal trends and track Key Performance Indicators (KPIs) to drive better business decisions.

From the definition, we can say that the Power BI has four main functionalities:

Connect -Power BI enables one to intergrate data from various sources.
Transform - This utilises tools like Power Query and is useful in cleaning, merging and working on raw data.
Model- Power BI enables creating relationship within a dataset to enable easy analysis.
Visualize - Finaly, you can use the models created to visualize data in a dashboard and draw conclusions that can be used to make business decisions.

Power BI provides a complete platform for collecting, transforming, modelling, analyzing, and visualizing data. To fully utilize its capabilities, it is important to understand the key concepts that form the foundation of Power BI.

Areas to be covered

The foundation : Data Ingestion and Transformation (Power Query)
Fact vs. Dimension Tables: Organizing Your Pantry
The Blueprint: Data Modelling and Schema Design
The Glue : Joins vs Relationships
The Engine : The DAX Calculation layer
The Presentation : Visualizations and Reporting

In this section, we will dive deeper into what Power BI is and explore the fundamental concepts that will help us build efficient data models and create impactful analytical reports.

The foundation : Data Ingestion and Transformation (Power Query)

Before you can cook a magnificent, five-star meal, you have to spend some time in the kitchen prepping your ingredients. You would never throw an unpeeled onion, unwashed vegetables, and raw chicken straight into a pot together. If you did, the final dish would be a complete disaster. You have to wash, chop, peel, and sort your ingredients first.

In Power BI, this digital kitchen is called Power Query, and it is where you perform ETL (Extract, Transform, Load).

When you import raw data into Power BI from Excel spreadsheets or databases, it arrives messy. It often contains typos, missing information, blank rows, and poorly formatted dates. Power Query acts as your data preparation room. Without writing any complicated code, you can click simple buttons to:

Chop away excess data: Remove columns and rows you don’t need.
Wash the data: Fix typos, replace errors, and filter out blank spaces.
Organize the pantry: Ensure numbers are treated as numbers and dates look like actual calendar dates.

By taking the time to cleanly prepare your data "ingredients" in Power Query first, you ensure that the rest of your Power BI building experience is fast, smooth, and error-free.

Fact vs. Dimension Tables: Organizing Your Pantry

Now that your data ingredients are clean and prepped, it’s time to organize them. In Power BI, you don't just dump all your data into one massive, endless spreadsheet. Instead, you separate your data into two distinct types of tables: Fact tables and Dimension tables.

To a complete beginner, these names sound intimidating, but they are just like dividing your kitchen into a Sales Ledger and a Pantry.

Fact TAbles (The "Sales Ledger)

Think of the Fact table as a fast-moving restaurant's sales ledger. Every single time a customer buys a meal, a new line is written down. It answers the questions “How much?”, “How many?”, and “When?”

What lives here: Numbers, quantities, and prices (metrics). It also holds unique ID numbers to link back to your pantry.
The Shape: It is a narrow but incredibly deep table. It might only have a few columns, but it can easily hold millions of rows recording every single historical transaction.

Dimension Tables (The "Pantry Items")

Think of Dimension tables as the labeled jars sitting neatly in your pantry. They don't record actions; instead, they hold the descriptive details about the things surrounding the actions. They answer the questions “Who?”, “What?”, and “Where?”

What lives here: Text labels like product names, food categories, customer addresses, store locations, and calendar dates.
The Shape: It is a _wide but shallow _table. It has lots of descriptive columns (like item color, size, and category) but far fewer rows.

Why Separate Them?

If you wrote the full name, address, and phone number of a customer on every single line of your sales ledger, your book would become massive, bloated, and impossible to read. By keeping the details in the pantry (Dimension tables) and the math in the ledger (Fact table), Power BI can find answers in milliseconds.

The Blueprint: Data Modelling and Schema Design

Now that you have separated your clean ingredients into a transactional ledger (Fact table) and structured pantry jars (Dimension tables), you need a plan for how they will sit together. In the tech world, this layout plan is called Schema Design, and the act of connecting them is called Data Modeling.

Think of this as designing the physical blueprint and traffic flow of your kitchen so the chef can grab ingredients instantly without tripping over anything.

In Power BI, there is one supreme blueprint that beats all others: The Star Schema.

What is a Star Schema?

A Star Schema is simply a visual pattern.

The Center: You place your heavy Fact table (the sales ledger) right in the middle of your digital workspace.
The Points: You place your Dimension tables (the pantry jars for Products, Customers, and Dates) in a ring surrounding that central table.

When you draw connection lines from each surrounding pantry jar to the central ledger, the layout naturally begins to look like a star (or a solar system with planets orbiting a central sun).

Why is this blueprint the "Golden Rule"?

Power BI is custom-engineered from the ground up to read data that is laid out like a star. When a user clicks a button on your dashboard to filter by "October" or "Espresso Drinks," Power BI starts at the outer edge of the star (the Dimension table) and flows smoothly down a single highway directly into the center (the Fact table) to calculate the totals.

If you don't follow this blueprint—and instead create a chaotic spiderweb of tangled tables—your report will become painfully slow, your charts will show incorrect numbers, and your calculations will break.

The Glue: Joins vs. Relationships

Now that you have your Star Schema blueprint mapped out on paper, you need to actually connect your tables so they can talk to each other. In Power BI, this is where beginners often get tripped up because they hear two terms used interchangeably: Joins **and **Relationships.

While both act as the "glue" that connects your data using a matching ID column (like a ProductID), they happen at entirely different times and serve completely different purposes.

Joins (The Upstream Glue)

A Join happens early in the kitchen prep room (Power Query).

How it works: Imagine taking two separate pieces of paper—like a list of subcategories and a list of categories—and physically smashing them together with glue to create one single, wider spreadsheet.

The Goal: You use Joins to clean up messy data layouts (like a snowflake schema) and flatten them down so they fit perfectly into your neat outer pantry jars (Dimension tables).

Relationships (The Downstream Glue)

A Relationship happens later on the main stage (Model View canvas).

How it works: Instead of physically smashing tables together, a relationship keeps the tables completely separate but cuts a "peek-a-boo window" or draws a bridge between them.

The Goal: You draw virtual connection lines directly from your outer pantry jars to your central ledger. This tells Power BI: "When a user filters a chart by a specific product name over here, look across the bridge and update the total sales numbers over there."

Why the Difference Matters to You

If you use Joins for everything, your data model becomes one giant, bloated spreadsheet that slows Power BI to a crawl. If you use Relationships correctly to connect your Star Schema, your file size stays incredibly small, and your reports run lightning-fast.

The Engine: The DAX Calculation Layer.

Once your tables are neatly prepped, organized into a star layout, and connected by relationships, your data is officially ready to work for you. But how do you actually calculate your business metrics, like your total profit margins, year-over-year growth, or active customer counts?

You use *DAX *(Data Analysis Expressions).

Think of DAX as the high-powered engine under the hood of Power BI. While it looks a bit like Excel formulas, it behaves completely differently. For a beginner, the absolute most important step to mastering DAX is understanding the difference between a Calculated Column and a Measure.

Calculated Columns (The Hard Drive Glues)

A Calculated Column calculates a new value for every single row in your table, right when the data is loaded.

The Analogy: Imagine writing a new number by hand onto every single receipt line in your ledger.

The Cost: Because these numbers are physically written into your data, they take up permanent storage space. If you have millions of rows, your file size will balloon quickly.

When to use: Use them sparingly, usually only when you need to create a new text label for a slicer or filter (like grouping ages into "Under 30" and "30+").

Measures (The On-Demand Calculator)

A Measure does not calculate anything ahead of time and takes up zero physical storage space. It sits completely invisible in your model until you drop it onto a chart.

The Analogy: Think of a measure like a programmable button on a smart calculator. It waits patiently until you click it, instantly looks at whatever filters are currently active on your dashboard screen, and calculates the answer on the fly in milliseconds.

The Benefit: It keeps your Power BI files incredibly small and handles dynamic filtering seamlessly.

When to use: Use measures for 99% of your math—sums, averages, percentages, and performance metrics.

The Golden Concept: Filter Context

As a beginner, you will hear the phrase "Filter Context" a lot. Don't let it scare you. It simply means that DAX measures adapt to their environment. If you drop a "Total Sales" measure into a bar chart showing different countries, that single measure instantly figures out how to calculate Sales for Canada in the Canada bar, and Sales for Kenya in the Kenya bar. It only calculates what the user is currently looking at.

The Presentation: Visualizations & Reporting

You have prepped your ingredients in Power Query, organized your kitchen into a Star Schema, connected the tables with relationships, and built your DAX calculation engine. Now comes the fun part: serving the meal to your guests. In Power BI, this is the Visualization and Reporting layer.

This is where the magic happens for the person reading your report, but as a creator, you must understand how your back-end structure controls your front-end visuals.

Visuals are Just Windows into Your Data

Every chart, graph, map, and card you drop onto your canvas is just a visual window running your DAX calculations.

The Golden Rule for Layouts: When building a visual, you will almost always pull your text categories and labels (like Product Name, Region, or Month) from your Dimension tables to use as your chart axes, rows, or slicers.

The Golden Rule for Numbers: You will always pull your dynamic Measures (like Total Revenue or Profit Margin) into the values field of the chart.

How Interaction Works: Filter Propagation

The coolest feature of Power BI for a beginner is interactivity—click on a slice of a pie chart, and the rest of the page instantly updates. This is called Filter Propagation.

Because you drew those neat "relationship bridges" earlier in your Star Schema, clicking a visual sends a silent command across the bridge. It instantly filters down your tables behind the scenes, and your DAX measures recalculate the new numbers in the blink of an eye.

Keep It Simple for Your User

When designing the actual report page, remember that your audience didn't see all the hard work you did behind the scenes.

Don't clutter the page with dozens of complex charts just because you can.
Stick to 3 or 4 key visuals per page.
Make sure your most critical number (like total profit) stands out in a large, easy-to-read "Card" visual right at the top.

Conclusion: Your Data Masterpiece Awaits

Building a Power BI report doesn't have to feel like a mystery. By understanding these core concepts, you have shifted from someone who just clicks random buttons to a true data architect.

Remember the journey your data takes:

Power Query cleans and preps your raw ingredients.
The Star Schema organizes them into a central Fact table surrounded by detailed Dimension tables.
Relationships build the bridges that let your tables talk to each other.
DAX Measures act as the smart, on-demand calculators.
Visualizations bring it all to life for your audience.

Now, It’s Your Turn!

The absolute best way to master Power BI is to get your hands dirty. Open up Power BI Desktop, pull in a simple spreadsheet, and start sorting your ingredients. Don't worry about making it perfect on your first try—every expert dashboard designer started exactly where you are sitting right now. Trust your blueprint, lean into the process, and go build your very first data masterpiece!

Connecting Power BI to SQL databases : Local PostgreSQL and Aiven cloud

Gideon Kiprono — Tue, 15 Sep 2026 16:54:13 +0000

Introduction

Power BI supports connectivity to a wide range of data sources. This includes files, relational databases, cloud platforms, web services and online applications. In this article, we will focus specifically on connecting Power BI to PostgreSQL database. This can be done either locally or using the cloud.
A Database is is a highly organized, centralized digital system designed to store, manage, secure, and instantly retrieve massive amounts of structured data.
A database is managed by a software engine as known as the database management system (DBMS), like SQL Server or PostgreSQL. When Power BI asks for data, this engine does the heavy lifting: filtering, aggregating, and sorting millions of rows in milliseconds before sending just the necessary results back to Power BI.
We will explore two scenarios:

Connecting Power BI to a postgreSQL database hosted locally.
Connecting Power BI to a cloud-hosted postgreSQL database using Aiven with SSL configuraion

Before proceeding, it will be important to compare the two scenarios:

Attribute	Local PostgreSQL	Cloud-Hosted PostgreSQL
Primary Location	Host machine (localhost or local IP)	Public cloud server instance
Security Setup	Minimal setup; generally unencrypted over local networks	Strict Aiven Console CA certificate download and SSL configuration (sslmode=require)
Accessibility	Limited to the local device or a restricted local network	Accessible anywhere over the internet
Maintenance	User-managed (manual updates, scaling, backups)	Fully managed by Aiven (automated patches, easy hardware scaling)

Connecting Power BI to a local SQL database

A local database is a database server installed and run entirely on your own local computer rather than a remote cloud or external server.

Process 1: Connecting a new localhost database in DBeaver.

Step 1
To get started with the process, we need to download all the tools that we need for the process.

The postgreSQL from our browser. Clink this link to download from the official site, PostgreSQL download link. We then install it to our computer.
DBeaver which is a free, open-source database management tool recommended for personal projects. Used to manage and explore SQL databases like MySQL, MariaDB, PostgreSQL, SQLite, Apache Family, and more. We will download and install it in our local machine.

Step 2
We then open the DBeaver and create a new local database connection with PostgreSQL Database.
When you open the debeaver, a the Home screen will open as shown below will open:

To create a new database connection, we will press the CTRL + SHIFT + N open or alternatively we can click the new connection wizard button in the toolbar. This will open a new window as shown below.

Step 3
We will then click on the postgreSQL icon and it will take us to a new window to configure the connection. Since we are using localhost, we will not be required to change the configurations. We only need to input our password which we had set while installing our postgreSQL database.

Step 4
After inputing our password, we will be required to test our connection by pressing the test connection button in the bottom left conner of the window pane. If the connection is sucessful, we will the press the finish button in the bottom right conner to complete the process.

Step 5
In the home screen of DBeaver, a section will appear that will show our new connection as shown below.

Process 2: Importing data to our database

After confirming that we already have our database connection working in DBeaver, we will need to import data to our database.
This is an important step that we will need in the later stages.

To import data to DBeaver, on the left side expand on databases > defaultdb > schemas > either create a new schema or use the public schema.
We will first create a databse schema where now we will be able to put our data.
To create a schema we will use the sql command:
create schema schemaName;

Inside our schema, we will import our data from the browser.
Right click on your preffered schema and click import data.A window will po up as shown below.

Go to -> Input file(s), Browse and select your data

After selecting the file to be imported, you will click next until the end where you you will click proceed. This will complete the process of importation. Congratulations, you will now have completed the process of data importation. Your data will appear as shown below;

Process 3: Getting data in power BI

We will now open our Power BI desktop app, we will click on the blank remote to work with it.

Expand the view in the Get data section as shown below.

Click on more... to expand our options to open a dialog box.
In the dialog box, search for PostgreSQL database under the Database category and click Connect.

After clicking connect, a dialog box will appear that will require our server and database name. We get this information from the time we connected our PostgreSQL database in DBeaver.
Server: localhost:5432 / 127.0.0.1 : 5432 .
(Note: 5432 is our port number.)
Database: postgres

After imputing the values, we proceed by clicking the ok buttton

A new dialog box will appear that requires our User name and password.
User name: postgres
Password: **** (requires the password you set while installing PostgreSql.)
Proceed by clicking the button connect.

This wil take us to a new window where we can access all the tables we had already imported in DBeaver. Choose the tables you want to work on. if you need to clean you can using the transform button which will direct you into power query where you will be able to clean your data. Otherwise, you can load the tables directly so you can start working on them.

Congratulations you have successfully connected Power BI to your localhost postgreSQL database!

Let's now look into how you can connect to a cloud based

Connecting Power BI to a cloud-hosted postgreSQL database using Aiven with SSL configuraion

STEP 1: Setting up our aiven account.

What is Aiven.
Aiven is a fully managed cloud data platform that provides open-source technologies, such as PostgreSQL and Apache Kafka, as a service. It automates complex infrastructure tasks like setup, security patching, backups, and scaling so developers can focus strictly on building applications. The platform allows you to deploy these managed databases seamlessly across major cloud environments, including AWS, Google Cloud, and Microsoft Azure. By offering an accessible free tier, it makes it easy for developers to launch and manage a PostgreSQL database globally with minimal effort.

To setup our aiven account,we will need to go to our browser an type aiven.io
When aiven.io opens, click on get building to proceed to set up our aiven account.

You will need to sign up into your account and then you login to your account. You will be taken to a window that will require you to create a service.
Choose PostgreSQL as your service and click the create service buttom in the bottom right of the window.

You will be taken into the projects tabs where we can see our service details. This details are important to help us later when we open Power BI.
Note: Make sure the services builds until you see it's status changes to running.

STEP 2: Connecting our database in DBeaver.

To establish a cloud database connection in dbeaver, we will start by opening dbeaver console.

Using the new connection button or the shortcut SHIFT + CTRL + N to establish a open a dialog window.
In the dialog box, choose postgreSQL as our database and click next.

Next, we configure the database based on the details we generated from the service we created in aiven.io

Host: according to the service you created in aiven.
Port: according to the service you created in aiven.
Database: defaultdb
Username: avnadmin
Password: according to the service you created in aiven.

Test the connection by clicking on the button Test connection in the bottom left corner then click finish.

After finishing the action, you will be able to see a new connection in the General tab under connections.

Step 43: CA cerificate download and Importation into our Trusted root crtificates

The proceed without doing this step we will generate this error.

We need the Aiven PostgreSQL Service Certificate because Aiven enforces strict, mandatory SSL/TLS encryption for all database connections by default, and your computer needs to trust Aiven's identity to establish a secure link.
Therefore we will need to download and import the CA certificate into our trusted root certifications.

To proceed we will download the CA certificate in aiven.io.

After dowloading the CA certificate, we will need to import the certifictae to our managed user certificates.

Open the managed user certificate control panel in your computer, and under the Trusted root certification, right click on certificates , under all tasks click on import, click next then browse the VA certificte you downloaded and proceed until you click the finish button.

To confirm if the process is successful, a success message box will appear.

STEP 4: Connecting PowerBI to our aiven PostgreSQL database.

We will now open our Power BI Desktop App, we will click on the blank report to work with it.

Expand the view in the **Get data **section as shown below.

Click on more... to expand our options to open a dialog box.
In the dialog box, search for PostgreSQL database under the Database category and click Connect.

A dialog box will appear that will require us to input the server and Database box with values that we obtain while creating the service in aiven.io.
Server: Host name : port number (Depending on your service)
Database: defaultdb
Press ok to proceed.

A new dialog box will appear that will require our username and password. Refer to our service conection details in aiven.ai.
Username: avnadmin
Password: (as provided in the service you created)
Click connect to to proceed

This will take us to a new window where we can access all the tables we had already imported in DBeaver. Choose the tables you want to work on. if you need to clean you can using the transform button which will direct you into power query where you will be able to clean your data. Otherwise, you can load the tables directly so you can start working on them.

Congratulations you have successfully connected Power BI to your aiven postgreSQL database!

Hope you enjoyed this blog. Until next time.

HOW EXCEL IS USED IN REAL WORLD DATA ANALYSIS

Gideon Kiprono — Sat, 06 Jun 2026 21:14:21 +0000

Introduction

What is Excel

Excel is an important tool used in Data analysis. Excel enables a data analyst to ineteract with raw data. Raw data are always messy and require cleaning. This is now the work of excel to help in cleaning, organizing, analysing and visualizing data.

Ways Excel is used in real world data analysis

Financial Modelling . Excel is used in building financial models that can be used to predict the performance of a business. Excel formulas can be used to calculate profits, revenue and costs.
Auditing.Auditors use excel in analysing financial records of an organization.They use it to check errors, detect any fraud and verify financial records.
Tasks Automation. Excel can be used to automate repetitive tasks using formulas.
Data Visualization. One can use excel to turn raw data into charts, graphs and dashboards for easier interpretation of the data.

Basic featuers of Excel

Excel as a tool has many features that enable it bto function properly, this includes;

Excel Ribbon

The Excel Ribbon is the toolbar located at the top of the excel interface that contains all commands and tools organized into tabs such as Home, Insert, Data, Formulas and View. Each tab groups related functions for easy access.

Data Entry and Formatting

Excel allows users to enter different kinds of data such as texts, numbers and dates. Formatting tools are also used for organizing data to improve readability.

Filtering and Sorting

Filtering helps in displaying specific data, while sorting arranges data in ascending or descending order. They help to in organizing large datasets.

Conditional Formating

This is a special feature that helps in highlighting data based on specific rules, making it easier to identify trends and patterns.

Functions in Excel

Excel has differnt kinds of functions that aid in data analysis;

Mathematical / Aggregate Functions: SUM(), AVERAGE(), MEDIAN(),MODE().
Statistical Functions.
Text Functions.
Lookup Functions.
Date and time functions.

Final thoughts

I am not going to lie, Before doing a deep dive into how excel works, my understanding was that excel was not that powerful in data analysis. To my suprise, I have come to know and appreciate excel as a powerful tool in the hands of any aspiring analyst.

DEV Community: Gideon Kiprono

Python Data structures

Introduction

1. The Core Four Data Structures

Lists

Common List Operations in Python

Tuples

Lists vs Tuples in Python

Dictionaries

Lists vs Tuples vs Dictionaries in Python

Sets

Comparison of Python Collections

2. Cheat Sheet: When to Use Each One

3. Practical Example: The Event Registration Manager

Conclusion

Python functions

Introduction

1. Defining and calling functions

2. Parameters and arguments

3. Return values

4. Scope

5. Practical example : The Automated Shipping Fee Calculator

Conclusion

Python loops

Introduction

1. The Two Types of Loops

The for Loop

The while Loop

2. Upgrading Your Loops

break and continue (The Steering Wheel)

Difference between break and continue

enumerate() (The Step Counter)

3. Practical Example: The Automated Security Badge Scanner

Conclusion

Operators and Conditionals

Introduction

1. Operators

Arithmetic Operators

Comparison Operators

Logical Operators

2. Making Choices: If, Elif, and Else

3. Practical Example: The Automated Smart Thermostat

Conclusion

Python Basics

Introduction

Variables (The Digital Storage Units)

How It Works

Dynamic Typing

Data types (The Building Blocks of Information)

Text (Strings)

Whole Numbers (Integers)

Decimals (Floats)

True/False States (Booleans)

Input and Output (Talking to Your Program )

Output: Showing Results with print()

Input: Gathering Data with input()

Basic Python Syntax

The Power of Whitespace (Indentation)

Case Sensitivity Matters

Comments

Practical example

The Digital Receipt Calculator

Conclusion

KIOSK_MANAGER : End to end python project

The problem

Project Scope

Step 1: Design the data

Step 2: Welcome and setup

Step 3: Saving and loading data

to come back here!!!!

Step 4: Viewing stock

Step 5: Adding or restocking a product

Step 6: Selling a product

Step 7: The sales report

Step 8: Searching products

Step 9: The main program

Conclusion

Understanding Subqueries and CTE's

Introduction

SQL Subqueries

Difference between `break` and `continue`

Output: Showing Results with `print()`

Input: Gathering Data with `input()`

The Power of Whitespace (`Indentation`)