Introduction
Structured Query Language (SQL) serves as the backbone for working with relational databases. Throughout my journey of learning SQL, two primary command categories have emerged as most important: DDL and DML. Grasping the distinction between these two is crucial for anyone aiming to work effectively with databases. This piece discusses the nature of DDL and DML, the application of common SQL commands, filtering methods, and the usefulness of conditional logic through CASE WHEN.
DDL (Data Definition Language)
DDL refers to the group of SQL commands concerned with the structure of database objects. You can think of it as the blueprint designer who plans everything before any actual data is placed.
Typical DDL Commands:
| Command | Function |
|---|---|
| CREATE | Constructs databases, schemas, tables, and indexes |
| ALTER | Changes existing table layouts (add, rename, or remove columns) |
| DROP | Eliminates entire database objects |
| TRUNCATE | Empties all rows while preserving the table structure |
DML (Data Manipulation Language)
DML deals with the actual information stored within those structures. If DDL acts as the blueprint designer, DML functions as the moving crew that positions, rearranges, or clears out furnishings.
Typical DML Commands:
| Command | Function |
|---|---|
| INSERT | Introduces new rows of information |
| UPDATE | Changes existing information |
| DELETE | Erases specific rows |
| SELECT | Fetches information (sometimes categorized as DQL – Data Query Language) |
CREATE – Establishing the Base
The CREATE command sets up databases, tables, and other structures. When building a table, you need to specify:
- Column names – The labels for each column
- Data types – INT, VARCHAR, DATE, and so on
- Constraints – PRIMARY KEY, FOREIGN KEY, NOT NULL Key lesson: Defining primary keys and appropriate data types prevents data integrity issues later
ALTER
ALTER lets you modify existing table structures:
*INSERT *
INSERT adds new rows to a table. Always specify the column names to avoid errors if the table structure changes:
UPDATE
UPDATE modifies existing data. Always use a WHERE clause unless you intentionally want to update every row:
DELETE
DELETE removes specific rows. Like UPDATE, always use WHERE:
Critical warning: Without WHERE, DELETE FROM removes every row in the table!
FILTERING
it lets you target specific rows instead of operating on entire tables. This is essential for both querying data with SELECT and for safe UPDATE/DELETE operations.
Pattern Matching with LIKE
The LIKE operator uses wildcards for flexible text searching:
'A%' – Starts with 'A' (e.g., 'Alice', 'Andrew')
'%son' – Ends with 'son' (e.g., 'Johnson', 'Jackson')
'%Studies%' – Contains 'Studies' anywhere
'_at' – Underscore matches exactly one character (e.g., 'cat', 'bat', 'hat')
Combining Conditions
You can combine multiple conditions using AND and OR. Use parentheses to control logic order:
CASE WHEN
CASE WHEN is SQL's version of an IF-THEN-ELSE statement. It creates calculated columns without altering your original data. This is incredibly useful for reporting and data categorization.
Conclusion
SQL is two languages in one: DDL for shaping the container and DML for managing the contents. The WHERE clause is your precision tool for targeting specific data, and CASE WHEN is your transformation engine for creating meaningful categories on the fly.
The most important lesson I've learned is that every database change – whether structural (DDL) or data-related (DML) – requires careful thinking, testing, and often a double-check of WHERE clauses. One small mistake can have large consequences, but with proper understanding of these fundamental concepts, you can work with databases confidently and safely.
Top comments (0)