SQL for Beginners: Building a Mini School Database from Scratch!

SQL for Beginners: Building a Mini School Database from Scratch

If you’re learning SQL, the fastest way to make it stick is to build something real not just read boring theory. In this post, I’ll walk through a small project assignment I built while learning PostgreSQL over the weekend: a mini database for a fictional school called Greenwood Academy.

We’ll go from an empty schema to a fully populated database with students, subjects, and exam results then run queries to answer real questions about the data. Along the way, we’ll cover:

• Creating tables (DDL)
• Inserting, updating, and deleting data (DML)
• Filtering with WHERE
• Range, membership, and pattern-matching operators
• Counting rows with COUNT
• Categorizing data with CASE WHEN

Let’s dive in.

Setting the Scene

Greenwood Academy needs three things tracked: students, the subjects they take, and their exam results. That’s a clean, realistic setup for practicing relational database basics one table naturally connects to the others through IDs.

1. Building the Database (DDL)

First, we create a dedicated schema so everything for this project stays organized and separate from other databases:

CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;

Enter fullscreen mode Exit fullscreen mode

Then we define our three core tables.

Students the people at the center of everything:

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)
);

Enter fullscreen mode Exit fullscreen mode

Subjects what’s being taught:

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

Enter fullscreen mode Exit fullscreen mode

Exam results the link between students and subjects:

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)
);

Enter fullscreen mode Exit fullscreen mode

Schemas aren’t set in stone once created. A great early lesson is learning to evolve a table with ALTER TABLE adding a column you realize you need, renaming one that’s poorly named, and dropping one you don’t:

-- 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
ALTER TABLE students DROP COLUMN phone_number;

Enter fullscreen mode Exit fullscreen mode

This mirrors real life requirements change, and your schema needs to keep up without you having to drop and rebuild the whole table.

2. Filling the Database (DML)

With the structure in place, it’s time to add data. INSERT statements can take multiple rows at once, which keeps things concise:

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');
-- ...and so on

Enter fullscreen mode Exit fullscreen mode

The same pattern applies to subjects and exam_results. Once the tables are populated, a quick sanity check confirms everything landed correctly:

SELECT * FROM students;
SELECT * FROM subjects;
SELECT * FROM exam_results;

Enter fullscreen mode Exit fullscreen mode

Data isn’t static, though. People move, mistakes happen, and records get cancelled. That’s where UPDATE and DELETE come in:

-- Correct a student's recorded city
UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

-- Fix a marking error
UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

-- Remove a cancelled exam result
DELETE FROM exam_results
WHERE result_id = 9;

Enter fullscreen mode Exit fullscreen mode

Pro tip: Always pair UPDATE and DELETE with a WHERE clause. Forgetting it means updating or deleting every row in the table, a classic (and painful) beginner mistake.

Respect the WHERE clause. Forget it on an UPDATE or DELETE and you’re not fixing one row you’re rewriting the whole table.

3. Querying the Data with WHERE

Now for the fun part, asking questions. The WHERE clause is how you filter rows to get exactly what you need.

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

-- Subjects in the Sciences department
SELECT * FROM subjects WHERE department = 'Sciences';

-- Exam results with marks of 70 or higher
SELECT * FROM exam_results WHERE marks >= 70;

Enter fullscreen mode Exit fullscreen mode

You can combine conditions with AND and OR to ask more specific questions:

-- Form 3 students who live in Nairobi
SELECT * FROM students
WHERE class = 'Form 3' AND city = 'Nairobi';

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

Enter fullscreen mode Exit fullscreen mode

AND narrows the results (both conditions must be true), while OR widens them (either condition can be true) a distinction that trips up a lot of beginners at first.

4. Range, Membership & Search Operators

Plain equality checks only get you so far. SQL gives you more expressive tools for common patterns.

BETWEEN for ranges (inclusive on both ends):

-- Marks between 50 and 80
SELECT * FROM exam_results
WHERE marks BETWEEN 50 AND 80;

-- Exams that happened in a date range
SELECT * FROM exam_results
WHERE exam_date BETWEEN '2024-03-15' AND '2024-03-18';

Enter fullscreen mode Exit fullscreen mode

IN and NOT IN for membership checks much cleaner than a chain of ORs:

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

-- Students NOT in Form 2 or Form 3
SELECT * FROM students
WHERE class NOT IN ('Form 2', 'Form 3');

Enter fullscreen mode Exit fullscreen mode

LIKE for pattern matching % is a wildcard for “any characters”:

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

-- Subjects containing the word "Studies"
SELECT * FROM subjects
WHERE subject_name LIKE '%Studies%';

Enter fullscreen mode Exit fullscreen mode

'A%' means “starts with A,” while '%Studies%' means “contains Studies anywhere in the string.” Small syntax, huge flexibility.

5. Counting with COUNT

Sometimes you don’t need the rows themselves, just how many there are. That’s what COUNT(*) is for:

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

-- How many exam results have marks of 70 or above?
SELECT COUNT(*) AS total_marks_70_or_above
FROM exam_results
WHERE marks >= 70;
-- Result: 6

Enter fullscreen mode Exit fullscreen mode

COUNT combined with WHERE is one of the most common patterns you’ll write in real reporting queries “how many of X meet condition Y” comes up constantly.

6. Categorizing Data with CASE WHEN

This is where SQL starts to feel genuinely powerful. CASE WHEN lets you create new, human-readable categories right inside a query, there is no need to pull data into another language just to label it.

Grading exam performance:

SELECT
    result_id,
    student_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;

Enter fullscreen mode Exit fullscreen mode

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'
        ELSE 'Unknown'
    END AS student_level
FROM students;

Enter fullscreen mode Exit fullscreen mode

CASE WHEN evaluates top to bottom and stops at the first matching condition so order your conditions from most specific to least specific (or in this case, from highest marks to lowest).

Wrapping Up

In just six sections, we went from an empty schema to a working mini-database that can answer real questions: Who’s in Form 4? Which exams need attention? Who’s a top performer?

The core lesson here isn’t really about school records, it’s that a small set of SQL fundamentals (CREATE, INSERT, WHERE, BETWEEN, IN, LIKE, COUNT, CASE WHEN) can already answer a surprising number of real-world questions. Everything more advanced joins, subqueries, window functions builds directly on top of this foundation.

A Few Things I’d Tell Someone Starting Out

Run your INSERTs, then immediately double-check with SELECT COUNT(*).
Read every WHERE clause twice before hitting run on an UPDATE or DELETE.
IN and BETWEEN will save you from writing long, ugly chains of OR.
Order your CASE WHEN conditions carefully, it stops at the first match.

If you’re learning SQL, I’d genuinely recommend building something similar: pick a small, relatable domain (a school, a shop, a gym), design two or three connected tables, and just start asking it questions.

Here is a github link to the assignment if you wanna check it out : (https://github.com/Neema-Kirui/sql-week2-assignment-neema/tree/main)

Happy querying!