Understanding SQL Basics: DDL, DML, Filtering, and Data Transformation

This week I was focused on building a solid foundation in SQL by working with student, subject, and exam result data. Along the way, I explored key concepts like DDL, DML, filtering using WHERE, and transforming data using CASE WHEN. Here’s a summary of what I learned.

What are DDL and DML?

SQL commands are broadly categorized into two main types:

1. DDL (Data Definition Language)

DDL is used to define and manage the structure of a database.

Examples include:

• CREATE – used to create tables
• ALTER – used to modify table structure
• DROP – used to delete tables

Example:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    gender CHAR(1),
    date_of_birth DATE,
    class VARCHAR(10),
    city VARCHAR(50)
);

In simple terms, DDL defines how the database looks.

2. DML (Data Manipulation Language)

DML is used to manage and manipulate the data inside the tables.

Examples include:

• INSERT – add new data
• UPDATE – modify existing data
• DELETE – remove data

Example:

INSERT INTO students (student_id, first_name, last_name)
VALUES (1, 'Amina', 'Wanjiku');

DML focuses on the data itself.

USING CREATE, INSERT, UPDATE, and DELETE
I used CREATE to define tables like students, subjects, and exam_results.
I used INSERT to populate tables with records extracted from provided data.
Although not heavily used, UPDATE can be applied to change values,
for example:

UPDATE students
SET city = 'Nairobi'
WHERE student_id = 1;
DELETE removes records:
DELETE FROM students
WHERE student_id = 10;

These commands helped me understand how databases are built and maintained step by step.

Filtering Data Using WHERE
The WHERE clause is used to filter records based on conditions.

These are some of operators used:
= → exact match

→ greater than
BETWEEN → range filtering
IN → multiple values
LIKE → pattern matching

Examples:

-- Exact match
SELECT * FROM students WHERE class = 'Form 4';

-- Range
SELECT * FROM exam_results WHERE marks BETWEEN 50 AND 80;

-- Multiple values
SELECT * FROM students WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

-- Pattern matching
SELECT * FROM students WHERE first_name LIKE 'A%';

This is one of the most powerful parts of SQL because it allows precise data retrieval.

Transforming Data Using CASE WHEN
CASE WHEN allows us to create new columns based on conditions. It works like an "if-else" statement in programming.

Example:

SELECT 
    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;

This helped convert raw numeric data into meaningful categories.
CASE WHEN is very useful for reporting and data interpretation.

Reflection
This week was both challenging and interesting.
What I found challenging:
Understanding when to use operators like IN vs BETWEEN
Writing clean and error-free queries, most of my queries were to be corrected before running.

Seeing how raw data can be transformed into meaningful insights on the other hand was very interesting.

Conclusion
This week helped me understand the basics of working with databases:

• DDL defines structure
• DML manipulates data
• WHERE filters results
• CASE WHEN transforms data

These are essential building blocks for any developer working with databases. I’m looking forward to learning more advanced SQL concepts like joins and aggregations next and I will share it all here.