Building a School Database from Scratch: A Practical Introduction to SQL

Building a school database from scratch

There’s a certain kind of satisfaction that comes from building something structured out of nothing. No shortcuts, no pre-built templates, just a blank database and a problem to solve.

That was the starting point for this project: designing and managing a database for Nairobi Academy, a secondary school. What began as a simple assignment quickly turned into a practical lesson in how data systems are built, maintained, and questioned.

The Two Sides of SQL: Structure vs Action

Every SQL workflow sits on two pillars: DDL (Data Definition Language) and DML (Data Manipulation Language).

DDL is about structure. It is where you define the blueprint by creating schemas, tables, and columns.

For example:

CREATE SCHEMA nairobi_academy;

USE nairobi_academy;

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

DML is about action. Once the structure is in place, DML handles everything that happens inside it.

INSERT INTO students (student_id, first_name, last_name, gender, class, city)
VALUES (1, 'Alice', 'Wanjiku', 'F', 'Form 4', 'Nairobi');

The distinction becomes clearer in practice:

• DDL defines the system
• DML works with the data inside it

Building the System and Adjusting It Along the Way

Creating the tables with CREATE felt straightforward at first. But once requirements started shifting, things became more realistic.

At one point, I had to modify the structure:

ALTER TABLE students
ADD phone_number VARCHAR(20);

Then later:

ALTER TABLE subjects
RENAME COLUMN credits TO credit_hours;

And eventually:

ALTER TABLE students
DROP COLUMN phone_number;

This back-and-forth made it clear that databases are not static. They evolve with changing needs.

Bringing Data to Life

With the structure in place, the next step was filling it.

INSERT INTO exam_results (result_id, student_id, subject_id, marks, exam_date)
VALUES (1, 1, 101, 78, '2024-03-15');

Once data was in place, I needed to maintain it.

Updating a student’s record:

UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

Fixing incorrect marks:

UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

Removing a cancelled record:

DELETE FROM exam_results
WHERE result_id = 9;

These operations made the database feel dynamic. Data wasn’t just stored, it was actively managed.

Filtering Data with WHERE

The WHERE clause is where SQL becomes more than storage. It becomes a tool for asking focused questions.

For example, finding all Form 4 students:

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

Finding high-performing students:

SELECT *
FROM exam_results
WHERE marks >= 70;

Using ranges:

SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

Using multiple values:

SELECT *
FROM students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

Using patterns:

SELECT *
FROM students
WHERE first_name LIKE 'A%' OR first_name LIKE 'E%';

At this stage, SQL starts to feel less like writing commands and more like working through logic.

Turning Data into Meaning with CASE WHEN

Raw data gives numbers, but interpretation gives insight.

Using CASE WHEN, I transformed exam results into performance categories:

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

And classified students:

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

This made the data easier to interpret and more useful for decision-making.

Reflection

The most challenging part of this assignment was not the syntax. It was the thinking behind it.

Combining conditions correctly took practice. Small mistakes could completely change the output. Remembering to always use WHERE with UPDATE and DELETE was especially important.

What stood out the most was how quickly SQL shifts from technical to analytical. Once the basics are in place, it becomes less about writing queries and more about asking better questions.

Final Thoughts

This project started as a database exercise but turned into something broader.

Building tables, inserting data, and running queries are all important steps. But the real value comes from understanding how everything connects. Structure supports data, and data supports decisions.

SQL, at its core, is not just about managing information. It is about making that information useful.