Forem: Victor Karanja

Understanding SQL: DDL, DML, and Data Transformation

Victor Karanja — Fri, 24 Apr 2026 04:59:22 +0000

Introduction

Structured Query Language (SQL) is the standard language for managing and manipulating relational databases.

This article explores:

Data Definition Language (DDL)
Data Manipulation Language (DML)
Filtering with WHERE
Conditional logic using CASE WHEN

What Are DDL and DML?

SQL commands are broadly categorized into two groups: DDL and DML.

Data Definition Language (DDL)

DDL commands define and modify the structure of database objects. They shape of your database—creating tables, altering columns, and removing schemas. DDL operations take effect immediately and permanently.

Command	Purpose
`CREATE`	Build new database objects (schemas, tables, columns)
`ALTER`	Modify existing structures
`DROP`	Delete objects entirely
`RENAME`	Change object names

Data Manipulation Language (DML)

DML commands manage the data inside tables, adding rows, updating values, and deleting records. Unlike DDL, DML changes can be rolled back using transactions if something goes wrong.

Command	Purpose
`INSERT`	Add new rows to a table
`UPDATE`	Modify existing data
`DELETE`	Remove specific rows
`SELECT`	Retrieve data (sometimes classified as DQL)

The Key Difference

Aspect	DDL	DML
Focus	Structure	Data
Commands	`CREATE`, `ALTER`, `DROP`, `RENAME`	`INSERT`, `UPDATE`, `DELETE`, `SELECT`
Reversibility	Permanent (auto-commit)	Can be rolled back
Example	Adding a new column	Changing a student's grade

DDL in Action: Building the Nairobi Academy Database

In the Nairobi Academy assignment, DDL commands established the entire database framework from scratch.

Creating the Schema and Tables

The first step was creating a dedicated schema to organize all school-related tables:


sql
CREATE SCHEMA nairobi_academy;
SET search_path TO nairobi_academy;
Then three tables were built using CREATE TABLE:
sql
-- Students table stores pupil information
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    class VARCHAR(20),
    city VARCHAR(50),
    date_of_birth DATE
);

-- Subjects table stores courses offered
CREATE TABLE subjects (
    subject_id SERIAL PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    credits INTEGER
);

-- Exam_results links students to their scores
CREATE TABLE exam_results (
    result_id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES students(student_id),
    subject_id INTEGER REFERENCES subjects(subject_id),
    marks INTEGER CHECK (marks >= 0 AND marks <= 100),
    exam_date DATE
);
Modifying Structure with ALTER
Real-world databases evolve. When the school forgot to include phone numbers, ALTER TABLE resolved it:
sql
Copy
ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);
Later, the credits column needed clearer naming:
sql

ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;
When the requirement changed again, the column was removed:
sql

ALTER TABLE students DROP COLUMN phone_number;
These ALTER operations demonstrate DDL's flexibility—structures adapt without rebuilding everything.
DML in Action: Populating and Managing Data
Once tables existed, DML commands brought them to life with real student records.
INSERT: Adding Data
Ten students, ten subjects, and ten exam results were inserted:
sql

INSERT INTO students (first_name, last_name, gender, class, city, date_of_birth) 
VALUES ('James', 'Mwangi', 'M', 'Form 4', 'Nairobi', '2006-03-15');
The INSERT statement follows a clear pattern: specify the table, list columns, then provide values in matching order. Bulk inserts use comma-separated value sets.
UPDATE: Correcting Data
When Esther Akinyi moved from Nakuru to Nairobi, UPDATE reflected this change:

sql
UPDATE students 
SET city = 'Nairobi' 
WHERE student_id = 5;
A marks entry error was also fixed:

sql

UPDATE exam_results 
SET marks = 59 
WHERE result_id = 5;
Critical rule: Always use WHERE with UPDATE. Without it, every row changes.
DELETE: Removing Data
When an exam was cancelled, DELETE removed it cleanly:
sql

DELETE FROM exam_results 
WHERE result_id = 9;
Again, WHERE is essential—omitting it empties the entire table.
Filtering Data with WHERE
The WHERE clause is SQL's gatekeeper. It filters rows based on conditions, ensuring queries return only relevant data.
Basic Comparison Operators
Table
Operator    Meaning Example
=   Equal to    WHERE class = 'Form 4'
>   Greater than    WHERE marks > 70
<   Less than   WHERE marks < 40
>=  Greater than or equal   WHERE marks >= 70
<=  Less than or equal  WHERE marks <= 50
<> or !=    Not equal   WHERE city <> 'Nairobi'
Logical Operators
Combine conditions with AND and OR:

sql
-- Form 3 students from Nairobi (both conditions must be true)
SELECT * FROM students WHERE class = 'Form 3' AND city = 'Nairobi';

-- Students in Form 2 or Form 4 (either condition can be true)
SELECT * FROM students WHERE class = 'Form 2' OR class = 'Form 4';
Special Operators
BETWEEN checks ranges inclusively:

sql
-- Marks from 50 to 80, including both endpoints
SELECT * FROM exam_results WHERE marks BETWEEN 50 AND 80;
IN checks membership in a list:

sql

-- Students in any of these three cities
SELECT * FROM students WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');
N IN excludes values:

sql

-- Students in Form 1 or Form 4 only
SELECT * FROM students WHERE class NOT IN ('Form 2', 'Form 3');

LIKE enables pattern matching with wildcards:
Table
Pattern Matches
'A%'    Starts with 'A'
'%Studies%' Contains 'Studies' anywhere
'_a%'   Second letter is 'a'

sql

-- First names starting with A or E
SELECT * FROM students 
WHERE first_name LIKE 'A%' OR first_name LIKE 'E%';
Transforming Data with CASE WHEN
Raw data often needs interpretation before it becomes useful. CASE WHEN acts as SQL's if-then-else logic, creating new calculated columns based on conditions.
Grading Exam Results
Instead of displaying raw marks, the assignment labeled each score with a performance grade:
sql

SELECT 
    result_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;
SQL evaluates conditions top to bottom. A mark of 85 hits the first condition (>= 80) and becomes "Distinction"—it never reaches the >= 60 check. This ordering is crucial.
Categorizing Students
Students were grouped into academic levels:
sql

SELECT 
    first_name,
    last_name,
    class,
    CASE 
        WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
        WHEN class IN ('Form 1', 'Form 2') THEN 'Junior'
    END AS student level
FROM students;
Every CASE must end with END, and AS names the new column.    

Inconclusion, DDL builds the container, while DML fills and shapes the content. The Nairobi Academy data, demonstrated this relationship—CREATE and ALTER established tables, then INSERT, UPDATE, and DELETE managed student records. Filtering with WHERE and operators like BETWEEN, IN, and LIKE narrowed results to specific needs. Finally, CASE WHEN transformed numerical marks into meaningful categories, proving that SQL is not just about storing data—it's about making data understandable.
Mastering these basics prepares you for;
*joins
*windows functions
*Query optimization
*subqueries.
 In SQL real understanding comes from writing queries and lots of practice.

Unlocking Insights: Transforming Messy Data Into Action Using Power BI

Victor Karanja — Mon, 09 Feb 2026 06:37:03 +0000

## How Analysts Clean Messy Data, Use DAX, and Build Dashboards Using Power BI Power BI is an intelligent tool that helps organizations visualize and analyze their data. Raw day-to-day data can be messy and unfiltered. Analysts often deal with missing values, inconsistent formats, duplicates, and poorly structured tables. Power BI stands out because it allows analysts to clean, model, analyze, and visualize data. This article explains how analysts transform messy data into meaningful insights.

Cleaning Messy Data with Power Query

After loading your dataset, open transform data to access the Power Query Editor.

common data problems

Duplicate records- Inconsistent text (e.g. 'cardiology' 'cardio dep' 'Cardiolgy')
Numbers stored as text
Multiple values in a single column
Inconsistent date form

Some of the common Power query Transformation are:

Common Data Problems

Real-world datasets are rarely perfect. Analysts often encounter the following data issues:

Missing values (nulls or blanks)
Duplicate records
Inconsistent text values (cardiology, Cardiology, Cardiology dept)
Incorrect data types (numbers stored as text)
Inconsistent date formats
Spelling errors and typos
Extra spaces or hidden characters
Multiple values stored in a single column
Inconsistent units of measurement
Outliers and extreme values
Poorly structured tables
Mismatched keys between tables
Changing data definitions over time

Addressing these problems during data preparation ensures accurate analysis, reliable dashboards, and trustworthy business insights.

Power Query automatically records every step, so you can modify your transformation at any time.

🧩 Understanding Data Modelling in Power BI

Before you start writing DAX, it’s important to understand data modelling. A data model is simply the way your tables connect inside Power BI. Think of it as the “map” that tells Power BI how your data fits together.

A good data model usually includes:

Fact tables – contain numbers you want to analyze (sales, revenue, quantities).
Dimension tables – contain descriptive information (dates, products, customers).
Relationships – links between tables that allow Power BI to filter and calculate correctly.

A clean model makes your reports faster, your calculations easier, and your DAX formulas more accurate.

Once your data model is organized, you can start adding DAX logic to create measures, calculations, and business rules.

Using Data Analysis Expressions(DAX) To create Business Logic

With a clean data we use dax to create calculations questions.
Dax is used for:
-conditional logic
-time intelligence
-Measures ratios and percentages

Examples of Dax measure
Total Sales =
SUM(Fact Sales[Sales Amount])

Sales Growth =
DIVIDE([Total Sales] - [Last Year Sales], [Last Year Sales])

Building Simple and Clean Dashboards

An effective dashboard presents only the most important metrics, allowing users to understand insights at a glance without being overwhelmed by unnecessary visuals. By prioritizing key KPIs, using consistent layouts analysts create dashboards that are intuitive, easy to navigate, and focused on actionable insights.

Common Power BI visuals are like;

-Bar graphs and column chats

-Line charts for trends

-KPI cards

-Tables and matrices for detail

-Maps for geographic analysis

Using Power Query to clean and Dax, adding logic to data you can transform raw data into meaningful insights.

Inconclusion cleaning and preparing data is the basic foundation of every Insightful Power BI . When you build a clear data model and choose the rightful visuals, your dashboards become intuitive data or report.

#Schemas and Data Modelling in Power BI

Victor Karanja — Sun, 01 Feb 2026 22:01:17 +0000

Schemas and Data Modelling in Power BI

Data modelling is one of the most important steps in Power BI. A well-designed data model improves performance, accuracy, and ease to analyse, while a poor model can lead to slow reports and incorrect insights.

This article explains schemas and data modelling concepts in Power BI.

What Is Data Modelling?

Data Modelling involves organizing data sources into a structured model. In Power BI, this means organizing tables and defining relationships between them to support accurate reporting and efficient calculations.

What Is a Schema?

A schema is the structure and organization of data in a table. It's the logical arrangement of tables used in reports on Power BI.

In Power BI, schemas help determine:

How data is connected
How filters flow between tables
How efficiently queries are executed.

****Types of schemas in Power BI.

-Galaxy schema
-Snowflakes schema
-star schema

Snowflakes schema

Here dimensions are split into sub dimensions, that can also be split into further smaller tables.

Star schema

It's the mostly schema used in Excel, where it has multiple dimensions and one fact table.
its advantages are;

Faster query performance
Easier to write DAX formulas
Better filter -Easy to understand.

Fact Tables

A fact table stores quantitative data (measures) that can be analysed.

Characteristics of Fact Tables:

Contain numerical values
Have many rows
Reference dimension tables using keys

Examples:

Costomer Id
Name
Age
Region

Dimension Tables

A dimension table contains descriptive information that provides context to facts.

Characteristics of Dimension Tables:

Contain text or categorical data
Have fewer rows than fact tables
Used for filtering and grouping data

Examples:

Date
Product
Customer ID
Location

What Is a Relationship in Power BI?

A relationship connects a column in one table to a column in another table, usually through a key.

Example:

Sales[ProductID] → Product[ProductID]

This allows Power BI to understand how records relate across tables.

Relationship Types in Power BI

One-to-Many

This is the most common and recommended relationship type.

One record in a dimension table
Many matching records in a fact table

Example:

One product → many sales records

Used in star schemas.

Many-to-Many (:)

Occurs when both tables contain duplicate values.

Can cause ambiguous results
Should be avoided when possible

Use only when necessary and with caution.

One-to-One (1:1)

Each value appears once in both tables
Rarely used in analytical models

Relationship Direction

Single Direction (Recommended)

Filters flow from dimension tables to fact tables
Predictable and efficient behavior

Best practice for star schemas.

Both Direction (Bi-Directional)

Filters flow both ways
Can cause confusion and performance issues

Use only when absolutely necessary.

Relationships in Star Schema

In a star schema:

All dimension tables connect directly to the fact table
Relationships are one-to-many
Filter direction is single

This results in:

Faster performance
Simpler DAX formulas
Accurate filtering

Relationships in Snowflake Schema

In a snowflake schema:

Dimension tables connect to other dimension tables
More relationships are required
More joins occur during queries

This can:

Reduce performance in Power BI
Complicate filter behavior
Make DAX harder to write and maintain

Why Relationships Matter in Power BI

Correct relationships:

Ensure accurate calculations
Control how filters behave
Improve report performance
Prevent incorrect totals

Poor relationships can lead to:

Incorrect results
Slow visuals
Broken slicers and filters

Best Practices for Power BI Relationships

Use star schema whenever possible
Keep relationships one-to-many
Use single-direction filtering

- Avoid many-to-many relationships

Conclusion

Good data modelling is the foundation of effective Power BI reporting. By using well-structured schemas such as the star and snowflake schema and defining correct relationships, you can improve performance, ensure accurate calculations, and create reports that are easy to understand and maintain. Investing time in proper modelling leads to faster insights and more reliable decision-making.