Data Manipulated Language (DML) in SQL

Data Manipulated Language (DML), which is responsible to manipulated language, the records of table. A Data Manipulation Language (DML)  is a sublanguage of SQL responsible to insert, update, delete or manipulate database object’s records. DML provides the following statement for manipulating the database objects.

  • Insert
  • update
  • delete

Syntax for insert

Insert into [(col1, col2, col3,….N)
Values (value1, value2, value3……..N);

Above syntax is syntax of data insertion using column list. User has only remembered name of column not the sequence.

Better to read the previous topic to get good knowledge about SQL.
Data Definition Language (DDL) in SQL
Function in SQL
Wildcard Operators in SQL

Insert NULL value in a column

NULL value can be assigned in a particular column in two different manners.

  • Explicitly using NULL keyword
  • Implicitly using column list


Insert into tblUser
Values (2,'Tito',NULL,'Manger');

Insert into tblUser(empno,job, name)
Values (3,'Mito', 'ITA');


User is not allowed null value a column explicitly or implicitly if column explicitly, if column is protected with not null constraint.
Data insertion using substation variable.

Insert into tblUser

Syntax for Update Record by Data Manipulated Language

Set col_name=<value>[col_name=value, …..];
[where <condition>];


Update  tblUser
Set sal=1000, job= 'manger'
Where empno in (4,5);

Data Manipulated Language Important Note:

When where clause are omitted from update statement then it will update all value of specific column.

update tblUser
set sal = NULL

Syntax for Delete Records in SQL by DML operation

Delete from <t_name>
[Where <condition>];

If where clues are omitted from delete statement then all data are deleting from that table.

Copy table to another Table in Data Manipulated Language

Create table <new t_name>
As select col1,col2,…..coln from <old t_name>;

Whenever a table is copied only NOTNULL constraint are copied to the new table except other constraint. Copy only structure of table without data.

Create table newEmp
As select * from emp
Where 1=2;

Delete identical record from a table by Data Manipulated Language

Whenever ‘n’ number of column in a table is created in oracle database then ‘n+1’ column in a table is created, which is automatically and that column is known as ROWID. ROWID is a physical invisible column and their data are not equal by system.

Select rowid, ename, sal from emp;
Delete from emp where rowed like ‘%c’;


ROWNUM is another column provide by oracle, which is virtual column responsible to display the number of records using serial number .

Select rownum, ename from emp;

Reference Link
Data Manipulation Language in Wiki

Leave a Comment

Your email address will not be published. Required fields are marked *