Ibrahim0695

Posted on Apr 12

Understanding DDL and DML in Database Management

#beginners #database #sql #tutorial

When working with databases, understanding the difference between Data Definition Language (DDL) and Data Manipulation Language (DML) is fundamental. These two categories of SQL commands serve different purposes but work together to manage and utilize data effectively.

What are DDL and DML?

Data Definition Language (DDL) refers to SQL commands that define or modify database structure. These commands create, alter, and delete database objects like tables, indexes, and schemas. DDL changes are permanent and auto-commit in most database systems.

Data Manipulation Language (DML) refers to SQL commands that manipulate data within existing database objects. These commands allow you to insert, update, delete, and retrieve data from tables.

Key Difference

DDL	DML
Defines structure	Manipulates data
Auto-commits	Requires explicit COMMIT
Examples: CREATE, ALTER, DROP	Examples: INSERT, UPDATE, DELETE

CREATE – Building the Foundation

The CREATE statement establishes new database objects. In my assignment, I used CREATE to set up tables with proper structure:

create table students(
    student_id INT primary key,
    first_name VARCHAR(50) not null,
    last_name VARCHAR(50) not null,
    email VARCHAR(100) unique,
    gender VARCHAR(1),
    date_of_birth date,
    class varchar(10),
    city varchar(50)
 );

This creates a students table with defined columns and data types. The PRIMARY KEY constraint ensures each student has a unique identifier.

INSERT – Adding Data

INSERT populates tables with new records. I used INSERT to add student information:

For multiple records:

insert into students (student_id, first_name, last_name, gender , date_of_birth, class, city)
values 
    (1, 'Amina', 'Wanjiku','F','2008-03-12', 'Form 3','Nairobi'),
    (2, 'Brian', 'Ochieng','M','2007-07-25', 'Form 4','Mombasa'),
    (3, 'Cynthia', 'Mutua','F','2008-11-05', 'Form 3','Kisumu'),
    (4, 'David', 'Kamau','M','2007-02-18', 'Form 4','Nairobi'),
    (5, 'Esther', 'Akinyi','F','2009-06-30', 'Form 2','Nakuru'),
    (6, 'Felix', 'Otieno','M','2009-09-14', 'Form 2','Eldoret'),
    (7, 'Grace', 'Mwangi','F','2008-01-22', 'Form 3','Nairobi'),
    (8, 'Hassan', 'Abdi','M','2007-04-09', 'Form 4','Mombasa'),
    (9, 'Ivy', 'Chebet','F','2009-12-01', 'Form 2','Nakuru'),
    (10, 'James', 'Kariuki','M','2008-08-17', 'Form 3','Nairobi');

UPDATE – Modifying Existing Data

UPDATE changes existing records. I used UPDATE to correct or modify data:

update students
set city = 'Nairobi'
where student_id = 5;

This changes Akinyi's city from Nakuru to Nairobi. The WHERE clause ensures only specific records are updated.

DELETE – Removing Data

DELETE removes records from a table:

delete from exam_results
where result_id = 9;

This removes results of result_id 9 from exam_reults table. Without WHERE, DELETE removes ALL records, so WHERE is critical.

Filtering with WHERE

The WHERE clause filters records based on conditions. I used various operators:

Equality (=)

select * from students
where class= 'Form 4';

Greater Than or equal to(>=)

select * from exam_results
where marks >= 70;

BETWEEN (Range)

select * from exam_results
where marks between 50 and 80;

IN (Multiple Values)

select *  from students
where city in ('Nairobi','Mombasa', 'Kisumu');

LIKE (Pattern Matching)

select * from subjects
where subject_name like '%Studies';

This finds all students whose first name starts with 'J'.

CASE WHEN – Transforming Data

CASE WHEN creates conditional logic to transform data. I used it to categorize students:

select
    first_name,
    last_name,
    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;

results

first_name	last_name	student_level
Amina	Wanjiku	Senior
Brian	Ochieng	Senior
Cynthia	Mutua	Senior
David	Kamau	Senior
Felix	Otieno	Junior
Grace	Mwangi	Senior
Hassan	Abdi	Senior
Ivy	Chebet	Junior
James	Kariuki	Senior
Esther	Akinyi	Junior

CASE WHEN is powerful for creating new columns based on conditions without altering original data.

My Reflection

This week I found WHERE clause operators particularly interesting. Understanding how LIKE, BETWEEN, and IN work together made filtering data much more efficient. The challenge was remembering that WHERE affects UPDATE and DELETE statements permanently, so I had to be careful with my queries.

Key takeaway: DDL builds the structure while DML populates and manipulates it. Both are essential for effective database management.

DEV Community