DDL vs DML in SQL

Introduction
When working with SQL databases, commands are broadly categorized into DDL (Data Definition Language) and DML (Data Manipulation Language). Understanding the difference between these two categories is crucial for managing and interacting with databases effectively.

What is DDL (Data Definition Language)?
DDL is responsible for defining and modifying the structure of database objects like tables, schemas, indexes, and constraints. DDL commands affect the database schema and are typically executed automatically without requiring explicit transactions.

DDL Commands:
1- CREATE - Used to create new tables, schemas, or other database objects.
2- ALTER - Modifies an existing database object (e.g., adding a new column to a table).
3- DROP - Deletes a database object permanently (e.g., removing a table from the database).

-- Creating a new table
CREATE TABLE Employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(50)
);

-- Adding a new column to an existing table
ALTER TABLE Employees ADD salary DECIMAL(10,2);

-- Dropping a table
DROP TABLE Employees;

What is DML (Data Manipulation Language)?
DML is used to manage data stored in database tables. Unlike DDL, DML commands interact with the actual data rather than the structure of the database.

DML Commands:
1- INSERT - Adds new records to a table.
2- UPDATE - Modifies existing records in a table.
3- DELETE - Removes specific records from a table.
4- SELECT - Retrieves data from tables (though often categorized separately as DQL - Data Query Language).

-- Inserting a new employee record
INSERT INTO Employees (id, name, age, department, salary)
VALUES (1, 'John Doe', 30, 'IT', 60000.00);

-- Updating an employee's salary
UPDATE Employees SET salary = 65000.00 WHERE id = 1;

-- Deleting an employee record
DELETE FROM Employees WHERE id = 1;

Conclusion
Both DDL and DML are essential for database management. DDL helps define and modify database structure, while DML allows interaction with the stored data. Understanding these two categories will enhance your ability to design, maintain, and query databases effectively.
By mastering DDL and DML, you'll be able to create efficient database architectures and perform essential operations on data.