What is self join in oracle database?

A self-join is a join in which a table is joined with itself. To join a table itself means that each row of the table is combined with itself and with every other row of the table.

For E.g:

Suppose we have a table called “SelfJoin” and their data shown below

Query:- Select * from SelfJoin;

O/P:-

EmpId	Name	MgrId
1	Paul
2	Greg	1
3	Jon
4	Samy	3

In the table we have Employee’s id i.e EmpId, Name of the Employee and Manager of those employees i.e MgrId.

Condition:- Fetch the name of the employee and their managers.

Query:- Select M.Name ||' is the employee and '||E.Name||' their manager' as Details

from SelfJoin E,SelfJoin M

where M.mgrid=E.EmpId;

O/P:-

Details

Greg is the employee and Paul their manager

Samy is the employee and Jon their manager

Here the table “SelfJoin” is joined itself on the basis of EmpId and MgrId.

What is a sequence in oracle database?

A sequence is an object in oracle database i.e used to generate a number sequence. This can be useful when you need to create a unique number that acts as the primary key.

E.g:-

Create Sequence SEQ_TEST

MinValue 1

MaxValue 999999999

Increment by 1

Start With 1

NoCache NoOrder NoCycle;

Note:- Here SEQ_TEST is the name of the sequence, then after we need to set the parameter according to our conditions and requirements.

Call the Sequence in select statement:-

Query:- select SEQ_TEST1.nextval from dual;

Check the list of sequences created in database user:

Query:- Select  * from User_Sequences;

Conditions:-
* When we have a requirement to change the sequence increment number from 1 to increment by 5 then

Query:-  Alter Sequence SEQ_TEST increment by 5;

*When we a condition to restart the sequence then
Query:-  Alter Sequence SEQ_TEST Restart With 1;

* In this sequence code we have set NoCache, Suppose we have set Cache 5 then it will store the upcoming 5 number in the memory and the LAST_NUMBER of the sequence as per the cache i.e +5 it will update automatically.

Query to update or change the cache value in sequence:-
Alter Sequence SEQ_TEST Cache 5; ----> (Here it will set the cache 5 in your sequence)