Unit 11.2A · Term 2

SQL: DDL & DML

SQL (Structured Query Language) is the standard language for managing relational databases. It has two main sub-languages: DDL (Data Definition Language) for structure, and DML (Data Manipulation Language) for data.

Learning Objectives

11.4.2.1 Explain the purpose of a data dictionary
11.4.2.2 Compare DDL and DML
11.4.2.3 Basic SQL: CREATE, ALTER, DROP
11.4.2.4 Basic SQL: SELECT, UPDATE, INSERT, DELETE
11.4.2.5 Use SELECT for multiple tables (JOIN)

Lesson Presentation

11.2A-sql.pdf · Slides for classroom use

View Download

Conceptual Anchor

The Warehouse Analogy

DDL is like building the warehouse — constructing shelves (CREATE), modifying layout (ALTER), and demolishing sections (DROP). DML is like working inside the warehouse — adding stock (INSERT), finding items (SELECT), updating labels (UPDATE), and removing products (DELETE).

Rules & Theory

DDL vs DML

Feature	DDL (Data Definition)	DML (Data Manipulation)
Purpose	Define/modify table structure	Work with the data inside tables
Commands	CREATE, ALTER, DROP	SELECT, INSERT, UPDATE, DELETE
Affects	Schema (structure)	Data (records)

DDL Commands

-- CREATE: Build a new table
CREATE TABLE Students (
    StudentID   VARCHAR(4) PRIMARY KEY,
    Name        VARCHAR(50) NOT NULL,
    Age         INTEGER,
    Email       VARCHAR(100),
    Class       VARCHAR(3)
);

-- ALTER: Modify an existing table
ALTER TABLE Students ADD Phone VARCHAR(15);
ALTER TABLE Students DROP COLUMN Phone;

-- DROP: Delete entire table (WARNING: permanent!)
DROP TABLE Students;

DML Commands

-- INSERT: Add a new record
INSERT INTO Students (StudentID, Name, Age, Email, Class)
VALUES ('S001', 'Ali', 16, 'ali@nis.edu.kz', '11A');

-- SELECT: Retrieve data
SELECT * FROM Students;                      -- all columns
SELECT Name, Age FROM Students;              -- specific columns
SELECT * FROM Students WHERE Class = '11A';  -- with filter
SELECT * FROM Students ORDER BY Name ASC;    -- sorted

-- UPDATE: Modify existing records
UPDATE Students SET Age = 17 WHERE StudentID = 'S001';

-- DELETE: Remove records
DELETE FROM Students WHERE StudentID = 'S003';

SELECT with Multiple Tables (JOIN)

-- Tables:
-- Students: StudentID, Name, Class
-- Grades:   GradeID, StudentID, Subject, Mark

-- INNER JOIN: Get student names with their grades
SELECT Students.Name, Grades.Subject, Grades.Mark
FROM Students
INNER JOIN Grades ON Students.StudentID = Grades.StudentID;

-- Result:
-- ┌────────┬─────────┬──────┐
-- │ Name   │ Subject │ Mark │
-- ├────────┼─────────┼──────┤
-- │ Ali    │ Maths   │ 85   │
-- │ Ali    │ CS      │ 92   │
-- │ Dana   │ CS      │ 88   │
-- └────────┴─────────┴──────┘

-- With WHERE filter:
SELECT Students.Name, Grades.Mark
FROM Students
INNER JOIN Grades ON Students.StudentID = Grades.StudentID
WHERE Grades.Mark > 90;

Useful SELECT Clauses

Clause	Purpose	Example
WHERE	Filter rows	WHERE Age > 16
ORDER BY	Sort results	ORDER BY Name ASC
AND / OR	Combine conditions	WHERE Age > 16 AND Class = '11A'
LIKE	Pattern matching	WHERE Name LIKE 'A%'
COUNT()	Count records	SELECT COUNT(*) FROM Students
SUM() / AVG()	Aggregate functions	SELECT AVG(Mark) FROM Grades
GROUP BY	Group results	GROUP BY Class

Data Dictionary

A data dictionary is metadata about the database: table names, field names, data types, constraints, and relationships. It's like a "table of contents" for the database — essential for developers to understand the structure.

Worked Examples

1 Create a School Database

-- Step 1: Create tables
CREATE TABLE Courses (
    CourseID   VARCHAR(5) PRIMARY KEY,
    Title      VARCHAR(50) NOT NULL,
    Credits    INTEGER
);

CREATE TABLE Enrolment (
    StudentID  VARCHAR(4),
    CourseID   VARCHAR(5),
    Grade      INTEGER,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

-- Step 2: Insert data
INSERT INTO Courses VALUES ('CS101', 'Computer Science', 3);
INSERT INTO Courses VALUES ('MA201', 'Mathematics', 4);

INSERT INTO Enrolment VALUES ('S001', 'CS101', 92);
INSERT INTO Enrolment VALUES ('S001', 'MA201', 85);

-- Step 3: Query across tables
SELECT s.Name, c.Title, e.Grade
FROM Students s
INNER JOIN Enrolment e ON s.StudentID = e.StudentID
INNER JOIN Courses c ON e.CourseID = c.CourseID
WHERE e.Grade > 80
ORDER BY e.Grade DESC;

Common Pitfalls

DELETE without WHERE

DELETE FROM Students; deletes ALL records in the table! Always include a WHERE clause unless you truly want to empty the entire table.

DROP vs DELETE

DROP removes the entire table (structure + data). DELETE removes only records (keeps the table structure). They are very different!

Tasks

Remember

List all DDL and DML commands and state what each does.

Apply

Write SQL to: (a) Create a Products table with ProductID, Name, Price, Stock. (b) Insert 3 records. (c) Select all products under $10.

Apply

Write a JOIN query that shows all students and their course names from the Enrolment table.

Analyze

Explain the difference between DROP TABLE and DELETE FROM. When would you use each?

Self-Check Quiz

Q1: What is the difference between DDL and DML?

DDL defines/modifies the structure (CREATE, ALTER, DROP). DML works with the data inside tables (SELECT, INSERT, UPDATE, DELETE).

Q2: Write SQL to get all students aged over 16, sorted by name.

SELECT * FROM Students WHERE Age > 16 ORDER BY Name ASC;

Q3: What does INNER JOIN do?

INNER JOIN returns only the rows where there is a match in BOTH tables being joined (based on the ON condition).

Q4: What is a data dictionary?

Metadata describing the database structure: table names, field names, data types, constraints, and relationships.