Run the script below to create the school database used throughout the Intermediate and Advanced tracks (it drops and recreates the tables, so it is safe to re-run). Save it as school.sql and load it with psql -d school -f school.sql.
A JOIN combines rows from two tables where a condition matches. INNER JOIN (or just JOIN) keeps only rows that match on both sides. LEFT JOIN keeps every row from the left table and fills the right side with NULL when there is no match.
An anti-join finds rows with no match: LEFT JOIN ... WHERE right.id IS NULL (or NOT EXISTS). Give tables short aliases (s, r, sub) to keep queries readable.
DROP TABLE IF EXISTS results, payments, students, subjects, teachers CASCADE;
CREATE TABLE teachers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name text NOT NULL
);
CREATE TABLE subjects (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code text NOT NULL UNIQUE,
name text NOT NULL,
teacher_id bigint REFERENCES teachers (id)
);
CREATE TABLE students (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name text NOT NULL,
form smallint NOT NULL CHECK (form BETWEEN 1 AND 6),
stream char(1) NOT NULL,
gender char(1) NOT NULL CHECK (gender IN ('F', 'M')),
email text UNIQUE,
fee_balance numeric(12,2) NOT NULL DEFAULT 0 CHECK (fee_balance >= 0)
);
CREATE TABLE results (
student_id bigint NOT NULL REFERENCES students (id) ON DELETE CASCADE,
subject_id bigint NOT NULL REFERENCES subjects (id),
term smallint NOT NULL CHECK (term IN (1, 2)),
score smallint NOT NULL CHECK (score BETWEEN 0 AND 100),
PRIMARY KEY (student_id, subject_id, term)
);
CREATE TABLE payments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
student_id bigint NOT NULL REFERENCES students (id),
amount numeric(12,2) NOT NULL CHECK (amount > 0),
method text NOT NULL CHECK (method IN ('mpesa', 'bank', 'cash')),
paid_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO teachers (full_name) VALUES ('Mr. Mwakyusa'), ('Ms. Lyimo'), ('Mrs. Mrema');
INSERT INTO subjects (code, name, teacher_id) VALUES
('MATH', 'Basic Mathematics', 1), ('BIO', 'Biology', 2),
('ENG', 'English Language', 3), ('CHEM', 'Chemistry', NULL);
INSERT INTO students (full_name, form, stream, gender, email, fee_balance) VALUES
('Amina Hassan', 4, 'A', 'F', 'amina@example.com', 0),
('Baraka Mushi', 4, 'B', 'M', NULL, 150000),
('Neema Kimaro', 4, 'A', 'F', 'neema@example.com', 50000),
('Juma Said', 4, 'B', 'M', 'juma@example.com', 200000),
('Rehema Mollel', 3, 'A', 'F', NULL, 300000),
('Ali Mohamed', 3, 'A', 'M', 'ali@example.com', 0),
('Zawadi Njau', 3, 'B', 'F', 'zawadi@example.com', 75000),
('Frank Temba', 2, 'A', 'M', NULL, 120000);
INSERT INTO results (student_id, subject_id, term, score) VALUES
(1,1,1,88),(1,2,1,79),(1,3,1,91),(1,1,2,92),(1,2,2,81),(1,3,2,89),
(2,1,1,42),(2,2,1,55),(2,3,1,61),(2,1,2,48),(2,2,2,52),(2,3,2,66),
(3,1,1,71),(3,2,1,84),(3,3,1,66),(3,1,2,69),(3,2,2,88),(3,3,2,72),
(4,1,1,29),(4,3,1,48),(4,1,2,35),(4,3,2,51),
(5,1,1,64),(5,2,1,70),(5,1,2,74),(5,2,2,77),
(6,1,1,95),(6,2,1,62),(6,1,2,97),(6,2,2,58),
(7,2,1,81),(7,3,1,77),(7,2,2,85),(7,3,2,80);
INSERT INTO payments (student_id, amount, method, paid_at) VALUES
(1, 300000, 'mpesa', '2026-01-10'), (2, 150000, 'bank', '2026-01-15'),
(3, 250000, 'mpesa', '2026-01-12'), (3, 50000, 'cash', '2026-03-02'),
(6, 300000, 'bank', '2026-01-20'), (7, 225000, 'mpesa', '2026-02-05');-- INNER JOIN: each result with student and subject names
SELECT s.full_name, sub.name AS subject, r.term, r.score
FROM results r
JOIN students s ON s.id = r.student_id
JOIN subjects sub ON sub.id = r.subject_id
WHERE r.term = 2 AND sub.code = 'MATH'
ORDER BY r.score DESC;
-- LEFT JOIN: every subject, with its teacher if it has one
SELECT sub.name, coalesce(t.full_name, '(no teacher yet)') AS teacher
FROM subjects sub
LEFT JOIN teachers t ON t.id = sub.teacher_id
ORDER BY sub.name;
-- Anti-join: students who have never made a payment
SELECT s.full_name, s.fee_balance
FROM students s
LEFT JOIN payments p ON p.student_id = s.id
WHERE p.id IS NULL
ORDER BY s.full_name;
-- Total paid per student, including students who paid nothing
SELECT s.full_name, coalesce(sum(p.amount), 0) AS total_paid
FROM students s
LEFT JOIN payments p ON p.student_id = s.id
GROUP BY s.id, s.full_name
ORDER BY total_paid DESC, s.full_name;Key points
- INNER JOIN keeps matches only; LEFT JOIN keeps every left-side row.
- Anti-join (
LEFT JOIN ... IS NULLorNOT EXISTS) finds missing relationships. - With LEFT JOIN + aggregates, wrap sums in
coalesce(..., 0).
Exercise
List every subject with the number of students who sat it in term 1 (including subjects nobody sat). Then find students who have results in Maths but not in Biology.
Show solution
Try the exercise yourself first — then compare your approach with this one.
Start from subjects and LEFT JOIN the term-1 results, putting the term filter in the ON clause — in WHERE it would remove subjects with no results. count(r.student_id) counts only matched rows, so Chemistry shows 0. The second query is an anti-join with NOT EXISTS.
-- Students who sat each subject in term 1, including subjects nobody sat
SELECT sub.name, count(r.student_id) AS students
FROM subjects sub
LEFT JOIN results r ON r.subject_id = sub.id AND r.term = 1
GROUP BY sub.name
ORDER BY students DESC, sub.name;
-- Students with Maths results but no Biology results
SELECT DISTINCT s.full_name
FROM students s
JOIN results r ON r.student_id = s.id
JOIN subjects sub ON sub.id = r.subject_id AND sub.code = 'MATH'
WHERE NOT EXISTS (
SELECT 1
FROM results r2
JOIN subjects b ON b.id = r2.subject_id AND b.code = 'BIO'
WHERE r2.student_id = s.id
);