DML commands of SQL

DML command stands for Data Manipulating Language. DML commands of SQL is used for manipulating data of data base. DML commands can't manipulating schema, constraints or designs of data base.

DML have following 3 commands -
1. insert into
2. update
3. delete
we learn following table to study of DML command.

Account -
Ac no. (14,0)
Cname varchar (20)
Balance number (11, 2)
Mob_no. varchar (13)



1. insert into :-

this DML command can insert a single record or multiple records (touple) or value of specific column/columns.

a. insert a single record :-

syntax :-
insert into table_name values (value1, value2, .... , valuen);

example :- insert into account values (11010235649845, 'Divya Nagor', 555075, '+919876543210');

b. insert multiple records :-

syntax :-
insert into table_name values (&column1, &column2, .... , &columnn);

example :- insert into account values (&Ac_no, '&cname', &balance, '&mob_no');

c. insert value in to specific columns -

syntax :-

insert into table_name (column1, column2, ... , columnn) values (value1, value2, ... , valuen);

example :- insert into account (Ac_no, cname) value (9921000104568, 'vaishnavi');

2. update :-
this DML command is used to update value of a single record, multiple record or single column/multiple column.

a. updating records of a specific row:-

syntax:-

update table_name set (column1=value1, column2=value2, ... , columnn=valuen) where predicate;

example :-

1. update account set (balance=1250) where Ac_no=11010235649845;

2. update account set (balance=12360, mob_no='+911234567890') where ac_no=99221000104568;

b. updating whole record/records :-

example :-

update account set (ac_no=99220000001009, cname='Teena Saini', balance=7000, mob_no='+919999988888') where ac_no=99221000104568;

3. delete :-

this DML command is used to delete a single/multiple record from a table which satisfies condition/predicate. delete and drop are different commands and their main difference are as follows -
I. delete command can delete data stored in table only whereas drop command can delete data of table and design of table both permanently.

II. we can rollback changes made by delete command but it is not applicable for drop command.

syntax:-

a. delete specific records from table -

syntax :-
delete from table_name where predicate;

example :- delete from account where balance < 5000;

b. delete all records from table -

syntax :-
delete from table_name;

example:- delete from account;