DEV Community: rapidlashes

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.

To illustrate more , check the image below:

The icons are SQL databases and Dbeaver is the software .

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.