Skip to content
Ameen Abozaid
Englishالعربية
PublishedBEG-050-L01

SQL overview: DDL vs DML

  • Lesson 1 of 9
  • 11 min read
  • Beginner
On This Lesson 11 topics

Lesson Overview

In the previous module you learned what a relational database is: tables of rows and columns, primary keys, foreign keys, and how to split data across tables. You have only designed those tables so far. This module teaches the language you use to build them and ask them questions: SQL.

Throughout the module we use one picture: asking the librarian precise questions about the card catalog. The librarian only understands a strict, small language. If you speak it exactly, you get exactly what you asked for.

This lesson is the map. SQL statements fall into two families. DDL statements build and change the shelves (the structure). DML statements move the books (the rows). Knowing which family a statement belongs to tells you what it can break.

This lesson builds on BEG-049 (tables, keys, relationships). It needs no other background.

Learning Objectives

By the end of this lesson you will be able to:

  • Say what SQL is and what a DBMS does with the statements you send it.
  • Define DDL and DML and name the main statements of each family.
  • Classify a statement as DDL or DML.
  • Read a simple statement and say which part is a keyword, a name, a value, and where it ends.
  • Explain why a statement that changes structure is riskier than one that reads rows.
  • Recognize a few basic SQL syntax errors from the error message.

Written Explanation

What SQL is

SQL (Structured Query Language) is the standard language for talking to relational databases. You write a statement, send it to the database management system (DBMS, from BEG-048), and the DBMS does the work and sends back a result or an error.

Two points to keep in mind:

  • SQL is declarative: you describe what you want ("books published after 2000"), not how to find them. The DBMS decides how.
  • SQL is a standard, but every product adds its own details. In this module the examples run on PostgreSQL. Most of what you learn works almost unchanged on other relational databases, but small differences exist (for example, how you limit the number of rows returned), so check your product's documentation.

The two families

FamilyStands forWhat it works onStatements you will learn
DDLData Definition LanguageThe structure: tables and columnsCREATE, ALTER, DROP
DMLData Manipulation LanguageThe rows inside the tablesINSERT, UPDATE, DELETE, SELECT

In the library picture:

LibrarySQL
Building a shelf, adding a label slot to the catalog cardDDL: CREATE TABLE, ALTER TABLE
Taking a shelf away, with every book on itDDL: DROP TABLE
Putting a new book on a shelfDML: INSERT
Correcting a cardDML: UPDATE
Removing a book from the catalogDML: DELETE
Asking "which books are on the Science shelf?"DML: SELECT

A word of caution about names. This course puts SELECT in the DML family because it works on rows. Some textbooks give reading its own family, DQL (Data Query Language), and others use the terms slightly differently. The PostgreSQL manual does not use the DDL/DML labels at all: it simply lists each command. So treat DDL and DML as useful teaching categories, not as official parts of the PostgreSQL reference.

Anatomy of a statement

sql
INSERT INTO shelves (id, name) VALUES (1, 'Fiction');
PieceRole
INSERT INTO, VALUESKeywords: words the language reserves
shelves, id, nameNames of a table and its columns
1, 'Fiction'Values: numbers are written plainly, text goes in single quotes
;The terminator: marks the end of the statement

Rules that are true for PostgreSQL, according to its manual on lexical structure:

  • Keywords and unquoted names are case-insensitive: select, SELECT, and Select mean the same thing. Writing keywords in capitals and names in lower case is a common convention, and this course follows it because it makes statements easier to scan.
  • Text values use single quotes. A single quote inside text is written twice: 'O''Brien'. Double quotes mean something else (they quote a name), so avoid them for values.
  • A statement ends with a semicolon. You may spread one statement over several lines; the semicolon is what ends it.
  • -- starts a comment that runs to the end of the line.

Which family is dangerous?

A reading SELECT changes nothing. You can run it a hundred times and the database is the same afterwards. The other statements change something:

  • DML that writes (INSERT, UPDATE, DELETE) changes rows. A mistake here damages data. Lesson 3 shows how to make these safe.
  • DDL (CREATE, ALTER, DROP) changes structure. DROP TABLE removes the table and every row in it. A mistake here can damage the shape of everything that depends on it.

Many databases offer ways to undo changes (transactions, backups), but you will not meet those until later. For now, the safe habit is to write DDL and write-DML slowly, and to run a SELECT first whenever you can.

Reading the database's answer

The DBMS replies to each statement. In psql, PostgreSQL's command-line client, you see a short command tag after a statement and a table of rows after a SELECT. A GUI tool shows the same information in panels, and menus differ between tools, so use whatever client your course setup provides.

Two kinds of reply to learn early:

  • Success: a tag such as CREATE TABLE or INSERT 0 1, or a result table.
  • Error: a message naming the problem and the position. Read it: it nearly always points at the exact spot.

Examples

All examples were run on PostgreSQL 18.3 with psql, in a scratch database. Outputs are copied from the real run.

Example 1: one statement from each family (simple)

sql
CREATE TABLE shelves (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
INSERT INTO shelves (id, name) VALUES (1, 'Fiction');
INSERT INTO shelves (id, name) VALUES (2, 'Science');
SELECT * FROM shelves;

Output of the last statement:

text
 id |  name   
----+---------
  1 | Fiction
  2 | Science
(2 rows)

CREATE TABLE is DDL: it built an empty shelf. The two INSERT statements are DML: they put rows on it. The tag after each insert was INSERT 0 1, meaning one row was added. (You can ignore the 0.) SELECT * reads all columns of all rows, and changes nothing.

Example 2: changing rows, then structure (practical)

sql
UPDATE shelves SET name = 'Science and Nature' WHERE id = 2;
DELETE FROM shelves WHERE id = 1;
SELECT * FROM shelves;
text
UPDATE 1
DELETE 1
 id |        name        
----+--------------------
  2 | Science and Nature
(1 row)

Each tag reports how many rows it touched: UPDATE 1, DELETE 1. That count is useful feedback: if you expected one row and the tag says UPDATE 40, stop and look. Both statements are DML, so they left the table itself alone: it still has the same two columns.

Now the structural statement:

sql
DROP TABLE shelves;
SELECT * FROM shelves;
text
DROP TABLE
ERROR:  relation "shelves" does not exist
LINE 1: SELECT * FROM shelves;
                      ^

DROP TABLE is DDL. The table, and the row that was left in it, are gone. PostgreSQL calls a table a "relation" in its error messages: that is the formal name used in relational theory.

Example 3: reading syntax errors (realistic)

Day-to-day SQL work is mostly writing, running, and fixing. Two typical slips:

sql
SELECT * FROM shelves
SELECT 1;
text
ERROR:  syntax error at or near "SELECT"
LINE 2: SELECT 1;
        ^

The first statement has no semicolon, so the database read both lines as one statement and found a second SELECT where it did not expect one. The caret points at the first place the grammar broke, which is usually just after the real mistake.

Case does not matter for names that are not quoted:

sql
select * from SHELVES;

This ran without error and returned the same rows, because unquoted names are folded to lower case.

Backend Insight

In a backend application, SQL is what your code sends to the database. You will usually see two very different habits:

  • Application code at runtime mostly runs DML: SELECT to load data, INSERT to save a new order, UPDATE to change a status, DELETE to remove a record.
  • DDL is usually run at deployment time, by migration scripts that create and alter tables in a controlled, reviewed way. A web endpoint that runs DROP TABLE would be a serious design problem.

This is also why teams give the running application a database account that cannot drop tables, while a separate account is allowed to change structure. You will see that idea again in later modules. Keep the split in your head: data changes often and in small pieces; structure changes rarely and deliberately.

Common Mistakes

Mistake 1: Putting a value in double quotes

  • Incorrect: INSERT INTO shelves (id, name) VALUES (3, "Poetry");
  • Correct: INSERT INTO shelves (id, name) VALUES (3, 'Poetry');

In PostgreSQL double quotes mean a column or table name, so the first form is read as a column called Poetry and fails.

Mistake 2: Forgetting the semicolon between statements

When you run several statements together, a missing ; makes two statements read as one, as in Example 3. If the error points at the start of the next statement, check the end of the previous one.

Mistake 3: Thinking DELETE and DROP are the same

DELETE FROM shelves WHERE ...; removes some rows and keeps the table. DROP TABLE shelves; removes the table and everything in it. One is DML, the other DDL.

Mistake 4: Assuming every database speaks identical SQL

The core statements are shared, but details vary between products. When something works in one tool and fails in another, suspect a dialect difference before suspecting your logic.

Exercises

Try each exercise yourself before opening the solutions.

Level A: Foundation

A1. Write the full names behind DDL and DML.

Show solution A1

A1. DDL is Data Definition Language; DML is Data Manipulation Language.

A2. Classify each statement as DDL or DML: (a) CREATE TABLE, (b) SELECT, (c) DROP TABLE, (d) DELETE, (e) INSERT.

Show solution A2

A2. (a) DDL. (b) DML in this course (some books call it DQL). (c) DDL. (d) DML. (e) DML.

A3. In UPDATE books SET title = 'Dune' WHERE id = 7; name two keywords, one table name, and one text value.

Show solution A3

A3. Keywords: UPDATE, SET, WHERE. Table name: books. Text value: 'Dune'. (Also accepted: the column names title and id, the number 7.)

Level B: Application

B1. For each need, say which statement you would use, without writing it in full: (a) add a new member, (b) add a phone column to the members table, (c) list all loans, (d) remove a lost book from the catalog.

Show solution B1

B1. (a) INSERT. (b) ALTER TABLE (DDL). (c) SELECT. (d) DELETE, with a WHERE that picks that one book.

B2. The tag after a statement was DELETE 40, but you expected one row. What do you do next, and why?

Show solution B2

B2. Stop and investigate before doing anything else. The count says the statement removed 40 rows, so the WHERE was probably wrong or missing. Check the statement, and see what your setup offers for recovery (a backup or transaction, covered later). The lesson of the habit is to run a SELECT with the same condition first, to see how many rows match.

B3. The following fails. Find two separate problems.

sql
INSERT INTO authors (id, name) VALUES (1, "Ursula Le Guin")
SELECT * FROM authors;
Show solution B3

B3. The text value uses double quotes, which PostgreSQL reads as a name; it needs single quotes. The first statement also has no semicolon, so it runs into the SELECT and causes a syntax error.

Level C: Challenge

C1. Explain in three or four sentences why the same application usually runs DML all day but DDL only when it is deployed. Use the library picture.

Show solution C1

C1. Data changes constantly: each order, visit, or payment is a new or changed row, like books being borrowed and returned. The shelves themselves (which tables and columns exist) only change when the application changes, like a library being rearranged. Because structure changes can affect everything and are hard to undo, they are done deliberately, in reviewed scripts, while data changes are handled by normal application code.

C2. A teammate says "SELECT is DML, so it can damage data". Respond, and mention one way the DDL/DML split is itself only a convention.

Show solution C2

C2. A plain SELECT only reads, so it cannot damage data; the writing statements are INSERT, UPDATE, and DELETE. The groups are a convention: PostgreSQL's manual lists commands without these labels, and some books file SELECT under a separate DQL family or define the families differently.

Mini Challenge

Without a database, sort this list into two columns, DDL and DML, and for each DML statement say whether it reads or writes rows:

  1. Create a table fines.
  2. List members who joined this year.
  3. Add a notes column to loans.
  4. Record a new loan.
  5. Mark a loan as returned.
  6. Delete the table fines.

Then write one sentence on which of the six you would most want to double-check before running, and why.

Check your work: items 1, 3, and 6 are DDL; 2 is DML that reads; 4 and 5 are DML that write. Your "most careful" answer should name a statement that destroys or changes a lot (item 6, for example).

Summary & Next Lesson

Key takeaways

  • SQL is the standard declarative language for relational databases; each product adds its own details.
  • DDL (CREATE, ALTER, DROP) builds and changes structure. DML (INSERT, UPDATE, DELETE, SELECT) works with rows.
  • Keywords and unquoted names are case-insensitive in PostgreSQL; text values use single quotes; a semicolon ends a statement.
  • SELECT only reads. Everything else changes something, and DROP TABLE removes data with the structure.
  • Applications mostly run DML at runtime; DDL is run deliberately, at deployment.

Next lesson

You can now tell the two families apart. The next lesson, "CREATE and ALTER", starts with DDL: how to create a table with the right column types and a primary key, and how to change it later.

Self-check

  1. Is INSERT DDL or DML?
  2. What does the semicolon do?
  3. How do you write the text Hello as a value?
  4. Which family does DROP TABLE belong to, and what does it remove?
  5. Why is it useful that statements report how many rows they changed?
Show answers
  1. DML.
  2. It marks the end of a statement.
  3. In single quotes: 'Hello'.
  4. DDL. It removes the table and all the rows in it.
  5. The count lets you notice an unexpected result, such as 40 rows changed instead of 1, so you can stop and investigate.

References

  • PostgreSQL documentation, Lexical Structure. Supports case-insensitive keywords and unquoted names, single-quoted strings with doubled quotes, semicolon-terminated commands, and -- comments.
  • PostgreSQL documentation, SQL Commands. Lists CREATE TABLE, ALTER TABLE, DROP TABLE, INSERT, UPDATE, DELETE, and SELECT as commands, without DDL or DML labels. The DDL/DML grouping, the declarative description, and the library analogy are this course's own teaching framing.
  • All outputs were produced by running the statements on PostgreSQL 18.3 in a scratch database. The remark that product details differ is general knowledge not tied to one cited page.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson

Type to search levels, phases, modules and lessons.