Building a School Database with PostgreSQL: My SQL Week 2 Journey

Databases are everywhere. Every company and institution, and even your favourite social media app relies on them to organise and retrieve information efficiently. This week, I stepped into the role of a database administrator and built a complete school database from scratch using PostgreSQL.

The project was based on a fictional secondary school called Greenlake Academy. Although it sounds simple at first, the assignment covered many of the fundamental SQL skills that every aspiring data engineer or database administrator needs to master.

Creating the Foundation

The first step was creating a dedicated schema called greenlake_academy. A schema acts like a folder inside a database, keeping related tables organised.

After that, I created three tables:

• students – stores student information.

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 – stores the subjects offered by the school.

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 – stores each student's exam performance.

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

Each table required appropriate data types and constraints such as:

• Primary Keys
• NOT NULL constraints
• UNIQUE constraints

These constraints_ help maintain data accuracy_ and prevent duplicate or incomplete records.

Making Changes with ALTER TABLE

One lesson I enjoyed was seeing how databases can evolve over time.

Initially, the school wanted a phone_number column added to the students table. Later, they decided it was no longer necessary. Using ALTER TABLE, I was able to add the column and later remove it without rebuilding the entire table.

alter table students
add column phone_number VARCHAR(20);

I also renamed the credits column to credit_hours, demonstrating how database structures can adapt as requirements change.

alter table subjects
rename column credits to credit_hours;

Populating the Database

Once the tables were ready, I inserted data for:

• 10 students

insert into students values...

• 10 subjects

insert into subjects values...

• 10 exam results

INSERT INTO exam_results VALUES...

Seeing the tables fill with realistic information made the database feel much more alive. Instead of empty structures, I now had meaningful records that could be queried and analysed.

Updating and Maintaining Data

Real-world data changes constantly.

To simulate this, I performed several maintenance tasks:

• Updated Esther Akinyi's city after she moved to Nairobi.

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

• Corrected an exam mark that had been entered incorrectly.

UPDATE exam_results
SET marks=59
WHERE result_id=5;

• Deleted an exam result that had been cancelled.

DELETE FROM exam_results
WHERE result_id=9;

These exercises highlighted the importance of maintaining clean and accurate data.

Retrieving Useful Information

This was my favourite part.

Using SQL queries, I was able to answer questions such as:

• Which students are in Form 4?
• Which subjects belong to the Sciences department?
• Which students are female?
• Which students come from Nairobi?
• Which students scored above 70 marks?

It was exciting to see how a few lines of SQL could instantly retrieve exactly the information I needed.

Working with Operators

I also explored SQL operators including:

• BETWEEN eg

SELECT * FROM exam_results
WHERE marks BETWEEN 50 AND 80;

• IN eg

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

• NOT IN eg

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

• LIKE eg

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

These made searching much more flexible.

For example, instead of writing multiple conditions, I could retrieve all students from Nairobi, Mombasa, and Kisumu using a single IN statement. I also searched for names beginning with specific letters and subjects containing the word Studies.

Counting Records

Another useful skill was using the COUNT() function. For example

SELECT COUNT(*) AS form3_students
FROM students
WHERE class='Form 3';

With one simple query, I could determine:

• How many students were currently in Form 3.
• How many exam results had marks of 70 or higher.

Aggregate functions like COUNT() are incredibly useful when generating reports and dashboards.

Making Data Easier to Understand with CASE WHEN.

The final section introduced the CASE WHEN statement. For example

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

Instead of displaying only raw marks, I categorised students' performance into:

• Distinction
• Merit
• Pass
• Fail

I also classified students as either:

• Senior
• Junior

This demonstrated how SQL can transform raw data into meaningful insights without modifying the original records.

Lessons Learned

This assignment reinforced several important database concepts:

• Designing well-structured tables.
• Applying constraints to maintain data quality.
• Modifying database structures safely.
• Inserting, updating, and deleting records.
• Writing efficient queries to retrieve information.
• Using SQL logic to generate meaningful reports.

One challenge I encountered was a syntax error while writing a CASE WHEN statement. It reminded me that SQL is very particular about commas, keywords, and the END statement. Debugging the error helped me understand the language much better.

Final Thoughts

Building the Greenlake Academy database was more than just completing an assignment—it was practical experience in managing data from creation to analysis.

Every query I wrote strengthened my understanding of SQL and showed me how databases power everyday systems. As I continue learning data engineering, I know these skills will become the foundation for working with larger datasets, building data pipelines, and designing scalable database solutions.

This project reminded me that SQL isn't just about writing commands,...it's about turning data into organised, accurate, and meaningful information.