SQL Simplified: DDL, DML, and Essential Commands.

INTRODUCTION.

SQL (Structured Query Language) is the foundation of relational databases. At first, it may seem like there are many commands to learn, but most real-world work is based on a few core ideas: defining structure, managing data, and querying it effectively.

In my recent work with SQL, I explored how different command types serve different purposes. DDL (Data Definition Language) focuses on structure, using commands like CREATE to build tables. DML (Data Manipulation Language) handles the data itself, using INSERT, UPDATE, and DELETE to manage records. In this article I will walk through those concepts using a school database for a fictional institution called Nairobi Academy.

DDL vs DML — What's the Difference?

• DDL (Data Definition Language)- These are commands that define or change the structure of a database. Examples: CREATE, ALTER, DROP.
• DML (Data Manipulation Language) - DML deals with content. Once your structure exists, DML commands fill it with data, update existing records, and delete what is no longer needed. Examples: INSERT, UPDATE, DELETE, SELECT.

DDL COMMANDS.

1. CREATE- Used to make a new table.

First, I created the schema (a named container that organises your tables inside a database) then built the three required tables for the school: students, subjects, and exam_results.

Always remember to mention the data types for each column such as VARCHAR for text or SERIAL for auto-incrementing numbers. And don’t forget to define a primary key, which acts as the unique identifier for every row in your table.

2. ALTER - Used to change existing tables.

After creating the Students table, the school realized they had forgotten to include a phone number column. Instead of deleting the entire table and starting from scratch, I used the ALTER TABLE command to add the missing column and later, I also used it to remove it when it was no longer needed.

DML COMMANDS.

1. INSERT - Used in adding data.

With the table structure in place, the next step was to populate it with data. PostgreSQL makes this easier by allowing you to insert multiple rows in a single statement, which saves time and reduces repetitive typing.

2. UPDATE - Used for fixing data.

When Esther Akinyi moved from Nakuru to Nairobi, there was no need to delete her entire record. Instead, I simply updated the specific field that changed, using a WHERE clause to ensure that only her row was modified.

Always include a WHERE clause when using UPDATE or DELETE. Without it, the command will affect every row in the table, which is almost never the intended outcome.

1. DELETE - Used for Removing a Row.

The school cancelled one exam result. Using a DELETE statement with a WHERE clause removed only that specific record.

FILTERING WITH WHERE.

The WHERE clause is what makes a query precise instead of overwhelming. It instructs PostgreSQL to return only the rows that meet a specific condition, and it’s essential when working with SELECT, UPDATE, or DELETE statements.

Basic Operators

1. BETWEEN- Used in range queries.

Instead of writing marks >= 50 AND marks <= 80, you can use BETWEEN for a more concise and readable query. The BETWEEN operator is inclusive of both boundary values.

2.IN AND NOT IN - Used in list matching.

Rather than chaining multiple OR conditions, the IN operator lets you check against a list of values in a single, clear, and readable statement.

3. LIKE- Used for pattern matching.

Use LIKE when you only know part of a value. The % symbol acts as a wildcard, meaning “any sequence of characters can go here."

TRANSFORMING DATA WITH CASE WHEN.

CASE WHEN is SQL’s equivalent of an if/else statement. Instead of simply retrieving data as-is, it allows you to create a new column dynamically based on conditions. This makes it especially powerful for transforming raw numbers into meaningful labels.

Syntax structure

Example - Grading exams results.

Every CASE WHEN expression must end with END. Always assign a name to the resulting column using AS, otherwise, the output header will default to “case,” which is not very helpful.

CONCLUSION AND REFLECTION

One of the challenges i faced was BETWEEN operator. I accidentally repeated the column name (exam_date) and used the wrong date format '2024-3-18'. The correct syntax is cleaner: specify the column once, then provide the two boundary values. Fixing this taught me how precise SQL is. Even small format slips can break a query, but once corrected, the logic feels elegant.

SQL can seem intimidating at first, but its logic becomes clear once you grasp its layered structure: use DDL to define the schema, DML to populate it, and SELECT with WHERE to query it. Each step builds naturally on the one before.