Multiple choiceQuestion 1 · Recall
Loading…
By the end you can look at any SQL statement and say whether it is DDL or DML, say exactly what it changes, and explain why one language is the industry standard for both jobs.
Think of a spreadsheet. It has a structure — the sheet names, the column headings, the fact that "Date of birth" holds a date and not a paragraph. And it has data — the actual cells.
These are separate things, and you can change one without touching the other. You can rename the column "DOB" to "Date of birth" and every cell underneath stays exactly as it was.
A database works the same way, which is why it needs two kinds of statement:
SQL is the language for both, but the two jobs have their own names.
DDL is the Data Definition Language: the part of SQL that defines and modifies the structure of the database.
| DDL statement | What it does |
|---|---|
CREATE DATABASE |
makes a new, empty database |
CREATE TABLE |
makes a new table and defines its fields and data types |
ALTER TABLE |
changes an existing table: adds/removes a field, adds a key |
DROP TABLE |
deletes a table and its structure completely |
The keyword at the start gives it away: CREATE, ALTER and DROP are all about
definitions.
This is the important link back to the DBMS: the DBMS carries out all creation and modification of the structure using its DDL. You never edit the data dictionary by hand. You send a DDL statement, and the DBMS updates the data dictionary to match — field names, data types, keys and constraints all at once.
DML is the Data Manipulation Language: the part of SQL that queries the data and keeps it up to date.
| DML statement | What it does |
|---|---|
SELECT |
reads rows that match a condition |
INSERT INTO |
adds new rows |
UPDATE |
changes values in existing rows |
DELETE FROM |
removes rows |
Notice what is not in that list: nothing here creates or renames a field. DML works on the data inside a structure that already exists.
SELECT is the odd one out in the list — it changes nothing at all. It is still DML because
it manipulates data: it retrieves and combines it.
SQL stands for Structured Query Language. It is the industry standard for both DDL and DML.
"Standard" here is not a marketing word. SQL is published as an official standard by the standards bodies ISO and ANSI, and every major relational DBMS — MySQL, SQL Server, Oracle, PostgreSQL — implements it.
Three consequences worth remembering:
The word mostly matters. Each DBMS adds its own extra commands and small differences (dialects), and they also report errors slightly differently. Cambridge questions use the standard sub-set, which is the part you are learning here.
SQL (Structured Query Language)
The standard language for relational databases. Its DDL part creates and modifies the structure (database, tables, fields, keys); its DML part queries and maintains the data.
SQL is declarative. You describe the result you want; you do not describe the steps the computer should take to get it.
Write SELECT FirstName FROM Student WHERE Average > 60 and you never mention which disk
block to read, which index to use, or in what order to visit rows. The DBMS's query
processor works that out — the same tool you met in topic 8.2.
Analogy: ordering a coffee. You say "a flat white, oat milk". You do not specify the order of the grinding, tamping and steaming. Where it breaks down: if the coffee shop has no oat milk, you get an error message — and SQL errors are just as blunt.
Worked example: Classifying statements you have never seen
For each statement, say whether it is DDL or DML and what it does to the database.
ALTER TABLE Student ADD Email VARCHAR(60);DELETE FROM Student WHERE ClassID = 4;CREATE DATABASE Attendance;SELECT ClassID, Teacher FROM Class;