Understanding SQL: DDL, DML, and data manipulation

#sql #buildinpublic #data #newbie

What are DDL and DML?

SQL commands are grouped based on what they act on.

Think of a database as a house:

DDL builds the house (structure)
DML furnishes and maintains the house (data inside it)

DDL (data definition language) — building the structure

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

It answers questions like:

What tables exist?
What columns do they have?
What type of data goes into them?

Common DDL commands

CREATE -> create new tables
ALTER -> modify existing tables
DROP -> delete tables

Example: creating a table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

Key idea

DDL works on the entire structure, not individual rows.

DML (data manipulation language) — working with the data

DML is used to interact with the actual data inside the tables.

It answers questions like:

What data do we have?
How do we add, update, or remove it?

Common DML commands

SELECT -> retrieve data
INSERT -> add data
UPDATE -> modify data
DELETE -> remove data

Examples of DML in action

Inserting data

INSERT INTO students (student_id, first_name, age, city)
VALUES (1, 'Alice', 14, 'Nakuru');

Retrieving data

SELECT * FROM students;

Updating data

UPDATE students
SET city = 'Nairobi'
WHERE student_id = 1;

Deleting data

DELETE FROM students
WHERE student_id = 1;

DDL vs DML — key differences

aspect	DDL (data definition language)	DML (data manipulation language)
Purpose	defines and manages database structure	works with the data inside tables
Focus	tables, schemas, columns	rows and records
Level of impact	affects entire objects (tables, schemas)	affects specific rows
Common commands	`CREATE`, `ALTER`, `DROP`	`SELECT`, `INSERT`, `UPDATE`, `DELETE`
Transaction behaviour	auto-committed (usually cannot rollback)	transactional (can rollback before commit)
Use of WHERE	not used (operates on full objects)	heavily used to filter specific rows
Real-world analogy	building or modifying a house	adding, changing, or removing furniture
Risk level	high (can remove entire structures)	moderate (usually scoped to selected data)

Filtering data with the where clause

The WHERE clause helps you target specific rows instead of working on everything.

Think of it as a filter.

Examples

Equality '='

SELECT * FROM students
WHERE city = 'Nairobi';

greater than '>'

SELECT * FROM exam_results
WHERE marks > 80;

between(inclusive of the numbers in the range)

SELECT * FROM exam_results
WHERE marks BETWEEN 60 AND 79;

in (multiple values)

SELECT * FROM students
WHERE class IN ('Form 2', 'Form 3');

like (pattern matching)

SELECT * FROM students
WHERE first_name LIKE 'A%';

Transforming data with CASE WHEN

The CASE WHEN statement is a powerful tool for adding conditional logic to queries, transforming raw data into more meaningful information.

For instance, I used it to convert numerical marks into a simpler way that shows the performance at a glance.

SELECT 
    student_id,
    marks,
    CASE 
        WHEN marks >= 80 THEN 'Distinction'
        WHEN marks >= 60 THEN 'Merit'
        WHEN marks >= 40 THEN  'Pass'
        ELSE 'Fail'
    END AS grade
FROM exam_results;