DEV Community: mary kariuki

Joins In Sql

mary kariuki — Sat, 07 Mar 2026 10:02:15 +0000

Them that are joined together thou shall not put asunder!! not even through an analysis of windows function.

I actually don't know why this phrase keeps popping up in my head whenever i think of this topic, well let me indulge you;

i will give you a very simple illustration of joins in a marriage set-up this will remain etched in your brains forever.

To understand JOINS Think of two families and their relation, in two tables
Table 1 The Bride Table;

bride_id	bride_name
1	Mary
2	Anna
3	Grace

Table 2 The Grooms Table;

groom_id	bride_id	groom_name
10	1	John
11	2	Peter

We will use a join to connect this two tables using the bride_id as the Connecting Factor.

Thus

A join is used to connect two tables in a scenario where the two tables have a related column.

Let me share another simple example to make understanding even more easier.

Table 2
| name| age|
| Mary | 39 |
| john| 42 |

These two tables have |name| column as a common column.
What this means essentially is that you can connect Table 1 to Table 2 using a join.

This is how you will join in SQL

----show student_id and age from Table 1 and connect name table on matching column,

Types of Joins

Inner Join

These allow or they return only the rows that match or have matching values in both tables.
in short only the matching stuff Married couples only
see this easy example for clarity

Example 1
Table 1 The Bride Table;
| bride_id | bride_name |
| -------- | ---------- |
| 1 | Mary |
| 2 | Anna |
| 3 | Grace |

Table 2The Grooms Table;
| groom_id | bride_id | groom_name |
| -------- | -------- | ---------- |
| 10 | 1 | John |
| 11 | 2 | Peter |

Using an inner Join write the following query on*SQL*
select b. bride_name, g.groom_name
from Bride b
inner join Grooms g on b.bride_id= g bride_id;
NOTEGrace will dissappear as she is not married

Example 2

Customer Table.
| customer_id | name |
| ----------- | ----- |
| 1 | Mary |
| 2 | John |
| 3 | Sarah |
Transaction Table
| customer_id | amount |
| ----------- | ------ |
| 1 | 500 |
| 2 | 700 |
Run this query
select c.name, t.amount
from customer c
inner join transaction t on c.customer_id= t.transaction_id

left join

When joining using left join you are simply creating a query on SQL As follows
take everything from the left table match it with the right table if available if there is no match give me a null on the right tables.

see a simple example
Table 1
create table bride_right(
name varchar(20),
bride_id serial
);
insert into bride_right(name)values
('nancy_kariuki'),
('ann_kariuki'),
('margret_kariuki'),
('mary_kariuki');
create table groom(
groom_name varchar(20),
groom_id serial
);
drop table groom;
create table groom_left(
groom_name varchar(20),
groom_id serial
);
insert into groom_left(groom_name)values
('albert_kimani'),
('moses_kimani'),
('Joseph_kimani')
select
b.bride_id,
b.name as bride_name,
g.groom_name as groom_name
from
bride_right b
left join
groom_left g
on
b.bride_id = g.groom_id;

Right Join

This is the opposite of the left join.
keep all records from the right table match it with the left table if available if there is no match give me a null for the left tables.

Example
select

b.bride_id,
b.name as bride_name,
g.groom_name as groom_name
from
bride_right b
right join
groom_left g
on
b.bride_id = g.groom_id;

full outer Join

Shows all the rows from both tables.
it combines the right outer and left outer joins together.

Example
select
b.bride_id,
b.name as bride_name,
g.groom_name as groom_name
from
bride_right b
full outer join
groom_left g
on
b.bride_id = g.groom_id;

Cross Join

A CROSS JOIN creates every possible combination of rows between the two tables.
there is a slight difference when writting its querry we do not use on . Because it does not match rows based on a condition.
*Example
select
b.bride_id,
b.name as bride_name,
g.groom_name as groom_name
from
bride_right b
cross join
groom_left g

Self Join.

`This is Joining a table to itself.
For Example finding an employee who is a manager in an employee table,
the assumption is we have two tables in one table by using alias we separate the table into two.
use distinct key word
select distinct manager name(m)as manager,
from employee e
inner join employees m on e.manager_id= m.employee_id
use an inner join without conditions.

Example
select
b1.name as bride1,
b2.name as bride2
from
bride_right b1
join
bride_right b2
on b1.bride_id <> b2.bride_id;

Natural Join

automatically all tables using columns that have the same name
employee tables vs department table with a common table
select all from employees table natural join department; the department id becomes the unifying factor.

Example:
select
b.bride_id,
b.name as bride_name,
g.groom_name as groom_name
from
bride_right b
natural join
groom_left g

When choosing which join to use the most important question is which data must i not loose

Designing a Schema in Power Bi.

mary kariuki — Sun, 15 Feb 2026 20:44:41 +0000

Before we design a schema there is need to understand what a schema is:
In any relational database data is structured into Rows,columns, and tables.
These are connected to one another using Primary Or Foreign Keys.

A schema is how your tables are structured and connected inside the Model View of Microsoft Power BI.

For easier understanding lets equate schema Designing to A tailor Designing a suit. Below is summary of the process from when the tailor Receives this order to the point of delivering this suit.

Tailor.

Understands client.
Takes measurements.
Cuts fabric.
Stitches pieces.
Final fitting.

Data Modeler.

Understands business.
Analyzes data structure.
Separates fact & dimensions.
Creates relationships.
Tests measures & visuals.

For you to understand the business you need to draw a business flow chart, this will help you understand how data flow in your business.
Understand what does this business really do, what its core activity. What does the management need to measure, what are the key nounsused in the business and what are their relationships?

Secondly we must analyze data structure; this is like understanding the shape of the data before reshaping it.
These are the tables i have, they represent the following, are they transactional or descriptive, are the columns texts, dates,are they keys or numeric? If they are keys are they primary or foreign/secondary keys, does this data have problems? How much detail have you been given as well as cardinality.

Separating facts from dimensional tables entails understanding what each table means.
A fact table these table answers the questions of How Much? or How Many?

A dimensional table on the other hand answer "who?", "what?", "when?", "where?", and "why?"

If the Information is measurableand numeric put it in the fact table.
If the information is descriptive or categorical describing who?,what?,when?,why?,or where put it in the dimensional table.

Lets try this with a real Example

Given such a flat table

Open Power Bi
Load The fLat Table
Navigate to Power Queryby clicking Transform Data.
Start creating your Dimensional tables.

Create a Customer Dim table.

By :Duplicating your flat Table, Rename this to Customer Dimension keep Customer Name And Contact Columns and Remove other Columns, Remove any Duplicates And add a Column titled customer Key See Below:

Now you have your first Customer Dimensional table

Create a Product Dimension table by:

Duplicate the original flat table,Rename it to Product Dimension,InsertIndex Column And Name it As Product keys
See Below:

Your Product Dimension Table

Create a Date Dimension Table By:

Duplicateyour Original Flat Table, Keep the Datecolumn and Remove All other Columns,Remove Duplicates,AddColumns a for Date Month, Day and Year. Finally add an Index Column titled Date Keys

See Below:

Your Date Dimension Table

Now Create your Fact table By

Rename your Flat Table to Fact table
Keep The foreign Key Columns and Numeric Columns, Invoice No ,Product Name ,Customer Name ,Date ,Total Amount, Unit Price, And quantity Columns.

Finally we connect our schema using the merge Function.

please allow me to discuss this function in my next article as well as data redundancy on schemas..

Hope you find this helpful

Primary & Secondary Keys In Power Bi.

mary kariuki — Tue, 10 Feb 2026 21:31:26 +0000

When Keys are mentioned in a Power Bi context one cannot help but wonder which is the Primary or Foreign/Secondary key:

A Primary Key its like a nametag which uniquely identifies a record.
A foreign Key its a pointer to another table showing to which primary key it belong to in the other table.

Remember Power Bi is a data Modelling tool that works with structured data whose data is presented in a tabular format.

What is Tabular format
Its data presented in tables which has rows and columns.

This Tables below shows the two major Keys one in our facts table and the other in the Dimensional table.

***Facts Table :foreign Keys, or secondary keys as known form the backbone of the fact table and connects tis table to Dimension tables.

Dimension tablesA dimension table is a table that describes something — like customers, products, or dates.

The primary key in a dimension table is like a unique ID card for each row. It uniquely identifies each item in the table.

Hope you find this helpful

How Analyst translates messy data, Dax, and Dashboard into action using power Bi.

mary kariuki — Mon, 09 Feb 2026 18:38:23 +0000

Messy data is a huge business Risk it can be very misleading and if decision are made using messy data this could be very expensive and can kill a company silently.
Any skilled data analyst strife's to understand the data before touching it. They will get its meaning before any decision is taken using the data.

power bi provides a platform where messy data is cleaned through the power query through sum functions listed below:

Cleaned using the trim function to remove extra spaces.
Remove hidden characters using the Clean function.
Standardize texts using the Upper/lower Function.
Remove Duplicates.
Removing Blanks among others.

Data Modelling In Power Bi

This function is very crucial cause it helps in functions Listed below:

Giving accurate calculation.
Improving The Performance of a report.
Making an Analysis fast and easier.

Modeling Involves organizing and structuring data, here tables are organized in schemas and are joined with relationship Keys

What Is a key

It is a Particular column in a table with unique identifiers which are used to connect other Tables.

Tables are classified into two main categories:

Fact Tables
Dimension Tables

The two tables are connected into a schemas

Some of the major Schemas Widely used Includes:

Star Schema
In this type of a table the fact table is placed at the middle and is surrounded by the other dimension tables as shown below,

Snowflake
This Schema has one main table and the supporting Tables branch out like snowflakes as seen below:

Once the data has been analyzed and modelled the analyst can now use DAX to give meaning to the data.

What Is DAX

"It's just Excel math... but supercharged!" 🚀

What can DAX do

Dax adds meaning to data this is by allowing logical functions listed below:

Simple calculations: Add up sales, count customers, find averages
Time comparisons: This year vs last year, month-over-month growth
Filtering: Calculate sales for just one region or product

1.Custom metrics: Profit margins, conversion rates, customer retention.

With our data being meaningful we can now display it with the Main KPI'S On a Dashboard

What's a DashBoard in Power Bi

Normally a dashboard Should answer Question Not add more Question.
it looks like such:

Giving the most important numbers and charts that enhance the understanding of what happening to the business at a glance.

Essentials KPI`S To Be Displayed on the dash board

revenue/sales Answers the important question of whether revenue is increasing or decreasing.

Profit
This KPI Answer the question whether we are making any money from the the revenue received less all the expenses.

Growth Trend Shows the direction in which the business is moving either on the decline or improving thus informed decisions are made.

Customer The question as to who is buying from the business and what they are buying is answered by this KPI Thus the Business is able to know where to invest in.

Performance Vs Target

Every business has got its goals so with this KPI its possible to know whether we are achieving our goals.

In conclusion Power Bi is A very Key Tool in any business and should be subscribed to by data analysist When it comes to making informed decision using data.

Supercharged Excel

mary kariuki — Mon, 02 Feb 2026 21:25:54 +0000

When your data grows bigger and messier, When our excel grows tired and slower Install Power Bi They call it Excel On Steroids.
Is it Justified
Well with Power Bi

There is Zero Freeze Or Crash.
lets Handle a Few millions of rows I got you says Mr Power Power Bi.
Oozes real time data.
Better Visual.
Interactive Dashboards.
And DAX the Analytics Engine. Who Doesn't want such an experience????

Introduction to MS Excel for Data Analytics

mary kariuki — Sun, 25 Jan 2026 21:03:24 +0000

Excel is a widely used program by Microsoft.
Most data analyst can attest to how messy data can me and most use Excel to collect,organise clean data and analyze data.

if one have a dataset as shown below always begin by cleaning it through

Removing duplicates#

As shown below this makes your database less bulky and its more easy to get accurate information.

Ensuring that all columns and rows are set in accordance to what they represents if its in text,values,dates or others.

sort function ensures that the analysts can display their data set in various ways to give the intended outcomes

filter tool helps the analysist to pick what information to dispay at a particular time while hiding the rest of the data,

most common functions in excel which most analysists use includes

SUM With this function adding up data is made very easy remember some datasets entails very many rows and columns of data and it would very tedious to use a manual way to add up these data.

one can also use this formulae for summation.

###We have various Logical formulae's used for decision making they includes

IFERROR =IFERROR(Q2/P2, "Error")
Index AND matching

Vlookup

Calculating Lead Time

Organising distorted informations

Creation of Pivot tables to analyse information from datasets

Some of the analysis that one can do with the pivot tables

#Hope you find this helpful and to write more on these in future

THE ORDER IS LATE AGAIN???

mary kariuki — Sun, 25 Jan 2026 16:28:11 +0000

How many times have you lost that sale, reason? you are too busy to keep date trails on all the orders pending and their required dates.

with just an excel sheet you can calculate your lead time and sort from urgent to less urgent orders.

This how you calculate the Lead time

=IF(OrderDate>RequiredDate, "Late", DATEDIF(OrderDate, RequiredDate, "d"))

Teaching an Old Dog New Tricks.

mary kariuki — Sat, 17 Jan 2026 18:43:40 +0000

For a very long time i have considered myself as skilled data analysist who knew my spreadsheets inside out. I could literally maneuver a spreadsheet with my eyes closed. Apparently this assumption changed when i landed on LuxDevHq (https://www.luxdevhq.ai) page during my social media daily doze scroll. I found Alumnus statements and profiles of how LuxDev had moulded them into the best data analysist they were today. I knew it was time to move the challenge had been presented so why not. I enrolled myself to their first Online class and was very eager to learn my machine in a way i had never before

Am excited to share my first article on the onboarding tools that our instructors have guided us which are applicable for windows pc.

Git hub Desktop.

This An online platform that stores git project more like a file holder for git projects which keep track to all their changes.

Search Github desktop on your browser,(https://desktop.github.com/download/)

Click download for windows,
Run the installer and launch.
Sign up for a github account.
Set up using you name and your email.

Git bash

This is a tool that is used to connect your computer to git hub, here you can type commands to control git without using a mouse.
its icon looks as below:

Search for Git on your browser (https://git-scm.com/).
Click Install for windows.
Run as administrator.
Follow up the setup wizard

Some of the areas not to miss includes:

choosing the default editor as Git.

Complete Installation and launch.

Power Bi

Link(https://apps.microsoft.com/detail/9NTXR16HNW1T?hl=en-us&gl=US&ocid=pdpshare)

How to install

1.Open Microsoft store

Search for power bi desktop
Press Install
Download
then open the app for use

Python 3.12

choose*Python 3.12* as it's the Most stable version

Link(https://apps.microsoft.com/detail/9NCVDN91XZQP?hl=en-us&gl=US&ocid=pdpshare)

How to install

1.Open Microsoft store

search for python 3.12
Install
Once installed open for use

Visual Studio Code

Link(https://apps.microsoft.com/detail/XP9KHM4BK9FZ7Q?hl=en-US&gl=US&ocid=pdpshare)

DBeaver Ce

the Icon looks as below

Go to Microsoft Store
search for DBeaver Cv
Install
Upon installation, Launch.

PostgreSQL

Visit PostgreSQL download (https://www.postgresql.org/download/)
choose windows

Download the installer and choose for windows version
Upon download run as administrator.
Follow set up wizard

select the components you want to install.
set data directory.
Create a password.
set database port*5432*.
Complete Installation and launch.
Connect your postgreSQL to Your Dbeaver for easy database management as shown below:
Launch your DBeaver
on your DBeaver click on New Database Connection ,
In the database list choose PostgreSQL then click next,
On the connection details enter the details copied from your postgreSQL as shown Below

Test Connection.
If Connected Finish.

Aiven.io

On your browser search (https://aiven.io/)
Start for free.
Signup to get ana account, you can also signup with your google account.

(I hope this is helpful join me in the next Article as i explain how to to push and pull code, track changes, and understand version control using Git.)

DEV Community: mary kariuki

Joins In Sql

Them that are joined together thou shall not put asunder!! not even through an analysis of windows function.

Thus

Let me share another simple example to make understanding even more easier.

This is how you will join in SQL

Types of Joins

Inner Join

Example 2

left join

Right Join

full outer Join

Cross Join

Self Join.

Natural Join

Designing a Schema in Power Bi.

Lets try this with a real Example

Create a Customer Dim table.

Now you have your first Customer Dimensional table

Create a Product Dimension table by:

Your Product Dimension Table

Create a Date Dimension Table By:

Your Date Dimension Table

Now Create your Fact table By

Finally we connect our schema using the merge Function.

Hope you find this helpful

Primary & Secondary Keys In Power Bi.

Hope you find this helpful

How Analyst translates messy data, Dax, and Dashboard into action using power Bi.

power bi provides a platform where messy data is cleaned through the power query through sum functions listed below:

Data Modelling In Power Bi

What Is a key

What Is DAX

What's a DashBoard in Power Bi

Essentials KPI`S To Be Displayed on the dash board

Supercharged Excel

Introduction to MS Excel for Data Analytics

Removing duplicates#

Ensuring that all columns and rows are set in accordance to what they represents if its in text,values,dates or others.

sort function ensures that the analysts can display their data set in various ways to give the intended outcomes

filter tool helps the analysist to pick what information to dispay at a particular time while hiding the rest of the data,

most common functions in excel which most analysists use includes

one can also use this formulae for summation.

THE ORDER IS LATE AGAIN???

This how you calculate the Lead time

Teaching an Old Dog New Tricks.

Git hub Desktop.

Git bash

Power Bi

How to install

Python 3.12

How to install

Visual Studio Code

DBeaver Ce

PostgreSQL

Aiven.io