DEV Community: rapidlashes

SQL Operations in a nutshell :

rapidlashes — Thu, 17 Sep 2026 11:35:59 +0000

Congratulations, we now have worked on retrieving data. Next step is now how to work on that data to produce ready to use output. In this article we will basically cover how to transform, calculate and reshape data directly inside a querry rather than pulling those raw values and working with them on a separate platform.
We have different SQL Operations: Arithmetic, string, date, conditional and Aggregate Operations.

Arithmetic Operations

SQL supports standard mathematical operations in a select statement
syntax:

SELECT product_name, price, quantity, price * quantity AS line_total
FROM order_items;

line_total is a column we have made in our select query to find the total price of each item ordered.
Arithmetic operations are also used for :

Applying discounts or tax - price * 0.16
computing differences - Revenue - cost_price

syntax:

SELECT product_name, price, price * 0.9 AS discounted_price
FROM products;

String Operations

String operations clean up, combine, or reformat text data.
common string functions:

UPPER() / LOWER() — change cases
TRIM() — remove leading/trailing whitespace
LENGTH() — get the number of characters
SUBSTRING() — extract part of a string
REPLACE() — swap out text
initcap() - Changes the cases of the begining of every word to upper case. In another language is putting words into proper cases.

syntax:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

syntax:

update tembo.tembo_staging
set guest_name = initcap(trim(guest_name))
where guest_name != initcap(trim(guest_name));

What the above code does:
It is updating the table tembo_staging specifically the column guest_name by telling SQL to look through the values of that column, find all those values that are not trimmed and properly cased and trim them first before properly casing them hence the trim() inside the inticap() function.

Aggregate Operations

Aggregate functions collapse many rows into a single summary value, and are almost always paired with GROUP BY.
Core aggregate functions:

COUNT() — number of rows
SUM() — total of a numeric column
AVG() — average value
MIN() / MAX() — smallest/largest value

syntax:

SELECT product_category, AVG(price) AS avg_price, COUNT(*) AS total_products
FROM products
GROUP BY product_category;

Conditional Operations with CASE WHEN

CASE lets you build if/else logic directly into a query, turning raw values into categories, labels, or flags without needing a separate processing step.
syntax:

SELECT order_id, total,
  CASE
    WHEN total > 500 THEN 'Large'
    WHEN total > 100 THEN 'Medium'
    ELSE 'Small'
  END AS order_size
FROM orders;

order_size will be a column on it's own, that categorises the orders by the total price.

We can also use a case when statement inside aggregates for conditional counting or summing eg,
syntax:

SELECT
  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_orders,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders
FROM orders;

cancelled_orders and shipped_orders are columns of their own derived from the orders table.The values inside are the number of orders that were cancelled and shipped respectively.

Date Operations

Dates come with their own set of operations, since they need to be compared, calculated on, and formatted differently from plain numbers or text.
syntax:

SELECT order_id, order_date, CURRENT_DATE - order_date AS days_since_order
FROM orders;

The values i the column days_since_order will be in days

syntax:

SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

INTERVAL on it's own is a date function. Here we are filtering by recent activity.

syntax:

SELECT order_id, EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

EXTRACT is also a date function used to extracting parts of a date eg months, days or years.

syntax:

SELECT TO_CHAR(order_date, 'YYYY-MM') AS order_month
FROM orders;

TO_CHAR converts the date value to a readable text string/ text , however you like to call it.

NOTE: Some date functions differe for different databases. The above examples are for Postgres Database. You will need to check more on the same once we move past the basics.

Combining them all

In a real data set you will mostly find yourself in a postion where you will have to combine all these functions into one querry to answer a certain business question.
syntax:

SELECT
  TO_CHAR(order_date, 'YYYY-MM') AS month,
  COUNT(*) AS total_orders,
  SUM(total) AS total_revenue,
  ROUND(AVG(total), 2) AS avg_order_value
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

In layman's language, im trying to find the total number of orders and total revenue for each month, but I want my month column to be in the format 2024 March for instance, and I want the table to be ordered from the earliest date to the latest.

To summarise...

You move from just retrieving data to generating insights from that data, that is the whole point of SQL operations.

Once comfortable with that, you can move to the next step which are subquerries and CTE's then to window functions.

Next step:Filtering and joins

rapidlashes — Mon, 14 Sep 2026 14:12:30 +0000

Once you are comfortable with basic select statements, your next step is now combining data using joins and narrowing it down(fitering) to exactly what you need using statements like order by , group by and the sort.

JOINS

You can have multiple tables in your database eg Customers table and orders table both from the same parent data. Now customers table holds customers info while orders table hold orders info from your parent data. A data analyst can pull related data between the two tables using the joins operations in an sql querry.

NOTE: for this to happen , there has to be a common column between the two tables. We'll dive deep into that when we get to the syntax part.

Now there are 5 major joins in sql:

1. INNER JOIN
Returns only rows that have a match in both tables.

syntax:

select c.name, o.product_id, o.quantity 
from duka.duka_customers c
inner join duka.duka_orders o
on c.customer_id = o.customer_id;

We have 2 tables sharing a common column customer_id. The column name comes from the table duka_customers.
You can tell that from the alias c, while product_id and quantity come from the table duka_orders.
An Alias is like a pointer here to show you which column comes from which table.

2. LEFT JOIN(LEFT OUTER JOIN)
Returns all rows from the left table plus matching data from the right table. Rows with no match get NULL for the right table's columns.
syntax:

select p.product_name, o.order_id 
from duka.duka_products p
left join duka.duka_orders o on p.product_id = o.product_id;

From the syntax above , we are basically trying to see all the products from the table duka_products along side their order_ids from the table duka_orders.

3. RIGHT JOIN(RIGHT OUTER JOIN)
The mirror image of a LEFT JOIN — returns all rows from the right table, with matches from the left table where available.

4. FULL OUTER JOIN
Returns all rows from both tables, matching where possible and filling with NULL where there's no match on either side. Useful for spotting mismatches between two datasets.

5. SELF JOIN
A table joined to itself — common for hierarchical data, like employees and their managers stored in the same table.

This is the original table.

syntax:

select
e.employee_name as employee,
m.employee_name as manager
from public.Company e
left join public.Company m on e.manager_id = m.employee_id;

This is the table after running the self join querry above.

FILTERING

A where clause comes a long way when you want to narrow down your results. Let's take a look at this syntax we had used earlier.

select p.product_name, o.order_id 
from duka.duka_products p
left join duka.duka_orders o on p.product_id = o.product_id
where o.order_id is NULL;

Without the where clause, all product names will be returned alongside their order_ids, but after the where clause, only the products whose order_ids are NULLS will be returned.

NOTE: notice how we have used is NULL instead of = NULL
to identify NULLS. A simple explanation is, NULL is not a value, it is a place holder to show you that a certain value is missing.

Common filtering tools:

AND / OR — combine multiple conditions
NOT — negate a condition
IN — match against a list of values
BETWEEN — match a range
LIKE — pattern matching with wildcards (% for any characters, _ for a single character)
IS NULL / IS NOT NULL — check for missing values

syntax:

SELECT *
FROM customers
WHERE country IN ('Kenya', 'Nigeria', 'Ghana')
  AND email IS NOT NULL;

HAVING — filtering after aggregation

WHERE can't filter on aggregate results (like a COUNT or SUM), because those values don't exist until after grouping. That's where HAVING comes in:
syntax:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

This returns customers with more than 5 orders. You cant say where count(*) > 5.

To conclude...
This is should be your Step no. 2 in learning SQL after understanding the basics including the command categories. The Next part will now be Operations to help you transform and calculate the data.

NO idea where to start learning SQL?

rapidlashes — Sun, 13 Sep 2026 19:30:18 +0000

This article is a beginner friendly guide that gets you started into learning SQL as a programming language.

Introduction

Relational databases store data in tabular form, often referred to as structured data. The alternative is Unrelational databases. Now SQL is simply the programming language that enables you to do so. SQL stands for Structured Querry Language

Basics

To understand a Structured Querry Language , these 4 concepts should come in mind:

Tables - data organized into rows and columns, similar to a spreadsheet eg Customer's table
rows - a single record in a table eg a specific name,'Ahmed'
columns - a single attribute or field eg customer_name
Databases - A Database is like a house that stores the organised data electronically making it easier and safer to find, retrieve and modify the data.This is usually done by the help of a software.
Schemas - The data in our database is grouped in schemas.
Think of it as a cabinet that holds your data in tables, views and rules acting as a blueprint on how your data will be organised.

To illustrate more , check the image below:

The icons are SQL databases and Dbeaver is the software .

tembo and public are schemas, while tembo_production and tembo_staging are tables inside the schema tembo.

SQL command categories:

DDL - Data Definition Language Defines and modifies the structure of the database itself: tables, schemas, and constraints.

create - building a new table or a database
alter - changing an existing table's structure
drop - deleting a table or databbase entirely
truncate - deleting the content inside a table but maintainging the structure

A data engineer setting up a new pipeline will lean on DDL to create the tables that will hold incoming data, and adjust them with ALTER as requirements change.

DQL - Data Querry Language
Is used to retrieve data without changing it. Common key word is select.
A data analyst spends most of their day writing DQL (SELECT) queries — pulling metrics, building reports, and answering ad hoc business questions.
TCL - Transaction Control Language
Manages groups of changes as a single unit, ensuring consistency of data

commit - save all changes made in a transaction
rollback - undo changes made in a transaction
savepoint - set a point within a transaction to roll back to, without undoing everything

Now a common question will be what is a transaction.
Well a transaction is like a single unit of work made up of one or more operations eg deleting, updating, writting data that either completes or fails.

Think of it as an all or nothing rule. All steps work, changes are saved but if a single step doesnt, all previous changes are undone.

In systems handling money or critical records, TCL ensures that a group of related changes either all happen or none do. For instance, transferring funds between two bank accounts involves debiting one and crediting another — COMMIT only fires once both steps succeed, and a failure triggers a rollback so the data never ends up half-updated.

DML - Data Manipulation Language Used for working with the data inside a table.

insert - adding new rows
delete - removing rows
update - modifying exisiting rows

An application connected to the database uses DML constantly in the background: every time a user signs up, places an order, or updates their profile, the app runs INSERT, UPDATE, or DELETE statements.

5.DCL - Data Control Language
Basically manages who can do what.

grant - gives a user permission to perform a certain action
revoke - takes the permission away

A database administrator (DBA) uses DCL to control who has access to sensitive tables — for example, granting an analyst read-only access while restricting DELETE permissions to a small team.

Getting started on queries

The select statement is where all sql work begins.

select first_name, last_name
from customers
where county = 'Mombasa'
order by age desc;

first_name, last_name, county, age are all columns in our table.
The table name is called customers, while 'Mombasa' is a value in the column county.

The example above is a simple sql querry just to get you started as a beginner, once you have mastered these concepts, next thing you will dive into is filtering and operations , and joins.

Connecting Power BI to a Database: A Complete Guide

rapidlashes — Wed, 08 Jul 2026 18:04:28 +0000

Introduction

You are data analyst in an organisation working on a large volume of data. Your company stores that data in a postgres SQL database for instance and you need to access that data and analyze it using the tool of your choice like Power BI, Tableau, Looker Studio etc, then you have to find a way to connect that visualization tool to the database to access that data.

Your data can be stored in a local database server or in a cloud managed platfrom like Aiven. Whichever the case, the guide below shows you how to connect to both.

Prerequsites

Before connecting your power BI to these databases ensure you have;

An Active Aiven account
A running service in Aiven, in our case Postgres SQL database service
A running postgres SQL database hosted locally
Power BI installed in your desktop

1. Connecting power BI to a local postgres SQL database

The process is straight forward, but first , you will need a tool like Dbeaver, a software that enables you to store and organise your data and also allows you to connect to not only one but multiple databases.

Step 1-open your dbeaver
press Cntrl+shift+N to open a new database connection in our case Postgres SQL

Step 2-Input your connection password and press okay , you can test the connection to see if it is valid, then press okay

Step 3-Right click on your connection name, and on the drop down click connect, make sure your connection name has a green tick

Once you have established that connection, you can now manually add your data from your machine to the database
Step 1-go to your database

Step 2-Import data

Step 3- choose the type of data

Step 4-Browse from your local files

Now launch your power BI , got to home , click Get Data , go to databases and choose postgres SQL database.

You will then be prompted to enter your connection details for the local postgres SQL you just created

click connect, from your navigator page, you can now select the files you want to work on directly from your database.

2. Connecting Power BI to a cloud-based postgres database in Aiven

Unlike a local PostgreSQL installation, Aiven databases are hosted securely in the cloud. Every connection is encrypted using SSL.

Why SSL matters

When Power BI connects to an Aiven PostgreSQL database, your data travels across the internet.

Without SSL:

Data could potentially be intercepted.
Credentials might be exposed.
Connections are vulnerable to man-in-the-middle attacks.

With SSL enabled:

Data is encrypted.
Passwords remain protected.
Power BI verifies that it is communicating with the legitimate Aiven server.

Downloading and configuring the SSL certificate

Step 1-Open your Aiven Postgres SQL service, make sure it's running

Step 2- download the CA certificate

Step 3- open windows, go to manage user certificates, Trusted Root certification

Step 4- Right click on the drop down certifcates, all tasks, then import data. You will then browse from your local files where your certificate is stored

Connecting to your power BI
The process is more or less the same with the local Postgres SQL. Your connection details are in your Aiven postgres SQL service.The server box details are (hostname:port) while the rest of the details are input directly.

Conclusion

Connecting Power BI to PostgreSQL is a straightforward process whether your database is hosted locally or in the cloud
The key distinction when connecting to Aiven is the use of SSL/TLS encryption.

POWER BI RELATIONSHIPS, SCHEMAS AND JOINS.

rapidlashes — Thu, 02 Jul 2026 14:51:37 +0000

Understanding the concepts

Relationships, schemas and joins in power BI are key concepts in understanding data structures in power BI

relationships

Are basically connections between tables in power BI that shows how a data in one table is related to a data in another table.

cardinality:

one to one -each value appears once in the two tables eg;

one to many-one value in one table appears many times in another table

this basically means Fatma has placed multiple orders

many to many- multiple values in one table can appear multiple times in another table. A simplified example is a data table for students performance in a school where we find that one student can take many subjects and also one subject can be taken by multiple students.

Primary keys and Foreign keys

A primary keys is a column that acts as a unique identifier in a table while a foreign key is a column that basically referrences a primary key in another table.
From the above table explaining the one to many relationship of a schema, we can conclude that the customerID column is a primary key in the customer's table while the same column acts as a foreign key in the Order table.

Schemas/data structure/data blueprint

This is a model/ structure in which your data sits on. As you feed more data into your table, a schema is how your data will be arranged in your .pbix file.
To understand this concept, you have to first understand the two types of tables involved in your .pbix file namely;

dimensional tables
fact tables

For instance you are working on a table of raw data containing thousands of rows. As a data analyst it is your duty to prepare the data in such a way that it becomes easier to analyse , understand and interpret. One large table can be broken down into multiple tables that are interconnected with each other.
Dimensional tables will contain primary keys while a fact table will house those primary keys as foreign keys in it.
A good example is on the above tow tables explaining the one to many relationships. We can conclude that the Customer's table is a dimensional table while the Order table is a fact table.
There are 2 types of schemas:

Star schema- has one fact table and several dimensional tables

snowflake schema- has one fact table, several dimensional tables and sub dimensional tables

Joins

This is a function in your power BI platfrom that now you will use to merge these dimensional and subdimensional tables into one large meaningfull flat table.It is used in power query when transforming your data.
There are types of joins:

Full outer join- combines all values from both tables
right outer join-combines all values from the second table and matching values from the first table
left outer join-combines all values from the first table and matching values from the second table.(note: the arrangement of the table depends entirely on the analyst)

-** inner join**-combines only matching values from the columns of the two tables(dimensional and fact tables)

Antijoins

left Antijoin will only select rows that are in the first table only and are missing in the second table
Right Antijoin only selects rows in the second table alone and are missing in the first

Key take aways:

Schemas are used to break down tables
Joins are used to merge your tables
Antijoins cant be used to merge tables
Foreign keys are in fact tables, in their dimensional tables they are primary keys
Primary keys are unique in their tables

Why Excel....

rapidlashes — Sat, 06 Jun 2026 17:04:28 +0000

What is excel

Let's say for instance you are running a garage business and you have been spending a lot of money in operations which does not tally the income cash flow from the business, and you are looking to see which department uses a large percentage of the daily operational cost of the business.

You obviously need a tool to analyse that cost, from money used in transportation to money spent in procurement of spare parts and garage work tools.

Excel with its features can give you the best platform to analyse that cost enabling you to make decisions that will help you cut down on expenses while still maximising profits.

What makes it stand out is , its a tool you can use for free and by far the easiest one to achieve mastery.

definitions

Excel is basically a tool provided by Microsoft to help you analyse and store data. The data can be in numeric form or text form or even Pictorial form in some cases.

Applications

Now from the above introduction we have seen one way we can use excel spreadsheets, by analysing a company's operational cost in comparison to the company's income cash flow.

Another instance we can use an excel spreadsheet still in a business set up is analysing which products sale the most.
In a business like running a spare shop for instance, you end up selling a variety of spare parts but not all products sale at the same rate.
Excel will help you identify fast moving products from the slow moving ones and the dead stocks therefore enabling you to make the best decisions before procuring spare parts.

Another application where excel can be essential for use is analysing an employee's work performance and using that data to handle payroll. One can analyse aggregates like hours of work, hire date, years of experience and project completion time to derive rational decisions on whether to increase or decrease an employee's salary.

Features

Excel has a wide variety of features and functions that enable the user to work efficiently. Tools like conditional formatting allow one to point out and highlight a specific cell or cells that satisfy certain conditions like for instance duplicate values or text.

There are also a wide variety of numeric functions that can help one find the Sum, Mode or Average of a numeric data set.Such functions are useful in accounting.

Another feature which is a basic one but very essential is the one that allows you to convert any type of data in a certain cell to a format of your liking, for instance, a numeric data can be converted to a currency of one's choice.

In conclusion...
When youre trying to find simplicity and efficiency all in one entity, excel is what you get.