ADDING,
DELETING AND MODIFYING RECORD
I. Adding record
II. Deleting record.
III. Updating/modifying data values.
IV. Saving and undoing change in data.
V. Truncate
VI. Drop
ADDING RECORD:-
DBMS is
basically collection of data in the form of table with row and columns. The first
step is to create a table and adding new record.
Inserting values of different data
types
Character data types:-
The
character/string values can be regarded as input which use single quotes.
Number data types:-
The number
data type can be regarded as input directly without any quotes.
Date data types:-
The date
data types values can be regarded as input single quotes if the value being
supplied is in default oracle format (dd-mm-yy or dd-mm-yyyy).
To insert in
desired format we have to use the TO_DATE () built-in function with format
specified.
Adding
null values:-
Any data
types can be supplied unknown value by specifying NULL.
Notes:-
Do not use
NULL with quotes as it will store the next ‘NULL’ INSTEAD of the unknown
values.
Now create
table with different data types and add record.
SQL>
create table students(rollno char(4),name varchar2(10),date_of_birth date,mark
number(5));
Table
created.
SQL>
insert into students values('1','alam','14-oct-1993',60);
1 row
created.
SQL> insert into students
values('2','praveen','20-nov-1994',70);
1 row
created.
SQL>
insert into students values('3','ashu','15-aug-1991',null);
1 row
created.
SQL>
insert into students values('4','aman',null,96);
1 row
created.
SQL> insert into students values('5',null,'15-dec-1995',90);
1 row
created.
SQL>
select * from students;
ROLL
NAME DATE_OF_B MARK
----
---------- --------- ----------
1 alam
14-OCT-93 60
2 praveen
20-NOV-94 70
3 ashu
15-AUG-91
4 aman 96
5 15-DEC-95 90
SQL>
select rollno,name,to_char(date_of_birth,'dd/mm/yyyy hh24:mi:ss') DOB,mark from
students;
ROLL
NAME DOB MARK
----
---------- ------------------- ----------
1 alam
14/10/1993 00:00:00 60
2 praveen
20/11/1994 00:00:00 70
3 ashu
15/08/1991 00:00:00
4 aman 96
5 15/12/1995 00:00:00 90
Notes:-
Character
data type’s values are enclosed in single quotes.
Number data
type are not required quotes.
Date data
type:-
Values are
enclosed in single quotes. Format specified is optional.
If we supply
date component only then the time store is 00:00:00.
The time
component can be specified using built in function TO_DATE () with format
specifier.
To display both date and time use TO_CHAR ()
built in function.
String substitution for adding
multiple record:-
It is quite
cumbersome and requires a lot of typing if we want to insert many record.
Oracle has a
provision called STRING SUBSTITUTION which is come very handy in such
scenarios’.
String
substitution uses the symbol ‘&’ and ‘&&’.
SQL>
create table std(collage char(5),rollno number,name varchar2(10),marks
number(4));
Table
created.
SQL>
insert into std
values('&p_collage','&p_rollno','&p_name','&p_marks');
Enter value
for p_collage: ime
Enter value
for p_rollno: 1
Enter value
for p_name: ashu
Enter value
for p_marks: 65
old 1: insert into std
values('&p_collage','&p_rollno','&p_name','&p_marks')
new 1: insert into std
values('ime','1','ashu','65')
1 row
created.
SQL> /
Enter value
for p_collage: its
Enter value
for p_rollno: 2
Enter value
for p_name: aman
Enter value
for p_marks: 60
old 1: insert into std
values('&p_collage','&p_rollno','&p_name','&p_marks')
new 1: insert into std
values('its','2','aman','60')
1 row
created.
Explanation:-
‘&p_collage’
tell oracle to ask for the values of ‘p_collage’ string when the insert
statement is executed.
Single quote
is required as the collage column is of character data types.
Single
quotes is not required for marks as it is of number data types.
To add new
record just types ‘/’ at sql prompt and press enter key. Oracle will repeat the
previous step.
‘/’ repeat
the previously executed sql statement.
Let suppose
we have to enter multiple record for the student of the same collage, say ‘IME’
oracle provide ‘&&’ as substitution string which store the value once
supplied for the current sql session.
SQL> insert into std
values('&&p_collage','&p_rollno','&p_name','&p_marks');
Enter value
for p_collage: ime
Enter value
for p_rollno: 3
Enter value
for p_name: alam
Enter value
for p_marks: 65
old 1:
insert into std
values('&&p_collage','&p_rollno','&p_name','&p_marks')
new 1:
insert into std values('ime','3','alam','65')
1 row
created.
SQL> /
Enter value
for p_rollno: 4
Enter value
for p_name: pankaj
Enter value
for p_marks: 50
old 1:
insert into std
values('&&p_collage','&p_rollno','&p_name','&p_marks')
new 1:
insert into std values('ime','4','pankaj','50')
1 row
created.
Note that
values for p_collage was prompted for the first INSERT statement and not for
the next INSERT statement which is executed by / at SQL prompted.
DELETING RECORD:-
Deleting
implies removing the record from a table which are no longer required.
SQL>
select * from std;
COLLA ROLLNO NAME MARKS
-----
---------- ---------- ----------
ime 1 ashu 65
its 2 aman 60
ime 3 alam 65
ime 4 pankaj 50
SQL>
delete from std;
4 row
deleted.
SQL>select
* from std;
No row
selected.
All record
have been removed on issuing the delete command.
Deleting specific record:-
Now assume
that the table with the original data.
Delete
record of rollno 4. Record of rollno 4 can be deleted by using where clause.
SQL>delete
from STD where rollno=4;
1 row
deleted.
Delete specific record using Boolean
operator:-
SQL>delete
from STD where rollno =4 and subject=’math’;
1 row
deleted.
Deleting record from one table
depending on record in another table:-
We need to
delete those rollno from student_master table for which mark have not been
entered in student_details table.
SQL>delete
from student_master where rollno NOT IN (select rollno from
student_details);
1 row
deleted.
UPDATE/MODIFYING DATA VALUES:-
Update refer
to changing or modifying values in columns of existing record.
SQL>
select * from std;
COLLA ROLLNO NAME MARKS
-----
---------- ---------- ----------
ime 1 ashu 65
its 2 aman 60
ime 3 alam 65
ime 4 pankaj 50
Updating all record:-
Let now add
5 mark to mark obtained by every student.
SQL>
update std set marks=marks+5;
4 rows
updated.
SQL>
select * from std;
COLLA ROLLNO NAME MARKS
-----
---------- ---------- ----------
ime 1 ashu 70
its 2 aman 65
ime 3 alam 70
ime 4 pankaj 55
Updating specific column values for a
specific record:-
SQL>
update std set name='praveen' where rollno=4;
1 row
updated.
SQL>
select * from std;
COLLA ROLLNO NAME MARKS
-----
---------- ---------- ----------
ime 1 ashu 70
its 2 aman 65
ime 3 alam 70
ime 4 praveen 55
Updating multiple column in single
update statement:-
SQL>
update std set name='arun',marks=90 where rollno=1;
1 row
updated.
SQL>
select * from std;
COLLA ROLLNO NAME MARKS
-----
---------- ---------- ----------
ime 1 arun 90
its 2 aman 65
ime 3 alam 70
ime 4 praveen 55
Updating table using IN operator:-
SQL>
select * from student_masters;
ROLLNO NAME CITY
----------
------------------------------ ----------
1 satish bhopal
2 rasmi ajmer
3 rishi bhopal
4 shama ajmer
5 nilesh delhi
SQL>
select * from student_details;
ROLLNO SUBJECT MARKS
----------
------------------------------ ----------
1 maths 12
1 phy 23
2 maths 14
2 phy 21
3 maths 34
4 maths 43
5 maths 20
7 rows
selected.
SQL>
update student_details set marks=marks+5
2
where subject='maths'
3 and
rollno in (select rollno from student_masters where city='bhopal');
2 rows
updated.
SQL>
select * from student_details;
ROLLNO SUBJECT MARKS
----------
------------------------------ ----------
1 maths 17
1 phy 23
2 maths 14
2 phy 21
3 maths 39
4 maths 43
5 maths 20
7 rows
selected.
SAVING AND UNDOING CHANGE IN DATA:-
Oracle provide
certain feature for saving record and undoing changes.
Letus we are
creating a table name department and insert some record.
SQL>
create table department(id number,dname varchar2(10),djob varchar2(10));
Table
created.
SQL>
insert into department values(1,'DBA','mysql');
1 row
created.
SQL> insert into department
values(2,'DBA','oracle');
1 row
created.
SQL> insert into department
values(3,'java','developer');
1 row
created.
SQL>
select * from department;
ID DNAME DJOB
----------
---------- ----------
1 DBA mysql
2 DBA oracle
3 java developer
Now if
instead of typing ‘EXIT’ at the SQL prompt, you just click the CROSS button at
the right top of SQL screen and login again and then select record from
department table
SQL>select
* from department;
No row
selected.
Oracle has
not save your data you have a killed particular SQL session.
COMMIT:-
What we
normally call save is called ‘COMMIT’ IN ORACLE.
You can use
DML operation after that need for ‘COMMIT’ command in oracle.
You can also
change enable or disable commit.
SQL>
alter session disable commit in procedure;
Session
altered.
ROLLBACK:-
What we normally
call ‘UNDO’ is called rollback in oracle.
SQL>
create table student_detail(rollno number,name varchar2(10));
Table
created.
SQL>
insert into student_detail values(1,'pankaj');
1 row
created.
SQL>
insert into student_detail values(2,'alam');
1 row
created.
SQL>
commit;
Commit
complete.
SQL>
insert into student_detail values(3,'ashu');
1 row
created.
SQL>
insert into student_detail values(4,'aman');
1 row
created.
SQL>
select * from student_detail;
ROLLNO NAME
----------
----------
1 pankaj
2 alam
3 ashu
4 aman
SQL>
rollback;
Rollback
complete.
SQL>
select * from student_detail;
ROLLNO NAME
----------
----------
1 pankaj
2 alam
We issued
the COMMIT statement after inserting rollno 1 and 2. Then we insert rollno 3
and 4 followed by rollback.
Therefore on
selecting record from details table after ‘ROLLBACK’ only rollno 1 and 2 exit
in the table.
SAVEPOINT:-
Oracle provide
a feature wherein we can define savepoints in various DML operation so that at
a later stage we can rollback change to a specific savepoints.
SQL>
create table collage(rollno number,name varchar2(20),marks number(5));
Table
created.
SQL>
insert into collage values(1,'alam',20);
1 row
created.
SQL>
insert into collage values(2,'ashu',20);
1 row
created.
SQL>
savepoint sp1;
Savepoint
created.
SQL>
insert into collage values(3,'aman',30);
1 row
created.
SQL>
insert into collage values(3,'arun',40);
1 row
created.
SQL>
savepoint sp2;
Savepoint
created.
SQL>
insert into collage values(5,'praveen',50);
1 row
created.
SQL>
select * from collage;
ROLLNO NAME MARKS
----------
-------------------- ----------
1 alam 20
2 ashu 20
3 aman 30
3 arun 40
5 praveen 50
SQL>
rollback to savepoint sp2;
Rollback
complete.
SQL>
select * from collage;
ROLLNO NAME MARKS
----------
-------------------- ----------
1 alam 20
2 ashu 20
3 aman 30
3 arun 40
SQL>
rollback to savepoint sp1;
Rollback
complete.
SQL>
select * from collage;
ROLLNO NAME MARKS
----------
-------------------- ----------
1 alam 20
2 ashu 20
Now we will
create two savepoints sp1 and sp2 and then issue a commit statement.
SQL> insert into collage values(1,'alam',20);
1 row
created.
SQL> insert into collage values(2,'aman',30);
1 row
created.
SQL>
savepoint sp1;
Savepoint
created.
SQL> insert into collage values(3,'ashu',40);
1 row
created.
SQL> insert into collage values(4,'arun',50);
1 row
created.
SQL> savepoint
sp2;
Savepoint
created.
SQL> insert into collage values(5,'pankaj',60);
1 row
created.
SQL>
commit;
Commit
complete.
SQL>
select * from collage;
ROLLNO NAME MARKS
----------
-------------------- ----------
1 alam 20
2 aman 30
3 ashu 40
4 arun 50
5 pankaj 60
SQL>
rollback to sp1;
rollback to sp1
*
ERROR at
line 1:
ORA-01086:
savepoint 'SP1' never established in this session or is invalid
TRUNCATE:-
If we want
to delete all record permanently, we first issue the delete statement followed
by the commit.
Now that we
can just use a single statement ‘TRUNCATE’ to achieve the result.
SQL>
select * from collage;
ROLLNO NAME MARKS
----------
-------------------- ----------
1 alam 20
2 aman 30
3 ashu 40
4 arun 50
5 pankaj 60
SQL>
truncate table collage;
Table
truncated.
SQL>
select * from collage;
no rows selected
SQL>
rollback;
Rollback
complete.
SQL>
select * from collage;
no rows
selected
Note that we
can use ‘TRUNCATE’ command, truncate command is delete only for record not for
table or table structure and single statement and we can can’t rollback.
DROP:-
DROP command
is used for some special case because drop command delete record and table
structure.
A table has
two aspects, namely
1. The structure: - Number of column,
data types of column and constraints like PRIMARY KEY, NOT NULL, UNIQUE etc.
2. The data: - The record of row.
The DROP
command is used to destroy the entire table along with contains. The drop will
remove all the record and destroy the table structure also. Moreover, dropped
table can’t be recovering using the ROLLBACK command.
The TRUNCATE
command permanently remove the record only and not the structure. Record truncate
can’t be rolled back.
The DELETE
command remove the desired record (all or depending on where clause) but record
removing using the DELETE command can be recovered using ROLLBACK command.
SQL>
select * from collage;
ROLLNO NAME MARKS
----------
-------------------- ----------
1 ashu 30
2 aman 40
3 alam 40
SQL> drop
table collage;
Table
dropped.
SQL> desc
collage;
ERROR:
ORA-04043:
object collage does not exist
SQL>
select * from collage;
select *
from collage
*
ERROR at
line 1:
ORA-00942:
table or view does not exist
SQL>
rollback;
Rollback
complete.
SQL>
select * from collage;
select *
from collage
*
ERROR at
line 1:
ORA-00942:
table or view does not exist
DIFFERENCE BETWEEN DELETE
AND TRUNCATE:-
Delete
record can be restore using rollback. Truncate record can’t be restore because
they are remove permanently.
In
the DELETE statement we can specify the WHERE clause there by specifying which
record to be deleted. TRUNCATE remove all record.
On
executing the DELETE statement all database trigger are fired, where as in
using the TRUNCATE statement database trigger can’t be fired. As such TRUNCATE
is faster and does not use as much undo space as DELETE.
THANK YOU
No comments:
Post a Comment