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
JOIN in SQL
Wildcard Operators in SQL
Contents
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
Example
Insert into tblUser
Values (2,'Tito',NULL,'Manger');
Insert into tblUser(empno,job, name)
Values (3,'Mito', 'ITA');
NOTE:
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
Values(&empno,'&name',&salary,'&job');
Syntax for Update Record by Data Manipulated Language
Update<T_name>
Set col_name=<value>[col_name=value, …..];
[where <condition>];
Example-
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’;
Note:
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
