SQL ARTICLE

#beginners #database #sql #tutorial

Understanding DDL, DML, and SQL Operations in Practice

DDL vs. DML

In SQL, commands are broadly divided into two categories: Data Definition Language (DDL) and Data Manipulation Language (DML).

DDL defines and structures the database. It includes commands like CREATE, ALTER, DROP, and RENAME. These commands shape the schema, tables, and columns.
DML works with the actual data inside those structures. It includes INSERT, UPDATE, DELETE, and SELECT. These commands add, modify, remove, or query records.

The difference is clear: DDL sets up the “blueprint” of the database, while DML fills and manages the “content.”

Applying CREATE, INSERT, UPDATE, and DELETE

In the Nairobi Academy assignment:

CREATE was used to build the schema (CREATE SCHEMA nairobi_academy) and tables (students, subjects, exam_results).
INSERT populated the tables with 10 students, 10 subjects, and 10 exam results.
UPDATE modified specific records, such as changing Esther Akinyi’s city from Nakuru to Nairobi and adjusting exam marks.
DELETE removed unwanted data, like cancelling exam result ID 9.

These commands show the lifecycle of data: creation, insertion, adjustment, and removal.

Filtering with WHERE

The WHERE clause is the backbone of SQL queries. It filters results based on conditions:

= finds exact matches (e.g., students in Form 4).
> or < compares values (e.g., marks greater than 70).
BETWEEN checks ranges (e.g., marks between 50 and 80).
IN and NOT IN test membership (e.g., students from Nairobi, Mombasa, or Kisumu).
LIKE searches patterns (e.g., names starting with “A” or “E”).

This flexibility makes WHERE essential for precise data retrieval.

CASE WHEN for Transformation

The CASE WHEN statement adds logic to queries, transforming raw data into meaningful categories. For example:

Exam results were labeled as Distinction, Merit, Pass, or Fail based on marks.
Students were classified as Senior (Form 3 & 4) or Junior (Form 1 & 2).

This feature turns numbers and codes into human‑readable insights, making reports more useful.

Reflection

Working through this assignment highlighted how SQL is both technical and practical. The DDL tasks were straightforward—building tables felt like laying a foundation. The DML tasks required more attention, especially ensuring data consistency when updating or deleting records. The most interesting part was using CASE WHEN, because it showed how SQL can go beyond storage and retrieval to provide analysis and interpretation. The challenge was remembering the exact syntax for altering and renaming columns, but once mastered, it felt empowering to reshape the database structure mid‑stream.