The SQL Environment: DDL / DML / DCL, Catalog & Schema

One language, three jobs

SQL looks like a single language, but its commands split into three functional families, and the exam routinely asks you to drop a command into the right bucket:

- Data Definition Language (DDL) — commands that define *structure*: CREATE, ALTER, DROP (plus TRUNCATE in many systems). They build and change tables, views, indexes, and constraints. In a production shop DDL is usually restricted to the DBA, because changing structure is high-stakes. DDL is the work of the *Physical Design* and *Implementation* SDLC phases. - Data Manipulation Language (DML) — commands that work with *data inside the structure*: SELECT, INSERT, UPDATE, DELETE. This is what most users run all day; SELECT is by far the most complex and most-used SQL statement, and it carries you from Implementation through the entire Maintenance life of the database. - Data Control Language (DCL) — commands that *control access and transactions*: GRANT, REVOKE (and, in the broad sense, COMMIT / ROLLBACK). DCL is the DBA's security and transaction lever.

A one-line memory hook: DDL = what exists, DML = what the data says, DCL = who can do what.

Worked example: classify a command list

Given this list, label each command:

CREATE TABLE student ...   → DDL  (defines structure)
INSERT INTO student ...    → DML  (changes data)
SELECT * FROM student ...  → DML  (queries data)
GRANT SELECT ON student    → DCL  (controls access)
ALTER TABLE student ...    → DDL  (changes structure)
REVOKE SELECT ON student   → DCL  (controls access)

The test is always *what does it touch* — the schema (DDL), the rows (DML), or the permissions/transaction (DCL)?

Catalog, schema, and the information schema

The DBMS keeps a self-describing hierarchy of metadata:

- A catalog is the *complete* description of one database — the full set of schemas taken together. The DBMS maintains it automatically: every CREATE TABLE or DROP VIEW you run silently updates the catalog. You never hand-edit it; you can browse it with ordinary SELECTs. - A schema is a named collection of objects (base tables, views, domains, constraints, triggers) belonging to one user or application — a *namespace* inside a catalog. Many schemas can live in one catalog, so the same table name can exist in two schemas without collision (you qualify with owner.table to disambiguate). - Every catalog contains at least one information schema that describes all the other schemas and their objects — metadata about the metadata.

The analogy that sticks: a catalog is the whole library's card catalog (it describes everything in the library), while a schema is one author's shelf within it. A company might keep a DEV catalog and a PROD catalog, each with its own schemas, so development never touches live business data.