Categories: Business Analytics

DML Commands in SQL: A Beginner’s Guide In 6 Easy Points (2022)

Introduction

In the 21st century, data is the new oil. Every application, software, device, and equipment has some form of data or information that humans can optimize to make it favorable for them. Businesses now understand the importance of data collection and storage and leveraging them to aid their decision-making process.Databases, i.e., information stored digitally in rows and columns, are fundamental to an  IT organization. The handling and maintenance of these databases require a steep learning curve for the best results. Therefore, understanding data manipulation commands (DML Commands) is of utmost importance.

Many Relational Database Management Systems (RDBMS) are used to manage databases, such as MS Access, Oracle, Postgres, MySQL, SQLite, SQL Server, etc. Structure Query Language, also known as SQL, is one of the most popular database languages. 

Here, in this article, we’ll look at Commands of SQL and its syntax to define the different aspects of various Relational Database Management System (RDBMS).

Types of Commands in SQL

The main types of commands present in SQL are:

  1. Data Definition Language (DDL)
  2. Data Manipulation Language (DML)
  3. Data Control Language (DCL)
  4. Transaction Control Language (TCL)
  5. Data Query Language (DQL)

Take a look at some of the commands of SQL queries in this picture:

Figure: SQL Commands source

 

1) DDL

DDL is an abbreviation for Data Definition Language. DDL is used to define the schema or the structure of a database. Here are the main DDL commands of SQL with their syntax.

  • CREATE

The Create command is used to build new tables, views, and databases in DBMS.

Syntax:

Create table table_name (Datatypes, Column_name[,…]);

Example:

Create table mines;Create view for_learn;Create database sqltutorial;

 

  • DROP

Executing the Drop command in DDL can remove databases and tables from the RDBMS.

Syntax:

DROP table;

For example:

Drop table mines;Drop database;Drop object_type object_name;Drop view for_learn;

 

  • ALTER

The Alter command in DDL can change or modify the database structure.

Syntax:

To add a new row in the table,

ALTER table table_name ADD row_name row-definition;

And to make a change to an existing row in a given database,

ALTER Table Modify (Row Definition …..);

For example:

ALTER table mines add subject varchar;

 

  • TRUNCATE

When executed, the Truncate command in DDL can remove or delete all rows from a specific table and clear or free the table, respectively.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE table mines;

 

2) DML

DML is an abbreviation for Data Manipulation Language. 

Data Manipulation Language or DML represents a collection of programming languages explicitly used to make changes in the database, such as:

  1. CRUD operations to create, read, update, and delete data
  2. Using the INSERT, SELECT, UPDATE and Delete commands.

DML commands are often part of a more extensive database language, for instance, SQL (Structure Query Language). These DML commands may have a specific syntax to manage data in that language. 

DML Commands provide a way to read, update, delete, or merge data precisely. In the beginning, DML commands were part of computer programs only, but with the popularity of SQL, they have now become a part of database management.

Data Manipulation Languages (DML) have two primary classifications: Procedural and Non-procedural programming (declarative programming).

List of DML Commands in SQL

Here is a shortlist of all DML commands and their specific functions in the SQL programming language.

  1. SELECT: Command to fetch data or values from the database
  2. INSERT: Command to add new or fresh value to the database
  3. UPDATE: Command to change or update the present/existing data to a newer value inside the database
  4. DELETE: Command to remove or delete the values or data information from the database’s current table
  5. MERGE: Command to merge two or more data tables inside a database.

The Syntax for DML Commands

  • INSERT

The INSERT query command in SQL provides a way to add new rows of information or data inside a specific database of the RDBMS. INSERT can be executed using two syntaxes:

Syntax:

INSERT INTO TABLE_NAME (column1, column2, column3,…columnN)
VALUES (value1, value2, value3,…valueN);

Here ‘column’ represents the table column’s specific names for inserting data in the desired way.

You may avoid the column name and add the values as defined in the column previously.

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,…valueN);

Example:

Here is an example of adding five records in the customer database table:

INSERT INTO CUSTOMERS (ID,NAME,AGE,CITY,COMPENSATION)VALUES (1, Kritesh, 45, ‘Delhi’, 2500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,CITY,COMPENSATION)VALUES (2, Mehta, 35,Kochi, 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,CITY,COMPENSATION)VALUES (3, Preet, 32, Delhi, 7000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,CITY,COMPENSATION)VALUES (4, Sonali, 52, ‘Kolkata’, 3500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,CITY,COMPENSATION)VALUES (5, Ritwik, 56, Noida, 8500.00 );

And using the second syntax, you can add the record as that too:

INSERT INTO CUSTOMERSVALUES (6, ‘Shubhra’, 45, ‘MP’, 4500.00 );

And all the above records will fetch the following result on checking the CUSTOMERS table as following: 

+—-+———-+—–+———–+———-+| ID | NAME | AGE | ADDRESS   | SALARY   |+—-+———-+—–+———–+———-+|  1 | Kritesh   |  45 | Delhi   |  2500.00 ||  2 | Mehta     |  35 | Kochi  |  1500.00 ||  3 | Preet    |  32 | Delhi |  7000.00 ||  4 | Sonali    |  52 | Kolkata |  3500.00 ||  5 | Ritwik    |  56 | Noida      |  8500.00 ||  6 | Shubhra   |  45 | MP    |  4500.00 |+—-+———-+—–+———–+———-+

 

  • UPDATE

The Update command provides a way to make changes/update or modify the values present in a table’s column.

Syntax:

UPDATE table_nameSET column1 = value1, column2 = value2…., columnN = valueNWHERE [condition];

You can add more conditions using OR or AND operators to make multiple changes using a single query.

Let’s consider the above example.

+—-+———-+—–+———–+———-+| ID | NAME | AGE | ADDRESS   | SALARY   |+—-+———-+—–+———–+———-+|  1 | Kritesh   |  45 | Delhi   |  2500.00 ||  2 | Mehta     |  35 | Kochi  |  1500.00 ||  3 | Preet    |  32 | Delhi |  7000.00 ||  4 | Sonali    |  52 | Kolkata |  3500.00 ||  5 | Ritwik    |  56 | Noida      |  8500.00 ||  6 | Shubhra   |  45 | MP    |  4500.00 |+—-+———-+—–+———–+———-+

Now you can update the address of the 5th customer with the following UPDATE Query.

SQL> UPDATE CUSTOMERSSET ADDRESS = ‘Indore’WHERE ID = 5;

On checking, the customer records will fetch the following result:

+—-+———-+—–+———–+———-+| ID | NAME | AGE | ADDRESS   | SALARY   |+—-+———-+—–+———–+———-+|  1 | Kritesh   |  45 | Delhi   |  2500.00 ||  2 | Mehta     |  35 | Kochi  |  1500.00 ||  3 | Preet    |  32 | Delhi |  7000.00 ||  4 | Sonali    |  52 | Kolkata |  3500.00 ||  5 | Ritwik    |  56 | Indore     |  8500.00 ||  6 | Shubhra   |  45 | MP    |  4500.00 |+—-+———-+—–+———–+———-+

And in case you are looking to change the Salary and address of the whole customer database, you can do so with the help of the below-mentioned query:

SQL> UPDATE CUSTOMERSSET ADDRESS = ‘Indore’, SALARY = 3000.00;
+—-+———-+—–+———–+———-+| ID | NAME | AGE | ADDRESS   | SALARY   |+—-+———-+—–+———–+———-+|  1 | Kritesh   |  45 | Indore     |  3000.00 ||  2 | Mehta     |  35 | Indore     |  3000.00 ||  3 | Preet    |  32 | Indore    |  3000.00 ||  4 | Sonali    |  52 | Indore    |  3000.00 ||  5 | Ritwik    |  56 | Indore     |  3000.00 ||  6 | Shubhra   |  45 | MP    |  3000.00 |+—-+———-+—–+———–+———-+

 

  • DELETE

Delete command provides a way to delete a single column or multiple columns from a table’s specific row.

Syntax:

DELETE FROM table_name
[WHERE condition];

You can use a combination of different operators to get more specific or precise results.

Example:

+—-+———-+—–+———–+———-+| ID | NAME | AGE | ADDRESS   | SALARY   |+—-+———-+—–+———–+———-+|  1 | Kritesh   |  45 | Delhi   |  2500.00 ||  2 | Mehta     |  35 | Kochi  |  1500.00 ||  3 | Preet    |  32 | Delhi |  7000.00 ||  4 | Sonali    |  52 | Kolkata |  3500.00 ||  5 | Ritwik    |  56 | Indore     |  8500.00 ||  6 | Shubhra   |  45 | MP    |  4500.00 |+—-+———-+—–+———–+———-+

And the DELETE query for ID 3 would then be:

SQL> DELETE FROM CUSTOMERSWHERE ID = 3;

Now the database will look something like this:

+—-+———-+—–+———–+———-+| ID | NAME | AGE | ADDRESS   | SALARY   |+—-+———-+—–+———–+———-+|  1 | Kritesh   |  45 | Delhi   |  2500.00 ||  2 | Mehta     |  35 | Kochi  |  1500.00 ||  4 | Sonali    |  52 | Kolkata |  3500.00 ||  5 | Ritwik    |  56 | Indore     |  8500.00 ||  6 | Shubhra   |  45 | MP    |  4500.00 |+—-+———-+—–+———–+———-+

You can also the use following command to DELETE all customers from a given database:

SQL> DELETE FROM CUSTOMERS;

 

This will delete all records of the customers from a specific database, respectively.

DML Statements and Transactions

DML Statements:

  • Database table data can be added, changed, or deleted using Data Manipulation Language (DML) statements.
  • DML Statements access the data and process/change the existing tables.
  • In the SQL environment, DML statements are entered after the SQL> prompt
  • DML statements are entered in the Worksheet in the SQL Developer environment. To access and manipulate data, the SQL Developer Connections frame and tools can be used.
  • The effect of a DML statement is not permanent until the transaction that includes it is committed.

 

Transaction control statements

  • A transaction is a set of one or multiple SQL statements that the DBMS treats as one unit (single command): either all of the statements are executed or none of them are.
  • Transactions are required when writing code for business processes that require multiple operations to be performed as a unit simultaneously.

For example, when a team leader (TL) quits the company, a row has to be inserted into the JOB_ HISTORY table to show when the team leader left, and the value of TL_ID in the has to be updated against each of his team members in the EMPLOYEES table. To execute this process in a business application, the ‘INSERT’ and ‘UPDATE’ DML commands must be combined into a single transaction.

 

3) DCL

Data Control Language (DCL) provides commands to add more rights and permissions to different aspects of database parameters inside an RDBMS. Grant and Revoke are part of the DCL command in SQL.

  • Grant

The Grant command adds access privilege to a specific database.

Syntax:

GRANT SELECT, UPDATE ON TABLE_NAME to USER, SPECIFIC_USER;

Example:

GRANT SELECT ON Users to Sam@admin;

 

  • Revoke

The Revoke command provides a way to remove specific permissions from the given user.

Syntax:

REVOKE privilege_nameON object_nameFROM {user_name |PUBLIC |role_name}

Example:

REVOKE SELECT, UPDATE ON student FROM BBA, MBA;

 

4) TCL

Transaction Control Language or TCL represents the transactions in query handling in RDBMS. Commit, Rollback, and SAVEPOINT are the three main TCL commands.

  • Commit

The Commit command saves all the transactions to a specific database.

Syntax:

Commit;

Example:

DELETE FROM StudentsWHERE AdmissionNo = 35;Commit;

 

  • Rollback

The rollback command allows you to return or undo any transaction that is not present in the database. 

Syntax:

ROLLBACK;

Example:

DELETE FROM StudentsWHERE AdmissionNo = 35;ROLLBACK;

 

  • SAVEPOINT

Here is the syntax for SAVEPOINT to specify and set a savepoint in the transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

 Example:

SAVEPOINT AdmissionNo;

 

5) DQL

DQL, also known as Data Query Language (DQL), comprises the main commands used to fetch information or data from a database. 

SELECT is the primary fundamental query command used with FROM and WHERE to give direction to the commands.

  1. SELECT: Command to choose/select an attribute from its condition defined by the WHERE condition.
  1. FROM: Defines the relation for taking input or values for selection in the query.
  1. WHERE: Defines the conditions to get specific results.

For instance:

SELECT writer_name

FROM book_writer

WHERE age > 60;

This query or command will fetch the list of writers’ names who have written books and are aged more than 60.

Difference Between DDL and DML Commands

Here are the main differences between DDL and DML commands in RDBMS:

DDL Commands DML Commands
Full-Form Data Definition Language Data Manipulation Language
Main Purpose DDL commands are mainly used to create new databases, users, constraints, tables, constraints, etc. The primary purpose of DML commands is to select, insert, deleting, update, and merge data records in RDBMS.
Further Classification None. DML is further classified into two types:Procedural DML ,Non-procedural DML
Commands Example CREATE, ALTER, DROP, and RENAME SELECT, INSERT, UPDATE, DELETE, and MERGE

 

Conclusion

SQL is one of the primary database management languages. Commands of SQL have different types and syntax that allow you to manage data precisely and deliver optimum results.

There are five different types of commands within DDL, DML, DCL, TCL, and DQL. Each type has a specific function and role to perform in the programming language. 

With both public and private organizations now depending on data to run their operations, data management has become one of the most crucial tools in today’s times. With continued improvisation in technologies, storage, and IT solutions, data manipulation will remain an area of much interest for younger generations.

Software is designed mainly to make life easy. The database is an integral part of the software.  Any user interface that collects data from the user is stored in a database. This database is used for further reference. We have all received customer calls if our accounts have not been active for some time. The customer care officials take the data from the database from the column ‘last active’ in the table and call the customers. 

It is also important to find the right place to learn and become proficient in all these skills and languages. Jigsaw Academy, recognized as one of the Top 10 Data Science Institutes in India, is the right place for you. Jigsaw Academy offers an Integrated Program In Business Analytics for enthusiasts in this field. The course runs for 10 months and is conducted live online. Learners are offered a joint certificate by the Indian Institute of Management, Indore, and Jigsaw Academy.

Also Read

Ajay Sarangam

Published by
Ajay Sarangam

Recent Posts

Books on Analytics

Analytics is a vast field. At the one end, it overlaps with statistics and higher…

Career in analytics in a KPO

Do you love to explore and investigate information? Do you find spreadsheets to be a…

Indian companies using analytics

India has developed into the global hub for analytics. A large number of MNCs have…

IBM: Betting big on analytics

International Business Machines Corp. Or IBM as it is popularly known recently announced its restructuring…

How to build a successful career in analytics

So you have got a job as an analyst in your dream company? Here are…

What’s the sentiment on “sentiment analysis”?

What's the sentiment on "sentiment analysis"? Is the field ready to take off?