Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 79 ยท Introduction to Databases

Database Languages and Database Objects

Learn the five families of SQL statements (DDL, DML, DQL, DCL, TCL) and the main database objects: tables, views, indexes, sequences and synonyms, with live examples.

A DBMS is not controlled by clicking around inside a file. You talk to it in a database language, and one language, SQL, covers a surprising range of jobs: describing the structure of the data, changing the data, asking questions, deciding who may do what, and grouping changes into safe units. Textbooks split these jobs into families with short, exam-friendly names: DDL, DML, DQL, DCL and TCL. Knowing which family a statement belongs to tells you what it affects, whether it can be undone, and which kind of user normally runs it.

The second half of this lesson looks at what those statements create and manage. Everything stored in a database is a database object: tables, of course, but also views, indexes, sequences and synonyms. You met the idea of a catalog in DBMS Architecture; this lesson names the objects that fill it.

Three families work with data

DDL Define structure. CREATE, ALTER, DROP.
DML Change data. INSERT, UPDATE, DELETE.
DQL Ask questions. SELECT.

The names come from the job, not from different languages.

1 / 3

The five families

Family Stands for Typical statements What it touches
DDL Data Definition Language CREATE, ALTER, DROP, TRUNCATE The schema (the catalog)
DML Data Manipulation Language INSERT, UPDATE, DELETE, MERGE The rows
DQL Data Query Language SELECT Nothing; it only reads
DCL Data Control Language GRANT, REVOKE Permissions
TCL Transaction Control Language BEGIN, COMMIT, ROLLBACK, SAVEPOINT How changes are grouped

A few notes make the table more useful.

  • Is SELECT part of DML? Some books fold it into DML, because it manipulates (reads) data. Others give it its own name, DQL. Both are common in exams; the content is the same.
  • DDL is special. It changes the description of the data, so the system has to update the catalog. In many products (Oracle, MySQL) a DDL statement commits automatically, and cannot be rolled back. PostgreSQL and SQLite are the friendly exceptions: their DDL is transactional.
  • TRUNCATE versus DELETE. DELETE is DML: it removes chosen rows one by one and can be rolled back. TRUNCATE is usually DDL: it empties the whole table quickly. SQLite has no TRUNCATE; DELETE FROM t without a WHERE does the same job.
  • DCL is about people. It is run by the database administrator or the object's owner. See Authorization and Access Control for what the privileges mean.

One session, four families

The next example uses DDL to create a table, DML to fill and change it, TCL to make a mistake and take it back, and DQL to look at the result. (SQLite has no GRANT, so the DCL family appears as a comment.)

SQL DDL, DML, TCL and DQL in one session

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

Inside the transaction the table is empty (0 rows), which shows that DML takes effect at once for the session that runs it. After ROLLBACK, all three accounts are back. Cleo's balance is 150 because the earlier UPDATE was committed before the transaction started. If you replace ROLLBACK with COMMIT, the deletion becomes permanent. The transaction concept explains why this grouping matters.

Database objects

A database object is any named thing the DBMS stores and manages in a schema. The catalog lists them, and DDL creates and removes them.

Tables

The table is the basic object: columns and rows, with constraints from DDL and Constraints. Everything else is either built on tables or helps you reach them.

Views

A view is a stored query with a name. It holds no data of its own; each time you read it, the DBMS runs the query. Views hide complexity, restrict which columns a user sees, and give old programs a stable shape when the tables underneath change. Nested Queries and Views covers updatable views and materialized views in more depth.

Indexes

An index is a separate structure, usually a B+ tree (see B-Trees and B+ Trees), that lets the engine find rows without scanning the whole table. Indexes change speed, never the answer. They cost disk space and slow down writes, because each insert or update must also maintain the index.

##s

A sequence is an object that produces a series of unique numbers, most often for surrogate keys. Oracle, PostgreSQL and SQL Server have CREATE SEQUENCE; you ask for the next number with NEXTVAL (Oracle, PostgreSQL) or NEXT VALUE FOR (SQL Server). Numbers are handed out outside the transaction, so a rolled-back transaction leaves a gap in PostgreSQL and Oracle. MySQL and SQLite attach the same idea to a column instead (AUTO_INCREMENT, or AUTOINCREMENT on SQLite's INTEGER PRIMARY KEY). The common rule: never reuse a number, and do not expect the numbers to be gap-free.

Synonyms

A synonym is an alias for another object, often one in a different schema or even a different database. Oracle and SQL Server have CREATE SYNONYM. It lets a program say orders while the real table is sales_2025.orders, so the real name can change later without touching the program. PostgreSQL and MySQL have no synonyms; a view that selects everything, or a search_path setting, does the same job.

Here is a SQLite session that shows a view, an index and the sequence-like behaviour of AUTOINCREMENT, including a gap in the numbering. The query plan shows whether the index is used.

SQL View, index and AUTOINCREMENT

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

Read the two query plans: the first says the engine will SCAN book (read every row), the second says SEARCH book USING INDEX idx_book_genre. In the sequence part, Ghost took id 6 and was deleted, yet Kim gets id 7: a column declared with AUTOINCREMENT never reuses a number it has handed out, so the table has a gap at 6. (SQLite keeps its counter in an internal table that takes part in the transaction, so unlike a PostgreSQL or Oracle sequence it is rolled back together with a rolled-back insert.) The catalog query at the end lists the table, the view and the index together, which shows that all of them are objects of one kind of directory.

Which statement is which?

When you read an unfamiliar statement, ask two questions: does it change the structure or the data, and can it be undone?

Statement Family Undoable with ROLLBACK?
CREATE INDEX ... DDL PostgreSQL, SQLite: yes. Oracle, MySQL: no (auto-commit)
DELETE FROM t WHERE ... DML Yes, until you commit
SELECT ... DQL Nothing to undo
GRANT ... DCL Depends on the product; treat it as permanent
COMMIT TCL It is the thing that makes changes permanent

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which family does ALTER TABLE student ADD COLUMN email TEXT belong to?

    It changes the structure (the schema), not the rows, so it is Data Definition Language.
  2. 2. Which statement is Data Control Language?

    GRANT and REVOKE decide who may do what. COMMIT is TCL, INSERT is DML and CREATE VIEW is DDL.
  3. 3. What is true about a view?

    An ordinary view holds no data. It stores the query text, which is why it can hide complexity but does not make queries faster by itself.
  4. 4. Why can a sequence leave gaps in the numbers it produces?

    Sequences must stay fast under concurrency, so they do not take part in rollback. Never rely on gap-free numbering.
  5. 5. What is the purpose of a synonym?

    A synonym is an alias. The real table can be moved or renamed, and only the synonym definition changes.

Summary

  • SQL statements fall into DDL (structure), DML (rows), DQL (queries), DCL (permissions) and TCL (transaction control).
  • DDL updates the catalog and often auto-commits; DML and TCL work together to make changes atomic.
  • Tables store data; views are named queries; indexes speed up access without changing answers; sequences produce unique numbers (with gaps); synonyms are aliases.
  • Every object, whatever its kind, is recorded in the catalog.

Next: DBMS Interfaces and a Short History of Databases.