DDL vs DML

DDL and DML are two fundamental concepts in the field of relational database management systems (RDBMS).

DDL

DDL, or Data Definition Language, is a subset of SQL (Structured Query Language) that is used to define the structure of a relational database. It consists of a set of commands that are used to create, modify, and delete database objects such as tables, indexes, and relationships. The purpose of DDL is to define the schema of a database, which is the blueprint that describes the structure of the data stored in the database.

DDL commands are used to create tables, define the structure of the tables including columns, data types, and constraints, as well as modify and delete existing tables. The schema of a database defines the tables, columns, data types, constraints, and relationships between tables, and is used by the database management system (DBMS) to enforce data integrity and consistency.

Here are some common DDL commands:

• CREATE TABLE: Used to create a new table in the database. For example:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  salary DECIMAL(10, 2),
  hire_date DATE
);

• ALTER TABLE: Used to modify an existing table in the database. For example:

ALTER TABLE employees
ADD COLUMN department VARCHAR(255);

• DROP TABLE: Used to delete a table from the database. For example:

DROP TABLE employees;

• CREATE INDEX: Used to create an index on one or more columns of a table to improve the speed of data retrieval. For example:

CREATE INDEX idx_employees_name
ON employees (name);

• ALTER INDEX: Used to make changes to an existing index. For example:

ALTER INDEX idx_employees_name RENAME TO new_idx_employees_name;

• DROP INDEX: Used to delete an index from a table. For example:

DROP INDEX new_idx_employees_name;

DDL commands are typically executed by database administrators and are used to create a foundation for the database that will be used to store data. These commands are executed once at the beginning of a database’s lifecycle, and the resulting schema is used to enforce data integrity and consistency throughout the life of the database.

DML

DML, or Data Manipulation Language, is used to manipulate data stored in a relational database. It consists of a set of commands that are used to insert, update, and delete data in a database. The purpose of DML is to manipulate the data stored in a database, allowing you to insert new data, modify existing data, and delete data that is no longer needed.

DML commands are used to insert new data into tables, modify existing data, and delete data that is no longer needed. They allow you to perform CRUD (Create, Read, Update, Delete) operations on a database.

Here are some common DML commands:

• SELECT: Used to retrieve data from a table. For example:

SELECT * FROM employees;

• INSERT: Used to insert new data into a table. For example:

INSERT INTO employees (id, name, salary, hire_date)
VALUES (1, 'John Doe', 50000, '2022-01-01');

• UPDATE: Used to modify existing data in a table. For example:

UPDATE employees
SET salary = 55000
WHERE name = 'John Doe';

• DELETE: Used to delete data from a table. For example:

DELETE FROM employees
WHERE name = 'John Doe';

DML commands are typically executed by end-users and application programs to manipulate the data stored in a database. They are used to insert new data, modify existing data, and delete data that is no longer needed. The results of DML commands are immediately visible to other users of the database, and the changes persist until they are explicitly undone or the data is deleted.

Comparison of DDL and DML in a table format: