Can you explain the difference between DDL, DML, and DCL in SQL? — SQL & Databases Interview Question #5

DDL stands for Data Definition Language, and it is used to define and modify the structure of database objects such as tables, indexes, and constraints. DDL commands do not affect the data stored in the database. Common DDL commands include CREATE, ALTER, and DROP.

Here is an example of a DDL command to create a table:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(50),
  email VARCHAR(100),
  phone VARCHAR(20)
);

This statement creates a new table called customers with four columns and a primary key constraint.

DML stands for Data Manipulation Language, and it is used to manipulate data stored in the database. DML commands affect the data stored in the database but do not change the structure of database objects. Common DML commands include SELECT, INSERT, UPDATE, and DELETE.

Here is an example of a DML command to insert data into a table:

INSERT INTO customers (customer_id, customer_name, email, phone)
VALUES (1, 'John Smith', 'john@example.com', '555-1234');

This statement inserts a new record into the customers table with the specified values.

DCL stands for Data Control Language, and it is used to manage the access and permissions of database objects. DCL commands do not affect the data stored in the database or the structure of database objects. Common DCL commands include GRANT and REVOKE.

Here is an example of a DCL command to grant privileges to a user:

GRANT SELECT, INSERT, UPDATE ON customers TO user1;

This statement grants the user user1 the SELECT, INSERT, and UPDATE privileges on the customers table.

In summary, DDL is used to define and modify the structure of database objects, DML is used to manipulate data stored in the database, and DCL is used to manage access and permissions of database objects. By using these different types of commands, you can create, manipulate, and control data stored in the database.