📌 DDL vs DML
In SQL, commands are broadly divided into two categories:
DDL (Data Definition Language): These commands define and modify the structure of database objects such as tables, schemas, and indexes. Examples include CREATE, ALTER, and DROP.
DML (Data Manipulation Language): These commands deal with the actual data stored inside tables. Examples include INSERT, UPDATE, DELETE, and SELECT.
The key difference is that DDL changes the structure of the database while DML changes the contents of the database.
Putting Commands into Practice
During this week's assignment, I utilized four fundamental commands to build and maintain a mock database:
- CREATE (DDL): I used this to establish the initial structure of the table, defining column names and various data types.
- INSERT (DML): This was the first step in putting data in the tables I had created initially.
- UPDATE (DML): I used UPDATE combined with a specific condition to change existing values—for example, updating an exam result for a particular student.
- DELETE (DML): This used for the removal of specific records that are no longer needed.
Filtering Data Using WHERE
The WHERE clause is used to filter records based on specific conditions. It allows us to retrieve only the data that meets certain criteria.
Some commonly used operators include:
= to get an exact match (e.g., gender = 'F')
or < for comparison (e.g., marks > 70)
BETWEEN allows for range filtering (e.g., marks BETWEEN 50 AND 80)
IN matches multiple values (e.g., city IN ('Nairobi', 'Mombasa'))
LIKE for pattern matching (e.g., first_name LIKE 'A%')
In this assignment, I used these operators to:
- Find students in specific classes
- Filter exam results based on marks
- Search for names starting with certain letters
- Identify students from selected cities
Transforming Data with CASE WHEN
The CASE WHEN statement is used to create conditional logic within SQL queries. It allows us to transform data into more meaningful or readable formats.
In this assignment, I used CASE WHEN to:
Categorize student performance based on marks into “Distinction,” “Merit,” “Pass,” or “Fail”
Classify students as “Senior” or “Junior” based on their class level
This is particularly useful in data analysis because it helps convert raw numerical data into insights that are easier to interpret and present in reports or dashboards.
Weekly Reflection
The Interesting: I found the logic of the CASE WHEN statement incredibly rewarding. There is something satisfying about taking a column of "messy" raw numbers and instantly translating them into meaningful categories that tell a story.
One of the main challenges I encountered was ensuring that my SQL syntax was correct, especially when working with multiple tables and constraints. Small mistakes like missing commas or incorrect column references could cause errors which required careful debugging.
Top comments (0)