SQL (Cambridge (CIE) A Level Computer Science) : Revision Note

DDL & DML

What is DDL & DML?

• Data Definition Language (DDL) is used to build and manage the structure of a relational database

• It defines how data is stored by creating and modifying tables, fields, indexes, and relationships

• Data Manipulation Language (DML) is used to manage the data stored within the database structures created by DDL

• It deals with adding, updating, deleting, and retrieving data

• Both DDL and DML use SQL syntax to interact with the database

• DDL structures the database, while DML maintains the contents of the database

• Most modern DBMSs use SQL to support both DDL and DML operations

Basic DDL commands

Example: School database

• You are designing a database to store information about students and the courses they are enrolled in

• Each student can enrol in many courses, and each course can have many students (a many-to-many relationship)

• Three tables are needed:

  • Students – to store student details

  • Courses – to store course details

  • Enrolments – a link table to handle the many-to-many relationship

-- Create the Students table
CREATE TABLE Students (
    StudentID INTEGER,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DateOfBirth DATE,
    PRIMARY KEY (StudentID)
);

-- Create the Courses table
CREATE TABLE Courses (
    CourseID INTEGER,
    CourseName VARCHAR(100),
    Credits INTEGER,
    PRIMARY KEY (CourseID)
);

-- Create the Enrolments table to link Students and Courses
CREATE TABLE Enrolments (
    StudentID INTEGER,
    CourseID INTEGER,
    EnrolDate DATE,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Basic DML commands

Retrieving data

Data maintenance

Example: School database

• You're working with a school database containing three tables:

  • Students(StudentID, FirstName, LastName, Age)

  • Courses(CourseID, CourseName, Credits)

  • Enrolments(StudentID, CourseID, EnrolDate)

• You want to query the database and maintain the data as students enrol in courses, update their details, or leave the school

Querying

-- 1. Get the first and last names of all students
SELECT FirstName, LastName FROM Students;

-- 2. List students who are older than 16
SELECT * FROM Students WHERE Age > 16;

-- 3. List all students sorted alphabetically by surname
SELECT * FROM Students ORDER BY LastName ASC;

-- 4. Count how many students are enrolled on each course
SELECT CourseID, COUNT(*) FROM Enrolments GROUP BY CourseID;

-- 5. Show student names with the courses they are enrolled on
SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
INNER JOIN Enrolments ON Students.StudentID = Enrolments.StudentID
INNER JOIN Courses ON Enrolments.CourseID = Courses.CourseID;

-- 6. Get the total number of course credits available
SELECT SUM(Credits) FROM Courses;

-- 7. Get the average age of all students
SELECT AVG(Age) FROM Students;

Maintaining

-- 1. Add a new student to the Students table
INSERT INTO Students (StudentID, FirstName, LastName, Age)
VALUES (4, 'Amira', 'Patel', 17);

-- 2. Enrol that student on CourseID 2
INSERT INTO Enrolments (StudentID, CourseID, EnrolDate)
VALUES (4, 2, '01/09/2025'); -- UK date format (DD/MM/YYYY)

-- 3. Update a student's age
UPDATE Students SET Age = 18 WHERE StudentID = 4;

-- 4. Delete a student who has left the school
DELETE FROM Students WHERE StudentID = 4;