Data Manipulation Commands in DBMS

Data manipulation commands are used to manipulate data in the database.

Some of the Data Manipulation Commands are-

Select

Select statement retrieves the data from database according to the constraints specifies alongside.

SELECT <COLUMN NAME>
FROM <TABLE NAME>
WHERE <CONDITION>
GROUP BY <COLUMN LIST>
HAVING <CRITERIA FOR FUNCTION RESULTS>
ORDER BY <COLUMN LIST>

General syntax −

Example: select * from employee where e_id>100;

Insert

Insert statement is used to insert data into database tables.

General Syntax − 

INSERT INTO <TABLE NAME> (<COLUMNS TO INSERT>) VALUES (<VALUES TO INSERT>)

Example: insert into Employee (name, dept_id) values (‘ABC’, 3);

Update

The update command updates existing data within a table.

General syntax −

UPDATE <TABLE NAME>
SET <COLUMN NAME> = <UPDATED COLUMN VALUE>,
<COLUMN NAME> = <UPDATED COLUMN VALUE>,
<COLUMN NAME> = <UPDATED COLUMN VALUE>,…
WHERE <CONDITION>

Example: update Employee set Name=’AMIT’ where E_id=5;

delete

Deletes records from the database table according to the given constraints.

General Syntax −

DELETE FROM <TABLE NAME>
WHERE <CONDITION>

Example −

delete from Employee where e_id=5;

To delete all records from the table −

Delete * from <TABLE NAME>;

Merge

Use the MERGE statement to select rows from one table for update or insertion into another table. The decision whether to update or insert into the target table is based on a condition in the ON clause. It is also known as UPSERT i.e. combination of UPDATE and INSERT.

General Syntax (SQL) −

MERGE <TARGET TABLE> [AS TARGET]
USING <SOURCE TABLE> [AS SOURCE]
ON <SEARCH CONDITION>
[WHEN MATCHED
THEN <MERGE MATCHED  > ]
[WHEN NOT MATCHED [BY TARGET]
THEN < MERGE NOT MATCHED >]
[WHEN NOT MATCHED BY SOURCE
THEN <MERGE MATCHED  >];

General Syntax (Oracle)

MERGE INTO <TARGET TABLE>
USING <SOURCE TABLE>
ON <SEARCH CONDITION>
[WHEN MATCHED
THEN <MERGE MATCHED > ]
[WHEN NOT MATCHED
THEN < MERGE NOT MATCHED > ];