DEV Community: Milcah Mukunza

SQL Simplified: DDL, DML, and Essential Commands.

Milcah Mukunza — Sat, 11 Apr 2026 15:57:15 +0000

INTRODUCTION.

SQL (Structured Query Language) is the foundation of relational databases. At first, it may seem like there are many commands to learn, but most real-world work is based on a few core ideas: defining structure, managing data, and querying it effectively.

In my recent work with SQL, I explored how different command types serve different purposes. DDL (Data Definition Language) focuses on structure, using commands like CREATE to build tables. DML (Data Manipulation Language) handles the data itself, using INSERT, UPDATE, and DELETE to manage records. In this article I will walk through those concepts using a school database for a fictional institution called Nairobi Academy.

DDL vs DML — What's the Difference?

DDL (Data Definition Language)- These are commands that define or change the structure of a database. Examples: CREATE, ALTER, DROP.
DML (Data Manipulation Language) - DML deals with content. Once your structure exists, DML commands fill it with data, update existing records, and delete what is no longer needed. Examples: INSERT, UPDATE, DELETE, SELECT.

DDL COMMANDS.

1. CREATE- Used to make a new table.

First, I created the schema (a named container that organises your tables inside a database) then built the three required tables for the school: students, subjects, and exam_results.

Always remember to mention the data types for each column such as VARCHAR for text or SERIAL for auto-incrementing numbers. And don’t forget to define a primary key, which acts as the unique identifier for every row in your table.

2. ALTER - Used to change existing tables.

After creating the Students table, the school realized they had forgotten to include a phone number column. Instead of deleting the entire table and starting from scratch, I used the ALTER TABLE command to add the missing column and later, I also used it to remove it when it was no longer needed.

DML COMMANDS.

1. INSERT - Used in adding data.

With the table structure in place, the next step was to populate it with data. PostgreSQL makes this easier by allowing you to insert multiple rows in a single statement, which saves time and reduces repetitive typing.

2. UPDATE - Used for fixing data.

When Esther Akinyi moved from Nakuru to Nairobi, there was no need to delete her entire record. Instead, I simply updated the specific field that changed, using a WHERE clause to ensure that only her row was modified.

Always include a WHERE clause when using UPDATE or DELETE. Without it, the command will affect every row in the table, which is almost never the intended outcome.

DELETE - Used for Removing a Row.

The school cancelled one exam result. Using a DELETE statement with a WHERE clause removed only that specific record.

FILTERING WITH WHERE.

The WHERE clause is what makes a query precise instead of overwhelming. It instructs PostgreSQL to return only the rows that meet a specific condition, and it’s essential when working with SELECT, UPDATE, or DELETE statements.

Basic Operators

1. BETWEEN- Used in range queries.

Instead of writing marks >= 50 AND marks <= 80, you can use BETWEEN for a more concise and readable query. The BETWEEN operator is inclusive of both boundary values.

2.IN AND NOT IN - Used in list matching.

Rather than chaining multiple OR conditions, the IN operator lets you check against a list of values in a single, clear, and readable statement.

3. LIKE- Used for pattern matching.

Use LIKE when you only know part of a value. The % symbol acts as a wildcard, meaning “any sequence of characters can go here."

TRANSFORMING DATA WITH CASE WHEN.

CASE WHEN is SQL’s equivalent of an if/else statement. Instead of simply retrieving data as-is, it allows you to create a new column dynamically based on conditions. This makes it especially powerful for transforming raw numbers into meaningful labels.

Syntax structure

Example - Grading exams results.

Every CASE WHEN expression must end with END. Always assign a name to the resulting column using AS, otherwise, the output header will default to “case,” which is not very helpful.

CONCLUSION AND REFLECTION

One of the challenges i faced was BETWEEN operator. I accidentally repeated the column name (exam_date) and used the wrong date format '2024-3-18'. The correct syntax is cleaner: specify the column once, then provide the two boundary values. Fixing this taught me how precise SQL is. Even small format slips can break a query, but once corrected, the logic feels elegant.

SQL can seem intimidating at first, but its logic becomes clear once you grasp its layered structure: use DDL to define the schema, DML to populate it, and SELECT with WHERE to query it. Each step builds naturally on the one before.

How to Publish a Power BI Report and Embed it into a Website

Milcah Mukunza — Sun, 05 Apr 2026 15:05:31 +0000

A step-by-step guide from setting up your workspace to embedding live featuring annotated instructions and code examples. In this guide, we’ll walk through the steps to publish a Power BI report and how to embed it into a website.

INTRODUCTION

What is Power BI ?
Power BI is a Microsoft tool that allows you to connect to data, create interactive dashboards and visualizations, and share insights across your organization or the web. Whether you are analyzing sales, tracking project progress, or visualizing key metrics, it enables you to turn data into meaningful reports and communicate insights effectively.

Why should we Publish Reports?

Publishing transfers your report to the cloud through Power BI Service, whereas embedding enables it to be displayed within webpages such as company intranets, client portals, or public websites.”

Step 1: Creating a Workspace

A Workspace in Power BI Service is a collaborative cloud environment that functions like a shared folder. It enables users to store and organize multiple reports and dashboards, allowing team members to access them efficiently. Creating a workspace is the first step in the publishing process

How to create One:

Open your browser and go to **app.powerbi.com **and sign in with your work or school account.
In the left sidebar, click Workspaces.
Click + New workspace at the bottom of the panel.
Give your workspace a clear name (e.g., "Macy Cohort 15").
Optionally add a description and upload a logo.
Click Save.

Step 2: Uploading and Publishing Your Report

There are two common methods for publishing a Power BI report. The first method involves publishing the report directly from Power BI Desktop to the Power BI Service. The second method allows users to upload a .pbix file manually into an existing workspace within the Power BI Service.

Method 1- Publish from Power BI Desktop (Most common)

Open your completed report in Power BI Desktop.
Go to the Home tab on the ribbon.
Click the Publish button.
A dialog will ask "Select a destination". Choose the workspace you just created.
Click Select. Power BI will then upload your report.
Once complete, a success message appears with a link to open it in the browser.

Method 2 - Upload .pbix via the browser

In Power BI Service, navigate into your workspace.
Click + New → Upload a file.
Choose Local file and locate your .pbix file.
Click Open. The report and its dataset will both appear in the workspace.

Step 3: Generating the Embed Code

To embed a report on a website, you need an HTML code from Power BI.

How to generate one:

Open your report in the workspace.
Navigate to File → Embed → Website or portal.
Copy the HTML code provided.

Step 4: Embedding the Report on a Website

Now that you have the code, embedding it is simply a matter of pasting it into your webpage's HTML. Here's how to do it on a html file:

Open your website’s HTML file in your preferred editor.
Paste the iframe code in the section where you want the report to appear. Save and refresh your website in a browser. Your interactive Power BI report should appear.

Key Insights on Publishing and Embedding Power BI Reports

Publishing and embedding Power BI reports allows your data insights to reach real users through websites and online platforms. The following key points are important to remember:
Workspaces are essential -Always publish reports to a dedicated workspace instead of My Workspace to enable proper sharing, management, and collaboration.
Choose the correct embed type -Publish to web is public and free, while secure embedding requires authentication and licences. Avoid publishing sensitive data publicly.
Follow a consistent workflow -The standard process — Workspace → Publish → Generate Embed Code → Embed in Website — applies to all Power BI reports.

A photo of the published dashboard

Conclusion
Data insights are only valuable if they are seen and understood. Embedding Power BI reports brings your data to life for anyone visiting your website.

UNDERSTANDING DATA MODELLING IN POWER BI

Milcah Mukunza — Sat, 28 Mar 2026 22:10:27 +0000

In today’s data-driven environments, tools like Power BI make it easy to build reports, but getting them to work correctly is where the real challenge lies. This article takes a practical look at how to structure your data effectively so your reports remain accurate, intuitive, and easy to maintain.

DATA MODELLING

What is Data Modelling?
Data modeling is the art of structuring, defining, and connecting your data tables so that Power BI can answer analytical questions correctly. It helps you decide: How many tables you need, what goes in each table, and how those tables connect.

HOW TO PERFORM JOINS IN POWER QUERY
A join is a method of linking tables using a shared field so that related data can be viewed or analyzed as a single dataset. In Power BI, joins are called Merge Queries.

Heres a simple example
Table 1

Table 2

Steps

Load both tables into Power Query
Click on the Customers table
Go to Home → Merge Queries
Select:
First table: Customers
Second table: Orders
Click the matching column in both tables:
Customer ID → Customer ID
Choose join type (start with Left Outer)
Click OK

Final result

TYPES OF JOINS

Left Join
Returns all rows from the left table (Customers) and matches from the right (Orders). If there’s no match → NULL.

Illustration

All customers are included, even if they have no orders.

Right Join
Keeps all rows from the right table (Orders) and adds matching customers; unmatched rows display NULL.

All orders are included, even if no matching customer exists.

Inner Join

Returns only rows that exist in both tables (matching keys).

Samuel (No orders) and Order 300 (No customer) are excluded.

Full Outer Join

Includes all rows from both tables, matching where possible and filling unmatched fields with NULL.

Combines the effect of LEFT + RIGHT joins.

POWER BI RELATIONSHIPS

In Power BI, a relationship defines how tables are connected via one or more columns. Relationships enable data from different tables to interact seamlessly in visuals, filters, and calculations.

TYPES OF RELATIONSHIPS

One-to-Many (1:M)

One row in the first table matches many rows in the second table.
Example:
Table: Customers (1 row per customer)
Table: Orders (multiple orders per customer)

Many-to-Many (M:M)

Rows in both tables can have multiple matches in the other table.
Example: A student can enroll in many courses, and each course can have many students

One-to-One (1:1)

One row in the first table matches exactly one row in the second table.
Example:
Table: Employee
Table: Employee Details (with their address, department, etc.)

Active vs. Inactive Relationships

Active Relationship:
The default relationship used for calculations and filtering.
Only one active relationship is allowed between two tables.

Inactive Relationship:
Exists but is not used by default in visuals.
Can be activated in DAX formulas using the USERELATIONSHIP()
function

JOINS VS RELATIONSHIPS

Illustration:

CROSS FILTER FUNCTION

Single Direction:

Filters flow one way, from the (1 side) to the (M side only).

Both Direction:
Filters flow in both directions, useful for complex relationships or M:M scenarios.
Use carefully as it can create circular filter paths and slow queries.

Cardinality
It describes how many rows in one table relate to rows in another table. Determines how filters propagate and how aggregations behave.
Types: 1:1, 1:M, M:1, M:M

FACT VS DIMENSION TABLES

Fact Tables
Stores measurable, transactional data and contains numeric metrics (like sales amount, quantity) and foreign keys referencing dimensions.

Dimension Tables
Store descriptive attributes about entities, showing the "who, what, where" behind each transaction.

TYPES OF SCHEMAS

Star Schema

This is a data model where: A central fact table (e.g., Orders) is directly connected to dimension tables with combined attributes.
Each dimension is independent (not split further).

Illustration

Snowflake Schemas
This is an extension of the star schema where dimension tables are normalized (split into related sub-tables).

Illustration

COMMON MODELING ISSUES

Overusing Flat Tables - Leads to large data size, difficult updates, and inaccurate counts due to repeated data.
Granularity Mismatch - Occurs when fact and dimension tables are at different levels (e.g., order lines vs full orders), causing duplication.
Slicers Not Filtering -A slicer may appear but not affect results if the filter path is broken.This is usually caused by missing relationships or wrong direction.
Circular Relationships- Happens when tables filter each other in multiple paths, creating ambiguity. Leads to errors or incorrect calculations.
Missing Date Table- Without a proper date table, time-based calculations won’t work correctly.

CONCLUSION

Good Power BI modeling comes down to one core principle: keep facts and dimensions separate, connected by a clean star schema. This gives you fast queries, accurate numbers, and reports that scale. The discipline is simple: clean relationships, consistent granularity, sound dimensional design. Everything else follows.

[Boost]

Milcah Mukunza — Sat, 28 Mar 2026 17:24:50 +0000

EXCEL FUNCTIONS THAT WILL SAVE YOU HOURS

Milcah Mukunza — Sun, 22 Mar 2026 17:44:37 +0000

INTRODUCTION

As a beginner, I used to think that Excel was only meant for data entry, but there is much more to it than what meets the eye. Excel is a powerful tool that simplifies tasks, improves efficiency, and helps users analyze data effectively. Here are some of the functions that every beginner needs to understand in order to work more efficiently in Excel.

TRIM()

This function is used to remove extra spaces from a text.

For instance:
The cell B2 contains extra spaces around the name "Brianna"(a total of five extra spaces). We shall remove these extra spaces using the TRIM function: =TRIM(B2)
This formula has also been applied to the range B2:B6. The cleaned-up results are shown in column D, covering the range D2:D6.
B2:B6 - the original range with names (some with extra spaces).
TRIM(B2)- removes any extra spaces before, after, or between words in B2.
D2:D6 - the range displaying the names without extra spaces.
This method ensures that all unnecessary spaces are removed, leaving only the properly formatted names

CONCATENATE()

This function combines multiple texts or numbers into one string

For Example:
In this case we use the CONCATENATE function to combine two names 'Michael' and 'Williams' =CONCATENATE (B2, " ", C2). B2 contains Michael , " ", (The quotation marks with a space) adds a space between the first and last name. Never forget to include them for spacing. C2 contains Williams. The combined results are displayed in column D, covering the range D2:D6.

COUNTIF()

This function is used to count cells that match a specific condition.

In this example we use COUNTIF function to determine the number of employees in the finance department. =COUNTIF(D2:D12, "FINANCE"). D2:D12 is the range of the department column, and "FINANCE" is the criteria we are counting. After pressing enter the formula gives us the total number of employees in the Finance department, which is 3.

SUMIF()

This function is used to sum cells that match a specific condition

In this example we use SUMIF function to calculate the total number of years worked by employees in the HR department. =SUMIF(D2:D12, "HR", E2:E12). D2:D12 Is the range containing departments names, "HR" is the criteria specifying we only want employees from the HR department and E2:E12 is the range of values to sum which is the number of years each employee has worked from the HR department which gives us a total of 41 years.

CONCLUSION

Learning the above Excel functions has completely changed how I work with data. Before, I struggled to clean and analyze datasets, spending far too much time manually adjusting text, summing values, or counting items. Now, with these functions at my disposal, I can quickly clean data, combine information, and calculate insights with just a few formulas. Tasks that used to take hours can now be done in minutes, and I can focus more on interpreting results and making decisions.