DEV Community: Mark Glemba

Understanding DDL, DML, and Key SQL Concepts

Mark Glemba — Tue, 14 Apr 2026 07:11:57 +0000

Introduction
Structured Query Language (SQL) is essential for managing and manipulating data in relational databases. In this article, we explore two important categories of SQL commands—Data Definition Language (DDL) and Data Manipulation Language (DML)—along with practical operations such as CREATE, INSERT, UPDATE, DELETE, filtering with WHERE, and the use of CASE WHEN for data transformation.

What DDL and DML Are (and Their Differences)

Data Definition Language (DDL) refers to SQL commands used to define and manage the structure of a database. These commands deal with creating, modifying, and deleting database objects such as tables.

Common DDL commands include

CREATE – used to create new tables or databases
ALTER – used to modify existing structures
DROP– used to delete tables or databases

For example, using "CREATE TABLE" allows you to define columns, data types, and constraints for storing data.

Data Manipulation Language (DML), on the other hand, is used to manage the data within those structures. It focuses on inserting, updating, retrieving, and deleting records.

Common DML commands include:

INSERT – adds new records
UPDATE– modifies existing data
DELETE – removes records
SELECT– retrieves data

Key Difference:
DDL deals with the structure of the database, while DML deals with the data inside the database.

Using CREATE, INSERT, UPDATE, and DELETE

In a typical database assignment, these commands are used as follows:

CREATE: I used this command to define tables such as students, subjects, or exam results. It involved specifying column names, data types (e.g., INTEGER, VARCHAR), and primary keys.
INSERT: This command was used to add records into the tables. For example, inserting student names, subject details, and exam scores into the respective tables.
UPDATE: I used UPDATE to modify existing records. For instance, correcting a student’s score or updating a subject name.
DELETE: This command helped remove unwanted or incorrect records from the database, such as deleting a student entry or clearing outdated data.

These commands are fundamental for maintaining accurate and up-to-date data in any database system.

Filtering Data with WHERE

The WHERE clause is used in SQL to filter records based on specific conditions. It helps retrieve only the data that meets certain criteria.

Some commonly used operators include:

= (Equal to): Selects records that match a specific value
Example: "WHERE score = 80"
> (Greater than): Selects values greater than a given number
Example: "WHERE score > 70"
BETWEEN: Filters values within a range
Example: "WHERE score BETWEEN 50 AND 90"
IN: Matches values within a list
Example: "WHERE subject IN ('Math', 'Science')"
LIKE: Used for pattern matching
Example: "WHERE name LIKE 'J%'" (names starting with J)

The WHERE clause is powerful because it allows precise data retrieval, making queries more meaningful and efficient.

How CASE WHEN Helps in Transforming Data The CASE WHEN statement in SQL is used to perform conditional logic within queries. It allows you to transform data by assigning values based on conditions.

For example, you can categorize student performance:

If score ≥ 70 → “Pass”
If score < 70 → “Fail”

This helps in:

Creating calculated columns
Categorizing or grouping data
Improving readability of query results

CASE WHEN is especially useful in reports, where raw data needs to be interpreted into meaningful insights.

Conclusion

Understanding DDL and DML is crucial for working with databases effectively. While DDL defines the structure of the database, DML allows you to interact with the data itself. Commands like CREATE, INSERT, UPDATE, and DELETE form the backbone of database operations. Additionally, tools like the WHERE clause and CASE WHEN statement enhance your ability to filter and transform data, making SQL a powerful language for data management and analysis.

HOW TO PUBLISH A POWER BI REPORT AND EMBED IT INTO A WEBSITE

Mark Glemba — Mon, 06 Apr 2026 14:10:43 +0000

INTRODUCTION
Power BI is an analytics tool developed by Microsoft that lets a user visualize and share insights on certain data. Power BI is also used transform and clean data using Power Query Editor. Data transformation occurs through; shaping data by filtering, sorting and grouping, adding columns and changing data types through conversion to; numeric, text etc. Data cleaning is done through; handling blanks, standardizing and removing errors.

PUBLISHING PROCESS IN POWER BI

Create a workspace A workspace is like a folder where one stores reports, dashboards and datasets.

. Go to [https://app.powerbi.com/]
. Sign into your account
. Once your logged in, on the left far side click on workspaces

. Click on; + New workspace

2 .Uploading and publishing
. In the power bi desktop in the home page click on publish on the far right.

. Select your workspace where the report will be uploaded
. Once uploaded the dataset will appear on your workspace.

Generating embedding code . Open the report you want embedded . Click on File ~~~~ Embed report . Choose embedding option; either Public (Publish to Web), OR Secure Embed; which requires log in and is more secure. . After clicking on Publish to web, Power BI will generate an embed link + iframe code.

4.Embedding code into website

. Copy entire code
. Open your website HTML file
. Paste the iframe code where you want your report to appear

. Save and open your website where your report will display.

CONCLUSION
Using Power BI with embedding capabilities promotes better collaboration, transparency and efficiency. This empowers users to embrace data-driven solutions that support smarter strategies and improved outcomes.

HOW EXCEL IS USED IN REAL-WORLD DATA ANALYSIS

Mark Glemba — Tue, 31 Mar 2026 19:39:06 +0000

INTRODUCTION
Excel is a tool one can use to organize data for work or any other specific software functions. It allows one to develop graphs for comparison of data and create charts to organize and visualize data.

HOW EXCEL IS USED IN REAL WORLD SCENARIOS

DATA CLEANING
Excel is in various ways for data cleaning by performing functions such as; removing duplicates, trimming spaces, fixing formatting, finding errors, standardizing data, splitting data and filling blanks.
DATA SUMMARIZATION
Excel summarizes large data sets to offer quick insights by use of; pivot tables, formulas and functions, instead of users manually going through large data sets.
VISUALIZING TRENDS
Done by the use of charts; pie, line and bar charts.

FORMULAS USED IN EXCEL
Basic formulas in excel include:

Add: Used to add values of two or more cells. The (+) is used.
Subtract: Used to subtract values of two or more cells. The (-) is used.
Multiply: Used to multiply values of two or more cells. The (*) is used.
Divide: Used to divide values of two or more cells. The (/) is used.

BASIC FUNCTIONS USED IN EXCEL
Functions are used to automate tasks normally performed by formulas in excel. Some popular and basic functions include:

AVERAGE: It sums up and calculate the mean of values within a range given. For example,=AVERAGE(A1:A15).
SUM: It adds up a range of cells. For example,=SUM(A1:A15).
IF: Used to arrange values based on a logical test. The syntax for IF ; =IF(logical_test, value_if_true, [value_if_false]). For example, =IF(N2>30, "young", "old").
COUNTIF: Used to return the number of cells that meeet a certain criteria. The syntax is; =COUNTIF(range, criteria). For example, =COUNTIF(A1:A27, "Nairobi"). NOTE: COUNTIFS function counts the number of cells that meet multiple criteria.
VLOOKUP: Allows one to search for a value on a spreadsheet. Its syntax ; =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). For example, =VLOOKUP(300,A1:A20, 3, "False").

CONCLUSION
In conclusion Excel's versatility makes it an efficient tool for quick analysis and reporting. Its availability allows users to efficiently handle large data sets through processes such as performing calculations and creating meaningful visuals.

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

Mark Glemba — Tue, 31 Mar 2026 19:13:12 +0000

DATA MODELLING
This is a detailed process that involves creating a visual representation of data and its relationships.

TYPES OF TABLES
FACT TABLE
It contains quantitative data for analysis.
DIMENSIONS TABLE
Provides context to fact table data.
STAR SCHEMA
It contains a single fact table in the center that connects to multiple other dimensiontables.

SNOW FLAKE
Is an extension of a star schema where dimension tables are broken down into subdimensions.

FLAT TABLE
A table that displays data in a simple, two-dimensional format without any relationships to other tables.

SQL JOINS IN POWER BI

INNER JOIN: Returns rows present in both tables if there is a match.
LEFT JOIN: Returns all rows present in the left table and matching rows from the right table.
RIGHT JOIN: Returns matching rows from the left table and all rows present in the SQL right table.
FULL OUTER: Returns all rows present in both right and left tables.
LEFT ANTI: Returns rows from the left table that don't have matches in the right table.
RIGHT ANTI:Returns rows from the right table that don't have matches in the left table.

POWER BI RELATIONSHIPS

One-to-Many (1:M)
One row in table A matches many in table B. For example,

Many-to Many (M:M)
Here one is recommended to use a bridge table.

0ne-to-One (1:1)
One row in table A matches one in table B. For example,

Many-to-One (M:M)
Many rows in table A match one in table B. For example,

Active and Inactive relationships
The active relationship is used by default in reports and calculations, while the inactive relationship is not used unless specified. It is also useful for alternate paths such as multiple dates.

DIFFERENCE BETWEEN JOINS AND RELATIONSHIPS
Joins; combine tables based on a condition, result in new tables with combined columns and are used in power query steps. Relationships; define connections between tables in a data model and are used for; filtering, calculations and visuals across tables.