SQL Fundamentals: Building and Querying a School Database

Schools generate a surprising amount of structured data: student records, subject lists, exam scores, attendance — all of it begging to be organized, filtered, and analyzed. In this article, we'll build a small database for a fictional secondary school, Greenwood Academy, and use it to walk through six core SQL skills:

• A. Building the Database
• B. Filling the Database
• C. Querying the Data
• D. Range, Membership & Search Operators
• E. COUNT
• F. CASE WHEN

By the end, you'll be able to design a schema, populate it, and write queries that go well beyond SELECT *.

---

Section A: Building the Database

Before writing a single query, we need somewhere for the data to live. In SQL, a schema is a named container that groups related tables together — helpful once a database has dozens of tables across different departments (academics, finance, HR, etc.).

CREATE SCHEMA greenwood_academy;
USE greenwood_academy; -- MySQL syntax; in PostgreSQL: SET search_path TO greenwood_academy;

CREATE TABLE students (
    student_id      INT PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    gender          VARCHAR(1),
    date_of_birth   DATE,
    class           VARCHAR(10),
    city            VARCHAR(50)
);

CREATE TABLE subjects (
    subject_id      INT PRIMARY KEY,
    subject_name    VARCHAR(100) NOT NULL UNIQUE,
    department      VARCHAR(50),
    teacher_name    VARCHAR(100),
    credits         INT
);

CREATE TABLE exam_results (
    result_id       INT PRIMARY KEY,
    student_id      INT NOT NULL,
    subject_id      INT NOT NULL,
    marks           INT NOT NULL,
    exam_date       DATE,
    grade           VARCHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
);

Schemas evolve, and ALTER TABLE lets you change a table without rebuilding it:

-- Add a column
ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);

-- Rename a column
ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;

-- Remove a column that's no longer needed
ALTER TABLE students DROP COLUMN phone_number;

This is a common real-world pattern: add a column to test whether you need it, then drop it once you've decided it doesn't belong.

---

Section B: Filling the Database

With the structure ready, INSERT INTO adds rows:

INSERT INTO students (student_id, first_name, last_name, gender, date_of_birth, class, city) VALUES
(1, 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
(2, 'Brian', 'Ochieng', 'M', '2007-07-25', 'Form 4', 'Mombasa'),
(3, 'Cynthia', 'Mutua', 'F', '2008-11-05', 'Form 3', 'Kisumu'),
(4, 'David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi'),
(5, 'Esther', 'Akinyi', 'F', '2009-06-30', 'Form 2', 'Nakuru');

INSERT INTO subjects (subject_id, subject_name, department, teacher_name, credit_hours) VALUES
(1, 'Mathematics', 'Sciences', 'Mr. Njoroge', 4),
(2, 'English', 'Languages', 'Ms. Adhiambo', 3),
(3, 'Biology', 'Sciences', 'Ms. Otieno', 4),
(9, 'Computer Studies', 'Sciences', 'Mr. Oduya', 3);

INSERT INTO exam_results (result_id, student_id, subject_id, marks, exam_date, grade) VALUES
(1, 1, 1, 78, '2024-03-15', 'B'),
(2, 1, 2, 85, '2024-03-16', 'A'),
(3, 2, 1, 92, '2024-03-15', 'A'),
(5, 3, 2, 49, '2024-03-16', 'D');

Data isn't static — UPDATE and DELETE handle corrections:

-- Esther moved from Nakuru to Nairobi
UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

-- A mark was entered wrong: 49 should have been 59
UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

-- A result was cancelled and needs to be removed
DELETE FROM exam_results
WHERE result_id = 9;

Always confirm your inserts landed correctly:

SELECT COUNT(*) FROM students;
SELECT COUNT(*) FROM subjects;
SELECT COUNT(*) FROM exam_results;

---

Section C: Querying the Data

Filtering rows with WHERE is where SQL starts to feel powerful:

-- All Form 4 students
SELECT * FROM students WHERE class = 'Form 4';

-- All Sciences subjects
SELECT * FROM subjects WHERE department = 'Sciences';

-- All exam results with strong performance
SELECT * FROM exam_results WHERE marks >= 70;

-- Only female students
SELECT * FROM students WHERE gender = 'F';

Combine conditions with AND / OR:

-- Form 3 students from Nairobi
SELECT * FROM students
WHERE class = 'Form 3' AND city = 'Nairobi';

-- Form 2 or Form 4 students
SELECT * FROM students
WHERE class = 'Form 2' OR class = 'Form 4';

And joins pull related tables together — for example, showing each result with the student's name and subject:

SELECT s.first_name, s.last_name, sub.subject_name, e.marks
FROM exam_results e
JOIN students s ON e.student_id = s.student_id
JOIN subjects sub ON e.subject_id = sub.subject_id
ORDER BY e.marks DESC;

---

Section D: Range, Membership & Search Operators

Instead of chaining several comparisons, SQL offers compact operators for common patterns.

BETWEEN — range checks

-- Results in the "middle" band
SELECT * FROM exam_results WHERE marks BETWEEN 50 AND 80;

-- Exams within a date window
SELECT * FROM exam_results
WHERE exam_date BETWEEN '2024-03-15' AND '2024-03-18';

IN / NOT IN — membership checks

-- Students in specific cities
SELECT * FROM students WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

-- Students outside the lower forms
SELECT * FROM students WHERE class NOT IN ('Form 2', 'Form 3');

LIKE — pattern/text search

-- First names starting with 'A' or 'E'
SELECT * FROM students WHERE first_name LIKE 'A%' OR first_name LIKE 'E%';

-- Subjects with "Studies" in the name
SELECT * FROM subjects WHERE subject_name LIKE '%Studies%';

---

Section E: COUNT

COUNT collapses many rows into a single summary number — the basis of almost every dashboard metric.

-- How many students are in Form 3?
SELECT COUNT(*) AS form3_count
FROM students
WHERE class = 'Form 3';

-- How many results are 70 marks or above?
SELECT COUNT(*) AS strong_results
FROM exam_results
WHERE marks >= 70;

Pairing COUNT with GROUP BY gives per-category breakdowns:

SELECT class, COUNT(*) AS student_count
FROM students
GROUP BY class
ORDER BY student_count DESC;

---

Section F: CASE WHEN

CASE WHEN brings if/else logic directly into a query — perfect for turning raw numbers into human-readable labels.

Grading exam performance:

SELECT
    student_id,
    subject_id,
    marks,
    CASE
        WHEN marks >= 80 THEN 'Distinction'
        WHEN marks >= 60 THEN 'Merit'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS performance
FROM exam_results;

Classifying students by seniority:

SELECT
    first_name,
    last_name,
    class,
    CASE
        WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
        WHEN class IN ('Form 1', 'Form 2') THEN 'Junior'
    END AS student_level
FROM students;

You can even combine CASE WHEN with COUNT to build an instant summary report:

SELECT
    COUNT(CASE WHEN marks >= 80 THEN 1 END) AS distinctions,
    COUNT(CASE WHEN marks >= 60 AND marks < 80 THEN 1 END) AS merits,
    COUNT(CASE WHEN marks >= 40 AND marks < 60 THEN 1 END) AS passes,
    COUNT(CASE WHEN marks < 40 THEN 1 END) AS fails
FROM exam_results;

One query, an entire school's performance snapshot.

---

Wrapping Up

Starting from nothing, we:

1. Designed a schema and three related tables (Section A)
2. Populated them and corrected mistakes with UPDATE/DELETE (Section B)
3. Filtered and joined data with WHERE (Section C)
4. Used range, membership, and search operators for precise filtering (Section D)
5. Aggregated results with COUNT (Section E)
6. Added conditional logic with CASE WHEN (Section F)

These six building blocks - schema design, data manipulation, filtering, operators, aggregation, and conditional logic, cover most of what you'll reach for in day-to-day SQL work, whether you're managing a school's records or analyzing a company's sales.

Comments

[leslie angu]

The article was quite insightful. The only thing missing was the outputs from each operation that you performed. The output guides the user that is learning on whether they are wrong or correct.