sql
CREATE SCHEMA nairobi_academy;
SET search_path TO nairobi_academy;
Then three tables were built using CREATE TABLE:
sql
-- Students table stores pupil information
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    class VARCHAR(20),
    city VARCHAR(50),
    date_of_birth DATE
);

-- Subjects table stores courses offered
CREATE TABLE subjects (
    subject_id SERIAL PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    credits INTEGER
);

-- Exam_results links students to their scores
CREATE TABLE exam_results (
    result_id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES students(student_id),
    subject_id INTEGER REFERENCES subjects(subject_id),
    marks INTEGER CHECK (marks >= 0 AND marks <= 100),
    exam_date DATE
);
Modifying Structure with ALTER
Real-world databases evolve. When the school forgot to include phone numbers, ALTER TABLE resolved it:
sql
Copy
ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);
Later, the credits column needed clearer naming:
sql

ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;
When the requirement changed again, the column was removed:
sql

ALTER TABLE students DROP COLUMN phone_number;
These ALTER operations demonstrate DDL's flexibility—structures adapt without rebuilding everything.
DML in Action: Populating and Managing Data
Once tables existed, DML commands brought them to life with real student records.
INSERT: Adding Data
Ten students, ten subjects, and ten exam results were inserted:
sql

INSERT INTO students (first_name, last_name, gender, class, city, date_of_birth) 
VALUES ('James', 'Mwangi', 'M', 'Form 4', 'Nairobi', '2006-03-15');
The INSERT statement follows a clear pattern: specify the table, list columns, then provide values in matching order. Bulk inserts use comma-separated value sets.
UPDATE: Correcting Data
When Esther Akinyi moved from Nakuru to Nairobi, UPDATE reflected this change:

sql
UPDATE students 
SET city = 'Nairobi' 
WHERE student_id = 5;
A marks entry error was also fixed:

sql

UPDATE exam_results 
SET marks = 59 
WHERE result_id = 5;
Critical rule: Always use WHERE with UPDATE. Without it, every row changes.
DELETE: Removing Data
When an exam was cancelled, DELETE removed it cleanly:
sql

DELETE FROM exam_results 
WHERE result_id = 9;
Again, WHERE is essential—omitting it empties the entire table.
Filtering Data with WHERE
The WHERE clause is SQL's gatekeeper. It filters rows based on conditions, ensuring queries return only relevant data.
Basic Comparison Operators
Table
Operator    Meaning Example
=   Equal to    WHERE class = 'Form 4'
>   Greater than    WHERE marks > 70
<   Less than   WHERE marks < 40
>=  Greater than or equal   WHERE marks >= 70
<=  Less than or equal  WHERE marks <= 50
<> or !=    Not equal   WHERE city <> 'Nairobi'
Logical Operators
Combine conditions with AND and OR:

sql
-- Form 3 students from Nairobi (both conditions must be true)
SELECT * FROM students WHERE class = 'Form 3' AND city = 'Nairobi';

-- Students in Form 2 or Form 4 (either condition can be true)
SELECT * FROM students WHERE class = 'Form 2' OR class = 'Form 4';
Special Operators
BETWEEN checks ranges inclusively:

sql
-- Marks from 50 to 80, including both endpoints
SELECT * FROM exam_results WHERE marks BETWEEN 50 AND 80;
IN checks membership in a list:

sql

-- Students in any of these three cities
SELECT * FROM students WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');
N IN excludes values:

sql

-- Students in Form 1 or Form 4 only
SELECT * FROM students WHERE class NOT IN ('Form 2', 'Form 3');

LIKE enables pattern matching with wildcards:
Table
Pattern Matches
'A%'    Starts with 'A'
'%Studies%' Contains 'Studies' anywhere
'_a%'   Second letter is 'a'

sql

-- First names starting with A or E
SELECT * FROM students 
WHERE first_name LIKE 'A%' OR first_name LIKE 'E%';
Transforming Data with CASE WHEN
Raw data often needs interpretation before it becomes useful. CASE WHEN acts as SQL's if-then-else logic, creating new calculated columns based on conditions.
Grading Exam Results
Instead of displaying raw marks, the assignment labeled each score with a performance grade:
sql

SELECT 
    result_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;
SQL evaluates conditions top to bottom. A mark of 85 hits the first condition (>= 80) and becomes "Distinction"—it never reaches the >= 60 check. This ordering is crucial.
Categorizing Students
Students were grouped into academic levels:
sql

SELECT 
    first_name,
    last_name,
    class,
    CASE 
        WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
        WHEN class IN ('Form 1', 'Form 2') THEN 'Junior'
    END AS student_level
FROM students;
Every CASE must end with END, and AS names the new column.    

In onclusion, DDL builds the container, while DML fills and shapes the content. The Nairobi Academy data, demonstrated this relationship—CREATE and ALTER established tables, then INSERT, UPDATE, and DELETE managed student records. Filtering with WHERE and operators like BETWEEN, IN, and LIKE narrowed results to specific needs. Finally, CASE WHEN transformed numerical marks into meaningful categories, proving that SQL is not just about storing data—it's about making data understandable.
Mastering these basics prepares you for;
*joins
*windows funtions
*Query optimaization
*subqueries.
 In SQL real understanding comes from writing queries and lots of practice.