SQL (Structured Query Language) is the standard language for managing and manipulating data in relational databases. Two of the most important categories in SQL are DDL (Data Definition Language) and DML (Data Manipulation Language). Understanding the difference between them is essential for anyone working with databases.
DDL vs DML: What’s the Difference?
DDL (Data Definition Language) defines the structure of the database. It deals with creating, modifying, and deleting database objects such as tables, indexes, views, and schemas. DDL commands are typically used during the design and setup phase of a database.
Common DDL commands include:
CREATE – Creates new database objects
ALTER – Modifies existing database objects
DROP – Deletes database objects
TRUNCATE – Removes all data from a table but keeps the structure
DML (Data Manipulation Language), on the other hand, is used to manage the actual data stored inside the database objects. DML commands allow you to add, modify, retrieve, and remove records.
Common DML commands include:
INSERT – Adds new records (rows) into a table
UPDATE – Modifies existing records
DELETE – Removes records
SELECT – Retrieves data
The major difference between DDL and DML is DDL changes the schema (how the data is organised), while DML works with the data itself. DDL commands usually involve more permanent changes and often require higher privileges. In many database systems, DDL statements implicitly commit the transaction, whereas DML statements can be rolled back.
Using DDL and DML
Below are examples of how to practically use DDL and DML commands
- CREATE (DDL) The CREATE statement is used to define the table structures.
- INSERT (DML)
Once the table is created, it is possible to add data into it.
- UPDATE (DML)
After data is added in the table it is then possible to correct or update the records.
- DELETE (DML) Used to clean up data which is no longer in use.
Filtering Data with the WHERE Clause
One of the most powerful features in SQL is the ability to filter rows using the WHERE clause. It works with SELECT, UPDATE, and DELETE statements to specify which rows should be affected. Common comparison operators used with WHERE:
- = (equals) - Exact match
- >,<=,<,>= (comparison operators)
- BETWEEN - Checks if a value falls within a range
- IN - Checks if a value matches any in a list
- LIKE - Pattern matching(usually with wildcards %)
Transforming Data with CASE WHEN
The CASE WHEN expression is SQL’s way of doing if-then-else logic inside your queries. It’s very useful when you want to:
- Create new columns based on conditions
- Categorise your data (e.g., “Good”, “Average”, “Poor”)
- Clean or transform data without modifying the original table
Below is an example of using CASE WHEN
Conclusion
Mastering DDL and DML, along with filtering techniques (WHERE) and conditional logic (CASE WHEN), provides a strong foundation for working with relational databases. DDL lets you build the house, DML lets you furnish and maintain it, WHERE helps you find exactly what you need, and CASE WHEN allows you to reshape the data intelligently.
Whether you're a student, aspiring data analyst, or backend developer, becoming comfortable with these SQL fundamentals will significantly boost your productivity and understanding of how data is managed behind the scenes.
I have found the use of CASE WHEN the most interesting part of this week's learning as it not only filters through large tables but it also creates new columns with the stated new categories.
Top comments (0)