My First Week with SQL: A Beginner's Guide to Building, Filling, and Querying a Real Database

From CREATE TABLE to CASE WHEN - everything I learned this week, explained simply

---

Introduction - Why SQL?

If you have ever wondered how apps like M-Pesa know your balance, how a hospital tracks your records, or how a school manages thousands of student results - the answer is almost always a database. And the language used to talk to those databases is SQL - Structured Query Language.

This week I started learning SQL from scratch. By the end of the week, I could build a database from nothing, fill it with real data, search through it, filter results, and even create custom labels for every row. This article walks through everything I learned - written in plain English so that any complete beginner can follow along.

We built our practice database around Nairobi Academy - a fictional secondary school in Nairobi. Three tables: students, subjects, and exam results. Let's go through everything step by step.

---

Part 1 - Building the Database (DDL)

Before you can store any data, you need to create the structure that will hold it. This is called DDL - Data Definition Language. Think of it like building the shelves before you put any books on them.

Step 1 - Create a Schema

A schema is a container - it groups all your tables together in one named space. Think of it like a folder on your computer.

CREATE SCHEMA nairobi_academy;

set search_path to nairobi_academy;

CREATE SCHEMA makes the folder. set search_path tells SQL to go inside it.

Step 2 - Create Tables

A table is where data actually lives - like a spreadsheet with rows and columns. When creating a table you define every column, what type of data it holds, and what rules it must follow.

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

Key words to know:

• PRIMARY KEY - a unique ID for every row. No two students can share the same student_id.
• NOT NULL - this field is required. You cannot add a student without a first_name.
• VARCHAR(n) - text up to n characters long.
• INT - a whole number. Perfect for IDs and counts.
• DATE - a calendar date stored as YYYY-MM-DD.

Step 3 - Modify Tables with ALTER TABLE

Sometimes after creating a table you realise something needs to change. ALTER TABLE lets you add, rename, or remove columns without deleting the whole table.

-- Add a column
ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);

-- Rename a column
ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;

-- Remove a column completely
ALTER TABLE students DROP COLUMN phone_number;

** Warning:** DROP COLUMN permanently removes the column and all its data. Always double-check before running it - there is no undo.

---

Part 2 - Filling the Database (DML)

Once the tables exist, we fill them with data. This is called DML - Data Manipulation Language. Think of it like finally putting the books on the shelves.

INSERT INTO - Adding Rows

This is how you add data into a table. You list the columns you are filling, then provide the values in the same order.

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

** Tips:** Text values always go in single quotes: 'Nairobi'. Numbers do not need quotes: 1, 2, 3. You can insert multiple rows at once by separating each set with a comma.

UPDATE - Changing Existing Data

When data needs to change - like a student moving to a different city - you use UPDATE. Always include WHERE to target only the specific row you want to change.

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

** Golden rule:** NEVER run UPDATE without WHERE. Without it, SQL updates every single row in the table. Always target specific rows using WHERE.

DELETE - Removing Rows

To remove a specific row from a table, use DELETE FROM. Again - always use WHERE.

DELETE FROM exam_results
WHERE result_id = 9;

---

Part 3 - Finding What You Need (Filtering with WHERE)

Just pulling all the data with SELECT * is rarely useful in real life. You almost always need to filter - to tell SQL: give me only the rows that match my conditions. All filtering uses the WHERE clause followed by an operator.

Comparison Operators - The Basics

These compare a column value against something specific:

-- Find all Form 4 students
SELECT * FROM students WHERE class = 'Form 4';

-- Find exam results above 70
SELECT * FROM exam_results WHERE marks > 70;

-- Find students NOT from Nairobi
SELECT * FROM students WHERE city != 'Nairobi';

AND / OR / NOT - Combining Conditions

Sometimes one condition is not enough. AND requires both conditions to be true. OR requires at least one to be true. NOT flips a condition.

-- AND: Form 3 students from Nairobi only
SELECT * FROM students WHERE class = 'Form 3' AND city = 'Nairobi';

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

BETWEEN - Checking a Range

Instead of writing >= and <= separately, BETWEEN is a clean shortcut. It is inclusive - both the lower and upper values are included.

-- Marks between 50 and 80 (includes 50 and 80)
SELECT * FROM exam_results WHERE marks BETWEEN 50 AND 80;

-- Exams in a date range
SELECT * FROM exam_results WHERE exam_date BETWEEN '2024-03-15' AND '2024-03-18';

IN and NOT IN - Matching a List

When you want to match any value from a list, IN is much cleaner than writing many OR conditions.

-- Instead of: WHERE city = 'Nairobi' OR city = 'Mombasa' OR city = 'Kisumu'
SELECT * FROM students WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

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

LIKE - Searching for Patterns

LIKE lets you search for patterns inside text. The % symbol means 'any number of characters'.

-- Starts with 'A'
SELECT * FROM students WHERE first_name LIKE 'A%';

-- Contains the word 'Studies'
SELECT * FROM subjects WHERE subject_name LIKE '%Studies%';

-- Ends with 'i'
SELECT * FROM students WHERE city LIKE '%i';

COUNT - Counting Rows

COUNT(*) tells you how many rows match your condition. Very useful for quick summaries.

-- How many students are in Form 3?
SELECT COUNT(*) AS form3_count
FROM students WHERE class = 'Form 3';

---

Part 4 - Smart Labels with CASE WHEN

CASE WHEN is SQL's way of saying 'if this, then that'. It lets you create a new column in your results with a label or category based on conditions you define. The original table is never changed - you are just adding a label when you SELECT the data.

The Basic Structure

SELECT column_name,
    CASE
        WHEN condition1 THEN 'result1'
        WHEN condition2 THEN 'result2'
        ELSE 'default_result'
    END AS new_column_name
FROM your_table;

Real Example - Labelling Exam Results

Instead of showing just the number, let's label each result as Distinction, Merit, Pass, or Fail:

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;

** Important - Order matters!** SQL checks conditions from top to bottom and stops at the first one that is TRUE. Always put the most specific (highest) condition first. If you put marks >= 40 first, every result above 40 would get 'Pass' and never reach Merit or Distinction.

Labelling Students as Senior or Junior

We can use IN inside CASE WHEN to check multiple values at once:

SELECT
    first_name, last_name, class,
    CASE
        WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
        ELSE 'Junior'
    END AS student_level
FROM students;

---

Part 5 - What I Learned This Week

Looking back at the week, here are the most important things that stuck with me:

The Golden Rules I Will Never Forget

• Always use WHERE with UPDATE and DELETE - without it you change or delete every single row in the table.
• Text values need single quotes - 'Nairobi'
• Numbers never need quotes - WHERE marks > 70
• Every SQL statement ends with a semicolon ( ; ) - think of it as a full stop.
• BETWEEN is inclusive - BETWEEN 50 AND 80 includes 50 and 80 themselves.
• CASE WHEN checks top to bottom - put the most specific condition first, not last.
• LIKE with % is flexible - 'A%' starts with A, '%A' ends with A, '%A%' contains A.
• IN is cleaner than many ORs - IN ('A','B','C') vs city='A' OR city='B' OR city='C'.

The Moments That Made It Click

The librarian analogy was what made SQL make sense to me. You do not understand everything inside a library - but you can walk up to the librarian and say 'I need books about cooking published after 2020, arranged by title'. SQL is exactly that. You describe what you want and the database finds it.

The moment I ran my first CASE WHEN and saw 'Distinction', 'Merit', 'Pass', 'Fail' appear next to marks - instead of just numbers it felt like the data was finally speaking in human language. That was genuinely exciting.

And the first time I made the mistake of running UPDATE without WHERE - and saw all the cities change to the same value - I understood immediately why that golden rule exists.

What Is Coming Next

Next week we go deeper:

• Row-level functions - UPPER, LENGTH, ROUND, DATE_FORMAT and more
• CAST and formatting - converting between data types
• JOINs - combining data from multiple tables at once (this is where SQL gets really powerful)

---

Closing Thoughts

If you are a complete beginner reading this - SQL is not as scary as it looks. The commands are written in almost plain English. SELECT means 'get'. FROM means 'from'. WHERE means 'but only where'. INSERT INTO means 'add this to'. Once you see the pattern, it feels natural very quickly.

The best advice I can give from one week of learning: type every query yourself. Do not just read examples. Open your SQL tool, build the table, insert the rows, run the filters. The mistakes you make while doing it are worth more than a hundred examples you only read.

See you in the next article - where I will cover JOINs.