Data Manipulation Language

Data Manipulation Language commands are used to manipulate the data in a table.
INSERT
UPDATE
DELETE
MERGE(introduced in oracle 9i)

INSERT: Insert is used to insert data into in a table.

Method1:
Syntax: insert into table_name values(value1, value2,…..);
Examle:
create table pinnapa(tech_name varchar2(30), author_name varchar2(50));

insert into pinnapa values(‘Oracle’,’David’);
insert into pinnapa values(‘apps’,’Michael’);

Method2: Inserting values like DOS prompt for each column
Syntax: insert into table_name values(&column1, &column2);

Example:

insert into pinnapa values(‘&tech_name’, ‘&author_name’)
Enter value for tech_name: SQL Server
Enter value for author_name: Gates

Method2: If we want to skip columns use below syntax.
Syntax: insert itno table_name(col1, clo3, …) values(value1, value3,….);

Example:

insert into pinnapa(tech_name) values(‘Bali’);

UPDATE: It is used to modify the data in a table.

Syntax:
UPDATE table_name set column_name=new value
where condition;

Example:
insert into pinnapa values(‘Pyton’,’Bali’)

UPDATE pinnapa set tech_name=’Python’
where author_name=’Bali’

DELETE: Delete is used to delete particular rows or all rows from a table.

Syntax: DELETE from table_name where condition;

Example:
To delete particular records

delete from pinnapa where tech_name=’Python’;

To delete all records

delete from pinnapa;

To get the data back: rollback;

Differences between Delete and Truncate:

Delete: Whenever we are using delete from table_name deleted data temporarily stored in buffer. We can get it back using rollback.

Truncate: Whenever we are using truncate table table_name, total data permanently deleted. Because truncate is an DDL command and also by default all DDL commands are automatically committed. Because of this , we cannot get it back this deleted data.

Merge:
Merge statement used to transfer data from source table to target table If table structures are same.
Merge statement used in data warehousing applications.
We can use INSERT and UPDATE commands in Merge statements.
Whenever we create merge statement, we must create alias name for the tables.
Syntax:
MERGE INTO target_table_name
using Source_table_name
on (join condition)
when matched then
Update set traget_table_name values(sourc_table_column_names);

Powered by k2schools