Beginner SQL Basics: Part 1 – DDL and DML Commands

SQL might seem scary at first, but I remember when I started learning it five years ago - it felt like trying to speak a foreign language. Today, I can't imagine working with databases without it. Think of SQL as a way to have conversations with your database, asking it questions and telling it what to do.

In this first part of our SQL journey, we'll cover the two main types of SQL commands that you'll use almost every day: DDL and DML. These aren't just technical terms to memorize - they're the building blocks that will help you organize and work with your data effectively.

What is DDL (Data Definition Language)?

DDL stands for Data Definition Language. If databases were like houses, DDL would be the blueprint and construction tools. It's all about creating, changing, and removing the structure of your database.

When I first learned about DDL, my instructor compared it to setting up filing cabinets in an office. Before you can store any documents, you need to decide what cabinets you need, how many drawers each should have, and what labels to put on them.

Key DDL Commands

DDL has several important commands, but let's focus on the most common ones:

• CREATE - Makes new database objects like tables, indexes, or entire databases

• ALTER - Changes the structure of existing objects

• DROP - Removes objects completely

• TRUNCATE - Removes all data from a table but keeps the structure

CREATE Command

The CREATE command is like buying a new filing cabinet. You decide what it looks like and where it goes.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    enrollment_date DATE
);

This example creates a table called "students" with five columns. Each column has a specific data type - INT for numbers, VARCHAR for text with a character limit, and DATE for dates.

I remember creating my first table and feeling like I had just built something real. It's satisfying to see the structure come together piece by piece.

ALTER Command

Sometimes you realize you need to change your filing system. Maybe you need more drawer space or want to add new labels. That's what ALTER does.

ALTER TABLE students 
ADD COLUMN phone_number VARCHAR(15);

This command adds a new column for phone numbers to our existing students table. You can also use ALTER to change column types or remove columns:

ALTER TABLE students 
DROP COLUMN phone_number;

DROP Command

DROP is like throwing away an entire filing cabinet. Once you use it, everything inside is gone forever.

DROP TABLE students;

Be very careful with DROP. I've seen people accidentally delete important tables because they didn't double-check their command. Always make sure you have backups.

TRUNCATE Command

TRUNCATE empties all the drawers in your filing cabinet but keeps the cabinet itself.

TRUNCATE TABLE students;

This removes all student records but keeps the table structure intact. It's faster than deleting records one by one, which we'll talk about in the DML section.

What is DML (Data Manipulation Language)?

DML stands for Data Manipulation Language. If DDL is about building the filing cabinets, DML is about working with the documents inside them. You use DML to add, read, update, and remove the actual data.

This is probably where you'll spend most of your time once your database structure is set up. I find DML more exciting because you're working with real information that changes and grows.

Key DML Commands

The main DML commands are:

• SELECT - Retrieves data from tables

• INSERT - Adds new data

• UPDATE - Changes existing data

• DELETE - Removes specific data

SELECT Command

SELECT is like looking through your files to find specific documents. It's the most commonly used SQL command.

SELECT first_name, last_name 
FROM students 
WHERE enrollment_date > '2023-01-01';

This query finds the names of all students who enrolled after January 1st, 2023. You can select all columns using an asterisk (*), but I recommend being specific about which columns you need. It makes your queries faster and easier to understand.

INSERT Command

INSERT adds new records to your table, like filing a new document.

INSERT INTO students (student_id, first_name, last_name, email, enrollment_date)
VALUES (1, 'John', 'Smith', 'john.smith@email.com', '2023-09-15');

You can also insert multiple records at once:

INSERT INTO students (student_id, first_name, last_name, email, enrollment_date)
VALUES 
    (2, 'Sarah', 'Johnson', 'sarah.j@email.com', '2023-09-16'),
    (3, 'Mike', 'Brown', 'mike.brown@email.com', '2023-09-17');

UPDATE Command

UPDATE changes information in existing records. It's like taking a document out of the file, making corrections, and putting it back.

UPDATE students 
SET email = 'john.newaddress@email.com' 
WHERE student_id = 1;

Always use a WHERE clause with UPDATE unless you really want to change every single record. I once forgot the WHERE clause and accidentally updated every student's email to the same address. That was an embarrassing mistake that took hours to fix.

DELETE Command

DELETE removes specific records from your table.

DELETE FROM students 
WHERE student_id = 1;

Like UPDATE, always use a WHERE clause unless you want to delete everything. DELETE is different from TRUNCATE because DELETE can target specific records and is slower for removing large amounts of data.

Key Differences Between DDL and DML

Understanding when to use DDL versus DML has helped me avoid many mistakes over the years:

Purpose and Function

• DDL focuses on structure - creating and modifying tables, databases, and other objects

• DML focuses on content - working with the actual data inside those structures

When Changes Take Effect

• DDL commands usually take effect immediately and can't be undone easily

• DML commands can often be rolled back if you're using transactions

What They Affect

• DDL changes affect the database schema and structure

• DML changes affect the data stored within that structure

Think of DDL as renovating your house - you're changing the rooms, adding walls, or removing them. DML is like rearranging furniture and belongings within those rooms.

Practical Examples and Use Cases

Let me share a real scenario I worked on last year. We needed to create a system to track customer orders for a small online store.

First, we used DDL to create the structure:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
);

Then we used DML to work with the data:

-- Adding customers
INSERT INTO customers (customer_id, name, email)
VALUES (1, 'Alice Green', 'alice@email.com');

-- Adding orders
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES (101, 1, '2023-10-15', 49.99);

-- Finding orders over $30
SELECT * FROM orders WHERE total_amount > 30.00;

-- Updating a customer's email
UPDATE customers SET email = 'alice.green@newemail.com' WHERE customer_id = 1;

This combination of DDL and DML commands created a working system that the store could use right away.

Common Mistakes to Avoid

Based on my experience and watching others learn SQL, here are mistakes that happen frequently:

DDL Mistakes

• Not planning table structure carefully - It's harder to change structure later when data exists

• Using DROP carelessly - Always double-check before removing database objects

• Forgetting to set primary keys - This makes it harder to identify unique records later

DML Mistakes

• Forgetting WHERE clauses - This can update or delete more data than intended

• Not backing up data - Especially before running UPDATE or DELETE commands

• Using SELECT * in production - This can slow down your database and use unnecessary resources

I still make some of these mistakes occasionally, but being aware of them helps me catch them before they cause problems.

Best Practices for Beginners

Here are some habits that will save you time and trouble:

For DDL Operations

• Always test DDL commands on a copy of your database first

• Plan your table structure on paper before writing CREATE statements

• Use descriptive names for tables and columns

• Document your database structure as you build it

For DML Operations

• Start with SELECT statements to see your data before making changes

• Use transactions when making multiple related changes

• Keep your WHERE clauses simple and clear

• Test queries with small datasets first

General Tips

• Write your SQL commands in a text editor before running them

• Save your commonly used queries for future reference

• Practice regularly with sample databases

• Don't be afraid to experiment - that's how you learn

Learning SQL has been one of the most valuable skills in my career. It opens up so many possibilities for working with data, and once you understand these basics, more advanced concepts become much easier to grasp. Take your time with DDL and DML concepts - they form the foundation for everything else you'll learn in SQL.