DDL DML in SQL explain with example

DDL DML in SQL: In SQL (Structured Query Language), DDL (Data Definition Language) and DML (Data Manipulation Language) are two essential subsets that serve different purposes in managing and interacting with databases.

Data Definition Language (DDL in SQL)

DDL is used to define and manage the structure of the database, including creating, altering, and deleting tables and other database objects.

Common DDL Commands:

• CREATE: Creates a new table, database, index, or other database objects.
• ALTER: Modifies an existing database object, such as a table.
• DROP: Deletes an existing table, database, or other objects.
• TRUNCATE: Removes all records from a table, but the structure remains.

Example:

Let’s create a table named Employees with columns for ID, Name, and Position.

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Position VARCHAR(50)
);

This command defines a new table called Employees with three columns: ID, Name, and Position.

Data Manipulation Language (DML in SQL)

DML is used to manipulate the data within the database. It allows you to perform operations like inserting, updating, deleting, and retrieving data from the tables.

• INSERT: Adds new rows of data to a table.
• UPDATE: Modifies existing data within a table.
• DELETE: Removes rows of data from a table.
• SELECT: Retrieves data from one or more tables.

Example:

Let’s insert a new employee into the Employees table and then retrieve the data.

-- Insert a new record into the Employees table
INSERT INTO Employees (ID, Name, Position)
VALUES (1, 'John Doe', 'Manager');

-- Retrieve all records from the Employees table
SELECT * FROM Employees;

The INSERT command adds a new row with the employee’s ID, name, and position. The SELECT command retrieves the data from the table.

Summary:

• DDL deals with the structure and schema of the database (e.g., creating tables).
• DML deals with the actual data within the tables (e.g., inserting, updating records).

These two types of SQL commands are fundamental in managing and manipulating databases.