DEV Community: Dalton Imbiru

Understanding SQL Data Definition Language (DDL): Building the Foundation of Every Database

Dalton Imbiru — Thu, 30 Jul 2026 11:29:12 +0000

What is Data Definition Language (DDL)?

Data Definition Language (DDL) is a subset of SQL that deals with the structure of a database rather than the data itself. Unlike Data Manipulation Language (DML), which focuses on inserting, updating, and deleting records, DDL is responsible for creating and organizing the objects that hold the data.

Database administrators, software developers, and data engineers frequently use DDL when designing new databases or modifying existing ones.

Common DDL Commands

1. CREATE

The CREATE command is used to create new database objects such as databases and tables.

For example, creating a table to store employee information:

```sql id="c4w2m9"
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10,2)
);




This command creates a table named **Employees** with five columns and specifies `EmployeeID` as the primary key.

---

### 2. ALTER

As business requirements change, database structures often need to be updated. The `ALTER` command modifies existing database objects.

For example, adding an email column:



```sql id="q9x7ld"
ALTER TABLE Employees
ADD Email VARCHAR(100);

You can also use ALTER to rename columns, change data types, or add constraints.

3. DROP

The DROP command permanently removes a database object along with all its data.

```sql id="t6r1ke"
DROP TABLE Employees;




Since this action cannot usually be undone, it should be used with caution.

---

### 4. TRUNCATE

`TRUNCATE` removes all rows from a table while keeping the table structure intact.



```sql id="u2m5bz"
TRUNCATE TABLE Employees;

Unlike DELETE, TRUNCATE is generally faster because it removes all records without processing each row individually.

5. RENAME

Some database systems allow objects to be renamed.

```sql id="n8v4hf"
ALTER TABLE Employees
RENAME TO Staff;




The exact syntax varies depending on the database management system.

---

## Why DDL Matters

DDL is essential because it defines how data is organized and stored. Without well-designed tables and relationships, retrieving and analyzing data becomes inefficient and prone to errors.

A properly designed database offers several benefits:

* Organizes data logically.
* Reduces redundancy.
* Improves data integrity.
* Supports faster queries.
* Makes future maintenance easier.

Whether you're building a customer database, an inventory system, or a school management application, DDL provides the foundation upon which everything else is built.

---

## DDL vs DML

It's common for beginners to confuse DDL with DML, but they serve different purposes.

| Data Definition Language (DDL)                  | Data Manipulation Language (DML)                 |
| ----------------------------------------------- | ------------------------------------------------ |
| Defines database structures                     | Manipulates data within tables                   |
| Creates and modifies tables                     | Inserts, updates, retrieves, and deletes records |
| Examples: `CREATE`, `ALTER`, `DROP`, `TRUNCATE` | Examples: `SELECT`, `INSERT`, `UPDATE`, `DELETE` |

Understanding SQL Data Definition Language(DML)

Dalton Imbiru — Thu, 30 Jul 2026 11:26:47 +0000

What is Standard Query Language (SQL)

SQL is the standard language used to communicate with relational databases such as MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and SQLite. Databases organize information into tables made up of rows and columns, making it easier to store and retrieve data efficiently.

Businesses use SQL to manage customer information, employee records, sales transactions, inventory, and many other types of data.

Data Manipulation Language (DML)

Data Manipulation Language (DML) consists of commands used to interact with the data stored in tables.

The most common DML commands include:

SELECT – Retrieves data from a table.
INSERT – Adds new records.
UPDATE – Modifies existing records.
DELETE – Removes records.

For example, retrieving all employees from a table:

SELECT *
FROM Employees;

These commands form the foundation of everyday database operations.

Essential SQL Keywords

SQL provides several keywords that make querying data more effective.

Some of the most commonly used include:

FROM – Specifies the table.
WHERE – Filters records.
ORDER BY – Sorts results.
DISTINCT – Removes duplicates.
LIMIT or TOP – Restricts the number of rows returned.
AS – Creates aliases for columns.

These keywords help retrieve information accurately and present it in a meaningful way.

Filtering Data with Operators

Filtering allows us to retrieve only the data that meets specific conditions.

Common comparison operators include:

=
>
<
>=
<=
<>

Logical operators such as AND, OR, and NOT allow multiple conditions to be combined.

Other useful operators include:

BETWEEN
IN
LIKE

Example:

SELECT *
FROM Orders
WHERE Amount > 2000
AND Status = 'Completed';

Filtering is one of the most frequently used SQL skills because it helps answer specific business questions.

Combining Tables with SQL Joins

In most databases, information is stored across multiple tables. SQL joins make it possible to combine related data using a common key.

The four main joins are:

INNER JOIN – Returns matching records from both tables.
LEFT JOIN – Returns all records from the left table and matching records from the right.
RIGHT JOIN – Returns all records from the right table and matching records from the left.
FULL OUTER JOIN – Returns all records from both tables.

Joins are essential for creating comprehensive reports that combine customer, product, employee, and sales information.

Using CASE WHEN Statements

The CASE WHEN statement introduces conditional logic into SQL queries. It works similarly to an IF-ELSE statement in programming languages.

For example:

SELECT CustomerName,
       Amount,
       CASE
           WHEN Amount >= 5000 THEN 'High Value'
           WHEN Amount >= 2000 THEN 'Medium Value'
           ELSE 'Low Value'
       END AS Category
FROM Orders;

CASE WHEN is useful for categorizing data, assigning grades, creating customer segments, and generating business-friendly reports.

Row-Level Functions

Row-level functions operate on individual rows to clean and transform data.

Examples include:

UPPER()
LOWER()
CONCAT()
ROUND()
LENGTH()
TRIM()

These functions improve data consistency and make reports more readable.

Handling NULL Values

Missing data is common in real-world databases. SQL provides several ways to handle NULL values.

Useful functions include:

IS NULL
IS NOT NULL
COALESCE()
NULLIF()

Replacing missing values before analysis helps improve the accuracy of reports and calculations.

Working with Date and Time

Date and time functions simplify time-based analysis.

Examples include:

CURRENT_DATE
CURRENT_TIMESTAMP
DATEDIFF()
EXTRACT()
DATE_FORMAT()

These functions help answer questions such as:

How many orders were placed this month?
How long did delivery take?
Which customers have not purchased recently?

Time-based analysis is essential for tracking trends and business performance.

Using Subqueries

A subquery is a query nested inside another query.

For example, to find products priced above the average:

SELECT ProductName, Price
FROM Products
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
);

Subqueries make it possible to solve complex problems without manually calculating intermediate results.

Simplifying Queries with Common Table Expressions (CTEs)

Common Table Expressions (CTEs) improve the readability and organization of complex SQL queries.

Example:

WITH CustomerSales AS
(
    SELECT CustomerID,
           SUM(Amount) AS TotalSales
    FROM Orders
    GROUP BY CustomerID
)

SELECT *
FROM CustomerSales
WHERE TotalSales > 50000;

CTEs make large queries easier to understand, maintain, and debug.

Bringing It All Together

Imagine you're working as a Data Analyst for an e-commerce company. Management asks you to:

Display completed orders only.
Combine customer and order information.
Categorize customers based on spending.
Replace missing contact details.
Calculate delivery time.
Identify customers spending above the average.
Generate a clean sales report.

Using filtering, joins, CASE WHEN, row-level functions, NULL handling, date functions, subqueries, and CTEs, you can answer all these questions with efficient and readable SQL queries.

How to connect Power BI to SQL Databases

Dalton Imbiru — Thu, 30 Jul 2026 10:27:49 +0000

Power BI can connect directly to SQL databases either locally or in the cloud. By connecting Power BI to a SQL database, organizations can analyze live data, automate reporting, and make data-driven decisions without relying on manual data exports.

The following is a guide on how to connect Power BI to both local SQL databases and cloud-based SQL databases.

To connect to a SQL database, ensure you have:

Power BI Desktop installed.
Access to the SQL database.
Database server name.
Database name.
Username and password (if required).
Internet connectivity (for cloud databases).
PostgreSQL or SQL Server drivers installed if necessary.

Connecting Power BI to a Local SQL Database

A local SQL database is hosted on your computer or an organisation's internal network.

Step 1: Open Power BI Desktop and get data

If using PostgreSQL:

Launch Power BI Desktop - Home - Get Data - PostgreSQL Database.

Before Entering Connection details, go to PostgreSQL Connections and copy and paste the connection details into Power BI. That is, the server, which is the host, followed by a colon then copy and paste the port thereafter.
Copy and paste the Database Name and click ok.

After Loading, you will be directed to a navigator page where you will be able to select the data needed and load and transform the data.

Use Transform Data if cleaning or transforming data is required before loading.

Connecting Power BI to a Cloud-Based SQL Database

Cloud databases are hosted on remote servers such as:

Microsoft Azure SQL Database
Amazon RDS
Google Cloud SQL
Aiven PostgreSQL
ElephantSQL
Supabase PostgreSQL

The connection process is similar but requires internet access.
Using Aiven PostgreSQL

Before Entering Connection details, go to Aiven and copy and paste the connection details into Power BI. That is, the server, which is the host, followed by a colon then copy and paste the port thereafter.
Copy and paste the Database Name and click ok.

After Loading, you will be directed to a navigator page where you will be able to select the data needed and load and transform the data.

Use Transform Data if cleaning or transforming data is required before loading.

Importing the Aiven CA Certificate (Windows)

Aiven PostgreSQL uses SSL encryption to secure connections.

Download the CA Certificate

Log in to your Aiven Console.
Open your PostgreSQL service.
Navigate to the Overview or Connection Information section.
Download the CA Certificate.

Open the Certificate Manager

Press the Windows key.
Search for Manage User Certificates.
Open the application.

Import the Certificate

In the Certificate Manager, expand:

   Trusted Root Certification Authorities

Select:

   Certificates

Right-click Certificates.
Select:

   All Tasks → Import

Click Next.
Click Browse.
Navigate to your Downloads folder and select the CA Certificate you downloaded from Aiven.

Click Next.
Ensure the certificate is imported into:

   Trusted Root Certification Authorities

Click Finish.
A confirmation message should appear indicating that the import was successful.

Verify the Installation

After importing the certificate:

Restart Power BI Desktop if it was open.
Connect to your Aiven PostgreSQL database using the PostgreSQL connector.
The SSL certificate should now be trusted, allowing Power BI to establish a secure connection without certificate errors.

How to publish a Power BI report and embed it into a website using an iframe.

Dalton Imbiru — Wed, 15 Apr 2026 10:57:37 +0000

Publishing and Embedding Power BI Reports on the Web

Power BI is a business intelligence tool from Microsoft that makes it easy for users to transform raw data into meaningful insights using interactive dashboards and reports.

Power BI's most powerful feature is the ability to publish reports online and embed them into websites, allowing others to interact with the data without having direct access to Power BI.

This article will take you through the process of creating a workspace to embedding a report using an iframe, with visual guidelines and key insights.

1. Creating a Workspace in Power BI

This is where reports, dashboards, and datasets are stored and managed.

Steps to Create a Workspace

On your browser, go to Power BI Service
Sign in with your account
On the left sidebar, click Workspaces
Click Create a workspace

Enter:
- Workspace name
- Description
Click Save

Key Notes

Workspaces help organize your reports
You can collaborate with others by adding members
“My Workspace” is private, while new workspaces can be shared

2. Uploading and Publishing Your Report

After creating a report in Power BI Desktop, the next step is publishing it to the Power BI Service.

Steps to Publish

Open your report in Power BI Desktop
Click File → Publish → Publish to Power BI
Sign in if prompted
Select the workspace you created
Click Select

Verify Upload

Go back to Power BI Service
Open your workspace
Confirm your report appears under:
- Reports
- Datasets

Key Notes

Any updates in Power BI Desktop can be republished
Changes reflect automatically in the online version

3. Generating the Embed Code

To display your report on a website, you need an embed code, which Power BI generates for you.

Steps to Generate Embed Code

Open your report in Power BI Service
Click File
Select Embed report
Choose Publish to web (public)
Click Create embed code
Confirm the warning message

What You Get

A public URL
An iframe embed code

Uploading the Power BI report into a website

Open a new file in Visual Studio Code, generate an HTML boilerplate, and paste the iframe link into the body section.
-Run the HTML file.
After running the HTML file, a webpage will load in the browser displaying the Power BI Dashboard that a user can interact with.

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

Dalton Imbiru — Sun, 05 Apr 2026 20:22:55 +0000

Data modelling is the foundation of effective data analysis in Power BI. A well-structured model ensures faster performance, accurate calculations, and easier report building. This article breaks down everything you need to know—from SQL joins and Power BI relationships to schemas and practical implementation steps.

What is Data Modelling?

Data modelling is the process of organising data into tables and defining how those tables relate to each other so that analysis becomes meaningful and efficient.

In Power BI, data modeling involves:

Structuring tables (Fact and Dimension)
Defining relationships between tables
Optimizing performance and usability

SQL Joins Explained (With Examples)

Joins combine data from two or more tables based on a common column.

Examples:

Customers

CustomerID	Name
1	John
2	Mary
3	Alex

Orders

OrderID	CustomerID
101	1
102	2
103	4

INNER JOIN

Returns only matching records from both tables.

Result:

CustomerID	Name	OrderID
1	John	101
2	Mary	102

Use case: When you only want valid matches (e.g., customers who made purchases).

LEFT JOIN

Returns all records from the left table + matching from the right.

Result:

CustomerID	Name	OrderID
1	John	101
2	Mary	102
3	Alex	NULL

Use case: Show all customers, even those without orders.

RIGHT JOIN

Returns all records from the right table + matching from the left.

Result:

CustomerID	Name	OrderID
1	John	101
2	Mary	102
NULL	NULL	103

Use case: Show all orders, even if customer info is missing.

FULL OUTER JOIN

Returns all records from both tables.

Result:

CustomerID	Name	OrderID
1	John	101
2	Mary	102
3	Alex	NULL
NULL	NULL	103

Use case: Complete data reconciliation.

LEFT ANTI JOIN

Returns rows from left table that have no match in right table.

Result:

CustomerID	Name
3	Alex

Use case: Identify customers who never purchased.

RIGHT ANTI JOIN

Returns rows from right table with no match in left table.

Result:

OrderID	CustomerID
103	4

Use case: Identify orphan records (e.g., invalid orders).

Joins in Power BI (Power Query)

Power BI implements joins in Power Query.

Steps:

Go to Home → Transform Data
Select a table
Click Merge Queries
Select second table
Choose matching column(s)
Select join type:

Inner
Left Outer
Right Outer
Full Outer
Left Anti
Right Anti
1. Expand columns to finalize

Relationships in Power BI

Unlike SQL joins (which combine tables physically), Power BI relationships connect tables logically.

Types of Relationships

1. One-to-Many (1:M)

One record in Table A → Many in Table B
Example: Customers → Orders

Most common relationship

2. Many-to-Many (M:M)

Many records in both tables
Example: Students ↔ Courses

3. One-to-One (1:1)

One record matches exactly one record
Example: Employee ↔ Employee Details

Cardinality

Defines how tables relate:

One-to-Many
Many-to-One
Many-to-Many

Cross-Filter Direction

Controls how filters flow between tables.

Single direction → One way (recommended)
Both directions → Two-way filtering (use carefully)

Active vs Inactive Relationships

Active relationship → Default used in visuals
Inactive relationship → Exists but is not used unless activated via DAX (USERELATIONSHIP)

Example:

Order Date (Active)
Ship Date (Inactive)

Creating Relationships in Power BI

Method 1: Model View

Go to Model View
Drag one column to another
Relationship is created automatically

Method 2: Manage Relationships

Go to Home → Manage Relationships
Click New
Select:

Tables
Columns
Cardinality
Cross-filter direction
1. Click OK

Joins vs Relationships (Key Difference)

Feature	Joins	Relationships
Where used	Power Query	Data Model
Result	Combines tables	Keeps tables separate
Performance	Can increase size	More efficient
Flexibility	Static	Dynamic

Fact vs Dimension Tables

Fact Table

Contains measurable data (numbers)

Examples:

Sales
Revenue
Quantity

Dimension Table

Contains descriptive attributes

Examples:

Customer Name
Product Category
Date

Example Model

FactSales

OrderID
CustomerID
ProductID
SalesAmount

DimCustomer

CustomerID
Name

DimProduct

ProductID
Category

Data Modeling Schemas

Star Schema

One central fact table
Connected to multiple dimension tables

Snowflake Schema

Dimensions are normalized (split into multiple tables)

Example:

Product → Category → Department

Flat Table (Denormalized / DLAT)

All data in one table

Use Cases

Schema	When to Use
Star	Most Power BI reports
Snowflake	Complex hierarchical data
Flat Table	Small datasets or quick analysis

Role-Playing Dimensions

A role-playing dimension is a table used multiple times for different purposes.

Example: Date Table

Order Date
Ship Date
Delivery Date

In Power BI:

Duplicate the Date table
Create separate relationships

Common Data Modeling Issues

Ambiguous relationships
Happens with multiple paths between tables

Many-to-many confusion
Can lead to incorrect aggregations

Circular relationships
Causes errors

Poor performance
Caused by flat tables or too many joins

Solution:

Use star schema
Avoid unnecessary bi-directional filters
Keep relationships simple

Step-by-Step: Building a Model in Power BI

Step 1: Load Data

Home → Get Data

Step 2: Clean Data (Power Query)

Remove duplicates
Handle nulls
Merge tables if needed

Step 3: Create Relationships

Go to Model View
Drag and connect tables

Step 4: Validate Model

Check cardinality
Ensure no ambiguous paths

Step 5: Optimize

Use star schema
Reduce columns
Avoid many-to-many unless necessary

Conclusion

Data modeling in Power BI is not just technical, it’s strategic. Understanding joins helps you prepare data, while relationships allow you to analyze it efficiently. By structuring your data into fact and dimension tables and choosing the right schema (preferably star), you create a model that is both powerful and scalable.

Mastering these concepts transforms Power BI from a simple visualization tool into a robust analytics engine and ultimately changes how you interpret and interact with data.

How Excel is Used in Real-World Data Analysis

Dalton Imbiru — Mon, 23 Mar 2026 08:59:53 +0000

What is Excel?

Microsoft Excel is a spreadsheet program developed by Microsoft that is used to organize, analyze, and visualize data.
Think of it as a digital table made up of rows and columns, where each box (a cell) can hold numbers, text, or formulas.

Microsoft Excel is more than just a spreadsheet tool; it is a practical engine for handling real-world data across industries. From small businesses to global corporations, Excel is used daily to organize information, uncover patterns, and support decision-making.

In business, Excel is often used for budgeting and financial analysis. For example, a company can track its monthly expenses and revenue, then use formulas like SUM to calculate totals or IF statements to determine profit or loss. A manager might build a financial model to predict future performance based on past trends, helping guide decisions such as hiring or investment.

Image showing SUM OF Employees salary.
FORMULAS:
=SUM(G2:G877)
=SUMIFS(AF2:AF877, AE2:AE877, "London", W2:W877, "Single", S2:S877, "Female")

In data analysis, Excel allows users to clean, sort, and interpret large datasets. Features like PivotTables make it easy to summarize data—for instance, a sales team can quickly see which product sells the most in a specific region. Functions like VLOOKUP or XLOOKUP help combine data from different tables, which is useful when matching customer records or inventory lists.

Image showing Pivot Table
FORMULAS:
VLOOKUP(10865,A1:AJ877,4,FALSE)
INDEX(G2:G877,MATCH(10865,A1:A877,0))

In education, students and teachers use Excel to manage grades and analyze performance. A teacher might calculate class averages using AVERAGE, identify top-performing students, or visualize results using charts like bar graphs and pie charts.

In logistics and operations, Excel helps track inventory, orders, and deliveries. A small business, for example, can monitor stock levels and use conditional formatting to highlight items that are running low. This helps prevent shortages and improves efficiency.

In human resources, Excel is used to manage employee data, payroll, and attendance. Functions can calculate salaries, deductions, and overtime automatically, reducing manual work and errors.

Excel is also widely used for data visualization. Charts and dashboards turn raw numbers into insights—like showing sales growth over time or comparing performance across departments. This makes it easier for decision-makers to understand complex information quickly.

Ultimately, learning Excel has changed how I interact with data. Instead of seeing numbers as static information, I have began to recognize patterns, relationships, and insights. It has shifted my thinking from simply recording data to actively interpreting it, enabling more informed decisions in both academic and professional settings.