In simple terms
A friendly intro before the formal notes — no formulas yet.
Database Blueprints vs. Day-to-Day Operations
DDL is like the architect's blueprint, defining the structure of the database (tables, columns). DML is like the daily activity within the building, adding, changing, and retrieving the information stored inside.
Imagine building a library. The DDL is the process of designing the building itself: creating rooms (tables), deciding what each room is for, and building the shelves (columns) with specific rules, like 'this shelf only holds fiction books'. The DML is what the librarians and visitors do every day: putting new books on the shelves (INSERT), moving a book to a different shelf (UPDATE), finding a specific book (SELECT), or removing a damaged book (DELETE). You need the structure first (DDL) before you can manage the contents (DML).
- 1
DDL defines structure: CREATE, ALTER, DROP tables.
- 2
DML queries data: SELECT … FROM … WHERE …
- 3
DML modifies data: INSERT, UPDATE, DELETE.
- 4
PRIMARY KEY, FOREIGN KEY, NOT NULL enforce integrity.
Explore the concept
Use the live diagram and synced steps — play it or tap a step card to walk through.
Full topic notes
Formal explanation with the rigour you need for the exam.
Data Definition Language (DDL)
DDL is concerned with the schema of the database. Think of it as the architectural blueprint. You use DDL commands to build, modify, and demolish the objects that hold the data, such as tables, indexes, and constraints. These commands do not interact with the individual records but rather the containers for those records.
CREATE: Used to build new database objects. The most common is CREATE TABLE.
ALTER: Used to modify the structure of an existing object. For example, ALTER TABLE can add a new column.
DROP: Used to permanently delete an entire database object. For example, DROP TABLE removes a table and all its data.
Data Manipulation Language (DML)
Once the database structure is defined with DDL, you need a way to manage the data within it. This is the role of DML. DML commands are used for all forms of data interaction: adding new data, retrieving existing data, modifying it, and deleting it. These are the 'day-to-day' operations of a database.
SELECT: Retrieves data from one or more tables. Often used with FROM and WHERE clauses.
INSERT: Adds new rows of data into a table. The INSERT INTO command is used.
UPDATE: Modifies existing records within a table. A WHERE clause is critical to specify which records to change.
DELETE: Removes existing records from a table. A WHERE clause is essential to specify which records to remove.
A very common mistake is forgetting the WHERE clause on an UPDATE or DELETE statement. Without a WHERE clause, UPDATE will change every single row in the table, and DELETE will remove every single row. In an exam, always include a WHERE clause with these commands unless you are explicitly asked to affect all records.
Enforcing Data Integrity with DDL
A key function of DDL is not just to create tables, but to define rules that maintain the quality and consistency of the data. This is known as data integrity. These rules are implemented as constraints during table creation or modification.
Entity Integrity: Enforced by the PRIMARY KEY constraint. It ensures that each row in a table is a unique entity and can be specifically identified.
Referential Integrity: Enforced by the FOREIGN KEY constraint. It ensures that a value in one table that refers to a record in another table is valid. For example, you cannot assign a student to a CourseID that doesn't exist in the Course table.
Domain Integrity: Enforced by data types, NOT NULL, and CHECK constraints. It ensures that values in a column are valid for that column's domain (e.g., a DateOfBirth column only contains valid dates, a Score column is between 0 and 100).
Worked examples
See the formulas applied — reveal one step at a time, like the exam.
Write a DDL statement to create a table named Tutor to store information about school tutors. The table should include a unique ID, the tutor's first name, last name, and subject. The ID should be the primary key, and the names cannot be empty. Define appropriate data types.
- 1
CREATE TABLE Tutor ( TutorID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Subject VARCHAR(100) );Explanation:
- CREATE TABLE Tutor (...): This is the DDL command to create a new table named Tutor.
- TutorID INT PRIMARY KEY: Defines a column TutorID of type Integer and sets it as the PRIMARY KEY. This ensures every tutor has a unique, non-null ID.
- FirstName VARCHAR(50) NOT NULL: Defines a column for the first name, allowing up to 50 characters. The NOT NULL constraint ensures this field must have a value.
- LastName VARCHAR(50) NOT NULL: Same as above for the last name.
- Subject VARCHAR(100): Defines a column for the subject, allowing up to 100 characters. This can be left NULL if a tutor's subject is not yet assigned.
Using the Tutor table created previously, perform the following DML operations:
- Add a new tutor: Dr. Alan Turing, who teaches Computer Science and has TutorID 101.
- Change the subject for TutorID 101 to 'Advanced Computer Science'.
- Retrieve the first and last names of all tutors who teach a subject containing the word 'Computer'.
- 1
1. Insert a new record:
INSERT INTO Tutor (TutorID, FirstName, LastName, Subject) VALUES (101, 'Alan', 'Turing', 'Computer Science');Explanation: The INSERT INTO command specifies the table and the columns to be populated. The VALUES clause provides the data for the new row, in the corresponding order.
How it all connects
The big idea sits in the middle — tap a linked idea to explore the link.
Tap a linked idea to see how it connects back to the main topic — that connection is what examiners reward.
Glossary
Try to recall each definition before you reveal it.
Quick check
Answer in your head first — then tap to check. No pressure.
Revision flashcards
Flip the card. Test yourself before the exam.
What is Data Definition Language (DDL)?
A subset of SQL used to define and manage the database structure or schema. Its commands affect the tables themselves, not the data inside them.
Key takeaways
Review these before you close the topic — retrieval beats re-reading.
- ✓
CREATE: Used to build new database objects. The most common is CREATE TABLE.
- ✓
ALTER: Used to modify the structure of an existing object. For example, ALTER TABLE can add a new column.
- ✓
DROP: Used to permanently delete an entire database object. For example, DROP TABLE removes a table and all its data.
Practice — then mark it
The whole point: a real Cambridge question, marked mark-by-mark.
Test Your Knowledge on DDL & DML
Test Your Knowledge on DDL & DML
Extra simulations & links
PhET, GeoGebra and other curated tools — open in a new tab.
Frequently asked
Checkpoint
One marked question is worth ten re-reads — close the loop before you move on.
Reading it isn’t knowing it — prove it.
Before you move on: do Test Your Knowledge on DDL & DML on paper, snap a photo, and get examiner-style feedback on exactly where you win and lose marks.