DDL and DML

 

Data Definition Language (DDL)

The Data Definition Language (DDL) is the part of SQL used to define, modify, and remove the structure of database objects. It focuses on the schema of the database, not on the actual data stored in it.

Purpose of DDL

DDL is used to:

• Define database schemas

• Create and delete relations (tables)

• Modify table structures

• Specify integrity constraints

• Define views

Common DDL Commands

Command	Description
CREATE	Creates database objects such as tables, views, and indexes
ALTER	Modifies the structure of an existing table
DROP	Deletes database objects permanently
TRUNCATE	Removes all tuples from a table (structure remains)

Example (Conceptual)

CREATE TABLE EMPLOYEE (
  Ssn CHAR(9) PRIMARY KEY,
  Fname VARCHAR(20),
  Lname VARCHAR(20),
  Salary DECIMAL(10,2)
);

This statement:

• Defines the schema of the EMPLOYEE relation

• Specifies attributes and data types

• Declares a primary key constraint

Key Characteristics of DDL

• Operates on database structure

• Statements have permanent effects

• Automatically committed in most DBMSs

• Includes integrity constraint definitions

---

Data Manipulation Language (DML)

The Data Manipulation Language (DML) is the part of SQL used to retrieve and modify the data stored in the database. It operates on the tuples (rows) within relations.

Purpose of DML

DML is used to:

• Query data from relations

• Insert new tuples

• Delete existing tuples

• Modify existing tuples

Common DML Commands

Command	Description
SELECT	Retrieves data from one or more relations
INSERT	Adds new tuples to a relation
DELETE	Removes tuples from a relation
UPDATE	Modifies existing tuples

Example Queries

Retrieving data

SELECT Fname, Lname
FROM EMPLOYEE
WHERE Salary > 50000;

Inserting data

INSERT INTO EMPLOYEE
VALUES ('123456789', 'John', 'Smith', 60000);

Updating data

UPDATE EMPLOYEE
SET Salary = Salary + 5000
WHERE Ssn = '123456789';

Deleting data

DELETE FROM EMPLOYEE
WHERE Salary < 30000;

Key Characteristics of DML

• Operates on data, not structure

• Can be transaction-controlled (COMMIT, ROLLBACK)

• May violate integrity constraints (such operations are rejected)

• Most frequently used by end users and applications

---

DDL vs DML: Summary Comparison

Aspect	DDL	DML
Focus	Database structure	Database contents
Affects	Schema	Tuples
Examples	CREATE, ALTER, DROP	SELECT, INSERT, UPDATE, DELETE
Transaction control	Usually auto-commit	Supports COMMIT/ROLLBACK
Used by	Database designers/administrators	Users and applications

---

• DDL: The part of SQL used to define and modify the structure of database objects.

• DML: The part of SQL used to retrieve, insert, delete, and update data in relations.