Forem: Gathuru_M

SQL Joins Explained: Case Example

Gathuru_M — Mon, 02 Mar 2026 18:43:59 +0000

Structured Query Language(SQL) is a computer language for storing, manipulating, and retrieving data stored in a relational database.

SQL Joins are like clauses used to combine records from two or more tables in a database, just as the name(join) means.

In this article, you will learn, using a case example, the different types of joins, when, and how they are used. Be sure to check for “bonus joins” included at the end of the article.

Example Data

We will use data from these 2 tables to show various ways to display employees from John Smith's company.

Departments Table

Department_Id	Department_Name
1	Executive
2	HR
3	Sales
4	Support
5	Sales
6	Research

Employees Table

Employee_Id	Full_Name	Department_Id	Job_Role	Manager_Id
1	John Smith	1	CEO	Null
2	Sarah Goodes	1	CFO	1
3	Wayne Ablett	1	CIO	1
4	Michelle Carey	2	HR Manager	1
5	Chris Matthews	3	Sales Manager	2
6	Andrew Judd	4	Development Manager	3
7	Danielle McLeod	5	Support Manager	3
8	Matthew Swan	2	HR Representative	4
9	Stephanie Richardson	2	Salesperson	5
10	Tony Grant	3	Salesperson	5
11	Jenna Lockett	4	Front-End Developer	6
12	Michael Dunstall	4	Back-End Developer	6
13	Jane Voss	4	Back-End Developer	6
14	Anthony Hird	Null	Support	7
15	Natalie Rocca	5	Support	7

The code below shows the syntax for writing a JOIN:

SELECT columns
FROM table1
JOIN table2 ON table1.column1=table2.column1

Types of Joins

There are four major types of joins in SQL, as listed below:

Inner Join
Left Join
Right Join
Full Join

Inner Join

It creates a result table that displays information common between two tables based on a shared piece of information.

It is the most important and frequently used of the joins.

In this case, we could use it to display employees from the departments listed in the first table.

SELECT employees.Full_Name, employees.Job_Role, departments.Department_Name
FROM departments
INNER JOIN employees ON departments.Department_Id = employees.Department_Id
 -- You can replace the keyword INNER JOIN with JOIN

You should get the results below

Result

Full_Name	Job_Role	Department_Name
John Smith	CEO	Executive
Sarah Goodes	CFO	Executive
Wayne Ablett	CIO	Executive
Michelle Carey	HR Manager	HR
Chris Matthews	Sales Manager	Sales
Andrew Judd	Development Manager	Development
Danielle McLeod	Support Manager	Support
Matthew Swan	HR Representative	HR
Stephanie Richardson	Salesperson	HR
Tony Grant	Salesperson	Sales
Jenna Lockett	Front-End Developer	Development
Michael Dunstall	Back-End Developer	Development
Jane Voss	Back-End Developer	Development
Natalie Rocca	Support	Support

Left Join

A LEFT JOIN returns all rows from the left table, plus matched values from the right table or NULL in case of no match.

Note: The left table refers to the table that appears before the "LEFT JOIN" keywords in your SQL query. Same case applies when using RIGHT JOIN

We could use left join to retrieve a list of all employees along with their department names. If an employee doesn't belong to a department, display NULL

SELECT employees.Full_Name, departments.Department_Name
FROM employees 
LEFT JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Full_Name ASC

You should get the results below

Result

Employee_Id	Full_Name	Department_Name
1	John Smith	Executive
2	Sarah Goodes	Executive
3	Wayne Ablett	Executive
4	Michelle Carey	HR
5	Chris Matthews	SALES
6	Andrew Judd	Development
7	Danielle McLeod	Support
8	Matthew Swan	HR
9	Stephanie Richardson	HR
10	Tony Grant	SALES
11	Jenna Lockett	Development
12	Michael Dunstall	Development
13	Jane Voss	Development
14	Anthony Hird	NULL
15	Natalie Rocca	Support

You can use the COALESCE() function to replace NULL values with more relatable user-defined values.

i.e. If an employee doesn't belong to a department, display "No Department" instead.

SELECT employees.Employee_Id, employees.Full_Name, 
COALESCE(departments.Department_Name, "No Department") AS Department_Name
FROM employees 
LEFT JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Full_Name ASC

You should get the results below

Result

Employee_Id	Full_Name	Department_Name
1	John Smith	Executive
2	Sarah Goodes	Executive
3	Wayne Ablett	Executive
4	Michelle Carey	HR
5	Chris Matthews	SALES
6	Andrew Judd	Development
7	Danielle McLeod	Support
8	Matthew Swan	HR
9	Stephanie Richardson	HR
10	Tony Grant	SALES
11	Jenna Lockett	Development
12	Michael Dunstall	Development
13	Jane Voss	Development
14	Anthony Hird	No Department
15	Natalie Rocca	Support

Right Join

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches
in the left table. Not so different from the LEFT JOIN.

In this case, we could view employees and their various departments

SELECT Department_Name,
COALESCE (employees.Full_Name, "No Employee") AS Full_Name 
FROM employees 
RIGHT JOIN departments ON departments.Department_Id = employees.Department_Id;

You should get the results below

Result

Department_Name	Full_Name
Executive	Wayne Ablett
Executive	Sarah Goodes
Executive	John Smith
HR	Stephanie Richardson
HR	Matthew Swan
HR	Michelle Carey
Sales	Tony Grant
Sales	Chris Matthews
Development	Jane Voss
Development	Michael Dunstall
Development	Jenna Lockett
Development	Andrew Judd
Support	Natalie Rocca
Support	Danielle McLeod
Research	No Employee

Full Join

The SQL FULL JOIN combines the results of both left and right outer joins.

The joined table will contain all records from both tables and fill in NULLs for missing
matches on either side.

SELECT employees.Employee_Id, employees.Full_Name, employees.Job_Role, 
departments.Department_Id, departments.Department_Name
FROM employees 
FULL JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Employee_Id ASC

You should get the results below

Result

EMPLOYEE_ID	FULL_NAME	JOB_ROLE	DEPARTMENT_ID	DEPARTMENT_NAME
1	John Smith	CEO	1	Executive
2	Sarah Goodes	CFO	1	Executive
3	Wayne Ablett	CIO	1	Executive
4	Michelle Carey	HR Manager	2	HR
5	Chris Matthews	Sales Manager	3	Sales
6	Andrew Judd	Development Manager	4	Development
7	Danielle McLeod	Support Manager	5	Support
8	Matthew Swan	HR Representative	2	HR
9	Stephanie Richardson	Salesperson	2	HR
10	Tony Grant	Salesperson	3	Sales
11	Jenna Lockett	Front-End Developer	4	Development
12	Michael Dunstall	Back-End Developer	4	Development
13	Jane Voss	Back-End Developer	4	Development
14	Anthony Hird	Support	NULL	NULL
15	Natalie Rocca	Support	5	Support
NULL	NULL	NULL	6	Research

Bonus Joins

Self Join

A SELF JOIN is typically not a join type but a special way of joining a table to itself. You may want to combine rows in a table based on a related column present in the table.

It is commonly used when you need to traverse a hierarchical structure where each row references another row in the same table

When comparing data within rows in the same table.

In this case, we will use self-join in the employees’ table to display the employee-manager relationship. Each employee record contains a reference to the manager's ID, allowing us to retrieve more information about the managers by adding another column, “Manager_Name”. In Data Science, this is an example of feature engineering.

SELECT
e.Employee_Id,
e.Full_Name,
e.Job_Role,
m.Employee_Id AS Manager_Id,
m.Full_Name AS Manager_Name
FROM employees e, employees m 
WHERE e.Manager_Id = m.Employee_Id;

You should get the results below

Result

EMPLOYEE_ID	FULL_NAME	JOB_ROLE	DEPARTMENT_ID	MANAGER_NAME
2	Sarah Goodes	CFO	1	John Smith
3	Wayne Ablett	CIO	1	John Smith
4	Michelle Carey	HR Manager	1	John Smith
5	Chris Matthews	Sales Manager	2	Sarah Goodes
6	Andrew Judd	Development Manager	3	Wayne Ablett
7	Danielle McLeod	Support Manager	3	Wayne Ablett
8	Matthew Swan	HR Representative	4	Michelle Carey
9	Stephanie Richardson	Salesperson	5	Chris Matthews
10	Tony Grant	Salesperson	5	Chris Matthews
11	Jenna Lockett	Front-End Developer	6	Andrew Judd
12	Michael Dunstall	Back-End Developer	6	Andrew Judd
13	Jane Voss	Back-End Developer	6	Andrew Judd
14	Anthony Hird	Support	7	Danielle McLeod
15	Natalie Rocca	Support	7	Danielle McLeod

That's most of what you need to know about SQL Joins. Hopefully, you found this information helpful. Feel free to share it with anyone who's having a hard time with Joins, and keep learning! 😊

If you have any questions, please leave them in the comments section below.

Thanks for reading!

Turning Messy Data into Business Action

Gathuru_M — Mon, 09 Feb 2026 02:07:28 +0000

How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power BI

As a data analyst, whether in the corporate world or working on a personal project, you will find that data analysis is often driven by the need to solve a problem or wanting an accurate, insightful view of data to make a better decision.

1. Define the Objective

First, we need to come up with the objective of our analysis.

Ask Questions: Write down the specific questions you want answers to from the analysis.
Theme Your Dashboard: If you need a dashboard, design one that maintains a clear theme. Don’t just show every kind of data to the user—focus on what matters.

2. Gathering the Data

The data we need is often scattered. You might need to:

Scrape it online.
Use data from survey results.
Combine several Excel sheets.
Fetch data from a database.

So, how do analysts translate this Messy Data into action?

Power BI allows you to access data from various sources, load it, transform it, and analyze it—all in the same workspace.

In Power BI, we follow the ELT approach (Extract, Load, Transform).

The ELT Process

Extract and Load

First, we start by Extracting the required data.
When you launch Power BI, you get a prompt to select a data source. Simply select where your data lies and import it into Power BI by Loading it there.

Transform

Once all your data is loaded, the next step is Cleaning. You want to remove all inconsistencies in your data by ensuring:

Correct Data Types: All fields (Dates, Decimals, Text) must be set correctly.
Handle Missing Values: Deal with nulls or blanks by either removing rows or filling them where appropriate.
Splitting/Merging: Split or merge columns where necessary (e.g., splitting an "Address" column to get separate "Location" and "Street" fields).
Standardization: Ensure consistency (e.g., making sure all currency data is in either Dollars or Shillings across the entire dataset).

Data Modelling: The Integration Step

After the data is clean and transformed, the best way to integrate it is through Data Modelling.

As discussed in my previous article Link , data modelling helps you create a clear structure and establish necessary relationships before you begin any plotting or calculations. This involves creating a proper Star Schema, consisting of a Fact table and Dimension tables.

Note: Always make sure to click "Close & Apply" to save all changes made in the Power Query Editor before you start building your reports.

DAX

Once your data model is set up, you might find that the raw data doesn't provide all the answers immediately. This is where DAX (Data Analysis Expressions) comes in.
DAX is the formula language of Power BI, allowing us to calculate and generate new information in two primary ways:

Measures: Use DAX to create summary aggregations. These calculate values on the fly, such as Total Sales, Year-over-Year Growth, Profit Margin, or Average Selling Price.
Calculated Columns: Generate new columns within your tables to provide more granular information. For example, you could create a "Profit Status" column that labels each row as "Profitable" or "Loss" based on a calculation.

DAX respects your model’s relationships. When you create a measure and plot it in a visual, it automatically reacts to the "Filter Context." This means the numbers will automatically filter and update based on the Dimensions (such as Date, Category, or Location) you use in your report.

Visualizing for Answers

A well-modeled dataset allows us to answer any question we might have. The best way to do this is to plot Visuals. Why?

Spot Patterns Fast: Visuals help you and your audience see trends or patterns immediately.
Tell a Story: After answering your initial questions, creating a dashboard tells a story about the problem you want to solve.

A great dashboard should:

Describe the problem clearly.
Illustrate insights found in the data.
Suggest a potential solution to the problem.

By following this flow, your analysis remains relevant and provides genuine value, rather than just showing the dashboard user what they already know.

How to Implement Data Modelling in Power BI

Gathuru_M — Mon, 02 Feb 2026 21:25:14 +0000

In this article we will explore the fundamental concepts of data modelling and specifically how to implement data modelling within Power BI for effective data analysis.

Data Modelling is simply structuring or setting up your data in tables, relationships, and access to data for analysis scenarios.

Often you'll find data distributed in several sheets or systems since, that is how it's maintained. If lucky your data could be in a neat setup (Data Warehouse) where most of the structuring has already been done.

Case Scenario:

Here is some sample data, to help make sense of this concept:
Sample Chocolate Sales Data

Understanding the Tables

In our case, we have 5 tables.

Fact Table: One table that contains pointers to other tables, but has no specific information. In our case, the shipments table.
Dimension Tables: Each explains one dimension of data. What is happening from the perspective of each dimension/entity. These will be the rest of our tables.
Calendar Table: A special Dimension table found in most data that holds the time component of analysis, i.e., [Timeseries, Forecasting or Trend analysis].

Doing this in Power BI

Load Data: Load the data in Power BI by selecting Excel Workbook as the data source.
Automatic Modelling: Power BI automatically tries to match the tables and create a model; you can see this in the Model View.
Manual Relationship Setup: 🤔 However, not all the data was modelled automatically. To finish up, drag the 'Shipdate' field from the Shipment table onto 'cal_date' in the Calendar table to create a new relationship and complete the look.

Note: Notice that the schema now looks Star-shaped. This kind of model with one fact table and multiple dimension tables is called a Star Schema. The process of setting up this structure is Data Modelling.
Manage Relationships: You can view and edit all connections by clicking on Manage Relationships. This menu shows the relationship status, specifically whether they are Active or Inactive.

Table Characteristics

Relationship Design: The Products table shows each product in a row with more information from the Shipments table indicating a Many to One Relationship.
Fact Table Design: The shipments table has more records as compared to the dimension tables in our data.

Therefore, Fact tables are engineered to host many records and may also have fewer columns as compared to dimension tables.

Conclusion

Effective data modelling is the backbone of any powerful analysis. By transforming messy, multi-sheet data into a structured Star Schema, everything will feel fast and intuitive; when it's wrong, you spend more time fixing "broken" numbers than actually analyzing data.

Introduction to Linux for Data Engineers, Including Practical Use of Vi and Nano with Examples

Gathuru_M — Mon, 26 Jan 2026 05:08:41 +0000

Why Every Data Engineer Needs to Make Friends with Linux

Diving into Data Engineering, you’re probably well into writing some Python scripts or SQL queries. But there's mentions of "The Server" or "Production," and suddenly everyone is talking about Linux.

If you’re wondering why you can’t just keep using Windows or macOS for everything, you’re not alone. Let’s break down why Linux.

If you look at regular people browsing the web, about 70-75% of them use Windows. It’s comfortable, right? But in the "under-the-hood" world, where we have servers that actually run the internet and process massive datasets, Linux owns about 90% to 100% of that space.

Knowing Linux isn't just a "nice to have"; it’s your secret weapon. If you can navigate a Linux terminal, you instantly become more hireable because you can actually manage the tools you build.

Here is why Linux is a big deal:

It’s where your work actually lives: You might write code on your laptop, but your data pipelines (the stuff that moves and cleans data) will almost certainly live on a Linux server. If it’s in "Production," it’s on Linux.
Security: In data engineering, you’re handling sensitive information — names, emails, credit card digits. Linux is built like a fortress. It handles permissions and privacy way better than the standard consumer OS.
The Command Line makes you a pro: Typing commands might feel like you're in an old hacker movie, but it’s actually way faster than clicking through menus. Mastering the command line makes you faster, more confident, and—honestly—just a better engineer.
It’s built for speed: Linux is "lightweight." It doesn’t waste energy on background apps you don't need. This means your data pipelines run faster and more efficiently.

Part 2: Getting Set Up (The WSL2 Guide)

On Linux/Mac: You’re already set! Just search for "Terminal" in your apps.

On Windows: The best way to do this is through WSL2 (Windows Subsystem for Linux).

How to install WSL2:

Enable the Feature: Go to your Windows Search Bar and Type 'Turn on Windows Features On or Off'.
Find "Windows Subsystem for Linux" in the list, check the box, and click OK.

Open PowerShell as Administrator and type:
wsl --install

Restart: You must restart your computer for the changes to take effect.

Finalize: After rebooting, a terminal will open. Follow the prompts to create a Username and Password. (Note: The password won't show characters as you type—this is normal!).

Part 3: The Essential Commands

We first need to know how to navigate this environment. Here are some basic commands you can try out.

pwd (Print Working Directory): Tells you exactly which folder you are currently in.
ls (List): Shows you what’s inside your current folder.
cd (Change Directory): Your "walking" command. Use cd .. to go back one folder.
mkdir folder_name (Make Directory): Creates a new folder.
touch filename: Creates a brand new, empty file.
rm filename (Remove): Deletes a file. Be careful: there is no "Undo."

Pro Productivity Tips

Tab Completion: Start typing a name and hit Tab. Linux will finish the word for you.
The Up Arrow: Hit the up arrow to see commands you typed previously.
clear: Wipes the screen clean.
Ctrl + C: The "Emergency Stop" button if a command is stuck.

Part 4: Real-World Practice

We’re going to create a workspace and download a real dataset (the famous Iris flower dataset).

# Create a folder and redirect to it
mkdir linux_practice
cd linux_practice

Create your own file

Before we download anything, let's create a small file to store settings or write small notes.
touch my_notes.txt

If you run ls, you’ll see your new empty file sitting there.

Download the data using 'wget'

wget https://raw.githubusercontent.com/dataprofessor/data/master/iris.csv

If you run ls again, you’ll see your new csv file added to the list.

Peek at the top 10 rows

head -n 10 iris.csv

Part 5: Mastering the Editors (Nano vs. Vim)

On a remote server, you don't have VS Code. You have to edit files inside the terminal.

1. Nano:

Nano is like a very basic version of Notepad that lives in your terminal.

How to use it:

Type nano iris.csv.
Editing: Use your arrow keys to move the cursor and just start typing.
The Bottom Menu: See those ^ symbols? That means the Ctrl key.
To Save: Press Ctrl + O (Write Out), then hit Enter.
To Exit: Press Ctrl + X.

2. Vim:

How to use it:

Type vim iris.csv.
Normal Mode: You start here. You cannot type text yet. This mode is for moving around and running commands.
Insert Mode: To start typing, press the letter i. You’ll see -- INSERT -- at the bottom. Now you can edit the file.
Saving your work: * First, hit the Esc key to leave "Insert Mode" and go back to "Normal Mode."
Type :wq and hit Enter. (w means save/write, q means quit.
The Emergency Exit: If you messed up and want to leave without saving, hit Esc, then type :q! and hit Enter.

Conclusion

You navigated a server, pulled data, and edited files using the terminal.👏

You can also use a "cheat sheet" to guide you for the first few weeks. Happy coding!

Want to Learn Git and GitHub? A Step-by-Step Guide to Version Control

Gathuru_M — Sun, 18 Jan 2026 13:09:29 +0000

If you’re like me, you probably heard people talking about "pushing to main" or "merging branches" and thought, “What on earth are they talking about?”
It feels like everyone knows this secret language. But honestly? Once you get the hang of it, Git and GitHub are just a fancy way of making sure you don't accidentally delete your entire project.😊

Let’s break it down.

This guide will walk you through the fundamentals of Git and GitHub. You'll learn how to install them, connect them, and use them to manage your code effectively.

What is Git?

Git is a version control system. Think of it as a smart history book for your code. Every time you make changes to your project, Git can record them. This means you can:

Track modifications: See exactly what changes were made, when, and by whom.
Revert: Easily go back to an earlier state of your code if something goes wrong.

What is GitHub?

GitHub is a web-based hosting service for Git repositories, like the cloud (think Google Drive).
While Git is the tool you use on your computer to manage versions, GitHub is like a cloud storage and collaboration platform for your Git projects. It allows you to:

Store: Keep a backup if your laptop decides to take a permanent nap.
Share: Make your projects accessible to others.
Collaborate: Provide a central hub for others to work on a project with you without overwriting each other's work.
Showcase: Create a portfolio of your projects.

Step 1: Installing Git Bash

Git Bash is a command-line interface (CLI) for Windows where you type commands to talk to Git.

For Windows:

Download Git Bash: Go to the official Git website: https://git-scm.com/download/win
Run the installer: Follow the on-screen instructions. You can generally stick with the default options, but here is something to keep in mind:
- "Choosing the default editor used by Git": You can keep the default (Vim) or choose another editor you're familiar with (like VS Code, Notepad++, etc.).
- "Adjusting the name of the initial branch": Choose "Let Git decide" (it usually defaults to main now).
Verify the installation: Open the Git Bash application, type git --version and press Enter. You should see the Git version number.

Step 2: Configuring Git

Before you start using Git, you need to tell it who you are.
This information will be attached to your commits to track who is making changes.

While still on Git Bash and run these two commands, replacing the placeholders with your name and email:

git config --global user.name "Your Name"
git config --global user.email "your.email@example.com"

You can verify your settings with:

git config --global --list

Step 3: Connecting the Two (The Handshake)

You will first need a GitHub account.
If you don't have one already, head over to https://github.com/join and sign up.
Follow the prompts to create your username, password, and verify your email.

Now, to make your computer talk to GitHub without asking for your password every five seconds, we use something called an SSH Key.

Generate a new SSH key: In Git Bash, type:

ssh-keygen -t ed25519 -C "your@email.com"

Just keep hitting Enter until it stops.

Start the SSH agent:

eval "$(ssh-agent -s)"

Add your SSH key to the SSH agent:

ssh-add ~/.ssh/id_ed25519

Copy your public SSH key:

cat ~/.ssh/id_ed25519.pub

A bunch of random letters and numbers will pop up. Highlight and copy them.

Pass this to GitHub: Go to your GitHub settings, look for SSH and GPG keys, click New SSH Key, and paste that wall of text in there.

Step 4: Your First Project (The Fun Part)

Let’s say you have a folder on your computer called my-cool-app.

Tell Git to watch this folder: Inside Git Bash, go to that folder and type

git init

Make a file: Create a simple text file called readme.txt and write "Hello world" inside it.
The "Add and Commit":

git add .
git commit -m "My first save point"

(This tells Git, "Hey, look at all the changes I just made!").

Step 5: Sending it to the Cloud (Pushing)

Now, go to GitHub, create a "New Repository," and name it whatever you want. GitHub will give you a link that looks like git@github.com:yourname/your-repo.git.

Link them up: Type,

git remote add origin [PASTE YOUR LINK HERE]

Send it!:

git push -u origin main

Refresh your GitHub page, and boom! Your code is on the internet.

Step 6: Pulling it Back

If you ever go to a different computer, or if a friend changes your code on GitHub, you need to bring those changes back to your machine.

Just type:

git pull origin main

It’s like syncing your phone—it grabs the newest version from the cloud and puts it on your computer.

Wrapping Up

That’s pretty much the "Big Three":

Add (Pick what you want to save)
Commit (Save it)
Push (Send it to GitHub)

Don't worry if you forget the commands at first. I still have to Google them half the time.
Just keep playing around with them, and eventually, it’ll feel like second nature.
Happy coding!🥳

SQL Joins Explained: Case Example

Gathuru_M — Wed, 13 Sep 2023 01:55:44 +0000

Structured Query Language(SQL) is a computer language for storing, manipulating, and retrieving data stored in a relational database.

SQL Joins are like clauses used to combine records from two or more tables in a database, just as the name(join) means.

In this article, you will learn, using a case example, the different types of joins, when, and how they are used. Be sure to check for “bonus joins” included at the end of the article.

Example Data

We will use data from these 2 tables to show various ways to display employees from John Smith's company.

Departments Table

Department_Id	Department_Name
1	Executive
2	HR
3	Sales
4	Support
5	Sales
6	Research

Employees Table

Employee_Id	Full_Name	Department_Id	Job_Role	Manager_Id
1	John Smith	1	CEO	Null
2	Sarah Goodes	1	CFO	1
3	Wayne Ablett	1	CIO	1
4	Michelle Carey	2	HR Manager	1
5	Chris Matthews	3	Sales Manager	2
6	Andrew Judd	4	Development Manager	3
7	Danielle McLeod	5	Support Manager	3
8	Matthew Swan	2	HR Representative	4
9	Stephanie Richardson	2	Salesperson	5
10	Tony Grant	3	Salesperson	5
11	Jenna Lockett	4	Front-End Developer	6
12	Michael Dunstall	4	Back-End Developer	6
13	Jane Voss	4	Back-End Developer	6
14	Anthony Hird	Null	Support	7
15	Natalie Rocca	5	Support	7

The code below shows the syntax for writing a JOIN:

SELECT columns
FROM table1
JOIN table2 ON table1.column1=table2.column1

Types of Joins

There are four major types of joins in SQL, as listed below:

Inner Join
Left Join
Right Join
Full Join

Inner Join

It creates a result table that displays information common between two tables based on a shared piece of information.

It is the most important and frequently used of the joins.

In this case, we could use it to display employees from the departments listed in the first table.

SELECT employees.Full_Name, employees.Job_Role, departments.Department_Name
FROM departments
INNER JOIN employees ON departments.Department_Id = employees.Department_Id
 -- You can replace the keyword INNER JOIN with JOIN

You should get the results below

Result

Full_Name	Job_Role	Department_Name
John Smith	CEO	Executive
Sarah Goodes	CFO	Executive
Wayne Ablett	CIO	Executive
Michelle Carey	HR Manager	HR
Chris Matthews	Sales Manager	Sales
Andrew Judd	Development Manager	Development
Danielle McLeod	Support Manager	Support
Matthew Swan	HR Representative	HR
Stephanie Richardson	Salesperson	HR
Tony Grant	Salesperson	Sales
Jenna Lockett	Front-End Developer	Development
Michael Dunstall	Back-End Developer	Development
Jane Voss	Back-End Developer	Development
Natalie Rocca	Support	Support

Left Join

A LEFT JOIN returns all rows from the left table, plus matched values from the right table or NULL in case of no match.

Note: The left table refers to the table that appears before the "LEFT JOIN" keywords in your SQL query. Same case applies when using RIGHT JOIN

We could use left join to retrieve a list of all employees along with their department names. If an employee doesn't belong to a department, display NULL

SELECT employees.Full_Name, departments.Department_Name
FROM employees 
LEFT JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Full_Name ASC

You should get the results below

Result

Employee_Id	Full_Name	Department_Name
1	John Smith	Executive
2	Sarah Goodes	Executive
3	Wayne Ablett	Executive
4	Michelle Carey	HR
5	Chris Matthews	SALES
6	Andrew Judd	Development
7	Danielle McLeod	Support
8	Matthew Swan	HR
9	Stephanie Richardson	HR
10	Tony Grant	SALES
11	Jenna Lockett	Development
12	Michael Dunstall	Development
13	Jane Voss	Development
14	Anthony Hird	NULL
15	Natalie Rocca	Support

You can use the COALESCE() function to replace NULL values with more relatable user-defined values.

i.e. If an employee doesn't belong to a department, display "No Department" instead.

SELECT employees.Employee_Id, employees.Full_Name, 
COALESCE(departments.Department_Name, "No Department") AS Department_Name
FROM employees 
LEFT JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Full_Name ASC

You should get the results below

Result

Employee_Id	Full_Name	Department_Name
1	John Smith	Executive
2	Sarah Goodes	Executive
3	Wayne Ablett	Executive
4	Michelle Carey	HR
5	Chris Matthews	SALES
6	Andrew Judd	Development
7	Danielle McLeod	Support
8	Matthew Swan	HR
9	Stephanie Richardson	HR
10	Tony Grant	SALES
11	Jenna Lockett	Development
12	Michael Dunstall	Development
13	Jane Voss	Development
14	Anthony Hird	No Department
15	Natalie Rocca	Support

Right Join

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches
in the left table. Not so different from the LEFT JOIN.

In this case, we could view employees and their various departments

SELECT Department_Name,
COALESCE (employees.Full_Name, "No Employee") AS Full_Name 
FROM employees 
RIGHT JOIN departments ON departments.Department_Id = employees.Department_Id;

You should get the results below

Result

Department_Name	Full_Name
Executive	Wayne Ablett
Executive	Sarah Goodes
Executive	John Smith
HR	Stephanie Richardson
HR	Matthew Swan
HR	Michelle Carey
Sales	Tony Grant
Sales	Chris Matthews
Development	Jane Voss
Development	Michael Dunstall
Development	Jenna Lockett
Development	Andrew Judd
Support	Natalie Rocca
Support	Danielle McLeod
Research	No Employee

Full Join

The SQL FULL JOIN combines the results of both left and right outer joins.

The joined table will contain all records from both tables and fill in NULLs for missing
matches on either side.

SELECT employees.Employee_Id, employees.Full_Name, employees.Job_Role, 
departments.Department_Id, departments.Department_Name
FROM employees 
FULL JOIN departments ON departments.Department_Id = employees.Department_Id
ORDER BY employees.Employee_Id ASC

You should get the results below

Result

EMPLOYEE_ID	FULL_NAME	JOB_ROLE	DEPARTMENT_ID	DEPARTMENT_NAME
1	John Smith	CEO	1	Executive
2	Sarah Goodes	CFO	1	Executive
3	Wayne Ablett	CIO	1	Executive
4	Michelle Carey	HR Manager	2	HR
5	Chris Matthews	Sales Manager	3	Sales
6	Andrew Judd	Development Manager	4	Development
7	Danielle McLeod	Support Manager	5	Support
8	Matthew Swan	HR Representative	2	HR
9	Stephanie Richardson	Salesperson	2	HR
10	Tony Grant	Salesperson	3	Sales
11	Jenna Lockett	Front-End Developer	4	Development
12	Michael Dunstall	Back-End Developer	4	Development
13	Jane Voss	Back-End Developer	4	Development
14	Anthony Hird	Support	NULL	NULL
15	Natalie Rocca	Support	5	Support
NULL	NULL	NULL	6	Research

Bonus Joins

Self Join

A SELF JOIN is typically not a join type but a special way of joining a table to itself. You may want to combine rows in a table based on a related column present in the table.

It is commonly used when you need to traverse a hierarchical structure where each row references another row in the same table

When comparing data within rows in the same table.

SELECT
e.Employee_Id,
e.Full_Name,
e.Job_Role,
m.Employee_Id AS Manager_Id,
m.Full_Name AS Manager_Name
FROM employees e, employees m 
WHERE e.Manager_Id = m.Employee_Id;

You should get the results below

Result

EMPLOYEE_ID	FULL_NAME	JOB_ROLE	DEPARTMENT_ID	MANAGER_NAME
2	Sarah Goodes	CFO	1	John Smith
3	Wayne Ablett	CIO	1	John Smith
4	Michelle Carey	HR Manager	1	John Smith
5	Chris Matthews	Sales Manager	2	Sarah Goodes
6	Andrew Judd	Development Manager	3	Wayne Ablett
7	Danielle McLeod	Support Manager	3	Wayne Ablett
8	Matthew Swan	HR Representative	4	Michelle Carey
9	Stephanie Richardson	Salesperson	5	Chris Matthews
10	Tony Grant	Salesperson	5	Chris Matthews
11	Jenna Lockett	Front-End Developer	6	Andrew Judd
12	Michael Dunstall	Back-End Developer	6	Andrew Judd
13	Jane Voss	Back-End Developer	6	Andrew Judd
14	Anthony Hird	Support	7	Danielle McLeod
15	Natalie Rocca	Support	7	Danielle McLeod

That's most of what you need to know about SQL Joins. Hopefully, you found this information helpful. Feel free to share it with anyone who's having a hard time with Joins, and keep learning! 😊

If you have any questions, please leave them in the comments section below.

Thanks for reading!

Data Engineering 102: Introduction to Python for Data Engineering

Gathuru_M — Sun, 11 Sep 2022 20:08:07 +0000

Python is today’s most popular programming language with endless applications in various fields. It is ideally suited for deployment, analysis, and maintenance thanks to its flexible and dynamic nature.
It is one of the crucial skills required in the field of Data Engineering, to create Data Pipelines, set up Statistical Models, and perform a thorough analysis of them.
To start using python, you'll need it installed in the Operating system you're currently using be it Linux, Mac OS, or the most common Windows.
Python provides an ample amount of libraries and packages for various applications. These are the top 5 Python for Data Engineering packages. They include:

-Pandas
-pygrametl
-petl
-Beautiful Soup
-SciPy

But firstly, in Learning python, these are some of the topics you should perhaps start with:-

1.MATH EXPRESSIONS
Syntax Math Meaning
a+b a+b addition
a-b a-b) subtraction
a*b a\times b\ multiplication
a/b a\div b\, division (see note below)
a//b a\div b\ division - in python 2.2 & abv
a%b a mod b modulo
-a -a negation
abs(a) |a| absolute value
a**b a^{b} exponent
math.sqrt square root

2. Strings
Strings in python are surrounded by either single quotation marks or double quotation marks.
e.g.
'hello' is the same as "hello".

You can display a string literal with the print() function

3. Variables
Variables are containers for storing data values.
A variable is created the moment you first assign a value to it.
x = 5
y = "John"
print(x)
print(y)

4.Loops
Python provides three ways for executing the loops

(a)While Loop:
In python, a while loop is used to execute a block of statements repeatedly until a given condition is satisfied. And when the condition becomes false, the line immediately after the loop in the program is executed.

while expression:
statement(s)

(b) For in Loop:
For loops are used for sequential traversal. For example: traversing a list or string or array etc. In Python, there is no C style for loop, i.e., for (i=0; i<n; i++). There is “for in” loop which is similar to for each loop in other languages. Let us learn how to use for in loop for sequential traversals

for iterator_var in sequence:
statements(s)

(c)Nested Loops:
Python programming language allows using one loop inside another loop. The following section shows a few examples to illustrate the concept.

Syntax:

while expression:
while expression:
statement(s)
statement(s)

5. Functions
A function is a block of code that only runs when it is called.

The basic syntax is:
def my_function():
print("Hello from a function")

6. List, Tuples, Dictionary, and sets

It's also important to learn how to connect databases with:-
1.BOTO3
2.Psycopg2
3.MySQL

So, as long as there is data to process, data engineers will be in demand. I wish you all the best as you choose to pursue this journey.

Thanks for reading!
Any questions? Leave your comment below to start fantastic discussions!

Data Engineering 101: Introduction to Data Engineering

Gathuru_M — Sun, 21 Aug 2022 18:32:46 +0000

Data Engineering is the process of building data pipelines and making quality data available for efficient data-driven decision-making.
A person who performs these activities is called a Data Engineer.

But what are data pipelines exactly...
In data processing, there is the flow of data from say a point A to B to C i.e., from an application to a data warehouse or from a data source to the database. This series of processing steps is called a data pipeline.
In these series of steps, each step delivers an output that is the input to the next step. This continues until the pipeline is complete. However, in some cases, independent steps may be run in parallel.

What’s the difference between a data analyst and a data engineer?
Data scientists and data analysts analyze data sets to gain knowledge and insights. Data engineers on the other hand build systems for collecting, validating, and preparing that high-quality data which is then used by data scientists to promote better business decisions.

With that said, these are some of the Essential skills required to be a Data Engineer in 2022

Data Structures
SQL
NoSQL
Understanding of Data Lakes and Data Warehouse
Python
Big Data - Hadoop, Apache Spark(PySpark), Hive, and Apache Kafka
Cloud Services - AWS, Microsoft Azure, Google Cloud, Snowflake, etc.
Visualization - Tableau, PowerBI, Looker, Qlikview, etc.

I wish you all the best as you choose to pursue this journey.

Thanks for reading!
Any questions? Leave your comment below to start fantastic discussions!