Understanding SQL: DDL, DML, and Data Transformation

Introduction

Structured Query Language (SQL) is the standard language for managing and manipulating relational databases.

This article explores:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Filtering with WHERE
• Conditional logic using CASE WHEN

---

What Are DDL and DML?

SQL commands are broadly categorized into two groups: DDL and DML.

Data Definition Language (DDL)

DDL commands define and modify the structure of database objects. They shape of your database—creating tables, altering columns, and removing schemas. DDL operations take effect immediately and permanently.

Command	Purpose
CREATE	Build new database objects (schemas, tables, columns)
ALTER	Modify existing structures
DROP	Delete objects entirely
RENAME	Change object names

Data Manipulation Language (DML)

DML commands manage the data inside tables, adding rows, updating values, and deleting records. Unlike DDL, DML changes can be rolled back using transactions if something goes wrong.

Command	Purpose
INSERT	Add new rows to a table
UPDATE	Modify existing data
DELETE	Remove specific rows
SELECT	Retrieve data (sometimes classified as DQL)

The Key Difference

Aspect	DDL	DML
Focus	Structure	Data
Commands	CREATE, ALTER, DROP, RENAME	INSERT, UPDATE, DELETE, SELECT
Reversibility	Permanent (auto-commit)	Can be rolled back
Example	Adding a new column	Changing a student's grade

---

DDL in Action: Building the Nairobi Academy Database

In the Nairobi Academy assignment, DDL commands established the entire database framework from scratch.

Creating the Schema and Tables

The first step was creating a dedicated schema to organize all school-related tables:

sql
CREATE SCHEMA nairobi_academy;
SET search_path TO nairobi_academy;
Then three tables were built using CREATE TABLE:
sql
-- Students table stores pupil information
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    class VARCHAR(20),
    city VARCHAR(50),
    date_of_birth DATE
);

-- Subjects table stores courses offered
CREATE TABLE subjects (
    subject_id SERIAL PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    credits INTEGER
);

-- Exam_results links students to their scores
CREATE TABLE exam_results (
    result_id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES students(student_id),
    subject_id INTEGER REFERENCES subjects(subject_id),
    marks INTEGER CHECK (marks >= 0 AND marks <= 100),
    exam_date DATE
);
Modifying Structure with ALTER
Real-world databases evolve. When the school forgot to include phone numbers, ALTER TABLE resolved it:
sql
Copy
ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);
Later, the credits column needed clearer naming:
sql

ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;
When the requirement changed again, the column was removed:
sql

ALTER TABLE students DROP COLUMN phone_number;
These ALTER operations demonstrate DDL's flexibility—structures adapt without rebuilding everything.
DML in Action: Populating and Managing Data
Once tables existed, DML commands brought them to life with real student records.
INSERT: Adding Data
Ten students, ten subjects, and ten exam results were inserted:
sql

INSERT INTO students (first_name, last_name, gender, class, city, date_of_birth) 
VALUES ('James', 'Mwangi', 'M', 'Form 4', 'Nairobi', '2006-03-15');
The INSERT statement follows a clear pattern: specify the table, list columns, then provide values in matching order. Bulk inserts use comma-separated value sets.
UPDATE: Correcting Data
When Esther Akinyi moved from Nakuru to Nairobi, UPDATE reflected this change:

sql
UPDATE students 
SET city = 'Nairobi' 
WHERE student_id = 5;
A marks entry error was also fixed:

sql

UPDATE exam_results 
SET marks = 59 
WHERE result_id = 5;
Critical rule: Always use WHERE with UPDATE. Without it, every row changes.
DELETE: Removing Data
When an exam was cancelled, DELETE removed it cleanly:
sql

DELETE FROM exam_results 
WHERE result_id = 9;
Again, WHERE is essential—omitting it empties the entire table.
Filtering Data with WHERE
The WHERE clause is SQL's gatekeeper. It filters rows based on conditions, ensuring queries return only relevant data.
Basic Comparison Operators
Table
Operator    Meaning Example
=   Equal to    WHERE class = 'Form 4'
>   Greater than    WHERE marks > 70
<   Less than   WHERE marks < 40
>=  Greater than or equal   WHERE marks >= 70
<=  Less than or equal  WHERE marks <= 50
<> or !=    Not equal   WHERE city <> 'Nairobi'
Logical Operators
Combine conditions with AND and OR:

sql
-- Form 3 students from Nairobi (both conditions must be true)
SELECT * FROM students WHERE class = 'Form 3' AND city = 'Nairobi';

-- Students in Form 2 or Form 4 (either condition can be true)
SELECT * FROM students WHERE class = 'Form 2' OR class = 'Form 4';
Special Operators
BETWEEN checks ranges inclusively:

sql
-- Marks from 50 to 80, including both endpoints
SELECT * FROM exam_results WHERE marks BETWEEN 50 AND 80;
IN checks membership in a list:

sql

-- Students in any of these three cities
SELECT * FROM students WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');
N IN excludes values:

sql

-- Students in Form 1 or Form 4 only
SELECT * FROM students WHERE class NOT IN ('Form 2', 'Form 3');

LIKE enables pattern matching with wildcards:
Table
Pattern Matches
'A%'    Starts with 'A'
'%Studies%' Contains 'Studies' anywhere
'_a%'   Second letter is 'a'

sql

-- First names starting with A or E
SELECT * FROM students 
WHERE first_name LIKE 'A%' OR first_name LIKE 'E%';
Transforming Data with CASE WHEN
Raw data often needs interpretation before it becomes useful. CASE WHEN acts as SQL's if-then-else logic, creating new calculated columns based on conditions.
Grading Exam Results
Instead of displaying raw marks, the assignment labeled each score with a performance grade:
sql

SELECT 
    result_id,
    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;
SQL evaluates conditions top to bottom. A mark of 85 hits the first condition (>= 80) and becomes "Distinction"—it never reaches the >= 60 check. This ordering is crucial.
Categorizing Students
Students were grouped into academic levels:
sql

SELECT 
    first_name,
    last_name,
    class,
    CASE 
        WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
        WHEN class IN ('Form 1', 'Form 2') THEN 'Junior'
    END AS student level
FROM students;
Every CASE must end with END, and AS names the new column.    

Inconclusion, DDL builds the container, while DML fills and shapes the content. The Nairobi Academy data, demonstrated this relationship—CREATE and ALTER established tables, then INSERT, UPDATE, and DELETE managed student records. Filtering with WHERE and operators like BETWEEN, IN, and LIKE narrowed results to specific needs. Finally, CASE WHEN transformed numerical marks into meaningful categories, proving that SQL is not just about storing data—it's about making data understandable.
Mastering these basics prepares you for;
*joins
*windows functions
*Query optimization
*subqueries.
 In SQL real understanding comes from writing queries and lots of practice.