What are differences between DDL DML DCL and DQL

Differences between DDL, DML, DCL, and DQL

1. Full Name: 

DDL stands for Data Definition Language.

DML stands for Data Manipulation Language.

DCL stands for Data Control Language.

DQL stands for Data Query Language.

2. Purpose:

Use Data Definition Language to specify the structure of a database.

Use Data Manipulation Language to manipulate the data which are stored in the database.

Use Data Control Language to control access within the database.

Use Data Query Language to retrieve data from the database.

3. Type of operation:

To create and modify the database schema, we use DDL statements.

To insert, update, and delete data, we use DML statement.

To grant or revoke privileges to users, we use DCL statement.

To retrieve data from the database, we use DQL statement.

4. Examples of commands:

Example of DDL like CREATE, ALTER, and DROP commands.

    CREATE TABLE <TableName>

    (ID int Primary Key,

    Name Varchar(50),

    Address1 Varchar(50),

    Phone Varchar(10))

    

    ALTER TABLE <TableName> ADD Address2 VARCHAR(50) NOT NULL

Example of DML commands like INSERT, UPDATE, DELETE, and MERGE.

    INSERT INTO TableName(Name,Address1,Phone)

    VALUES('Sam','Delhi','9090909090')

Example of DCL commands like GRANT and REVOKE.

    GRANT SELECT ON <mytable> TO <myuser>

Example of DQL commands like SELECT.

    SELECT * FROM TableName WHERE ColumnName='value'

5. Changes made to the database:

To schema of the database, use DDL commands.

To change the data stored in the database, use DML commands.

To change the access permissions of the users, use DCL commands.

To retrieve data from the database, use DQL commands. DQL does change any data or schema.

6. Functionality:

To create tables, indexes, views, constraints, and other database objects, use DDL commands.

To insert, update, and delete data from the tables, use DML commands.

To grant or revoke privileges to users or roles, use DCL commands.

DQL is used to retrieve data from one or more tables.

7. Execution:

DDL commands are executed immediately.

DML commands are executed immediately.

DCL commands are executed immediately.

DQL commands are executed when the query is submitted.

8. Authorization:

DDL commands can only be executed by authorized users.

DML commands can only be executed by authorized users.

DCL commands can only be executed by authorized users.

DQL commands can be executed by any user with read access to the database.

9. Effects:

DDL commands can have a major impact on the database structure.

DML commands can change the data in the database.

DCL commands can change the access permissions for users or roles.

DQL commands do not change the database but retrieve data from it.