December 30, 1899 :1
Name:E.VidyaRani Date: 13-02-24
Roll No:22481A1253 Expt. No:1b
=====================================================================================================
AIM: Execute DML Commands
Program :
DML Commands :
Read :
--Select: To retrieve the data available in database
Write:
--Insert:To insert the data in database
--Delete: To delete the data from database
--Update: To update the data in database
--Merge: To perform more than one DML write operation at a
time. Introduced in 9i(to perform insert + update) and
revised in 10g as(insert + update + delete)
INSERT:
Syntax:
insert into <table_name>(coumn_1,column_2,….column_n)values(value_1,value_2…value_n)
Query 1:
create table orders
(
sno number(4),
productname varchar(20),
price number(10,2)
);
Table created.
> desc orders
TABLE ObjectORDERS
Data Len Preci Sc Primar Null Def Com
Table Column Type gth sion ale y Key able ault ment
ORDER Numb
SNO - 4 0 - - -
S er
PRODUC Varch
20 - - - - -
TNAME ar2
PRICE Numb - 10 2 - - -
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :2
er
1-3
>turn off auto commit:
Inserting more than one row at a time:
insert all
into orders(sno,productname,price) values(1,'kitchenware',5000)
into orders(sno,productname,price) values(2,'jewellery',6000)
into orders(sno,productname,price) values(3,'shoes',2000)
select 1 from dual
3 row(s) inserted.
> select * from orders
SNO PRODUCTNAME PRICE
1 kitchenware 5000
2 jewellery 6000
3 shoes 2000
>logout and login
select * from orders
no data found
Inserting values in default order:
Query2:
> insert into orders values(1,'kitchenware',5000);
1 row(s) inserted.
>commit
Changing the column order:
Query3 :
> insert into orders(productname,sno,price) values('jewellery',2,6000);
1 row(s) inserted.
>commit
Statement processed.
Inserting values for some columns:
Query4:
> insert into orders(sno,productname) values(3,'shoes');
1 row(s) inserted.
> commit
Statement processed.
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :3
>logout and login
select * from orders
SNO PRODUCTNAME PRICE
1 kitchenware 5000
2 jewellery 6000
3 shoes -
Inserting Date
Query5:
> alter table orders1 add ddate date
Table altered.
insert into orders1(ddate) values(to_date('23-sep-05 1:23','dd-mon-yy hh24:mi'));
1 row(s) inserted.
>commit
>select * from orders1
SNO PRODUCTNAME PRICE DDATE
1 kitchenware 5000 -
2 jewellery 6000 -
3 shoes - -
- - - 23-SEP-05
>commit
Statement processed.
>select to_char(ddate,'dd-mon-yyyy hh24:mm:ss') newdate from orders1
NEWDATE
-
-
-
23-sep-2005 01:09:00
>commit
Inserting Values Using Substitution variables in default order :
Query6:
> insert into orders2 values(:sno,:productname,:price);
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :4
1 row(s) inserted.
>commit
Statement processed.
Query7:
> insert into orders2 values(:a,:b,:c);
1 row(s) inserted.
>commit
Statement processed.
Query8:
> insert into orders2 values(:a,:bd,:c,);
1 row(s) inserted.
>commit
Statement processed.
Query9:
>insert into orders2(sno,productname,price) values(:sno,:productname,:price);
1 row(s) inserted.
>commit
Statement processed.
Query10:
>insert into orders2(sno,productname)values(:sno,:productname);
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :5
1 row(s) inserted.
>commit
Statement processed.
Query11:
>insert into orders2(sno,productname)values(:Enter_sno,:enter_pname);
1 row(s) inserted.
>commit
Statement processed.
select * from orders2
S PRODU PR
N CTNAM IC
O E E
Kitchenwa 500
1
re 0
300
2 jewellery
0
100
3 shoes
0
100
4 dresses
00
5 mobile -
5 laptop -
>commit
Inserting values into the table using other table
Query12:
>create table orders3 as select * from orders where 1=2;
Table created.
>commit
Statement processed.
Query13:
>insert into orders3 select * from orders;
3 row(s) inserted.
>commit
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :6
Statement processed.
>select * from orders3
SNO PRODUCTNAME PRICE
1 kitchenware 5000
2 jewellery 6000
3 shoes 1000
Query14:
> create table orders4 as select * from orders2 where 1=2;
Table created.
>insert into orders4 (sno,productname) select sno,productname from orders2;
--in Both the tables corresponding columns should be of same type.
6 row(s) inserted.
> commit
Statement processed.
>select * from orders4
SNO PRODUCTNAME PRICE
1 Kitchenware -
2 jewellery -
3 shoes -
4 dresses -
5 mobile -
5 laptop -
Select:
Selecting all records with all columns:
Query15: —selected order
>Select sno,productname,price from orders
SNO PRODUCTNAME PRICE
1 kitchenware 5000
2 jewellery 6000
3 shoes 1000
Query16: --default order
>Select * from orders
SNO PRODUCTNAME PRICE
1 kitchenware 5000
2 jewellery 6000
3 shoes 1000
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :7
Query17: —selected order
> Select productname,sno,price from orders
PRODUCTNAME SNO PRICE
kitchenware 1 5000
jewellery 2 6000
shoes 3 1000
Selecting all records with selected columns
Query18:
>Select productname from orders
PRODUCTNAME
kitchenware
jewellery
shoes
Query19:
>Select productname,sno from orders
PRODUCTNAME SNO
kitchenware 1
jewellery 2
shoes 3
Selecting particular/group of records with selected columns
Query20:
>Select sno,productname,price from orders where sno=1;
SNO PRODUCTNAME PRICE
1 kitchenware 5000
Query21:
>Select sno+10 from orders
SNO+10
11
12
13
Query22:
>Select sno+10,productname,price from orders
SNO+10 PRODUCTNAME PRICE
11 kitchenware 5000
12 jewellery 6000
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :8
13 shoes 1000
Query23:
>Select sno+10 new_sno from orders where sno=1
NEW_SNO
11
Query24:
> Select sno+10 new_sno,productname,price from orders where sno=1
NEW_SNO PRODUCTNAME PRICE
11 kitchenware 5000
Query25:
Select sno+10 as new_sno,productname,price from orders where sno=1
NEW_SNO PRODUCTNAME PRICE
11 kitchenware 5000
Query26:
Select sno+10 as new_sno,productname,price from orders
NEW_SNO PRODUCTNAME PRICE
11 kitchenware 5000
12 jewellery 6000
13 shoes 1000
Update:
Syntax : Update <table_name> set col1=value1,col2=val2,….col_n=val_n:
Updating all records but specific column:
Query27:
> update orders2 set sno=1
6 row(s) updated.
>commit
Statement processed.
>select * from orders2
SNO PRODUCTNAME PRICE
1 Kitchenware 5000
1 jewellery 3000
1 shoes 1000
1 dresses 10000
1 mobile -
1 laptop -
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :9
create table orders6 as select * from orders
Table created.
Updating all records but multiple columns
Query28:
>update orders6 set sno=5,price=3000
3 row(s) updated.
>commit
select * from orders6
NO PRODUCTNAME PRICE
5 kitchenware 3000
5 jewellery 3000
5 shoes 3000
Updating specific records but single column
Query29: create table orders7 as select * from orders
update orders7 set sno=30 where productname='shoes'
1 row(s) updated.
Commit
Statement processed.
select * from orders7
SNO PRODUCTNAME PRICE
1 kitchenware 5000
2 jewellery 6000
30 shoes 1000
Updating specific records but multiple columns
Query30:
update orders7 set sno=20 , price=4000 where price=6000
1 row(s) updated.
Commit
Statement processed.
select * from orders7
SNO PRODUCTNAME PRICE
1 kitchenware 5000
20 jewellery 4000
30 shoes 1000
Using Complex conditions
Query31 :
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :10
update orders2 set price=20000 where sno=1 and productname='mobile'
1 row(s) updated.
Commit
Statement processed.
select * from orders2
SNO PRODUCTNAME PRICE
1 Kitchenware 5000
1 jewellery 3000
1 shoes 1000
1 dresses 10000
1 mobile 20000
1 laptop -
Query32:
> update orders2 set price=50000 where sno=2 or price is NULL
1 row(s) updated.
Commit
Statement processed.
select * from orders2
SNO PRODUCTNAME PRICE
1 Kitchenware 5000
1 jewellery 3000
1 shoes 1000
1 dresses 10000
1 mobile 20000
1 laptop 50000
delete
syntax: delete from <table_name> where <condition>
Deleting all records
Query33: delete from orders2
6 row(s) deleted.
>commit
Statement processed.
>select * from orders2
no data found
Query34: delete orders1
4 row(s) deleted.
Commit
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :11
Statement processed.
select * from orders2
no data found
Query35:
>select * from orders1
SNO PRODUCTNAME PRICE
1 kitchenware 5000
2 jewellery 5000
3 shoes 1000
4 clothes 10000
5 mobile 20000
6 laptop 2000
>delete from orders1 where sno=6
1 row(s) deleted.
>commit
Statement processed.
>select * from orders1
SNO PRODUCTNAME PRICE
1 kitchenware 5000
2 jewellery 5000
3 shoes 1000
4 clothes 10000
5 mobile 20000
Query36:
>delete orders1 where price=5000
2 row(s) deleted.
> commit
Statement processed.
>select * from orders1
SNO PRODUCTNAME PRICE
3 shoes 1000
4 clothes 10000
5 mobile 20000
conditioning on more than one column
Query37:
> delete orders1 where productname='shoes' and price=1000;
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :12
1 row(s) deleted.
> commit
Statement processed.
>select * from orders1
SNO PRODUCTNAME PRICE
4 clothes 10000
5 mobile 20000
Query38:
>delete from orders1 where sno=4 or price=10;
1 row(s) deleted.
> commit
Statement processed.
>select * from orders1
SNO PRODUCTNAME PRICE
5 mobile 20000
Query39: delete orders
3 row(s) deleted.
> commit
Statement processed.
>select * from orders
no data found
>rollback
Statement processed.
select * from orders
SNO PRODUCTNAME PRICE
1 kitchenware 5000
2 jewellery 6000
3 shoes 1000
Query40: truncate table orders
| Seshadri Rao Gudlavalleru Engineering College
December 30, 1899 :13
Table truncated.
select * from orders
no data found
rollback
Statement processed.
select * from orders
no data found
| Seshadri Rao Gudlavalleru Engineering College
