Transitioning from viewing data as simple lists to managing it within a relational database is a milestone for any developer. This week, I took on the task of building a foundational system for** Nairobi Academy**. The goal was to move beyond theory and implement a functional database that tracks students, courses, and enrollments.
Here is a breakdown of the SQL fundamentals used to bring this project to life.
Understanding the Language: DDL vs. DML
Before writing a single line of code, it is essential to distinguish between the two primary "modes" of SQL:
DDL (Data Definition Language): Think of this as the blueprint phase. DDL commands define the structure of the database. When you create a table or change its columns, you are using DDL.
DML (Data Manipulation Language): This is the action phase. Once the structure exists, DML allows you to interact with the actual data—adding new students, updating grades, or removing records.
The Difference: DDL changes the container (the table), while DML changes the content (the rows).
Building the Framework: CREATE, INSERT, UPDATE, and DELETE
In the Nairobi Academy project, these four commands formed the core lifecycle of our data:
CREATE: I used this DDL command to establish our tables. For example, creating the Students table required defining data types like INT for IDs and VARCHAR for names.
INSERT: Once the tables were ready, I populated them with student records and course details. This is where the database actually starts to look like a school roster.
UPDATE: Data is rarely static. When a student changed their contact information or a course name was adjusted, UPDATE allowed me to modify existing rows without deleting them.
DELETE: This was used to remove records that were no longer relevant, such as a student who withdrew from a specific semester.
Precision with the WHERE Clause
A database with thousands of rows is useless if you can't find exactly what you need. The WHERE clause is the ultimate filter. During the project, I utilized several operators to refine my queries:
= and >: Used for direct matches or finding students above a certain age.
BETWEEN: Perfect for finding enrollments within a specific date range.
IN: Allowed me to filter students belonging to a specific list of departments (e.g., 'Science', 'Arts').
LIKE: A powerful tool for pattern matching, such as finding all students whose names start with "J" using LIKE 'J%'.
Transformation with CASE WHEN
One of the most interesting parts of the assignment was using the CASE WHEN statement. This functions like "if-then" logic within a query. Instead of just pulling raw numbers, I used it to transform data on the fly.
For example, I used CASE WHEN to categorize student marks:
If a score was above 80, the query returned 'Distinction'.
If it was between 60 and 79, it returned 'Pass'.
This allows the database to provide meaningful insights rather than just raw data points.
Reflection: Challenges and Wins
This week was an eye-opener. The most challenging aspect was ensuring "Referential Integrity"—making sure I didn't delete a student who was still enrolled in a course, which would cause a conflict in the database.
The most interesting part? Seeing how a few lines of SQL can transform a massive pile of disorganized information into a structured, searchable system. Building the Nairobi Academy project made the abstract concept of "data" feel tangible and manageable.
Next step: Mastering Joins to connect these tables even more deeply.
I really enjoyed this.
Top comments (0)