SQL Basics Explained: DDL, DML, Filtering, and CASE WHEN

Learning SQL can feel overwhelming at first, especially when you're staring at a blank query editor wondering where to even start. This guide breaks down the core building blocks of SQL from creating tables to filtering and summarizing data using a simple school database as a running example. By the end, you'll understand not just the syntax, but why each concept matters.

1. DDL (Data Definition Language): Building the Database Structure

DDL is the part of SQL used to define and shape a database before any data goes into it. This is where you decide what your tables look like, what type of data each column holds, and how tables relate to one another.

Key commands include:

• CREATE SCHEMA — groups related tables together, like a folder for your database objects
• CREATE TABLE — defines a new table and its columns
• ALTER TABLE — modifies an existing table, whether that's adding a column, renaming one, or changing a data type
• DROP COLUMN — permanently removes a column you no longer need
• Primary Keys — a column (or set of columns) that uniquely identifies each row
• Foreign Keys — a column that links one table to another, keeping data connected and consistent
• SERIAL — a PostgreSQL data type that automatically generates a unique, incrementing number for each new row

Example:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    class VARCHAR(10)
);

Here, SERIAL PRIMARY KEY means every student automatically gets a unique ID without you having to assign one manually, and NOT NULL ensures a first name can never be left blank. Together, these constraints protect the integrity of your data from the moment the table is created.

2. DML (Data Manipulation Language): Working with the Data Itself

Once your tables exist, DML is how you actually populate and maintain the data inside them. It covers three main operations:

• INSERT — adds new rows of data
• UPDATE — modifies existing rows
• DELETE — removes rows that are no longer needed

Example:

INSERT INTO students (first_name, last_name, class)
VALUES ('Amina', 'Wanjiku', 'Form 3');

Notice that if student_id is set to SERIAL, you don't need to include it here; the database generates it for you automatically. Including it manually would defeat the purpose of auto-incrementing and could even cause conflicts with existing IDs.

UPDATE and DELETE follow a similar pattern but always pair with a WHERE clause to target the correct row; skipping WHERE on either of these commands is one of the most common (and costly) beginner mistakes, since it will affect every row in the table.

3. Querying Data with SELECT and WHERE

Once data is stored, SELECT is how you retrieve it, and WHERE is how you filter it down to exactly what you need.

Example:

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

Filtering relies on comparison operators such as =, >, <, and >=, and these conditions can be combined:

• AND — narrows results, since all conditions must be true
• OR — broadens results, since any condition being true is enough

Understanding the difference between AND and OR is essential, since mixing them up is a quick way to get an empty result set or far more rows than you expected.

4. Range and Membership Operators

Beyond basic comparisons, SQL offers operators designed to check ranges, lists, and patterns more efficiently:

• BETWEEN — checks whether a value falls within a range
• IN — checks whether a value matches any item in a list
• NOT IN — the opposite of IN, excluding matches
• LIKE — searches for a text pattern

Examples:

marks BETWEEN 50 AND 80

city IN ('Nairobi', 'Kisumu')

first_name LIKE 'A%'

The % symbol is a wildcard that matches any sequence of characters, so 'A%' finds anything starting with "A," while '%a' would find anything ending in "a." These operators make queries shorter and far more readable than writing out long chains of OR conditions.

5. Aggregate Functions

Aggregate functions summarize data across many rows instead of returning each row individually. The most common one is:

COUNT(*)

COUNT(*) tells you how many rows match a given condition for example, how many students belong to a particular class. Other aggregates like SUM, AVG, MIN, and MAX follow the same logic: they collapse a set of rows into a single meaningful number, which is invaluable for reporting and analysis.

6. CASE WHEN: Conditional Logic in SQL

SQL isn't limited to retrieving raw data it can also apply conditional logic directly within a query using CASE WHEN.

Example:

CASE
    WHEN marks >= 80 THEN 'Distinction'
    WHEN marks >= 60 THEN 'Merit'
    ELSE 'Fail'
END

This works exactly like an if...else statement in a programming language: SQL checks each condition in order and returns the result tied to the first one that's true. It's a powerful way to turn raw numbers into meaningful, human-readable labels without needing to process the data outside the database.

Best Practices

A few habits will save you time and prevent costly mistakes as you write more SQL:

• Use meaningful, descriptive table and column names
• Always define primary keys for every table
• Use foreign keys to maintain relationships and data integrity between tables
• Use SERIAL (or GENERATED AS IDENTITY in newer PostgreSQL versions) for auto-incrementing IDs instead of assigning them manually
• Always test UPDATE and DELETE queries with a SELECT first to confirm exactly which rows will be affected

Conclusion

These fundamentals: DDL for structure, DML for managing data, WHERE for filtering, range and membership operators for smarter searches, aggregates for summarising, and CASE WHEN for conditional logic form the foundation that almost every SQL task builds on. Once these feel comfortable, the natural next step is to explore more advanced topics such as joins, indexes, views, functions, and stored procedures, which allow you to work with multiple tables and more complex logic at once.

Comments

[leslie angu]

The article has a logic flow that is insightiful on every paragraph. The title is a bit hard to digest for newbies. Soften it by making it eye-catching. Goodstuff, I hope to see more articles like this.