Topic 4 of 52
DDL vs DML
Overview
SQL is divided into sub-languages based on operation type. DDL (Data Definition Language) defines the schema structure, while DML (Data Manipulation Language) works with the actual data. Understanding the distinction is fundamental for database design and interviews.
Syntax
sql
-- DDL (Data Definition Language): defines database structure
-- Affects schema/structure, often auto-committed
CREATE TABLE products (...); -- create new table
ALTER TABLE products ADD ...; -- modify structure
DROP TABLE products; -- delete table permanently
TRUNCATE TABLE products; -- empty table (faster than DELETE)
RENAME TABLE products TO items; -- rename table
-- DML (Data Manipulation Language): manipulates data
-- Can be wrapped in transactions, can be rolled back
INSERT INTO products VALUES (...); -- add rows
UPDATE products SET price = 100 ...; -- modify rows
DELETE FROM products WHERE ...; -- remove rows
SELECT * FROM products; -- retrieve rows (also DQL)Common Pitfalls
- DDL statements (CREATE, ALTER, DROP) are usually auto-committed — they cannot be rolled back in most databases.
- TRUNCATE is DDL (not DML) in most databases — it resets auto-increment counters and cannot be rolled back without explicit transaction.
- Interview tip: The full SQL classification is DDL, DML, DCL (Data Control), TCL (Transaction Control), and DQL (Data Query).
Real-World Example
DDL for schema creation and DML for data operations in a product catalog:
example
sql
-- DDL: Create the table structure
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
category_id INT REFERENCES categories(id),
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0
);
-- DML: Work with the data
INSERT INTO categories (name) VALUES ('Electronics'), ('Clothing');
INSERT INTO products (name, category_id, price, stock)
VALUES ('iPhone 16', 1, 89999.00, 50);
UPDATE products SET price = 79999.00 WHERE name = 'iPhone 16';
DELETE FROM products WHERE stock = 0;
SELECT * FROM products WHERE category_id = 1 ORDER BY price;