DDL and DML in SQL

SQL LEARNING SERIES

DDL and DML in SQL: Understanding Database Structure and Data Manipulation

A practical beginner-friendly guide to creating, modifying, and managing data in relational databases.

If you're learning Data Analytics, Data Science, or Database Management, SQL is one of the most important skills to develop.

SQL allows us to communicate with relational databases ,from creating tables to inserting, updating, retrieving, and deleting data.

In this article, we'll explore two fundamental concepts:

• DDL — Data Definition Language
• DML — Data Manipulation Language

We'll also work through a practical Student Management System step by step.

What is DDL?

DDL stands for Data Definition Language.

DDL is used to create, modify, and remove the structure of database objects, such as tables.

Think of DDL as the architect of a database.

Before we store data, we need to create a structure that tells the database how our information should be organized.

Common DDL Commands

Command	Purpose
CREATE	Creates a database object
ALTER	Modifies an existing object
DROP	Removes a database object
TRUNCATE	Removes all records while keeping the table structure

DDL Procedure

Let's create a simple student database.

1. Create a Database

CREATE DATABASE school_db;
This creates a database called school_db.

If you are using MYSQL,you can select the database with;

USE school_db;

USE is supported by MySQL and some other database systems. PostgreSQL handles database selection through the connection rather than a USE statement.

2. Create a Table

Now let's create a students table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    course VARCHAR(100),
    age INT
);

The table contains;

Column	Description
student_id	Unique ID for each student
student_name	Student's name
course	Student's course
age	Student's age

3. Modify a Table Using ALTER

Instead of recreating the table, we can modify it using ALTER.

ALTER TABLE students
ADD email VARCHAR(150);

Our table will now contain;
student_id
student_name
course
age
email
This is still DDL because we are changing the structure of the table.

4. Remove All Records Using TRUNCATE

If we want to remove all records but keep the table structure.

TRUNCATE TABLE students;

use TRUNCATE
carefully, especially when working with important or production data.

5. Remove a Table Using DROP

If we no longer need the table, we can remove it completely.

DROP TABLE students;

DROP. It is a destructive operation and can result in permanent data loss if you don't have a backup.

What is DML?

DML stands for Data Manipulation Language.

While DDL deals with the structure of a database, DML deals with the data stored inside that structure.

Common DML Commands

Command	Purpose
INSERT	Adds new records
UPDATE	Modifies existing records
DELETE	Removes records

SELECT is commonly taught alongside DML, although it is more precisely classified as DQL (Data Query Language) in many SQL classifications.

DML Procedure

1. Insert Data

We can add a student using INSERT.

INSERT INTO students
    (student_id, student_name, course, age, email)
VALUES
    (1, 'Brian', 'ICT', 22, '[e-mail]');

You can insert more than one data at once.

INSERT INTO students
    (student_id, student_name, course, age, email)
VALUES
    (1, 'Brian', 'ICT', 22, '[e-mail]'),
    (2, 'Jane', 'Data Science', 21, '[e-mail]'),
    (3, 'Kevin', 'Business', 24, '[e-mail]');

2. Retrieve Data Using SELECT

Although SELECT is technically DQL, it is an essential part of working with the data we have entered.

SELECT *
FROM students;

3. Update Data

We can use UPDATE to updte a record in the table. If brian is now 23years old.

UPDATE students
SET age = 23
WHERE student_id = 1;

4. Delete Data

If brian leaves school,the record needs to be deleted

DELETE FROM students
WHERE student_id = 3;

The WHERE clause is important or else you would delete the entire record in the table.

DELETE FROM students;

DDL vs DML

text
              SQL
               |
        +------+------+
        |             |
       DDL           DML
        |             |
    Structure        Data
        |             |
     CREATE          INSERT
     ALTER           UPDATE
     DROP            DELETE
     TRUNCATE

DDL defines and modifies the datadase structure while DML manipulates the data stored in the database.

Final Thoughts

Learning SQL isn't just about memorizing commands.

It's about understanding how data is structured, stored, manipulated, and retrieved.

DDL gives us the tools to build the foundation of a database, while DML gives us the tools to work with the information stored inside it.

Once you understand these fundamentals, you're ready to explore more powerful SQL concepts such as:

1. SQL JOINs
2. Subqueries
3. CTEs
4. Aggregate Functions
5. GROUP BY
6. HAVING
7. Data Analysis Queries

Next in the SQL Learning Series
Part 2: SQL JOINs — Combining Data from Multiple Tables

In the next article, we'll explore how to connect tables and retrieve meaningful information from multiple data sources.