DBMS Tutorial: Understanding DDL and DML with Examples

DBMS Tutorial – Database Languages: DDL and DML

Database Management Systems (DBMS) use database languages to manage and manipulate data. These languages connect users, applications, and the database engine. This tutorial focuses on Data Definition Language (DDL) and Data Manipulation Language (DML), their commands, functionalities, and example queries.

---

1. Data Definition Language (DDL)

Definition: DDL is used to define, modify, or remove database structures such as tables, schemas, and indexes. It does not manipulate data, only structures.

Common DDL Commands

Command	Functionality	Example Query
CREATE	Creates a new database object	CREATE TABLE Employee(Emp_id INT PRIMARY KEY, Name VARCHAR(50), Department VARCHAR(50));
ALTER	Modifies an existing database object	ALTER TABLE Employee ADD Salary DECIMAL(10,2);
DROP	Deletes a database object permanently	DROP TABLE Employee;

Example Queries

-- Create a table
CREATE TABLE Employee(
    Emp_id INT PRIMARY KEY,
    Name VARCHAR(50),
    Department VARCHAR(50)
);

-- Alter a table
ALTER TABLE Employee
ADD Salary DECIMAL(10,2);

-- Drop a table
DROP TABLE Employee;

Functionality: DDL defines the structure of the database. Changes are auto-committed and do not require explicit transactions.

---

2. Data Manipulation Language (DML)

Definition: DML is used to manipulate data stored in database tables. It includes inserting, updating, deleting, and retrieving data.

Common DML Commands

Command	Functionality	Example Query
INSERT	Adds new records into a table	INSERT INTO Employee VALUES(101,'Arifa','IT',55000);
UPDATE	Modifies existing records	UPDATE Employee SET Salary=60000 WHERE Emp_id=101;
DELETE	Removes records from a table	DELETE FROM Employee WHERE Emp_id=101;
SELECT	Retrieves data from tables	SELECT Name, Department FROM Employee;

Example Queries

-- Insert data
INSERT INTO Employee(Emp_id, Name, Department, Salary)
VALUES (101, 'Arifa', 'IT', 55000);

-- Update data
UPDATE Employee
SET Salary = 60000
WHERE Emp_id = 101;

-- Delete data
DELETE FROM Employee
WHERE Emp_id = 101;

-- Select data
SELECT Name, Department, Salary
FROM Employee
WHERE Department='IT';

Functionality: DML manipulates the data in tables. Changes can be committed or rolled back using transaction control commands.

---

3. Summary: DDL vs DML

Feature	DDL	DML
Purpose	Defines database structure	Manipulates data in tables
Commands	CREATE, ALTER, DROP	INSERT, UPDATE, DELETE, SELECT
Transaction	Auto-committed	Can be rolled back
Example	CREATE TABLE Employee(...)	INSERT INTO Employee VALUES(...)

---

4. Conclusion

DDL and DML are the core database languages in DBMS. While DDL defines the structure of databases, DML handles data within these structures. Together, they enable database developers and administrators to create, modify, and maintain databases efficiently.