A Deep Dive into SQL Basics

This week, I transitioned from simply viewing data to actively managing it. Using a practical assignment focused on a hypothetical "Nairobi Academy," I explored how SQL serves as the backbone for creating, modifying, and querying relational databases. Here is a breakdown of the core concepts and how they were applied.

The Architecture of Data: DDL vs. DML

• DDL (Data Definition Language): This is used to define the structure of the database. It includes commands like CREATE (to build tables or schemas), ALTER (to change the structure), and DROP (to delete the structure).

• DML (Data Manipulation Language): This is used to manage the data within the structures that have been defined by the DDL. It includes commands like INSERT (adding records), UPDATE (modifying existing records), and DELETE (removing records).

Step By Step example in a hypothetical project.

Project Goal: Building a school management system from scratch (nairobi_academy project)

• CREATE: I first established a schema named nairobi_academy and then defined three core tables: students, subjects, and exam_results.

• INSERT: Once the structure existed, I populated the tables with data.

• UPDATE: Data is rarely static. When one of the students relocated from Nakuru to Nairobi, I used the UPDATE command paired with a WHERE clause to modify her specific record.

• DELETE: To handle errors or cancellations, I used DELETE to remove specific rows from the table in question.

Precision Querying with WHERE clause

The WHERE clause is the most powerful tool for filtering data. With it, we can find exactly what we need. During the assignment, I practiced using various operators:

• Comparison (=, >=): Finding all female students (gender = 'F') or identifying high achievers (marks >= 70).

• BETWEEN: This allowed for clean range filtering, such as finding exams taken within a specific four-day window in March 2024.

• IN: A more efficient way to check against a list. Instead of multiple OR statements, I used IN ('Nairobi', 'Mombasa', 'Kisumu') to find students from specific cities.

• LIKE (Wild Card): Essential for pattern matching. I used this to find all subjects containing the word "Studies" (%Studies%) or students whose names started with "A" or "E".

Transforming Data with CASE WHEN

One of the most interesting tools explored was the CASE WHEN statement. This allows for conditional logic within a query, effectively creating new data labels without changing the original table.

For the Nairobi Academy, I used CASE WHEN to:
Assign Grades: Convert numeric marks into labels like 'Distinction', 'Merit', or 'Pass'.
Classify Levels: Group students into 'Senior' (Form 3/4) or 'Junior' (Form 1/2) categories based on their class.

Reflection

The most interesting part of this week was realizing how critical the order of operations is, especially when dealing with Foreign Keys or dropping columns. A small challenge I encountered was dealing with instances where the column name I was trying to create had the same name as one of the SQL functions CLASS. For this, I had to wrap the column name in double quotes to specify that it is the column I’m referring to and not the function.

The highlight was definitely the CASE WHEN logic. It feels like "programming" within SQL, providing a way to turn raw numbers into meaningful insights that a school principal or teacher could actually use. It’s the difference between seeing "78" and seeing a "Distinction."