DEV Community: Melvin

Python Variables and Data Types: A Beginner's Guide

Melvin — Tue, 22 Sep 2026 18:20:10 +0000

When learning Python, two of the first concepts you need to understand are variables and data types.

Variables allow you to store information and use it later in your program, while data types tell Python what kind of information you are working with.

What Is a Variable?

A variable is a name given to a piece of information so you can use it later in your program.

Imagine you track your daily steps. You walked 9,200 steps today. Without a variable, you would have to type 9200 every time your program needed that number.

With a variable, you store the value once and use its name everywhere else:

steps = 9200

You can think of a variable like a labelled jar.

The label is steps
The value inside is 9200

If you walk 10,400 steps tomorrow, you can simply update the value:

steps = 10400

The variable name stays the same, but its value changes.

Variable Assignment

The basic syntax for creating a variable is:

variable_name = value

For example:

steps = 9200
name = "Melvin"
body_weight = 82.5
workout_done = True

The variable name is on the left, the value is on the right, and = assigns the value to the variable.

In Python, = means assignment. It does not mean that the two sides are mathematically equal.

For example:

steps = 9200

means:

Store the value 9200 in the variable called steps.

Naming Your Variables

Python gives you flexibility when naming variables, but there are some important rules and conventions.

Rule	Good	Bad
No spaces	`water_glasses`	`water glasses`
Cannot start with a number	`day1_steps`	`1steps`
Avoid special characters	`sleep_hours`	`sleep-hours`
Use descriptive names	`bench_press_kg`	`x`

Python variable names are case-sensitive.

steps = 9200
Steps = 10000

These are two different variables.

The standard Python convention is to use lowercase letters with underscores, known as snake_case:

daily_steps = 9200
body_weight_kg = 82.5
workout_completed = True

Good variable names make your code easier to understand.

What Are Data Types?

Not all information is the same.

For example:

9200 is a number
"Monday" is text
82.5 is a decimal number
True represents a yes/no condition

Python uses data types to identify what kind of value a variable contains.

Four data types you will use frequently are:

int - whole numbers
float - decimal numbers
str - text
bool - True or False

1. `int` - Whole Numbers

An int (integer) represents a whole number without a decimal point.

steps = 9200
water_glasses = 8
pages_read = 30
sleep_hours = 7
bench_press_reps = 12

Integers are commonly used for things such as counts, quantities, and measurements that don't require decimals.

2. `float` - Decimal Numbers

A float represents a number containing a decimal point.

body_weight_kg = 82.5
km_walked = 7.3
body_fat_percent = 14.2

Floats are useful when precision is required, such as measurements, percentages, prices, and distances.

3. `str` - Text

A str (string) represents text.

Strings must be enclosed in quotation marks. You can use either single or double quotes.

fasting_protocol = "OMAD"
skill_of_the_day = "Welding"
name = "Melvin"
morning_goal = "8,000 steps before 9 AM"

Python treats the text inside the quotes as a string.

4. `bool` - True or False

A bool (Boolean) represents one of two values:

True
False

For example:

cold_shower = True
workout_done = False
fasting_active = True

Booleans are especially useful when working with conditions and decision-making in programs.

Remember that True and False must begin with capital letters. true and false are not valid Python Boolean values.

Checking the Data Type

Python provides a built-in function called type() that allows you to check the type of a variable.

steps = 9200
body_weight = 82.5
name = "Melvin"
workout_done = True

print(type(steps))
print(type(body_weight))
print(type(name))
print(type(workout_done))

Output:

<class 'int'>
<class 'float'>
<class 'str'>
<class 'bool'>

The type() function is particularly useful when debugging your programs and checking whether a variable contains the type of data you expect.

Printing Variables with Text

You will often want to display a variable together with some descriptive text.

There are two common approaches.

Using Commas

You can pass multiple values to print() separated by commas:

steps = 9200

print("Steps today:", steps)

Output:

Steps today: 9200

Python automatically adds a space between the values.

Using f-Strings

A more flexible and commonly used approach is an f-string.

Place an f before the string and put variables inside {}:

steps = 9200
name = "Melvin"

print(f"{name} walked {steps} steps today.")

Output:

Melvin walked 9200 steps today.

f-strings make it easy to combine text and variables while giving you control over how the output is formatted.

Putting Everything Together

Here's a simple example using variables, different data types, type(), and an f-string:

name = "Melvin"
steps = 9200
body_weight_kg = 82.5
workout_done = True

print(f"Name: {name}")
print(f"Steps: {steps}")
print(f"Body weight: {body_weight_kg} kg")
print(f"Workout completed: {workout_done}")

print(type(name))
print(type(steps))
print(type(body_weight_kg))
print(type(workout_done))

Understanding variables and data types gives you a foundation for working with conditions, loops, functions, data structures, data analysis, and machine learning in Python.

Key Takeaways

A variable is a name used to store a value.
The = operator assigns a value to a variable.
Python variable names are case-sensitive.
Use descriptive names such as daily_steps instead of unclear names such as x.
int stores whole numbers.
float stores decimal numbers.
str stores text.
bool stores True or False.
Use type() to check a variable's data type.
Use f-strings to format text containing variables.

These concepts may look simple, but they form the foundation of almost everything you will build with Python.

SQL Subqueries & CTEs: Write Smarter, Cleaner Queries

Melvin — Fri, 11 Sep 2026 19:13:43 +0000

As SQL queries become more complex, you may need to use the result of one query inside another query. Two techniques that are useful for handling this are subqueries and Common Table Expressions (CTEs).

Both are widely used in PostgreSQL and are especially useful for data analysis, reporting, and working with large datasets.

What Is a Subquery?

A subquery is a SQL query written inside another SQL query.

The inner query produces a result that is then used by the outer query.

For example, suppose we have an employees table containing employee names and salaries. We want to find employees who earn more than the average salary.

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

The inner query:

SELECT AVG(salary)
FROM employees;

calculates the average salary.

The outer query then uses that result to find employees whose salary is greater than the average.

The basic idea is:

Inner query → produces a result
       ↓
Outer query → uses that result

This makes subqueries useful when one calculation depends on another query.

Why Use Subqueries?

Subqueries are useful when you need to perform an intermediate calculation without creating a separate table.

For example, you might want to:

Compare a value with an average, maximum, or minimum
Filter records based on another query
Find customers who have placed orders
Find products that meet a particular condition
Check whether related records exist
Use one query's result as input to another query

Subqueries are particularly useful for answering questions such as:

Which employees earn more than the average salary?

Which customers have placed an order?

Which products cost more than the average product price?

Subqueries with Aggregate Functions

One of the most common uses of subqueries is with aggregate functions such as:

AVG()
SUM()
MAX()
MIN()
COUNT()

For example, to find products that cost more than the average price:

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

The subquery calculates the average price, while the outer query identifies products above that average.

Another example is finding the employee with the highest salary:

SELECT name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

Here, the subquery returns the highest salary, and the outer query finds the employee who earns it.

Subqueries with `IN`

A subquery can return multiple values.

When this happens, operators such as IN are useful.

Suppose we want to find customers who have placed orders worth more than KSh 50,000.

SELECT name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE amount > 50000
);

The inner query identifies the customer IDs associated with orders above KSh 50,000.

The outer query then uses those IDs to find the customers' names.

This is useful when the subquery returns multiple rows.

Subqueries with `EXISTS`

The EXISTS operator checks whether a subquery returns at least one record.

For example, to find customers who have placed at least one order:

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

The important question here is not what the subquery returns.

The question is:

Does a matching order exist?

If an order exists for a customer, that customer is returned.

EXISTS is particularly useful when you are interested in the existence of related records.

Subqueries in the `FROM` Clause

A subquery can also be placed inside the FROM clause.

For example:

SELECT department, average_salary
FROM (
    SELECT
        department,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department
) AS department_stats;

The inner query creates a temporary result containing the average salary for each department.

The outer query then works with that result as if it were a table.

What is a CTE?

CTE stands for Common Table Expression.

A CTE allows you to define a temporary named result using the WITH keyword.

Basic syntax:

WITH cte_name AS (
    SELECT ...
)
SELECT *
FROM cte_name;

For example:

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

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT avg_salary
    FROM average_salary
);

The CTE is called average_salary.

Giving the intermediate result a name can make complex queries easier to understand.

Using multiple CTEs

One of the main advantages of CTEs is that you can create multiple steps within the same query.

For example:

WITH customer_sales AS (
    SELECT
        customer_id,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
),

average_sales AS (
    SELECT AVG(total_sales) AS avg_sales
    FROM customer_sales
)

SELECT
    customer_id,
    total_sales
FROM customer_sales
WHERE total_sales > (
    SELECT avg_sales
    FROM average_sales
)
ORDER BY total_sales DESC;

The query can be understood as:

Calculate customer sales
        ↓
Calculate average customer sales
        ↓
Find customers above average
        ↓
Sort the results

This structure is often easier to read than using several deeply nested subqueries.

Subquery vs CTE

Subqueries and CTEs can sometimes solve the same problem, but they are structured differently.

Subquery	CTE
Nested inside another query	Defined using `WITH`
Good for simple operations	Good for multi-step operations
Can become difficult to read when deeply nested	Usually easier to organize
Usually used for a specific calculation	Can represent an intermediate dataset
Useful for filtering and comparisons	Useful for complex analysis
Does not support recursive queries	Supports recursive CTEs

For example, a simple subquery is often perfectly suitable:

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

There is no need to introduce a CTE when the logic is this simple.

However, when the query contains several stages, a CTE may provide a cleaner solution.

When Should You Use Subqueries?

Use a subquery when the logic is relatively simple and the intermediate result is only needed in one part of the query.

Common situations include:

Comparing values with AVG(), MAX(), or MIN()
Filtering with IN
Checking records with EXISTS
Finding records based on another query
Performing a small intermediate calculation

For example:

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

When should you use CTEs?

Use a CTE when your SQL query contains several logical steps or when you want to make complicated SQL easier to read.

CTEs are particularly useful for:

Complex data analysis
Reporting queries
Multiple transformations
Reusing intermediate results
Organizing long SQL queries
Recursive data structures

For example:

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)

SELECT *
FROM monthly_sales
ORDER BY month;

The CTE separates the calculation of monthly sales from the final query.

Best Practices

Keep subqueries as simple as possible. If a query becomes heavily nested and difficult to understand, consider whether a CTE or JOIN would make the logic clearer.

Give CTEs meaningful names.

Instead of:

WITH x AS (...)

use:

WITH customer_sales AS (...)

A good name makes your SQL easier for other developers and analysts to understand.

Conclusion

Subqueries and CTEs are important tools for writing more powerful SQL queries.

A subquery is a query inside another query. It is particularly useful for simple calculations, filtering, comparisons, and checking whether related records exist.

A CTE uses the WITH keyword to create a named intermediate result. CTEs are especially useful when a query involves multiple logical steps and needs to be easier to read and maintain.

A simple rule to remember is:

Use subqueries for simple nested logic and CTEs for complex, multi-step SQL queries.

Once you understand both techniques, you can write cleaner and more effective PostgreSQL queries for data analysis, reporting, dashboards, and business intelligence.

SQL JOINs: A Practical Guide to Combining Data from Multiple Tables

Melvin — Thu, 10 Sep 2026 19:40:06 +0000

Introduction

Relational databases store data in multiple tables rather than placing everything into a single large table. This approach reduces duplication, improves data organization, and makes databases easier to maintain.

However, storing information across multiple tables creates an important question:

How do we retrieve related information from different tables at the same time?

This is where SQL JOINs come in.

A SQL JOIN allows you to combine rows from two or more tables based on a related column between them.

For example, imagine an e-commerce database with these tables:

customers - contains customer information
orders - contains customer orders
products - contains product information

A customer might be stored in the customers table while their orders are stored in the orders table.

To answer a question such as:

"Show me each customer's name and the orders they have placed."

we need to combine the two tables using a JOIN.

This article explains SQL JOINs, the major types of JOINs, when to use each one, and practical examples using PostgreSQL.

- What is an SQL JOIN?

An SQL JOIN combines rows from two or more tables based on a related column between them.

Consider these two tables.

`customers`

customer_id	customer_name	city
1	John	Nairobi
2	Mary	Mombasa
3	Peter	Kisumu

`orders`

order_id	customer_id	amount
101	1	5000
102	2	7500
103	1	3000

The customer_id column connects the two tables.

For example:

customers.customer_id
        ↓
orders.customer_id

We can use this relationship to retrieve information from both tables:

SELECT
    customers.customer_name,
    orders.order_id,
    orders.amount
FROM customers
JOIN orders
    ON customers.customer_id = orders.customer_id;

Result:

customer_name	order_id	amount
John	101	5000
Mary	102	7500
John	103	3000

The JOIN allows us to combine the customer information with their corresponding orders.

- Why Are JOINs Important?

JOINs are one of the most important concepts in SQL because real-world databases are usually normalized into multiple related tables.

For example, a banking database might contain:

customers
accounts
transactions
branches
loans

A company could ask:

Which customers made transactions above KSh 100,000?

The customer information and transaction information may exist in different tables.

A JOIN allows us to bring those datasets together.

JOINs are commonly used for:

Combining customer and order information
Connecting employees to departments
Combining products and sales
Connecting transactions to customers
Creating business intelligence reports
Building dashboards
Data analysis
Data cleaning and preparation
Generating reports

- The Basic JOIN Syntax

The general syntax is:

SELECT columns
FROM table1
JOIN table2
    ON table1.common_column = table2.common_column;

For example:

SELECT
    customers.customer_name,
    orders.order_id
FROM customers
JOIN orders
    ON customers.customer_id = orders.customer_id;

The ON clause tells SQL how the tables are related.

In this example:

customers.customer_id = orders.customer_id

is the relationship between the tables.

- INNER JOIN

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

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
    ON table1.column = table2.column;

Consider:

`customers`

customer_id	name
1	John
2	Mary
3	Peter

`orders`

order_id	customer_id
101	1
102	2
103	1

Peter has no order.

Run:

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

Result:

name	order_id
John	101
Mary	102
John	103

Peter does not appear because there is no matching record in orders.

When should you use INNER JOIN?

Use an INNER JOIN when you only want records that exist in both tables.

For example:

Show customers who have placed orders.

SELECT
    c.name,
    o.order_id
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id;

This is particularly useful when analyzing transactions, sales, purchases, or other activities where you only care about records with a valid relationship.

- LEFT JOIN

A LEFT JOIN returns:

All rows from the left table
Matching rows from the right table
NULL when there is no match

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
    ON table1.column = table2.column;

For example:

SELECT
    c.name,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

Result:

name	order_id
John	101
John	103
Mary	102
Peter	NULL

Peter appears even though he has no order.

The NULL tells us that there is no matching order.

When should you use LEFT JOIN?

Use a LEFT JOIN when the records from the first table are important even if they do not have a match.

For example:

Show all customers, including customers who have never placed an order.

SELECT
    c.name,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

This is extremely useful in data analysis.

For example, you might want to identify:

Customers who have never purchased
Products that have never sold
Employees who are not assigned to projects
Branches with no transactions
Students who have not submitted assignments

- Finding Records With No Match

One of the most useful patterns with LEFT JOIN is finding records that don't have a corresponding record in another table.

For example:

Find customers who have never placed an order.

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

The LEFT JOIN keeps all customers.

The condition:

WHERE o.customer_id IS NULL

then keeps only customers who don't have a matching order.

This pattern is commonly called an anti-join pattern.

- RIGHT JOIN

A RIGHT JOIN is the opposite of a LEFT JOIN.

It returns:

All rows from the right table
Matching rows from the left table
NULL where no match exists

Example:

SELECT
    c.name,
    o.order_id
FROM customers c
RIGHT JOIN orders o
    ON c.customer_id = o.customer_id;

When should you use RIGHT JOIN?

Use a RIGHT JOIN when the table on the right side of the JOIN is the table whose every row must be preserved.

- FULL JOIN

A FULL JOIN returns all rows from both tables.

It includes:

Matching rows
Rows that exist only in the first table
Rows that exist only in the second table

Example:

SELECT
    c.name,
    o.order_id
FROM customers c
FULL OUTER JOIN orders o
    ON c.customer_id = o.customer_id;

If a customer has no order, the order columns will contain NULL.

If an order has no matching customer, the customer columns will contain NULL.

When should you use FULL JOIN?

Use a FULL OUTER JOIN when you need to compare two datasets and want to preserve everything from both sides.

It can be useful for:

Data reconciliation
Comparing two datasets
Identifying missing records

For example:

Compare customers in two different systems and identify records missing from either system.

- SELF JOIN

A SELF JOIN occurs when a table is joined to itself.

This is useful when rows within the same table have relationships with other rows in that table.

Consider an employee table:

`employees`

employee_id	employee_name	manager_id
1	James	NULL
2	Mary	1
3	Peter	1
4	Sarah	2

Here, manager_id references another employee in the same table.

We can retrieve employees and their managers:

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

Result:

employee	manager
James	NULL
Mary	James
Peter	James
Sarah	Mary

When should you use SELF JOIN?

Self joins are useful for hierarchical data such as:

Employees and managers
Categories and parent categories
Organizational structures
Referral relationships
Parent-child records

- Joining More Than Two Tables

SQL allows you to join multiple tables.

Suppose we have:

customers
orders
products

The relationships are:

customers
    |
    | customer_id
    ↓
orders
    |
    | product_id
    ↓
products

We can retrieve information from all three tables:

SELECT
    c.name,
    o.order_id,
    p.product_name,
    o.quantity
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN products p
    ON o.product_id = p.product_id;

This allows us to answer questions such as:

Which products did each customer purchase?

Result:

name	order_id	product_name	quantity
John	101	Laptop	1
Mary	102	Phone	2
John	103	Monitor	1

Multiple-table JOINs are extremely common in real-world SQL analysis.

- Using Table Aliases

When queries involve multiple tables, table names can become repetitive.

For example:

SELECT
    customers.customer_name,
    orders.order_id
FROM customers
JOIN orders
    ON customers.customer_id = orders.customer_id;

We can simplify this using aliases:

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

Here:

customers c

means we can refer to customers as c.

Similarly:

orders o

means we can refer to orders as o.

Aliases make complex SQL queries easier to read.

- Choosing the Right JOIN

A simple way to choose a JOIN is to ask:

Do I only want matching records?

Use:

INNER JOIN

Example:

Customers who have orders.

Do I want everything from my main table?

Use:

LEFT JOIN

Example:

All customers, including those without orders.

Do I want everything from the second table?

Use:

RIGHT JOIN

Example:

All orders, including those without matching customer records.

In practice, this can often be rewritten using LEFT JOIN.

Do I want everything from both tables?

Use:

FULL JOIN

Example:

Compare two datasets and identify records that exist on either side.

Does a table contain a relationship to itself?

Use:

SELF JOIN

Example:

Match employees with their managers.

- A Practical Business Example

Imagine a company has three tables.

Customers

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

Orders

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

Products

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(100),
    price NUMERIC(10, 2)
);

A business analyst might ask:

"Show the customer, order amount, and product purchased."

If orders also contains product_id, we can write:

SELECT
    c.customer_name,
    o.order_id,
    p.product_name,
    o.amount
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN products p
    ON o.product_id = p.product_id;

We have now combined three related datasets.

This is the type of SQL query commonly used when preparing data for Power BI dashboards, business reports, and analytics.

- JOINs and Data Analytics

JOINs are particularly important for data analysts.

Consider a company with:

customers
transactions
products
branches
employees

A data analyst might need to answer:

What is the total revenue by customer?
Which products generate the most revenue?
Which branch has the highest sales?
Which customers have never purchased?
What is the average transaction value?
Which employees generated the most sales?
How does revenue vary by region?

The required information may exist in several different tables.

JOINs allow the analyst to combine these tables before performing calculations.

For example:

SELECT
    b.branch_name,
    SUM(t.amount) AS total_revenue
FROM transactions t
JOIN branches b
    ON t.branch_id = b.branch_id
GROUP BY b.branch_name
ORDER BY total_revenue DESC;

This query combines transactional data with branch information and calculates revenue for each branch.

Conclusion

SQL JOINs allow us to combine related information stored across multiple tables. They are fundamental to working with relational databases and are used extensively in data analysis, reporting, business intelligence, and application development.

The most important JOINs to master first are:

INNER JOIN — when you only need matching records.
LEFT JOIN — when you need all records from your main table.
FULL JOIN — when you need records from both sides, including unmatched records.
SELF JOIN — when rows within the same table are related.

For most data analyst workflows, INNER JOIN and LEFT JOIN should be your first priority.

Once you understand how tables are related and how ON, WHERE, GROUP BY, and aggregate functions work with JOINs, you can begin writing SQL queries that answer real business questions rather than simply retrieving individual tables.

The key question to ask whenever you encounter multiple tables is:

"How are these tables related, and which records do I need to keep?"

That question will usually guide you toward the correct JOIN.

SQL Fundamentals: DDL and DML Commands with Practical Examples

Melvin — Thu, 10 Sep 2026 11:58:16 +0000

Introduction

Structured Query Language (SQL) is the standard language used to interact with relational databases. Whether you are working with PostgreSQL, MySQL, SQL Server, Oracle, or another relational database management system (RDBMS), SQL provides commands for creating databases and tables, inserting and modifying data, retrieving information, and managing database structures.

Two of the most important categories are:

DDL - Data Definition Language
DML - Data Manipulation Language

DDL is primarily concerned with the structure of a database, while DML is concerned with the data stored inside that structure.

This article explains the difference between DDL and DML and demonstrates their most important commands using practical examples.

- What Is SQL?

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

A relational database stores information in tables consisting of:

Rows - individual records
Columns - attributes or fields
Tables - collections of related records
Relationships - connections between tables

For example, a customers table might contain:

customer_id	first_name	last_name	email
1	John	Kamau	john@example.com
2	Mary	Wanjiku	mary@example.com
3	Peter	Otieno	peter@example.com

SQL allows us to create this table, add records, modify records, delete records, and retrieve information from it.

- Major Categories of SQL Commands

SQL commands can be grouped into several categories:

Category	Full Name	Main Purpose
DDL	Data Definition Language	Defines database structures
DML	Data Manipulation Language	Modifies data
DQL	Data Query Language	Retrieves data
DCL	Data Control Language	Controls permissions
TCL	Transaction Control Language	Manages transactions

Common commands include:

DDL - `CREATE` `ALTER` `DROP` `TRUNCATE`

DML - `INSERT` `UPDATE` `DELETE`

DCL - `GRANT` `REVOKE`

TCL - `COMMIT` `ROLLBACK` `SAVEPOINT`

This article focuses primarily on DDL and DML.

DDL - Data Definition Language

What Is DDL?

Data Definition Language (DDL) is a group of SQL commands used to define and modify the structure of database objects.

Database objects include:

Databases
Schemas
Tables
Columns
Constraints
Views
Indexes

DDL changes the structure of the database rather than the individual records stored inside it.

The most commonly used DDL commands are:

1. CREATE

The CREATE command is used to create new database objects.

For example, we can create a table called customers.

CREATE TABLE customers (
    customer_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

This creates a table with four columns:

customer_id
first_name
last_name
email

Adding Constraints

In real-world databases, constraints are used to maintain data integrity.

For example:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);

Here:

PRIMARY KEY uniquely identifies each customer.
NOT NULL prevents a column from containing NULL.
UNIQUE prevents duplicate email addresses.

- Creating a Table with a Foreign Key

Tables often need to be related to one another.

Suppose we have a customers table and an orders table.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),

    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

The foreign key establishes a relationship between the two tables.

The customer_id in the orders table references the customer_id in the customers table.

This allows us to represent relationships such as:

Customers
    |
    | customer_id
    |
    ↓
Orders

2. ALTER

The ALTER command is used to modify an existing database object.

For example, suppose we want to add a phone number to the customers table.

ALTER TABLE customers
ADD COLUMN phone VARCHAR(20);

The table now contains an additional column.

Rename a Column

ALTER TABLE customers
RENAME COLUMN phone TO phone_number;

Rename a Table

ALTER TABLE customers
RENAME TO customers_new;

Change a Column's Data Type

For example:

ALTER TABLE customers
ALTER COLUMN phone_number TYPE VARCHAR(30);

Add a Constraint

ALTER TABLE customers
ADD CONSTRAINT unique_phone UNIQUE (phone_number);

ALTER is especially important when database requirements change and an existing table needs to be modified without recreating it from scratch.

3. DROP

The DROP command permanently removes a database object.

For example:

DROP TABLE customers;

This removes the customers table and its structure.

The table itself no longer exists.
You can also drop a schema.

DROP SCHEMA sales;

Important Warning

DROP should be used carefully because it removes the database object itself.

For example:

DROP TABLE customers;

means that the table structure is removed.

Therefore, DROP is fundamentally different from DELETE.

4. TRUNCATE

The TRUNCATE command removes all rows from a table while keeping the table structure.

Example:

TRUNCATE TABLE customers;

After executing this command:

The table still exists.
The columns still exist.
The constraints remain according to the database system's behavior.
The rows are removed.

Consider a table containing:

customer_id	name
1	John
2	Mary
3	Peter

After:

TRUNCATE TABLE customers;

The table becomes empty:

customer_id	name

The structure remains.

- DML - Data Manipulation Language

What Is DML?

Data Manipulation Language (DML) consists of SQL commands used to manipulate the records stored in database tables.

The most common DML commands are:

INSERT
UPDATE
DELETE

DML works primarily with the data inside tables, rather than defining the tables themselves.

1. INSERT

The INSERT command adds new records to a table.

Suppose we have:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

We can insert a customer using:

INSERT INTO customers
(customer_id, first_name, last_name, email)
VALUES
(1, 'John', 'Kamau', 'john@example.com');

We can insert another customer:

INSERT INTO customers
(customer_id, first_name, last_name, email)
VALUES
(2, 'Mary', 'Wanjiku', 'mary@example.com');

The table now contains:

customer_id	first_name	last_name	email
1	John	Kamau	john@example.com
2	Mary	Wanjiku	mary@example.com

- Inserting Multiple Records

Multiple records can be inserted using a single statement.

INSERT INTO customers
(customer_id, first_name, last_name, email)
VALUES
(3, 'Peter', 'Otieno', 'peter@example.com'),
(4, 'Grace', 'Akinyi', 'grace@example.com'),
(5, 'David', 'Mwangi', 'david@example.com');

This is often more efficient than executing separate INSERT statements.

2. UPDATE

The UPDATE command modifies existing records.

Suppose John's email address has changed.

We can update it using:

UPDATE customers
SET email = 'john.kamau@example.com'
WHERE customer_id = 1;

The WHERE clause identifies the record that should be changed.

Updating Multiple Columns

You can modify several columns at once:

UPDATE customers
SET
    first_name = 'Jonathan',
    email = 'jonathan@example.com'
WHERE customer_id = 1;

- The Importance of WHERE with UPDATE

One of the most important SQL concepts is understanding the danger of running UPDATE without a WHERE clause.

For example:

UPDATE customers
SET email = 'jackson@example.com';

This updates every row in the table.

That may be intentional in some situations, but it can also be a serious mistake.

Therefore, before executing an UPDATE, always ask:

Which rows should be changed?

Then use an appropriate WHERE condition.

3. DELETE

The DELETE command removes records from a table.

For example:

DELETE FROM customers
WHERE customer_id = 5;

This removes the customer whose ID is 5.

The table itself remains.

- DELETE Without WHERE

Be extremely careful with:

DELETE FROM customers;

This removes all records from the table.

However, unlike:

DROP TABLE customers;

the table itself still exists.

This distinction is extremely important.

- DELETE vs TRUNCATE vs DROP

These three commands are commonly confused.

Consider the following:

DELETE FROM customers;

TRUNCATE TABLE customers;

DROP TABLE customers;

They have different effects.

Command	Removes Rows	Removes Structure	Typical Use
`DELETE`	Yes	No	Remove selected or all records
`TRUNCATE`	Yes, all	No	Quickly empty a table
`DROP`	Yes, with object	Yes	Remove the table itself

DELETE

DELETE FROM customers
WHERE customer_id = 5;

Removes specific records.

TRUNCATE

TRUNCATE TABLE customers;

Removes all records while retaining the table.

DROP

DROP TABLE customers;

Removes the entire table.

A simple memory trick is:

DELETE = remove data
TRUNCATE = empty the table
DROP = remove the object

- DDL vs DML

The fundamental difference between DDL and DML is what they primarily operate on.

Feature	DDL	DML
Full name	Data Definition Language	Data Manipulation Language
Main purpose	Defines database structure	Manipulates stored data
Works with	Tables, schemas, constraints, etc.	Rows/records
Common commands	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`	`INSERT`, `UPDATE`, `DELETE`
Example	Create a table	Add a customer
Focus	Structure	Data

Consider this example:

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

This is DDL because we are defining the table structure.

Now:

INSERT INTO employees
(employee_id, name, salary)
VALUES
(1, 'James', 75000);

This is DML because we are adding data to the table.

- A Practical Example

Let's build a simple employee database.

Step 1: Create the Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

This is DDL.

Step 2: Insert Data

INSERT INTO employees
(employee_id, first_name, last_name, department, salary)
VALUES
(1, 'John', 'Kamau', 'Finance', 85000),
(2, 'Mary', 'Wanjiku', 'IT', 95000),
(3, 'Peter', 'Otieno', 'Sales', 70000);

This is DML.

Step 3: Update Data

Suppose Peter receives a salary increase.

UPDATE employees
SET salary = 80000
WHERE employee_id = 3;

This is DML.

Step 4: Delete Data

Suppose employee 3 leaves the company.

DELETE FROM employees
WHERE employee_id = 3;

This is DML.

Step 5: Modify the Table Structure

Suppose we need to store employee phone numbers.

ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(20);

This is DDL.

Step 6: Empty the Table

If we want to remove all employees while retaining the table:

TRUNCATE TABLE employees;

This is commonly treated as DDL in SQL command classifications.

Step 7: Remove the Table

If the table is no longer needed:

DROP TABLE employees;

This is DDL.

- DCL - Data Control Language

DCL is concerned with database permissions and access control.

Two common commands are:

GRANT
REVOKE

For example:

GRANT SELECT ON employees TO analyst;

This grants the analyst user permission to query the table.
Permissions can also be removed:

REVOKE SELECT ON employees FROM analyst;

DCL is particularly important in environments where multiple users have different levels of access.

End-to-End Power BI Integration with PostgreSQL: Local and Aiven Cloud

Melvin — Wed, 22 Jul 2026 14:22:37 +0000

Introduction

Power BI is a business intelligence and data visualization tool that enables users to transform raw data into interactive dashboards and insightful reports.
PostgreSQL is a robust, open-source relational database management system widely used for storing and managing structured data.
Connecting Power BI to PostgreSQL allows analysts and business users to access live data, perform advanced analysis, and create data-driven visualizations for informed decision-making.

In this guide, you will learn how to connect Power BI to PostgreSQL in two common environments: a local PostgreSQL database running on your computer and an Aiven Cloud PostgreSQL database hosted in the cloud.

Establishing a Local PostgreSQL Database

The first method of connecting Power BI to a data source is by linking it to a local PostgreSQL database hosted on your computer.

Step 1: Open Power BI Desktop
Launch Power BI Desktop and select Blank report.

Step 2: Select Get Data

On the Home tab in the top-left corner of Power BI Desktop, click Get Data. This opens a window displaying the wide range of data sources that Power BI supports.

From the list of available data sources, search for or scroll to PostgreSQL Database, select it, and then click Connect to proceed.

Step 3: Enter the Database Connection Information

After selecting PostgreSQL Database, the PostgreSQL Database connection window will appear. Enter the required connection details as follows:

Server: localhost (or localhost:5432 if specifying the default PostgreSQL port) also 127.0.0.1:5432 works.
Database: postgres (replace this with the name of your PostgreSQL database)

Once you have entered the required information, click OK to proceed to the authentication step.

Step 4: Enter Your Database Credentials

After clicking OK, Power BI will prompt you to authenticate using your PostgreSQL database credentials.

Username: Your PostgreSQL username (for example, postgres)
Password: Your PostgreSQL password

Step 4: Load the Tables
Once the connection is successful, Power BI will open the Navigator window.
This window shows all the tables within the database.

The sample table is:

Kenya_Crops_Cleaned_Final

After selecting the required tables and clicking Load, Power BI imports the data from the local PostgreSQL database into the Power BI model. The loaded tables appear in the Data and Model views, where you can inspect the data, create relationships between tables and begin building reports and dashboards. This completes the data import process and prepares the dataset for analysis and visualization.

Integrating Power BI with an Aiven PostgreSQL Cloud Database.

Cloud-hosted databases provide a secure and scalable way to store and access data from anywhere.
Integrating Power BI with an Aiven PostgreSQL cloud database enables users to connect directly to cloud-based data, import tables, and create interactive reports and dashboards.
This integration supports real-time data analysis, centralized data management, and seamless business intelligence without relying on a local database.

Step 1

Open Aiven.io in your web browser and sign in to your account. If you don't already have an account, create one by selecting the Free plan and completing the registration process.
Once the service has been provisioned, ensure it is running by checking that its status is displayed as Running before proceeding.

Step 2

With the PostgreSQL service now running, the next step is to connect it to your preferred database management system (DBMS) so you can create, manage, and query your database using Aiven's connection string.

Enter the connection details provided by your Aiven PostgreSQL service into your database management system. These include the Host, Port, Database Name, and Username. Once the details have been entered, you can establish a connection to the cloud database.

Step 3

Ensure that the Aiven PostgreSQL service is running. A successful connection is typically indicated by a green check mark next to the database name in your database management system (DBMS).
Download the CA/SSL certificate provided by the Aiven service. This certificate is used to verify the server's identity and establish a secure, encrypted connection between your DBMS and the cloud database.
An SSL certificate acts as a digital credential that authenticates the server and helps protect data transmitted over the network.

Step 3.1 Installing the SSL Certificate into the Windows Certificate Store

Open the Windows search bar and type Manage computer certificates, then select the matching result to launch the Certificate Manager. Alternatively, press Win + R, type certlm.msc, and press Enter to open the Certificate Manager directly.
In the left-hand navigation pane of the Certificate Manager, locate and expand the Trusted Root Certification Authorities folder.
Right-click the Certificates subfolder under Trusted Root Certification Authorities, select All Tasks, and then click Import to launch the Certificate Import Wizard.
In the Certificate Import Wizard, click Next. When the file browser opens, change the file type filter in the bottom-right corner from X.509 Certificate to All Files (.). This allows the ca.pem file to be displayed and selected for import.
Select the ca.pem file and click Next. Accept the default certificate store location, continue through the remaining prompts, and then click Finish to complete the import. If Windows displays a security warning asking for confirmation, click Yes to proceed with installing the certificate.

Step 4: Accessing the Aiven PostgreSQL Cloud Database from Power BI

Launch Power Bi desktop and open blank report
Go to the Home tab and click Get Data. Select More, then choose PostgreSQL database from the list of available data sources.
Enter the Host and Port details provided by your Aiven PostgreSQL service into the Server field

Conclusion

Connecting Power BI to both local and cloud-based PostgreSQL databases is an essential skill for modern data professionals. While local database connections provide a convenient environment for development and learning, cloud-hosted platforms such as Aiven demonstrate how data is securely managed and accessed in real-world environments.
Mastering both local and cloud database connectivity ensures you are well-equipped to work with modern data platforms and create insightful, data-driven reports that support informed decision-making.

Data Modelling, Joins, Relationships & Types of Schemas in Power Bi.

Melvin — Sun, 21 Jun 2026 12:08:28 +0000

Introduction

In todays data-driven business environment, organizations generate and store huge amounts of data from various sources. To transform this raw data into meaningful insights, it is important to structure it effectively.
Data modelling provides a framework for representing data, defining relationships between data elements and ensuring consistency and accuracy within analytical systems.
In Power Bi, data modelling plays a critical role in enabling efficient data analysis and reporting.

Data Modelling

What is data modelling?

Data modelling is the process of organizing and connecting data tables together for analysis, visualization and reporting.
It involves creating relationships between tables, creating measures and calculated columns and structuring data in a way that makes analysis and reporting accurate.
In power Bi, a good model typically:

Uses dimension tables
Enables fast filtering and aggregation
Minimizes redundancy for perfomance and accuracy
Separates data into fact tables

A strong data model is the foundation for accurate dashboards and scalable analytics.

Joins

Joins define how two tables are combined based on a related column.

Types of joins:

Inner Join -It returns only the matching rows that exist in both the right and left tables. If a value doesn't exist in both tables, the row is thus dropped.
It is used when you only want records that are common in both tables.
Left Join - Returns all rows from the left table and matching rows from the rigt. Missing matches become null.
Right Join- Returns all rows from the right table and matches from left.
Full Outer Join - Returns all rows from both tables, matched where possible.

Relationships in Power Bi

Relationships in power Bi are the vital links that connect two or more data tables based on common columns (keys)
They dictate how filters propagate across the data model allowing you to slice and aggregate information from multiple tables into a single cohesive report without needing to merge them.

Types of relationships in Power Bi

One-to-Many(1:*)- Found when a primary table links to a secondary, larger dataset.
-one customer:many orders
Many-to-One(*:1)- It connects a dimension table with unique values to a facts table containing many instances of the same value.
-Its the most common and recommended relationship.
Many-to Many(:)- Used when both tables have repeating values.
-Requires carefull handling as it can cause ambiquity.
One-to-One(1:1)- Used when a value in either table appears exactly once. It is relatively rare.

Types of Schemas

What is a schema in power Bi?
A schema refers to the logical structure and organization of tables in a data model.
Star Schema-
A star schema is a foundational data modelling technique that organizes data into a central fact table sorrounded by dimension tables.
Key components

Fact table- its the central table in a data model. It contains numerical values that you want to anlyze and usually links to dimension tables through keys.
Dimension tables - They are tables that contain descriptive information about the business entities in the tables.
They provide contexts to the numbers stored in fact tables and are used for filtering, grouping and labeling data in reports.

Advantages

One central fact table
Multiple dimension tables
Fast query perfomance

Snowflake Schema
It is a data modelling structure where dimension tables are normalized and split into multiple, related sub-tables to save data storage space and reduce redundancy.
Advantages

Better data integrity
Reduced storage

Disadvantages

Slightly slower queries
Requires more joins

How Excel is Used in Real-World Data Analysis

Melvin — Sat, 06 Jun 2026 16:24:42 +0000

Microsoft excel is a spreadsheet program used to collect, analyze, store, calculate and visualize data efficiently.

Applications of Excel in real-world data analysis

Making data-driven business decisions: Companies usually collect data e.g sales numbers, customer behaviour. Excel allows that data to be cleaned and structured using tables,dashboards,filters and formulas. Once the data is organized, excel supports better decisions by making data easy to clean, visualize and interpret.
Financial reporting: Excel plays a critical role in financial reporting by transforming raw data into meaningful insights that helps businesses, stakeholders understand their perfomance and make data-driven decisions. It helps build financial models, create charts and dashboards used for reporting.
Marketing perfomance: Excel is used in marketing perfomance to measure results from campaigns like ads, email marketing and monitor engagement rates on social media platforms e.g facebook, instagram. Thus it's used to create charts, and create comparison on perfomance across different campaigns.

Formulas and features of Excel i've learned so far:

Example: A(product) B(region) C(sales)

SUMIF() function - it is used to add up values that meet specific condition. Example, if you do want to get the total sales of oranges only.

==SUMIF(A2:A6,"Orange",C2:C6)

COUNTIFS() function - it is used to count how many rows meet multiple conditions at the same time. Example, How many times did apple sell in Nairobi?

=COUNTIFS(A2:A7,"apple",B2:B7,"Nairobi")

Data validation - it is a feature in excel that controls what type of data can be enterd into a cell. It enables users enter only valid and accurate data. Example, if you only want to enter gender as only male and female.

Conclusion

In conclusion, learning excel has changed the way I view data because it has shifted my thinking from seeing raw and messy data to seeing cleaned, transformed data that's used in data-driven decion making.

DEV Community: Melvin

Python Variables and Data Types: A Beginner's Guide

What Is a Variable?

Variable Assignment

Naming Your Variables

What Are Data Types?

1. int - Whole Numbers

2. float - Decimal Numbers

3. str - Text

4. bool - True or False

Checking the Data Type

Printing Variables with Text

Using Commas

Using f-Strings

Putting Everything Together

Key Takeaways

SQL Subqueries & CTEs: Write Smarter, Cleaner Queries

What Is a Subquery?

Why Use Subqueries?

Subqueries with Aggregate Functions

Subqueries with IN

Subqueries with EXISTS

Subqueries in the FROM Clause

What is a CTE?

Using multiple CTEs

Subquery vs CTE

When Should You Use Subqueries?

When should you use CTEs?

Best Practices

Conclusion

SQL JOINs: A Practical Guide to Combining Data from Multiple Tables

Introduction

- What is an SQL JOIN?

customers

orders

- Why Are JOINs Important?

- The Basic JOIN Syntax

- INNER JOIN

customers

orders

When should you use INNER JOIN?

- LEFT JOIN

When should you use LEFT JOIN?

- Finding Records With No Match

- RIGHT JOIN

When should you use RIGHT JOIN?

- FULL JOIN

When should you use FULL JOIN?

- SELF JOIN

employees

When should you use SELF JOIN?

- Joining More Than Two Tables

- Using Table Aliases

- Choosing the Right JOIN

- A Practical Business Example

Customers

Orders

Products

- JOINs and Data Analytics

Conclusion

SQL Fundamentals: DDL and DML Commands with Practical Examples

Introduction

- What Is SQL?

- Major Categories of SQL Commands

DDL - CREATE ALTER DROP TRUNCATE

DML - INSERT UPDATE DELETE

DCL - GRANT REVOKE

TCL - COMMIT ROLLBACK SAVEPOINT

DDL - Data Definition Language

What Is DDL?

1. CREATE

Adding Constraints

- Creating a Table with a Foreign Key

2. ALTER

Rename a Column

Rename a Table

Change a Column's Data Type

Add a Constraint

3. DROP

Important Warning

1. `int` - Whole Numbers

2. `float` - Decimal Numbers

3. `str` - Text

4. `bool` - True or False

Subqueries with `IN`

Subqueries with `EXISTS`

Subqueries in the `FROM` Clause

`customers`

`orders`

`customers`

`orders`

`employees`

DDL - `CREATE` `ALTER` `DROP` `TRUNCATE`

DML - `INSERT` `UPDATE` `DELETE`

DCL - `GRANT` `REVOKE`

TCL - `COMMIT` `ROLLBACK` `SAVEPOINT`