ADDING, DELETING AND MODIFYING RECORD IN ORACLE DATABASE


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


Comments

Post a Comment

Popular posts from this blog

MySQL Point in Time Recovery: How To Configure And How Does it Work?

Image
 MySQL Point in Time Recovery: How To Configure And How Does it Work? The point-in-time recovery allows you to restore a MySQL database to a specific time in the past. The point-in-time recovery relies on two key components: Full backup: This serves as the foundation for recovery, providing the starting state of the database. Binary logs: These binary log files record all changes made to the database, allowing you to replay those changes to a desired point. If you don’t have a full backup or binary logs enabled, you cannot carry the point-in-time recovery. We’ll illustrate how to perform the point-in-time recovery in MySQL to recover a database to a specific point in time. Checking binar log status:- We need login to mysql server and run given below command:- show global variables like 'log_bin' ; Show output for enable mysql-bin log. Creating a new sample database & taking a full backup:- We are going to login in mysql databse and creating new database and table.given ...
Read more

MySQL Replication Switchover: Step-by-Step Guide

Image
 MySQL Replication Switchover: Step-by-Step Guide MySQL replication is a powerful feature that allows you to create redundant copies of your database for improved availability and scalability. In certain scenarios, it becomes necessary to switch the roles of the master and slave servers in a replication setup. This article will guide you through the process of performing a MySQL replication switchover, ensuring a smooth transition without any data loss. Let’s dive into the step-by-step instructions. I have tow node one is master node and another is slave node.Given below details:- Current Master: 192.168.70.10 Current Slave : 192.168.70.11 Verifying the Replication:- Before initiating the switchover, it’s important to verify that replication is functioning correctly on the current Slave server (192.168.70.11). Execute the following queries to ensure the replication status: mysql> show slave status\G This command displays the slave’s status, including information such as replicat...
Read more

Mysql Commercial Mysqlbackup:How to take Differential or Incremental Backup and resotre using mysqlbackup utility in mysql 8.0.37 enterprise edition

Image
Mysql Commercial Mysqlbackup:How to take Differential or Incremental Backup and resotre using mysqlbackup utility in mysql 8.0.37 enterprise edition Incremental vs Differential Backup:- The main difference between incremental and differential backups is how they save space and time by storing only changed files. However, the effectiveness of incremental backups is very different from differential backups. Before we dive into the differences between Incremental and Differential backups let’s review Full Backups.  Are you still using Full Backups? In its simplest form, a full backup is the starting point for both Incremental and Differential backup strategies. A full backup, of course, backs up all the data on a partition or disk by copying all disk sectors with data to the backup image file.  A full backup is time-consuming and requires a lot of space, it’s usually used as part of a backup plan with long intervals, like once a week or a month. During this time, if anything goes...
Read more