Two weeks ago, if you had asked me to explain the difference between a WHERE clause and a HAVING clause, I probably wouldn't have had an answer. Fast forward to today, and I can build a relational database from scratch, fill it with real data, break it on purpose, fix it, and write queries that turn raw data into meaningful insights. I'll walk you through that journey using a school database for Greenwood Academy, complete with students, subjects, and exam results.
We'll build the database step by step, explore the key PostgreSQL concepts, write practical queries that solve real problems, and provide practical examples you can follow along.
What is a database?
A database is an organized collection of related data that is stored electronically and designed to make it easy to store, retrieve, update, and manage information.
What is a Database Management System (DBMS)?
It is a software that enables us to create, manage, and query databases.
Some common examples include: MySQL, PostgreSQL, SQLite
Types of Databases
1. Relational Databases
A relational database stores data in tables (rows and columns).
The tables are related to each other using primary and foreign keys.
2. Non-Relational Databases
They store data in flexible formats rather than fixed tables, which are best suited for handling large volumes of unstructured data.
Why do we use databases?
- Efficient storage of large amounts of data.
- Easy updates without affecting unrelated data.
- Reduced duplication through a proper design.
- Data security with controlled access.
- Support for multiple users working simultaneously.
Creating a Relational Database
SQL(Structured Query Language) is the standard language used to interact with relational databases.
SQL Data Types and Constraints
Data types define the kind of data that can be stored in each column of a table. They help maintain accuracy and consistency.
Constraints are rules applied to columns or tables to control what data can be stored.
Categories of Data Types include:
1. Numeric data types
**INT** - stores standard whole numbers
**SERIAL** - stores auto-incrementing integers like IDs
**DECIMAL** - used for exact decimal values
2. Character/strings
- Char(n) - stores fixed-length text
- Varchar(n) - variable length text with a maximum limit, e.g., varchar(50)
- Text - variable-length text with no strict limit
3. Date and Time
- Date - stores date only
- Time - stores time only
- Timestamp - stores both date and time
Constrains
NOTNULL - ensures a column must have a value
NULL - allows a column to have no value
DEFAULT - provides a value automatically if none is given
PRIMARY KEY - uniquely identifies each row
UNIQUE - ensures all values in a column are different
FOREIGN KEY - it references a primary key from another table
CHECK - ensures a value meets a specific condition
The setup: Structure of the dataset we're working with
- students-student_id, first_name, last_name, gender, date_of_birth, class, city
- subjects-subject_id, subject_name, department, teacher_name, credit_hours
- exam_results-result_id, student_id, subject_id, marks, exam_date, grade
1. DDL - Designing the structure
DDL(Data Definition Language) refers to the set of commands that create and modify database objects such as schemas and tables. Common commands include CREATE SCHEMA, CREATE TABLE, and ALTER TABLE.
Tables are the primary structures used to store data in a relational database.
A schema is a logical container used to group related tables.
Schema Creation
CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;
This creates a schema called greenwood_academy and sets it as the working environment.
Creating 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)
);
This creates a table called students, with the student_id as the primary key. The NOT NULL constraint is used to ensure the database rejects incomplete data.
Creating 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),
credit_hours INT
);
This creates a table called subjects, with the subject_id as the primary key.
Creating the exam_results table
CREATE TABLE exam_results (
result_id INT PRIMARY KEY,
student_id INT NOT NULL references students(student_id)
subject_id INT NOT NULL references subjects(subject_id)
marks INT NOT NULL,
exam_date DATE,
grade VARCHAR(2)
);
The exam_results table is created with the result_id as the primary key. The references clause is introduced to showcase the relationship between the exam_results table, the students, and the subjects table.
The student_id and subject_id columns are foreign keys in the exam_results table; they reference the primary keys in their respective tables.
ALTER TABLE - used to modify an existing table
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 allowed us to add the phone_number column, rename the credits column to credit_hours, and drop the phone_number column we had created.
2. DML - Putting Data In, Taking it out
DML(Data Manipulation Language) consists of commands used to add, modify, and remove data from tables. The most common commands are INSERT, UPDATE, and DELETE.
INSERT - adds new records to a table, whether it is a single row or multiple rows in a single statement, by listing several sets of values.
Inserting data into 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 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 into exam_results
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');
UPDATE - It changes existing records.
Always filter by something specific, ideally the primary key, to avoid updating every row in the table.
UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;
DELETE - removes rows from a table, but maintains the structure of the table. It is accompanied by the where clause to filter the table; otherwise, you risk emptying the whole table.
DELETE FROM exam_results
WHERE result_id = 9;
This removes the row with the student_id of 9.
3. Filtering with WHERE - involves asking the database a question
The WHERE clause is used to filter rows based on a condition, and AND/OR lets you combine conditions.
AND means every condition must be met:
SELECT first_name, last_name, class, city
FROM students
WHERE class = 'Form 3'
AND city = 'Nairobi';
This returns all the students who are in form 3, and come from Nairobi city.
OR means any one of them must hold
SELECT first_name, last_name, class
FROM students
WHERE class = 'Form 2'
OR class = 'Form 4';
This returns either a student who is in form 2 or form 3.
4. Range, Membership & Search operators
This involves filtering the rows with the WHERE clause and the range operators BETWEEN, IN, NOT IN, LIKE
BETWEEN
It checks a range inclusive of both ends.
SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;
The 50 and 80 marks are included in the result.
IN
It replaces a chain of OR when matching against a list of values.
SELECT first_name, last_name, city
FROM students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');
NOT IN
It excludes a list instead of matching it
SELECT first_name, last_name, class
FROM students
WHERE class NOT IN ('Form 2', 'Form 3');
This returns students who are either in form 1 or form 4.
5. AGGREGATION - COUNT, SUM, GROUP BY, HAVING, LIMIT
Aggregation functions are used to get a meaningful summary of how many and how much in total.
COUNT
COUNT(*) - It is paired with the WHERE clause, and it counts the matching rows
SELECT COUNT(*) AS form3_student_count
FROM students
WHERE class = 'Form 3';
This returns the total number of students in Form 3.
SUM
SUM() Adds up a numeric column across matching rows
SELECT SUM(credit_hours) AS total_credit_hours
FROM subjects
WHERE department = 'Sciences';
It sums up the total credit_hours in the science department only.
GROUP BY
It aggregates per category, not just overall; it categorises rows by a shared column first, then runs the aggregate per category.
SELECT
subject_id,
COUNT(*) AS results_recorded,
AVG(marks) AS average_marks
FROM exam_results
GROUP BY subject_id;
It will group subject_id, then return the average marks and count of results per subject_id grouping.
HAVING
It filters the rows after grouping and aggregation is done.
SELECT
subject_id,
COUNT(*) AS results_recorded,
AVG(marks) AS average_marks
FROM exam_results
GROUP BY subject_id
HAVING COUNT(*) > 1;
This returns only subjects that have more than one recorded exam result.
LIMIT
It mostly follows the ORDER BY clause that arranges rows either in ascending or descending order, and it specifies how many rows are returned.
SELECT
subject_id,
COUNT(*) AS results_recorded,
AVG(marks) AS average_marks
FROM exam_results
GROUP BY subject_id
HAVING COUNT(*) > 1
ORDER BY average_marks DESC
LIMIT 3;
This returns only the top 3 average_marks.
6. CASE WHEN
It evaluates conditions top to bottom and returns the label for the first one that matches.
The order of the conditions matters because the first WHEN that evaluates true wins, broader conditions have to come last, not first.
SELECT
result_id,
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;
When you place the condition marks >= 40 above marks >= 80, then every top score gets mislabeled as a "Pass".
7. JOINS
A join connects related tables using the relationships you've created using primary and foreign keys, allowing PostgreSQL to combine the data into a single result set. In our case, we can answer a single question that requires all three tables, as joins allow separate pieces of information to work together as if they are stored in a single table.
INNER JOIN
It returns only the rows that have a match in both tables.
SELECT s.first_name, s.last_name, sub.subject_name, e.marks
FROM students s
INNER JOIN exam_results e ON s.student_id = e.student_id
INNER JOIN subjects sub ON e.subject_id = sub.subject_id;
If a student has no exam results, they won't appear in this output.
LEFT JOIN
A LEFT JOIN returns every row from the left table and matching rows from the right table.
If there is no match, the columns on the right will return a NULL
SELECT s.first_name, s.last_name, e.marks
FROM students s
LEFT JOIN exam_results e ON s.student_id = e.student_id;
Any student who hasn't sat a single exam still shows up, with NULL in the marks column, highlighting the missing results.
RIGHT JOIN
It returns all the rows from the right table and matching rows from the left table.
If there is no match, the columns from the left return NULL
SELECT sub.subject_name, e.marks
FROM exam_results e
RIGHT JOIN subjects sub ON e.subject_id = sub.subject_id;
It returns the subjects with no recorded marks.
SELF JOIN
It joins a table to itself, thus helping to compare rows within the same table.
SELECT
a.first_name AS student_a,
b.first_name AS student_b,
a.city
FROM students a
INNER JOIN students b
ON a.city = b.city
AND a.student_id < b.student_id;
This pairs students who live in the same city, without matching a student against themselves or listing every pair twice. The clause a.student_id < b.student_id ensures no duplicate pairing.
Conclusion
What This Two-Week Sprint Actually Taught Me
Looking back, the biggest lesson wasn't learning a particular SQL command; it was understanding how everything fits together.
Before you can query data, you need somewhere to store it, which is why DDL comes first. Once the structure is in place, you use DML to fill your tables with meaningful data. Only then does it make sense to start asking questions with SELECT, filtering with WHERE, summarizing with GROUP BY and HAVING, and finally connecting related tables with joins.
At first, I wondered why we didn't jump straight into joins. But after spending time querying individual tables, it became obvious why they're so important. A single table can only tell part of the story. The real power of a relational database comes from storing data in separate, well-organized tables and then bringing it together whenever you need to answer a question.
That realization completely changed how I think about SQL. It stopped feeling like a long list of commands to memorize and started feeling like a language for exploring data. Whether you're asking how many, how much, which students performed best, or what trends exist over time, SQL gives you the tools to find those answers.
I'm looking forward to learning more SQL concepts in the weeks ahead.
Top comments (0)