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.

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 CommandsDML Commands
Full-FormData Definition LanguageData Manipulation Language
Main PurposeDDL 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 ClassificationNone.DML is further classified into two types:Procedural DML ,Non-procedural DML
Commands ExampleCREATE, ALTER, DROP, and RENAMESELECT, 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

SHARE