SQL Data Languages

For a while, I thought of SQL as just “the language you use to query databases.” It wasn’t until I dug deeper into how SQL is actually structured that I realized it’s really a family of sub-languages, each with a distinct job. Understanding this breakdown changed how I write and reason about SQL, and I think it’s one of those foundational concepts that makes everything else about relational databases click into place. In this article, I’ll walk through the different SQL data languages using SQLite as the practical example throughout.

Why SQL Is Divided Into Sub-Languages

SQL isn’t a single monolithic language — it’s a composite of several sub-languages, each responsible for a different category of task: defining structure, manipulating data, controlling access, and managing transactions. Categorizing SQL this way helps me reason about what kind of operation I’m performing any time I write a query, which in turn helps me think about things like transaction boundaries, permissions, and schema evolution more clearly.

The four main categories I work with regularly are:

1. DDL — Data Definition Language
2. DML — Data Manipulation Language
3. DCL — Data Control Language
4. TCL — Transaction Control Language

Some people also call out DQL (Data Query Language) as a separate category for SELECT specifically, though it’s commonly grouped under DML. I’ll cover it separately here since I think it deserves its own explanation.

SQLite implements a meaningful subset of most of these categories, with a notable difference around DCL, which I’ll explain below.

1. DDL — Data Definition Language

DDL statements define and modify the structure of the database itself — tables, indexes, views, and triggers — rather than the data stored inside them.

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

ALTER TABLE products ADD COLUMN category TEXT;

DROP TABLE products;

CREATE INDEX idx_products_name ON products(name);

CREATE VIEW expensive_products AS
SELECT * FROM products WHERE price > 100;

SQLite’s ALTER TABLE support is more limited than some other databases — it supports adding columns, renaming tables and columns, and dropping columns in recent versions, but it doesn’t support the full range of in-place structural changes that something like PostgreSQL allows. For more complex schema changes, the common pattern in SQLite is to create a new table with the desired structure, copy the data over, drop the old table, and rename the new one.

CREATE TABLE products_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    category TEXT
);

INSERT INTO products_new (id, name, price)
SELECT id, name, price FROM products;

DROP TABLE products;
ALTER TABLE products_new RENAME TO products;

2. DML — Data Manipulation Language

DML statements manipulate the actual data stored inside tables — inserting, updating, and deleting rows.

INSERT INTO products (name, price) VALUES ('Keyboard', 49.99);

UPDATE products SET price = 44.99 WHERE name = 'Keyboard';

DELETE FROM products WHERE name = 'Keyboard';

SQLite also supports a convenient UPSERT syntax using ON CONFLICT, which is technically DML but deserves a special mention because it’s genuinely useful in day-to-day work:

INSERT INTO products (id, name, price)
VALUES (1, 'Mouse', 19.99)
ON CONFLICT(id) DO UPDATE SET price = excluded.price;

This inserts a new row, or updates the existing row’s price if a row with that primary key already exists — all in a single atomic statement.

3. DQL — Data Query Language

SELECT is technically the only statement in this category, but it’s arguably the most important thing you’ll ever write in SQL, so I think it deserves to stand on its own.

SELECT name, price
FROM products
WHERE price > 20
ORDER BY price DESC
LIMIT 10;

DQL is about reading and shaping data — filtering, sorting, joining, grouping, and aggregating — without modifying anything.

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 30;

4. DCL — Data Control Language

DCL statements control permissions and access — think GRANT and REVOKE in databases like PostgreSQL or MySQL. This is the category where SQLite genuinely diverges from most other SQL engines, because SQLite has no built-in user or permission system at all.

-- This works in PostgreSQL or MySQL, but SQLite has no equivalent:
GRANT SELECT ON products TO analyst_user;

Since SQLite is an embedded, file-based database rather than a client-server system with authenticated users, access control is handled entirely at the operating system’s filesystem permission level instead. If a process can read the .db file, it can read the data; if it can write to the file, it can modify the data. There’s no internal user/role system to manage.

This was a genuine “aha” moment for me — it explains why SQLite documentation never talks about database users the way MySQL or PostgreSQL docs constantly do.

5. TCL — Transaction Control Language

TCL statements manage transactions — grouping multiple operations together so they either all succeed or all fail as a unit.

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If something goes wrong partway through, you roll back instead:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- something goes wrong here
ROLLBACK;

SQLite also supports SAVEPOINT, which lets you create nested rollback points within a larger transaction — genuinely useful for complex operations where you want the option to undo part of the work without discarding everything.

BEGIN TRANSACTION;

INSERT INTO products (name, price) VALUES ('Monitor', 199.99);
SAVEPOINT before_risky_update;

UPDATE products SET price = price * 1.1;
-- decide that update was wrong
ROLLBACK TO before_risky_update;

COMMIT;

Why This Categorization Actually Matters in Practice

Understanding which category a statement belongs to has practically changed how I write migrations and application code:

• DDL statements in SQLite are implicitly transactional in most cases, but I still think about schema changes as a distinct, higher-risk category of operation compared to routine data manipulation.
• DML operations are the bread and butter of application logic, and wrapping related DML statements in explicit transactions (TCL) is critical for consistency — especially in SQLite, where a single writer lock means poorly managed transactions can create contention.
• Because SQLite has no DCL, I’ve learned to think about access control entirely differently than I would with a client-server database — it’s a filesystem and application-layer concern, not a database-layer one.

Best Practices

• Always wrap multi-statement DML operations in explicit transactions to guarantee atomicity and improve performance.
• For schema changes beyond simple additions, use the “create new table, copy data, drop old table, rename” pattern rather than expecting full in-place ALTER TABLE support.
• Since SQLite has no DCL, enforce access control at the filesystem level (proper file permissions) and, where needed, at the application layer.
• Use SAVEPOINT for complex multi-step operations where you want partial rollback capability.
• Keep DDL changes versioned in migration scripts, just as you would with any other database, even though SQLite’s schema changes feel lightweight.

Frequently Asked Questions

Does SQLite support GRANT and REVOKE like other databases? No. SQLite has no built-in user or permission system; access control happens entirely through operating system file permissions on the database file itself.

Is SELECT part of DML or its own category? It’s commonly grouped under DML in casual usage, but many textbooks classify it separately as DQL because it only reads data and never modifies it.

Can I roll back a DDL statement in SQLite? Yes, in most cases DDL statements inside SQLite participate in transactions just like DML statements, so they can be rolled back if wrapped in an explicit transaction that hasn’t been committed yet.

What’s the difference between COMMIT and SAVEPOINT? COMMIT finalizes an entire transaction, making all changes permanent. SAVEPOINT creates an intermediate marker within a transaction that you can roll back to without discarding the whole transaction.

Why doesn’t SQLite have user accounts? Because it’s designed as an embedded, serverless library rather than a networked multi-user service, so the concept of authenticated database users simply doesn’t apply the way it does in client-server systems.

Wrapping Up

Breaking SQL down into DDL, DML, DQL, DCL, and TCL gave me a much clearer mental model for reasoning about what any given SQL statement is actually doing. It also highlighted one of SQLite’s most distinctive traits — its complete absence of a data control language — which makes perfect sense once you remember that SQLite was never designed to be a multi-user, networked database in the first place. Keeping this categorization in mind has made me a noticeably more deliberate SQL writer, especially when it comes to transaction boundaries and schema changes.