From Zero to Querying Data: Building My First PostgreSQL Database

If you're just getting started with SQL, this project is a great way to learn the fundamentals. In this article, I'll walk you through how I created a simple school database using PostgreSQL, inserted data, and wrote queries to retrieve meaningful information.

Introduction

When learning SQL, one of the best ways to improve is by building a real database instead of just reading about commands. Recently, I completed an assignment where I had to create a database for a fictional secondary school called Greenwood Academy.

The goal wasn't just to write SQL queries; it was to understand how databases are created, how data is stored, and how SQL is used to retrieve and manipulate that data.

By the end of the project, I had learned how to:

• Create databases and tables
• Insert, update and delete records
• Filter data using different SQL operators
• Count records using aggregate functions
• Use CASE WHEN to categorize data

Step 1: Creating the Database

The first thing I did was create a schema called greenwood_academy.

Schemas help organize database objects such as tables. Then I switched into the schema before creating my tables.

Step 2: Creating the Tables

Next, I created three tables.

Students Table

This table stores information about students.

Some of the columns include:

• Student ID
• First name
• Last name
• Gender
• Date of Birth
• Class
• City

Subjects Table

This table stores the subjects taught in the school together with:

• Subject name
• Department
• Teacher
• Credit hours

Exam Results Table

Finally, I created a table that stores each student's exam performance.

It contains:

• Student ID
• Subject ID
• Marks
• Grade
• Exam date

This table connects students with the subjects they took.

Step 3: Modifying the Database

Databases often change as requirements change.

In this assignment, I practiced modifying tables using ALTER TABLE.

First, I added a phone number column, then later, I removed the same column because it was no longer needed.
I also renamed the credits column to credit_hours.

These exercises helped me understand that databases can evolve over time without starting from scratch.

Step 4: Inserting Data

Once the tables were ready, I populated them with data.

The assignment provided:

• 10 students
• 10 subjects
• 10 exam results

Using INSERT INTO, I added each record into its respective table.

After inserting the data, I confirmed everything had been inserted correctly using:

SELECT * FROM students;
I repeated this for the other two tables.

Step 5: Updating and Deleting Data

One thing I learned is that data isn't always permanent.

Sometimes information changes.

For example:

• A student moved from Nakuru to Nairobi.
• An exam mark was entered incorrectly.
• One exam record had to be deleted.

Commands such as delete and update allow us to maintain accurate data without recreating the database.

Step 6: Querying Data

This was probably the most interesting part of the assignment. Using SELECT and WHERE, I could answer questions about the stored data.Simple queries like these are the foundation of SQL.

Step 7: Using SQL Operators

After learning basic filtering, I explored more powerful operators.

BETWEEN

Used to find values within a range.

Example:

Find students who scored between 50 and 80.

IN

Instead of writing multiple OR conditions, IN makes queries cleaner.

NOT IN

Returns values that don't belong to a specified list.

LIKE

Used for pattern matching.

For example:

sql
WHERE first_name LIKE 'A%'

This returns students whose names begin with the letter A.

Learning these operators made my queries much shorter and easier to read.

Step 8: Counting Records

Sometimes you don't need the actual records—you only need the total number.

SQL provides aggregate functions for this.

For example:

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

This tells us how many students are currently in Form 3.

Aggregate functions become very useful when working with larger datasets.

Step 9: Categorizing Data with CASE WHEN

One of my favorite parts of the assignment was learning about CASE WHEN. It works similarly to an if-else statement in programming. Instead of just displaying marks, I could classify students into different performance levels.

For example:

• Distinction
• Merit
• Pass
• Fail

This makes query results much easier to understand.

I also used CASE WHEN to classify students as either:

• Junior
• Senior

based on their class.

What I Learned

This assignment helped me understand the basic workflow of working with relational databases.

I learned how to:

• Design tables
• Store data efficiently
• Modify existing tables
• Retrieve information using SQL queries
• Filter records using different operators
• Count records using aggregate functions
• Use conditional logic with CASE WHEN

Although the database was small, the concepts are the same ones used in much larger real-world systems such as banking applications, hospital management systems, school management systems, and e-commerce platforms.

If you're new to SQL, I highly recommend building small projects like this instead of only watching tutorials. Writing queries yourself helps reinforce the concepts much faster.

This Greenwood Academy project gave me hands-on experience with the core SQL skills every beginner should know. I'm looking forward to building more complex databases involving joins, grouping, subqueries, and database normalization as I continue learning.

If you've just started learning SQL, keep practicing. Every query you write brings you one step closer to becoming confident with databases.