SQL

>The language,which is used to communicate with database.

>Sql uses statements to communicate with the database
>Sql requires an environment(sql *plus/isql *plus)to implement its features in the form of
statements.
>Sql stands for structured query language
>Pronounced as s.q.l/sequel
>Sql is a non-Procedural language means we dont write any step by step process.
>Sql is a volatile language(Non historic)
>Sql is case insensitive language
>Sql commands cannot be abbreviated
>Sql statements can spread onto multiples lines(any number of lines)
>Sql is Ansi(American National standard Institute)standard.

==========================================================================

SQL Query to Create New User and grant permission

---------------------------------
alter session set "_ORACLE_SCRIPT"=true;
create user <user name> identify by <password>
grant dba to <user name>
connect <username>/<password>;
==========================================================================
Data types:
Data types defines type of information of information and the amount of spaces required for
a particular column.
1. CHAR: The data type is used to store alphanumeric values.
It can be also store special symbols like−,:, /,* and etc
This data type of fixed size. The Maximum size is 255
bytes. Memory is not used efficiently.
Ex: CREATE TABLE employees ( employee_id NUMBER,
first_name CHAR(10) );

2. VARCHAR2: This data type it can store alphanumeric values special symbols like
−,:,and etc. This data type is of variable size. Maximum size 4000 bytes. Memory is
efficiently used.
CREATE TABLE customers ( customer_id NUMBER, customer_name VARCHAR2(50) );

3. NUMBER(P,S): This data type used to store numeric values maximum size 38 digits. p
means precision value--determines the maximum length of the data .s means scale---
Determines the number of places to the right of the decimal
 for Ex:
 456.78 so p value is 5 and s value is 2

4. DATE: This data type is used to store date values it will not have size. Default Date
format is DD−MM−YY.
Ex: Create table student1(SNO number(2),
SNAME varchar2(20), MARKS number(3), DOJ Date);
 Insert into student1 values(101,'Ravi',99,SYSDATE);

5. LONG: Similar to varchar2 data type. Maximum size 2 GB.

6. RAW: used to store Images, logos, digital signatures etc. Maximum size 255 bytes.

7. LONG RAW: Similar to RAW data type. Maximum size 2 GB.

[Link]:(character large object) used to store characters. Maximum size 4

[Link]: (binary large object) used to store binary data. Maximum size 4 GB.

10 BFILE:(binary file)

===============================================================

SQL LANGUAGES

DDL:Data definition language): DDL --Data definition language ----Interacting with db

[Link] work becomes automatically permanent.
we cannot perform undo operation in ddl command.
we cannot perform rollback command in ddl

create -- used to create table

alter -- used to change table structure
drop -- used to drop the table
truncate -- to delete all the records from table
rename -- to change the column name
[Link]:
syntax:
create tablle
table_name(<field1><datatype>,<field2><datatype>,......<fieldn><datatype>
desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
EID NUMBER(5)
NAME VARCHAR2(20)
SALARY NUMBER(5,3)
DESIGNATION VARCHAR2(20)

SQL> insert into employee values(101,'ravi',45.04,'admin');

SQL> insert into employee values(102,'raj',75.04,'coder');
SQL> insert into employee values(103,'rekha',85.74,'developer');
SQL> insert into employee values(104,'revi',75.74,'manager');
-----------------------------------------------------------------------------------
[Link]:
syntax:
rename table_name to new_name;
SQL> rename employee to emp1;
--------------------------------------------------------------------------------------
[Link] :
syntax:
alter table table_name add column_name dataype;

SQL> ALTER TABLE emp1 add address varchar(20);

-------------------------------------------------------------------------------------
[Link] :It will delete the table records;
syntax:
truncate table table_name
SQL> truncate table emp1;
------------------------------------------------------------------------------------
SQL>select * from emp1;
no rows selected
#here table records will get deleted;
-------------------------------------------------------------------------------------
[Link]:
it will delete complete table permanently
syntax:
drop table emp1;
EX:
SQL> drop table emp1;
Table dropped.

=========================================================
=
DML:Data manipulation language(DML): Data manupulation language(manupulation means
modification)
>DML commands deal with the data only
>DML commands interact with the buffer first and then database on commit
>We can undo (Rollback)the changes.
>DML commands are slower (performance)
>Row-Level locks occurs implicitly on the modifed rows
insert -- to insert record into database
update -- to udpate database records
delete -- to delete database rows

It deals with data of [Link] provides 3 commands

[Link]
[Link]
[Link]
-------------------------------------------------------------------------------------
[Link]:
syntax:
insert into <table_name> values(data1,data2,......datan)

----------------------------------------------------------------
SQL> create table singer(name varchar2(20),address varchar2(20));
SQL> insert into singer values('sidsriram','Hyderabad');
SQL> insert into singer values('Arjith','Mumbai');
SQL> insert into singer values('Damini','Delhi');
------------------------------------------
SQL> select * from singer;
NAME ADDRESS
-------------------- --------------------
sidsriram Hyderabad
Arjith Mumbai
Damini Delhi
-------------------------------------------
[Link] :
syntax:
update<table> set <field>=<update> where <field>=<Reference data>;7

updating the name here:

SQL> update singer set name='Damini' where address='Hyderabad';

1 row updated.

SQL> select * from singer;

NAME ADDRESS
-------------------- --------------------
Damini Hyderabad
Arjith Mumbai
Damini Delhi
-------------------------------------------
SQL> update singer set name='Damini' where address='Kerela';
SQL> update singer set name='sidsriram' where address='Kerela';
SQL> update singer set name='sidsriram' where address='Delhi';
SQL> select * from singer;

NAME ADDRESS
-------------------- --------------------
Damini Hyderabad
Arjith Mumbai
sidsriram Delhi
----------------------------------------------------------------------------
[Link]:
syntax:
[Link] from table_name ----to delete records from table.
[Link] from <table_name> where condition statement;
SQL> delete from singer;
3 rows deleted.
-----------------------------------------------------------------
SQL> select * from singer;
no rows selected
-----------------------------------------------------------------
SQL> insert into singer values('Damini','Delhi');
SQL> insert into singer values('sidsriram','Hyderabad');
SQL> insert into singer values('Arjith','Mumbai');
SQL> select * from singer;

NAME ADDRESS
-------------------- --------------------
Damini Delhi
sidsriram Hyderabad
Arjith Mumbai
--------------------------------------------
SQL> delete from singer where name='Damini';
SQL> select * from singer;

NAME ADDRESS
-------------------- --------------------
sidsriram Hyderabad
Arjith Mumbai
--------------------------------------------------------------------------
=========================================================

DQL:Data query language

It is used to retrive the inforamtion from the [Link] has only one select command.
-------------------------------------------------------------------------------
syntax:
select * from table_name
-----------------------------------------------------------------------------
--->create table student(roll number(5),name varchar2(20),address varchar2(20));

SQL> create table stu(roll number(5),name varchar2(20),address varchar2(

20));
SQL> insert into stu values(1,'rahul','hyd'
SQL> insert into stu values(2,'raj','mum');
SQL> insert into stu values(3,'ravi','rajasthan');

SQL> select * from stu;

ROLL NAME ADDRESS

---------- -------------------- --------------------
1 rahul hyd
2 raj mum
3 ravi rajasthan

SQL> select * from stu where roll=2;

ROLL NAME ADDRESS

---------- -------------------- --------------------
2 raj mum
=========================================================
TCL:Transaction control language
commit -- to save data perminenlty
savepoint -- to create a reference variable where up to we can undo
rollback -- to undo executed sql querys

commit:whatever the temporary data we have stored in the sql plus that will be displayed
after the commit command;
Syntax:
without commit command:
SQL> create table player(rank number(5),name varchar2(10),best number(5));
SQL> insert into player values(1,'Dhoni',178);
SQL> insert into player values(2,'Virat',180);
SQL> insert into player values(3,'rohit',175);
SQL> select * from player;

RANK NAME BEST

---------- ---------- ----------
1 Dhoni 178
2 Virat 180
3 rohit 175
----------------------------------------------------------------------------------
If you exit the sql and again if you login the sql plus then you will be showing the no rows
selected.
------------------------------------------------------------------------------------
before exiting you write the commit command then you will be showing the data which was
there in the table.
--------------------------------------------------------------------------------
[Link]:
SQL> commit;
SQL> select * from player;

RANK NAME BEST

---------- ---------- ----------
1 Dhoni 178
2 Virat 180
3 rohit 175
---------------------------------------------------------------------------------
SQL> rollback;

Rollback complete.

SQL> select * from player;

RANK NAME BEST

---------- ---------- ----------
1 Dhoni 178
2 Virat 180
3 rohit 175

SQL> insert into player values(4,'ravi',123); #here 4 value is a temporary data.

1 row created.

SQL> select * from player;

RANK NAME BEST

---------- ---------- ----------
1 Dhoni 178
2 Virat 180
3 rohit 175
4 ravi 123

SQL> rollback;
SQL> select * from player;
 RANK NAME BEST
---------- ---------- ----------
1 Dhoni 178
2 Virat 180
3 rohit 175
---------------------------------------------------------------------------------
SQL> update player set name='kohli' where rank=1;
---------------------------------------------------------
SQL> select * from player;

RANK NAME BEST

---------- ---------- ----------
1 kohli 178
2 Virat 180
3 rohit 175
--------------------------------------------------------------------
SQL> rollback;

Rollback complete.

SQL> select * from player; #here after typing rollback original data will be printed.

RANK NAME BEST

---------- ---------- ----------
1 Dhoni 178
2 Virat 180
3 rohit 175
----------------------------------------------------

SQL> delete from player where rank=2;

1 row deleted.

SQL> select * from player;

RANK NAME BEST

---------- ---------- ----------
1 Dhoni 178
3 rohit 175
SQL> rollback; #rollback is nothing getting back the data

Rollback complete.

SQL> select * from player;

RANK NAME BEST

---------- ---------- ----------
1 Dhoni 178
2 Virat 180
3 rohit 175
------------------------------------------------------------------
[Link]: savepoint will work with rollback
SQL> rollback to A;
Rollback complete.

SQL> select * from player;

RANK NAME BEST

---------- ---------- ----------
1 Dhoni 178
2 Virat 180
3 rohit 175

SQL> rollback;

Rollback complete.

SQL> select * from player;

RANK NAME BEST

---------- ---------- ----------
1 Dhoni 178
2 Virat 180
3 rohit 175

SQL> savepoint A;

Savepoint created.

SQL> insert into player values(4,'Dhawan',180);

1 row created.
SQL> insert into player values(5,'sachin',198);
1 row created.
SQL> savepoint B;
Savepoint created.
SQL> insert into player values(6,'hari',158);
SQL> insert into player values(7,'harbajan',196);
1 row created.
SQL> savepoint C;
Savepoint created.
SQL> select * from player;

RANK NAME BEST

---------- ---------- ----------
1 Dhoni 178
2 Virat 180
3 rohit 175
4 Dhawan 180
5 sachin 198
6 hari 158
7 harbajan 196
DCL: GRANT: This command is used to give privileges to a user or role.

 GRANT <privilege> TO <user/role>;

 Example: GRANT SELECT ON employees TO john_doe;

REVOKE: This command is used to take back privileges from a user or role.

 REVOKE <privilege> FROM <user/role>;

 Example: REVOKE UPDATE ON employees FROM john_doe;

EXAMPLE:

Step 1: Create a new user CREATE USER john_doe IDENTIFIED BY password123;

Step 2: Grant privileges to the user GRANT CONNECT TO john_doe;

GRANT SELECT, INSERT, UPDATE ON employees TO john_doe;

Verify the granted privileges

SELECT * FROM ALL_TAB_PRIVS WHERE GRANTEE = 'JOHN_DOE';

Step 3: Revoke a privilege from the user

REVOKE UPDATE ON employees FROM john_doe;

Verify the updated privileges

SELECT * FROM ALL_TAB_PRIVS WHERE GRANTEE = 'JOHN_DOE';

FLASHBACK AND PURGE

Oracle has introduced ”Recycle bin” feature oracle log to store all the dropped objects. A
user drops a very important table accidentally. Oracle log Recycle bin feature the user
can easily restore the dropped object.

*To enable the recycle bin:

SQL> Alter system set recycle bin = on;
or
SQL> Alter session set recycle bin = on;

To view the recycle bin use the follows command:

To view the recycle bin use the follows command:
SQL> show recycle bin;
 Ex:

Create table test_inet1(val number(2));

SQL> insert into test_inet1(val)
values(10); SQL>drop table test_inet1;
Print the recycle bin;
Restore the objects back to data base:
FLASHBACK table <<table_name>> to before drop;

Ex: Flashback tabe test_inet1 to before drop;

SQL> select * from test_RBIN;

*Clearing the recycle bin (RB):

To clear the recycle bin the following statement can be used.
SQL> purge table <<table_name>>;
SQL> purge table student6;

==================================================
CLAUSES:

1. SELECT Clause

The SELECT clause is used to specify the columns that you want to retrieve from a table.

EXAMPLE: SELECT first_name, last_name FROM employees;

2. FROM Clause

The FROM clause specifies the table from which to retrieve the data.

Example: SELECT * FROM employees;

3. WHERE Clause

The WHERE clause is used to filter records based on a specified condition.

Example: SELECT first_name, last_name FROM employees WHERE department_id = 10;

4. ORDER BY Clause

The ORDER BY clause is used to sort the result set in either ascending (ASC) or descending
(DESC) order.

Example: SELECT first_name, last_name FROM employees ORDER BY last_name ASC;

5. GROUP BY Clause

The GROUP BY clause groups rows that have the same values into summary rows.
Example: SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

6. HAVING Clause

The HAVING clause is used to filter groups based on a specified condition. It is often used
with the GROUP BY clause.

Example: SELECT department_id, COUNT(*) FROM employees GROUP BY department_id

HAVING COUNT(*) > 5;

========================================================

INTEGRITY
CONSTRAINS:
Constrainsts are rules which are applied on tables.
Constrains helps in improving the accurency and quality of the data base.
They are five types of constrainst.
1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY or REFERENTIAL INTEGRITY CONSTRAINS
5. CHECK

1. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have a NULL value.

Example: CREATE TABLE employees ( employee_id NUMBER, first_name VARCHAR2(50)

NOT NULL, last_name VARCHAR2(50) NOT NULL, department_id NUMBER );

2. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

Example: CREATE TABLE employees ( employee_id NUMBER, email VARCHAR2(100)

UNIQUE, first_name VARCHAR2(50), last_name VARCHAR2(50) );

3. PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. A table can have only
one primary key, which can be a single column or a combination of columns.

Example: CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, first_name

VARCHAR2(50), last_name VARCHAR2(50), department_id NUMBER );
4. FOREIGN KEY Constraint

The FOREIGN KEY constraint ensures that the value in a column (or group of columns)
matches the value in a column (or group of columns) in another table.

Example: CREATE TABLE departments ( department_id NUMBER PRIMARY KEY,

department_name VARCHAR2(50) );

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, first_name

VARCHAR2(50), last_name VARCHAR2(50), department_id NUMBER, CONSTRAINT
fk_department FOREIGN KEY (department_id) REFERENCES departments (department_id)
);

5. CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy a specific condition.

Example: CREATE TABLE employees ( employee_id NUMBER, first_name VARCHAR2(50),

last_name VARCHAR2(50), salary NUMBER, CONSTRAINT chk_salary CHECK (salary > 0) );

6. DEFAULT Constraint

The DEFAULT constraint provides a default value for a column when none is specified.

Example:

CREATE TABLE employees ( employee_id NUMBER, first_name VARCHAR2(50), last_name

VARCHAR2(50), hire_date DATE DEFAULT SYSDATE );

=============================================
ALTER COMMAND:
The ALTER command allows us to altering the structure of object like -add a new
column,drop a column,Rename column,add constraint etc.
--------------------------------------------------------------------------------
[Link] to add a column:
syntax:
Alter table <table_name> add <field_name> <data-type>;

SQL> create table emp(eid number,emp varchar2(10));

SQL> insert into emp values(10,'raj');
SQL> insert into emp values(20,'rekha');
SQL> insert into emp values(30,'Divi');
SQL> insert into emp values(40,'Divya');
SQL> alter table emp add esal number;

can add two columns at once:

-----------------------------------
Example: alter table emp add(ehelp number,eadd varchar2(10));
[Link] a column:
alter table <table_name>modify<field_name><data-type>;

example:
alter table emp modify ename varchar(12);
SQL> insert into emp(ename) values('Dhanalakshmi');

[Link] to delete a column:

syntax:
alter table <table-name>drop column <field-name>;
SQL> alter table emp drop column ename;

[Link] to rename the column name;

syntax:
Alter table <table-name> rename <existing field-name> to <new field-name>;
SQL> alter table emp rename column esal to esalary;

[Link] to Rename the table-name;

syntax:`
alter table<existing table-name>rename to <new table-name>;
SQL> alter table emp rename to Employee;

[Link] to add a constraint by the help Alter command;

syntax:
Alter table<table-name>add constraint<constraint-name><constraint-type><field-name>;

Example:
To ensure that the salary column in the employee table always has a value greater than 0:
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);
=========================================================
Operators:
Operators are some characters or keywords which is use on expression to save any
operation.
The operators of sql can be categorzed into 3 types:
[Link] opertors:
(+,-,++,--,!)
[Link] operators:
-->Arithemetic operators
-->Concatenation operators
-->Relational operators
-->Logical operators
-->Another relational operator
[Link] operator:
-->union
-->union all
-->minus
-->intersect
---------------------------------------------------------------------------------
1>Arithmetic operators:
+,-,*,/
Example:
SQL> update employee set esal=esal+3000 where eid=30;
SQL> update employee set esal=esal-7000 where eid=10;
SQL> update employee set esal=esal*2000 where eid=40;
SQL> update employee set esal=esal/2 where eid=50;
--------------------------------------------------------------------------------
[Link] operators:
(=,>,<=,>=,<)
---------------------------------------------------
Example:
SQL> select * from employee esal where esal<12000;
SQL> select * from employee esal where esal>12000;
SQL> select * from employee esal where esal<=12000;
SQL> select * from employee esal where esal>=12000;
SQL> select * from employee esal where esal=12000;
----------------------------------------------------------------------------------
[Link] operator:
(::)
SQL> update employee set ename=ename||'Roy' where eid=10;
--------------------------------------------------------------------------------------
SQL> select * from employee;

EID ENAME ESAL DJOIN

---------- -------------------- ---------- ---------
10 RiyaRoy 13000 14-AUG-00
20 Roja 17000 01-JUN-00
30 Ali 15000 20-FEB-01
40 Ajay 18000 15-JAN-00
50 Ali 12000 12-JAN-00
60 Ajay 10000 15-JAN-00
----------------------------------------------------------------------------------
[Link] operators:
and,or,not
==================
and:Both columns should be true

SQL> select * from employee where esal=12000 and djoin='12-jan-2000';

EID ENAME ESAL DJOIN

---------- -------------------- ---------- ---------
50 Ali 12000 12-JAN-00
-------------------------------------------------------------------------------------
or :atleast one condition should be true
SQL> select * from employee where esal=10000 or djoin='14-aug-2000';

EID ENAME ESAL DJOIN

---------- -------------------- ---------- ---------
10 RiyaRoy 13000 14-AUG-00
60 Ajay 10000 15-JAN-00
-----------------------------------------------------------------------------------
Not: except that condition remaining will be displayed

SQL> select * from employee where not djoin='15-jan-2000';

EID ENAME ESAL DJOIN

---------- -------------------- ---------- ---------
10 RiyaRoy 13000 14-AUG-00
20 Roja 17000 01-JUN-00
30 Ali 15000 20-FEB-01
50 Ali 12000 12-JAN-00
----------------------------------------------------------------------------------
Another relational operator
Distinct,ALL,IN,NOT IN,BETWEEN,LIKE,NOT LIKE,NULL,NOT NULL
[Link] :
It allows us to retrieve unique records of table.
----------------------------------------------------------------------
Before:
SQL> select * from employee;
---------------------------------------
After:
SQL> select Distinct * from employee
[Link]:
It retrieve all the information of table including duplicate records.

All:
SQL> select all * from employee;
--------------------------------------------------------------------------
IN: It is used to retrieve more than one records of table.

SQL> select * from employee where eid in(20,70,30);

---------------------------------------------------------------------------
NOT IN:except that data remaining it will print.
SQL> select * from employee where eid not in(20,70,30);
---------------------------------------------------------------------------
Between :It is used to retrieve records of table between two ranges.
SQL> select * from employee where eid between 20 and 40;
SQL> select * from employee where eid not between 20 and 40;
---------------------------------------------------------------------------
#Like can be applied only for string type characters only.
Like:Like operator is used to retrieve pattern wise information from a table,it contains two
wild card character.
----->%-->Zero or more character.
----->_--->only for single character

SQL> select * from employee where ename like '%a';

SQL> select * from employee where ename like 'a%';
SQL> select * from employee where ename like '%a%';

SQL> select * from employee where ename like '_n';

SQL> select * from employee where ename like 'n_';
SQL> select * from employee where ename like '_n_';
SQL> select * from employee where ename is null;
SQL> select * from employee where ename is not null;
------------------------------------------------------------------------------
Not like:
Display unselected records
----------------------------------------------------------------------------
SQL> select * from employee where ename not like 'a%';

Set operators:
The set operators allow us to perform algebric operation among two or more tables or
relations and produce a result depending on its operations.
This operations contains four operators:
[Link]
[Link] all
[Link]
[Link]
UNION:
Select * form student10;

Sno sname marks

101 Arun 40
102 Arun 50
103 Arun 69

Select * from student20;

Sno sname marks

103 Arun 90
104 Arun 60

Union Syntax: (no duplicates)

Select sno from student 10 Union Select sno from student 20;

1. UNION ALL:
Union All Syntax: (All rows)
Select sno from student 10 Union All Select sno from student 20;

2. INTER SECT:
Insert Sect Syntax: (common rows)
Select sno from student 10
Intersect Select sno from student 20;
4: MINUS:
Select sno from student 10 Minus Select sno from student 20;
 Select sno from student 20 Minus Select sno from student10;

FUNCTIONS: Functions will manuplate the data items and gives the result. They are
two types of functions.
1. Group functions or multiple row functions
2. Scalar functions or single row function

1) Group functions or multiple row functions: This functions act on group of rows.
i. AVG: select AVG(sal) from emp;
ii. SUM: select SUM(sal) from emp;
iii. MAX: select MAX(sal) from emp;
iv. MIN: select MIN(sal) from emp;
v. COUNT(*): select COUNT(*) from emp; //Return total [Link] rows in the table
[Link](EXPR): Return [Link] values present in the column.

Dual table: It is a dummy table which is generally used to perform some calculation is
seeing to the system date and etc. Dual table is collection of one row and one column
with 'X' in it.
Ex:
Select 10+20 from dual;
Select 20+40, 50+60 from dual;
2) Scalar functions or single row functions: Scalar function are decided into
four types. They are given that.
i. Character functions
ii. Number functions
iii. Data functions
iv. Conversion functions
A) Character functions:
Upper: converts into lower case to upper case.
Ex: Select upper('oracle') from dual;

Lower: This function is convert to the upper to lower case.

Ex: Select lower('ORACLE') from dual;

INITCAP: First letter is capital and reaming letters are small letters.
Ex: Select INITCAP('oracle training') from dual;
 LENGTH: Returns length of the string.

Ex: Select LENGTH('oracle') from dual;

LPAD: pads the character towards the left side.

Ex: select LPAD('oracle',10,'z') from dual;

RPAD: Rpad the character towards the right side.

Ex: Select RPAD('ORACLE',10,'X') from dual;

LTRIM:
Ex: Select LTRIM('zzoracle','z') from dual;

RTRIM:
Ex: Select RTRIM('ZORACLEZZZ','Z') from dual;
TRIM: Removes the specified characters from both sides.
Ex: Select TRIM('z' from 'zzoraclezz' ) from
dual;

INSTR: Returns the passion of the string

Ex: Select INSTR('ORACLE','A') from dual;

SUBSTR: Returns the part of the string.
Ex: Select SUBSTR('ORACLE',2,3) from dual;
CONCAT: To join the two words. It will accept only two character.

Ex: Select concat('MAGA','STAR') from dual;

B)Number functions:
ABS: Returns absolute values

Ex: Select ABS(−40) from dual;

Select ABS(40) from dual;

SQRT: Returns the squawroot values.

Ex: Select SQRT(25) from dual;

MOD(A,B): Returns the MOD vaues.

Ex: select MOD(10,3) from dual;

POWER(A,B):
Ex: Select POWER(2,5) from dual;

CEIL:
Ex: Select CEIL(40.9) from dual;

FLOOR:
Ex: Select FLOOR(40.9) from dual;

TRUNC:(TRUNCATE) Remove the decimal points.

Ex: Select TRUNC(40.9) from dual;

ROUND: Rounds of the nearest value.

Ex: Select ROUND(40.9) from dual;

GREATEST:
Ex: Select GREATEST(100,200,300) from dual;

LEAST:
Ex: Select LEAST(100,200,300) from dual;

C)Date functions: They are four data functions.

ADD_MONTHS

MONTHS_BETWEEN

NEXT_DAY

LAST_DAY

A. ADD_MONTHS: ADD_MONTHS of months to the given date

Ex: Select ADD_MONTHS(SYSDATE,12) from dual;

B. MONTH_BETWEEN: Returns number of months b/w given the two months.

Ex: Select MONTHS_BETWEEN('11−JAN−05','11−JAN−04') from dual;

C. NEXT_DAY: Returns date of the specified date.

Ex: Select NEXT_DAY(SYSDATE,'MONDAY') from dual;

D. LAST_DAY: Returns the last day of the month.

Ex: Select LAST_DAY(SYSDATE) from dual;

D)Conversion functions:
Conversion functions are one data type to another data type conversion.
 They are three conversion functions

 TO_CHAR
 TO_NUMBER
 TO_DATE
1)TO_CHAR: This functions is having two functionalities.

a)Number to_char: This function is used only $ or u−rows and number is used 9.

Ex: Select eno,ename, TO_CHAR(sal,'9,999') from emp;

Select eno,ename, TO_CHAR(sal,'99,99') from emp;
Select eno,ename, TO_CHAR(sal,'8,888') from emp; // invalid number format

b)Date to_char:

Ex: Select eno,ename,hiredate from emp;

Select eno,ename, TO_CHAR(HIREDATE,'DD−MM−YY') from emp;
Select eno,ename, TO_CHAR(HIREDATE,'DD−MM−YYYY')fromemp;
Select SYSDATE from dual;
Select TO_CHAR(SYSDATE,'DD−MONTH−YY') from dual;
Select TO_CHAR(SYSDATE,'DAY') from dual;
Select TO_CHAR(SYSDATE,'YYYY') from dual;
Select TO_CHAR(SYSDATE,'MM') from dual;
Select TO_CHAR(SYSDATE,'DDD') from dual;
Select TO_CHAR(SYSDATE,'DD') from dual;
Select TO_CHAR(SYSDATE,'MON') from dual;
Select TO_CHAR(SYSDATE,'DY') from dual;
Select TO_CHAR(SYSDATE,'DD−MM−YY HH:MI:SS') from dual;

2)TO_NUMBER:
Ex: Select TO_NUMBER(LTRIM('$1400','$')) + 10 from dual;

3)TO_DATE: This function is used to convert character values to data value.

Ex: ADD_MONTHS

Select ADD_MONTH('11−JAN−05',2) from dual;

====================================================================
*Table alias:
Table alias is an alternate name given to a table.
By using a table alias length of the table reduces and at the same time performance is
maintains.
Table alias are create in same clause can be used in select clause as well as where
clause.
Table alias is temporary once the query is executed the table alias are losed.
Ex

Select [Link], [Link], [Link], [Link], [Link], [Link]

from emp E, Dept D
where [Link] = [Link];

============================================================
MERGE: MERGE command is used as a combination of insert and update.
create table destination(id number(5) GENERATED BY DEFAULT AS IDENTITY,name
varchar2(20),price number(5));
create table source(id number(5) GENERATED BY DEFAULT AS IDENTITY,name
varchar2(20),price number(5));

insert into destination values(1,'pen',50);

insert into destination values(2,'egg',6);
insert into destination values(3,'bread',20);

insert into source values(1,'pen',40);

insert into source values(2,'milk',30);
insert into source values(4,'sugar',60);

MERGE INTO destination d USING source s ON ([Link] = [Link]) WHEN MATCHED THEN UPDATE
SET [Link] = [Link], [Link] = [Link] WHEN NOT MATCHED THEN INSERT
([Link],[Link]) VALUES ([Link], [Link]);
========================================================================
 JOINS: joins are used to retrieve the data from multiple tables.
Types of Joins:
1. EQUI_JOIN
2. NON EQUI_JOIN
3. SELF JOIN
4. OUTER JOIN
Right outer join

Left outer join

Full outer join

1.EQUI_JOIN: when tables are joined basing on a common column it is called

EQUI_JOIN.
In EQUI_JOINS we along use to equal to operator in join condition
Ex: select empno, ename, dname
from emp, dept
where [Link] = [Link];
NON EQUI JOIN: When we do not use NON EQUI JOIN to operator in the join
condition is NON EQUI JOIN.
Ex:

Select * from SALGRADE;

GRADE LOSAL HISAL

1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

Select [Link], [Link], [Link], [Link] from emp e, salgrade s

where [Link] BETWEEN [Link] AND hisal;

EMPNO ENAME GRADE

7369 SMITH 1
7876 ADAMS 1
7900 JAMES 2

SELF JOIN: When a table is joining to it self it is called self join. In self joins we need to
create two table aliases for the same table.
Example:
Select empno, ename, job, mgr, from emp;
 Select [Link], [Link], [Link], [Link] from emp e, emp m
where [Link] = [Link];
CARTESIAN PRODUCT:
When tables are joined without any join condition it is called Cartesian product. In the
result we get all possible combination.

Select [Link], [Link], [Link], [Link], [Link], [Link] from

emp e, dept d;

OUTER JOINS: It is extension of EQUI JOINS.

In outer joins we get match as well as non matching rows.

1. RIGHT OUTER JOIN:
Example:
Select [Link], [Link], [Link], [Link], [Link], [Link]
from emp e RIGHT OUTER JOIN dept d ON([Link] = [Link]);
2. LEFT OUTER JOIN:
Example:
Select [Link], [Link], [Link], [Link], [Link], [Link]
from emp e LEFT OUTER JOIN dept d ON([Link] = [Link]);
3 FULL OUTER JOIN:
Example:
Select [Link], [Link], [Link], [Link], [Link], [Link]
from emp e FULL OUTER JOIN dept d ON([Link] = [Link]);

======================================

INDEXES:
In Oracle Database, the INDEX is a database object that provides a fast, efficient method of
locating rows in a table based on the values in one or more columns. An index is similar to
an index in the back of a book, where you can quickly find the page number associated with
a particular topic.
Purpose: The primary purpose of an index is to improve the speed of data retrieval
operations on a database table. By creating an index on one or more columns, Oracle can
locate and retrieve the rows more quickly than if there were no index.

Structure: An index is a separate database object that contains an entry for each unique
value in the indexed columns, along with a pointer to the corresponding row in the table.
Oracle uses a B-tree (balanced tree) structure for most indexes, which allows for efficient
search and retrieval.
Types of Indexes:

1. B-tree Index

A B-tree (balanced tree) index is the default type of index in Oracle. It is suitable for most
queries.

Example:

CREATE INDEX emp_last_name_idx ON employees (last_name);

2. Bitmap Index

A bitmap index is used primarily for columns with a low cardinality (few distinct values).

Example:

CREATE BITMAP INDEX emp_dept_idx ON employees (department_id);

3. Unique Index

A unique index ensures that the indexed column or columns do not have duplicate values.

Example:

CREATE UNIQUE INDEX emp_email_idx ON employees (email);

4. Composite Index

A composite index (also called a concatenated index) is an index on multiple columns.

Example:

CREATE INDEX emp_name_dept_idx ON employees (last_name, department_id);

5. Function-Based Index

A function-based index is created on an expression or function that involves one or more

columns in the table.

Example:

CREATE INDEX emp_upper_last_name_idx ON employees (UPPER(last_name));

6. Domain Index

A domain index is specific to an application and can be created to support specific types of
data, such as spatial or text data.

Example:
CREATE INDEX emp_domain_idx ON employees (employee_id) INDEXTYPE IS
[Link];

7. Clustered Index

Oracle does not have clustered indexes in the same sense as some other database systems.
However, Oracle does support index clustering through the use of index-organized tables
(IOTs).

Example:

CREATE TABLE emp_iot ( employee_id NUMBER, first_name VARCHAR2(50), last_name

VARCHAR2(50), department_id NUMBER, PRIMARY KEY (employee_id) ) ORGANIZATION
INDEX;

8. Reverse Key Index

A reverse key index is a type of B-tree index where the bytes of the indexed column are
reversed. This can help avoid index block contention in certain scenarios.

Example:

CREATE INDEX emp_rev_id_idx ON employees (REVERSE(employee_id));

9. Invisible Index

An invisible index is an index that is ignored by the optimizer unless explicitly referenced by
a hint.

Example:

CREATE INDEX emp_invisible_idx ON employees (first_name) INVISIBLE;

10. Descending Index

A descending index stores the data in descending order, which can be useful for queries that
frequently request data in descending order.

Example:

CREATE INDEX emp_desc_salary_idx ON employees (salary DESC);

 Synonym: it is an alternate name given to an object.

Syntax: create synonym <Synonym_name> for <Table_name);

Ex:

Create synonym E1 for emp;

Synonym helps in reducing the length of the query.

Synonym is used instead of table names for all the commands.
Ex:

Select * from E1;

Query to see all synonyms:

SQL> SELECT * FROM USER_SYNONYMS
WHERE TABLE_OWNER='SYSTEM'
AND TABLE_NAME='EMPLOYEE';

To drop a synonym:

SQL> DROP SYNONYM E1;

USER DEFINED Functions:

A function is a named PL/SQL block which must and should return a value.
Syntax: create or replace function<FUNCTION_NAME>(VAR1 datatype,
var2 datatype,…….,varn datatype);
Return datatype
IS
Begin
−−−−−−−
−−−−−−−
−−−−−−− END;
/

Ex: Create a function which accepts two numbers and returns the sum?
Create or replace function ADD_NUM_FUN(A number, B number)
Return number
IS
C number;
Begin
C := A + B;
Return C;
END;
/
Calling:
Declare
N number;
 Begin
N := ADD_NUM_FUN(10,20);
DBMS_OUTPUT.PUT_LINE(N)
;END;

We can invoke the function using 'select' statement.

Ex: select ADD_NUM_FUN(50,10) from dual;

Functions can be invoked from expression.

Ex: select 100 + ADD_NUM_FUN(50,10) from dual;
We can not have DML commands inside a function.
If a function has DML commands, we get the error when we invoke it. Functions are
preferred to perform calculations.

Triggers:

A Trigger is a PL/SQL block which is executed automatically basing on an event. Triggering

events are inserted, update, delete. Triggers are used to enforce business rules.
Trigger timing can be before, after, instead of
Syntax:
Create or replace trigger <TRIGGER_NAME><TIMMING><EVENT> ON <OBJECT_NAME>
Begin
−−−−−−−−−−−

−−−−−−−−−−−

−−−−−−−−
−−− END;

Ex:

Create table dept( dept_id number PRIMARY KEY,dname VARCHAR2(50),salary NUMBER);

create or replace trigger trg1

after insert on dept
begin
DBMS_OUTPUT.PUT_LINE('hello');
END;
/

Trigger created
SQL> insert into dept values(65,'Admin',6000);
hello

Trigger can be created on multiple events

Ex:
Create or replace trigger trg2
After insert or update or
delete on dept
Begin
If inserting then
DBMS_OUTPUT.PUT_LINE('thank you for inserting');
ElsIf updating then
DBMS_OUTPUT.PUT_LINE('thank you for updating');
Else
DBMS_OUTPUT.PUT_LINE('thank you for deleting');
End If;
END;
/
SQL> insert into dept values(68,'arun',6000);
Thank you for inserting
SQL> deleting from dept where deptno = 68;
Thank you for deleting

*Triggers are divided into two types

1. Statement level trigger
2. Row level trigger

*Statement level trigger:

These triggers are executed only once irrespective of number of rows affected by the
event. By default every trigger is a statement level.
*Row level trigger:
These triggers are executed for every row which is affected by the event (multiple
numbers of times). We can create a row level trigger by using ”FOR EACH ROW” clause.

Ex: Step 1: Create the employees Table

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, name VARCHAR2(50),

salary NUMBER );

Step 2: Create the salary_audit Table

CREATE TABLE salary_audit ( audit_id NUMBER PRIMARY KEY, employee_id NUMBER,
old_salary NUMBER, new_salary NUMBER, change_date DATE );
Step 3: Create the Trigger

We'll create a trigger that records any changes to the salary column in the employees table.

CREATE OR REPLACE TRIGGER salary_change_trigger BEFORE UPDATE OF salary ON

employees FOR EACH ROW BEGIN INSERT INTO salary_audit (audit_id, employee_id,
old_salary, new_salary, change_date) VALUES (salary_audit_seq.NEXTVAL,
:OLD.employee_id, :[Link], :[Link], SYSDATE); END; /

Step 4: Create Sequence for salary_audit

Create sequence salary_audit_seq start with 1 increment by 1;

To automatically generate unique audit_id values, we need a sequence:

Testing the Trigger

Insert a Test Employee

INSERT INTO employees (employee_id, name, salary) VALUES (1, 'John Doe', 50000);

Update the Employee's Salary

UPDATE employees SET salary = 55000 WHERE employee_id = 1;

Check the salary_audit Table

SELECT * FROM salary_audit;

You should see an entry in the salary_audit table reflecting the old and new salary values
for the updated employee.

*Instead of triggers:
Instead of triggers are created on view.
By using instead of trigger we can execute insert command on a view
EXAMPLE:
CREATE TABLE department1 (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(50)
);
CREATE TABLE employee2(
employee_id NUMBER PRIMARY KEY,
employee_name VARCHAR2(50),
department_id NUMBER,
FOREIGN KEY (department_id) REFERENCES department1(department_id)
);

INSERT INTO department1 (department_id, department_name) VALUES (1, 'HR');

INSERT INTO department1 (department_id, department_name) VALUES (2, 'Finance');

INSERT INTO employee2 (employee_id, employee_name, department_id) VALUES (1,
'John Doe', 1);

INSERT INTO employee2 (employee_id, employee_name, department_id) VALUES (2,

'Jane Smith', 2);

CREATE VIEW emp_dept_view AS

SELECT e.employee_id, e.employee_name, d.department_name
FROM employee2 e
JOIN department1 d ON e.department_id = d.department_id;

CREATE OR REPLACE TRIGGER emp_dept_view_insert_trigger

INSTEAD OF INSERT ON emp_dept_view

FOR EACH ROW

BEGIN
DECLARE

v_department_id NUMBER;
BEGIN

-- Get the department_id for the given department_name

SELECT department_id INTO v_department_id

FROM department1
WHERE department_name = :NEW.department_name;
-- Insert the new employee into the employee2 table

INSERT INTO employee2 (employee_id, employee_name, department_id)

VALUES (:NEW.employee_id, :NEW.employee_name, v_department_id);
EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20001, 'Department not found');

END;
 END;

Testing the Trigger

Try inserting a new row into the view.
INSERT INTO emp_dept_view (employee_id, employee_name, department_name)
VALUES (3, 'Alice Johnson', 'HR');
Verify the insertion by querying the employee2 table:
SELECT * FROM employee2;

SUBQUERIES:

Subqueries are used to get the result based on unknown values. They are different type.

1. Single Row subquery

2. Multiple Row subquery

3. Multiple column subquery

4. Co−related subquery

5. Scalar subquery

6. Inline view

*Single Row Subquery:

When subquery returns one row (1 value). It is called Single RowSubquery.

Ex: write a query to display details are having salary > 'ALLENS' sal ?

Select * from emp where sal > (select sal from emp where ename = 'ALLEN');

Multiple Row Subquery:

When subquery returns multiple rows. It is called multiple row salary.

Note: we should multiple row operators with multiple row subqueries. They are three
multiple row operators.

1. IN

2. ANY

3. ALL
EXAMPLE:

*ALL : Select * from emp

Where sal > ALL(Select sal from emp

Where deptno = 30);

*ANY: Select * from emp where sal > ANY(select sal from emp

where deptno = 30);

*IN: Select * from emp where ename IN('ALLEN', 'KING','FORD');

*MULTIPLE COLUMN SUBQUERY:

When subquery return more then one column. It is called multiple column subquery. We
should use in operator with multiple column subqueries.

Ex:

Select * from emp where(job,sal) IN(select job, sal from emp where deptno = 30);

Co-RELATED SUBQUERY:

When subquery is executed in relation to parent query, it is called co−related subquery.

EXAMPLE:

write a query to display all the rows who are having salary grater than AVG salary his
department?

Select * from emp e

where sal > (select AVG(sal) from emp where deptno = [Link]);

In co−related subquery, parent query is executed first and then subquery is executed in
relation to result of parent query.

SCALAR subquery: when we use subquery in the select clause. It is called as Scalar
subquery.

*write a query to display following output?

Deptno Dname loc sumsal

10 Accounting New York 8750

20 Research Dallas 10875

30 Sales Chicago 9400

40 Operations Boston −−−−−−

Select deptno, dname, loc, (Select sum(sal) from emp where deptno = [Link]) Sum_sal
from dept d;

Scalar subquery are also called sub select.

INLINE VIEW:

When a subquery is used in from clause. It is called INLINE view.

EXAMPLE:

Select Rownum, empno, ename, sal, deptno from emp;

=================================================

Views:view can be called as carbon copy

View is a logical table based on one or more table or views.
The table upon which a view is base called base table.
Note:
[Link] doesnot contain any data itself.
[Link] are used for security purpose because they provide encapsulation of the name of
table.
[Link] is in the virtual table not stored permanently.
[Link] display only selected data.
[Link] is also known as 'stored select statement'.

Types of views:
[Link] view
[Link] view
[Link] view
[Link] only view
[Link] check option view
----------------------------------------------------------
[Link] view:
The view which contain in a sub-query that retrives from one base table or view is called
simple view.
syntax:
create view view-name as select * from table_name;

SQL> create table emp(eid number,ename varchar2(10),eadd varchar2(10));

SQL> insert into emp values(&eid,&ename,&eadd);
SQL> select * from emp;

EID ENAME EADD

---------- ---------- ----------
 10 ajay hyd
20 anshu canada
30 geetha uk
40 retha china
50 akash usa
60 senorita italy

SQL> create view emp_view as select * from emp;

View created.

SQL> select * from emp_view;

EID ENAME EADD

---------- ---------- ----------
10 ajay hyd
20 anshu canada
30 geetha uk
40 retha china
50 akash usa
60 senorita italy

6 rows selected.

SQL> insert into emp_view values(70,'karuna','ranchi');

1 row created.

SQL> select * from emp_view;

EID ENAME EADD

---------- ---------- ----------
10 ajay hyd
20 anshu canada
30 geetha uk
40 retha china
50 akash usa
60 senorita italy
70 karuna ranchi

7 rows selected.

SQL> select * from emp;

EID ENAME EADD

---------- ---------- ----------
10 ajay hyd
20 anshu canada
30 geetha uk
40 retha china
50 akash usa
60 senorita italy
70 karuna ranchi
7 rows selected.

SQL> commit;

Commit complete.

SQL> insert into emp values(80,'roy','delhi');

1 row created.

SQL> select * from emp;

EID ENAME EADD

---------- ---------- ----------
10 ajay hyd
20 anshu canada
30 geetha uk
40 retha china
50 akash usa
60 senorita italy
70 karuna ranchi
80 roy delhi

8 rows selected.

SQL> select * from emp_view;

EID ENAME EADD

---------- ---------- ----------
10 ajay hyd
20 anshu canada
30 geetha uk
40 retha china
50 akash usa
60 senorita italy
70 karuna ranchi
80 roy delhi
--------------------------------------------------------

SQL> create view emp_view as select * from emp where eadd='canada';

create view emp_view as select * from emp where eadd='canada'
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
#you cannot create a view with same object
----------------------------------------------------------
With these we can create a view:
SQL> create or replace view emp_view as select * from emp where eadd='ca
nada';

----------------------------------------------------------
SQL> select * from emp_view;
 EID ENAME EADD
---------- ---------- ----------
20 anshu canada
----------------------------------------------------------
#view will support mostly on dml commands
----------------------------------------------------------
tried to insert values in emp_view but wont save in emp_view table but will stored in emp
table;

SQL> insert into emp_view values(90,'raghu','chennai');

1 row created.

SQL> select * from emp_view;

EID ENAME EADD

---------- ---------- ----------
20 anshu canada
---------------------------------------------------------
SQL> select * from emp;

EID ENAME EADD

---------- ---------- ----------
10 ajay hyd
20 anshu canada
30 geetha uk
40 retha china
50 akash usa
60 senorita italy
70 karuna ranchi
80 roy delhi
90 raghu chennai
----------------------------------------------------------
if i want to insert same values in the emp_view then it is going to add definetly;
SQL> select * from emp_view;

EID ENAME EADD

---------- ---------- ----------
20 anshu canada
100 raghu canada

----------------------------------------------------------
Now iam gonna update the row:
SQL> update emp_view set ename='arjun' where eid=100;

1 row updated.

SQL> select * from emp_view;

EID ENAME EADD

---------- ---------- ----------
20 anshu canada
 100 arjun canada

=========================================================
we cannot update the same record which is already there in the emp table
SQL> update emp_view set ename='parvathi' where eadd='ranchi';

0 rows updated.
In these no rows will be updated ,we cannot update the same data which is already there in
the table;
SQL> select * from emp;

EID ENAME EADD

---------- ---------- ----------
10 ajay hyd
20 anshu canada
30 geetha uk
40 retha china
50 akash usa
60 senorita italy
70 karuna ranchi
80 roy delhi
90 raghu chennai
100 arjun canada

----------------------------------------------------------
Delete:It will give permission to delete
SQL> delete from emp_view where eid=20;

1 row deleted.

SQL> select * from emp_view;

EID ENAME EADD

---------- ---------- ----------
100 arjun canada
----------------------------------------------------------
SQL> select * from emp;

EID ENAME EADD

---------- ---------- ----------
10 ajay hyd
30 geetha uk
40 retha china
50 akash usa
60 senorita italy
70 karuna ranchi
80 roy delhi
90 raghu chennai
100 arjun canada
----------------------------------------------------------
we cannot delete the eadd which is same in the emp table;
SQL> delete from emp_view where eadd='delhi';
0 rows deleted.

---------------------------------------------------------
if i give anyone my table we will get to know what the changes there are doing in the
[Link] cann also drop the view table.
SQL> delete from emp_view where eadd='delhi';

0 rows deleted.

SQL> drop view emp_view;

View dropped.

SQL> select * from emp_view;

select * from emp_view
*
ERROR at line 1:
ORA-00942: table or view does not exist
----------------------------------------------------------
we can take back the permission by droping the view [Link] are known as simple view.
---------------------------------------------------------

[Link] view:

complex view contain sub queries where sub queries contain query.
The view which is constructed with the help of more than one base table is known as
complex view;

syntax:
create view view-name as select *
from table_name1 union select * from table_name;
-------------------------------------------------------

#for these you need to create two tables:employee1 and [Link] performing using
set operator so you need to create both the column table should be equal.
----------------------------------------------------------
SQL> select * from employee1;

EID ENAME EADD

---------- ---------- ----------
10 kanna UK
20 laya paris
40 jevan jaipur
70 kalla kerela
50 neha kerala

SQL> select * from department1;

DEPTEID DNAME DOJ

---------- ---------- ----------
20 sale 20-aug-19
30 Hr 24-feb-23
40 account 18-jan-18
 50 loan 12-aug-21

SQL> create view vname as select * from employee1 union select * from department1;

View created.

SQL> select * from vname;

EID ENAME EADD

---------- ---------- ----------
10 kanna UK
20 laya paris
40 jevan jaipur
70 kalla kerela
50 neha kerala
20 sale 20-aug-19
30 Hr 24-feb-23
40 account 18-jan-18
50 loan 12-aug-21

-----------------------------------------------------------you can use orderby clause,group by

clause also in these complex view;
----------------------------------------------------------
[Link] only view:
It only allows to perform read operation on base [Link] restricts to perform write operation.
Note:we cannot insert or update or delete the data.
After adding the data into emp table we can read the rview table but we cannot add in the
rview table directly.
and also it will give you the permission without any condition.
----------------------------------------------------------
syntax:
create view view_name as select * from table_name with read only;
SQL> create view rview as select ename,eadd from employee1 with read only;
--------------------------------------------------------

SQL> select * from department1;

DEPTEID DNAME DOJ

---------- ---------- ----------
20 sale 12-jan-20
40 hr 20-jan-19
60 account 14-jul-18
60 account 14-jul-18
SQL> select * from employee1;

EID ENAME EADD

---------- ---------- ----------
20 kanna canada
30 radhika vrindawan
40 chaitu canada
70 avika usa
40 deepu europe
----------------------------------------------------------
SQL> create view rview as select ename,eadd from employee1 with read only;

View created.

SQL> select * from rview;

ENAME EADD
---------- ----------
kanna canada
radhika vrindawan
chaitu canada
avika usa
deepu europe
----------------------------------------------------------

SQL> insert into rview(ename,eadd) values('rekha','paris');

insert into rview(ename,eadd) values('rekha','paris')
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
----------------------------------------------------------
#we can insert into employee table but not in rview.
SQL> insert into employee1 values(80,'ghana','kerela');
SQL> select * from employee1;

EID ENAME EADD

---------- ---------- ----------
20 kanna canada
30 radhika vrindawan
40 chaitu canada
70 avika usa
40 deepu europe
80 ghana kerela
----------------------------------------------------------
SQL> select * from rview;

ENAME EADD
---------- ----------
kanna canada
radhika vrindawan
chaitu canada
avika usa
deepu europe
ghana kerela
----------------------------------------------------------
[Link] check option:
In with check option view if the given condition satisfy in selected column then allows to
perform write operation otherise restricted.
syntax:
create view view_name as select * from table_name where condition with check option;
SQL> create view cview as select * from employee1 where eadd='canada' with check
option;

View created.

SQL> select * from cview;

EID ENAME EADD

---------- ---------- ----------
20 kanna canada
40 chaitu canada
------------------------------------------------------------------

SQL> insert into cview values(90,'divya','chennai');

insert into cview values(90,'divya','chennai')
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

------------------------------------------------------------------

SQL> insert into cview values(90,'divya','canada');

SQL> select * from cview;

EID ENAME EADD

---------- ---------- ----------
20 kanna canada
40 chaitu canada
90 divya canada
-------------------------------------------------------------------
SQL> select * from employee1;

EID ENAME EADD

---------- ---------- ----------
20 kanna canada
30 radhika vrindawan
40 chaitu canada
70 avika usa
40 deepu europe
80 ghana kerela
90 divya canada
-------------------------------------------------------------------
[Link] view:
Force view is used basically for the situation when we create a view using a table but the
table is not exists at that time we use force view.
#here table_name is not there in the database
syntax:
create force view view_name as select * from table_name;

SQL> create view v as select * from stu;

create view v as select * from stu
*
ERROR at line 1:
ORA-00942: table or view does not exist
-------------------------------------------------------------------

SQL> create force view v as select * from stu;

Warning: View created with compilation errors.

============================================

Sequences: sequences are database objects used primarily for generating unique
numeric values. They are often employed to generate primary key values for tables.

Syntax:

Create sequence <SEQUENCE_NAME> start with <value> increment by <value>;

*Using the Sequence: There are two pseudo to sequence.

1. NEXTVAL

2. CURRVAL

*NEXTVAL: Nextval is used to generate a number.

*CURRVAL: Currval is used to know the latest number generated.

Creating a Sequence

To create a sequence in Oracle, you use the CREATE SEQUENCE statement. Here’s a basic
syntax example:

CREATE SEQUENCE seq_example START WITH 1 INCREMENT BY 1 NOMAXVALUE;

seq_example: Name of the sequence.

START WITH 1: The sequence starts generating numbers from 1.
INCREMENT BY 1: Each successive number will increment by 1.
NOMAXVALUE: Specifies that there is no maximum limit for the sequence (it will continue to
generate numbers).

Create a table using the sequence and Insert using the sequence

After creating a sequence, you can use it to generate values for columns in tables. Typically,
sequences are used in INSERT statements to populate primary key columns:

CREATE TABLE example_table ( id NUMBER PRIMARY KEY, name VARCHAR2(50) );

INSERT INTO example_table (id, name) VALUES (seq_example.NEXTVAL, 'Example Name');

seq_example.NEXTVAL: NEXTVAL is a function that retrieves the next value from the
sequence seq_example. This value can then be used in an INSERT statement to generate a
unique identifier for id

Retrieving Current Sequence Value

You can also retrieve the current value of a sequence without advancing it using the
CURRVAL function:

SELECT seq_example.CURRVAL FROM dual;

This will return the current value of the sequence seq_example

Altering Sequence Properties

You can alter the properties of a sequence using the ALTER SEQUENCE statement. For
example, to change the increment value:

Example:

ALTER SEQUENCE seq_example INCREMENT BY 2;

This changes the increment value to 2, so the next value generated will be incremented by
2 instead of 1.

Dropping a Sequence

To drop (delete) a sequence from the database:

Example:

DROP SEQUENCE seq_example;

PROCEDURE: a procedure is a stored PL/SQL (Procedural Language/Structured Query

Language) program unit that performs one or more specific tasks. Procedures are useful for
encapsulating and organizing complex business logic that can be reused across different
parts of an application. A procedure is a PL/SQL block which is compiled and permanently
stored in the database for repeated execution.

Syntax:

create or replace procedure<procedure_Name> IS

Begin

−−−−−−−−−−−−−−

−−−−−−−−−−−−−− END;

/
EXAMPLE:

CREATE OR REPLACE PROCEDURE proc_example

IS

BEGIN

-- PL/SQL statements

DBMS_OUTPUT.PUT_LINE('Hello, this is a procedure.');

END proc_example;

CREATE OR REPLACE PROCEDURE: This creates a new procedure or replaces an existing

procedure if it already exists.
proc_example: Name of the procedure.
IS: This keyword begins the declaration section of the procedure.
BEGIN ... END: This is the body of the procedure where you write the PL/SQL statements.
In this example, it simply outputs a message using DBMS_OUTPUT.PUT_LINE

Executing a Procedure

To execute (run) a procedure in Oracle, you typically use the EXECUTE or EXEC command in
SQL*Plus

EXEC proc_example;

Altering a Procedure

To modify an existing procedure, you use the CREATE OR REPLACE PROCEDURE statement
again with the new definition.

Dropping a Procedure

To drop (delete) a procedure from the database:

DROP PROCEDURE proc_example;

Procedure can have 3 types of parameters

1. Inparameter

2. OutParameter

3. In Out parameter
1. IN Parameters

IN parameters are used to pass values into a procedure. These parameters are read-only
within the procedure, meaning their values cannot be modified.

Example:

CREATE OR REPLACE PROCEDURE greet_user(p_name IN VARCHAR2)

IS

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');

END greet_user;

Calling the Procedure:

BEGIN

greet_user('ZEENATH ');

END;

In this example, the p_name parameter is an IN parameter that takes the name of the user
and prints a greeting message.

2. OUT Parameters

OUT parameters are used to return values from a procedure. These parameters are write-
only within the procedure, meaning they can be assigned a value, which can be read by the
caller after the procedure execution.

Example:

Example:

CREATEOR REPLACE PROCEDURE get_square(p_number IN NUMBER, p_square OUT

NUMBER)

IS

BEGIN

p_square := p_number * p_number;

END;

Calling the Procedure:

DECLARE

l_square NUMBER;

BEGIN

get_square(5, l_square);

DBMS_OUTPUT.PUT_LINE('The square is: ' || l_square);

END;

In this example, the p_square parameter is an OUT parameter that returns the square of
the input number.

3. IN OUT Parameters

IN OUT parameters are used to pass values into a procedure and return updated values
back to the caller. These parameters can be read and modified within the procedure.

Example:

Example:

CREATE OR REPLACE PROCEDURE increment_value(p_value IN OUT NUMBER) IS

BEGIN

p_value := p_value + 1;

END ;

Calling the Procedure:

DECLARE

l_value NUMBER := 10;

BEGIN
 increment_value(l_value);

DBMS_OUTPUT.PUT_LINE('The incremented value is: ' || l_value);

END;

In this example, the p_value parameter is an IN OUT parameter that takes an input value,
increments it by 1, and returns the updated value.

Control statement: If − Then − Else

Declare

A number := 5;

Begin

DBMS_OUTPUT.PUT_LINE( 'welcome' );

If A > 10 Then

DBMS_OUTPUT.PUT_LINE( 'HELLO1' );

Else

DBMS_OUTPUT.PUT_LINE( 'HELLO2' );

END If;

DBMS_OUTPUT.PUT_LINE( 'THANK YOU' );

END;
*LOOPS: There are three types

1. Simple loop

2. While loop

3. for loop

1. Simple:

Declare

A number(2) := 1;

Begin

DBMS_OUTPUT.PUT_LINE( 'welcome' );

LOOP

DBMS_OUTPUT.PUT_LINE( 'HELLO1' );

DBMS_OUTPUT.PUT_LINE( 'HELLO2' );

Exit when A = 4;

A := A + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE( 'THANK YOU' );

END;

/
[Link]:

Declare

A number(2) :=1;

Begin

DBMS_OUTPUT.PUT_LINE( 'WELCOME' );

While A <=4 loop

DBMS_OUTPUT.PUT_LINE( 'HELLO1' );

DBMS_OUTPUT.PUT_LINE( 'HELLO2' );

A := A + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE( 'THANK YOU' ); END;

[Link] LOOP:

Declare

A number;

Begin

DBMS_OUTPUT.PUT_LINE( 'WELCOME' );

FOR A IN 1 .. 4 LOOP

DBMS_OUTPUT.PUT_LINE( 'HELLO1' );

DBMS_OUTPUT.PUT_LINE( 'HELLO2' );

END LOOP;

DBMS_OUTPUT.PUT_LINE( 'THANK YOU' );

END;

/
Cluster: a cluster is a schema object that contains one or more tables that have a common
column and are physically stored together. This is done to improve performance for certain types
of queries. When rows in multiple tables that are frequently accessed together are physically
stored together, it can reduce the amount of I/O operations needed to retrieve related data.

Key Concepts:

1. Cluster Key: The column or columns on which the tables in the cluster are joined.
2. Cluster Index: An index created on the cluster key to manage the storage of rows in the
cluster.

Types of Clusters:

1. Indexed Clusters: Rows are stored based on a cluster key value. A cluster index is
created on the cluster key.
2. Hash Clusters: Rows are stored based on a hash value of the cluster key.

Example of Indexed Cluster:

1. Create the Cluster:

CREATE CLUSTER emp_dept_cluster1 (department_id NUMBER);

[Link] Tables in the Cluster:

CREATE TABLE employee4 (

employee_id NUMBER PRIMARY KEY,
employee_name VARCHAR2(50),
department_id NUMBER
) CLUSTER emp_dept_cluster1 (department_id);
CREATE TABLE department4 (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(50)
) CLUSTER emp_dept_cluster1 (department_id);

[Link] the Cluster Index:

CREATE INDEX emp_dept_cluster_idx ON CLUSTER emp_dept_cluster1;

[Link] Data: INSERT INTO department4 (department_id, department_name)

VALUES (1, 'HR');

INSERT INTO department4 (department_id, department_name) VALUES (2,

'Finance');
INSERT INTO employee4 (employee_id, employee_name, department_id) VALUES
(1, 'John Doe', 1);

INSERT INTO employee4(employee_id, employee_name, department_id) VALUES

(2, 'Jane Smith', 2);

INSERT INTO employee4 (employee_id, employee_name, department_id) VALUES

(3, 'Alice Johnson', 1);

SELECT e.employee_id, e.employee_name, d.department_name

FROM employee4 e
JOIN department4 d ON e.department_id = d.department_id;

Example of Hash Cluster:

Create the Hash Cluster:

1. CREATE CLUSTER employees_cluster5

2. (employee_id NUMBER(10))
3. SIZE 8192
4. HASHKEYS 1000;

In this example:

 employees_cluster5 is the name of the hash cluster.

 employee_id is the key column.
 SIZE 8192 specifies the size of the data blocks.
 HASHKEYS 1000 indicates that there are 1000 unique hash values.

[Link] Tables in the Hash Cluster:

CREATE TABLE employees5

(employee_id NUMBER(10),

name VARCHAR2(50),

department VARCHAR2(50))

CLUSTER employees_cluster5(employee_id);

This creates the employees5 table within the employees_cluster5 hash cluster.

[Link] Data: Now, you can insert data into the employees5 table. Oracle will use the hash
function to determine where to store each row.
INSERT INTO employees5 (employee_id, name, department) VALUES (1, 'Alice', 'HR');

INSERT INTO employees5 (employee_id, name, department) VALUES (2, 'Bob', 'Finance');

INSERT INTO employees5 (employee_id, name, department) VALUES (3, 'Carol', 'IT');

Query Data:

 When you query the table based on the key column (employee_id), Oracle can quickly
locate the row using the hash function.

SELECT * FROM employees5 WHERE employee_id = 2;

 EXCEPTIONS: Exceptions are conditions that occur during the execution of a
program and disrupt the normal flow of operations. Runtime Errors are called as
Exceptions. They are
1. Predefined Exception or system defined
2. USER Defined Exception
PREDEFINED (SYSTEM-DEFINED) EXCEPTIONS
In Oracle PL/SQL, predefined exceptions are error conditions that are defined by the Oracle runtime
system. These exceptions are automatically raised by the runtime system when an error occurs. Here
are some common predefined exceptions with examples:

1) NO_DATA_FOUND:
EXAMPLE:

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25),

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID)

);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (6, 'Komal', 22, 'MP', 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (7, 'ZEENU', 28, 'HYD', 4100.00 );

DECLARE

c_id [Link]%type := 8;

c_name [Link]%type;

c_addr [Link]%type;

BEGIN

SELECT name, address INTO c_name, c_addr

FROM customers

WHERE id = c_id;

DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);

DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

Output:

No such customer!

PL/SQL procedure successfully completed.

2) TOO_MANY_ROWS: Raised when a SELECT INTO statement returns more than one
row.

CREATE TABLE EMPLOYEES20 (

EMPLOYEE_ID NUMBER PRIMARY KEY,

NAME VARCHAR2(50),

DEPARTMENT VARCHAR2(50)

);

INSERT INTO EMPLOYEES20 (EMPLOYEE_ID, NAME, DEPARTMENT) VALUES (1, 'John Doe', 'HR');

INSERT INTO EMPLOYEES20 (EMPLOYEE_ID, NAME, DEPARTMENT) VALUES (2, 'Jane Smith', 'Finance');

INSERT INTO EMPLOYEES20 (EMPLOYEE_ID, NAME, DEPARTMENT) VALUES (3, 'Alice Johnson', 'HR');
DECLARE

v_name [Link]%TYPE;

BEGIN

-- This will raise an exception because there are multiple employees in 'HR'

SELECT NAME INTO v_name

FROM EMPLOYEES20

WHERE DEPARTMENT = 'HR';

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);

EXCEPTION

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('Error: More than one employee found in the HR department.');

END;

3) DUP_VAL_ON_INDEX: Raised when an attempt is made to store duplicate

values in a column that is constrained by a unique index.

BEGIN

INSERT INTO employees20 (EMPLOYEE_ID, NAME, DEPARTMENT) VALUES (1, 'John Doe','hr'); --
Assuming employee_id 100 already exists

EXCEPTION WHEN DUP_VAL_ON_INDEX THEN

DBMS_OUTPUT.PUT_LINE('Duplicate value on unique index.');

END;

/
4 )ZERO_DIVIDE: Raised when an attempt is made to divide a number by zero.
DECLARE

v_num1 NUMBER := 10;

v_num2 NUMBER := 0;

v_result NUMBER;

BEGIN v_result := v_num1 / v_num2;

EXCEPTION WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE('Attempt to divide by zero.');

END;

5)VALUE_ERROR: Raised when an arithmetic, conversion, truncation, or size-constraint

error occurs

DECLARE

v_num NUMBER(3);

BEGIN

v_num := 1234; -- Too large for the declared size

EXCEPTION WHEN VALUE_ERROR THEN

DBMS_OUTPUT.PUT_LINE('Value error: number too large.');

END;

/
User-Defined Exceptions

You can define your own exceptions and raise them as needed.

DECLARE

e_invalid_age EXCEPTION;

v_age NUMBER := -5;

BEGIN

IF v_age < 0 THEN

RAISE e_invalid_age;

END IF;

EXCEPTION WHEN e_invalid_age THEN

DBMS_OUTPUT.PUT_LINE('Invalid age: Age cannot be negative');

END;

Output: invalid_age Age cannot be negative

Example 4: Using RAISE_APPLICATION_ERROR

This allows you to associate a user-defined error message with an error number and raise it.

DECLARE

v_salary NUMBER := 20000;

BEGIN IF v_salary > 10000 THEN

RAISE_APPLICATION_ERROR(-20001, 'Salary exceeds the limit');

END IF;

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
/

Here, if v_salary exceeds 10000, a custom error with code -20001 and message 'Salary exceeds
the limit' is raised. The SQLERRM function is used to print the error message.

=====================================================================================

CURSORS: CURSOR is a memory location which is used to run SQL commands. They
are two types of cursors.
1. Implicit cursor
2. Explicit cursor

Implicit cursor: All the activities related to cursors like opening the cursor, processing
the cursor and closing the cursor are done automatically. Hence these cursor are
called as implicit cursor. Automatically created by Oracle when an INSERT, UPDATE,
DELETE, or SELECT INTO statement is executed.

Implicit cursor attributes: They are 4 implicit cursor attributes.

[Link]%ISOPEN
[Link]%FOUND
[Link]%NOTFOUND
[Link]%ROWCOUNT
SQL%ISOPEN: It is a Boolean attribute. It always returns false.
*SQL%FOUND: it is a Boolean. It returns true. If SQL command is successful
returns false if SQL command is fails.
*SQL%NOTFOUND: It is a Boolean attribute returns true. If SQL command is fails
returns false if SQL command is successful.
Note:
SQL%NOTFOUND attribute is exactly opposite to SQL%FOUND.
*SQL%ROWCOUNT: It returns the number of rows affected by SQL command.

EXAMPLE: using *SQL%ROWCOUNT

CREATE TABLE employees40 (

employee_id NUMBER PRIMARY KEY,

 first_name VARCHAR2(50),

last_name VARCHAR2(50),

salary NUMBER

);

INSERT INTO employees40 (employee_id, first_name, last_name, salary) VALUES (1, 'John', 'Doe', 5000);

INSERT INTO employees40 (employee_id, first_name, last_name, salary) VALUES (2, 'Jane', 'Smith',
6000);

INSERT INTO employees40 (employee_id, first_name, last_name, salary) VALUES (3, 'Jim', 'Brown',
5500);

DECLARE

v_total_salary NUMBER;

BEGIN

-- Update the salary of all employees

UPDATE employees40

SET salary = salary * 1.10;

-- Display the number of rows affected by the UPDATE statement

DBMS_OUTPUT.PUT_LINE('Number of rows updated: ' || SQL%ROWCOUNT);

-- Calculate the total salary of all employees

SELECT SUM(salary)

INTO v_total_salary

FROM employees40;
 -- Display the total salary

DBMS_OUTPUT.PUT_LINE('Total salary of all employees: ' || v_total_salary);

END;

Example * using SQL%FOUND:

Begin

Update employees40 set salary = 10000 where employee_id = 1;

IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE('UPDATE SUCCESSFUL');

ELSE

DBMS_OUTPUT.PUT_LINE('UPDATE FAILED');

END IF;

END;

Example *SQL%ISOPEN
Begin

Update employees40 set salary = 8000 where employee_id =2;

IF SQL%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('CURSOR IS OPEN');

ELSE

DBMS_OUTPUT.PUT_LINE('CURSOR IS CLOSED');

END IF;

END;

/
Explicit Cursors: Defined and controlled by the programmer to handle queries returning
multiple rows.

Steps to use explicit cursors:

Step 1: declare the
cursor Step 2: open the
cursor
Step 3: fetch data from cursor to local
variables Step 4: close the cursor

Syntax:
Step 1: declare the cursor

CURSOR <CURSOR_NAME> IS <SELECT STMT>;

Step 2: open the cursor

OPEN <CURSOR_NAME>;

Step 3: fetch data from cursor to local variables

FETCH <CURSOR_NAME> INTO <VAR1>,<VAR2>,….,….,….<VARn>;

Step 4: close the cursor

CLOSE <CURSOR_NAME>;

EXAMPLE:

CREATE TABLE EMPLOYEES30 (

EMPLOYEE_ID NUMBER PRIMARY KEY,

NAME VARCHAR2(50),

DEPARTMENT VARCHAR2(50)

);

INSERT INTO EMPLOYEES30 (EMPLOYEE_ID, NAME, DEPARTMENT) VALUES (1, 'John Doe', 'HR');

INSERT INTO EMPLOYEES30 (EMPLOYEE_ID, NAME, DEPARTMENT) VALUES (2, 'Jane Smith', 'Finance');

INSERT INTO EMPLOYEES30 (EMPLOYEE_ID, NAME, DEPARTMENT) VALUES (3, 'Alice Johnson', 'HR');
DECLARE

-- Declare a cursor named 'hr_employees' to fetch names from the HR department

CURSOR hr_employees IS

SELECT NAME

FROM EMPLOYEES30

WHERE DEPARTMENT = 'HR';

-- Declare a variable to hold the name of an employee

v_name [Link]%TYPE;

BEGIN

-- Open the cursor

OPEN hr_employees;

-- Loop to fetch each row from the cursor

LOOP

-- Fetch the current row into the variable 'v_name'

FETCH hr_employees INTO v_name;

-- Exit the loop when there are no more rows to fetch

EXIT WHEN hr_employees%NOTFOUND;

-- Print the name of the employee

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);

END LOOP;

-- Close the cursor

CLOSE hr_employees;

END;

/
Output:

Employee name: john doe

Employee name:alice Johnson

1. DECLARE Section:
o Cursor Declaration: The cursor hr_employees is declared to
select the NAME of employees from the 'HR' department.
o Variable Declaration: The variable v_name is declared to hold
the fetched employee name.
2. BEGIN Section:
o Open the Cursor: OPEN hr_employees opens the cursor and
allocates memory.
o Fetch Loop: A LOOP is used to fetch each row from the cursor
into the variable v_name.
 Fetch Statement: FETCH hr_employees INTO v_name
retrieves the current row.
 Exit Condition: EXIT WHEN hr_employees%NOTFOUND
exits the loop when there are no more rows to fetch.
 Output Statement: DBMS_OUTPUT.PUT_LINE('Employee
Name: ' || v_name) prints the employee name.
o Close the Cursor: CLOSE hr_employees closes the cursor and
frees the allocated memory.
PACKAGE:

In Oracle, a package is a schema object that groups logically related

PL/SQL types, variables, constants, subprograms (procedures and
functions), cursors, and exceptions. Using packages provides several
benefits, such as modularity, encapsulation, and reusability.

A package consists of two parts:

1)Package Specification (Spec): Declares the public items that can be

referenced from outside the package. It acts as the interface to the
package.
2)Package Body: Defines the actual implementation of the package's public
items and can also declare and define private items that are not accessible
outside the package.
Here's a complete example to demonstrate the creation and use of
packages in Oracle.

Example: Employee Management Package

create table employees60(emp_id number,name varchar2(50),salary

number);

Step 1: Create the Package Specification

CREATE OR REPLACE PACKAGE emp_mgmt AS

-- Public variable
g_bonus_rate CONSTANT NUMBER := 0.1;

-- Public procedure
PROCEDURE add_employee(p_emp_id IN NUMBER, p_name IN
VARCHAR2, p_salary IN NUMBER);

-- Public function
FUNCTION get_employee_salary(p_emp_id IN NUMBER) RETURN
NUMBER;

-- Public cursor
CURSOR c_employees IS SELECT emp_id, name, salary FROM
employees60;
END emp_mgmt;
/

Step 2: Create the Package Body

CREATE OR REPLACE PACKAGE BODY emp_mgmt AS

-- Private variable
v_employee_count NUMBER;

-- Public procedure implementation

PROCEDURE add_employee(p_emp_id IN NUMBER, p_name IN
VARCHAR2, p_salary IN NUMBER) IS
BEGIN
INSERT INTO employees60 (emp_id, name, salary)
VALUES (p_emp_id, p_name, p_salary);
v_employee_count := v_employee_count + 1;
END add_employee;

-- Public function implementation

FUNCTION get_employee_salary(p_emp_id IN NUMBER) RETURN
NUMBER IS
v_salary [Link]%TYPE;
BEGIN
SELECT salary INTO v_salary
FROM employees60
WHERE emp_id = p_emp_id;

RETURN v_salary;
END get_employee_salary;

END emp_mgmt;
/
Step 3: Use the Package

-- Add a new employee

BEGIN
emp_mgmt.add_employee(1, 'zeenu', 50000);
emp_mgmt.add_employee(2, 'ameena', 60000);
END;
/

-- Retrieve the salary of an employee

DECLARE
v_salary NUMBER;
BEGIN
v_salary := emp_mgmt.get_employee_salary(1);
DBMS_OUTPUT.PUT_LINE('Salary of employee 1: ' || v_salary);
END;
/

-- Fetch and display all employees using the public cursor

BEGIN
FOR emp_rec IN emp_mgmt.c_employees LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.emp_id || ',
Name: ' || emp_rec.name || ', Salary: ' || emp_rec.salary);
END LOOP;
END;
/
Explanation
Package Specification:

Declares a constant g_bonus_rate, a procedure add_employee, a function

get_employee_salary, and a cursor c_employees.
These declarations are accessible to any user who has EXECUTE privilege
on the package.

Package Body:

Implements the procedure add_employee which inserts a new employee

into the employees table and increments the private variable
v_employee_count.
Implements the function get_employee_salary which retrieves the salary of
an employee based on the provided employee ID.
Implements the cursor c_employees which selects all employees from the
employees table.

Using the Package:

The procedure add_employee is called to add two new employees.

The function get_employee_salary is called to retrieve and display the
salary of an employee.
The cursor c_employees is used in a loop to fetch and display all
employees.
This example illustrates how to create and use packages in Oracle to
encapsulate and manage related PL/SQL code efficiently.