DEV Community: Gumathi Geo

A quick review of SQL Joins

Gumathi Geo — Fri, 11 Sep 2026 05:58:22 +0000

SQL Joins, Simply

Join combines rows from two tables, matched on shared column, usually an id.

Example tables:

customers orders

Note: customer_id 5 has no match in customers. Carla has no orders. These gaps show what each join does differently.

_Inner Join _— only matching rows on both sides.

sql
SELECT customers.name, orders.item
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

Result: Amina's two orders only. Carla and order 103 drop out, no match.

Left Join — keeps all rows from left table, matched or not.

sql
SELECT customers.name, orders.item
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Result: Amina's orders plus Carla with NULL item.

Right Join — keeps all rows from right table, matched or not.

sql
SELECT customers.name, orders.item
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

Result: Amina's orders plus order 103 with NULL name.

Full Join — keeps all rows from both tables.

sql
SELECT customers.name, orders.item
FROM customers
FULL JOIN orders ON customers.customer_id = orders.customer_id;

Result: Amina's orders, Carla with NULL item, order 103 with NULL name. Nothing dropped.

Quick rule

: Inner join = strict match only. Left/right = pick which side to keep fully. Full join = keep everything. Default to inner join unless missing rows matter to your question.

A DEEP DIVE INTO DDL'S AND DML'S

Gumathi Geo — Fri, 11 Sep 2026 05:40:55 +0000

_ DDL and DML, the basics_

I keep seeing these two terms thrown around whenever people talk SQL, so here's what I've picked up about them so far.

DDL

DDL stands for Data Definition Language. Basically, these are the commands that deal with the structure of a database — the tables themselves, not what's sitting inside them. If you're creating a table, changing its columns, or getting rid of it completely, that's DDL.

Say I'm building a small table to track books at a library:

sql
CREATE TABLE library.books (
book_id VARCHAR(20) NOT NULL,
title VARCHAR(150) NULL,
author VARCHAR(100) NULL,
date_added TIMESTAMP NULL
);

That's CREATE — it builds a brand new table with the columns you tell it to have.

Now say I forgot a column and need to add it later, maybe to track whether a book is checked out:

sql
ALTER TABLE library.books ADD COLUMN is_checked_out BOOLEAN;

ALTER changes a table that already exists, instead of building a new one.

If a table isn't needed anymore, DROP removes it completely — structure and all the data in it, gone:

sql
DROP TABLE library.books;

There's also TRUNCATE, which is a bit different from DROP. It empties out every row in the table but keeps the table itself standing, so you can start filling it up again without recreating it:

sql
TRUNCATE TABLE library.books;

I mixed up DROP and TRUNCATE for a while when I was starting out — DROP kills the table, TRUNCATE just empties it.

And then there's RENAME, for when a name just isn't working anymore. Maybe books should really be called catalog:

sql
ALTER TABLE library.books RENAME TO library.catalog;

You can rename a single column the same way, like if date_added should really be added_on:

sql
ALTER TABLE library.catalog RENAME COLUMN date_added TO added_on;

So that's DDL in a nutshell — it's the commands that shape what your database looks like.

DML

DML is Data Manipulation Language, and this is where things get more familiar if you've written any SQL at all. DML doesn't touch the structure of a table — it works with the actual rows and data inside it.

The one everybody starts with is SELECT, for pulling data out:

sql
SELECT * FROM library.catalog WHERE is_checked_out = TRUE;

This grabs every column, but only for the rows where a book is currently checked out.

To put new data in, there's INSERT:

sql
INSERT INTO library.catalog (book_id, title, author)
VALUES ('BK001', 'The Alchemist', 'Paulo Coelho');

That adds one new row. Notice I didn't fill in every single column — just the ones I had values for.

To change something that's already there, UPDATE is the one to reach for. Say BK001 just got checked out:

sql
UPDATE library.catalog
SET is_checked_out = TRUE
WHERE book_id = 'BK001';

Without the WHERE clause here, every single row in the table gets updated, not just the one you meant — learned that one the hard way on a test database, thankfully.

And finally DELETE, for getting rid of rows you don't want anymore:

sql
DELETE FROM library.catalog WHERE book_id = 'BK001';

This removes just that one row and it's permanent, so it's worth double-checking the WHERE clause before running it. You can also delete based on other conditions, not just an ID — like clearing out anything added before a certain date:

sql
DELETE FROM library.catalog WHERE added_on < '2024-01-01';

ACTUAL DIFFERENCE

DDL shapes the container — the tables, the columns, whether something exists at all. DML deals with what's poured into that container — adding, changing, reading, or removing the actual rows. If you ever forget which is which, ask yourself: am I changing the table itself, or just what's inside it? That's basically the whole distinction.

SQL for Beginners: Window Functions vs GROUP BY

Gumathi Geo — Fri, 11 Sep 2026 05:39:43 +0000

Windows function VS Group by
Both window functions and GROUP BY help you summarize data. But they do it in different ways, and mixing them up leads to confusing results.

- GROUP BY squishes many rows into one row per group.
- A window function keeps every row, and just adds an extra column next to it.

Once you see that difference, it's easy to know which one to reach for.

We'll use one simple table the whole way through, so the examples stay easy to follow:

students
---------------------------
name      | class | score
---------------------------
Amina     | A     | 90
Brian     | A     | 70
Carla     | A     | 85
Dennis    | B     | 60
Efrem     | B     | 95

The simple way

GROUP BY answers a question like: "What's the average score in each class?" It gives you back fewer rows than you started with — one row per class.

A window function answers a question like: "How does this student's score compare to their class average?" It gives you back the same number of rows you started with — one per student — just with something extra calculated for each one.

So:

Want one summary row per group? Use GROUP BY.
Want to keep every row, but add a calculation? Use a window function.

Example 1: GROUP BY — one row per class

-- One row per class. We lose the individual students.
SELECT class, AVG(score) AS average_score
FROM students
GROUP BY class;

Result:

class | average_score
------------------------
A     | 81.6
B     | 77.5

Notice we no longer see Amina, Brian, or any individual name. GROUP BY traded the detail for a summary. That's fine when the summary is all you need.

Example 2: A window function — keep every row

Now say you want to see each student's score next to their class average, without losing any rows:

-- Every student stays, plus a new column showing their class average.
SELECT
    name,
    class,
    score,
    AVG(score) OVER (PARTITION BY class) AS class_average
FROM students;

Result:

name    | class | score | class_average
------------------------------------------
Amina   | A     | 90    | 81.6
Brian   | A     | 70    | 81.6
Carla   | A     | 85    | 81.6
Dennis  | B     | 60    | 77.5
Efrem   | B     | 95    | 77.5

All five students are still there. PARTITION BY class just tells SQL: "calculate the average separately for each class," instead of one big average for everyone.

Think of PARTITION BY as a GROUP BY that doesn't delete any rows.

Example 3: Ranking students in their class

Window functions are also great for ranking. Say you want to know each student's rank inside their own class:

-- Rank students by score, but restart the ranking for each class.
SELECT
    name,
    class,
    score,
    RANK() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
FROM students;

Result:

name    | class | score | class_rank
----------------------------------------
Amina   | A     | 90    | 1
Carla   | A     | 85    | 2
Brian   | A     | 70    | 3
Efrem   | B     | 95    | 1
Dennis  | B     | 60    | 2

ORDER BY score DESC says "rank from highest score to lowest." PARTITION BY class says "start the ranking over again for each class." That's why both Amina and Efrem get rank 1 — one for class A, one for class B.

Example 4: Comparing a row to the one before it

Another handy window function is LAG(). It looks at the previous row so you don't have to join a table to itself.

scores_by_month
--------------------
month  | score
--------------------
Jan    | 60
Feb    | 70
Mar    | 65

-- Show each month's score next to last month's score.
SELECT
    month,
    score,
    LAG(score) OVER (ORDER BY month) AS previous_month_score
FROM scores_by_month;

Result:

month | score | previous_month_score
----------------------------------------
Jan   | 60    | NULL
Feb   | 70    | 60
Mar   | 65    | 70

January has NULL because there's no month before it. Every other row simply grabs the score from the row above it.

When to use which — quick guide

What you want	Use
Just the average/total/count per group	`GROUP BY`
Individual rows AND a group calculation together	Window function
A running total	Window function
Ranking rows within a group	Window function
Comparing a row to the row before or after it	Window function

Simple mistakes to watch for

Trying to filter a window function with WHERE. This won't work:

-- This will cause an error.
SELECT name, class_rank
FROM students
WHERE class_rank <= 2;

WHERE runs before window functions are calculated, so it doesn't know what class_rank even is yet. Instead, wrap it in a subquery or CTE first, then filter on the outside:

WITH ranked AS (
    SELECT
        name,
        class,
        RANK() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
    FROM students
)
SELECT name, class, class_rank
FROM ranked
WHERE class_rank <= 2;

Forgetting ORDER BY inside OVER(). Without it, running totals won't build up properly — SQL won't know which order to add rows in.

Mixing up RANK() and ROW_NUMBER(). If two students tie for the same score, RANK() gives them the same number. ROW_NUMBER() always gives out different numbers, even for ties.

The one-line takeaway

If you just need a summary, use GROUP BY. If you need to keep every row and add a calculation next to it, use a window function.

SQL for Beginners: Window Functions vs GROUP BY

Gumathi Geo — Mon, 07 Sep 2026 21:13:52 +0000

Windows function VS Group by
Both window functions and GROUP BY help you summarize data. But they do it in different ways, and mixing them up leads to confusing results.

GROUP BY squishes many rows into one row per group.
-A window function keeps every row, and just adds an extra column next to it.

Once you see that difference, it's easy to know which one to reach for.

We'll use one simple table the whole way through, so the examples stay easy to follow:

students
---------------------------
name      | class | score
---------------------------
Amina     | A     | 90
Brian     | A     | 70
Carla     | A     | 85
Dennis    | B     | 60
Efrem     | B     | 95

Difference between Windows Functions and Group by

GROUP BY answers a question like: "What's the average score in each class?" It gives you back fewer rows than you started with — one row per class.

So:

Want one summary row per group? Use GROUP BY.
Want to keep every row, but add a calculation? Use a window function.

Example 1: GROUP BY — one row per class

-- One row per class. We lose the individual students.
SELECT class, AVG(score) AS average_score
FROM students
GROUP BY class;

Result:

class | average_score
------------------------
A     | 81.6
B     | 77.5

Notice we no longer see Amina, Brian, or any individual name. GROUP BY traded the detail for a summary. That's fine when the summary is all you need.

Example 2: A window function — keep every row

Now say you want to see each student's score next to their class average, without losing any rows:

-- Every student stays, plus a new column showing their class average.
SELECT
    name,
    class,
    score,
    AVG(score) OVER (PARTITION BY class) AS class_average
FROM students;

Result:

name    | class | score | class_average
------------------------------------------
Amina   | A     | 90    | 81.6
Brian   | A     | 70    | 81.6
Carla   | A     | 85    | 81.6
Dennis  | B     | 60    | 77.5
Efrem   | B     | 95    | 77.5

All five students are still there. PARTITION BY class just tells SQL: "calculate the average separately for each class," instead of one big average for everyone.

Think of PARTITION BY as a GROUP BY that doesn't delete any rows.

Example 3: Ranking students in their class

Window functions are also great for ranking. Say you want to know each student's rank inside their own class:

-- Rank students by score, but restart the ranking for each class.
SELECT
    name,
    class,
    score,
    RANK() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
FROM students;

Result:

name    | class | score | class_rank
----------------------------------------
Amina   | A     | 90    | 1
Carla   | A     | 85    | 2
Brian   | A     | 70    | 3
Efrem   | B     | 95    | 1
Dennis  | B     | 60    | 2

Example 4: Comparing a row to the one before it

Another handy window function is LAG(). It looks at the previous row so you don't have to join a table to itself.

scores_by_month
--------------------
month  | score
--------------------
Jan    | 60
Feb    | 70
Mar    | 65

-- Show each month's score next to last month's score.
SELECT
    month,
    score,
    LAG(score) OVER (ORDER BY month) AS previous_month_score
FROM scores_by_month;

Result:

month | score | previous_month_score
----------------------------------------
Jan   | 60    | NULL
Feb   | 70    | 60
Mar   | 65    | 70

January has NULL because there's no month before it. Every other row simply grabs the score from the row above it.

When to use which — quick guide

What you want	Use
Just the average/total/count per group	`GROUP BY`
Individual rows AND a group calculation together	Window function
A running total	Window function
Ranking rows within a group	Window function
Comparing a row to the row before or after it	Window function

Simple mistakes to watch for

Trying to filter a window function with WHERE. This won't work:

-- This will cause an error.
SELECT name, class_rank
FROM students
WHERE class_rank <= 2;

WHERE runs before window functions are calculated, so it doesn't know what class_rank even is yet. Instead, wrap it in a subquery or CTE first, then filter on the outside:

WITH ranked AS (
    SELECT
        name,
        class,
        RANK() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
    FROM students
)
SELECT name, class, class_rank
FROM ranked
WHERE class_rank <= 2;

Forgetting ORDER BY inside OVER(). Without it, running totals won't build up properly — SQL won't know which order to add rows in.

Mixing up RANK() and ROW_NUMBER(). If two students tie for the same score, RANK() gives them the same number. ROW_NUMBER() always gives out different numbers, even for ties.

CONCLUSION

If you just need a summary, use GROUP BY. If you need to keep every row and add a calculation next to it, use a window function.

Understanding Database Relationships: From Relational Models to Star and Snowflake Schemas

Gumathi Geo — Mon, 29 Jun 2026 13:05:49 +0000

INSIGHTS INTO POWER BI DATA ANALYSIS :FROM DATA TO DECISIONS

Databases are much more than collections of tables—they're systems designed to organize, connect, and retrieve data efficiently. Whether you're building an e-commerce platform, a banking application, or a business intelligence dashboard, understanding database relationships is essential.

In this article, we'll explore the different types of relationships used in relational databases before introducing two important data warehousing models: the Star Schema and the Snowflake Schema.

Types of Database Relationships

There are three primary relationship types you'll encounter in relational databases.

One-to-One (1:1)

A One-to-One relationship exists when one record in a table corresponds to exactly one record in another table.

For example:

One employee has one company ID card

This type of relationship is commonly used when separating sensitive or optional information into a different table.

Employee │ │ Company ID
One-to-Many (1)

The One-to-Many relationship is the most common relationship found in database systems.
One record in the parent table can relate to multiple records in another table.

Examples include:

One customer can place many orders.

Customer │ ├── Order 1 ├── Order 2 └── Order 3
This relationship keeps information organized by storing customer details only once while linking multiple orders back to the same customer.

Many-to-Many (M)

A Many-to-Many relationship occurs when multiple records in one table can relate to multiple records in another.

Examples include:

Students enroll in many courses.

Because relational databases cannot directly represent this relationship, an additional table—often called a junction or bridge table—is introduced.

Students │ │ Enrollments │ │ Courses

This intermediary table stores the associations between the two entities while preserving data integrity.

Understanding Star Schema

When working with data warehouses and business intelligence, database design follows a slightly different approach.

One of the most popular models is the Star Schema.

A Star Schema consists of:

One central Fact Table
Multiple surrounding Dimension Tables The fact table stores measurable business data, while the dimension tables provide descriptive information.

               Date
                 │
                 │
Product ── Sales Fact ── Customer
                 │
                 │
             Store

Fact Table

The fact table usually contains:

Sales Amount
Quantity Sold
Profit
Revenue

Dimension Tables

Dimension tables provide context such as:

Customer
Product
Store
Date
Employee

Advantages of a Star Schema

Simple to understand
Fast analytical queries
Excellent for dashboards and reporting
Fewer joins required

Disadvantages

Some information may be duplicated.
Uses more storage than highly normalized designs.

Understanding Snowflake Schema

The Snowflake Schema is an extension of the Star Schema.
Instead of storing all descriptive information in a single dimension table, each dimension is further normalized into multiple related tables.

                   Date
                    │
                    │
Product ── Sales Fact ── Customer
    │                   │
Category               city
    │                   │
Department        Country

Notice how the Product dimension is split into additional tables like Category and Department, while the Customer dimension connects to City and Country.

This branching structure resembles a snowflake, which is where the model gets its name.

Star Schema vs Snowflake Schema
Feature Star Schema _ _Snowflake Schema

Structure Denormalized Normalized
Number of Joins - Fewer - More
Query Performance - Faster - Slightly slower
Storage Usage - Higher - Lower
Complexity - Simple - More complex
Maintenance - Easier - More structured

When Should You Use Each?
Choose a Star Schema when:

Building dashboards
Creating reports
Prioritizing query performance
Working with business intelligence tools
Choose a Snowflake Schema when:
Reducing data redundancy is important
Maintaining strict data consistency
Managing very large enterprise data warehouses
Handling complex dimensional hierarchies

Final Thoughts

Understanding relationships is the foundation of relational database design. One-to-One, One-to-Many, and Many-to-Many relationships help organize operational databases efficiently, ensuring consistency and minimizing duplication.

As organizations grow and begin analyzing large volumes of historical data, designs evolve into dimensional models such as the Star Schema and Snowflake Schema. While both support analytical workloads, they differ in complexity, normalization, and performance trade-offs.

Mastering these concepts not only improves your database design skills but also prepares you to work with modern data warehouses, reporting systems, and business intelligence platforms.

Whether you're developing applications or designing enterprise data solutions, understanding how these relationships fit together is a skill every developer and data engineer should have.

If you enjoyed this article, consider leaving a ❤️ and sharing it with other developers. Happy coding!

How Excel Is Used In Real-World Data Analysis

Gumathi Geo — Sun, 07 Jun 2026 11:14:01 +0000

INTRODUCTION

Ms Excel

Microsoft Excel is a powerful computer software tool that is used to store, organize and analyze data. Excel is a real world data interactive platform made up of worksheets containing rows and columns in which data entries are made ,formatted and analyzed according to a specific criteria where they can even be presented in visually appealing forms by use of charts and graphs so that they can meet the user's demands.

Real world applications of Microsoft excel

Microsoft excel is widely used in most data related disciplines, some of the most reputable career paths and fields mainly use excel for real time data processing and data predictions, some of the tasks excel is used for include:

Data cleaning and preparation :Excel is used in removing duplicates present in data sets ,Text formatting and splitting columns.
Exploratory data analysis:Tools such as pivot tables are used to explore data and discover hidden patterns, sorting and filtering features can also be used to pin point specific data entries.
Advanced calculations and logical testing :Excel can be used to perform complex logic operations such as data merging and logical analysis along side statistical modelling to understand data distribution

Prominent Features and Formulae

During my introductory phase of familiarizing with excel i came across some core functions and formulae used in data operations .the functions and formulae used in data operation are as listed below :
Math & Trigonometry functions
They calculate basic arithmetic values and powers

SUM :Adds all numbers in a range.
SQRT :Calculates the positive square root of a number.
POWER :Raises a base number to a specified exponent.
Statistical Functions
They are used to analyze a data set to find central tendencies and extreme values.
AVERAGE :Calculates arithmetic mean of a range.
MEDIAN :Identifies the middle number in a sorted list.
MODE :Finds the most frequently occurring number in a data
MAX :Returns the largest value in a set.
MIN :Returns the smallest value in a set.
COUNT :Counts cell containing numbers in a range.
Conditional Statistical Functions
They count or add cells only if they meet a specific criteria
SUMIF :Adds cells that meet one specific condition
COUNTIF :Counts cells that meet one specific condition
COUNTIFS: Counts cells that meet multiple specified conditions
SUMIFS :Adds cells that meet specified multiple conditions
Personal point of view
Microsoft excel is a highly recommendable tool for both data scientists and data analysts as it can both be used to find hidden data insights as well as reviewing and deriving information from past data trends.

CONCLUSION

Excel is a definitive key to uncovering deeper insights and maximizing your data potential.