Forem: James Njoroge

SQL for Beginners: Essential Concepts Made Simple

James Njoroge — Sun, 19 Apr 2026 15:19:22 +0000

SQL stands for Structured Query Language. It is the standard language used to communicate with databases. Think of SQL as the "English" you speak to a database when you want to get information, add new data, update records, or delete something. SQL allows you to do 4 main things (often remembered as CRUD) where C represents Create, R represents READ while U represents Update and D represents Delete. However the SQL keyword for Create is INSERT, for Read is SELECT, for Update is UPDATE and for Delete is DELETE. SQL also useful in filtering data(WHERE), sorting data(ORDER BY), grouping data(GROUP BY, AVG, COUNT), Combining tables(JOIN) and advanced analysis(NTILE)

Learning SQL can be made simple by breaking down learning into key concepts making it much easier to understand and grasp how it works. In this guide, we'll cover fundamental topics that every beginner should know which include JOINS, compare Window Functions and GROUP BY and cover SQL functions every beginner should know.

SQL JOINs

A JOIN combines rows from two or more tables based on a related column. The tables in the join must have a column they share in common. There are different types of JOINS and each is unique based on the rows needed from the different tables. The joins include;

INNER JOIN - returns only the rows that have matching values in both tables and row without a match are excluded from the result.
LEFT JOIN (or LEFT OUTER JOIN) - Returns all rows from the left table plus matching rows from the right table. If no match exists, it returns NULL for columns from the right table.
RIGHT JOIN (or RIGHT OUTER JOIN) - Returns all rows from the right table + matching rows from the left table. Opposite of LEFT JOIN.
FULL OUTER JOIN (or FULL JOIN) - returns all rows from both tables. Matches where possible and puts NULL where there is no match.

Window Functions vs GROUP BY – What is the Difference?

SQL window functions and GROUP BY both perform calculations across related rows, but they solve different problems. GROUP BY summarises data by collapsing rows into one result per group, while Window Functions calculate values across a set of rows without removing the original row details. GROUP BY is commonly used for aggregation and reporting while Window Functions are for analysis while keeping detail. They let you calculate things like rank, totals, averages and still show each original row.

With GROUP BY, the output becomes smaller because rows are combined into groups. That means you usually cannot select non-grouped columns unless they are aggregated too. With window functions, the output keeps the same number of rows as the input, because the calculation is attached to each row using OVER(...). This makes it useful when you want both the row detail and the aggregated context in the same result.

SQL Functions Every Beginner Should Know

Here are the five functions I recommend learning first:

ROUND(number, decimals) - Rounds numbers cleanly. ROUND(mark, 2) → 87.345 becomes 87.35
CONCAT() or || (PostgreSQL string concatenation)Joins text together.first_name || ' ' || last_name → “John Smith”
TO_CHAR(date, format) Formats dates beautifully.TO_CHAR(exam_date, 'Day, DDth Month YYYY') → “Friday, 15th March 2024”

SQL Fundamentals: DDL vs DML and Essential Commands

James Njoroge — Sun, 12 Apr 2026 14:51:32 +0000

SQL (Structured Query Language) is the standard language for managing and manipulating data in relational databases. Two of the most important categories in SQL are DDL (Data Definition Language) and DML (Data Manipulation Language). Understanding the difference between them is essential for anyone working with databases.

DDL vs DML: What’s the Difference?

DDL (Data Definition Language) defines the structure of the database. It deals with creating, modifying, and deleting database objects such as tables, indexes, views, and schemas. DDL commands are typically used during the design and setup phase of a database.
Common DDL commands include:

CREATE – Creates new database objects
ALTER – Modifies existing database objects
DROP – Deletes database objects
TRUNCATE – Removes all data from a table but keeps the structure

DML (Data Manipulation Language), on the other hand, is used to manage the actual data stored inside the database objects. DML commands allow you to add, modify, retrieve, and remove records.
Common DML commands include:

INSERT – Adds new records (rows) into a table
UPDATE – Modifies existing records
DELETE – Removes records
SELECT – Retrieves data

The major difference between DDL and DML is DDL changes the schema (how the data is organised), while DML works with the data itself. DDL commands usually involve more permanent changes and often require higher privileges. In many database systems, DDL statements implicitly commit the transaction, whereas DML statements can be rolled back.

Using DDL and DML

Below are examples of how to practically use DDL and DML commands

CREATE (DDL) The CREATE statement is used to define the table structures.

INSERT (DML) Once the table is created, it is possible to add data into it.
UPDATE (DML) After data is added in the table it is then possible to correct or update the records.
DELETE (DML) Used to clean up data which is no longer in use.

Filtering Data with the WHERE Clause

One of the most powerful features in SQL is the ability to filter rows using the WHERE clause. It works with SELECT, UPDATE, and DELETE statements to specify which rows should be affected. Common comparison operators used with WHERE:

= (equals) - Exact match
>,<=,<,>= (comparison operators)
BETWEEN - Checks if a value falls within a range
IN - Checks if a value matches any in a list
LIKE - Pattern matching(usually with wildcards %)

Transforming Data with CASE WHEN

The CASE WHEN expression is SQL’s way of doing if-then-else logic inside your queries. It’s very useful when you want to:

Create new columns based on conditions
Categorise your data (e.g., “Good”, “Average”, “Poor”)
Clean or transform data without modifying the original table

Below is an example of using CASE WHEN

Conclusion

Mastering DDL and DML, along with filtering techniques (WHERE) and conditional logic (CASE WHEN), provides a strong foundation for working with relational databases. DDL lets you build the house, DML lets you furnish and maintain it, WHERE helps you find exactly what you need, and CASE WHEN allows you to reshape the data intelligently.

Whether you're a student, aspiring data analyst, or backend developer, becoming comfortable with these SQL fundamentals will significantly boost your productivity and understanding of how data is managed behind the scenes.

I have found the use of CASE WHEN the most interesting part of this week's learning as it not only filters through large tables but it also creates new columns with the stated new categories.

How Excel is Used in Real-World Data Analysis

James Njoroge — Wed, 25 Mar 2026 12:31:53 +0000

Excel is a grid-based spreadsheet application developed by Microsoft for organizing, analyzing and storing data. Excel is very dynamic and provides users with a variety of functions allowing them to perform calculations using formulas, visualise data through charts and pivot tables and manage large datasets. It is used widely for business, finance and personal use alike.

One of the main uses of excel is data entry and data storage where users can store raw data over a period of time and from which they can be able to automate complex calculations, visualise trends and analyze data to get insights. An example of use of excel is a restaurant keeping a record of their daily sales from which they can be able to get insights of their revenue, profit margins, best selling meals, weekly and monthly sales. The analysis can then be used by management to make crucial decisions based on trends. Below is a an example of an excel worksheet which shows a record of sales in a few restaurant chains.

Excel uses arithmetic expressions which are formulas that calculate numerical values using basic operators like + (add), - (Subtract), * (Multiply), / (Divide). Every formula must begin with an equal sign (=) which enables you to use both raw numbers and cell references. Cell reference refers to the column number followed by the row number with no space in between (A1) which would refer to the first column and the first row and it is Order ID according to the spreadsheet above. To get more insights from large datasets it is possible to use Math functions like MIN, MAX, SUM, AVERAGE.

Excel Math functions have syntax which always starts with an equals sign and includes a range of cells which can be easily highlighted e.g =AVERAGE(D1:D28) which returns the mean of column D. =MAX(E1:E28) would bring back the highest number in the Quantity column which indicates the largest value and it the product the most sales.

Apart from arithmetic functions one can also create pivot tables and pivot charts from the worksheet which are useful in visualising trends that are clear to all stakeholders. Below is an example of a line chart derived from the dataset above and it visualises how increase in price items leading to a sharp decline in items sold as the price increases past 20€.

Learning excel thus far, has been eye opening after seeing how an easily accessible tool can be so useful and powerful in various ways depending on the end-users needs which can range from personal use to small business and even financial institutions.