Relational Query Languages |Data Manipulation Language(DML)

DML (Data Manipulation Language)

The Data Manipulation Language (DML) is the subset of SQL used to add, update and delete data and select or retrieval.

COMMAND USED-INSERT, UPDATE, DELETE,SELECT

INSERT adds rows (formally tuples) to an existing table.

UPDATE modifies a set of existing table rows.

DELETE removes existing rows from a table.

SELECT Retrieve data from one or more table

1. INSERT COMMAND- This command is used when we want to insert a data in a new existing table.This syntax is used for inserting a single value in a each column of a table at a time.

SYNTAX: insert into <tname> (col1,col2,col3) values (‘v1’, ‘v2’ , ‘v3’);

E.G.: insert into hotel (ename , salary, place) values (‘Akash’, ‘10000’, ‘chd’);

When we want to insert a multiple values in each column of a existing table then we used a following syntax:

SYNTAX: insert into <tname> values(‘&1’, ‘&2’);

You have to enter the values  and inspite of writing this command again you can use only [(/) enter].

2.UPDATE COMMAND- This command is used when we want to update a data of existing table.

syntax: update <table name> set <condition1> where <condition2>;

E.g.: update emp set <id=525> where name=’raman’ ;

3.DELETE COMMAND-When we want to delete the data from the existing table.

syntax: delete  <table name> where <condition>;

E.g.: delete  emp where name = (‘pahul’);

4. SELECT COMMAND- Select statement is used when we want to retrieve a data from a existing table.

syntax:   select * from <table name>;

E.g. select * from emp;

When we want to retrieve a specific column from a existing table then we use a another syntax.

syntax: select <colname1> ,<colname2> from <tname>;

E.g.: select name , rollno. from emp;

When we want to retrieve a specific row from the existing table then we use following syntax:

syntax: select * from <table name> where <condition>;

E.g.: select * from emp where depno. = 10;

·    Arithmetic operation perform on a table using select command

Ø Add (+): This is used when we want to add some more content in any row of the existing table.

syntax: select <condition> from <table name>;

E.g.: select sal+1000 from emp;

Ø Between : This command is used when we want to retrieve a data in some particular range.

SYNTAX- select * from <table name> where  <condition>;

E.g.: select * from dept where deptno.. between 10 and 30;

Ø In: This command is used when we want to retrieve a particular data.

SYNTAX-  select * from <table name> where <condition>;

E.g.: select * from dept where deptno. IN(10,20,30);

Ø Null: This command is used when we want to select a null value from the existing table.

SYNTAX- select*from where <column name> is null;

E.g.: select * from where dname is null;

·   Logic condition using select command.

Ø AND

SYNTAX-select * from <table name> where <condition1> and <condition2>;

EG- select * from dept where deptno= 10 and dname= ‘sales’;

Ø OR

SYNTAX- select * from <table name> where <condition1 > or <condition2 >;

EG- select * from dept where deptno.=10 or dname = ‘sales’;

Ø NOT

SYNTAX- select*from <table name> where <condition>;

EG- select * from dept where deptno  not in (10,20,30);

·   Sorting commands for table

Ø Ascending order:

syntax: select * from <tname> order by <col name> ;

E.g: select * from dept order by depno;

Ø Descending order:

syntax: select * from <tname> order by <col name> desc;

E.g.: select * from dept order by depno dese;