SET Operators:

SET operators are special type of operators which are

used to combine the result of two queries/Two select
statement.

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

[Link]:

It is used to obtain only unique values from both the queries.

SYNTAX:

SELECT COL_NAME
FROM TN1
UNION
SELECT COL_NAME
FROM TN2;
EX:

SELECT DEPTNO
FROM EMP
UNION
SELECT DEPTNO
FROM DEPT;

2. UNION ALL

This operator Is used to combines all the records from both

the queries.

SYNTAX:

SELECT COL_NAME
FROM TN1
UNION ALL
SELECT COL_NAME
FROM TN2;
EX:
SELECT DEPTNO
FROM EMP
UNION ALL
SELECT DEPTNO
FROM DEPT;

3. INTERSECT

This operator returns only the common values from both

the queries.

SYNTAX:

SELECT COL_NAME
FROM TN1
INTERSECT
SELECT COL_NAME
FROM TN2;
EX:
SELECT DEPTNO
FROM EMP
INTERSECT
SELECT DEPTNO
FROM DEPT ;

[Link]

It returns the record which are present in the first query

but absent in the
second query without duplicates.

SYNTAX:

SELECT COL_NAME
FROM TN1
MINUS
SELECT COL_NAME
FROM TN2;
EX:

SELECT DEPTNO
FROM DEPT
MINUS
SELECT DEPTNO
FROM EMP;

NOTE :
There are certain rules which must be followed to perform
SET operators in SQL.

Every select statment with set operator should have same

number
Of columns .
The columns must have similar datatypes .
The columns in every select statements must also be in the
Same order

SELECT ENAME,DEPTNO
FROM EMP
UNION
SELECT DNAME,DEPTNO
FROM DEPT;
 VIEWS

[Link]'s a named query

[Link] is a virtual table which has rows and columns same
like a table , but it's not a table.
[Link] stores the name of the table not data . It won’t stores
any data physically.
[Link] whenever you query the view
[ select * from view name ;]
the data will be fetched from base table not from view table .
[Link] whatever changes done on view table automatically it
will get change in base table .
[Link] provides data security.

TO CREATE VIEW :
SYNTAX:
CREATE VIEW VIEW_NAME
AS
SELECT COL_NAME
FROM TABLE_NAME
[WHERE <CONDITION>];
Permission to be taken to create view

CONNECT: SYSTEM
Enter password:*****
Connected.

EX:
GRANT CREATE VIEW
TO SCOTT;
EX:

CREATE VIEW V
AS
SELECT *
FROM EMP;
EX:

CREATE VIEW V1
AS
SELECT *
FROM EMP
WHERE JOB='MANAGER';
CREATE OR REPLACE VIEW
SYNTAX:
CREATE OR REPLACE VIEW VIEW_NAME
AS
SELECT *
FROM TABLE_NAME;

EX:
CREATE OR REPLACE VIEW V1
AS
SELECT *
FROM EMP
WHERE JOB='SALESMAN';

TO DROP THE VIEW:

SYNTAX:
DROP VIEW VIEW_NAME;
EX: DROP VIEW V;
MATERIALIZED VIEW :

[Link]'s a named query

2. Materialized view store the name of table along with data
physical in database .
3. Here ,whenever u query the materialized view [ select *
from materialized view ]
[Link] data will be fetched from materailized table only .
[Link] here we cannot make any changes in mv table . ( data
manipulation operation not legal on this view )
6. To create materialized view on any table , that table should
consist of primary key compulsory.

TO CREATE MATERIALIZED VIEW :

SYNTAX:

CREATE MATERIALIZED VIEW VIEW_NAME

AS
SELECT COL_NAME
FROM TABLE_NAME
[WHERE <CONDITION>];
Permission to be taken to create MATERIALIZED VIEW

CONNECT SYSTEM
Enter password:*****
Connected.

GRANT CREATE MATERIALIZED VIEW

TO SCOTT;

EXAMPLE:

CREATE MATERIALIZED VIEW V2

AS
SELECT *
FROM EMP
WHERE JOB='SALESMAN';
TO DROP MATERIALIZED VIEW
SYNTAX:
DROP MATERIALIZED VIEW VIEW_NAME;
EX: DROP MATERIALIZED VIEW V1;

INDEX
WHAT IS AN INDEX IN ORACLE SQL?
An index in Oracle is a database object that improves the
speed of data retrieval.
It works like an index in a book — instead of scanning every
row, Oracle can jump
directly to the data.

Syntax:
CREATE INDEX index_name
ON table_name (column1, column2, ...);
Step-by-Step Example:
[Link] a Table:

CREATE TABLE employees (

emp_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
department VARCHAR2(50),
salary NUMBER
);

[Link] Sample Data:

INSERT INTO employees VALUES (1, 'John Doe', 'HR',

50000);
INSERT INTO employees VALUES (2, 'Jane Smith', 'IT',
60000);
INSERT INTO employees VALUES (3, 'Alice Brown',
'Finance', 70000);
COMMIT;
[Link] an Index on a Column (e.g., department):

CREATE INDEX idx_dept

ON employees(department);
(This index speeds up queries that filter on the department
column.)

HOW TO RETRIEVE (SELECT) DATA USING INDEX

You don't need to mention the index explicitly in your

SELECT queries. Oracle automatically uses the index if it
helps performance.

SELECT *
FROM employees
WHERE department = 'IT';
SYNTAX TO CHECK INDEX:

EXPLAIN PLAN FOR

SELECT *
FROM TABLE_NAME
WHERE COL_NAME='VALUE' ;
// COL_NAME=INDEX_CREATED _COL//

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);
Oracle will use the idx_dept index (if it improves
performance).

HOW TO UPDATE DATA

Indexes are automatically updated when you update the
indexed column.
UPDATE employees
SET department = 'Operations'
WHERE emp_id = 2;
Oracle automatically updates the index idx_dept if the
department value is changed.
HOW TO DELETE DATA

DELETE FROM employees

WHERE emp_id = 2;
Again, indexes are automatically updated when rows are
deleted.

HOW TO DROP (DELETE) AN INDEX

If you want to remove an index:

Syntax:
DROP INDEX idx_dept;

Types of Indexes in Oracle

1. B-tree Index :Default and most common
[Link] Index: For columns with few distinct values (e.g.,
gender)
[Link] Index: Ensures all values are unique
[Link] Index :On multiple columns
5. Function-Based Index :On expressions (e.g., `UPPER(name)`)
 GENERAL SYNTAX OF PL/SQL BLOCK:

DECLARE
-- Variable declarations (optional)
BEGIN
-- Executable statements (mandatory)
EXCEPTION
-- Exception handling (optional)
END;

Example:

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;
/
 TRIGGER

What is a Trigger in SQL?

A Trigger is a stored procedure in a database that
automatically executes (fires) in response to certain events on
a specified table or view. These events could be:

[Link]
[Link]
[Link]

Triggers are commonly used for:

[Link] (e.g., tracking changes to a table)
[Link] business rules
[Link] validation or calculations
Syntax of a Trigger (in Oracle-style SQL)

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF}

{INSERT | UPDATE | DELETE}

[OF column_name] -- (Only for UPDATE)

ON table_name

[REFERENCING {OLD AS old | NEW AS new}]

[FOR EACH ROW] -- Row-level (optional)

[WHEN (condition)] -- Optional condition

DECLARE

-- variable declarations (optional)

BEGIN

-- PL/SQL statements (trigger body)

EXCEPTION

-- exception handling (optional)

END;

/
Step 1: Create the emps table (if not exists)

CREATE TABLE emps(

emp_id NUMBER PRIMARY KEY,

ename VARCHAR2(50),

salary NUMBER

);

Step 2: Create a simple AFTER INSERT trigger

CREATE OR REPLACE TRIGGER trg_emps_insert_msg

AFTER INSERT ON emps

FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE('Employee ' || :[Link] || '

added with salary ' || :[Link]);

END;

/
Step 3: Insert a row to test the trigger

SET SERVEROUTPUT ON; -- Important to enable

DBMS_OUTPUT

INSERT INTO emps VALUES (101, 'Alice', 50000);

you can see as:

Employee Alice added with salary 50000

Checking Errors:
If Oracle says “trigger created with compilation errors,” run:

SHOW ERRORS TRIGGER trg_after_insert_emp;

CURSOR
What is a Cursor in SQL (PL/SQL)?
A cursor is a pointer to a result set of a query. In PL/SQL,
cursors allow you to process individual rows returned by a
SQL query, one at a time.
Why Use Cursors?
[Link] you need to loop through rows one-by-one.
[Link] complex logic or processing is needed per row in a
query result.
Types of Cursors
Implicit Cursor:
In Oracle PL/SQL, implicit cursors are automatically created
by Oracle whenever a DML statement (INSERT, UPDATE,
DELETE, SELECT INTO) is executed. You don't need to
declare them.
Syntax:
BEGIN

-- DML statement (INSERT, UPDATE, DELETE, SELECT

INTO)

SQL statement;

-- Use implicit cursor attributes

DBMS_OUTPUT.PUT_LINE('Rows affected: ' ||

SQL%ROWCOUNT);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('No rows found.');

END;

/
Implicit Cursor Attributes
Attribute Description
SQL%ROWCOUNT: Number of rows affected by the last DML
operation

SQL%FOUND :Returns TRUE if one or more rows were affected

SQL%NOTFOUND :Returns TRUE if no rows were affected

SQL%ISOPEN :Always FALSE for implicit cursors (not manually

opened)

Oracle provides a set of cursor attributes to check the outcome of the

last DML statement.

Eg:
1. Implicit Cursor Example

DECLARE

emp_name [Link]%TYPE;

BEGIN

SELECT ENAME INTO emp_name FROM EMPS WHERE

EMP_ID = 101;

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

END;

/
Explicit Cursor: Manually declared and controlled by the
programmer.

syntax:
DECLARE

CURSOR cursor_name IS

SELECT_statement;

variable1 datatype;

variable2 datatype;

BEGIN

OPEN cursor_name;

LOOP

FETCH cursor_name INTO variable1, variable2;

EXIT WHEN cursor_name%NOTFOUND;

-- Your processing logic

END LOOP;

CLOSE cursor_name;

END;

/
2. Explicit Cursor Example
DECLARE

CURSOR emp_cur IS

SELECT EMPNO, ENAME, SAL FROM EP;

v_empno [Link]%TYPE;

v_ename [Link]%TYPE;

v_sal [Link]%TYPE;

BEGIN

OPEN emp_cur;

LOOP

FETCH emp_cur INTO v_empno, v_ename, v_sal;

EXIT WHEN emp_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('EMPNO: ' || v_empno || ', NAME:

' || v_ename || ', SAL: ' || v_sal);

END LOOP;

CLOSE emp_cur;

END;

Explanation:
emp_cur is an explicit cursor for the SELECT query.

OPEN prepares it.

FETCH retrieves one row at a time.

EXIT WHEN emp_cur%NOTFOUND breaks the loop when all rows
are processed.

CLOSE releases the cursor.

3. Cursor FOR Loop Example (Recommended for

simplicity)
BEGIN

FOR emp_rec IN (SELECT EMPNO, ENAME, SAL FROM

EMP) LOOP

DBMS_OUTPUT.PUT_LINE('EMPNO: ' || emp_rec.EMPNO || ',

NAME: ' || emp_rec.ENAME || ', SAL: ' || emp_rec.SAL);

END LOOP;

END;

Explanation:
No need to DECLARE, OPEN, FETCH, or CLOSE — it’s automatic.

emp_rec is a record holding each row's values.

When to Use Which?

Situation Cursor Type

Simple SELECT INTO one row :Implicit Cursor

Need row-by-row control :Explicit Cursor

Need looping, but want simplicity :Cursor FOR Loop

 SCHEMA

WHAT IS SCHEMA?
A schema is the blueprint or structure that defines how data is
organized and stored in a database
In Oracle SQL, a schema is a logical collection of database
objects (like tables, views, indexes, sequences, procedures,
Functions,Triggers etc.) that are owned by a specific database
user.
The schema defines the logical view of the entire database and
specifies the rules that govern the data, including its types,
constraints, and relationships.

Key points about a database schema:

[Link] defines how data is logically organized, including tables,
fields, and relationships.
[Link] outlines the relationships between entities, such as
primary and foreign keys.
[Link] helps resolve issues with unstructured data by organizing
it in a clear, structured way.
[Link] schemas guide how data is accessed, modified,
and maintained.
Step 1: Connect to the Database as SYS or SYSTEM

You must log in as a DBA user (like SYS or SYSTEM) to

create other users/schemas.

EX:

CONNECT
ENTER USERNAME: SYSTEM
PASSWORD:TIGER

Step 2: Create a User (This Creates the Schema)

syntax:
CREATE USER SCHEMA_NAME IDENTIFIED BY
PASSWORD;

Eg:
CREATE USER hr_schema IDENTIFIED BY hr123;
(This creates a user and a corresponding schema named
hr_schema.)
Step 3: Grant Required Privileges to the User
SYNTAX:

GRANT CONNECT, RESOURCE TO

SCHEMA_NAME;

EG:

GRANT CONNECT, RESOURCE TO hr_schema;

(CONNECT lets the user log in.
RESOURCE allows creating tables, indexes, procedures,
etc)

Set Default Tablespace and Quota (Optional but

Recommended)
SYNTAX:
ALTER USER SCHEMA_NAME DEFAULT
TABLESPACE users;
Eg:
ALTER USER hr_schema DEFAULT TABLESPACE
users;
ALTER USER schema_NAME QUOTA UNLIMITED
ON users;
Eg:
ALTER USER hr_schema QUOTA UNLIMITED ON
users;

Step 5: Connect as the New User and Create

Objects
LOGON

USERNAME:HR_SCHEMA
PASSWORD:HR123

Then create objects like tables, views, etc.

CREATE TABLE employees (

emp_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
salary NUMBER
);
 ACID Properties in DBMS
(Database Management System)

ACID is an acronym that stands for Atomicity, Consistency,

Isolation, and Durability. These are the four key properties
that ensure reliable processing of database transactions.

1. Atomicity
Definition: A transaction is an atomic unit of work — either
all of its operations are performed, or none of them are.
Example:
Suppose you are transferring ₹1000 from Account A to
Account B:
Step 1: Debit ₹1000 from Account A
Step 2: Credit ₹1000 to Account B
If the system crashes after debiting but before crediting,
atomicity ensures the debit is rolled back.

2. Consistency
Definition: A transaction must take the database from one
valid state to another, maintaining data integrity constraints.
Example:
If Account A has ₹5000 and you try to transfer ₹6000, the
system should reject the transaction to maintain consistency
(no account should go negative if that's a constraint).

3. Isolation
Definition: Transactions should run independently — one
transaction’s intermediate operations should not be visible to
other transactions.
Example:
T1: Transferring money from A to B
T2: Checking the balance of A
Even if T1 is not complete, T2 should not see the partial result
(like debited amount but not credited yet).

4. Durability
Definition: Once a transaction is committed, the changes are
permanently stored in the database, even in case of a power
failure or crash.

Example:
If you book a train ticket and the transaction is committed,
your seat reservation should not be lost even if the server
crashes immediately after.
Summary Table

Property Description Example

Atomicity All or nothing No change to either

execution Transfer account.
fails

Consistency Data must remain Reject transfer if

valid and follow account goes below
rules minimum balance

Isolation Transactions do not No dirty reads or

interfere with each partial updates
other visible

Durability Once committed, Power failure

changes are saved doesn’t erase
permanently committed
bookings.

CTE
What is a CTE in Oracle?
CTE stands for Common Table Expression.
It's a temporary result set that you can reference within a
SELECT, INSERT, UPDATE, or DELETE statement.
In Oracle, CTEs are defined using the WITH clause and are
especially useful for:
[Link] readability
[Link] logic
[Link] recursive queries

Syntax of CTE in Oracle

WITH cte_name AS (
SELECT ...
FROM ...
WHERE ...
)
SELECT *
FROM cte_name;

Eg:
WITH annual_sal AS(
SELECT EMP.*,SAL*12
FROM EMP
)
SELECT *
FROM ANNUAL_SAL;
Example: Simple CTE

Suppose you have a table called employees:

emp_id emp_name dept_id salary

1 Alice 10 5000

2 Bob 10 6000

3 Carol 20 4000

4 David 20 7000

Now, suppose you want to:

Get employees whose salary is above the average

salary.
Using CTE:

WITH avg_salary_cte AS (
SELECT AVG(salary) AS avg_sal
FROM employees
)
SELECT *
FROM employees
WHERE salary > (SELECT avg_sal FROM
avg_salary_cte);

Example: Recursive CTE

Suppose you have an employee hierarchy table:

emp_id emp_name manager_id

1 Alice NULL
2 Bob 1
3 Carol 2
4 David 2
[Link] you want to get all subordinates of employee
with emp_id = 1?

WITH emp_hierarchy (emp_id, emp_name, manager_id)

AS
(
SELECT emp_id, emp_name, manager_id
FROM employees
WHERE manager_id IS NULL -- start with top-level
manager (e.g., Alice)
UNION ALL
SELECT e.emp_id, e.emp_name, e.manager_id
FROM employees e
JOIN emp_hierarchy eh ON e.manager_id = eh.emp_id
)
SELECT *
FROM emp_hierarchy;
This recursive CTE will return the entire hierarchy under
Alice.
Key Points:
[Link] improve query modularity and clarity.
[Link] CTEs are ideal for hierarchical or tree-structured
data.
[Link] can use multiple CTEs in a single WITH clause,
separated by commas.

Stored Procedure

What is a Stored Procedure?

A stored procedure is a precompiled block of SQL code stored
in the database that can be executed repeatedly with optional
input/output parameters.

Stored procedures:
[Link] performance by reducing parsing and compilation
time
[Link] enforce business logic at the database level
[Link] be reused and maintained easily.
Key Features

[Link] contain SQL statements like SELECT, INSERT,

UPDATE, DELETE
[Link] parameters (IN, OUT, IN OUT)
[Link] control structures like IF, WHILE, FOR
[Link] and executed on the database server

Syntax in Oracle
CREATE OR REPLACE PROCEDURE procedure_name
(
param1 IN datatype,
param2 OUT datatype
)
IS
BEGIN
-- SQL statements here
END;
/
Example: Stored Procedure in Oracle
Task:

Create a procedure to get the salary of an employee

by their ID.

Step 1: Assume we have a table

CREATE TABLE employees (
emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER
);

Step 2: Insert some sample data

INSERT INTO employees VALUES (1, 'Alice', 5000);

INSERT INTO employees VALUES (2, 'Bob', 6000);
Step 3: Create the stored procedure
CREATE OR REPLACE PROCEDURE get_salary (
p_emp_id IN NUMBER,
p_salary OUT NUMBER
)IS
BEGIN
SELECT salary INTO p_salary
FROM employees
WHERE emp_id = p_emp_id;
END; /
How to Execute the Stored Procedure
You can call the procedure using an anonymous PL/SQL
block:
DECLARE
v_salary NUMBER;
BEGIN
get_salary(1, v_salary);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/
Types of Parameters

Type Description

IN Passes a value into the procedure

OUT Returns a value from the procedure
IN OUT Passes a value in and gets a modified result

Benefits of Stored Procedures

Modular code — write once, reuse many times

Security — grant access to procedures, not tables
Faster execution — precompiled and cached
Maintainability — easy to update logic in one place
 PACKAGE

What is a Package in PL/SQL?

A package in PL/SQL is a schema object that groups

logically related procedures, functions, variables, cursors,
constants, and types under a single name.
It consists of two parts:
1. Package Specification – Declares the public items
(procedures, functions, variables).
2. Package Body – Contains the actual code
(implementation) of the procedures and functions.

Advantages of Using Packages

 Modularity: Encapsulates related logic.

 Reusability: Code can be reused across multiple
programs.
 Performance: Improves performance by loading all
related procedures/functions at once.
 Security: Expose only necessary procedures/functions
via the specification.
Example Using the EMP Table
Assume this is your EMP table:
EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL,
COMM, DEPTNO)

Package Specification

CREATE OR REPLACE PACKAGE emp_pkg IS

-- Procedure to give a raise to an employee
PROCEDURE give_raise(p_empno NUMBER, p_percent
NUMBER);

-- Function to get employee salary

FUNCTION get_salary(p_empno NUMBER) RETURN
NUMBER;
END emp_pkg;

Package Body

CREATE OR REPLACE PACKAGE BODY emp_pkg IS

PROCEDURE give_raise(p_empno NUMBER, p_percent

NUMBER) IS
BEGIN
UPDATE emp
SET sal = sal + (sal * p_percent / 100)
WHERE empno = p_empno;
END give_raise;
 FUNCTION get_salary(p_empno NUMBER) RETURN
NUMBER IS
v_sal NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno =
p_empno;
RETURN v_sal;
END get_salary;

END emp_pkg;

Usage

-- Giving a 10% raise to employee 7369

BEGIN
emp_pkg.give_raise(7369, 10);
END;
/

-- Getting the salary of employee 7369

DECLARE
v_salary NUMBER;
BEGIN
v_salary := emp_pkg.get_salary(7369);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/