What are DML commands in SQL? Select, Insert, Update, Delete?

DML - stands for Data Manipulation Language, its the part of SQL that deals with querying, updating, deleting and inseting records in tables, views.

The following types of actions may be performed using DML commands:

1) Select - This command is used to fetch a result set of records from a table, view or a group of tables, views by making use of SQL joins.

Retrieval of data using SQL statements can be done by using different predicates along with it like
Where
Group By
Having
Order By

-- The simplest example of a select statement where in a user wants to
-- retrieve all the records of a table, can be performed by using '*'
-- Use an asterisk character to retrieve all records of a table
SELECT * FROM t_students

The Where clause is used with DML statements to check for a condition being met in row.

SELECT * FROM t_students where age > 12 and age < 16;

-- Another way
SELECT * FROM t_students where age between 12 and 16;

SELECT * FROM t_students where name like 'R%'
-- Query above uses the like predicate along with a wildcard
-- The result will retrieve all names starting with character 'R'

The Group By statement in SQL is used for aggregation, which means that the result that is returned is based on grouping of results based on a column aggregation.

SELECT Roll_No, SUM(Marks) FROM t_students
WHERE Class = 5
GROUP BY Roll_No

The Having statement in SQL makes sure that an SQL SELECT statement should only return rows where aggregate values match conditions that are stated.

SELECT student_id, SUM(Marks) FROM t_students
WHERE Admission_Date = '01-Apr-2009'
GROUP BY student_id
HAVING SUM(Marks) > 500

The Order By clause in SQL is used to set the sequence of the output in terms of being alphabetical, magnitude of size, order of date. It may accompanied by an 'asc' or 'desc' clause so as to specify whether the results are in ascending or descending order. Note: The results of a select query that does not use asc or desc is in ascending order, by default.

SELECT fname, lname FROM t_students ORDER BY fname ASC;

2) Insert - This command is used to add record(s) to a table. While inserting a record using the insert statement, the number of records being entered should match the columns of the table. In case the number of items being entered are less than the number of columns, in that case the field names also need to be specified along with the insert statement. See code example below:

Consider a table named t_employees with the following fields:
Emp_Id, FirstName, LastName, Height, Weight
The syntax to insert a record in this table will be:

INSERT INTO T_EMPLOYEES VALUES ('445','Amitabh','Bachan','6ft','85kg');

What if not all the items need to be insert? Do the following:

INSERT INTO T_EMPLOYEES (Emp_Id, FirstName, Height)
VALUES ('445','Amitabh','6ft');

Further, an Insert statement can also be used in combination with Select statement. What we can do is that the result of the Select statement may be used as the values to be inserted in a table. See code example below:

INSERT INTO T_EMPLOYEES
SELECT * FROM T_SOME_OTHER_EMPLOYEE_TABLE
WHERE FirstName IN ('Pappu', 'Ramu','Kallu','Gabbar');

You may even insert specific columns like below:

INSERT INTO T_EMPLOYEES (Emp_Id, FirstName)
SELECT Emp_Id, FirstName FROM T_SOME_OTHER_EMPLOYEE_TABLE
WHERE FirstName IN ('Mogambo', 'Dr. Dang','Shakaal','Gabbar','Ajgar Jurraat','Bhaktawar','Bad Man', 'Prem','Billa Jilani');

3) Update - This command is used to edit the record(s) of a table. It may be used to update a single row based on a condition, all rows, or a set of rows based on a condition.

It is used along with the set clause. Optionally, a where clause may be used to match conditions. See code example below:

UPDATE TABLE T_EMPLOYEES SET FIRSTNAME = 'Anthony'
WHERE EMP_ID = '445';

More examples below:

Update the value of a column

UPDATE TABLE T_EMPLOYEES SET AGE = AGE + 1;

Update multiple columns in one statement

UPDATE TABLE T_SALARY SET
BONUS = BONUS + 10000,
BASIC = BASIC + (0.2 * BONUS);

4) Delete - This command is used to remove record(s) from a table. All records may be removed in one go, or a set of records may be deleted based on a condition. See code example below:

DELETE FROM T_VILLAINS WHERE FIRSTNAME = 'Pappu';

Code below deletes record(s) based on a condition

DELETE FROM T_VILLAINS WHERE AGE < 18 ;


Delete may also be done based on the result of a sub query:

DELETE FROM T_VILLAINS WHERE AGE IN
(SELECT AGE FROM T_VILLAINS WHERE AGE < 18)