Intermediate SQL Portfolio Project: Master Key Skills
If you already know basic SQL and want a project that feels more practical, this Greenwood Academy database is a good next step. It starts with simple tables, then moves into real database work: inserting data, updating records, filtering results, using operators, counting records, formatting dates, writing CASE WHEN logic, and using a subquery for a more advanced question.
This project is labelled as intermediate SQL because it goes beyond only creating tables and selecting rows. It shows how SQL can help organize school data and turn it into information that teachers, administrators, students, and technical teams can understand.
What this project is about
Greenwood Academy is a fictional school database. The database stores students, subjects, and exam results.
Instead of working with random examples, the project follows a story. A school needs to keep student details, know which subjects are taught, record exam marks, correct mistakes, remove cancelled results, and create simple reports.
The database has three main tables:
| Table | What it stores | Example use |
|---|---|---|
students |
Student names, gender, date of birth, class, and city | Find Form 3 students from Nairobi |
subjects |
Subject names, departments, teachers, and credit hours | Find all Science subjects |
exam_results |
Student results, marks, grades, and exam dates | Find results above 70 marks |
The main idea is simple: first create a clean structure, then add data, then ask useful questions.
1. Creating the schema
The project starts by creating a schema called greenwood_academy. A schema helps group related database objects together.
CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;
This keeps the school database objects organized in one place.
2. Creating the students table
The first table stores student information.
CREATE TABLE students (
student_id SERIAL 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)
);
The most important column here is student_id. It is the primary key, which means every student gets a unique identifier.
For a non-technical reader, this is like giving every learner a school admission number. Even if two students have the same name, the database can still tell them apart.
3. Creating the subjects table
The second table stores the subjects offered by Greenwood Academy.
CREATE TABLE subjects (
subject_id SERIAL PRIMARY KEY,
subject_name VARCHAR(100) NOT NULL UNIQUE,
department VARCHAR(50),
teacher_name VARCHAR(100),
credits INT
);
The UNIQUE rule on subject_name prevents the same subject from being entered more than once with the exact same name.
4. Creating the exam results table
The third table connects students to subjects through exam performance.
CREATE TABLE exam_results (
result_id SERIAL PRIMARY KEY,
student_id INT NOT NULL,
subject_id INT NOT NULL,
marks INT NOT NULL,
exam_date DATE,
FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE CASCADE
);
This is where the project becomes relational. The exam_results table does not store the full student name or full subject details. It stores student_id and subject_id, then uses foreign keys to connect back to the correct records.
That means one student can have many exam results, and one subject can appear in many exam results.
5. Altering tables after creation
A database design often changes as the project grows. In this project, I added a phone number column, renamed a subject column, removed the phone number column, and added a grade column.
ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);
ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;
ALTER TABLE students DROP COLUMN phone_number;
ALTER TABLE exam_results ADD COLUMN grade VARCHAR(1);
This part is important because real databases are not always designed perfectly on the first attempt. ALTER TABLE lets you adjust the structure while continuing to build.
6. Inserting student data
After creating the tables, I inserted sample students into the database.
INSERT INTO students (
first_name,
last_name,
gender,
date_of_birth,
class,
city
)
VALUES
('Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
('Brian', 'Ochieng', 'M', '2007-07-25', 'Form 4', 'Mombasa'),
('Cynthia', 'Mutua', 'F', '2008-11-05', 'Form 3', 'Kisumu'),
('David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi');
Sample output from the students table:
| student_id | first_name | last_name | class | city |
|---|---|---|---|---|
| 1 | Amina | Wanjiku | Form 3 | Nairobi |
| 2 | Brian | Ochieng | Form 4 | Mombasa |
| 3 | Cynthia | Mutua | Form 3 | Kisumu |
| 4 | David | Kamau | Form 4 | Nairobi |
7. Inserting subjects and exam results
The subjects table stores what the school teaches.
INSERT INTO subjects (
subject_name,
department,
teacher_name,
credit_hours
)
VALUES
('Mathematics', 'Sciences', 'Mr. Njoroge', 4),
('English', 'Languages', 'Ms. Adhiambo', 3),
('Biology', 'Sciences', 'Ms. Otieno', 4),
('History', 'Humanities', 'Mr. Waweru', 3);
The exam results table then records marks for specific students and subjects.
INSERT INTO exam_results (
student_id,
subject_id,
marks,
exam_date,
grade
)
VALUES
(1, 1, 78, '2024-03-15', 'B'),
(1, 2, 85, '2024-03-16', 'A'),
(2, 1, 92, '2024-03-15', 'A'),
(2, 3, 55, '2024-03-17', 'C');
This is where the three tables start working together.
8. Updating and deleting records
In a real system, data changes. A student may move, a mark may be corrected, or an exam result may be cancelled.
-- Esther Akinyi moved from Nakuru to Nairobi
UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;
-- Correct marks for result_id 5 from 49 to 59
UPDATE exam_results
SET marks = 59
WHERE result_id = 5;
-- Remove a cancelled exam result
DELETE FROM exam_results
WHERE result_id = 9;
This section shows that SQL is not only for reading data. It is also used to maintain accurate records.
9. Filtering students and subjects
Filtering is one of the most useful SQL skills. It allows you to ask focused questions.
-- Find all students in Form 4
SELECT *
FROM students
WHERE class = 'Form 4';
Expected output:
| first_name | last_name | class | city |
|---|---|---|---|
| Brian | Ochieng | Form 4 | Mombasa |
| David | Kamau | Form 4 | Nairobi |
| Hassan | Abdi | Form 4 | Mombasa |
Another example finds all subjects in the Sciences department.
-- Find all subjects in the Sciences department
SELECT *
FROM subjects
WHERE department = 'Sciences';
Expected output:
| subject_name | department | teacher_name |
|---|---|---|
| Mathematics | Sciences | Mr. Njoroge |
| Biology | Sciences | Ms. Otieno |
| Physics | Sciences | Mr. Kamande |
| Chemistry | Sciences | Ms. Muthoni |
| Computer Studies | Sciences | Mr. Oduya |
10. Combining conditions with AND and OR
SQL becomes more useful when conditions are combined.
-- Find Form 3 students from Nairobi
SELECT *
FROM students
WHERE class = 'Form 3'
AND city = 'Nairobi';
Expected output:
| first_name | last_name | class | city |
|---|---|---|---|
| Amina | Wanjiku | Form 3 | Nairobi |
| Grace | Mwangi | Form 3 | Nairobi |
| James | Kariuki | Form 3 | Nairobi |
The AND keyword means both conditions must be true.
The OR keyword is used when either condition can be true.
-- Find students who are in Form 2 or Form 4
SELECT *
FROM students
WHERE class = 'Form 2'
OR class = 'Form 4';
11. Using BETWEEN, IN, NOT IN, and LIKE
Intermediate SQL includes operators that make queries cleaner.
| Operator | Meaning | Example use |
|---|---|---|
BETWEEN |
Match values inside a range | Marks from 50 to 80 |
IN |
Match any value in a list | Students from Nairobi, Mombasa, or Kisumu |
NOT IN |
Exclude values in a list | Students not in Form 2 or Form 3 |
LIKE |
Match a text pattern | Subjects containing Studies |
-- Find exam results where marks are between 50 and 80
SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;
-- Find students who live in selected cities
SELECT *
FROM students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');
-- Find subjects whose name contains Studies
SELECT *
FROM subjects
WHERE subject_name LIKE '%Studies%';
These operators reduce repetition and make the question easier to read.
12. Working with string functions
String functions help format text without changing the original stored data.
-- Show city names in uppercase and lowercase
SELECT
city,
UPPER(city) AS upper_city,
LOWER(city) AS lower_city
FROM students;
Expected output:
| city | upper_city | lower_city |
|---|---|---|
| Nairobi | NAIROBI | nairobi |
| Mombasa | MOMBASA | mombasa |
| Kisumu | KISUMU | kisumu |
Another useful string function is CONCAT, which joins text values together.
-- Combine subject name and teacher name
SELECT CONCAT(subject_name, ' - ', teacher_name) AS subject_teacher
FROM subjects;
Expected output:
| subject_teacher |
|---|
| Mathematics - Mr. Njoroge |
| English - Ms. Adhiambo |
| Biology - Ms. Otieno |
13. Working with dates
Dates are very important in school data because students have birth dates and exams have exam dates.
-- Extract year, month, and day from each student's date of birth
SELECT
first_name,
date_of_birth,
EXTRACT(YEAR FROM date_of_birth) AS birth_year,
EXTRACT(MONTH FROM date_of_birth) AS birth_month,
EXTRACT(DAY FROM date_of_birth) AS birth_day
FROM students;
Expected output:
| first_name | date_of_birth | birth_year | birth_month | birth_day |
|---|---|---|---|---|
| Amina | 2008-03-12 | 2008 | 3 | 12 |
| Brian | 2007-07-25 | 2007 | 7 | 25 |
The project also formats exam dates into a friendlier format.
-- Format each exam date as a friendly date
SELECT
result_id,
exam_date,
TO_CHAR(exam_date, 'Day, DDth Month YYYY') AS friendly_date
FROM exam_results;
14. Important gotcha: hidden spaces in TO_CHAR day names
This was one of the most important small lessons I noticed while working with dates.
When TO_CHAR(exam_date, 'Day') is used, PostgreSQL pads the day name with extra spaces so all day names line up with the longest day name, Wednesday. That means the output may look correct on the screen, but the value can contain hidden trailing spaces.
| Format used | What it looks like | What may actually be returned |
|---|---|---|
TO_CHAR(exam_date, 'Day') |
Saturday |
'Saturday ' with hidden padding |
TO_CHAR(exam_date, 'FMDay') |
Saturday |
'Saturday' without padding |
This matters because hidden spaces can break conditional logic. A value that looks like Saturday may not equal the text 'Saturday' if the database actually returned 'Saturday '.
-- This can fail because 'Day' may return padded text such as 'Saturday '
SELECT
result_id,
TO_CHAR(exam_date, 'Day') AS exam_day,
CASE
WHEN TO_CHAR(exam_date, 'Day') IN ('Saturday', 'Sunday') THEN 'Weekend'
ELSE 'Weekday'
END AS day_type
FROM exam_results;
The safer version from this project uses FMDay. The FM modifier means fill mode, and it removes the blank padding from the formatted result.
-- Safer version: FMDay removes hidden padding from the day name
SELECT
result_id,
TO_CHAR(exam_date, 'FMDay') AS exam_day,
CASE
WHEN TO_CHAR(exam_date, 'FMDay') IN ('Saturday', 'Sunday') THEN 'Weekend'
ELSE 'Weekday'
END AS day_type
FROM exam_results;
Another safe option is to wrap the formatted value with TRIM().
-- Alternative fix: TRIM removes leading and trailing spaces
SELECT
result_id,
TRIM(TO_CHAR(exam_date, 'Day')) AS exam_day,
CASE
WHEN TRIM(TO_CHAR(exam_date, 'Day')) IN ('Saturday', 'Sunday') THEN 'Weekend'
ELSE 'Weekday'
END AS day_type
FROM exam_results;
The lesson is simple: when using TO_CHAR() for day names in conditions, use FMDay or TRIM(). Otherwise, the query may look correct but still classify weekends as weekdays because of invisible spaces.
15. Counting records
Counting helps turn raw records into summaries.
-- Count how many students are currently in Form 3
SELECT
class,
COUNT(student_id) AS total_students
FROM students
WHERE class = 'Form 3'
GROUP BY class;
Expected output:
| class | total_students |
|---|---|
| Form 3 | 4 |
Another count checks exam results with marks of 70 or above.
-- Count exam results with marks of 70 or above
SELECT COUNT(result_id) AS total_results
FROM exam_results
WHERE marks >= 70;
This is where the database starts to behave like a reporting tool.
16. Using CASE WHEN for readable labels
CASE WHEN is useful when raw values need to become meaningful labels.
-- Label each exam result with a grade description
SELECT
result_id,
marks,
CASE
WHEN marks >= 80 THEN 'Distinction'
WHEN marks >= 60 THEN 'Merit'
WHEN marks >= 40 THEN 'Pass'
ELSE 'Fail'
END AS result_grade
FROM exam_results;
Expected output:
| result_id | marks | result_grade |
|---|---|---|
| 1 | 78 | Merit |
| 2 | 85 | Distinction |
| 3 | 92 | Distinction |
| 4 | 55 | Pass |
| 5 | 59 | Pass |
17. Using CASE WHEN with class labels
The project also labels students as Senior or Junior based on their class.
-- Label students as Senior or Junior
SELECT
first_name,
last_name,
class,
CASE
WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
WHEN class IN ('Form 2', 'Form 1') THEN 'Junior'
END AS class_label
FROM students;
Expected output:
| first_name | last_name | class | class_label |
|---|---|---|---|
| Amina | Wanjiku | Form 3 | Senior |
| Brian | Ochieng | Form 4 | Senior |
| Esther | Akinyi | Form 2 | Junior |
This shows how SQL can add meaning to stored values.
18. Finding upcoming birthdays with a subquery
The most advanced query in this project finds students whose birthdays are coming up in the next 30 days.
The challenge is that date_of_birth includes the original year of birth. To compare birthdays with the current date, the query first calculates each student's birthday in the current year, then filters from that calculated result.
-- Find students whose birthday is coming up in the next 30 days
SELECT
student_id,
first_name,
last_name,
date_of_birth,
birthday_this_year - CURRENT_DATE AS days_until_birthday
FROM (
SELECT
*,
(
date_of_birth
+ (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM date_of_birth))
* INTERVAL '1 year'
)::date AS birthday_this_year
FROM students
) sub
WHERE birthday_this_year BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days';
This is a strong SQL example because it solves a problem in two stages:
| Stage | What happens |
|---|---|
| Inner query | Calculates each student's birthday in the current year |
| Outer query | Filters only birthdays between today and the next 30 days |
The lesson here is that SQL queries can be built step by step. When a question is too complex, create the missing calculated value first, then filter from it.
What I learned from this project
This project helped me see SQL as more than a language for writing commands. It is a way to organize a real-world situation into clear tables and useful questions.
I practiced:
- Creating schemas and tables
- Using primary keys and foreign keys
- Inserting sample data
- Updating and deleting records
- Filtering with
WHERE,AND, andOR - Using
BETWEEN,IN,NOT IN, andLIKE - Working with string and date functions
- Counting records with
COUNT - Grouping records with
GROUP BY - Creating labels with
CASE WHEN - Solving a multi-step question with a subquery
Final thoughts
Greenwood Academy is a small database project, but it follows the same thinking used in larger systems. First, you decide what information matters. Then you store it in the right tables. After that, SQL helps you ask better questions and produce answers that people can use.
That is what makes this an intermediate SQL project. It is not only about syntax. It is about using SQL to turn raw data into structured, readable, and meaningful information.
Top comments (3)
Good job Nelly👏🏾
I appreciate it Danson
NICE WORK