sql

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;