Introduction to SQL: DDL, DML, DCL, TCL & DQL

SQL — Structured Query Language — is the universal language of relational databases. In this chapter, you’ll learn what SQL is, why it was designed, and how its five sublanguages cover everything from creating tables to controlling user permissions.

What is SQL?

SQL is a declarative programming language designed specifically for managing and querying data stored in relational databases. Unlike procedural languages where you tell the computer how to do something step-by-step, SQL lets you describe what you want, and the database engine figures out how to retrieve it.

“SQL is to databases what English is to international diplomacy — a standardised language that makes communication possible across different systems and vendors.”

SQL was originally developed at IBM in the early 1970s by Edgar F. Codd, Donald Chamberlin, and Raymond Boyce — initially called SEQUEL. It became an ISO and ANSI standard in 1986, and every major RDBMS (MySQL, PostgreSQL, Oracle, SQL Server) supports it — with minor dialect differences.

SQL is not case-sensitive for keywords — select, SELECT, and Select all work. However, by convention, SQL keywords are written in UPPERCASE and table/column names in lowercase. We follow this convention throughout this course for readability.

The Five Sublanguages of SQL

SQL commands are grouped into five sublanguages, each with a different purpose. Think of them as different departments in a company — each handles a distinct area of responsibility:

DDL

Data Definition Language

DML

Data Manipulation Language

DQL

Data Query Language

DCL

Data Control Language

TCL

Transaction Control Language

We’ll now explore each sublanguage in detail with MySQL examples.

DDL

Data Definition Language

Define and manage the structure of database objects

DDL commands define the schema — the blueprint of your database. They create, modify, or delete database objects such as databases, tables, indexes, and views. DDL changes are auto-committed in MySQL — they cannot be rolled back.

CREATE ALTER DROP TRUNCATE RENAME

SQL — DDL Examples

1-- CREATE: Define a new table
2CREATE TABLE products (
3    product_id   INT           NOT NULL AUTO_INCREMENT,
4    product_name VARCHAR(200)  NOT NULL,
5    price        DECIMAL(10,2) NOT NULL,
6    created_at   DATETIME      DEFAULT NOW(),
7    PRIMARY KEY (product_id)
8);
9
10-- ALTER: Add a new column to an existing table
11ALTER TABLE products
12    ADD COLUMN stock_quantity INT DEFAULT 0;
13
14-- DROP: Permanently delete a table and all its data
15DROP TABLE products;
16
17-- TRUNCATE: Remove all rows but keep the table structure
18TRUNCATE TABLE products;

DML

Data Manipulation Language

Add, modify, and remove data in tables

DML commands operate on the data itself — inserting new records, updating existing ones, and deleting rows. Unlike DDL, DML commands are part of transactions and can be rolled back if needed.

INSERT UPDATE DELETE REPLACE

SQL — DML Examples

1-- INSERT: Add a new row of data
2INSERT INTO products (product_name, price, stock_quantity)
3VALUES ('Wireless Keyboard', 1299.00, 50);
4
5-- INSERT multiple rows at once
6INSERT INTO products (product_name, price, stock_quantity)
7VALUES
8    ('USB-C Mouse',  849.00,  120),
9    ('Laptop Stand', 2499.00, 30);
10
11-- UPDATE: Modify existing data (always use WHERE!)
12UPDATE products
13SET   price = 999.00,
14      stock_quantity = stock_quantity - 1
15WHERE product_id = 1;
16
17-- DELETE: Remove specific rows (always use WHERE!)
18DELETE FROM products
19WHERE stock_quantity = 0;

⚠️ Always use WHERE with UPDATE and DELETE. Without a WHERE clause, MySQL updates or deletes every single row in the table. This is one of the most common beginner mistakes.

DQL

Data Query Language

Retrieve and read data from tables

DQL has essentially one command — SELECT — but it’s the most powerful and most-used command in all of SQL. It lets you filter, sort, aggregate, join, and transform data before returning it. Some authors classify SELECT under DML; both conventions are acceptable.

SELECT

SQL — DQL Examples

1-- SELECT all columns from a table
2SELECT *
3FROM   products;
4
5-- SELECT specific columns with a filter
6SELECT product_name, price
7FROM   products
8WHERE  price < 1000
9ORDER BY price DESC;
10
11-- SELECT with aggregation (counting products per price range)
12SELECT   COUNT(*) AS total_products,
13         AVG(price) AS avg_price
14FROM     products
15WHERE    stock_quantity > 0;

DCL

Data Control Language

Manage user permissions and access rights

DCL commands control who can do what in the database. They grant or revoke privileges to database users, enabling fine-grained access control — critical for security in multi-user and production environments.

GRANT REVOKE

SQL — DCL Examples

1-- GRANT SELECT and INSERT on a specific table to a user
2GRANT SELECT, INSERT
3ON shop_db.products
4TO 'app_user'@'localhost';
5
6-- GRANT all privileges on an entire database
7GRANT ALL PRIVILEGES
8ON shop_db.*
9TO 'admin_user'@'%';
10
11-- REVOKE a previously granted privilege
12REVOKE INSERT
13ON shop_db.products
14FROM 'app_user'@'localhost';

TCL

Transaction Control Language

Manage groups of DML operations as atomic units

TCL commands manage transactions — groups of SQL operations that must succeed or fail as a single, atomic unit. This is essential for maintaining data integrity in operations like bank transfers, where both the debit and credit must succeed together or neither should happen.

COMMIT ROLLBACK SAVEPOINT START TRANSACTION

SQL — TCL Example (Bank Transfer)

1-- Begin a transaction (both operations must succeed together)
2START TRANSACTION;
3
4-- Debit sender's account
5UPDATE accounts
6SET   balance = balance - 5000
7WHERE account_id = 101;
8
9-- Credit receiver's account
10UPDATE accounts
11SET   balance = balance + 5000
12WHERE account_id = 202;
13
14-- If both succeeded → save permanently
15COMMIT;
16
17-- If something went wrong → undo everything since START TRANSACTION
18-- ROLLBACK;

Quick Reference: All Five Sublanguages

Use this table as a handy cheat sheet throughout the course:

Sublanguage	Full Name	Key Commands	Purpose	Auto-Commit?
DDL	Data Definition Language	`CREATE, ALTER, DROP, TRUNCATE, RENAME`	Define database structure (schema)	Yes
DML	Data Manipulation Language	`INSERT, UPDATE, DELETE, REPLACE`	Add, modify, delete rows	No *
DQL	Data Query Language	`SELECT`	Read and retrieve data	N/A
DCL	Data Control Language	`GRANT, REVOKE`	Control user access and permissions	Yes
TCL	Transaction Control Language	`COMMIT, ROLLBACK, SAVEPOINT`	Manage transaction boundaries	Controls DML

* DML auto-commits when MySQL’s autocommit mode is ON (the default). Use START TRANSACTION to take explicit control.

Key Takeaways

SQL is a declarative language — you describe what you want, not how to get it. The MySQL engine optimises the execution plan.
DDL defines structure (CREATE, ALTER, DROP) — changes are permanent and auto-committed in MySQL.
DML manipulates data (INSERT, UPDATE, DELETE) — always use a WHERE clause to avoid accidental full-table changes.
DQL (SELECT) retrieves data — it’s read-only and the most frequently used command in SQL.
DCL controls security (GRANT, REVOKE) — essential for managing multi-user database environments.
TCL manages transactions (COMMIT, ROLLBACK) — ensuring groups of DML operations succeed or fail as a unit.

What’s Next?

You’ve now completed Section 1 — the conceptual foundation of MySQL. In Section 2 — First Steps with MySQL, we dive into practical work: creating your first real databases, selecting them for use, writing SQL scripts, and executing them from files.