Why is "Writing an UPDATE or DELETE without a WHERE clause, so every row in the table is changed or removed." a common mistake in Data Definition Language (DDL) and Data Manipulation Language (DML)?

Why it happens: Students focus on the SET / DELETE FROM part and forget the filter that limits which rows are affected. How to avoid it: Always ask 'which rows should this affect?' and add a WHERE clause — usually matching the primary key — unless every row really is intended. Source: Examiner reports note marks lost where UPDATE/DELETE answers omit the WHERE clause.

Why is "Classifying commands wrongly — e.g. calling SELECT a DDL command or thinking CREATE TABLE manipulates data." a common mistake in Data Definition Language (DDL) and Data Manipulation Language (DML)?

Why it happens: Both DDL and DML are written in SQL, so the two roles blur together. How to avoid it: Remember the split: DDL = structure (CREATE, ALTER); DML = the data inside it (SELECT, INSERT, UPDATE, DELETE).

Why is "Using an aggregate function (SUM/COUNT/AVG) alongside a normal field but omitting GROUP BY." a common mistake in Data Definition Language (DDL) and Data Manipulation Language (DML)?

Why it happens: Students see the aggregate gives one total and forget they also need one result per category. How to avoid it: Whenever you SELECT a field together with an aggregate 'per category', add GROUP BY on that field.

Why is "Writing an INNER JOIN without a correct ON condition, or joining on the wrong fields." a common mistake in Data Definition Language (DDL) and Data Manipulation Language (DML)?

Why it happens: Students know two tables are needed but are unsure which fields link them. How to avoid it: Join on the foreign key = primary key pair: ON ChildTable.ForeignKey = ParentTable.PrimaryKey. Qualify ambiguous field names with the table name.

Why is "Using non-Cambridge data type names (e.g. STRING, INT, BOOL) or omitting the length on VARCHAR." a common mistake in Data Definition Language (DDL) and Data Manipulation Language (DML)?

Why it happens: Students mix in names from programming languages or other SQL dialects. How to avoid it: Use the exact 9618 data types: CHARACTER, VARCHAR(n), BOOLEAN, INTEGER, REAL, DATE, TIME — and always give VARCHAR a length, e.g. VARCHAR(40).

Why is "Forgetting quotes around text/date values, mismatching the field and value order in INSERT, or leaving off the terminating semicolon." a common mistake in Data Definition Language (DDL) and Data Manipulation Language (DML)?

Why it happens: Under time pressure students copy structure but miss the small syntax details examiners check. How to avoid it: Put text and dates in single quotes ('S2045', '2009-03-14'); list VALUES in the same order as the field list; end each statement with a semicolon.

Cambridge International A Levels Computer Science (9618)

Data Definition Language (DDL) and Data Manipulation Language (DML)

0% Complete

Detailed Study Notes

Detailed notes on Databases for Cambridge International A Levels Computer Science, covering key concepts, explanations, examples, and exam-focused revision points.

DDL and DML — Cambridge AS & A Level Computer Science 9618 Study Notes (2027-2029)

How a DBMS uses SQL to build a database's structure (DDL) and to query and maintain its data (DML). Learn to write CREATE TABLE, ALTER TABLE, primary and foreign keys, SELECT with WHERE / ORDER BY / GROUP BY / INNER JOIN and aggregate functions, plus INSERT, UPDATE and DELETE — all in the exact SQL syntax Cambridge expects.

At a glance

SQL is the industry-standard language for both defining and manipulating a relational database.
DDL = structure (CREATE DATABASE, CREATE TABLE, ALTER TABLE) — it builds and changes tables and keys.
DML = the data inside (SELECT, INSERT INTO, UPDATE, DELETE) — it queries and maintains records.
9618 data types: CHARACTER, VARCHAR(n), BOOLEAN, INTEGER, REAL, DATE, TIME.
Keys: PRIMARY KEY (field) uniquely identifies a row; FOREIGN KEY (field) REFERENCES Table(Field) links tables.
Query one table with SELECT … FROM … WHERE … ORDER BY ….
Combine two tables with INNER JOIN … ON foreignKey = primaryKey (queries span at most two tables).
Summarise data with GROUP BY and the aggregates SUM, COUNT, AVG.
Maintain data with INSERT INTO, UPDATE … SET … WHERE …, DELETE FROM … WHERE … — never forget the WHERE.

What you’ll learn

Mapped to the Cambridge International A Level 9618 syllabus (2027-2029).

8.3 — Show understanding that a DBMS uses a Data Definition Language (DDL) to create and modify the structure of a database.
8.3 — Show understanding that a DBMS uses a Data Manipulation Language (DML) to query and maintain the data in a database.
8.3 — Show understanding that SQL is the industry standard for both DDL and DML.
8.3 — Write DDL statements: CREATE DATABASE, CREATE TABLE (with data types CHARACTER, VARCHAR, BOOLEAN, INTEGER, REAL, DATE, TIME), ALTER TABLE, PRIMARY KEY and FOREIGN KEY … REFERENCES.
8.3 — Write DML queries using SELECT, FROM, WHERE, ORDER BY, GROUP BY, INNER JOIN and the aggregate functions SUM, COUNT and AVG (queries span at most two tables).
8.3 — Write DML maintenance statements: INSERT INTO, UPDATE and DELETE.

SQL, DDL and DML — the big picture

One language (SQL) does two jobs: define the structure, then work with the data.

A Database Management System (DBMS) is the software that sits between users (and programs) and the actual stored data. To talk to it we use SQL (Structured Query Language) — the industry standard for relational databases. SQL has two halves, doing two very different jobs:

Part	Job	Typical commands
DDL — Data Definition Language	Create and modify the structure of the database (tables, fields, data types, keys)	`CREATE DATABASE`, `CREATE TABLE`, `ALTER TABLE`
DML — Data Manipulation Language	Query and maintain the data held in the tables	`SELECT`, `INSERT INTO`, `UPDATE`, `DELETE`

A simple way to remember it: DDL builds the empty filing cabinet and labels the drawers; DML puts paper in, reads it, edits it and throws it away. Both are written in SQL, but only DDL changes the shape of the database.

SQL is the industry standard for relational databases.
DDL = define/modify structure (CREATE, ALTER).
DML = query and maintain data (SELECT, INSERT, UPDATE, DELETE).
Both are SQL; only DDL changes the table structure.

Defining structure with DDL

CREATE TABLE with data types and keys; ALTER TABLE to change an existing table.

CREATE DATABASE makes a new, empty database:

CREATE DATABASE SportsCentre;

CREATE TABLE defines a table by listing each field with a data type, then declaring the keys. The 9618 data types are:

Data type	Use it for	Example
`CHARACTER` / `CHARACTER(n)`	a single / fixed-length character code	`'M'`, `'S2045'`
`VARCHAR(n)`	text up to n characters	`'Mia Khan'`
`BOOLEAN`	true / false	`TRUE`, `FALSE`
`INTEGER`	whole numbers	`42`
`REAL`	numbers with a fractional part	`19.99`
`DATE`	a calendar date	`'2009-03-14'`
`TIME`	a time of day	`'14:30:00'`

A full table with both a primary key and a foreign key:

CREATE TABLE Booking (
  BookingID INTEGER,
  CourtID CHARACTER(3),
  Hours INTEGER,
  MemberID CHARACTER(6),
  PRIMARY KEY (BookingID),
  FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
);

PRIMARY KEY (field) — the field that uniquely identifies each row.
FOREIGN KEY (field) REFERENCES Table(Field) — a field that links to the primary key of another table, enforcing the relationship.

ALTER TABLE changes an existing table — for example to add a key to a table that was created without one:

ALTER TABLE Booking
ADD FOREIGN KEY (MemberID) REFERENCES Member(MemberID);

CREATE TABLE lists each field as 'FieldName DataType'.
Use the exact 9618 data types; give VARCHAR a length, e.g. VARCHAR(40).
PRIMARY KEY (field) = unique row identifier.
FOREIGN KEY (field) REFERENCES Table(Field) = link between tables.
ALTER TABLE changes the structure of an existing table.

See the full worked example for data definition language (ddl) and data manipulation language (dml) →

Querying data with SELECT

SELECT … FROM … WHERE … ORDER BY — pick fields, filter rows, sort the result.

The core of DML is the SELECT query. Its building blocks always appear in this order:

SELECT FieldList        -- which columns to show
FROM TableName          -- which table to read
WHERE condition         -- which rows to keep (optional)
ORDER BY Field ASC/DESC; -- how to sort (optional)

Example — list unpaid students, A–Z by name:

SELECT StudentID, FullName
FROM Student
WHERE FeesPaid = FALSE
ORDER BY FullName;

SELECT lists the fields you want (use * only when you truly want every field).
WHERE filters rows using conditions and operators such as =, <, >, <=, >=, <>, and AND / OR.
ORDER BY sorts the output; ASC is ascending (default), DESC is descending.

Text and date values in conditions go in single quotes (WHERE CourtID = 'C3'); numbers and BOOLEAN keywords do not.

SELECT fields → FROM table → WHERE filter → ORDER BY sort.
WHERE conditions use =, <, >, <=, >=, <>, AND, OR.
ORDER BY … ASC (default) or DESC.
Quote text and dates; leave numbers and TRUE/FALSE unquoted.

See the full worked example for data definition language (ddl) and data manipulation language (dml) →

Combining and summarising: INNER JOIN, GROUP BY, aggregates

Join two tables on a key; summarise groups with SUM, COUNT and AVG.

INNER JOIN combines rows from two tables wherever a matching key value exists in both — normally the foreign key of one table equals the primary key of the other. (9618 queries use at most two tables.)

SELECT Booking.BookingID, Booking.CourtID, Member.MemberName
FROM Booking
INNER JOIN Member
  ON Booking.MemberID = Member.MemberID;

Qualify field names with the table name (Booking.CourtID) when the same name could appear in both tables.

Aggregate functions work out a single summary value over many rows. 9618 uses three:

Function	Returns
`SUM(field)`	the total
`COUNT(field)` / `COUNT(*)`	the number of rows
`AVG(field)`	the mean (average)

GROUP BY is what turns an aggregate into "one value per category". Without it, the aggregate collapses the whole table into a single row.

SELECT CourtID, SUM(Hours)
FROM Booking
GROUP BY CourtID
ORDER BY SUM(Hours) DESC;

This gives the total hours for each court, busiest first.

INNER JOIN … ON foreignKey = primaryKey combines two tables.
Qualify shared field names as Table.Field.
Aggregates: SUM (total), COUNT (rows), AVG (mean).
GROUP BY gives one aggregate value per category.
Queries span at most two tables in 9618.

See the full worked example for data definition language (ddl) and data manipulation language (dml) →

Maintaining data: INSERT, UPDATE, DELETE

Add, change and remove records — and always restrict UPDATE/DELETE with WHERE.

Beyond querying, DML keeps the data up to date.

INSERT INTO adds a new record. Name the fields, then give matching VALUES in the same order:

INSERT INTO Student (StudentID, FullName, DateOfBirth, FeesPaid)
VALUES ('S2045', 'Mia Khan', '2009-03-14', FALSE);

UPDATE changes existing records. SET says what to change; WHERE says which rows:

UPDATE Student
SET FeesPaid = TRUE
WHERE StudentID = 'S2045';

DELETE FROM removes whole records (rows), with WHERE choosing which:

DELETE FROM Booking
WHERE CourtID = 'C3';

The single most important rule on this topic: an UPDATE or DELETE without a WHERE clause affects every row in the table. Always include a WHERE clause (usually matching the primary key) unless you genuinely mean to change or delete everything.

Note the difference between DELETE and ALTER TABLE: DELETE removes rows of data (DML); to remove a field/column you would change the structure with ALTER TABLE (DDL).

INSERT INTO Table (fields) VALUES (...) — values in the same order.
UPDATE Table SET field = value WHERE condition.
DELETE FROM Table WHERE condition — removes whole rows.
Never run UPDATE/DELETE without WHERE unless you mean ALL rows.
DELETE removes data; ALTER TABLE changes structure.

See the full worked example for data definition language (ddl) and data manipulation language (dml) →

Quick recap

SQL is the industry standard; DDL defines structure, DML works with the data.
CREATE TABLE lists fields with 9618 data types and declares PRIMARY KEY / FOREIGN KEY; ALTER TABLE changes an existing table.
FOREIGN KEY (field) REFERENCES Table(Field) links two tables.
SELECT … FROM … WHERE … ORDER BY queries one table; INNER JOIN … ON joins two.
GROUP BY with SUM / COUNT / AVG gives one summary value per category.
INSERT INTO adds records; UPDATE SET changes them; DELETE FROM removes whole rows.
Always restrict UPDATE and DELETE with a WHERE clause (usually the primary key).

Memorise this

Verbatim phrases and definitions Cambridge mark schemes credit.

DDL = structure (CREATE, ALTER); DML = data (SELECT, INSERT, UPDATE, DELETE).
Data types: CHARACTER, VARCHAR(n), BOOLEAN, INTEGER, REAL, DATE, TIME.
PRIMARY KEY (field); FOREIGN KEY (field) REFERENCES Table(Field).
SELECT fields FROM Table WHERE condition ORDER BY field;
INNER JOIN Table2 ON Table1.FK = Table2.PK.
Aggregates: SUM, COUNT, AVG — used with GROUP BY for one value per group.
UPDATE/DELETE without WHERE changes/removes EVERY row.

How it’s examined

Section 8.3 is assessed in Paper 1 (Theory Fundamentals, 1 h 30 min, 75 marks) — written, externally assessed, no calculator. Expect questions that ask you to write SQL: a CREATE TABLE with correct data types and keys, a SELECT with WHERE / ORDER BY, an INNER JOIN across two tables, a GROUP BY query using an aggregate, and INSERT / UPDATE / DELETE maintenance statements. Use exact SQL syntax — examiners credit correct keywords, data types, the ON condition on a JOIN and the presence of a WHERE clause on UPDATE/DELETE. Queries are limited to at most two tables.

Sources: Cambridge International AS & A Level Computer Science 9618 syllabus (2027-2029), section 8.3. Last reviewed 2026-06-21.

Take this whole topic with you

Step-by-step worked examples — Data Definition Language (DDL) and Data Manipulation Language (DML)

Step-by-step solutions to past-paper-style questions on data definition language (ddl) and data manipulation language (dml), written exactly the way a tutor would explain them at the board.

Question type:

1Create a table with data types and a primary key
Getting startedMulti-step problem• 9618 Paper 1 style• DDL, CREATE TABLE, primary key, AO2
Question
A school stores its students in a table named Student with the fields StudentID (a short code, primary key), FullName (up to 40 characters), DateOfBirth and FeesPaid (true/false). Write the SQL DDL statement to create this table. (4 marks)
Step-by-step solution
1. Step 1
  Begin with CREATE TABLE followed by the table name and an opening bracket. Each field is then listed as 'FieldName DataType'.
2. Step 2
  Choose suitable Cambridge data types: a short fixed code suits CHARACTER (or VARCHAR), a name suits VARCHAR(40), a calendar date suits DATE and a true/false flag suits BOOLEAN.
3. Step 3
  Add the PRIMARY KEY clause naming the field that uniquely identifies each row, then close the bracket and end with a semicolon.
Answer
CREATE TABLE Student ( StudentID CHARACTER(6), FullName VARCHAR(40), DateOfBirth DATE, FeesPaid BOOLEAN, PRIMARY KEY (StudentID) );
Examiner tip
1 mark CREATE TABLE Student (...), 1 mark for sensible data types, 1 mark for VARCHAR(40) showing a length, 1 mark for PRIMARY KEY (StudentID). Use Cambridge data type names exactly: CHARACTER, VARCHAR(n), BOOLEAN, INTEGER, REAL, DATE, TIME.
2Query one table with WHERE and ORDER BY
Getting startedWord problem• 9618 Paper 1 style• DML, SELECT, WHERE, ORDER BY, AO2
Question
Using the Student table, write an SQL query to list the StudentID and FullName of every student who has not paid their fees, sorted alphabetically by FullName. (3 marks)
Step-by-step solution
1. Step 1
  SELECT the two fields the question asks for: StudentID and FullName, FROM the Student table.
2. Step 2
  Add a WHERE clause to filter the rows. 'Not paid' means the BOOLEAN FeesPaid is FALSE.
3. Step 3
  Add ORDER BY FullName to sort the output. Ascending (A–Z) is the default, so ASC is optional.
Answer
SELECT StudentID, FullName FROM Student WHERE FeesPaid = FALSE ORDER BY FullName;
Examiner tip
1 mark SELECT the correct two fields FROM Student, 1 mark for the correct WHERE condition, 1 mark for ORDER BY FullName. Only return the fields asked for — using SELECT * here would lose the field-selection mark.
3Insert a new record and update an existing one
Building confidenceMulti-step problem• 9618 Paper 1 style• DML, INSERT, UPDATE, AO2
Question
(a) A new student joins: StudentID 'S2045', FullName 'Mia Khan', DateOfBirth 2009-03-14, fees not yet paid. Write the SQL to add this record. (b) Mia then pays her fees. Write the SQL to update only her record. (4 marks)
Step-by-step solution
1. Step 1
  For the insert, name the table and the fields being filled, then give the matching VALUES in the same order. Text and dates go in quotes; BOOLEAN values do not.
2. Step 2
  For the update, use UPDATE Student SET FeesPaid = TRUE, then a WHERE clause so that ONLY Mia's row changes — identify her by her primary key.
3. Step 3
  The WHERE clause is essential: without it, UPDATE would set FeesPaid = TRUE for every student in the table.
Answer
-- (a) add the new student INSERT INTO Student (StudentID, FullName, DateOfBirth, FeesPaid) VALUES ('S2045', 'Mia Khan', '2009-03-14', FALSE); -- (b) update only Mia's record UPDATE Student SET FeesPaid = TRUE WHERE StudentID = 'S2045';
Examiner tip
(a) 2 marks: correct INSERT INTO ... (fields) and VALUES (...) with values in the matching order and quoted correctly. (b) 2 marks: UPDATE ... SET FeesPaid = TRUE (1) and a WHERE clause restricting it to S2045 (1). A missing WHERE on UPDATE is the classic costly error.
4Aggregate and GROUP BY
Building confidenceMulti-step problem• 9618 Paper 1 style• DML, GROUP BY, COUNT, aggregate, AO2
Question
A table Booking(BookingID, CourtID, Hours, MemberID) records sports-court bookings. Write an SQL query that, for each court, outputs the CourtID and the total number of hours booked, ordered from the busiest court to the least busy. (5 marks)
Step-by-step solution
1. Step 1
  We need a value per court, so the rows must be grouped by CourtID using GROUP BY.
2. Step 2
  'Total number of hours' is a sum across each group, so use the aggregate function SUM(Hours).
3. Step 3
  SELECT the grouping field (CourtID) and the aggregate SUM(Hours). The grouping field listed in SELECT must also appear in GROUP BY.
4. Step 4
  'Busiest first' means largest total first, so ORDER BY the summed value in descending order with DESC.
Answer
SELECT CourtID, SUM(Hours) FROM Booking GROUP BY CourtID ORDER BY SUM(Hours) DESC;
Examiner tip
Marks: SELECT CourtID (1), SUM(Hours) (1), GROUP BY CourtID (1), ORDER BY ... DESC (1), overall valid syntax (1). A frequent error is using SUM without GROUP BY (which collapses everything into one row) — you need GROUP BY to get one total per court.
5INNER JOIN across two tables with a foreign key
StretchMulti-step problem• 9618 Paper 1 style• DML, INNER JOIN, foreign key, AO2
Question
Two tables hold the data: Member(MemberID, MemberName) and Booking(BookingID, CourtID, Hours, MemberID), where Booking.MemberID is a foreign key referencing Member. Write an SQL query to list each booking's BookingID and CourtID together with the MemberName of the member who made it. (5 marks)
Step-by-step solution
1. Step 1
  The required output comes from two tables, so they must be combined with an INNER JOIN.
2. Step 2
  Join the tables on the matching key columns: Booking.MemberID must equal Member.MemberID. This is the ON condition.
3. Step 3
  SELECT the requested fields, qualifying each with its table name (Table.Field) to avoid ambiguity where a field name appears in both tables.
4. Step 4
  BookingID and CourtID come from Booking; MemberName comes from Member. List them in the SELECT clause.
Answer
SELECT Booking.BookingID, Booking.CourtID, Member.MemberName FROM Booking INNER JOIN Member ON Booking.MemberID = Member.MemberID;
Examiner tip
Marks: SELECT the correct three fields (1), FROM one table (1), INNER JOIN the other (1), correct ON condition matching the key fields (1), overall valid syntax (1). The ON condition must link the foreign key to the primary key — joining on the wrong fields is the commonest INNER JOIN error.
6ALTER TABLE to add a foreign key, then DELETE rows
StretchMulti-step problem• 9618 Paper 1 style• DDL, DML, ALTER TABLE, FOREIGN KEY, DELETE, AO2
Question
(a) The Booking table already exists but has no defined relationship to Member. Write the SQL DDL to add a foreign key so that Booking.MemberID references Member(MemberID). (b) Write the SQL to delete every booking that is for CourtID 'C3'. (5 marks)
Step-by-step solution
1. Step 1
  Changing the structure of an existing table is DDL — use ALTER TABLE Booking.
2. Step 2
  Add the constraint with ADD FOREIGN KEY (MemberID) REFERENCES Member(MemberID). The field in brackets is the foreign key; REFERENCES names the table and primary-key field it points to.
3. Step 3
  Deleting data is DML, not DDL. Use DELETE FROM Booking — DELETE always removes whole rows, never individual fields.
4. Step 4
  Add a WHERE clause so only the C3 bookings are removed: WHERE CourtID = 'C3'. Without it, every row in Booking would be deleted.
Answer
-- (a) add the foreign key (DDL) ALTER TABLE Booking ADD FOREIGN KEY (MemberID) REFERENCES Member(MemberID); -- (b) delete the C3 bookings (DML) DELETE FROM Booking WHERE CourtID = 'C3';
Examiner tip
(a) 3 marks: ALTER TABLE Booking (1), FOREIGN KEY (MemberID) (1), REFERENCES Member(MemberID) (1). (b) 2 marks: DELETE FROM Booking (1), WHERE CourtID = 'C3' (1). Note DELETE removes rows; to remove a column you would ALTER TABLE instead — don't confuse the two.

Model Answers — Data Definition Language (DDL) and Data Manipulation Language (DML)

High-scoring sample answers for data definition language (ddl) and data manipulation language (dml) on the Cambridge IGCSE paper, with examiner-style notes mapping each response to the mark scheme and assessment objectives.

Question 1
9618 Paper 1 style3 marks
A table Product(ProductID, ProductName, Price, InStock) is used by an online shop. Write an SQL query to display the ProductName and Price of every product that costs less than 20. (3 marks)
Model answer
SELECT ProductName, Price FROM Product WHERE Price < 20;

This selects only the two requested fields, takes them from the Product table, and the WHERE clause keeps only rows whose Price is below 20.
Why this scores
1 mark SELECT the correct fields, 1 mark FROM Product, 1 mark for the correct WHERE condition (Price < 20). Listing only the fields asked for is expected; SELECT * would not gain the field-selection mark.
Question 2
9618 Paper 1 style4 marks
Define the terms Data Definition Language (DDL) and Data Manipulation Language (DML), and give one example SQL command for each. (4 marks)
Model answer
Data Definition Language (DDL) is the subset of SQL used to create and modify the structure of a database — for example its tables, fields, data types and keys. Example command: CREATE TABLE (others: ALTER TABLE, CREATE DATABASE).

Data Manipulation Language (DML) is the subset of SQL used to query and maintain the data held inside the tables — retrieving, adding, changing and removing records. Example command: SELECT (others: INSERT INTO, UPDATE, DELETE).
Why this scores
1 mark per correct definition (DDL = structure, DML = the data within it) and 1 mark per valid matching example command. A common slip is giving SELECT as a DDL example — querying data is DML.
Question 3
9618 Paper 1 style4 marks
Using the table Product(ProductID, ProductName, Price, Category), write an SQL query that, for each Category, outputs the Category and the average Price of the products in that category. (4 marks)
Model answer
SELECT Category, AVG(Price) FROM Product GROUP BY Category;

The rows are grouped by Category with GROUP BY so that one result row is produced per category, and the aggregate function AVG(Price) calculates the mean price within each group.
Why this scores
Marks: SELECT Category (1), AVG(Price) (1), FROM Product (1), GROUP BY Category (1). Using an aggregate function alongside a non-aggregated field (Category) requires that field in GROUP BY — omitting GROUP BY is the usual error.
Question 4
9618 Paper 1 style5 marks
A library database has a table Member(MemberID, MemberName). Write the SQL DDL to create a Loan table with the fields LoanID (primary key, integer), BookTitle (up to 60 characters), DueDate, and MemberID, where MemberID is a foreign key referencing Member(MemberID). (5 marks)
Model answer
CREATE TABLE Loan ( LoanID INTEGER, BookTitle VARCHAR(60), DueDate DATE, MemberID CHARACTER(6), PRIMARY KEY (LoanID), FOREIGN KEY (MemberID) REFERENCES Member(MemberID) );

LoanID uses INTEGER and is set as the PRIMARY KEY; BookTitle uses VARCHAR(60); DueDate uses DATE; MemberID is declared as a FOREIGN KEY that REFERENCES the MemberID field of the Member table, enforcing the relationship between the two tables.
Why this scores
Marks: CREATE TABLE Loan with fields (1), suitable data types incl. VARCHAR(60) and DATE (1), PRIMARY KEY (LoanID) (1), FOREIGN KEY (MemberID) (1), REFERENCES Member(MemberID) (1). The foreign-key field's data type should match the primary key it references.
Question 5
9618 Paper 1 style6 marks
Two tables hold cinema data: Film(FilmID, Title, Rating) and Screening(ScreeningID, FilmID, ScreenNumber, StartTime), where Screening.FilmID is a foreign key referencing Film. Write an SQL query to list the Title and StartTime of every screening of films rated 'PG', ordered by StartTime. (6 marks)
Model answer
SELECT Film.Title, Screening.StartTime FROM Screening INNER JOIN Film ON Screening.FilmID = Film.FilmID WHERE Film.Rating = 'PG' ORDER BY Screening.StartTime;

The two tables are combined with an INNER JOIN matching Screening.FilmID to Film.FilmID. The WHERE clause keeps only films rated 'PG', the SELECT clause returns the requested Title and StartTime (qualified with their table names), and ORDER BY StartTime sorts the output.
Why this scores
Marks: SELECT the correct qualified fields (1), FROM one table (1), INNER JOIN the other (1), correct ON condition on the key fields (1), WHERE Rating = 'PG' (1), ORDER BY StartTime (1). The query must span exactly the two tables and join on the foreign-key/primary-key pair — joining on unrelated fields scores no JOIN marks.
Question 6
9618 Paper 1 style6 marks
A table Employee(EmpID, Name, Department, Salary) is maintained by an HR system. (a) Write the SQL to give every employee in the 'Sales' department a salary of 32000. (b) Write the SQL to remove the record of the employee whose EmpID is 'E118'. (6 marks)
Model answer
(a) Update only the Sales department:

UPDATE Employee SET Salary = 32000 WHERE Department = 'Sales';

(b) Delete the single employee record:

DELETE FROM Employee WHERE EmpID = 'E118';

In (a) the WHERE clause restricts the change to Sales employees only; in (b) DELETE FROM removes whole rows and the WHERE clause identifies the one row to remove by its primary key.
Why this scores
(a) 3 marks: UPDATE Employee (1), SET Salary = 32000 (1), WHERE Department = 'Sales' (1). (b) 3 marks: DELETE FROM Employee (1), WHERE clause (1), correct primary-key condition EmpID = 'E118' (1). Both statements lose data integrity without the WHERE clause — examiners specifically reward its presence.

Key Definitions and Keywords — Data Definition Language (DDL) and Data Manipulation Language (DML)

Definitions to memorise and the exact keywords mark schemes credit for data definition language (ddl) and data manipulation language (dml) answers — sharpened from recent examiner reports for the 2026 Cambridge IGCSE sitting.

SQL (Structured Query Language)
Examiner keyword
The industry-standard language used by a DBMS both to define a database's structure and to manipulate the data it holds. It is divided into DDL and DML commands.
Data Definition Language (DDL)
Examiner keyword
The subset of SQL used to create and modify the structure of a database — its tables, fields, data types and keys. Key commands: CREATE DATABASE, CREATE TABLE, ALTER TABLE.
Data Manipulation Language (DML)
Examiner keyword
The subset of SQL used to query and maintain the data inside the tables. Key commands: SELECT, INSERT INTO, UPDATE, DELETE.
CREATE TABLE
Examiner keyword
A DDL statement that defines a new table, listing each field with its data type and declaring the primary key and any foreign keys. Data types include CHARACTER, VARCHAR(n), BOOLEAN, INTEGER, REAL, DATE and TIME.
Example
CREATE TABLE Student (StudentID CHARACTER(6), PRIMARY KEY (StudentID));
ALTER TABLE
A DDL statement that changes the structure of an existing table, for example adding or removing a field or adding a primary/foreign key constraint.
PRIMARY KEY (clause)
Examiner keyword
A DDL clause declaring the field (or combination of fields) that uniquely identifies each record in a table. Written as PRIMARY KEY (FieldName).
FOREIGN KEY (clause)
Examiner keyword
A DDL clause declaring a field that links to the primary key of another table, enforcing the relationship between them. Written as FOREIGN KEY (Field) REFERENCES Table(Field).
SELECT
Examiner keyword
The DML command that retrieves data from one or more tables. The fields to return are listed after SELECT and the table(s) after FROM.
Example
SELECT FullName FROM Student;
WHERE
Examiner keyword
A clause that filters the rows returned (in SELECT) or affected (in UPDATE/DELETE) so that only rows meeting the given condition are included.
ORDER BY
A clause that sorts the rows of a query result by one or more fields. ASC gives ascending order (the default); DESC gives descending order.
INNER JOIN
Examiner keyword
A clause that combines rows from two tables where a matching value exists in both, typically the foreign key of one table equalling the primary key of the other (specified with ON).
Example
FROM Booking INNER JOIN Member ON Booking.MemberID = Member.MemberID
GROUP BY and aggregate functions
Examiner keyword
GROUP BY collects rows that share a value into groups so that an aggregate function can be applied per group. The aggregate functions in 9618 are SUM (total), COUNT (number of rows) and AVG (mean).

Common Mistakes and Misconceptions — Data Definition Language (DDL) and Data Manipulation Language (DML)

The traps other students keep falling into on data definition language (ddl) and data manipulation language (dml) questions — taken from recent Cambridge IGCSE examiner reports and mark schemes — and how to avoid them.

✕Writing an UPDATE or DELETE without a WHERE clause, so every row in the table is changed or removed.
Examiner reports note marks lost where UPDATE/DELETE answers omit the WHERE clause.
Why it happens
Students focus on the SET / DELETE FROM part and forget the filter that limits which rows are affected.
How to avoid it
Always ask 'which rows should this affect?' and add a WHERE clause — usually matching the primary key — unless every row really is intended.
✕Classifying commands wrongly — e.g. calling SELECT a DDL command or thinking CREATE TABLE manipulates data.
Why it happens
Both DDL and DML are written in SQL, so the two roles blur together.
How to avoid it
Remember the split: DDL = structure (CREATE, ALTER); DML = the data inside it (SELECT, INSERT, UPDATE, DELETE).
✕Using an aggregate function (SUM/COUNT/AVG) alongside a normal field but omitting GROUP BY.
Why it happens
Students see the aggregate gives one total and forget they also need one result per category.
How to avoid it
Whenever you SELECT a field together with an aggregate 'per category', add GROUP BY on that field.
✕Writing an INNER JOIN without a correct ON condition, or joining on the wrong fields.
Why it happens
Students know two tables are needed but are unsure which fields link them.
How to avoid it
Join on the foreign key = primary key pair: ON ChildTable.ForeignKey = ParentTable.PrimaryKey. Qualify ambiguous field names with the table name.
✕Using non-Cambridge data type names (e.g. STRING, INT, BOOL) or omitting the length on VARCHAR.
Why it happens
Students mix in names from programming languages or other SQL dialects.
How to avoid it
Use the exact 9618 data types: CHARACTER, VARCHAR(n), BOOLEAN, INTEGER, REAL, DATE, TIME — and always give VARCHAR a length, e.g. VARCHAR(40).
✕Forgetting quotes around text/date values, mismatching the field and value order in INSERT, or leaving off the terminating semicolon.
Why it happens
Under time pressure students copy structure but miss the small syntax details examiners check.
How to avoid it
Put text and dates in single quotes ('S2045', '2009-03-14'); list VALUES in the same order as the field list; end each statement with a semicolon.

Data Definition Language (DDL) and Data Manipulation Language (DML)

Detailed Study Notes

At a glance

What you’ll learn

Quick recap

Memorise this

How it’s examined

Take this whole topic with you

1Create a table with data types and a primary key

2Query one table with WHERE and ORDER BY

3Insert a new record and update an existing one

4Aggregate and GROUP BY

5INNER JOIN across two tables with a foreign key

6ALTER TABLE to add a foreign key, then DELETE rows

SQL (Structured Query Language)

Data Definition Language (DDL)

Data Manipulation Language (DML)

CREATE TABLE

ALTER TABLE

PRIMARY KEY (clause)

FOREIGN KEY (clause)

SELECT

WHERE

ORDER BY

INNER JOIN

GROUP BY and aggregate functions

✕Writing an UPDATE or DELETE without a WHERE clause, so every row in the table is changed or removed.

✕Classifying commands wrongly — e.g. calling SELECT a DDL command or thinking CREATE TABLE manipulates data.

✕Using an aggregate function (SUM/COUNT/AVG) alongside a normal field but omitting GROUP BY.

✕Writing an INNER JOIN without a correct ON condition, or joining on the wrong fields.

✕Using non-Cambridge data type names (e.g. STRING, INT, BOOL) or omitting the length on VARCHAR.

✕Forgetting quotes around text/date values, mismatching the field and value order in INSERT, or leaving off the terminating semicolon.