Difference between DDL and DML commands in SQL with examples

What are SQL commands?

SQL commands are just that: commands. It's used to send and receive data from the database. It may also be used to carry out particular activities, functions, and data searches.

SQL can build tables, add data to tables, drop tables, change tables, and define permissions for users, among other things.

There are different types of SQL commands as depicted in the picture below: 

 

Now, let's understand each one of them separately in depth. We will be discussing the DDL and DML commands as they are the most widely used commands.

1. DML

Although DML instructions are not auto-committed, they allow you to manipulate the data saved in the database. Furthermore, they aren't long-term. As a result, the operation can be rolled back. DML stands for Data Manipulation Language in its entire form.

Here are a few useful DML commands:

• INSERT
• UPDATE
• DELETE

INSERT: This is a SQL query in the form of a statement. This command is used to populate a table row with data.

    Syntax - 

INSERT INTO TABLE_NAME  (col1, col2,.... col N)  
VALUES (value1, value2, .... valueN);  
Or 
INSERT INTO TABLE_NAME    
VALUES (value1, value2, .... valueN);    

    Example - 

    Let's suppose we have an Employee table. to Insert values into the table, we would use an insert statement.

INSERT INTO Employee(name, salary) VALUES('Virat',100);

UPDATE: This command is used to change or update the value of a table column.

    Syntax - 

    UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]  

    Example - Continuing the previous example, 

    UPDATE Employee SET name = 'Rohit' WHERE salary = 100;

DELETE: To delete one or more rows from a table, use this command.

    Syntax - 

    DELETE FROM table_name [WHERE condition];

    Example - Continuing the previous example, 

DELETE FROM Empoyee WHERE name = 'Rohit' and salary = 100;

Importance of DML

The following are some of the advantages and disadvantages of DML:

• DML statements allow you to make changes to data in a database.
• The data that is required might be specified by the user.
• DML comes in a variety of flavors and capabilities depending on the database provider.
• It allows for effective human-computer interaction.

2. DDL

The Data Definition Language aids in the creation of a database structure or schema. DDL instructions aid in the creation of database schema and other database objects. Its instructions are auto-committed, which means that the modifications are permanently preserved in the database. DDL stands for Data Definition Language in its entire form.

Here are a few DDL commands with their explanation

• CREATE
• DROP
• ALTER
• TRUNCATE

CREATE: The database structure schema is defined using CREATE statements:

    Syntax - 

    

    CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]); 

    Example - 

    CREATE TABLE Employee (ID INT(50) PRIMARY KEY

                           ,NAME VARCHAR(80)
                           ,SALARY INT(90));

DROP: Drops commands are used to delete tables and databases from a relational database management system (RDBMS).

    Syntax - 

DROP TABLE table_name;

    Example - 

    DROP TABLE Employee;

ALTER: The Alters command allows you to change the database's structure.

    Syntax - 

    ALTER TABLE table_name ADD column_name COLUMN-definition;
 

    Example - 

    ALTER TABLE Employee ADD retirement Date;

TRUNCATE: This command was used to remove all of the rows from the table and free up the space where the table was stored.

    Syntax - 

    TRUNCATE TABLE table_name; 

    Example - 

    TRUNCATE TABLE Employee;

Importance of DDL

The following are some of the benefits of adopting the DDL method:

• Allows you to save data that is shared with others.
• Integrity was increased via data independence.
• Multiple users are permitted.
• Improved data security and accessibility

Now as we have already discussed the DDL and DML commands, let's compare them both and note down the differences. Can you guys do this exercise first?! Look down t find the answer but try this out first. Note all the missed out points and also do comment for any point that is missed out :p

DDL vs DML

The primary distinction between DDL and DML instructions is as follows:

• Statements in the Data Definition Language (DDL) explain the structure of a database or schema. Data Manipulation Language (DML) commands, on the other hand, allow you to change data in the database that already exists.
• The DDL instructions are used to create the database or schema, whereas the DML commands are used to populate and change it.
• DDL instructions have the ability to affect the whole database or table, whereas DML statements only affect single or multiple rows dependent on the query condition.
• Modifications to DDL instructions are permanent and cannot be undone because they are auto-committed. DML statements, on the other hand, are not auto-committed, meaning that changes are temporary and may be undone.
• DML is a declarative method, whereas DDL is an imperative and procedural method.
• WHERE clauses can be used to filter data in DML statements, however, they cannot be used to filter records in DDL statements.

The following comparison table quickly highlights their primary differences:

Conclusion

We compared and contrasted DDL and DML commands in this post. We've arrived at the conclusion that both languages are required to create and access a database. When working with a large database, be sure the condition is appropriately stated because it has the potential to destroy the whole database or table.

Other SQL Articles and Tutorials you may like

• How to find Nth Highest salary in SQL
• How to remove duplicate rows in SQL
• How to use row_number function in SQL

Thanks a lot for reading this article so far. If you liked this article then please share with your friends and colleagues.