DEV Community: Michael Muthama

Understanding SQL: DDL,DML,Filtering and DCL.

Michael Muthama — Mon, 13 Apr 2026 06:55:12 +0000

SQL is an abbreviation for Standard Query Language. It is a programming language that is used to manage and work with data in databases.

Sql is a very important tool as it is used in all types of industries be it Banks, Hospitals, Schools, Mpesa. All these have databases that work behind the scenes
DBMS stands for a Database Management System which is a software used to create, manage and interact with database e.g Postgresql which i use in my class projects. We also have others like Mysql, IBM DB2,Mongo db e.t.c, There are two types of DBMS

Think of it this way; DDL builds the house, DML fills it with furniture.

In my current project, the main commands I used were CREATE, INSERT, UPDATE, and DELETE and I will take you through the 'how' shortly.

I used CREATE to create a schema called nairobi_academy, then CREATE TABLE to build three tables: students, subjects, and exam_results;

I also used ALTER TABLE to modify tables after creation, that is; adding a phone_number column, renaming credits to credit_hours, and dropping phone_number when it was no longer needed. This taught me that SQL is flexible and you can always adjust a table's structure even after it has been created.

Next, I used INSERT command, where I added 10 students, 10 subjects, and 10 exam results into the database -see example below;

I then used UPDATE command and corrected data that had changed. For instance; updating Esther's city from Nakuru to Nairobi, and fixing incorrect marks from 49 to 59.

In addition, I used DELETE command to remove a cancelled exam result from the table.

NB: The most important lesson with UPDATE and DELETE is that, always use a WHERE clause, else every single row in the table gets affected and will be deleted.

Another key lesson has been how to use SQL filtering functions e.g., WHERE, SEARCH, IN, NOT IN, COUNT & CASE WHEN.

The WHERE clause filters rows based on conditions while CASE WHEN works like an if-else statement. It creates new labels based on conditions without changing the original.

The biggest challenge this week was managing errors when running scripts multiple times. Some errors I encountered today was "relation already exists" and it kept appearing till it got frustrating. I learned that using IF NOT EXISTS and DROP SCHEMA CASCADE solves this cleanly.

I also learned the hard way that column names matter. For instance; renaming credits to credit_hours after inserting data caused errors because the INSERT still referenced the old name. Order really matters in SQL! Do not give up when creating and querying data in SQL, remain calm, research on how to debug and you've got it!

“Power BI Essentials: Tools, Functions, and Real-World Applications”

Michael Muthama — Sun, 29 Mar 2026 18:57:11 +0000

Understanding Power BI and Its Functions

In today’s data-driven world, organizations rely heavily on tools that can transform raw data into meaningful insights. One of the most powerful tools for this purpose is Microsoft Power BI. It is a business intelligence (BI) platform that enables users to connect, analyze, visualize, and share data efficiently.

What is Power BI?

Microsoft Power BI is a suite of tools developed by Microsoft that helps users create interactive dashboards and reports. It is widely used by businesses, analysts, and students to turn complex datasets into clear visual insights.

Power BI consists of several components:

Power BI Desktop – for creating reports
Power BI Service – for sharing and collaboration (cloud-based)
Power BI Mobile – for accessing reports on mobile devices

Key Features of Power BI

1. Data Connectivity

Power BI can connect to a wide range of data sources, including:

Excel files
Databases (SQL Server, Oracle)
Cloud services (Google Analytics, Azure)
Web data sources

This flexibility allows users to gather data from multiple places into one unified system.

2. Data Transformation (Power Query)

Power BI includes a powerful data cleaning tool called Power Query. It helps you:

Remove duplicates
Handle missing values
Filter and sort data
Merge and reshape datasets

This ensures your data is accurate and ready for analysis.

3. Data Modeling

Data modeling allows you to define relationships between tables. Power BI uses:

Relationships (one-to-many, many-to-one)
Calculated columns
Measures

This step is essential for building meaningful reports.

Important Functions in Power BI (DAX)

Power BI uses a formula language called DAX (Data Analysis Expressions) to perform calculations. Below are some commonly used functions:

1. SUM Function

Adds up values in a column:

Total Sales = SUM(Sales[Amount])

2. AVERAGE Function

Calculates the average of a column:

Average Sales = AVERAGE(Sales[Amount])

3. COUNT Function

Counts the number of rows:

Total Orders = COUNT(Sales[OrderID])

4. IF Function

Performs logical tests:

Profit Status = IF(Sales[Profit] > 0, "Profit", "Loss")

5. CALCULATE Function

One of the most powerful DAX functions, used to modify filters:

Total Sales in 2025 = CALCULATE(SUM(Sales[Amount]), Year = 2025)

6. FILTER Function

Filters data based on conditions:

High Sales = FILTER(Sales, Sales[Amount] > 1000)

7. RELATED Function

Fetches related data from another table:

Customer Name = RELATED(Customers[Name])

Data Visualization in Power BI

One of the standout features of Microsoft Power BI is its ability to create stunning visualizations such as:

Bar charts
Line graphs
Pie charts
Maps
Dashboards

These visuals make it easier to identify trends, patterns, and insights.

Advantages of Power BI

User-friendly interface – easy for beginners
Real-time data updates
Integration with other Microsoft tools like Excel
Powerful data analysis capabilities
Interactive dashboards

Conclusion

Microsoft Power BI is a powerful and versatile tool for data analysis and visualization. By understanding its core features and functions—especially DAX—you can transform raw data into valuable insights that support better decision-making.

Whether you are a student, business professional, or data analyst, learning Power BI is a valuable skill that can significantly enhance your ability to work with data.

“From Beginner to Expert: How to Effectively Use Microsoft Excel”

Michael Muthama — Sun, 29 Mar 2026 17:40:55 +0000

Introduction
Microsoft Excel is one of the most powerful and widely used spreadsheet applications in the world. It is part of the Microsoft Office suite and is used for data entry, analysis, visualization, and automation. From students to financial analysts, Excel plays a crucial role in managing and interpreting data efficiently.

What is Microsoft Excel?
Microsoft Excel is a spreadsheet program that organizes data into rows and columns. Each intersection of a row and column is called a cell, where data can be stored, manipulated, and analyzed.

Excel allows users to:

Perform calculations
Analyze large datasets
Automate repetitive tasks

Types of Excel Formulas

Arithmetic Formulas Used for basic mathematical operations such as addition, subtraction, multiplication, and division.

Examples:

=A1 + B1 (Addition)
=A1 - B1 (Subtraction)
=A1 * B1 (Multiplication)
=A1 / B1 (Division)

Statistical Formulas Used to analyze data.

Common functions:

=AVERAGE(A1:A10) – Calculates the average
=COUNT(A1:A10) – Counts numeric values
=MAX(A1:A10) – Finds the highest value
=MIN(A1:A10) – Finds the lowest value

Logical Formulas Used to make decisions based on conditions.

Example:

=IF(A1>50, "Pass", "Fail")
This checks if the value in A1 is greater than 50.

Text Formulas Used to manipulate text.

Examples:

=CONCAT(A1, " ", B1) – Combines text
=LEFT(A1, 4) – Extracts first 4 characters
=RIGHT(A1, 3) – Extracts last 3 characters

Date and Time Formulas Used to work with dates and times.

Examples:

=TODAY() – Returns current date
=NOW() – Returns current date and time
=DATEDIF(A1, B1, "Y") – Calculates years between dates

Lookup and Reference Formulas Used to search for data.

Examples:

=VLOOKUP(A2, A1:B10, 2, FALSE)
=HLOOKUP(A2, A1:D5, 2, FALSE)
=INDEX(A1:B10, 2, 1)
=MATCH("John", A1:A10, 0)

Benefits of Using Excel Formulas
Accuracy: Reduces human error in calculations
Efficiency: Saves time on repetitive tasks
Automation: Automatically updates results when data changes
Data Analysis: Helps uncover trends and insights