DEV Community: Gideon Kiprono

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.