2 Naren 400
3 Ramesh
4 Madhu
5 Visu
6 Rattu
SQL> select * from student order by no desc;
NO NAME MARKS
--- ------- ---------
6 Rattu
5 Visu
4 Madhu
3 Ramesh
2
Saketh 200
2 Naren 400
1 Sudha 100
1 Jagan 300
USING DML
USING
UPDATE
This
can be used to modify the table data.
Syntax:
Update <table_name> set <col1>
= value1, <col2> = value2 where <condition>;
Ex:
SQL> update student set marks = 500;
If you are not specifying any condition this will update entire
table.
SQL> update student set marks = 500 where no = 2;
SQL> update student set marks = 500, name = 'Venu' where no = 1;
USING
DELETE
This
can be used to delete the table data temporarily.
Syntax:
Delete <table_name> where <condition>;
Ex:
SQL> delete student;
If you are not specifying any condition this will delete entire
table.
SQL> delete student where no = 2;
USING
ALTER
This
can be used to add or remove columns and to modify the precision of the
datatype.
a) ADDING COLUMN
Syntax:
alter table <table_name> add <col
datatype>;
Ex:
SQL> alter table student add sdob date;
b) REMOVING COLUMN
Syntax:
alter table <table_name> drop <col
datatype>;
Ex:
SQL> alter table student drop column sdob;
c) INCREASING OR DECREASING PRECISION OF A
COLUMN
Syntax:
alter table <table_name>
modify <col datatype>;
Ex:
SQL> alter table student modify marks number(5);
* To decrease precision the column
should be empty.
d) MAKING COLUMN UNUSED
Syntax:
alter table <table_name>
set unused column <col>;
Ex:
SQL> alter table student set unused
column marks;
Even though the column is unused still it will occupy
memory.
d) DROPPING UNUSED COLUMNS
Syntax:
alter table <table_name>
drop unused columns;
Ex:
SQL> alter table student drop unused columns;
* You can not drop individual unused
columns of a table.
e) RENAMING COLUMN
Syntax:
alter table <table_name>
rename column <old_col_name> to <new_col_name>;
Ex:
SQL> alter table student rename column marks to smarks;
USING
TRUNCATE
This
can be used to delete the entire table data permanently.
Syntax:
truncate table <table_name>;
Ex:
SQL> truncate table student;
USING
DROP
This
will be used to drop the database object;
Syntax:
Drop table <table_name>;
Ex:
SQL> drop table student;
USING
RENAME
This
will be used to rename the database object;
Syntax:
rename <old_table_name> to
<new_table_name>;
Ex:
SQL> rename student to stud;
USING
COMMIT
This
will be used to save the work.
Commit
is of two types.
1 Implicit
2 Explicit
a) IMPLICIT
This will be issued by oracle internally
in two situations.
1 When any DDL operation is performed.
2 When you are exiting from SQL * PLUS.
b) EXPLICIT
This will be issued by the user.
Syntax:
Commit or commit work;
* When ever you committed then
the transaction was completed.
USING
ROLLBACK
This
will undo the operation.
This
will be applied in two methods.
1 Upto previous commit
2 Upto previous rollback
Syntax:
Roll or roll work;
Or
Rollback or rollback work;
*
While process is going on, if suddenly power goes then oracle will rollback the
transaction.
USING
SAVEPOINT
You
can use savepoints to rollback portions of your current set of transactions.
Syntax:
Savepoint <savepoint_name>;
Ex:
SQL> savepoint s1;
SQL> insert into student values(1, ‘a’, 100);
SQL> savepoint s2;
SQL> insert into student values(2, ‘b’, 200);
SQL> savepoint s3;
SQL> insert into student values(3, ‘c’,
300);
SQL> savepoint s4;
SQL> insert into student values(4, ‘d’,
400);
Before rollback
SQL> select * from student;
NO NAME MARKS
--- -------
----------
1 a 100
2 b 200
3 c 300
4 d 400
SQL> rollback to savepoint s3;
Or
SQL> rollback to s3;
This will rollback last two records.
SQL> select * from student;
NO NAME MARKS
--- -------
----------
1 a 100
2 b 200
