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;

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)

);

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

);

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)

);

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;

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

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;

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;

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;

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

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

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

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%';

'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

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;

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;

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!