Introduction
Structured Query Language (SQL) is the standard language used to interact with relational databases. Whether you are working with PostgreSQL, MySQL, SQL Server, Oracle, or another relational database management system (RDBMS), SQL provides commands for creating databases and tables, inserting and modifying data, retrieving information, and managing database structures.
Two of the most important categories are:
- DDL - Data Definition Language
- DML - Data Manipulation Language
DDL is primarily concerned with the structure of a database, while DML is concerned with the data stored inside that structure.
This article explains the difference between DDL and DML and demonstrates their most important commands using practical examples.
- What Is SQL?
SQL (Structured Query Language) is a language used to communicate with relational databases.
A relational database stores information in tables consisting of:
- Rows - individual records
- Columns - attributes or fields
- Tables - collections of related records
- Relationships - connections between tables
For example, a customers table might contain:
| customer_id | first_name | last_name | |
|---|---|---|---|
| 1 | John | Kamau | john@example.com |
| 2 | Mary | Wanjiku | mary@example.com |
| 3 | Peter | Otieno | peter@example.com |
SQL allows us to create this table, add records, modify records, delete records, and retrieve information from it.
- Major Categories of SQL Commands
SQL commands can be grouped into several categories:
| Category | Full Name | Main Purpose |
|---|---|---|
| DDL | Data Definition Language | Defines database structures |
| DML | Data Manipulation Language | Modifies data |
| DQL | Data Query Language | Retrieves data |
| DCL | Data Control Language | Controls permissions |
| TCL | Transaction Control Language | Manages transactions |
Common commands include:
DDL - CREATE ALTER DROP TRUNCATE
DML - INSERT UPDATE DELETE
DCL - GRANT REVOKE
TCL - COMMIT ROLLBACK SAVEPOINT
This article focuses primarily on DDL and DML.
DDL - Data Definition Language
What Is DDL?
Data Definition Language (DDL) is a group of SQL commands used to define and modify the structure of database objects.
Database objects include:
- Databases
- Schemas
- Tables
- Columns
- Constraints
- Views
- Indexes
DDL changes the structure of the database rather than the individual records stored inside it.
The most commonly used DDL commands are:
1. CREATE
The CREATE command is used to create new database objects.
For example, we can create a table called customers.
CREATE TABLE customers (
customer_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
);
This creates a table with four columns:
customer_idfirst_namelast_nameemail
Adding Constraints
In real-world databases, constraints are used to maintain data integrity.
For example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE
);
Here:
-
PRIMARY KEYuniquely identifies each customer. -
NOT NULLprevents a column from containingNULL. -
UNIQUEprevents duplicate email addresses.
- Creating a Table with a Foreign Key
Tables often need to be related to one another.
Suppose we have a customers table and an orders table.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
amount DECIMAL(10, 2),
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
The foreign key establishes a relationship between the two tables.
The customer_id in the orders table references the customer_id in the customers table.
This allows us to represent relationships such as:
Customers
|
| customer_id
|
↓
Orders
2. ALTER
The ALTER command is used to modify an existing database object.
For example, suppose we want to add a phone number to the customers table.
ALTER TABLE customers
ADD COLUMN phone VARCHAR(20);
The table now contains an additional column.
Rename a Column
ALTER TABLE customers
RENAME COLUMN phone TO phone_number;
Rename a Table
ALTER TABLE customers
RENAME TO customers_new;
Change a Column's Data Type
For example:
ALTER TABLE customers
ALTER COLUMN phone_number TYPE VARCHAR(30);
Add a Constraint
ALTER TABLE customers
ADD CONSTRAINT unique_phone UNIQUE (phone_number);
ALTER is especially important when database requirements change and an existing table needs to be modified without recreating it from scratch.
3. DROP
The DROP command permanently removes a database object.
For example:
DROP TABLE customers;
This removes the customers table and its structure.
The table itself no longer exists.
You can also drop a schema.
DROP SCHEMA sales;
Important Warning
DROP should be used carefully because it removes the database object itself.
For example:
DROP TABLE customers;
means that the table structure is removed.
Therefore, DROP is fundamentally different from DELETE.
4. TRUNCATE
The TRUNCATE command removes all rows from a table while keeping the table structure.
Example:
TRUNCATE TABLE customers;
After executing this command:
- The table still exists.
- The columns still exist.
- The constraints remain according to the database system's behavior.
- The rows are removed.
Consider a table containing:
| customer_id | name |
|---|---|
| 1 | John |
| 2 | Mary |
| 3 | Peter |
After:
TRUNCATE TABLE customers;
The table becomes empty:
| customer_id | name |
|---|---|
The structure remains.
- DML - Data Manipulation Language
What Is DML?
Data Manipulation Language (DML) consists of SQL commands used to manipulate the records stored in database tables.
The most common DML commands are:
INSERT
UPDATE
DELETE
DML works primarily with the data inside tables, rather than defining the tables themselves.
1. INSERT
The INSERT command adds new records to a table.
Suppose we have:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
);
We can insert a customer using:
INSERT INTO customers
(customer_id, first_name, last_name, email)
VALUES
(1, 'John', 'Kamau', 'john@example.com');
We can insert another customer:
INSERT INTO customers
(customer_id, first_name, last_name, email)
VALUES
(2, 'Mary', 'Wanjiku', 'mary@example.com');
The table now contains:
| customer_id | first_name | last_name | |
|---|---|---|---|
| 1 | John | Kamau | john@example.com |
| 2 | Mary | Wanjiku | mary@example.com |
- Inserting Multiple Records
Multiple records can be inserted using a single statement.
INSERT INTO customers
(customer_id, first_name, last_name, email)
VALUES
(3, 'Peter', 'Otieno', 'peter@example.com'),
(4, 'Grace', 'Akinyi', 'grace@example.com'),
(5, 'David', 'Mwangi', 'david@example.com');
This is often more efficient than executing separate INSERT statements.
2. UPDATE
The UPDATE command modifies existing records.
Suppose John's email address has changed.
We can update it using:
UPDATE customers
SET email = 'john.kamau@example.com'
WHERE customer_id = 1;
The WHERE clause identifies the record that should be changed.
Updating Multiple Columns
You can modify several columns at once:
UPDATE customers
SET
first_name = 'Jonathan',
email = 'jonathan@example.com'
WHERE customer_id = 1;
- The Importance of WHERE with UPDATE
One of the most important SQL concepts is understanding the danger of running UPDATE without a WHERE clause.
For example:
UPDATE customers
SET email = 'jackson@example.com';
This updates every row in the table.
That may be intentional in some situations, but it can also be a serious mistake.
Therefore, before executing an UPDATE, always ask:
Which rows should be changed?
Then use an appropriate WHERE condition.
3. DELETE
The DELETE command removes records from a table.
For example:
DELETE FROM customers
WHERE customer_id = 5;
This removes the customer whose ID is 5.
The table itself remains.
- DELETE Without WHERE
Be extremely careful with:
DELETE FROM customers;
This removes all records from the table.
However, unlike:
DROP TABLE customers;
the table itself still exists.
This distinction is extremely important.
- DELETE vs TRUNCATE vs DROP
These three commands are commonly confused.
Consider the following:
DELETE FROM customers;
TRUNCATE TABLE customers;
DROP TABLE customers;
They have different effects.
| Command | Removes Rows | Removes Structure | Typical Use |
|---|---|---|---|
DELETE |
Yes | No | Remove selected or all records |
TRUNCATE |
Yes, all | No | Quickly empty a table |
DROP |
Yes, with object | Yes | Remove the table itself |
DELETE
DELETE FROM customers
WHERE customer_id = 5;
Removes specific records.
TRUNCATE
TRUNCATE TABLE customers;
Removes all records while retaining the table.
DROP
DROP TABLE customers;
Removes the entire table.
A simple memory trick is:
DELETE = remove data
TRUNCATE = empty the table
DROP = remove the object
- DDL vs DML
The fundamental difference between DDL and DML is what they primarily operate on.
| Feature | DDL | DML |
|---|---|---|
| Full name | Data Definition Language | Data Manipulation Language |
| Main purpose | Defines database structure | Manipulates stored data |
| Works with | Tables, schemas, constraints, etc. | Rows/records |
| Common commands |
CREATE, ALTER, DROP, TRUNCATE
|
INSERT, UPDATE, DELETE
|
| Example | Create a table | Add a customer |
| Focus | Structure | Data |
Consider this example:
CREATE TABLE employees (
employee_id INT,
name VARCHAR(100),
salary DECIMAL(10, 2)
);
This is DDL because we are defining the table structure.
Now:
INSERT INTO employees
(employee_id, name, salary)
VALUES
(1, 'James', 75000);
This is DML because we are adding data to the table.
- A Practical Example
Let's build a simple employee database.
Step 1: Create the Table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10, 2)
);
This is DDL.
Step 2: Insert Data
INSERT INTO employees
(employee_id, first_name, last_name, department, salary)
VALUES
(1, 'John', 'Kamau', 'Finance', 85000),
(2, 'Mary', 'Wanjiku', 'IT', 95000),
(3, 'Peter', 'Otieno', 'Sales', 70000);
This is DML.
Step 3: Update Data
Suppose Peter receives a salary increase.
UPDATE employees
SET salary = 80000
WHERE employee_id = 3;
This is DML.
Step 4: Delete Data
Suppose employee 3 leaves the company.
DELETE FROM employees
WHERE employee_id = 3;
This is DML.
Step 5: Modify the Table Structure
Suppose we need to store employee phone numbers.
ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(20);
This is DDL.
Step 6: Empty the Table
If we want to remove all employees while retaining the table:
TRUNCATE TABLE employees;
This is commonly treated as DDL in SQL command classifications.
Step 7: Remove the Table
If the table is no longer needed:
DROP TABLE employees;
This is DDL.
- DCL - Data Control Language
DCL is concerned with database permissions and access control.
Two common commands are:
GRANT
REVOKE
For example:
GRANT SELECT ON employees TO analyst;
This grants the analyst user permission to query the table.
Permissions can also be removed:
REVOKE SELECT ON employees FROM analyst;
DCL is particularly important in environments where multiple users have different levels of access.
Top comments (0)