Cambridge Notes, Past Papers, Revision Questions

Cambridge A-Level Computer Science 9618 – 8.3 DDL and DML

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

Learning Objective

Write an SQL script to query or modify data (DML) that are stored in at most two database tables.

Key Concepts

DDL (Data Definition Language) – statements that define or modify the structure of database objects (tables, indexes, views, etc.).

DML (Data Manipulation Language) – statements that retrieve or change the data stored in those objects.

Typical DDL Statements

Command	Purpose	Example
CREATE TABLE	Define a new table and its columns.	CREATE TABLE Student (StudentID INT PRIMARY KEY, Name \cdot ARCHAR(50), Age INT);
ALTER TABLE	Modify an existing table (add, drop, or change columns).	ALTER TABLE Student ADD Email \cdot ARCHAR(100);
DROP TABLE	Delete a table and all its data.	DROP TABLE Student;

Typical DML Statements

Command	Purpose	Example
SELECT	Retrieve rows that satisfy a condition.	SELECT Name, Age FROM Student WHERE Age > 18;
INSERT	Add new rows to a table.	INSERT INTO Student (StudentID, Name, Age) VALUES (101, 'Alice', 19);
UPDATE	Modify existing rows.	UPDATE Student SET Age = Age + 1 WHERE StudentID = 101;
DELETE	Remove rows from a table.	DELETE FROM Student WHERE StudentID = 101;

Relational Model Reminder

In relational algebra, a query can be expressed as \$ \sigma{condition}(R) \$ (selection) or \$ \pi{attributes}(R) \$ (projection). SQL implements these operations with WHERE and SELECT clauses respectively.

Example Scenario – Two Tables

We will work with the following tables:

Student – stores basic student information.

Enrolment – records which courses each student is taking.

DDL – Creating the Tables


CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name \cdot ARCHAR(50) NOT NULL,
Age INT CHECK (Age >= 16)
);
CREATE TABLE Enrolment (
EnrolmentID INT PRIMARY KEY,
StudentID INT REFERENCES Student(StudentID),
CourseCode \cdot ARCHAR(10) NOT NULL,
Semester \cdot ARCHAR(6) NOT NULL
);

DML – Sample Script (≤ two tables)

The script below demonstrates inserting data, updating a record, deleting a row, and retrieving information using a join.


-- 1. Insert new students
INSERT INTO Student (StudentID, Name, Age) VALUES
(201, 'Bob', 20),
(202, 'Clara', 19);
-- 2. Enrol the students in courses
INSERT INTO Enrolment (EnrolmentID, StudentID, CourseCode, Semester) VALUES
(301, 201, 'CS101', 'Fall21'),
(302, 202, 'CS101', 'Fall21'),
(303, 202, 'MA102', 'Fall21');
-- 3. Update a student's age (Bob has a birthday)
UPDATE Student
SET Age = Age + 1
WHERE StudentID = 201;
-- 4. Delete an enrolment (Clara drops MA102)
DELETE FROM Enrolment
WHERE EnrolmentID = 303;
-- 5. Query: list all students and the courses they are currently enrolled in
SELECT s.Name, e.CourseCode, e.Semester
FROM Student s
JOIN Enrolment e ON s.StudentID = e.StudentID
ORDER BY s.Name;

Explanation of the Script

Steps 1‑2 use INSERT to add data to both tables.

Step 3 uses UPDATE to modify a single attribute.

Step 4 uses DELETE to remove a specific enrolment.

Step 5 demonstrates a SELECT with an INNER JOIN to combine data from the two tables.

Common Pitfalls

Forgetting to enforce referential integrity – the StudentID in Enrolment must exist in Student.

Using DELETE without a WHERE clause removes all rows.

When updating, ensure the WHERE clause uniquely identifies the target rows to avoid unintended changes.

Suggested diagram: ER diagram showing the one‑to‑many relationship between Student (1) and Enrolment (M).

Self‑Check Questions

What DDL command would you use to add a new column Phone to the Student table?

Write an SQL statement to list the names of students who are not enrolled in any course.

Explain why the REFERENCES clause in the Enrolment table definition is important.

Answers (for teacher reference)

ALTER TABLE Student ADD Phone \cdot ARCHAR(15);

SELECT Name FROM Student WHERE StudentID NOT IN (SELECT StudentID FROM Enrolment);

The REFERENCES clause enforces referential integrity, preventing enrolment records that refer to non‑existent students and ensuring that deletions/updates cascade appropriately.

Write an SQL script to query or modify data (DML) which are stored in (at most two) database tables