DML (Data Manipulation Language) Commands in SQL

          Data Manipulation Language (DML)

DML stands for Data Manipulation Language. It is used to Retrieve, Store, Modify, Delete, Insert and Update data in database.

INSERT

SELECT

DELETE

UPDATE

INSERT – INSERT query is use to add records/data to a table.

Syntax:

INSERT INTO <tbl_Name> (Col1, Col2, Col3) VALUES (Val1, Val2, Val3)

Example:

INSERT INTO tbl_Employees (EmpID, EmpName, EmpDept) VALUES (1,'Sekhar','MIS')

INSERT INTO tbl_Employees (EmpID, EmpName, EmpDept) VALUES (2,'Raj','Admin')

INSERT INTO tbl_Employees (EmpID, EmpName, EmpDept) VALUES (3,'Naga','Accounts')

INSERT INTO tbl_Employees (EmpID, EmpName, EmpDept) VALUES (4,'Ramesh','MIS')

INSERT INTO tbl_Employees (EmpID, EmpName, EmpDept) VALUES (5,'Sudha','Operations')

To insert more than one rows in single attempt.

INSERT INTO tbl_Employees (EmpID, EmpName, EmpDept) VALUES (6,'Mahesh','Accounts'), (7,'Jagan','HK'), (8,'Bhanu','Transactions')

***************************************************************************

***************************************************************************

SELECT– Retrieves data from a table

SELECT Command is used to retrieve data from one or more tables which are having relation. While retrieving data we can make changes (Manipulate) on the output. So, it’s placed in DML group.

Syntax:

SELECT Col1, Col2, Col3 FROM <tbl_Name>

     Example:

SELECT EmpID, EmpName FROM tbl_Employees.

The above example is to retrieving mentioned columns and all rows from a table.

Output:

EmpID	EmpName
1	Sekhar
2	Raj
3	Naga
4	Ramesh

To retrieve all columns and all rows information

SELECT * FROM tbl_Employees

Output:

EmpID	EmpName	EmpDept
1	Sekhar	MIS
2	Raj	Admin
3	Naga	Accounts
4	Ramesh	MIS

‘*’ represents all column names from the table.

To get few rows information which we required, we need to add WHERE clause to the query.

Syntax:

SELECT Col1, Col2, Col3 FROM <tbl_Name> WHERE < Col_Nme> = <Parameter>

Let us suppose we need the information of the person who is having EmpID = 3

SELECT EmpID, EmpName, EmpDept FROM tbl_Employees WHERE EmpID = 3   

Output:

EmpID	EmpName	EmpDept
3	Naga	Accounts

Different  WHERE Clauses

SELECT EmpID, EmpName, EmpDept FROM tbl_Employees WHERE EmpID >= 2

To get the List of the employees who are having employees ID >=2

Output:

EmpID	EmpName	EmpDept
2	Raj	Admin
3	Naga	Accounts
4	Ramesh	MIS

SELECT EmpID, EmpName, EmpDept FROM tbl_Employees WHERE EmpDept = 'MIS'

                           (Or)

SELECT EmpID, EmpName, EmpDept FROM tbl_Employees WHERE EmpDept like 'MIS'

To get the list of employees who are working in MIS Dept.

Output:

EmpID	EmpName	EmpDept
1	Sekhar	MIS
4	Ramesh	MIS

Like key word is used to get the list of records which are matching with the mentioned characters.

SELECT EmpID, EmpName, EmpDept FROM tbl_Employees WHERE EmpDept like '%MI%'

Output:

EmpID	EmpName	EmpDept
1	Sekhar	MIS
2	Raj	Admin
4	Ramesh	MIS

 In the above example, I want to get the information of employees who are having characters ‘MI’ in their department column.

SELECT * FROM tbl_Employees WHERE  EmpDept in ('MIS','Accounts')

Fetch the records from the tbl_Employees which are having 'MIS','Accounts'

as "EmpDept" 

SELECT * FROM tbl_Employees WHERE EmpID BETWEEN 4 AND 8 

Fetch the records from the tbl_Employees whose employee IDS in between 4 AND 8.

Like this we can write different WHERE clauses based on our needs.

***************************************************************************

***************************************************************************

UPDATE – UPDATE command is used to make changes in the existing data. After insert some data, if we want to make some changes in inserted data UPDAT command useful to us.

Syntax:

UPDATE <tbl_Name> SET <Col_Name> = <Value>

Example:

UPDATE tbl_Employees SET EmpDept = 'Sales'

If you run the above query, all cells in EmpDept column values set to 'Sales'.

If you want to changes values in particular rows only, then we need to add WHERE clause.

Syntax:

UPDATE <tbl_Name> SET <Col_Name> = <Value> WHERE <Col_Name> = <Value>

Example:

UPDATE tbl_Employees SET EmpDept = 'SALES' WHERE EmpID = 6

***************************************************************************

***************************************************************************

DELETE – DELETE command is used to delete some or all rows from a particular table.

Syntax:

DELETE FROM <tbl_Name>

Example:

DELETE FROM tbl_Employees

The above example deletes all rows from Employees table.

To delete some particular rows only

Syntax:

DELETE FROM <tbl_Name> WHERE <Col_Name> = <Value>

Example:

              DELETE FROM tbl_Employees WHERE EmpDept = 'MIS'