Understanding DDL and DML Statements in SQL

In database management, SQL (Structured Query Language) is an essential tool that allows users to interact with databases. SQL is divided into several sub-languages, two of the most critical being Data Definition Language (DDL) and Data Manipulation Language (DML). This blog will explore the differences between DDL and DML, their respective statements, and their roles in database management.

What is DDL?

Data Definition Language (DDL) refers to the set of SQL commands used to define and manage all structures in a database. DDL statements deal primarily with the database's schema or structure, focusing on creating, altering, and deleting database objects such as tables and indexes.

Common DDL Statements:

1. CREATE: Creates new database objects.

   CREATE TABLE Employees
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
HireDate DATE
);

---

2. ALTER: Modifies existing database objects.

ALTER TABLE Employees ADD COLUMN Email VARCHAR(100);

---

3. DROP: Removes a database object permanently.

DROP TABLE Employees;

---

4. TRUNCATE: Deletes all rows in a table but retains the structure.

TRUNCATE TABLE Employees;

What is DML?

Data Manipulation Language (DML) refers to the subset of SQL commands used to manipulate data within existing database objects. DML statements allow users to retrieve, insert, update, and delete data.

Common DML Statements:

1. SELECT: Retrieves data from one or more tables.

SELECT * FROM Employees WHERE HireDate > '2020-01-01';

---

2. INSERT: Adds new rows of data to a table.

INSERT INTO Employees (FirstName, LastName, HireDate, Email)
VALUES ('John', 'Doe', '2023-10-01', 'john.doe@example.com');

---

3. UPDATE: Modifies existing data within a table.

UPDATE Employees SET Email = 'john.new@example.com' WHERE EmployeeID = 1;

---

4. DELETE: Removes existing rows from a table.

DELETE FROM Employees WHERE EmployeeID = 1;

Differences Between DDL and DML:

Let’s take an example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Position VARCHAR(50),
    Salary DECIMAL(10, 2) CHECK (Salary >= 0)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate, Email, Position, Salary) VALUES
(1, 'John', 'Doe', '2023-01-15', 'john.doe@example.com', 'Developer', 75000.00),
(2, 'Jane', 'Smith', '2022-03-10', 'jane.smith@example.com', 'Manager', 90000.00),
(3, 'Alice', 'Johnson', '2021-06-22', 'alice.johnson@example.com', 'Designer', 65000.00),
(4, 'Bob', 'Brown', '2020-11-30', 'bob.brown@example.com', 'Analyst', 55000.00);


UPDATE Employees 
SET Salary = 80000.00 
WHERE EmployeeID = 1;
SELECT * FROM Employees;


DELETE FROM Employees 
WHERE EmployeeID = 4;

DROP TABLE Employees;

After Updating the Employee's Salary:

If you execute UPDATE Employees SET Salary = 80000.00 WHERE EmployeeID = 1;, followed by SELECT * FROM Employees;, the output will now show:

After Deleting an Employee Record

If you execute DELETE FROM Employees WHERE EmployeeID = 4;, followed by SELECT * FROM Employees;, the output will be:

Conclusion:

Understanding the difference between DDL and DML is crucial for anyone working with databases. While DDL focuses on the structure and schema of the database, DML is concerned with the data itself. Both play vital roles in managing and utilizing databases in real-world applications.