Surviving SQL Is Winning, Franklin

I was given a PostgreSQL assignment.

The brief said:

You are now the DBA for Greenwood Academy.

No handover meeting. No existing documentation. No senior DBA saying, “Here is what happened last quarter.”

Just a school, some messy data, and a list of tasks.

So I opened DBeaver.

For anyone new to this: I was using DBeaver to connect to PostgreSQL and run my SQL queries. PostgreSQL is the database system. DBeaver is the tool I used to work with it.

I wrote the instructions in DBeaver.

PostgreSQL did the actual work.

And, as I would soon discover, PostgreSQL was very committed to doing exactly what I told it to do.

SQL Has Departments

Before touching Greenwood Academy, I needed to understand the different types of SQL commands.

SQL commands are grouped according to what they do. Think of them as different departments in the same organisation. They all work with the database, but they have different jobs.

Category	Full Name	Main Job	Examples
DDL	Data Definition Language	Creates and changes database structures	CREATE, ALTER, DROP, TRUNCATE
DML	Data Manipulation Language	Adds, changes, and removes data	INSERT, UPDATE, DELETE
DQL	Data Query Language	Retrieves data	SELECT
DCL	Data Control Language	Manages permissions and access	GRANT, REVOKE
TCL	Transaction Control Language	Manages database transactions	COMMIT, ROLLBACK, SAVEPOINT

The quick version:

DDL → Build the structure
DML → Change the data
DQL → Ask questions
DCL → Control access
TCL → Decide whether changes stay

We will not use every command in this article. Greenwood Academy did not require me to manage database permissions or recover from a transaction disaster.

This assignment focused mainly on:

DDL → CREATE, ALTER
DML → INSERT, UPDATE, DELETE
DQL → SELECT

We will also use WHERE, AND, OR, BETWEEN, IN, NOT IN, LIKE, COUNT(*), and CASE WHEN.

So, with the departments introduced, it was time to start with the construction department.

SECTION A: DDL - Building Greenwood Academy

DDL stands for Data Definition Language.

DDL is about the structure of the database.

Before asking:

Which students are in Form 3?

we need somewhere to store the students.

Before storing exam results, we need somewhere to put the results.

So the first task was to create a schema:

CREATE SCHEMA greenwood_academy;

A schema helps organise database objects. I wanted the Greenwood Academy tables grouped together instead of scattered around the database like files named:

final.sql
final2.sql
final_final.sql
final_final_use_this_one.sql

Then I set the schema as the working location:

SET search_path TO greenwood_academy;

Now PostgreSQL knew where I wanted to work.

Creating the Tables

The students table needed a unique ID, names, gender, date of birth, class, and city:

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

The student_id is the PRIMARY KEY, meaning each student must have a unique identifier.

Because names are not always enough.

You can have:

Brian Ochieng
Brian Ochieng
Brian Ochieng

But their IDs should still be different.

The database does not care which Brian sits near the window. The database wants the ID.

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

The UNIQUE constraint on subject_name helps prevent duplicate subject names.

Because we probably do not need:

Mathematics
Mathematics
Mathematics

unless Mathematics has somehow become three different departments.

Finally, the exam results:

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

Instead of repeatedly storing the student's full name and every other detail in every exam record, we use IDs.

That keeps the data more organised and saves us from trying to determine whether these are four different people:

Amina Wanjiku
Amina Wanjiku 
AMINA WANJIKU
Amina Wanjiku N.

Sometimes the database problem is not the database.

Sometimes it is the person who entered the data.

Then the Requirements Changed

Once the tables were created, someone realised the students table needed a phone number.

So we added one:

ALTER TABLE students
ADD COLUMN phone_number VARCHAR(20);

Then the credits column needed a new name:

ALTER TABLE subjects
RENAME COLUMN credits TO credit_hours;

Then the phone number was no longer needed:

ALTER TABLE students
DROP COLUMN phone_number;

Added.

Renamed.

Removed.

The database had barely settled down.

This is a realistic introduction to working with requirements. Someone says:

“We need this column.”

You add it.

Then:

“Actually, we don't need it.”

You remove it.

Then, three months later:

“Why doesn't the column exist anymore?”

This is why database work requires technical skills and the ability to remain calm while requirements perform a complete Nairobi matatu route change.

SECTION B: DML - Putting Data Into the Tables

DDL built the structure.

Now we needed data.

DML stands for Data Manipulation Language.

The main commands are:

INSERT
UPDATE
DELETE

This is where the database starts becoming useful.

It is also where you start reading your queries twice.

Maybe three times.

INSERT: Adding the Data

The first student:

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

Then the remaining students were inserted.

The data included students from Nairobi, Mombasa, Kisumu, Nakuru, and Eldoret.

Then came the subjects.

Then the exam results.

At this point, the database had data.

Which meant it was time for the data to be wrong.

Esther Moved From Nakuru to Nairobi

Esther Akinyi had moved from Nakuru to Nairobi.

The database still had her old city.

So:

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

The WHERE clause tells PostgreSQL exactly which student to update.

Student number 5.

Esther.

Not everyone.

Now look at this:

UPDATE students
SET city = 'Nairobi';

That updates every student.

Amina moves to Nairobi.

Brian moves to Nairobi.

Cynthia moves to Nairobi.

Everyone moves to Nairobi.

Mombasa is finished.

Nakuru is finished.

Eldoret is now a historical concept.

This is why a missing WHERE clause can ruin your career.

The database does not stop and ask:

“Are you sure you meant all 10 students?”

It does exactly what you wrote.

Which is admirable.

Until you realise what you wrote.

The 49 That Was Actually a 59

Result number 5 had the wrong mark.

The database said 49.

The correct mark was 59.

So:

UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

One record.

One correction.

Done.

Now imagine this:

UPDATE exam_results
SET marks = 59;

Every exam result is now 59.

The student who scored 95?

59.

The student who scored 78?

59.

The student who scored 49?

Also 59.

We have not corrected the data.

We have created a very strange grading policy.

This is the moment you start appreciating the humble WHERE clause.

The Canceled Exam

Result number 9 had been canceled:

DELETE FROM exam_results
WHERE result_id = 9;

One result removed.

The dangerous version:

DELETE FROM exam_results;

That deletes every exam result.

No warning.

No popup.

No person from IT appearing beside you asking:

“What exactly are you doing?”

The database trusts you.

Perhaps too literally.

A good habit is to check the row before changing or deleting it:

SELECT *
FROM exam_results
WHERE result_id = 9;

If the correct row appears, then delete it.

The same principle applies to updates:

SELECT *
FROM students
WHERE student_id = 5;

Then:

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

First, inspect.

Then, change.

Because fixing one student's city is a normal database task.

Explaining why the entire school now lives in Nairobi is a different kind of meeting.

SECTION C: DQL - Asking the Database Questions

Now we get to SELECT.

DQL stands for Data Query Language.

The database has the data.

We ask questions.

To find Form 4 students:

SELECT *
FROM students
WHERE class = 'Form 4';

To find subjects in the Sciences department:

SELECT *
FROM subjects
WHERE department = 'Sciences';

To find exam results with marks of 70 or above:

SELECT *
FROM exam_results
WHERE marks >= 70;

To find female students:

SELECT *
FROM students
WHERE gender = 'F';

This is the basic idea behind WHERE.

You describe what you want.

The database checks the rows.

It returns what matches.

No manually scanning through hundreds of spreadsheet rows and hoping you did not accidentally leave a filter active from yesterday.

AND: When Both Conditions Must Be True

The assignment asked us to find students who were in Form 3 and from Nairobi:

SELECT *
FROM students
WHERE class = 'Form 3'
AND city = 'Nairobi';

Both conditions must be true.

A Form 3 student from Kisumu does not qualify.

A Nairobi student in Form 4 does not qualify.

You can add more conditions:

SELECT *
FROM students
WHERE gender = 'F'
AND class = 'Form 3'
AND city = 'Nairobi';

Now all three conditions must be true.

OR: When Either Condition Works

Now we want students in Form 2 or Form 4:

SELECT *
FROM students
WHERE class = 'Form 2'
OR class = 'Form 4';

Form 2?

Include.

Form 4?

Include.

Form 3?

Not this time.

The difference is:

AND → all conditions must be true
OR  → at least one condition must be true

Two small words.

Very different results.

SECTION D: Better Ways to Filter Data

Sometimes the question is not:

Is this value exactly equal to that value?

Sometimes we need a range, a list, or a pattern.

That is where BETWEEN, IN, NOT IN, and LIKE become useful.

BETWEEN: Working With a Range

To find exam results between 50 and 80:

SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

This includes both boundaries:

50 → included
65 → included
80 → included
49 → excluded
81 → excluded

You could also write:

WHERE marks >= 50
AND marks <= 80;

That works.

But BETWEEN is cleaner when working with a range.

It can also be used with dates:

SELECT *
FROM exam_results
WHERE exam_date BETWEEN '2024-03-15' AND '2024-03-18';

IN: Checking Against a List

Suppose we want students from Nairobi, Mombasa, or Kisumu.

We could write:

WHERE city = 'Nairobi'
OR city = 'Mombasa'
OR city = 'Kisumu';

Or:

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

IN is useful when you have a list of acceptable values.

It is shorter, easier to read, and saves you from writing a query that looks like it is negotiating with itself.

NOT IN: Excluding Values

Now find students who are not in Form 2 or Form 3:

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

This excludes those two classes.

Everyone else remains eligible.

Sometimes the easiest way to describe what you want is to describe what you do not want.

SQL understands that too.

LIKE: Searching for Patterns

Suppose we want students whose first name starts with A or E:

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

The % is a wildcard.

It means there can be anything after this.

The assignment also asked for subjects containing the word Studies:

SELECT *
FROM subjects
WHERE subject_name LIKE '%Studies%';

The % appears on both sides because the word can occur anywhere in the subject name.

This is cleaner than manually writing:

WHERE subject_name = 'Computer Studies'
OR subject_name = 'Business Studies'
OR subject_name = 'Social Studies';

and then remembering another subject later.

The wildcard does the searching.

We do not have to manually list every possible value.

SECTION E: COUNT(*) - Let the Database Do the Counting

How many students are currently in Form 3?

We could count them manually.

But we have a database:

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

How many exam results have marks of 70 or above?

SELECT COUNT(*)
FROM exam_results
WHERE marks >= 70;

COUNT(*) counts the number of rows that match the condition.

This is one of those moments where using a computer is a sensible decision.

The database is already sitting there with the rows.

Let it count them.

You have other things to do.

SECTION F: CASE WHEN - Giving Raw Data Some Meaning

The database stores marks.

People like categories.

For example:

80 and above → Distinction
60–79        → Merit
40–59        → Pass
Below 40     → Fail

We can write:

Now the query can give us:

Marks	Performance
85	Distinction
72	Merit
59	Pass
39	Fail

The important part is that we did not change the original marks.

If the database stores:

85
72
59
39

it still stores:

85
72
59
39

The query simply creates a new label while displaying the results.

That means we can change the classification rules later without rewriting the original marks.

The raw data stays as it is.

The interpretation can change.

We can do the same thing with student classes:

A student in Form 3 is still in Form 3.

We are simply creating another way to describe that student.

No rewriting the original data.

No creating another spreadsheet.

The query handles it.

The Part I Found Most Important

The assignment covered a lot of SQL.

But the part I kept thinking about was the difference between:

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

and:

UPDATE students
SET city = 'Nairobi';

One updates Esther.

The other relocates the entire school.

The same applies to:

DELETE FROM exam_results
WHERE result_id = 9;

versus:

DELETE FROM exam_results;

One removes a canceled exam.

The other removes every exam result.

SQL is not difficult because the database is trying to trick you.

SQL is difficult because the database is perfectly willing to do exactly what you told it to do.

Even when what you told it to do was a terrible idea.

So before running an UPDATE or DELETE, especially on important data, check what you are about to affect:

SELECT *
FROM students
WHERE student_id = 5;

Then make the change.

Same condition.

First, inspect.

Then, change.

This is not advanced database engineering.

It is simply a good habit.

And good habits are useful when you are tired, it is 2:00 AM, and somebody has just said:

“It is a very small change.”

What Greenwood Academy Taught Me About SQL

The assignment started with an empty database.

By the end, I had:

• Created the greenwood_academy schema.
• Created the students table.
• Created the subjects table.
• Created the exam_results table.
• Added a phone_number column.
• Renamed credits to credit_hours.
• Removed the phone_number column.
• Inserted student, subject, and exam result records.
• Updated Esther's city from Nakuru to Nairobi.
• Corrected a mark from 49 to 59.
• Deleted a canceled exam result.
• Filtered data using WHERE.
• Combined conditions using AND and OR.
• Used BETWEEN for ranges.
• Used IN and NOT IN for lists.
• Used LIKE with wildcards.
• Counted records using COUNT(*).
• Created performance labels using CASE WHEN.

That sounds like a lot.

But when broken down, SQL was asking me to do a few very direct things:

CREATE → Make something
INSERT → Put something in
UPDATE → Change something
DELETE → Remove something
SELECT → Show me something

The difficult part was not simply knowing that these commands exist.

The difficult part was being precise about what they should affect.

That is where the WHERE clause quietly becomes one of the most important things in the entire assignment.

You can learn the syntax.

You can memorise the commands.

But at some point, you have to ask:

What exactly will this query change?

That question is probably more useful than memorising another SQL keyword.

Final Thoughts

I started with an empty PostgreSQL database and a list of questions.

By the end, I had a better understanding of how the different parts fit together.

DDL built the structure.

DML put data into it and changed it.

DQL let me ask questions about it.

BETWEEN, IN, NOT IN, and LIKE made those questions more specific.

COUNT(*) handled the counting.

CASE WHEN let me add useful labels without changing the original data.

And WHERE reminded me that SQL is extremely literal.

The database will not rescue you from your own query.

It will not stop and say:

“I think you meant one row.”

It will execute.

So I have taken one practical rule away from Greenwood Academy:

Before I run an UPDATE or a DELETE, I want to know exactly which rows I am touching.

Because fixing Esther's city is a normal database task.

Moving every student in the school to Nairobi is a different kind of meeting.

And I am not trying to attend that meeting.

Surviving SQL is winning, Franklin.