Forem: Sharon-nyabuto

Applying SQL Concepts: DDL, DML, Filtering, and Data Transformation

Sharon-nyabuto — Sun, 12 Apr 2026 18:25:54 +0000

Structured Query Language (SQL) is the standard language used to interact with relational databases.
SQL is a command type language. Whether you want to create, delete, update or read data, SQL provides commands to perform these operations.
SQL commands are categorized based on their specific functionalities, and allow for the creation, manipulation, retrieval and control of data and database structures.
Among the command categories include;

Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Query Language (DQL)
Data Control Language (DCL)
Transaction Control Language (TCL)

In this article, we will look at Data Definition Language (DDL) and Data Manipulation Language (DML).

DDL (DATA DEFINITION LANGUAGE)

Data Definition Language (DDL) is a set of commands used to define and manage the structure of a database.
It focuses on creating and modifying database objects such as tables, schemas, and indexes.
DDL uses commands like CREATE, ALTER, and DROP, to determine how data is organized and stored within the database.
These commands affect the structure of the database and not the data itself.
DDL operations are often automatically committed, therefore changes take effect immediately without needing a manual COMMIT.

DML (DATA MANIPULATION LANGUAGE)

Data Manipulation Language (DDL) is a set of SQL commands used to manage and manipulate the data _stored within database tables.
Unlike DDL, which focuses on defining the structure of the database, DML is concerned with performing operations on the data itself.
Common DML commands include INSERT, UPDATE, and DELETE, which are used to add new records, modify existing data, and remove records from a table respectively.
In most database systems, DML changes are not automatically committed, meaning they can be rolled back if necessary before being finalized.

Below are some SQL commands and how they are used;

CREATE
DDL command used to create an object in a database, e.g a table

The syntax;
CREATE TABLE table_name(
column1 datatype constraints,
column2 datatype constraints,
column3 datatype constraints
);
For example, to create a schema in a database for a school, and add a table called students in the Schema you use CREATE

create table students
(student_id INT primary key,
first_name VARCHAR(50) not null,
last_name VARCHAR(50) not null,
gender VARCHAR(1),
date_of_birth DATE, 
class VARCHAR(10), 
city VARCHAR(50) 
);

INSERT

This command is used to add a new record in a database.

The syntax;
INSERT INTO table_name (column1, column2, column3)
VALUES
(value1, value2, value3),
(value1, value2, value3);

To add student details to the students table created above, you use INSERT

insert into students (student_id,first_name,last_name,gender,date_of_birth,class,city)
values 
(1,'Amina','Wanjiku','F','2008-03-12','Form 3','Nairobi'),
(2,'Brian','Ochieng','M','2007-07-25','Form 4','Mombasa'),
(3,'Cynthia','Mutua','F','2008-11-05','Form 3','Kisumu'),
(4,'David','Kamau','M','2007-02-18','Form 4','Nairobi'),
(5,'Esther','Akinyi','F','2009-06-30','Form 2','Nakuru');

UPDATE
The update command is used to modify existing records in a database.

The syntax;
UPDATE table_name
SET column1 = value1,
WHERE condition;

In the students table created, if student id number 2, moved to Nairobi from Mombasa, UPDATE is used.

update students
set City = 'Nairobi'
where student_id = 2;

DELETE

This deletes one or more records from a database object.
It is used together with the _WHERE _clause to ensure that only specific rows are deleted.

The syntax;

DELETE FROM table_name
WHERE condition;

In the students table, if student_id 4 is no longer a student there, his records are removed using DELETE

delete 
from students
 where student_id = 4;

FILTERING WITH WHERE

The WHERE clause filters rows based on one or more conditions, so your query only modifies the records that match.
It is often used across SELECT, UPDATE, and DELETE statements.

The Where clause is used with logical and comparison operators such as equal to (=), greater than (>) and less than (<).

It can also be used with other operators including;

BETWEEN

Where clause is used with the BETWEEN operator, to filter records within a specified range.
When using between, the result will include both the start and end values of the range.

For example, to find students that scored between 55 and 95 marks from a results table, the values returned will include 55 and 95

select result_id,student_id,subject_id,marks,grade
from exam_results 
where marks between 55 and 95;

The WHERE clause can be used with the IN operator to filter records based on multiple values.
It makes queries shorter and easier to understand, instead of writing multiple conditions using the (=) operator combined with OR.

Syntax;

SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, value3);

To find students from different cities in our students table, we use IN

select first_name, last_name, city
from students 
where city in ('Nairobi','Nakuru','Mombasa');

LIKE

WHERE clause is used with the LIKE operator when searching for a particular pattern.
When using LIKE, a wildcard is used;
‘%’- signifies any number of characters including 0.
‘_’ wildcard is used for a specific number of characters.

Syntax is;
SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

For example, to find the list of subjects that contain the word 'studies' in a subjects table, LIKE is used.

select subject_name
from subjects
where subject_name like '%Studies%';

CASE WHEN STATEMENT

The CASE WHEN statement is used to add conditional logic inside queries, similar to an if-else structure.
It checks conditions one by one and returns a value as soon as a matching condition is found.
It helps categorize data dynamically and allows creation of new categories based on the specific conditions within a query.

The syntax is;

select column name,
case
when condition1 then 'result 1'
when condition2 then 'result 2'
else 'default result
end as new column_name

For example, CASE WHEN can be used to group marks into a new column called Performance, in the results table, as shown below.

select result_id, student_id, subject_id,marks,
    case
        when marks >= 80 then 'Distinction'
        when marks >= 60 then 'Merit'
        when marks >= 40 then 'Pass'
        else 'Fail'
    end as Performance
from exam_results;

REFLECTION

Learning Databases and SQL can seem difficult, but once you get into it, it becomes interesting especially if you are curious to see how you can create and modify your database along the way.

The most interesting thing with SQL is that there are some rules for consistency and functionality across databases. Some of those rules are;

Always end your statements with a semicolon (;)
SQL commands and key words such as SELECT and INSERT are not case-sensitive.
Spaces and new lines are allowed for better readability.
Do not use SQL keywords as names, if you have to, write them in quotes.
To include comments in your script, begin single line comments with --- and for multi-line comments you use /* at the beginning and */ at the end.
Commands like DROP and DELETE should be used with caution as they could result in permanent data loss without proper use of transactions or backups.

A Comprehensive Guide to Publishing and Embedding Power BI Reports on the Web with IFrames

Sharon-nyabuto — Mon, 06 Apr 2026 23:25:01 +0000

Microsoft PowerBI is one powerful tool when it comes to analyzing and visualizing data.

Power BI allows users to connect to different data sources, transform data, build interactive dashboards and share insights with others.

I have spent the last two weeks learning how to build queries, clean data on power query, use DAX functions, data modelling and relationships, as well as building dashboards and reports in Power BI.

Reports and dashboards in Power BI
Reports and dashboards are important for visualizing data.

While dashboards **are often a one-page summary of key insights, **reports are multiple pages and with a detailed analysis.
In the day to day operations, reports have to be shared with managers, teams and other stakeholders.
Instead of sending ‘.pbix’ files, which require Power BI desktop, embedding reports enables viewing them in browsers, interaction with filters and accessing live and updated data.

Embedding reports is a necessary move from static reports, to interactive web-based dashboards, that no one has a challenge accessing.

In this article, we will learn step by step how to;

Create a Workspace in Power BI Services
Upload and Publish a Power BI report from Power BI desktop
Generate an Embed code
Embed the report on a web page using an iframe.

Brief Overview of Power BI and the Publishing Process
Power BI consists of two components;

- Power BI Desktop– Where the data is loaded, cleaned and transformed, relationships and visuals are built and reports designed.
- Power BI Service- Where the reports are published, workspaces and embedded links are created, dashboards are shared and work is done in collaboration with others.

Creating a Workspace in Power BI Services

A Workspace is an online folder, that is dedicated to storing and organizing your dashboards and reports.

The default workspace is always ‘My Workspace’ which is often just for personal testing, and as a result, you cannot get a working public embed code if you generate your report here. Therefore, do NOT use My Workspace.

How to create a Workspace step-by-step;

Open your browser and go to https://app.powerbi.com
Log in using your Microsoft account
On the left menu, click Workspaces
Click the New Workspace button at the top right
Enter a Workspace Name, e.g. Electronics Sales
Click Apply

Once this is complete, your Workspace is ready. You can now publish your report.

Uploading and publishing a Power BI report from Power BI desktop.

Now we will upload the .pbix file to the workspace we just created.

How to publish a report to the Workspace step-by-step;

Open your report in Power BI desktop.
On the top right corner of the home ribbon, click ‘Publish’.
Select the workspace you just crated, (e.g. Electronic Sales) from the list of Workspaces that Power BI will display.
Click Select
Power BI will now upload your report. It may take a few seconds to 2 minutes, so give it some time.
You will see a message that shows you have successfully published your report to Power BI Services.

Until you have confirmed that the report has been published, do not close your Power BI Desktop.
If you close and there’s something wrong, you may have to start the process all over again.

To confirm if the report has been uploaded to the Power BI Service;

Go back to https://app.powerbi.com
Open your Workspace and refresh. You should see your report

Generating the Embed Code.

Embedding allows the report to be displayed inside a website.
Power BI provides an embed code, that can be copied and pasted in a website.

How to generate a code, step-by-step;

Open your report in the Workspace.
Click ‘File’ at the top of the page.
Select ‘Embed Report’, then click on Website or portal
Click on create Embed code.
Then click Confirm.
Copy the embed code to be used later.

You will now see a pop-up window with the link to embed, and a box with the embed code needed, this is the only correct place to get your embed code from.

The embed code, is an Iframe (inline frame) which is a HTML tag that allows you to display another webpage inside your webpage.

A typical iframe will look like this;

<iframe title="Electronic_Data" width="1140" height="541.25" src="https://app.powerbi.com/reportEmbed?reportId=1e766837-7d75-4a37-bc3d-3e0ea1f56073&autoAuth=true&ctid=c95984e8-6a70-4512-a440-1c79bca9cc37" frameborder="0" allowFullScreen="true"></iframe>

The width and height parts of the code are flexible, and you can change those to whatever you like. All the other parts however, should remain exactly as they are.

A common mistake often made is using the Share button to embed a report.
The share button generates links accessible only to those that have accounts in your organization. It will therefore not generate an appropriate embed code.

Embedding the report on a webpage

Embedding codes allow integration into websites.

How to do it, step-by-step;

Open any plain text editor, such as VS code or Notepad. Do not use Microsoft Word.
Create an empty .html file.
Add basic HTML structure;

html
<!DOCTYPE html>
<html>
<head>
<title>Title of report e.g. Electronic Sales Data</title>
</head>
<body>
<h1>Heading e.g. Electronic Sales Dashboard</h1>
<Power BI iframe goes here>
</body>
</html>

Click file > Save as, and name the file Index.html

That is it, if you double click on the saved html file, it will open on any browser and you can load your report, and see all the interactive report running perfectly.

Troubleshooting common Issues

If something is not working, it could be one of the following;

Problem: Report requires users to log in
Fix: New workspaces are sometimes set to private by default. Go back to your workspace, click manage access, and add Everyone as Viewer.

Problem: Blank Screen after the publishing and embedding
Fix: Give it time. This is a normal occurrence, as Power BI can take a few minutes for the embed code to work, after publishing.

Problem: The report works on your end but no one else can see it.
Fix:It is possible you used ‘Share’ Button, when embedding, instead of generating the embed code. You might need to generate the embed code anew.

Key Insights and Conclusion

Now that you have learnt this process, you can go ahead and use it for your student projects, portfolios, and for work, absolutely free.

You do not need a paid premium license to embed your Power BI reports.
Every singe feature of your report will work exactly as it does on Power BI Desktop, the slicers, filters and interactions will work just fine inside the Iframe.

In conclusion, you could build the best Power BI report, but if you cannot publish it and share it with other people, it's use may not be maximized, and a lot of insight could be lost.

Understanding Data Modeling in Power BI: Joins, Relationships, and Schemas Explained

Sharon-nyabuto — Sat, 04 Apr 2026 17:24:06 +0000

Ever tried to build a puzzle where half the pieces were in a different room?
That is how it feels working with raw data before understanding Data Modelling.

In the real world, data is not stored in one sheet, it is scattered across dozens different files. A major chain for example, has separate files for their stores, customers, sales and products. If you tried to cram all that information into one massive table, managing it would be difficult to manage!

So what is Data Modelling?

Data modelling is the process of organizing data into structured tables and defining how those tables relate to each other. In professional environments, data is organized into 2 main types;

Facts tables - These store measurable events (the metrics). A sales table for instance, is a fact because it records every transaction, indicating how much was sold and when. Fact tables are usually long and full of numbers.

2.Dimensions tables - These provide descriptive contexts, such as customer names, color and product category. They are relatively smaller, and give meaning to the facts.

Tables are connected using Joins (in Power Query) or Relationships (in the model view).

In this article, we will look at;

SQL Joins in Power Query
Power BI Relationships
Schemas
Role Playing Dimensions
Common Modelling Issues to watch out for

SQL JOINS IN POWER QUERY

Before data gets to the model, it has to be cleaned, this happens in Power Query using Joins.

A Join is a way of combining data from two tables based on a common column. It is a rule, that tells the computer how to merge two tables into one physical table.

Our scenario; An online retail store with two lists;

• Table A (Customers) – With Customer Name, Customer ID and Customer Location
• Table B (Orders) - Order ID, Customer ID, Order Amount, Order Date

There are 6 main kinds of Joins;

How to do it Step-By-Step (The steps are common for ALL joins.)
In Power Query:

Click your main table (e.g. Customer List)
Click Merge Queries in the Home Ribbon
Select your second table, (e.g. Orders List in our case)
Click on the matching columns in both tables (e.g. Customer ID)
Choose your Join Kind from the dropdown.

POWER BI RELATIONSHIPS

Relationships define how tables are connected in a data model, so they can interact with each other in reports. Relationships connect tables while keeping them separate, and are created in the Model View.

Cardinality (The Match Ratio)

Cardinality describes the nature of the relationship between two tables. It answers the question How many

**_One-to-Many (1:M)_**

One record in one table relates to many records in another. For example; where one customer has multiple orders or where one bank account has multiple transactions.
Many-to-Many (M:M)
Multiple records in both tables relate to each other. These can cause double-counting and incorrect numbers. Use bridge tables if necessary.
One-to-One (1:1)
Where one record matches exactly one other in another table. This is pretty rare for relationships, and sometimes means that two tables should be merged instead.

Direction and Activity
Cross filter directions: Determines how filters flow between tables.

Single: Filters flow one way, from dimension to fact. A good example is how filtering customers will affect Orders, but orders won’t filter customers.
Both: Filters flow both ways. It is useful but can easily create confusion or incorrect results.

Active Vs Inactive Relationships
In Power BI, you can only have ONEactive relationship between two tables at a time.

Active filters are the connections that are always on.

Inactive filters are the connections that are always off, unless they are required for a certain calculation.

PowerBI allows you to have one active relationship, and one or more inactive relationships at any given time.

How to do it step-by-step

Go to model view
Click a column in your dimension table and drag it to the matching column in your fact table; A line appears
Double click the line to check Cardinality and Direction
You can make the relationship active or inactive

Differences Between Joins and Relationships

SCHEMAS

Now that we know how to connect tables, it is important to also know how to organize them. In data modelling, tables follow a layout, i.e. a Schema.
Every good model will separate data into either fact tables or dimensions tables.

There are three main table designs;

A Flat Table (DLAT)

This is where both your fact and dimension tables are in one place, as would appear in Excel.
While this could be used for one off analysis or very small datasets, it is not advisable on big models, because as data grows, the table becomes very slow and very hard to manage.
It also has a higher probability of duplicate data.

How to get it;
Use the Merge Queries in Power Query to merge all your tables together into one single, massive table.

The Star Schema
This is a model with one fact table at the center, and dimension tables surround it.
It is the industry standard, and results in a clean, fast and simple layout.
It is easy to understand and hard to break, and preferred in over 90% of reports.
It is advisable to use this, unless you have a reason not to.

How to get it;

Load your Fact Table and Dimension Tables separately into Power Query.
Enter Model View: Click the _Model View _icon on the far left sidebar..
Click and drag a unique ID from your Dimension table (e.g., Product ID) onto the matching column in your Fact table.
Drag the Fact table to the center and pan out your Dimension tables around it.
Check for cardinality.

Snowflake Schema

This is a star schema, whose dimensions are further broken down to other dimensions. This is called normalization.

It is used when there are complex relationships such as Country>County>Sub-county.
While it can save on space due to less repetition, it becomes more complicated and reports are slower.

How to get it;

Start Like a Star: Follow the steps above for your main Fact table.
Split a Dimension for example instead of loading Products directly into the Fact link, load Products and then a separate Categories table.
Connect hierarchically, i.e. Fact → Products, then Products → Categories.

ROLE PLAYING DIMENSIONS

Sometimes a single dimension table has to play more than one role. A great example is a date table, that you use to filter both Order dates, and Shipping Dates. In this case, the problem is handled using ‘inactive relationships.
Managing relationships this way allows for a clean model, as it lets you slice data differently, and there's no need to clone the table twice.

COMMON MODELLING ISSUES TO WATCH OUT FOR.

As you start building, here are a few issues to avoid;

Many-to-Many relationships (M:M) - This happens when you try to connect two tables that don't have a unique "one" side. It causes Power BI to get confused and often results in numbers that look correct but are actually completely wrong.
Blanks in Keys– If your ID column has empty cells, they could break the relationship silently. That is why you should clean your data in Power Query before loading.
Flat tables- Don’t try to keep everything in one table just because it feels like excel, you may have a hard time navigating or getting anything done.

How Excel is used in Real World Data Analysis

Sharon-nyabuto — Mon, 30 Mar 2026 06:24:23 +0000

A month ago, I challenged myself to learn data analysis, so I went shopping across the internet for a good place to start and the consensus was unanimous; check out good old Excel.

So what exactly is Excel?

Far from just being a digital ledger, excel is a powerful data engine. It is used in turning observations into trends in Field Research, helps businesses track sales and predict growth while in finance it is used to ensure every cent is accounted for.

Learning Excel felt very intimidating at first, the aggregate and lookup functions were all too new to me! Once I stopped being anxious however, I have been enjoying the journey of discovering this ability excel has! Here are a few things I have learned and use often;

COUNTIF () FUNCTION

This simply counts how many times something appears in your list. In its simplest form, it asks:
=COUNTIF(Where do you want to look?, What do you want to look for?)

IF() FUNCTION

This operator allows excel to ‘think’. It checks if a condition is true, and gives you one result if it is, and another if it is not.

Syntax =IF(logical_test, value_if_true, value_if_false)

You can have as many conditions as you need, all you have to do is properly nest them!

=IF(X3<2, "Poor Score",IF(X3<3,"Average Score",IF(X3<4,"Good Score","Excellent Score")))

Lookup Functions

In the real world, all the data will not necessarily be in the same place always. The sales and customer names may be in different sheets, and the lookup functions are the connectors!

VLOOKUP

It searches down the first column, to find a value and pulls information from a different column on the right.

 Syntax: =VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])

Translation:=VLOOKUP(What are you looking for?, Where is the whole table?, Which column number has the answer?, FALSE)

The FALSE at the end ‘tells’ excel that you want a perfect match

=VLOOKUP(10010, A2:F13, 5, FALSE)

(This simlpy says; Find ID 10010 in this table, and give me the value from the 5th column. The result will be 82107)

Because it relies on a fixed column number, adding a new column to your table can break its logic. If you insert a new column before Column 5, it becomes Column 6, but VLOOKUP will still look at Column 5! Since the 5th column is no longer where the salary ‘lives’, it might return the wrong data, without giving you any error message.

XLOOKUP

A relatively newer version that beats these limitations is the XLOOKUP, and it does not require one to count the columns! XLOOKUP works, regardless whether other columns are added or deleted.

Syntax: =XLOOKUP(Lookup_Value, Lookup_Array, Return_Array)

=XLOOKUP("P-102", A:A, C:C)
(This simply says "Find P-102 in Column A, and give me the corresponding value from Column C."

XLOOKUP only works on Office 365, Excel 2021, or later.

(INDEX + MATCH)

What if you’re working on an older model and still need to bypass VLOOKUP's limitations? You use INDEX and MATCH.

MATCH finds the position of a value, and INDEX grabs the value from that position.

Syntax:=INDEX(Return_Column, MATCH(Lookup_Value, Lookup_Column, 0))

(Note: We use 0 for an exact match every time!)

=INDEX(G2:G13, MATCH(10010, A1:A13, 0))

Translation: Find ID 10010 in Column A, then give me the value from the matching row in Column G.

This combo is powerful because unlike VLOOKUP, it can look to the left or right without breaking!

PIVOT TABLES

Before embarking on excel, these used to look like Martian tech to me! Now, I realize they are effective summary machines! In a few clicks, you have a clean summary of your data. They take data and tell me the averages, totals and counts in less than a minute!

You highlight all your data.
Click Insert
Select Pivot Table.
Drag a field to Rows.
Drag a different field to Values.

And just like that, you have every sum, average, count that you need.

REFLECTION

It’s only been two weeks, but my relationship with data has shifted. There is a massive sense of empowerment in looking at a messy sheet and having a good idea how to fix it, realizing i have the tools to make my data talk to me!

Since I’m still very much in the learning phase and have only just scratched the surface, I’m curious:

What was that one surprisingly simple Excel trick that completely broke your brain when you first saw it?

If you're a seasoned pro, what is a golden tip you wish you knew when you were just starting out in excel?

Please drop your tips, shortcuts, or aha! moments, I’m all ears and ready to learn!