SQL Made Simple: Greenwood Academy Database Project with PostgreSQL

Opening a blank database console for the first time can feel intimidating. You're staring at an empty space and somehow expected to turn it into a clean, organized structure. Over the past two weeks I went from not knowing basic syntax to building, populating and running real queries in PostgreSQL.

If you're just starting out, it helps a lot to break things down by what each piece actually does. Here's how I made sense of it, using the exact queries I wrote while building a small management system for a school called Greenwood Academy.

1. First you build the house (DDL)

Before you can put any data anywhere, you have to decide what the data even looks like. That's DDL - Data Definition Language. It's just the "building the shelves before you put stuff on them" part.

One trick that saved me a lot of typing: instead of writing greenwood_academy.students every single time, you can just tell Postgres which schema to default to.

-- Create a schema called greenwood_academy and set search path
CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;

-- Create the students table
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)
);

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

-- Create the exam_results table
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)
);

And of course, that is not the end. You can do various alterations on your table without having to start over. The below are for adding, renaming and removing columns:

-- adding a phone number column to students table
ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);

-- Rename credits to credit_hours in subjects table
ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;

-- Drop phone_number column from students table
ALTER TABLE students DROP COLUMN phone_number;

2. Then you actually put data in it (DML)

Once the table exists, it's empty. This is where DML comes in - Data Manipulation Language; adding, changing or removing rows.

Inserting data into students table

-- Insert all 10 students into the students table
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'),
(4, 'David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi'),
(5, 'Esther', 'Akinyi', 'F', '2009-06-30', 'Form 2', 'Nakuru'),
(6, 'Felix', 'Otieno', 'M', '2009-09-14', 'Form 2', 'Eldoret'),
(7, 'Grace', 'Mwangi', 'F', '2008-01-22', 'Form 3', 'Nairobi'),
(8, 'Hassan', 'Abdi', 'M', '2007-04-09', 'Form 4', 'Mombasa'),
(9, 'Ivy', 'Chebet', 'F', '2009-12-01', 'Form 2', 'Nakuru'),
(10, 'James', 'Kariuki', 'M', '2008-08-17', 'Form 3', 'Nairobi');

Inserting data into subjects table

-- Insert all 10 subjects into the subjects table
INSERT INTO subjects (subject_id, subject_name, department, teacher_name, credit_hours) VALUES
(1, 'Mathematics', 'Sciences', 'Mr. Njoroge', 4),
(2, 'English', 'Languages', 'Ms. Adhiambo', 3),
(3, 'Biology', 'Sciences', 'Ms. Otieno', 4),
(4, 'History', 'Humanities', 'Mr. Waweru', 3),
(5, 'Kiswahili', 'Languages', 'Ms. Nduta', 3),
(6, 'Physics', 'Sciences', 'Mr. Kamande', 4),
(7, 'Geography', 'Humanities', 'Ms. Chebet', 3),
(8, 'Chemistry', 'Sciences', 'Ms. Muthoni', 4),
(9, 'Computer Studies', 'Sciences', 'Mr. Oduya', 3),
(10, 'Business Studies', 'Humanities', 'Ms. Wangari', 3);

Inserting data into exams_results table

-- Insert all 10 exam results into the exam_results table
INSERT INTO exam_results (result_id, student_id, subject_id, marks, exam_date, grade) VALUES
(1, 1, 1, 78, '2024-03-15', 'B'),
(2, 1, 2, 85, '2024-03-16', 'A'),
(3, 2, 1, 92, '2024-03-15', 'A'),
(4, 2, 3, 55, '2024-03-17', 'C'),
(5, 3, 2, 49, '2024-03-16', 'D'),
(6, 3, 4, 71, '2024-03-18', 'B'),
(7, 4, 1, 88, '2024-03-15', 'A'),
(8, 4, 6, 63, '2024-03-19', 'C'),
(9, 5, 5, 39, '2024-03-20', 'F'),
(10, 6, 9, 95, '2024-03-21', 'A');

Confirm all 10 rows exist

-- Confirm all 10 rows exist in each of the three tables
SELECT * FROM students;
SELECT * FROM subjects;
SELECT * FROM exam_results;

UPDATE - Changes existing records

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

DELETE - removes rows from a table, but keeps the structure of the table

--- Delete exam result with result_id 9
DELETE FROM exam_results 
WHERE result_id = 9;

3. WHERE now comes in

Honestly this is where I started seeing the power of SQL. WHERE is how you check through rows and pull out exactly what you need.

Here are the operators I used:

-- students who scored between 50 and 80
SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

-- students from a specific set of cities
SELECT *
FROM students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

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

-- any subject with "Studies" somewhere in the name
SELECT *
FROM subjects
WHERE subject_name LIKE '%Studies%';

That % wildcard in LIKE became interesting when I finally figured it out. %Studies% just means "What's before or after it is not of concern, just find 'Studies' somewhere in there." So much cleaner than stacking a bunch of OR conditions.

4. Aggregate functions

• This is basically just the summary.

-- how many Form 3 students do we have?
SELECT COUNT(*) AS total_form3_students
FROM students
WHERE class = 'Form 3';

-- how many students scored 70 or above?
SELECT COUNT(*) AS total_above_70
FROM exam_results
WHERE marks >= 70;

5. CASE WHEN

• Checks each rule one by one down the list, returning the matching label the second a condition is met.

--- Label each exam result with a grade description
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;

My takeaway from this

• SET search_path is a small habit that saves a lot of repetitive typing.
• Always, check your WHERE clause before running an update or delete.
• Name your aggregate results something readable.

Conclusion

Building the Greenwood Academy database was a genuinely useful beginner project because it touched both database design and querying. By the end I'd practiced creating tables, inserting data, updating records, filtering results, summarizing data and writing conditional logic.
If you're learning SQL, I'd recommend building a small project like this yourself. It's a much better way to understand how these pieces actually get used than just reading through syntax on its own.