Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 125 ยท Getting Started

SQL Command Types: DDL, DML, DQL, DCL and TCL

Learn the five families of SQL statements, DDL, DML, DQL, DCL and TCL, with a runnable example of each and a guide to which ones SQLite supports.

SQL has dozens of statements, but they fall into a handful of families. Some statements build the structure of a database, some change the data inside it, one reads data, some control who may do what, and some group changes so they succeed or fail together. Knowing which family a statement belongs to tells you a lot about what it can do, and how careful you need to be with it.

The five families have short names that show up in job interviews, documentation and database permission screens: DDL, DML, DQL, DCL and TCL. This lesson runs an example of each one, so you get a feel for the whole language before the following chapters go through each statement in detail.

The five families at a glance

Family Stands for What it does Main statements Runs here?
DDL Data Definition Language Creates, changes and removes structure CREATE, ALTER, DROP, TRUNCATE Yes (no TRUNCATE in SQLite)
DML Data Manipulation Language Adds, changes and removes rows INSERT, UPDATE, DELETE, MERGE Yes (no MERGE in SQLite)
DQL Data Query Language Reads data SELECT Yes
DCL Data Control Language Grants and removes permissions GRANT, REVOKE No (SQLite has no users)
TCL Transaction Control Language Groups changes into all-or-nothing units BEGIN, COMMIT, ROLLBACK, SAVEPOINT Yes

These categories are a teaching aid, not part of the SQL standard, so sources don't always agree. Many books put SELECT inside DML, because both read or work with the data rather than the structure. Others say DQL to stress that SELECT never changes anything. Neither is wrong; what matters is the distinction between structure, data, permissions and transactions.

DDL: defining structure

Data Definition Language statements work on database objects: tables, indexes, views and triggers. They describe the shape of the data, not the data itself.

  • CREATE makes a new object: CREATE TABLE, CREATE INDEX, CREATE VIEW.
  • ALTER changes an existing one, for example by adding a column.
  • DROP deletes an object completely, including all its rows.
SQL DDL in action

Press Run (or Ctrl + Enter) to see the result here.

Each DDL statement shows OK., because it doesn't return rows. The first query shows the table and its index; after DROP TABLE, both are gone and objects_left is 0. Dropping a table also drops its indexes.

DDL is powerful and often permanent. DROP TABLE deletes the table and every row in it, with no "Are you sure?". In most databases, many DDL statements also can't be undone with a rollback. MySQL and Oracle, for example, commit the current transaction automatically before running DDL. PostgreSQL and SQLite are friendlier: they can roll back DDL inside a transaction.

TRUNCATE TABLE is also usually classed as DDL, even though it removes rows: it empties a table in one fast operation instead of deleting row by row. SQLite doesn't have it; a DELETE without a WHERE clause does the same job, and SQLite optimizes that case. The details are in CREATE TABLE and ALTER TABLE and DROP TABLE.

DML: changing data

Data Manipulation Language statements work on rows:

  • INSERT adds new rows.
  • UPDATE changes values in existing rows.
  • DELETE removes rows.

Unlike DDL, they leave the table's structure alone. Here they are working on a fresh copy of the bookstore database:

SQL DML in action ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Each change reports how many rows it touched: OK, 1 row affected. for the insert, OK, 2 rows affected. for the update (there are two mystery books) and OK, 2 rows affected. for the delete (two orders were placed before March 2024). The final SELECT confirms the new prices: 12 and 13.75.

That row count is your best friend. If an UPDATE you meant for one customer reports 250 rows affected, something is wrong with its WHERE clause. You'll learn to write these statements safely in INSERT, UPDATE and DELETE.

Some databases also have MERGE, which inserts, updates or deletes in one statement depending on whether a matching row exists. SQLite offers the same idea through INSERT ... ON CONFLICT, covered in Upserts with ON CONFLICT.

DQL: asking questions

Data Query Language has one statement, SELECT, and it's the one you'll write most. It reads data and returns a result table, without changing anything in the database:

SQL DQL in action ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Even though DQL is one statement, it's by far the largest part of SQL. Filtering, sorting, grouping, joins, subqueries and window functions are all parts of SELECT, and most of this course is about it, starting with SELECT in Depth.

Because SELECT is read-only, it's also the safest statement to experiment with. A good habit before any risky change is to run a SELECT with the same WHERE clause first, check that it finds exactly the rows you expect, and only then turn it into an UPDATE or DELETE.

DCL: controlling access

Data Control Language decides who may run the other statements. Server databases have user accounts and roles, and DCL gives or takes away their privileges:

SQL
-- PostgreSQL
CREATE ROLE report_reader LOGIN PASSWORD 'change-me';

GRANT SELECT ON orders, customers TO report_reader;

REVOKE SELECT ON customers FROM report_reader;

After these statements, report_reader can read orders but not customers, and can't change anything at all. This is the principle of least privilege: give each account only the access it needs. If a reporting tool's password leaks, the damage is limited to reading a few tables.

SQLite has no user accounts, so GRANT and REVOKE don't exist there; whoever can open the database file can do everything. That's why DCL examples on this site are read-only. You'll find more in Users, Roles and Permissions (DCL).

TCL: all or nothing

Transaction Control Language groups several statements into a transaction: a unit of work that either happens completely or not at all. The classic example is moving money between two accounts. Taking 100 from one account and adding 100 to the other must both succeed, or neither should.

  • BEGIN (or START TRANSACTION) starts a transaction.
  • COMMIT makes all its changes permanent.
  • ROLLBACK undoes every change since BEGIN.
SQL ROLLBACK undoes changes

Press Run (or Ctrl + Enter) to see the result here.

The first result, inside the transaction, shows the transfer: Amina 400, Bruno 300. After ROLLBACK, the second result shows the original balances again: 500 and 200. Change ROLLBACK to COMMIT and run it again; this time the transfer stays.

A savepoint is a bookmark inside a transaction. ROLLBACK TO a savepoint undoes only the work after it:

SQL Savepoints

Press Run (or Ctrl + Enter) to see the result here.

The result has two rows, step 1 and step 3: the rollback removed only step 2. Without an explicit BEGIN, SQLite (like most databases) runs each statement in its own tiny transaction and commits it immediately, which is called autocommit. Transactions get a full lesson in Transactions.

How to tell them apart

When you meet an unfamiliar statement, ask what it acts on:

Question Family
Does it create, change or remove a table, index or view? DDL
Does it add, change or remove rows? DML
Does it only read rows? DQL
Does it give or take away permissions? DCL
Does it start, finish or undo a group of changes? TCL

Database permission systems follow the same lines. A typical web application's account gets DQL and DML rights on its own tables, a migration tool gets DDL rights, and only administrators get DCL.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which family does ALTER TABLE tasks ADD COLUMN due TEXT; belong to?

    ALTER changes the definition of a table, which is structure, so it's DDL. DML changes rows.
  2. 2. A transaction runs two UPDATE statements and then ROLLBACK. What happens to the two updates?

    ROLLBACK undoes every change made since BEGIN.
  3. 3. Why can't you run GRANT SELECT ON orders TO reporter; in SQLite?

    SQLite controls access through the database file itself; there are no users or roles to grant privileges to.
  4. 4. In the savepoint example, which messages remain after COMMIT?

    ROLLBACK TO before_step_2 undoes only what happened after the savepoint, which was step 2.
  5. 5. Which statement is DQL?

    SELECT only reads data. DELETE is DML and DROP is DDL.

Summary

  • DDL (CREATE, ALTER, DROP, TRUNCATE) defines structure; DROP removes a table and all its data.
  • DML (INSERT, UPDATE, DELETE, MERGE) changes rows and reports how many rows it affected.
  • DQL is SELECT: it reads data and never changes it. Some sources count it as part of DML.
  • DCL (GRANT, REVOKE) manages permissions in server databases; SQLite has none.
  • TCL (BEGIN, COMMIT, ROLLBACK, SAVEPOINT) makes groups of changes all-or-nothing.

Next: SQL Syntax and Style, where you'll learn the rules for keywords, names, quotes and comments.