SQL Notes
Uploaded by
Malle VenkatakumarSQL Notes
Uploaded by
Malle VenkatakumarSQL Notes TeluguWebGuru
Oracle SQL Course – Brief Overview
🎯 Course Goal:
To introduce learners to Oracle SQL, enabling them to write queries, manipulate data, and understand
Oracle-specific database features.
📘 Key Topics:
1. Introduction to Oracle SQL
○ Understanding Oracle Database
○ SQL vs PL/SQL
○ Using Oracle SQL Developer
2. Basic SQL Operations
○ SELECT, FROM, WHERE, ORDER BY
○ Filtering and sorting data
3. Functions & Operators
○ Aggregate functions: COUNT, SUM, AVG
○ String, number, and date functions
4. Data Manipulation (DML)
○ INSERT, UPDATE, DELETE
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
○ Using COMMIT and ROLLBACK
5. Data Definition (DDL)
○ CREATE, ALTER, DROP tables
○ Constraints: PRIMARY KEY, UNIQUE, FOREIGN KEY
6. Joins & Subqueries
○ INNER, LEFT, RIGHT joins
○ Subqueries within SELECT, WHERE, FROM
7. Oracle-Specific Features
○ DUAL table
○ ROWNUM, SYSDATE
○ Sequences for auto-incrementing values
✅ Outcome:
By the end, learners will be able to:
● Query and manage data in Oracle databases
● Understand and use Oracle-specific SQL features
● Write optimized, real-world SQL queries
🧠 Introduction to SQL (Structured Query Language)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
💾 1. Database
● A database is a structured collection of interrelated data.
● It stores data in an organized way (tables, rows, columns).
● Example: A database might store customer details, sales data, or employee records.
👉 Think of a database as a warehouse where all the data is stored.
🧠 2. DBMS (Database Management System)
● A DBMS is software that allows you to create, manage, and interact with a database.
● It handles data storage, retrieval, security, backups, and concurrency.
● Examples: Oracle, MySQL, SQL Server, PostgreSQL
👉 Think of a DBMS as the manager of the warehouse (database), handling everything inside.
💬 3. SQL (Structured Query Language)
● SQL is the language used to communicate with the DBMS.
● It allows you to perform actions like:
○ Create tables (CREATE)
○ Insert data (INSERT)
○ Query data (SELECT)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
○ Update or delete data (UPDATE, DELETE)
👉 Think of SQL as the instructions or commands you give to the DBMS manager to get work done in
the warehouse.
🧱 Types of DBMS
DBMSs are categorized based on how they store and manage data. There are four main types:
1. 🧱 Object-Oriented DBMS (OODBMS)
● Structure: Based on object-oriented programming (OOP).
● Characteristics:
○ Data stored as objects (with attributes and methods).
○ Supports complex data types.
● Examples: ObjectDB, db4o
🧠 Think of: Storing entire objects like in Java or C++
2. 🔗 Hierarchical DBMS
● Structure: Tree-like structure (parent-child relationship).
● Characteristics:
○ One parent, many children.
○ Fast and simple if data follows a fixed hierarchy.
● Example: IBM IMS (used in banking, airline systems)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🧠 Think of: A folder system in your computer.
3. 🌐 Network DBMS
● Structure: Graph structure (many-to-many relationships).
● Characteristics:
○ A child can have multiple parents.
○ More flexible than hierarchical DBMS.
● Example: IDMS (used in telecom and logistics)
🧠 Think of: A social network where one person can be connected to many people.
4. 📊 Relational DBMS (RDBMS)
● Structure: Table format (rows and columns).
● Characteristics:
○ Uses SQL (Structured Query Language).
○ Most widely used DBMS type.
● Examples: MySQL, Oracle, SQL Server, PostgreSQL
🧠 Think of: Excel spreadsheets that can be linked together.
🗂️ What is Metadata?
Metadata is data that describes other data. It provides information about a data resource, such as its
structure, content, origin, time, or format.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🧠 Simple Definition:
"Metadata is data about data."
✅ Real-World Examples of Metadata
Data (Main Content) Metadata (Describes the Data)
📄 A Word Document File name, author, word count, creation date, file size
📷 A Photo Camera model, resolution, date taken, GPS location, file format
🎵 A Song in Spotify Song title, artist, album, genre, duration
🎬 A YouTube Video Title, uploader, tags, resolution, duration, upload date
📊 A Table in a Database Table name, column names, data types, primary key, row count
🗺️ A GIS Map File Location coordinates, projection type, scale, creator
💾 Example: Metadata in a Database Table
Let’s say we have a table called Employees.
Actual Data:
ID Name Department Salary
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
1 John Doe Sales 50000
Metadata:
● Table name: Employees
● Columns: ID, Name, Department, Salary
● Data types: ID (INTEGER), Name (VARCHAR), etc.
● Constraints: ID is PRIMARY KEY
● Created on: 2024-01-01
● Total Rows: 100
📌 This metadata tells how the data is stored and how it can be used, but not the data itself.
✅ How to Install Oracle Database (Step-by-Step Guide)
Here’s a beginner-friendly guide to installing Oracle Database on your system. This covers the Oracle
Database 21c or 19c Express Edition (XE), which is free and ideal for learning purposes.
🧰 System Requirements (Oracle XE)
● OS: Windows 10/11 (64-bit) or Linux
● RAM: Minimum 2 GB (4 GB recommended)
● Disk Space: At least 10 GB free
● Privileges: Admin rights
🔽 Step 1: Download Oracle Database
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
1. Visit the official Oracle download page:
[Link]
2. Choose the appropriate version:
○ Oracle 21c XE (latest)
○ Or Oracle 19c XE (if you want a long-term support version)
3.Click “Download”, accept the license agreement, and sign in or create an Oracle account.
💻 Step 2: Install Oracle Database (Windows Example)
1. Run the Installer:
● Locate the downloaded .exe file and double-click to start.
2. Follow Setup Wizard:
● Choose an installation folder.
● Set a password for the SYS, SYSTEM, and PDBADMIN accounts.
● Let the installer run and complete the process.
3. Installation Complete
● Oracle Database will be installed along with:
○ Oracle Database Server
○ Oracle Listener
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
○ Oracle SQL*Plus
○ Oracle SQL Developer (GUI for working with SQL)
🔐 Step 3: Verify and Login
✅ To connect using SQL*Plus:
Open Command Prompt and enter:
bash
sqlplus sys as sysdba
Then enter the password you set during installation.
✅ To use Oracle SQL Developer:
1. Open Oracle SQL Developer.
2. Create a new connection with:
○ Username: system
○ Password: [your password]
○ Hostname: localhost
○ Port: 1521
○ Service Name: XEPDB1 (or XE for older versions)
Click Test → then Connect.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🚀 You're Ready!
Now you can start creating tables, writing queries, and learning Oracle SQL!
📘 Sub-Languages in SQL
SQL (Structured Query Language) is divided into five main sub-languages, each serving a specific
function in interacting with the database.
Sub-Language Purpose Key Commands Example
DQL (Data Query Retrieve data SELECT SELECT * FROM
Language) employees;
DML (Data Manipulation Manipulate data INSERT, UPDATE, INSERT INTO dept
Language) DELETE VALUES (1, 'HR');
DDL (Data Definition Define/change CREATE, ALTER, CREATE TABLE dept
Language) structure DROP, TRUNCATE (...);
DCL (Data Control Control access GRANT, REVOKE GRANT SELECT ON
Language) employees TO user1;
TCL (Transaction Control Manage transactions COMMIT, COMMIT;
Language) ROLLBACK,
SAVEPOINT
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Datatypes:
🔍 What Are Datatypes in Oracle?
In Oracle Database, a datatype defines the kind of data a column, variable, or expression can hold. It tells
the database how much space to allocate and how to store and manipulate the data.
✅ Why Are Datatypes Important?
● They ensure data integrity (e.g., storing only numbers in a salary column).
● They improve performance by optimizing how data is stored.
● They help Oracle enforce rules like constraints, sorting, and indexing.
📂 Categories of Datatypes in Oracle:
1.Numeric Datatypes – Store numbers.
2.Character Datatypes – Store text or characters.
3.Date and Time Datatypes – Store dates and times.
4.Large Object (LOB) Datatypes – Store large data like files or documents.
5.Long and raw datatype – Stores very large text data (like long descriptions or articles).
6.ROWID/UROWID – Store row addresses.
🔢 Numeric Datatypes
Oracle provides several numeric datatypes to store numbers—both integers and floating-point numbers.
Here's a breakdown:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ 1. NUMBER
The most commonly used numeric datatype.
sql
NUMBER(p, s)
● p = precision (total digits)
● s = scale (digits after the decimal point)
Example Description
NUMBER A number with up to 38
digits of precision
✔ Used for both integers and
decimals.
NUMBER(6) A number with up to 6 digits, no
decimal
✅ 2. INTEGER / INT /
SMALLINT
NUMBER(8,2) 6 digits before the decimal, 2 after ● These are just different
names for whole numbers.
NUMBER(*,0) Any number with 0 digits after ● All are the same as
decimal (integer) NUMBER without decimal part.
Example:
sql
INTEGER -- Same as NUMBER(38)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ 3. FLOAT
Stores approximate numeric values.
sql
FLOAT(p)
● p = binary precision (not decimal digits)
Example Description
FLOAT Approximate number, default precision
126 bits
FLOAT(10) Approximate with binary precision of
10 bits
📝 If you want precise decimal values, use NUMBER instead of FLOAT.
✅ 4. BINARY_FLOAT and BINARY_DOUBLE
High-performance floating-point types using IEEE 754.
Datatype Precision Description
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
BINARY_FLOAT 32-bit Fast, approximate floating-point
number
BINARY_DOUBLE 64-bit Double-precision floating-point
number
✔ Use these for performance-critical calculations where approximate values are acceptable.
🔤 Character Datatypes
These are used to store text — like names, addresses, or messages.
✅ 1. CHAR(n)
● Stores fixed-length text.
● If the text is shorter, it fills with spaces.
Example:
sql
CHAR(5)
-- 'Hi' is stored as 'Hi '
✅ 2. VARCHAR2(n)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Stores variable-length text.
● Only stores what you type (no extra spaces).
Example:
sql
VARCHAR2(5)
-- 'Hi' is stored as 'Hi'
👉 This is the most commonly used text type.
✅ 3. NCHAR(n)
● Like CHAR, but supports Unicode (multi-language).
✅ 4. NVARCHAR2(n)
● Like VARCHAR2, but supports Unicode.
📌 Quick Comparison Table:
Datatype Fixed or Variable Used For
CHAR(n) Fixed Short, fixed-length text
VARCHAR2(n) Variable Regular text (most common)
NCHAR(n) Fixed Unicode fixed text
NVARCHAR2(n) Variable Unicode variable text
🗓️ Date and Time Datatypes
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
These are used to store dates and times, like birth dates, timestamps, or event times.
✅ 1. DATE
● Stores date and time (year, month, day, hour, minute, second).
● Default format: DD-MON-YY or DD-MON-YYYY.
Example:
sql
DATE
-- '08-JUL-2025 10:30:00'
🟢 Most commonly used for storing date and time.
✅ 2. TIMESTAMP
● Like DATE but also stores fractions of a second.
● Example:
sql
TIMESTAMP
-- '08-JUL-2025 10:30:00.123456
✅ 3. INTERVAL YEAR TO MONTH
● Stores a period of time in years and months.
Example:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
sql
INTERVAL '2-6' YEAR TO MONTH
-- 2 years and 6 months
✅ 4. INTERVAL DAY TO SECOND
● Stores a duration in days, hours, minutes, seconds (and fractions).
Example:
sql
INTERVAL '3 04:30:15' DAY TO SECOND
-- 3 days, 4 hours, 30 minutes, 15 seconds
📌 Quick Summary Table:
Datatype Stores
DATE Date + time (no fractional seconds)
TIMESTAMP Date + time + fractional seconds
INTERVAL YEAR TO MONTH Time period in years and months
INTERVAL DAY TO SECOND Time duration in days, hours, seconds
💾 Large Object (LOB) Datatypes
LOB (Large Object) datatypes in Oracle are used to store large amounts of data, such as text, images,
audio, video, and other files.
There are two types of LOBs:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Internal LOBs: Stored in the database (e.g., CLOB, BLOB)
● External LOBs: Stored outside the database (e.g., BFILE)
✅ Types of LOB Datatypes
Datatype Type Stores Max Size Stored Where?
CLOB Character Large text data 4 GB+ Inside database
NCLOB Character Large Unicode text 4 GB+ Inside database
BLOB Binary Large binary data 4 GB+ Inside database
BFILE Binary Large binary files 4 GB+ Outside database (OS
(read-only) file)
🔍 Explanation of Each:
1. CLOB (Character Large Object)
● Stores large plain text like documents, articles, logs.
● Uses CHARACTER set of the database.
2. NCLOB (National Character Large Object)
● Like CLOB, but supports multilingual Unicode text (NCHAR data).
3. BLOB (Binary Large Object)
● Stores binary files, such as images, videos, PDFs.
● Data is stored exactly as-is, no character conversion.
4. BFILE (Binary File)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Stores a pointer to an external file (not stored in the DB).
● Read-only from PL/SQL.
● File must be in a directory that Oracle has access to.
🔹Long and raw datatype
1. LONG Datatype
● Purpose: Stores very large text data (like long descriptions or articles).
● Type: Character (Text)
● Maximum Size: Up to 2 GB
● Limitations:
○ Only one LONG column per table.
○ Cannot use in WHERE, GROUP BY, ORDER BY, JOIN, etc.
● Status: ✅ Deprecated (Oracle recommends using CLOB instead).
Example:
sql
CREATE TABLE books (
book_id NUMBER,
summary LONG
);
[Link] Datatype
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Purpose: Stores binary data, not readable as text (like images, encrypted data).
● Type: Binary
● Maximum Size: Up to 2000 bytes
maximum size is 32767 bytes if max_string_size_exanded
● Usage: Stores data "as-is", with no character set conversion.
Example:
sql
CREATE TABLE files (
file_id NUMBER,
file_data RAW(1000)
);
[Link] RAW (Related to RAW)
● Same as RAW, but supports larger binary data (up to 2 GB).
● Also deprecated — use BLOB instead.
✅ Summary Table
Datatype Type Stores Max Status Modern Alternative
Size
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
LONG Character Large text 2 GB Deprecated CLOB
RAW(n) Binary Small binary data 2000 B Still used —
LONG RAW Binary Large binary data 2 GB Deprecated BLOB
🔹 Rowid and urowid datatype
1. ROWID (Row Identifier)
● Type: Pseudocolumn / Datatype
● Purpose: Uniquely identifies the physical address of a row in a table.
● Contents: Includes data like:
○ Block number
○ File number
○ Row position in block
✅ Key Points:
● Each row in a table has a unique ROWID.
● Very fast for accessing rows.
● Often used for performance optimization and row-level updates.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
2. UROWID (Universal ROWID)
● Type: Universal Row Identifier
● Purpose: Can store both physical and logical row addresses.
● Used especially for tables with object types or index-organized tables (IOTs).
● More flexible than ROWID.
Why UROWID?
● ROWID only works for regular heap-organized tables.
● UROWID can also handle rows that don’t have a fixed physical location, such as:
○ Rows in IOTs
○ Rows in foreign tables (like through DBMS_LOB)
🔹 What is DDL
In SQL (Structured Query Language), DDL stands for Data Definition Language. It's one of the key
sublanguages of SQL, used to define and manage the structure of database objects such as tables, indexes,
schemas, and constraints.
What DDL Does:
DDL commands create, modify, and delete database structures—but not the data stored in them.
✅ Common DDL Commands:
Command Description
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
CREATE Creates new database objects (e.g., tables, views, schemas).
ALTER Modifies existing database objects (e.g., add/remove columns).
DROP Deletes existing database objects.
Flashback Used to recover data from a previous point in time without restoring from backup.
purge Permanently removes objects from the database Recycle Bin
TRUNCATE Removes all rows from a table quickly, without logging individual row deletions.
RENAME Changes the name of a database object (supported in some RDBMSs).
CREATE
● Purpose: Creates a new database object, such as a table, schema, or index.
⁕Syntax:
sql
CREATE TABLE table_name (
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
column1 datatype [constraints],
column2 datatype [constraints],
...
);
⁕Example:
sql
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
Notes: Defines the structure (columns, datatypes, constraints) of a new table or other object.
Fails if the object already exists unless specified otherwise (e.g., IF NOT EXISTS).
ALTER
● Purpose: Modifies an existing database object, such as adding, modifying, or dropping
columns in a table.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
⁕Syntax:
sql
ALTER TABLE table_name
[ADD | MODIFY | DROP ] column_name datatype [constraints];
EXAMPLES:
⁕Add a column:
sql
ALTER TABLE Students ADD Email VARCHAR(100);
⁕Modify a column’s datatype:
sql
ALTER TABLE Students MODIFY Age VARCHAR(3);
⁕Drop a column:
sql
ALTER TABLE Students DROP COLUMN Email;
⁕Rename a column:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
sql
ALTER TABLE table_name
Rename column old_column_name to new column name;
TRUNCATE
● Purpose: Removes all rows from a table, resetting it to an empty state while
preserving its structure (columns, constraints, indexes).
● Syntax:
sql
TRUNCATE TABLE table_name;
⁕Example:
sql
TRUNCATE TABLE Students;
RENAME
● Purpose: Changes the name of an existing database object, such as a table or column.
● Syntax (varies by database):
⁕For tables:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
sql
ALTER TABLE old_table_name RENAME TO new_table_name;
or (in some databases like Oracle):
sql
RENAME old_table_name TO new_table_name;
DROP
● Definition: Removes a database object (e.g., table, index, schema) from the database.
● Why DDL?: Modifies the database structure by deleting object metadata.
⁕Syntax:
sql
DROP TABLE table_name;
⁕Example:
sql
DROP TABLE Students;
Deletes the Students table, moving it to the recycle bin (in Oracle, if enabled).
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
FLASHBACK
● Definition: Restores a dropped object or reverts a table/database to a prior state
(Oracle-specific).
● Why DDL?: Affects object metadata or structural state (e.g., recovering a table).
⁕Syntax:
sql
FLASHBACK TABLE table_name TO BEFORE DROP;
or
sql
FLASHBACK TABLE table_name TO TIMESTAMP timestamp;
⁕EXAMPLE:
sql
FLASHBACK TABLE Students TO BEFORE DROP;
Restores the Students table from the recycle bin.
PURGE
● Definition: Permanently deletes a dropped object or clears the recycle bin, preventing
recovery (Oracle-specific).
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Why DDL?: Removes object metadata from the database.
⁕Syntax:
sql
PURGE TABLE table_name;
or
sql
PURGE RECYCLEBIN;
⁕Example:
sql
PURGE TABLE Students;
Permanently deletes the Students table from the recycle bin.
🔹 What is DML
Data Manipulation Language (DML) in SQL refers to a set of commands used to manipulate
data within a database's tables. Unlike Data Definition Language (DDL), which manages
database structure (e.g., tables, schemas), DML focuses on inserting, updating, deleting, and
retrieving data in tables.
● Common DML Commands:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
○ INSERT : Add new data.
○ UPDATE : Modify existing data.
○ DELETE : Remove data.
○ INSERT ALL : Inserts multiple rows into one or more tables in a single
statement, optionally with conditions.
○ MERGE : Conditionally updates, inserts, or deletes rows in a target
table based on a comparison with a source table or dataset.
INSERT
INSERT is a DML command that adds one or more rows of data to a specified table in a
database.
Syntax
sql
INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);
✅ Example:
Suppose you have a table called employees:
sql
CREATE TABLE employees (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
salary NUMBER
);
You can insert a new employee like this:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
sql
INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (101, 'John', 'Doe', TO_DATE('2025-07-10', 'YYYY-MM-DD'), 50000);
Insert Without Column Names:
If you're inserting values into all columns in the correct order, you can omit the column list:
sql
INSERT INTO employees
VALUES (102, 'Jane', 'Smith', TO_DATE('2025-07-01', 'YYYY-MM-DD'), 60000);
✅ Basic Syntax with DATE:
sql
INSERT INTO table_name (date_column)
VALUES (TO_DATE('YYYY-MM-DD', 'YYYY-MM-DD'));
Example :
sql
INSERT INTO events (event_id, event_name, event_date)
VALUES (1, 'Meeting', TO_DATE('2025-07-10', 'YYYY-MM-DD'));
✅ Basic Syntax with TIMESTAMP:
sql
INSERT INTO table_name (timestamp_column)
VALUES (TO_TIMESTAMP('YYYY-MM-DD HH24:MI:[Link]', 'YYYY-MM-DD HH24:MI:[Link]'));
Example :
sql
INSERT INTO events (event_id, event_name, event_time)
VALUES (2, 'System Upgrade', TO_TIMESTAMP('2025-07-10 14:30:00.123', 'YYYY-MM-DD
HH24:MI:[Link]'));
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Update
In Oracle SQL, the UPDATE statement is a DML (Data Manipulation Language) command
used to modify existing records in a table.
Syntax:
sql
UPDATE table_name
SET column1 = value1,
column2 = value2,
...
WHERE condition;
🔸 Important: Always use a WHERE clause to avoid updating all rows unless that is your
intention.
Example:
sql
CREATE TABLE employees (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER,
hire_date DATE
);
Example 1: Update a Single Column
sql
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;
This updates the salary of the employee with employee_id = 101.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Example 2: Update Multiple Columns
sql
UPDATE employees
SET first_name = 'John',
last_name = 'Smith'
WHERE employee_id = 102;
Commit:
The COMMIT statement is used to permanently save those changes to the database.
Syntax:
sql
UPDATE table_name
SET column1 = value1,
column2 = value2
WHERE condition;
COMMIT;
🟢 COMMIT makes the changes permanent and visible to all users.
🔴 If you do not issue COMMIT, the changes are only visible in your session and can be rolled
back using ROLLBACK.
Delete
In SQL, the DELETE statement is a DML (Data Manipulation Language) command used to remove one
or more rows from a table.
Basic DELETE Syntax:
sql
DELETE FROM table_name
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
WHERE condition;
🔴 Important: Always use a WHERE clause unless you want to delete all rows from the table.
Example:
sql
CREATE TABLE employees (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
department VARCHAR2(50)
);
Example 1: Delete a Specific Row
sql
DELETE FROM employees
WHERE employee_id = 101;
Deletes the row where employee_id = 101.
Example 2: Delete Based on a Condition
sql
DELETE FROM employees
WHERE department = 'HR';
Deletes all employees in the HR department.
Example 3: Delete All Rows (but keep table structure)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
sql
DELETE FROM employees;
🔔 This deletes all rows from the table but keeps the table itself.
Example 4: Use with COMMIT
sql
DELETE FROM employees
WHERE department = 'IT';
COMMIT;
Makes the deletion permanent.
Insert all
In SQL, INSERT ALL is a DML statement that allows you to insert multiple rows into one or more
tables using a single SQL statement. It is very useful when you want to insert multiple records efficiently.
✅ Syntax of INSERT ALL:
sql
INSERT ALL
INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
...
SELECT * FROM dual;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔸 DUAL is a special one-row, one-column table used in Oracle when a SELECT is required
syntactically.
Example: INSERT ALL into One Table
sql
INSERT ALL
INTO employees (employee_id, first_name, last_name, salary)
VALUES (201, 'Alice', 'Smith', 60000)
INTO employees (employee_id, first_name, last_name, salary)
VALUES (202, 'Bob', 'Johnson', 62000)
INTO employees (employee_id, first_name, last_name, salary)
VALUES (203, 'Charlie', 'Williams', 58000)
SELECT * FROM dual;
✅ This inserts three rows into the employees table in one SQL command.
Insert into Multiple Tables (Advanced Usage)
sql
INSERT ALL
INTO employees (employee_id, first_name, last_name, salary)
VALUES (204, 'Diana', 'Brown', 70000)
INTO audit_log (log_id, action, action_date)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
VALUES (1, 'Insert into employees', SYSDATE)
SELECT * FROM dual;
This example assumes you have a second table called audit_log.
🔹 Key Notes
● INSERT ALL is not the same as BULK INSERT or INSERT INTO ... SELECT.
● Use INSERT FIRST if you want to insert into only one table based on conditions (first match
only).
● You must use SELECT * FROM dual at the end, even if inserting hardcoded values.
Syntax: INSERT ALL with Subquery
sql
INSERT ALL
INTO table1 (col1, col2) VALUES (sub_col1, sub_col2)
INTO table2 (col3, col4) VALUES (sub_col3, sub_col4)
SELECT sub_col1, sub_col2, sub_col3, sub_col4
FROM source_table
WHERE condition;
🔸 The subquery pulls data from a source table and the INSERT ALL pushes the same data (or
different parts of it) into one or more destination tables.
Merge
In Oracle SQL, the MERGE statement is a DML command used to combine INSERT and UPDATE
(or DELETE) in a single operation.
It's also called "UPSERT", because it will insert new rows or update existing rows depending on whether a
match is found.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Syntax of MERGE in Oracle:
sql
MERGE INTO target_table t
USING source_table s
ON (t.key_column = s.key_column)
WHEN MATCHED THEN
UPDATE SET t.col1 = s.col1, t.col2 = s.col2
WHEN NOT MATCHED THEN
INSERT (col1, col2, col3)
VALUES (s.col1, s.col2, s.col3);
Example Tables:
sql
-- Target table
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
name VARCHAR2(50),
salary NUMBER
);
-- Source table
CREATE TABLE new_employees (
emp_id NUMBER,
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
name VARCHAR2(50),
salary NUMBER
);
Example: MERGE to Update or Insert
sql
MERGE INTO employees e
USING new_employees n
ON (e.emp_id = n.emp_id)
WHEN MATCHED THEN
UPDATE SET [Link] = [Link],
[Link] = [Link]
WHEN NOT MATCHED THEN
INSERT (emp_id, name, salary)
VALUES (n.emp_id, [Link], [Link]);
🔍 What this does:
● If emp_id exists in employees, it updates the name and salary.
● If emp_id does not exist, it inserts the row into employees.
🔹 What is DRL or DQL
DRL (Data Retrieval Language) or DQL (Data Query Language) is a subset of SQL used only to
query data from one or more tables in a database. In Oracle SQL, the SELECT statement is the only
command in this category.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Purpose of DRL / DQL in Oracle
● Retrieve data without modifying it
● Analyze, view, or filter data
● Not transactional (does not need COMMIT or ROLLBACK)
✅ Main Command: SELECT
sql
SELECT column1, column2, ...
FROM table_name
WHERE condition;
🧾 Example Table: employees
Let's say you have this table:
sql
CopyEdit
CREATE TABLE employees (
emp_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
department VARCHAR2(50),
salary NUMBER
);
With data:
sql
CopyEdit
INSERT INTO employees VALUES (101, 'Alice', 'Smith', 'HR', 50000);
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
INSERT INTO employees VALUES (102, 'Bob', 'Johnson', 'IT', 65000);
INSERT INTO employees VALUES (103, 'Charlie', 'Lee', 'IT', 62000);
INSERT INTO employees VALUES (104, 'Diana', 'White', 'Finance', 58000);
COMMIT;
DQL / DRL Examples in Oracle SQL
🔸 1. Retrieve All Data
sql
SELECT * FROM employees;
✅ Shows all records and columns from the table.
🔸 2. Select Specific Columns
sql
SELECT first_name, department FROM employees;
✅ Retrieves only names and departments.
🔸 3. Filter with WHERE Clause
sql
SELECT first_name, salary
FROM employees
WHERE department = 'IT';
✅ Gets IT employees and their salaries.
🔸 4. Sort Results with ORDER BY
sql
SELECT first_name, salary
FROM employees
ORDER BY salary DESC;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Lists employees from highest to lowest salary.
🔸 5. Aggregate Data with GROUP BY
sql
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
✅ Shows average salary for each department.
Operators in sql
1. Arithmetic operators
+, -, *, /
2. Relational operators
=, != or <>, <, <=, >, >=
3. Logical operators
AND, OR, NOT
4. Special operators
IN, BETWEEN-AND, LIKE, IS NULL
5. Set operators
UNION, UNION ALL, INTERSECT, MINUS
6. Concatenation operators
||
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Arithmetic Operators in SQL
SQL provides arithmetic operators to perform mathematical operations on numeric values in
queries, expressions, and calculations.
🔹 List of Arithmetic Operators
Operator Description Example
+ Addition a+b
- Subtraction a-b
* Multiplication a*b
/ Division a/b
1. + (Addition)
Adds two numbers.
sql
SELECT salary, salary + 1000 AS increased_salary
FROM employees;
✅ This query adds 1000 to each employee's salary.
🔹 2. - (Subtraction)
Subtracts the second number from the first.
sql
SELECT salary, salary - 500 AS adjusted_salary
FROM employees;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Subtracts 500 from each employee's salary.
🔹 3. * (Multiplication)
Multiplies two numbers.
sql
SELECT salary, salary * 2 AS double_salary
FROM employees;
✅ Multiplies each salary by 2.
🔹 4. / (Division)
Divides the first number by the second.
sql
SELECT salary, salary / 2 AS half_salary
FROM employees;
✅ Divides each salary by 2.
🔹 Combined Example
You can use multiple arithmetic operators in the same query:
sql
SELECT
salary,
salary + 1000 - 200 AS bonus_adjusted_salary,
salary * 1.1 AS salary_with_10_percent_raise,
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
salary / 12 AS monthly_salary
FROM employees;
✅ This query:
● Adds 1000 and subtracts 200 (bonus logic),
● Increases salary by 10%,
● Calculates monthly salary assuming 12 months.
✅ Relational Operators in SQL
In SQL, relational operators (also called comparison operators) are used to compare two values. These
operators return a Boolean value: TRUE, FALSE, or NULL (unknown).
🔹 List of Relational Operators
Operator Description Example
= Equal to salary = 5000
!= or <> Not equal to salary != 5000
> Greater than salary > 5000
< Less than salary < 5000
>= Greater than or equal to salary >= 5000
<= Less than or equal to salary <= 5000
🔹 Examples Using the employees Table
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
1. Equality
sql
SELECT * FROM employees
WHERE department_id = 10;
✅ Returns all employees in department 10.
2. Not Equal
sql
SELECT * FROM employees
WHERE job_id <> 'IT_PROG';
✅ Returns all employees who are not IT programmers.
3. Greater Than
sql
SELECT * FROM employees
WHERE salary > 6000;
✅ Returns employees with a salary greater than 6000.
4. Less Than or Equal
sql
SELECT * FROM employees
WHERE salary <= 4000;
✅ Returns employees with a salary less than or equal to 4000.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Logical Operators in SQL
In SQL, logical operators are used to combine multiple conditions in a WHERE clause or to control the
flow of logical expressions. The main logical operators in Oracle SQL are:
🔹 1. AND
● Returns TRUE if both conditions are true.
Example:
sql
SELECT * FROM employees
WHERE department_id = 10 AND salary > 5000;
🔹 2. OR
● Returns TRUE if either condition is true.
Example:
sql
SELECT * FROM employees
WHERE department_id = 10 OR salary > 5000;
🔹 3. NOT
● Negates a condition; returns TRUE if the condition is false.
Example:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
sql
SELECT * FROM employees
WHERE NOT department_id = 10;
✅ Special Operators in SQL
1. IN
Checks if a value is in a list of values or a subquery result.
Example:
sql
SELECT * FROM employees
WHERE department_id IN (10, 20, 30);
Note: Be careful with IN and NULL — if the list contains NULL, the result may be unknown.
2. BETWEEN ... AND
Checks if a value is in a specified inclusive range.
Example:
sql
SELECT * FROM employees
WHERE salary BETWEEN 3000 AND 7000;
3. LIKE
Used for pattern matching with wildcards:
● % matches any sequence of characters.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● _ matches a single character.
Example:
sql
SELECT * FROM employees
WHERE last_name LIKE 'Sm%'; -- Matches 'Smith', 'Smythe', etc.
4. IS NULL / IS NOT NULL
Tests whether a value is NULL.
Example:
sql
SELECT * FROM employees
WHERE commission_pct IS NULL;
✅ Set Operators in SQL
In SQL, set operators are used to combine the results of two or more SELECT statements into a single
result set. They work similarly to mathematical set operations like UNION, INTERSECT, and MINUS.
🔹 List of Set Operators in Oracle SQL
Operator Description
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
UNION Combines results from two queries and removes duplicates
UNION ALL Combines all results from two queries including duplicates
INTERSECT Returns only the common rows between two queries
MINUS Returns rows from the first query that are not in the second
🔸 Rules for Using Set Operators
1.Both SELECT statements must have the same number of columns.
2.The data types of corresponding columns must be compatible.
3.The column order must match.
4.Set operators must appear outside of subqueries, not inside them directly.
🔹 1. UNION – Combine and Remove Duplicates
sql
SELECT department_id FROM employees
UNION
SELECT department_id FROM departments;
✅ Returns unique department IDs from both tables.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔹 2. UNION ALL – Combine and Keep Duplicates
sql
SELECT department_id FROM employees
UNION ALL
SELECT department_id FROM departments;
✅ Returns all department IDs including duplicates.
🔹 3. INTERSECT – Return Common Rows Only
sql
SELECT department_id FROM employees
INTERSECT
SELECT department_id FROM departments;
✅ Returns only the department IDs that exist in both tables.
🔹 4. MINUS – Return Rows from First Query Only
sql
SELECT department_id FROM departments
MINUS
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
SELECT department_id FROM employees;
✅ Returns department IDs that exist in departments but not in employees.
✅ Concatenation Operators in SQL
In Oracle SQL, the concatenation operator is used to join two or more strings together.
✅ Syntax:
sql
string1 || string2 [|| string3 ...]
● The || operator joins strings together.
● It automatically converts numbers or dates to strings when used.
Example : Basic String Concatenation
sql
SELECT 'Hello' || ' ' || 'World' AS greeting FROM dual;
Result:
markdown
GREETING
---------
Hello World
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔹 Transaction Control Language
In SQL, TCL (Transaction Control Language) is used to manage transactions in a database.
Transactions are logical units of work that contain one or more SQL statements, and TCL commands let
you control when these changes are saved (committed) or undone (rolled back).
Main TCL Commands in SQL
Command Description
COMMIT Saves all changes made in the transaction to the database permanently
ROLLBACK Undoes changes made in the current transaction
SAVEPOINT Sets a point within a transaction to which you can later roll back
1. COMMIT
Permanently saves changes made by INSERT, UPDATE, or DELETE.
✅ Example:
sql
INSERT INTO employees (employee_id, first_name) VALUES (999, 'Test');
COMMIT;
● Now the new row is saved and visible to others.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
2. ROLLBACK
Cancels all changes made in the current transaction (since the last COMMIT or SAVEPOINT).
✅ Example:
sql
DELETE FROM employees WHERE employee_id = 999;
ROLLBACK;
● The deletion is canceled.
3. SAVEPOINT
Creates a named point in a transaction you can roll back to without undoing the entire transaction.
✅ Example:
sql
SAVEPOINT sp1;
UPDATE employees SET salary = salary + 1000 WHERE department_id = 10;
SAVEPOINT sp2;
UPDATE employees SET salary = salary + 500 WHERE department_id = 20;
ROLLBACK TO sp2; -- Only undoes the last update
✅ Combined TCL Example
Let's say Alice transfers 200 to Bob, and we want full control over this transaction with SAVEPOINT,
ROLLBACK, and COMMIT.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
sql
-- Start transaction (Oracle begins automatically after last COMMIT)
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
-- Save point after deducting from Alice
SAVEPOINT after_alice_deduction;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;
-- Oops! Let's say there's a mistake (e.g., wrong account or wrong amount)
-- We decide to roll back only to the point after Alice's deduction
ROLLBACK TO after_alice_deduction;
-- Now fix the mistake and re-add the correct amount to Bob
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;
-- Commit the whole transaction
COMMIT;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Step Action
1 Alice's balance is reduced by 200
2 Savepoint created (after_alice_deduction)
3 Amount added to Bob, but we realize an issue
4 Rollback to savepoint: undo only Bob’s credit
5 Re-credit Bob correctly
6 Commit the entire transaction
🔸 Final Account Balances:
● Alice: 800
● Bob: 1700
🛑 Note:
If you had used ROLLBACK without a savepoint, both updates (to Alice and Bob) would have been
undone.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔹 Data Control Language
In SQL, DCL (Data Control Language) is used to control access to data in the database. DCL
commands manage privileges and permissions for users and roles.
Main DCL Commands in SQL
Command Description
GRANT Gives privileges to users or roles
REVOKE Removes privileges from users or roles
🔸 1. GRANT – Give Privileges
The GRANT command is used to assign access rights to database objects (like tables, views, procedures)
or system privileges.
✅ Syntax:
sql
GRANT privilege_type ON object TO user_or_role;
🔹 Example 1: Granting access to a table
sql
GRANT SELECT, INSERT ON employees TO hr_user;
✅ This allows hr_user to select and insert into the employees table.
🔹 Example 2: Granting system privileges
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
sql
GRANT CREATE SESSION TO hr_user;
✅ Allows the user to log in to the database.
🔹 Example 3: Grant with WITH GRANT OPTION
sql
GRANT SELECT ON employees TO hr_user WITH GRANT OPTION;
✅ Allows hr_user to give that same SELECT privilege to other users.
🔸 2. REVOKE – Remove Privileges
The REVOKE command is used to take back privileges granted earlier.
✅ Syntax:
sql
REVOKE privilege_type ON object FROM user_or_role;
🔹 Example 1: Revoking object privileges
sql
REVOKE SELECT, INSERT ON employees FROM hr_user;
✅ Removes SELECT and INSERT rights from hr_user.
🔹 Example 2: Revoking system privileges
sql
REVOKE CREATE SESSION FROM hr_user;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Prevents hr_user from logging into the database.
🔸 Object vs System Privileges
Type Example Privileges
System CREATE SESSION, CREATE TABLE, DROP USER
Object SELECT, INSERT, UPDATE, DELETE on specific tables or views
⚠️ Notes:
● Only a DBA or a user with GRANT ANY PRIVILEGE can grant system privileges.
● Revoking a privilege with WITH GRANT OPTION can cascade and remove access from users
who received it indirectly.
● DCL commands like GRANT and REVOKE cause an implicit commit.
🔹 GROUP BY
In SQL, the GROUP BY clause is used to aggregate data based on one or more columns. It groups rows
that have the same values in specified columns into summary rows, typically in combination with
aggregate functions like SUM(), COUNT(), AVG(), MAX(), or MIN().
🔹 Syntax
sql
SELECT column1, column2, AGGREGATE_FUNCTION(column3)
FROM table_name
GROUP BY column1, column2;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● column1, column2 → grouping columns
● AGGREGATE_FUNCTION(column3) → an aggregate function applied to other column(s)
🎓 Suppose you have this table called marks:
student_id subject marks
1 Math 85
1 English 78
2 Math 92
2 English 81
3 Math 75
3 English 88
Example 1: Total Marks per Student
sql
SELECT student_id, SUM(marks) AS total_marks
FROM marks
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
GROUP BY student_id;
Output:
student_id total_marks
1 163
2 173
3 163
Example 2: Average Marks per Subject
sql
SELECT subject, AVG(marks) AS avg_marks
FROM marks
GROUP BY subject;
Output:
subject avg_marks
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Math 84.0
English 82.3
Example 3: Count of Students per Subject
sql
SELECT subject, COUNT(student_id) AS student_count
FROM marks
GROUP BY subject;
Output:
subject student_count
Math 3
English 3
Example 4: Using HAVING — Subjects with Average Marks > 83
sql
SELECT subject, AVG(marks) AS avg_marks
FROM marks
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
GROUP BY subject
HAVING AVG(marks) > 83;
Output:
subject avg_marks
Math 84.0
🎓 Table: marks
student_id subject marks semester
1 Math 85 1
1 English 78 1
2 Math 92 1
2 English 81 1
3 Math 75 2
3 English 88 2
4 Math 60 2
4 English 72 2
✅ Example 1 : Average Marks per Subject for Semester 1, Ordered by
Average Descending
🔹 SQL Query:
sql
SELECT subject, AVG(marks) AS avg_marks
FROM marks
WHERE semester = 1
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
GROUP BY subject
ORDER BY avg_marks DESC;
🔸 Step-by-Step Explanation:
Clause Purpose
SELECT What columns to display (subject, AVG(marks))
FROM From which table (marks)
WHERE Filter only records from semester = 1
GROUP BY Group rows by subject to calculate average marks
ORDER BY Sort the results by avg_marks in descending order
🔹 Output:
subject avg_marks
Math 88.5
English 79.5
✅ Example 2: Total Marks per Student in Semester 2, Ordered by Student
ID
sql
SELECT student_id, SUM(marks) AS total_marks
FROM marks
WHERE semester = 2
GROUP BY student_id
ORDER BY student_id;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔹 Output:
student_id total_marks
3 163
4 132
🔹 HAVING CLAUSE
The HAVING clause in SQL is used to filter groups of rows after applying the GROUP BY clause. It's like
a WHERE clause, but specifically for aggregate results (like SUM(), AVG(), COUNT(), etc.).
🔹 Syntax
sql
SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
WHERE condition
GROUP BY column1
HAVING AGGREGATE_FUNCTION(column2) condition;
🔸 Example Using marks Table
Suppose you have this table:
student_id subject marks semester
1 Math 85 1
1 English 78 1
2 Math 92 1
2 English 81 1
3 Math 75 2
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
3 English 88 2
4 Math 60 2
4 English 72 2
✅ Example 1: Get Subjects with Average Marks > 80
sql
SELECT subject, AVG(marks) AS avg_marks
FROM marks
GROUP BY subject
HAVING AVG(marks) > 80;
Result:
subject avg_marks
Math 78.0
English 79.75
(Assuming actual values—adjust depending on your data.)
✅ Example 2: Total Marks per Student in Semester 2, Only If Total > 150
sql
SELECT student_id, SUM(marks) AS total_marks
FROM marks
WHERE semester = 2
GROUP BY student_id
HAVING SUM(marks) > 150;
Result:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
student_id total_marks
3 163
🔍 Key Difference Between WHERE and HAVING:
Clause Filters... Can use aggregates?
WHERE Before grouping ❌ No
HAVING After grouping ✅ Yes
🔹 OFFSET AND FETCH
In SQL, the OFFSET and FETCH clauses are used for pagination—retrieving a subset of rows from a
result set, usually for things like pages of results in an app or website.
OFFSET
In Oracle SQL, the OFFSET clause is used to skip a specific number of rows in the result set. It is
typically used in combination with ORDER BY and optionally FETCH to implement pagination.
✅ Syntax
sql
SELECT columns
FROM table_name
ORDER BY column
OFFSET n ROWS;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● OFFSET n ROWS skips the first n rows
● You must use ORDER BY to ensure a deterministic result
🔹 Example: Skip First 3 Rows from a students Table
Assume the students table:
student_id name marks
1 Alice 90
2 Bob 85
3 Carol 88
4 David 92
5 Emma 84
🔸 Query: Skip First 3 Rows by Marks (Descending)
sql
SELECT student_id, name, marks
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
FROM students
ORDER BY marks DESC
OFFSET 3 ROWS;
Result:
student_id name marks
2 Bob 85
5 Emma 84
FETCH
The FETCH clause in Oracle SQL is used to limit the number of rows returned by a query. It is
commonly paired with OFFSET to implement pagination.
🔹 Syntax
sql
SELECT columns
FROM table_name
ORDER BY column
FETCH FIRST n ROWS ONLY;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
or with pagination:
sql
SELECT columns
FROM table_name
ORDER BY column
OFFSET x ROWS
FETCH NEXT y ROWS ONLY;
🔸 Example: Top 3 Students by Marks
Assume a table students
student_id name marks
1 Alice 90
2 Bob 85
3 Carol 88
4 David 92
5 Emma 84
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Query: Get Top 3 Students
sql
SELECT student_id, name, marks
FROM students
ORDER BY marks DESC
FETCH FIRST 3 ROWS ONLY;
Result:
student_id name marks
4 David 92
1 Alice 90
3 Carol 88
🔹 FETCH with OFFSET (Pagination)
sql
-- Get students ranked 4–5 by marks
SELECT student_id, name, marks
FROM students
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
ORDER BY marks DESC
OFFSET 3 ROWS
FETCH NEXT 2 ROWS ONLY;
🔹 FETCH Variants
Clause Description
FETCH FIRST n ROWS ONLY Returns only the first n rows
FETCH NEXT n ROWS ONLY Same as above, used after OFFSET
FETCH FIRST n ROWS WITH TIES Includes all rows that tie with the last row
Join operations in SQL
In Oracle SQL, JOIN operations are used to combine rows from two or more tables based on a related
column. This is essential for working with relational data.
Here's clear example of a JOIN operation in Oracle SQL using two tables.
📘 Tables
employees table:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
emp_id emp_name
1 Alice
2 Bob
3 Carol
departments table:
dept_id emp_id dept_name
10 1 HR
20 2 Finance
✅ JOIN Example: Show Employee Names and Their Departments
sql
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d
ON e.emp_id = d.emp_id;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔍 Result:
emp_name dept_name
Alice HR
Bob Finance
🔸 Explanation:
● This is an INNER JOIN: it returns only employees who have a matching department.
● Carol is not included because she doesn't belong to any department in the departments table.
✅ Types of JOINs in Oracle SQL
JOIN Type Description
INNER JOIN Returns rows that have matching values in both tables
OUTERJOIN An outer join returns all matching rows plus non-matching rows from one or
both tables, with NULL for missing values.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
CROSS JOIN Returns the Cartesian product of the two tables
SELF JOIN Joins a table to itself
INNER JOIN:
An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows
that satisfy the join condition.
1. Equijoin
2. Nonequijoin
1.EQUIJOIN in Oracle SQL
An Equijoin is a type of JOIN that combines rows from two or more tables based on an equality
condition between specified columns—usually using the = operator.
In Oracle SQL, an Equijoin is typically written using either:
● Oracle legacy syntax: using the WHERE clause
● ANSI syntax (recommended): INNER JOIN ... ON
Oracle legacy syntax:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🎓 Example Tables
Table: students
student_id name
1 Alice
2 Bob
3 Carol
Table: marks
student_id subject marks
1 Math 90
2 Science 85
🔸 Oracle-Style Equijoin Query:
sql
SELECT s.student_id, [Link], [Link], [Link]
FROM students s, marks m
WHERE s.student_id = m.student_id;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Output:
student_id name subject marks
1 Alice Math 90
2 Bob Science 85
🔍 Explanation:
● The WHERE s.student_id = m.student_id is the equijoin condition.
● It returns only rows where student_id exists in both tables.
● This is functionally the same as using INNER JOIN ... ON, but written in Oracle’s legacy syntax.
Note: It changes the orders of records in result each time. It follows the join column order sometimes and
first column order sometimes so on…if you want specific order then use order by.
ANSI syntax:
🔹 Example Scenario
Let's say we have two tables:
employees table:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
emp_id emp_name
1 Alice
2 Bob
3 Carol
departments table:
dept_id emp_id dept_name
10 1 HR
20 2 Finance
✅ Example 1: Equijoin Using ANSI JOIN Syntax
sql
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d
ON e.emp_id = d.emp_id;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔍 Result:
emp_name dept_name
Alice HR
Bob Finance
[Link]:
A Nonequijoin is a join with a join condition containing all operators other than equality operator.
Nonequi Conditions
● >
● <
● >=
● <=
● != or <> (though rarely useful in joins)
1️⃣ Nonequi Join Using >
Scenario: Employees matched to salary tiers where employee salary is greater
than the tier minimum.
sql
SELECT e.emp_name, [Link], t.tier_name
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
FROM employees e, salary_tiers t
WHERE [Link] > t.min_salary;
● Explanation: Join employees with all tiers having a min_salary less than employee's salary.
2️⃣ Nonequi Join Using <
Scenario: Products matched to price categories where product price is less than
the category max price.
sql
SELECT p.product_name, [Link], c.category_name
FROM products p, price_categories c
WHERE [Link] < c.max_price;
● Explanation: Join products with categories where price is below max price.
3️⃣ Nonequi Join Using <> (Not Equal)
Scenario: Find all pairs of students where student IDs are not equal (i.e.,
different students).
sql
SELECT s1.student_name AS student1, s2.student_name AS student2
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
FROM students s1, students s2
WHERE s1.student_id <> s2.student_id;
● Explanation: Self-join excluding pairs with the same student.
Summary
Operator Usage Example Condition
> Greater than [Link] > t.min_salary
< Less than [Link] < c.max_price
<> Not equal (different values) s1.student_id <> s2.student_id
Band join:
A Band join is a special type of nonequi join in which key values in one data set must fall with in the
specified range (“band”) of the second data set.
How Does a Band Join Work?
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Suppose you have:
● A table A with a value column (e.g., salary)
● A table B with two columns defining a range: min_value and max_value
You want to join rows from A to B where:
css
[Link] BETWEEN B.min_value AND B.max_value
This is the band join condition because the join matches rows where [Link] lies within the band
(range) defined by B.
Why Use Band Joins?
● Useful for mapping values to ranges (e.g., employee salaries to salary grades, transaction
amounts to discount tiers).
● It’s a natural way to do range-based categorization or bucketing.
● More flexible than simple equi-joins.
Oracle Style Band Join Example (Summary)
sql
SELECT a.column1, b.column2
FROM table_a a, table_b b
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
WHERE a.value_column BETWEEN b.min_value AND b.max_value;
Important Notes
● Because the join condition is not equality, the result can have multiple matches per row from
the first table (if ranges overlap).
● Oracle style uses the WHERE clause, not the ANSI JOIN syntax.
● You can also express the band join using two conditions:
sql
WHERE a.value_column >= b.min_value
AND a.value_column <= b.max_value
OUTER JOIN :
An outer join returns all matching rows plus non-matching rows from one or both tables, with
NULL for missing values.
1. LEFT OUTERJOIN:
2. RIGHT OUTERJOIN
3. FULL OUTERJOIN
🔸 1. Oracle Style (Proprietary Syntax using (+))
➤ Left Outer Join:
Returns all rows from the left table, even if there’s no match in the right table.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Syntax:
sql
SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id(+);
🟢 Meaning: Show all employees, even if they have no department.
➤ Right Outer Join:
Returns all rows from the right table, even if there’s no match in the left table.
Syntax:
sql
SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id(+) = d.dept_id;
🟢 Meaning: Show all departments, even if no employee is assigned.
⛔ Oracle's (+) syntax does not support full outer joins.
➤ Full Outer Join: Using UNION
syntax
sql
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
-- LEFT OUTER JOIN part (all employees, with departments if matched)
SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id(+)
UNION
-- RIGHT OUTER JOIN part (all departments, with employees if matched)
SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id(+) = d.dept_id;
🔍 Explanation
● The first query gets all employees (even if they have no department).
● The second query gets all departments (even if they have no employees).
● UNION removes duplicates and combines the result.
✅ This effectively mimics a FULL OUTER JOIN.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔸 2. Ansi style
➤ Left Outer Join:
Returns all rows from the left table and matching rows from the right table. If no match, fills with
NULL.
syntax:
sql
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.dept_id = d.dept_id;
🟢 Keeps all employees, even if they don’t belong to any department.
➤ Right Outer Join:
Returns all rows from the right table and matching rows from the left table.
syntax:
sql
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT OUTER JOIN departments d
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
ON e.dept_id = d.dept_id;
🟢 Keeps all departments, even those with no employees.
➤ Full Outer Join:
Returns all rows from both tables. Where there's no match, NULL fills the missing side.
syntax:
sql
SELECT e.emp_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d
ON e.dept_id = d.dept_id;
🟢 Keeps all employees and all departments, matched or not.
✅ ANSI Style Benefits
● Easier to read and maintain
● Supports all outer join types (Oracle's (+) style does not support FULL OUTER JOIN)
● Works across all modern SQL platforms
Self Join:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by
table aliases that qualify column names in the join condition.
✅ When to Use a Self Join
Use a self join when:
● A table has a hierarchical or referential relationship within itself.
● You need to compare rows in the same table, such as:
○ Employees and their managers
○ Products and similar products
○ Students and their mentors
✅ What Does != Do in a Self Join?
● It returns pairs of different rows from the same table.
● Common use cases include:
○ Finding all possible combinations of distinct rows.
○ Comparing rows excluding self (e.g., pairing students who aren't the same person).
🔸 Oracle SQL (Proprietary Style)
Here’s a simple self join using != in Oracle-style SQL:
sql
SELECT s1.student_name AS student1,
s2.student_name AS student2
FROM students s1, students s2
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
WHERE s1.student_id != s2.student_id;
🧾 Example Table: students
student_id student_name
1 Alice
2 Bob
3 Charlie
✅ Output (All Pairs of Different Students)
STUDENT1 STUDENT2
Alice Bob
Alice Charlie
Bob Alice
Bob Charlie
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Charlie Alice
Charlie Bob
🔍 Explanation
● The self join creates all possible row combinations.
● The condition s1.student_id != s2.student_id ensures that a student is not paired with themselves.
🔸 Ansi style
🔸 Example: Compare Students Who Are Not the Same
🎓 Table: students
sql
CREATE TABLE students (
student_id NUMBER,
student_name VARCHAR2(50)
);
📌 Sample Data
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
student_id student_name
1 Alice
2 Bob
3 Charlie
🔹 ANSI Style Self Join Using !=
sql
SELECT s1.student_name AS student1,
s2.student_name AS student2
FROM students s1
JOIN students s2
ON s1.student_id != s2.student_id;
🧾 Output
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
STUDENT STUDENT
1 2
Alice Bob
Alice Charlie
Bob Alice
Bob Charlie
Charlie Alice
Charlie Bob
🔍 Explanation
● The table students is joined to itself using aliases s1 and s2.
● ON s1.student_id != s2.student_id ensures you exclude self-pairs.
● This gives all unique pairings of different students.
Cross join/cartesian join :
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
If two tables in a join query have no join condition, then oracle database returns their cartesian product.
✅ What It Does
● No join condition is used.
● Total number of rows in the result = rows in table A × rows in table B.
🧾 Example Scenario
Let's say we want to generate all combinations of colors and sizes for a product catalog.
🔹 Step 1: Create Sample Tables
sql
CREATE TABLE colors (
color VARCHAR2(20)
);
CREATE TABLE sizes (
size VARCHAR2(20)
);
-- Insert some sample data
INSERT INTO colors VALUES ('Red');
INSERT INTO colors VALUES ('Blue');
INSERT INTO sizes VALUES ('Small');
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
INSERT INTO sizes VALUES ('Medium');
INSERT INTO sizes VALUES ('Large');
🔹 Step 2: ANSI Style CROSS JOIN
sql
SELECT [Link], [Link]
FROM colors c
CROSS JOIN sizes s;
✅ This returns all combinations of color × size.
🔹 Output
COLOR SIZE
Red Small
Red Medium
Red Large
Blue Small
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Blue Medium
Blue Large
🔹 Step 3: Oracle Proprietary Style CROSS JOIN
sql
SELECT [Link], [Link]
FROM colors c, sizes s;
✅ This is the Oracle-style (non-ANSI) cross join — same result, but written with comma-separated tables and
no WHERE clause.
⚠️ If you forget a WHERE clause when doing normal joins using Oracle-style, you might accidentally
perform a cross join.
✅ Summary
Style Syntax Supported in Oracle?
ANSI FROM A CROSS JOIN B ✅ Yes
Oracle (legacy) FROM A, B (no WHERE) ✅ Yes
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
📘 Introduction to Subqueries in SQL
A subquery is a query nested inside another SQL query — typically in the SELECT, FROM, or WHERE
clause.
It helps you retrieve intermediate results used by the outer (main) query.
✅ What Is a Subquery?
A subquery is a SQL query nested inside another query’s SELECT, FROM, or WHERE clause.
It helps break down complex logic into manageable parts.
🔹 Basic Structure
sql
SELECT column1
FROM table
WHERE column2 = (SELECT column3 FROM another_table WHERE condition);
🎯 Why Use Subqueries?
● To filter results based on values from another table
● To calculate derived values (e.g. maximum, average)
● To create virtual tables to join with
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● To improve readability of complex logic
🧾 Example 1: Subquery in WHERE(nested subquery)
Get employees with the highest salary:
sql
SELECT emp_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
● In the Where clause we can write only 255 levels of subqueries.
🧾 Example 2: Subquery in FROM (Inline View)
Get average salary per department:
sql
SELECT dept_id, AVG(salary)
FROM (SELECT * FROM employees WHERE active = 'Y')
GROUP BY dept_id;
● In the From clause we can write unlimited subqueries.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🧾 Example 3: Subquery in SELECT
sql
SELECT emp_name,
(SELECT dept_name FROM departments d WHERE d.dept_id = e.dept_id) AS dept
FROM employees e;
📝 Summary
Feature Description
Purpose Use result of one query inside another
Use cases Filtering, calculating, joining
Benefits More powerful and readable SQL
🧠 When Should We Use a Subquery?
Use a subquery in Oracle SQL when:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Situation Why a Subquery Helps
You need a value that depends on another table Subqueries return scalar or tabular values dynamically
You want to filter based on an aggregated value Use subquery in WHERE to compare with MAX(), etc.
You want to compare across tables Use subquery with IN, EXISTS, =, ANY, ALL
You want to simplify complex joins Use subqueries in FROM to isolate logic
You need conditional logic per row Use correlated subqueries
Use subqueries for the following purposes:
● To define the set of rows to be inserted into the target table of an insert or create table statement.
INSERT INTO ... SELECT — Use a Subquery to Insert Rows
Syntax:
sql
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;
● To define the set of rows to be inserted in a view or materialized view in a create view or create
materialized view statement
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
1. CREATE VIEW with a Subquery
Syntax:
sql
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table1
WHERE condition;
Example: Create a view of high-salary employees
sql
CREATE VIEW high_salary_emps AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 5000;
📌 The subquery (SELECT ...) defines the rows included in the view.
● To define one or more values to be assigned to existing rows in an Update statement
Update Statement with Subquery
sql
UPDATE employees e
SET dept_name = (
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
SELECT d.dept_name
FROM departments d
WHERE d.dept_id = e.dept_id
);
✅ This subquery pulls the correct department name for each employee based on dept_id.
● To provide values for conditions in a whare clause, having clause, or start with clause of select, update
and delete statements
1. Subquery in WHERE Clause
Example: Get employees who work in departments located in 'New York'
sql
SELECT emp_name
FROM employees
WHERE dept_id IN (
SELECT dept_id
FROM departments
WHERE location = 'New York'
);
📌 The subquery returns department IDs to match in the outer query.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
2. Subquery in HAVING Clause
Example: Get departments where the average salary is greater than the company-wide average
sql
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (
SELECT AVG(salary) FROM employees
);
📌 The subquery calculates the company-wide average salary.
3. Subquery in START WITH Clause (Hierarchical Queries)
Example: Get all employees under the same manager as the one with the highest salary
sql
SELECT emp_id, emp_name, manager_id
FROM employees
START WITH emp_id = (
SELECT emp_id
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees)
CONNECT BY PRIOR emp_id = manager_id;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
📌 The subquery in START WITH finds the employee who has the highest salary — then Oracle follows the
hierarchy down from that employee.
4. Subquery in DELETE or UPDATE with WHERE
Example: Delete employees in departments that are marked inactive
sql
DELETE FROM employees
WHERE dept_id IN (
SELECT dept_id
FROM departments
WHERE active = 'N'
);
📌 Subquery returns only the inactive department IDs.
● To define a table to be operated on by a containing query
Types of subquries :
A subquery is a query nested inside another SQL query — used to return data that helps the main (outer) query
make decisions. Subqueries are powerful tools that help you write modular, readable, and efficient SQL.
1. Non-correlated :
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
A non-correlated subquery is a subquery that does not reference any column from the outer query.
It is self-contained and executes only once, and its result is then used by the main (outer) query.
🔹 Key Characteristics
Feature Description
Independent Does not depend on the outer query
Executes Once Runs only once, not per row
Flexible Use Can be used in SELECT, FROM, WHERE, HAVING
Efficient Often more efficient than correlated subqueries
2. Correlated :
A correlated subquery is a subquery that references columns from the outer query.
It is executed once for every row processed by the outer query — making it dependent on the outer
query's current row.
🔹 Key Characteristics
Feature Description
Depends on outer query Uses a value from the outer query inside itself
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Executes repeatedly Runs once for each row in the outer query
Often used for row-by-row Ideal for comparisons within groups
comparisons
Non-correlated subqueries:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
In Oracle SQL, a non-correlated subquery is a subquery that is independent of the outer query — it can be
executed on its own without referencing any columns from the outer query.
🔹 Characteristics of a Non-Correlated Subquery:
● Executed once for the entire outer query.
● Does not reference columns from the outer query.
● Can be placed in the SELECT, FROM, or WHERE clause.
🔸 Syntax Example
sql
SELECT first_name, last_name
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'IT'
);
Explanation:
The subquery:
sql
SELECT department_id FROM departments WHERE department_name = 'IT'
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● is non-correlated because it doesn't use any columns from the outer employees query.
● It returns the department ID for 'IT' once, and that result is used to filter the outer query.
Types of subqueries:
According to returned result:
1. Single row subquey
2. Multiple row subquery
3. Multiple column subquery
[Link] row subquey:
In Oracle SQL, a single-row subquery is a type of subquery that returns only one row and one or more
columns. It's often used in places where only a single value is expected, such as in a WHERE, SELECT, or SET
clause.
🔹 Key Features of Single-Row Subqueries:
● Returns only one row.
● Can return one or more columns (but commonly one column).
● Typically used with comparison operators like =, <, >, <=, >=, or <>.
🔸 Example
sql
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
SELECT salary
FROM employees
WHERE employee_id = 100
);
Explanation:
The subquery:
sql
SELECT salary FROM employees WHERE employee_id = 100
● returns one value (a single salary).
● The outer query finds employees whose salary is higher than that salary.
🔹 Common Operators Used:
Operator Meaning
= Equal to single subquery result
<> Not equal to subquery result
>, < Greater/Less than subquery result
>=, <= Greater/Less than or equal
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔸 Important Notes:
If the subquery returns more than one row, Oracle will throw an error:
sql
ORA-01427: single-row subquery returns more than one row
● To avoid this, ensure the subquery is limited to one row (e.g., using WHERE, ROWNUM, or
MAX/MIN).
🔹 Safe Example Using MAX:
sql
SELECT first_name, last_name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);
● This gets the employee(s) with the highest salary.
● MAX(salary) returns a single value, ensuring no error.
2. Multiple row subquey:
In Oracle SQL, a multiple-row subquery is a subquery that returns more than one row. It’s used when the
outer query needs to compare a value against a set of values.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔹 Key Features of Multiple-Row Subqueries:
● Returns multiple rows.
● Must be used with set operators or multi-row comparison operators like:
○ IN
○ ANY / SOME
○ ALL
🔸 Example 1: Using IN
sql
SELECT first_name, last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id = 1700
);
Explanation:
● The subquery returns a list of department IDs in location 1700.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● The outer query finds employees who belong to any of those departments.
🔸 Example 2: Using ANY
sql
SELECT first_name, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department_id = 50
);
Explanation:
● Compares each employee’s salary to any salary from department 50.
● Returns employees who earn more than at least one person in department 50.
🔍 ANY and SOME are synonyms in Oracle.
🔸 Example 3: Using ALL
sql
SELECT first_name, salary
FROM employees
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department_id = 50
);
Explanation:
● Finds employees whose salary is greater than all salaries in department 50.
❗ Caution
Using a comparison operator like = with a multi-row subquery will result in an error:
sql
SELECT first_name
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE location_id = 1700
);
If the subquery returns more than one row, Oracle will raise:
sql
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
ORA-01427: single-row subquery returns more than one row
✅ To fix it, use IN instead of =.
3. multiple column subquey:
A multiple-column subquery in Oracle SQL returns more than one column per row. You typically use it when
the outer query needs to compare against a combination of columns from the subquery.
🔹 Key Features of Multiple-Column Subqueries:
● Returns two or more columns per row.
● Can return one or multiple rows.
● Usually used in:
○ WHERE ... IN with row value comparisons
○ EXISTS
○ FROM clause (inline views)
🔸 Example 1: Using IN with Multiple Columns
sql
SELECT first_name, last_name, department_id, job_id
FROM employees
WHERE (department_id, job_id) IN (
SELECT department_id, job_id
FROM job_history
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
);
✅ Explanation:
● The subquery returns pairs of (department_id, job_id) from job_history.
● The outer query returns employees who have the same department_id and job_id as in the job
history.
🔸 Example 2: Using EXISTS with a Multi-Column Condition
sql
SELECT first_name, last_name
FROM employees e
WHERE EXISTS (
SELECT 1
FROM departments d
WHERE e.department_id = d.department_id
AND d.location_id = 1700
);
✅ Explanation:
● Even though the subquery isn't returning multiple columns, it uses multiple columns in its condition.
● Returns employees whose department is in location 1700.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔸 Example 3: Using Multiple-Column Subquery in FROM (Inline View)
sql
SELECT dept_name, employee_count
FROM (
SELECT d.department_name AS dept_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
);
✅ Explanation:
● The subquery in the FROM clause returns two columns: department name and count of employees.
● The outer query selects from that result.
❗ Important Notes:
● When using (col1, col2) IN (SELECT col1, col2 ...), the number and order of columns must match.
● Oracle compares the combination of values row by row.
Correlated subqueries:
A correlated subquery in Oracle SQL is a subquery that depends on a column from the outer query. Unlike
a non-correlated subquery, it cannot run independently, because it refers to values from the outer query.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔹 Key Characteristics of Correlated Subqueries:
● Executed once for each row of the outer query.
● References one or more columns from the outer query.
● Often used in WHERE, SELECT, or HAVING clauses.
🔸 Example: Find employees who earn more than the average salary in their
department
sql
SELECT first_name, last_name, department_id, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);
✅ Explanation:
The subquery:
sql
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● references e.department_id, which comes from the outer query.
● So, the subquery is re-evaluated for each employee using their department.
Inline view subquery:
An inline view is a subquery in the FROM clause that behaves like a temporary table or view for the outer
query to use.
✅ Purpose: Often used to simplify complex queries, especially when you want to aggregate or filter data
before applying additional logic.
🔹 Key Features of Inline Views:
● Appears in the FROM clause.
● Returns a result set used like a table or view.
● Can be aliased and joined or filtered in the outer query.
● Helps to improve readability and structure.
🔸 Basic Syntax:
sql
SELECT column1, column2
FROM (
SELECT col1, col2
FROM table_name
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
WHERE condition
) inline_view_alias
WHERE some_condition;
🔸 Example Scenario:
You have an EMPLOYEES table like this:
EMPLOYEE_ID FIRST_NAME SALARY DEPARTMENT_ID
101 John 8000 10
102 Alice 9000 10
103 Bob 10000 20
104 Carol 7000 10
You want to find all employees who earn more than the average salary in their department.
🔸 SQL Using Inline View:
sql
SELECT e.employee_id,
e.first_name,
[Link],
d.avg_salary
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
FROM employees e
JOIN (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)d
ON e.department_id = d.department_id
WHERE [Link] > d.avg_salary;
🔹 Explanation:
Inline View (Subquery):
sql
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
● This subquery calculates the average salary for each department.
● It becomes a temporary result set (aliased as d) available to the outer query.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Outer Query:
● Joins the employees table with the inline view d.
● Filters employees where their salary is greater than the average salary of their department.
✅ Result (Based on Sample Data):
EMPLOYEE_ID FIRST_NAME SALARY AVG_SALARY
102 Alice 9000 8000
103 Bob 10000 10000
Only Alice is returned (John and Carol earn less than 8000, Bob earns equal to avg of 10000).
Scalar subquery:
In Oracle SQL, a scalar subquery is a subquery that returns exactly one value (a single row and one
column) and can be used anywhere a single value (scalar) is allowed — such as in the SELECT, WHERE, or
ORDER BY clauses.
🔹 Characteristics of Scalar Subqueries
● Returns one row and one column (i.e., one value).
● Can be used in place of a constant or column.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
If it returns more than one row → Oracle throws:
sql
ORA-01427: single-row subquery returns more than one row
🔸 Example 1: Scalar Subquery in SELECT
sql
SELECT first_name,
salary,
(SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;
✅ Explanation:
● The subquery (SELECT AVG(salary) FROM employees) returns a single value (e.g., 7800).
● That value is shown alongside each employee’s row.
🔸 Example 2: Scalar Subquery in WHERE
sql
SELECT first_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
FROM employees
);
✅ Explanation:
● Filters employees whose salary is greater than the average.
● The scalar subquery returns one value (e.g., 7800).
Introduction to constraints:
🔹 Introduction to Constraints in Oracle SQL
In Oracle SQL, constraints are rules enforced on table columns to maintain data integrity, consistency, and
accuracy in the database.
They ensure that only valid data is inserted, updated, or deleted.
🔸 Why Use Constraints?
Constraints help:
● Prevent invalid or inconsistent data
● Enforce relationships between tables
● Apply business rules at the database level
🔸 Types of Constraints in Oracle SQL
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Constraint Type Description
PRIMARY KEY Ensures each row has a unique and not null identifier.
a DEFAULT constraint assigns a default value to a column
when no value is provided during an INSERT.
Default
FOREIGN KEY Enforces a relationship between two tables.
UNIQUE Ensures all values in a column (or group of columns) are
unique.
NOT NULL Ensures a column cannot have null values.
CHECK Ensures values in a column meet a specific condition.
Not null constraints:
🔹 NOT NULL Constraint in Oracle SQL
The NOT NULL constraint in Oracle SQL ensures that a column must always have a value — it cannot be
left empty (NULL). This is the most basic data integrity rule.
🔸 Purpose
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Prevents missing or incomplete data.
● Enforces that a value must be provided during an INSERT or UPDATE.
🔹 Syntax 1: Define NOT NULL When Creating a Table
sql
CREATE TABLE employees (
employee_id NUMBER NOT NULL,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50),
hire_date DATE NOT NULL
);
✅ In this example:
● employee_id, first_name, and hire_date must have values when inserting data.
🔹 Syntax 2: Add NOT NULL to an Existing Column
sql
ALTER TABLE employees
MODIFY last_name NOT NULL;
❗ You can only do this if the column already has no NULL values.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔸 Inserting Data — Example
sql
INSERT INTO employees (employee_id, first_name, hire_date)
VALUES (101, 'Alice', TO_DATE('2023-01-01', 'YYYY-MM-DD'));
✅ This will work because all NOT NULL columns are provided.
sql
INSERT INTO employees (employee_id, hire_date)
VALUES (102, TO_DATE('2023-01-01', 'YYYY-MM-DD'));
❌ This will fail because first_name is NOT NULL but was not provided.
🔸 Checking for NOT NULL Constraints
sql
SELECT column_name, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';
● nullable = 'N' → the column has a NOT NULL constraint.
🔧 Syntax to Add a NOT NULL Constraint
1. When Creating a Table
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
sql
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50) NOT NULL,
hire_date DATE NOT NULL
);
2. Adding NOT NULL to an Existing Column
You add a NOT NULL constraint using ALTER TABLE:
sql
ALTER TABLE employees
MODIFY last_name VARCHAR2(50) NOT NULL;
🔸 Note: You must ensure that existing values are not NULL before applying this change, or it
will fail.
3. Removing a NOT NULL Constraint
sql
ALTER TABLE employees
MODIFY last_name NULL;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
This allows NULL values again in the last_name column.
🔍 Checking for NOT NULL Constraints
To find NOT NULL constraints in Oracle, you can query the data dictionary:
sql
SELECT table_name, column_name
FROM all_tab_columns
WHERE nullable = 'N'
AND owner = 'YOUR_SCHEMA_NAME';
Default constraints:
In SQL, a DEFAULT constraint assigns a default value to a column when no value is provided during an
INSERT.
🔧 Syntax for DEFAULT Constraints
✅ 1. Create Table with DEFAULT
sql
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
hire_date DATE DEFAULT SYSDATE,
status VARCHAR2(10) DEFAULT 'ACTIVE'
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
);
● hire_date will default to the current date/time.
● status will default to 'ACTIVE' if not provided.
✅ 2. Add DEFAULT to an Existing Column
sql
ALTER TABLE employees
MODIFY status DEFAULT 'ACTIVE';
🔸 This only affects new inserts. Existing rows remain unchanged.
✅ 3. Remove DEFAULT Constraint
To remove a default value:
sql
ALTER TABLE employees
MODIFY status DEFAULT NULL;
This does not make the column NULL-only—it just removes the automatic default.
💡 Notes and Tips
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● DEFAULTs can be constants ('N/A', 0, 100), or built-in functions like SYSDATE, SYSTIMESTAMP,
USER, etc.
● You cannot use subqueries as default values.
● DEFAULTs are only applied when the column is omitted in the INSERT. If you explicitly insert
NULL, that NULL is stored.
🧪 Example:
sql
INSERT INTO employees (employee_id, first_name)
VALUES (101, 'Alice');
With the earlier table definition:
● hire_date becomes the current date
● status becomes 'ACTIVE'
Check constraints:
In Oracle SQL, a CHECK constraint enforces rules at the column or table level by specifying a condition that
each row must satisfy. It ensures that only valid data is stored in the database.
✅ Purpose of a CHECK Constraint
To ensure that column values meet specific criteria.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔧 Syntax Examples
1. CREATE TABLE with a CHECK Constraint
sql
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
salary NUMBER CHECK (salary >= 0),
status VARCHAR2(10) CHECK (status IN ('ACTIVE', 'INACTIVE', 'PENDING'))
);
● salary must be zero or positive.
● status must be one of the listed values.
2. Named CHECK Constraint
sql
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
age NUMBER,
CONSTRAINT chk_age CHECK (age >= 18)
);
You can give your CHECK constraint a name (chk_age), which makes it easier to reference or drop later.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
3. Add CHECK Constraint to Existing Table
sql
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 0);
4. Drop a CHECK Constraint
sql
ALTER TABLE employees
DROP CONSTRAINT chk_salary;
🚫 What You Cannot Do
CHECK constraints cannot reference other rows or tables.
sql
-- ❌ Not allowed
CHECK (salary > (SELECT AVG(salary) FROM employees))
● No subqueries or joins inside a CHECK constraint.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🧠 How Oracle Enforces It
When you insert or update data, Oracle evaluates the CHECK condition for the affected rows. If any condition
fails, it raises an error:
pgsql
ORA-02290: check constraint (SCHEMA.CHK_AGE) violated
🔍 Check Existing CHECK Constraints
You can query the data dictionary to find them:
sql
SELECT constraint_name, table_name, search_condition
FROM user_constraints
WHERE constraint_type = 'C'; -- 'C' = Check
Primary key constraints:
In Oracle SQL, a PRIMARY KEY constraint uniquely identifies each row in a table and ensures that the key
column(s) contain unique and non-null values.
✅ Key Features of PRIMARY KEY Constraints
Feature Description
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Uniqueness Values must be unique across the table
Not Null Automatically implies NOT NULL
One Per Table Only one primary key per table is allowed
Single or Composite Key Can include one or multiple columns
🔧 Syntax Examples
1. Create Table with PRIMARY KEY
sql
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
Here, employee_id is the primary key—it must be unique and not null.
2. Named PRIMARY KEY Constraint
sql
CREATE TABLE departments (
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
department_id NUMBER,
name VARCHAR2(100),
CONSTRAINT pk_department PRIMARY KEY (department_id)
);
3. Composite Primary Key
sql
CREATE TABLE project_assignments (
employee_id NUMBER,
project_id NUMBER,
assignment_date DATE,
CONSTRAINT pk_project_assignment PRIMARY KEY (employee_id, project_id)
);
Both employee_id and project_id together must be unique and not null.
4. Add PRIMARY KEY to Existing Table
sql
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
⚠️ The column must already have unique and not null values before adding the constraint.
5. Drop PRIMARY KEY
sql
ALTER TABLE employees
DROP PRIMARY KEY;
🔍 View Existing PRIMARY KEYS
sql
SELECT constraint_name, table_name, column_name
FROM user_cons_columns
WHERE constraint_name IN (
SELECT constraint_name
FROM user_constraints
WHERE constraint_type = 'P'
);
⚠️ Important Notes
● Only one PRIMARY KEY per table
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● It differs from UNIQUE in that UNIQUE allows multiple NULLs, but PRIMARY KEY does not allow
any NULLs
● You can reference a primary key from another table using a foreign key
Unique key constraints:
In Oracle SQL, a UNIQUE key constraint ensures that all values in a column or group of columns are
different (i.e., unique). Unlike a PRIMARY KEY, it allows NULLs, but not duplicate non-null values.
✅ Key Features of UNIQUE Constraints
Feature Description
Ensures uniqueness Prevents duplicate values in the column(s)
Allows NULLs Unlike PRIMARY KEY, NULLs are allowed (but not duplicate non-null values)
Multiple UNIQUEs allowed You can have multiple UNIQUE constraints per table
Can be composite Can include more than one column
🔧 Syntax Examples
1. Create Table with UNIQUE Constraint
sql
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
CREATE TABLE users (
user_id NUMBER PRIMARY KEY,
username VARCHAR2(50) UNIQUE,
email VARCHAR2(100) UNIQUE
);
Both username and email must be unique across the table. They can be null (unless NOT NULL is added).
2. Named UNIQUE Constraint
sql
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
national_id VARCHAR2(20),
CONSTRAINT uq_national_id UNIQUE (national_id)
);
This names the constraint uq_national_id.
3. Composite UNIQUE Constraint
sql
CREATE TABLE bookings (
customer_id NUMBER,
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
room_number NUMBER,
booking_date DATE,
CONSTRAINT uq_booking UNIQUE (room_number, booking_date)
);
This ensures no two bookings can have the same room on the same date.
4. Add UNIQUE Constraint to Existing Table
sql
ALTER TABLE employees
ADD CONSTRAINT uq_email UNIQUE (email);
5. Drop UNIQUE Constraint
sql
ALTER TABLE employees
DROP CONSTRAINT uq_email;
🔍 View Existing UNIQUE Constraints
sql
SELECT constraint_name, table_name, column_name
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
FROM user_cons_columns
WHERE constraint_name IN (
SELECT constraint_name
FROM user_constraints
WHERE constraint_type = 'U' -- 'U' = Unique
);
⚠️ UNIQUE vs PRIMARY KEY
Feature UNIQUE PRIMARY KEY
NULLs allowed ✅ Yes ❌ No (implicitly NOT NULL)
# per table ✅ Multiple allowed ❌ Only one
Purpose Data uniqueness Row identity & uniqueness
Foreign key constraints:
In Oracle SQL, a FOREIGN KEY constraint enforces a relationship between two tables. It ensures that the
values in a column (or group of columns) in the child table match values in a column of the parent
table—typically a PRIMARY KEY or UNIQUE constraint.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Key Features of FOREIGN KEY Constraints
Feature Description
Enforces referential integrity Prevents inserting values in the child table that don't
exist in the parent
Requires referenced column to be UNIQUE or The parent column must have unique values
PRIMARY KEY
Optional ON DELETE actions You can define behavior when a parent row is deleted
(CASCADE, SET NULL)
🔗 What Is a Foreign Key?
A foreign key is a column (or combination of columns) in one table that refers to the primary key in another
table.
It:
● Ensures referential integrity.
● Prevents orphaned records (i.e., child records that don’t have a corresponding parent).
● Controls what happens when the referenced data is updated or deleted.
🏗️ Syntax
sql
ALTER TABLE child_table
ADD CONSTRAINT fk_name
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column);
Or, when creating the table:
sql
CREATE TABLE child_table (
id NUMBER,
parent_id NUMBER,
CONSTRAINT fk_parent
FOREIGN KEY (parent_id)
REFERENCES parent_table(id)
);
🔧 Syntax Examples
1. Create Table with FOREIGN KEY
sql
CREATE TABLE departments (
department_id NUMBER PRIMARY KEY,
name VARCHAR2(100)
);
CREATE TABLE employees (
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
employee_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
department_id NUMBER,
CONSTRAINT fk_dept FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
This ensures that employees.department_id must exist in departments.department_id.
2. Add FOREIGN KEY to Existing Table
sql
ALTER TABLE employees
ADD CONSTRAINT fk_dept FOREIGN KEY (department_id)
REFERENCES departments(department_id);
3. ON DELETE Options
When defining a foreign key, you can specify what happens when a referenced row is deleted:
● ON DELETE CASCADE: Delete child rows when parent is deleted
● ON DELETE SET NULL: Set child foreign key to NULL when parent is deleted
sql
ALTER TABLE employees
ADD CONSTRAINT fk_dept FOREIGN KEY (department_id)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
REFERENCES departments(department_id)
ON DELETE SET NULL;
🔄 Example of CASCADE
sql
-- Deleting department 10 will delete all employees in department 10
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
department_id NUMBER,
CONSTRAINT fk_dept FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE CASCADE
);
✅ Rules for Foreign Keys
● The referenced column must be unique (usually a PRIMARY KEY or UNIQUE constraint).
● Data types of the columns must be compatible.
● You cannot insert a value into the child table that doesn’t exist in the parent.
● You cannot delete a row in the parent if child rows exist unless you use ON DELETE CASCADE or
ON DELETE SET NULL.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔍 View Existing FOREIGN KEYS
sql
SELECT constraint_name, table_name, r_constraint_name
FROM user_constraints
WHERE constraint_type = 'R'; -- 'R' = Referential (foreign key)
To find the referenced (parent) table:
sql
SELECT a.constraint_name, a.table_name AS child_table,
b.table_name AS parent_table, c.column_name AS fk_column
FROM user_constraints a
JOIN user_constraints b ON a.r_constraint_name = b.constraint_name
JOIN user_cons_columns c ON a.constraint_name = c.constraint_name
WHERE a.constraint_type = 'R';
🔍 How to View Foreign Keys in Oracle
You can use this query:
sql
SELECT constraint_name, table_name, r_constraint_name
FROM user_constraints
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
WHERE constraint_type = 'R';
constraint_type = 'R' indicates a referential (foreign key) constraint.
⚠️ Notes
● You cannot delete a parent row if child rows exist, unless:
○ You use ON DELETE CASCADE
○ Or delete child rows first
● FOREIGN KEY columns must have the same data type as the referenced column
Summary of constraints:
What Are Constraints?
Constraints are rules applied to table columns to enforce data integrity — they ensure the data in the database
is valid, consistent, and reliable.
🧱 Types of Constraints in Oracle SQL
Constraint Description
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
PRIMARY KEY Uniquely identifies each row in a table. Must be unique and not null. Only one primary
key per table.
FOREIGN KEY Ensures that a value in one table matches a value in another table. Maintains referential
integrity between tables.
a DEFAULT constraint assigns a default value to a column when no value is provided
during an INSERT.
Default
UNIQUE Ensures that all values in a column (or group of columns) are unique across rows.
Allows one null value.
NOT NULL Prevents a column from having NULL values. Ensures data is always entered in that
column.
CHECK Validates data against a custom condition (e.g., age > 18, salary >= 0).
🏗️ How to Define Constraints
1. Inline (inside column definition):
sql
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
name VARCHAR2(100) NOT NULL,
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
email VARCHAR2(100) UNIQUE,
salary NUMBER CHECK (salary > 0),
dept_id NUMBER
);
2. Out-of-line (after column list):
sql
CREATE TABLE departments (
dept_id NUMBER,
dept_name VARCHAR2(100),
CONSTRAINT pk_dept PRIMARY KEY (dept_id)
);
Add a foreign key separately:
sql
ALTER TABLE employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id);
🔧 General Syntax to Add Constraints
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
sql
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);
NOT NULL & DEFAULT
Unlike other constraints, NOT NULL & DEFAULT is added using MODIFY:
sql
ALTER TABLE employees
MODIFY name NOT NULL;
sql
ALTER TABLE employees
MODIFY name DEFAULT VALUE;
Summary Table
Constraint Type Command Example
PRIMARY KEY ADD CONSTRAINT pk_name PRIMARY KEY (col)
FOREIGN KEY ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(col)
UNIQUE ADD CONSTRAINT uq_name UNIQUE (col)
NOT NULL MODIFY col NOT NULL
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DEFAULT MODIFY col DEFAULT
CHECK ADD CONSTRAINT chk_name CHECK (condition)
🔥 General Syntax to Delete a Constraint
sql
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
🔄 Syntax to Rename a Constraint
sql
ALTER TABLE table_name
RENAME CONSTRAINT old_constraint_name TO new_constraint_name;
🔧 Syntax to Enable/Disable a Constraint
Enable a constraint
sql
ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;
Disable a constraint
sql
ALTER TABLE table_name
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DISABLE CONSTRAINT constraint_name;
✅ Example: Gender Column with CHECK Constraint
👇 Create Table with Gender Constraint
sql
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
gender CHAR(1),
CONSTRAINT chk_gender
CHECK (gender IN ('M', 'F', 'O')) -- Only allows M, F, or O
);
🔄 Add Gender Constraint to an Existing Table
If the table already exists:
sql
ALTER TABLE employees
ADD CONSTRAINT chk_gender
CHECK (gender IN ('M', 'F', 'O'));
🧪 Test the Constraint
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
sql
-- ✅ This works
INSERT INTO employees (emp_id, name, gender) VALUES (1, 'Alice', 'F');
-- ❌ This fails (invalid gender)
INSERT INTO employees (emp_id, name, gender) VALUES (2, 'Bob', 'X');
-- Error: ORA-02290: check constraint (CHk_GENDER) violated
✅ Summary
Constraint Type Purpose
CHECK Enforces valid gender values: 'M', 'F', 'O'
Set Operators in SQL:
In Oracle SQL, set operators are used to combine the results of two or more SELECT queries. They work
like sets in mathematics, combining rows while removing or keeping duplicates depending on the operator
used.
🔗 List of Set Operators in Oracle SQL
Operator Description
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
UNION Combines results from both queries and removes duplicates.
UNION ALL Combines results and keeps duplicates.
INTERSECT Returns only rows common to both queries.
INTERSECT All rows selected by both queries including duplicates
ALL
MINUS Returns rows in the first query but not in the second.
All rows selected by the first query but not the second including
duplicates
MINUS ALL
All distinct rows selected by the first query but not the second
EXPECT
All rows selected by the first query but not the second including
duplicates
EXPECT ALL
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🧱 Syntax
sql
SELECT column_list FROM table1
<SET OPERATOR>
SELECT column_list FROM table2;
📌 Rules:
● The number and data types of columns must match in both queries.
● The column names in the result come from the first query.
🔍 Examples
Assume we have two tables: employees_2024 and employees_2025
1. UNION (no duplicates)
sql
SELECT emp_id FROM employees_2024
UNION
SELECT emp_id FROM employees_2025;
2. UNION ALL (keeps duplicates)
sql
SELECT emp_id FROM employees_2024
UNION ALL
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
SELECT emp_id FROM employees_2025;
3. INTERSECT (common emp_ids)
sql
SELECT emp_id FROM employees_2024
INTERSECT
SELECT emp_id FROM employees_2025;
4. MINUS (emp_ids in 2024 but not in 2025)
sql
SELECT emp_id FROM employees_2024
MINUS
SELECT emp_id FROM employees_2025;
📌 When to Use Which?
Goal Use
Combine and remove duplicates UNION
Combine and keep duplicates UNION ALL
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Find common rows INTERSECT
Find rows in A but not B MINUS
Introduction to PL/SQL:
PL/SQL (Procedural Language for SQL) is Oracle Corporation's procedural extension to SQL, designed for
seamless integration with the Oracle Database. It combines the power of SQL with the procedural features of
programming languages like loops, conditions, and error handling.
🔹 What is PL/SQL?
PL/SQL stands for Procedural Language extensions to SQL. It allows developers to write code blocks that
combine SQL statements with procedural logic such as:
● Variables and constants
● Conditional statements (IF, CASE)
● Loops (FOR, WHILE)
● Exception handling (TRY/CATCH style)
● Functions, procedures, and packages
Here’s a clear comparison between SQL and PL/SQL:
Aspect SQL PL/SQL
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Full Form Structured Query Language Procedural Language/Structured Query Language
Type Declarative language Procedural programming language
Purpose Used to query and manipulate Used to write full programs (logic, loops, conditions, etc.)
data in a database
Execution Executes one statement at a Executes a block of code (multiple statements) at once
time
Control Structures Not supported Supported (IF, FOR, WHILE, CASE, etc.)
Error Handling Limited (only in tools, not in Robust error handling using EXCEPTION blocks
SQL itself)
Variables Cannot declare variables Can declare and use variables and constants
Procedures/Functions Not supported Fully supported
Used For Querying, inserting, updating, Creating applications: procedures, functions, triggers, packages
and deleting data
Example SELECT * FROM employees; A block with DECLARE, BEGIN, EXCEPTION, END
✅ Summary
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● SQL is mainly for data operations (DML/DDL).
● PL/SQL is for programming logic and creating complex applications within the database.
🔹 Features of PL/SQL
● ✅ Tight Integration with SQL
● ✅ High Performance for Batch Operations
● ✅ Strong Error Handling
● ✅ Modular Code (Procedures, Functions, Packages)
● ✅ Portability (Works across different platforms with Oracle DB)
● ✅ Security (Supports user permissions and encapsulation)
🔹 Use Cases
● Writing stored procedures and functions
● Automating tasks (like payroll processing)
● Creating triggers
● Batch processing of data
● Data validation and business logic enforcement
PL/SQL Procedures
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
A procedure in PL/SQL is a named block of code that performs a specific task. It can accept parameters,
execute SQL statements, and return results indirectly (through OUT parameters).
🔹 Syntax of a PL/SQL Procedure
sql
CREATE OR REPLACE PROCEDURE procedure_name (
parameter1 [IN | OUT | IN OUT] datatype,
parameter2 [IN | OUT | IN OUT] datatype
IS
-- Declarations
BEGIN
-- Procedure logic
EXCEPTION
-- Error handling
END procedure_name;
🔹 PL/SQL Block Structure
A PL/SQL program is made up of blocks. Each block has three sections:
sql
DECLARE -- Optional: Declare variables, constants, cursors
-- Variable declarations
BEGIN -- Mandatory: Code logic starts here
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
-- SQL and procedural statements
EXCEPTION -- Optional: Error handling
-- Actions to take when errors occur
END;
🔹 Example of a Simple PL/SQL Block
sql
DECLARE
v_employee_name VARCHAR2(50);
BEGIN
SELECT first_name INTO v_employee_name
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
END;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔹 Benefits of Using Procedures
● Reusable logic (write once, call many times)
● Modular programming
● Easier to debug and maintain
● Improved performance for batch processing
● Centralized business logic
🔹 Variables in PL/SQL:
In PL/SQL, variables are used to store data temporarily during the execution of a block. You can assign
values, manipulate them, and use them in SQL and control statements.
✅ Declaring Variables
Variables are declared in the DECLARE section of a PL/SQL block using the following syntax:
sql
variable_name datatype [ := initial_value ];
🔹 Example:
sql
DECLARE
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
v_name VARCHAR2(50); -- Without initial value
v_salary NUMBER := 50000; -- With initial value
v_hire_date DATE := SYSDATE; -- Using a function
BEGIN
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
DBMS_OUTPUT.PUT_LINE('Hire Date: ' || v_hire_date);
END;
🔹 Variable Naming Rules
● Must begin with a letter.
● Can include letters, numbers, and underscores.
● Should not match reserved keywords (like SELECT, BEGIN, etc.).
🔹 Common Data Types
Type Description Example
VARCHAR2 Variable-length character data VARCHAR2(50)
NUMBER Numeric data (integers, decimals) NUMBER, NUMBER(8,2)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DATE Date and time values DATE
BOOLEAN Logical TRUE, FALSE, or NULL BOOLEAN (only in PL/SQL)
String data types in pl/sql:
PL/SQL supports several string (character) data types to store and manipulate text. These are commonly used
for names, descriptions, codes, etc.
String datatypes:
● Fixed-length strings
● Variable-length strings
● Character large objects
Here's a detailed comparison and examples of the string-related data types in PL/SQL: VARCHAR2,
NVARCHAR2, CHAR, NCHAR, CLOB, and NCLOB.
🔹 1. VARCHAR2
● Stores variable-length character strings.
● Most commonly used.
● Max size: 32,767 bytes in PL/SQL (4000 in SQL context).
✅ Example:
sql
DECLARE
v_name VARCHAR2(50);
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
BEGIN
v_name := 'John Doe';
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
🔹 2. NVARCHAR2
● Like VARCHAR2, but stores Unicode character data (multi-language support).
● Each character may use 2 or more bytes depending on character set.
● Used for internationalization.
✅ Example:
sql
DECLARE
v_country_name NVARCHAR2(50);
BEGIN
v_country_name := N'日本'; -- Japanese characters
DBMS_OUTPUT.PUT_LINE('Country: ' || v_country_name);
END;
Note: Use N'...' to represent Unicode literals.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔹 3. CHAR
● Stores fixed-length strings.
● Pads with spaces if shorter than defined length.
✅ Example:
sql
DECLARE
v_code CHAR(5);
BEGIN
v_code := 'AB';
DBMS_OUTPUT.PUT_LINE('Code: "' || v_code || '"'); -- Output: "AB "
END;
🔹 4. NCHAR
● Like CHAR, but stores Unicode data in fixed-length format.
● Padded with Unicode spaces.
✅ Example:
sql
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DECLARE
v_lang_code NCHAR(5);
BEGIN
v_lang_code := N'हिं'; -- Hindi text
DBMS_OUTPUT.PUT_LINE('Language Code: "' || v_lang_code || '"');
END;
🔹 5. CLOB (Character Large Object)
● Stores large character data (up to 4 GB).
● Used for logs, documents, or long descriptions.
✅ Example:
sql
DECLARE
v_long_text CLOB;
BEGIN
v_long_text := 'This is a long description...';
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_long_text, 1, 100));
END;
Use SUBSTR to preview, since CLOBs can be huge.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔹 6. NCLOB (National Character LOB)
● Like CLOB, but stores Unicode text (multi-language, multi-byte).
● Ideal for multi-language documents.
✅ Example:
sql
DECLARE
v_unicode_text NCLOB;
BEGIN
v_unicode_text := N'多言語の長いテキストです'; -- Japanese text
DBMS_OUTPUT.PUT_LINE(SUBSTR(TO_CLOB(v_unicode_text), 1, 100));
END;
🔸 Summary Table
Data Type Fixed/Variable Supports Unicode Max Length (PL/SQL)
VARCHAR2 Variable ❌ 32,767 bytes
CHAR Fixed ❌ 32,767 bytes
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
NVARCHAR2 Variable ✅ 4,000 characters
NCHAR Fixed ✅ 2,000 characters
CLOB Variable (LOB) ❌ Up to 4 GB
NCLOB Variable (LOB) ✅ Up to 4 GB (Unicode)
String functions in pl/sql:
🔹 Built-in String Functions in PL/SQL
PL/SQL provides many built-in functions specifically for string handling. These functions help you
manipulate, format, and analyze text data efficiently.
✅ Commonly Used String Functions in PL/SQL
Function Description Example
LENGTH() Returns number of characters in a string LENGTH('Hello') → 5
SUBSTR() Extracts a substring SUBSTR('Oracle', 2, 3) → 'rac'
INSTR() Finds position of substring INSTR('email@[Link]', '@') → 6
UPPER() Converts string to uppercase UPPER('sql') → 'SQL'
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
LOWER() Converts string to lowercase LOWER('SQL') → 'sql'
INITCAP() Capitalizes first letter of each word INITCAP('john doe') → 'John Doe'
TRIM() Removes leading/trailing characters (default: space) TRIM(' abc ') → 'abc'
LTRIM() Removes leading characters LTRIM('---PLSQL', '-') → 'PLSQL'
RTRIM() Removes trailing characters RTRIM('data###', '#') → 'data'
REPLACE() Replaces substring with another REPLACE('car', 'a', 'u') → 'cur'
CONCAT() Concatenates two strings CONCAT('A', 'B') → 'AB'
TO_CHAR() Converts numbers/dates to string format TO_CHAR(SYSDATE, 'YYYY-MM-DD')
LPAD() Pads string from the left LPAD('9', 3, '0') → '009'
RPAD() Pads string from the right RPAD('9', 3, '0') → '900'
Examples:
● CONCAT()
Joins two strings (you can also use ||).
sql
BEGIN
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DBMS_OUTPUT.PUT_LINE(CONCAT('Data', 'Base')); -- Output: "DataBase"
END;
● UPPER()
Converts a string to uppercase.
sql
BEGIN
DBMS_OUTPUT.PUT_LINE(UPPER('pl/sql')); -- Output: "PL/SQL"
END;
● LOWER()
Converts a string to lowercase.
sql
BEGIN
DBMS_OUTPUT.PUT_LINE(LOWER('PL/SQL')); -- Output: "pl/sql"
END;
● INITCAP()
Capitalizes the first letter of each word.
sql
BEGIN
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DBMS_OUTPUT.PUT_LINE(INITCAP('oracle database')); -- Output: "Oracle Database"
END;
● SUBSTR()
Extracts a substring from a string starting at a position.
sql
BEGIN
DBMS_OUTPUT.PUT_LINE('Substring: ' || SUBSTR('Database', 5, 3)); -- Output: "bas"
END;
● INSTR()
Returns the position of a substring within a string.
sql
BEGIN
DBMS_OUTPUT.PUT_LINE('Position of "a": ' || INSTR('Database', 'a')); -- Output: 2
END;
● LPAD()
Pads the left side of a string with a specified character.
sql
BEGIN
DBMS_OUTPUT.PUT_LINE(LPAD('123', 5, '0')); -- Output: "00123"
END;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● RPAD()
Pads the right side of a string with a specified character.
sql
BEGIN
DBMS_OUTPUT.PUT_LINE(RPAD('123', 5, '0')); -- Output: "12300"
END;
● REPLACE()
Replaces occurrences of a substring.
sql
BEGIN
DBMS_OUTPUT.PUT_LINE(REPLACE('I like cats', 'cats', 'dogs')); -- Output: "I like dogs"
END;
● TRANSLATE()
Replaces characters by position (not substring-based like REPLACE).
sql
BEGIN
DBMS_OUTPUT.PUT_LINE(TRANSLATE('12345', '123', 'abc')); -- Output: "abc45"
END;
● TRIM()
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Removes leading and trailing spaces or characters.
sql
BEGIN
DBMS_OUTPUT.PUT_LINE('[' || TRIM(' PL/SQL ') || ']'); -- Output: "[PL/SQL]"
END;
● LTRIM()
Removes leading characters.
sql
BEGIN
DBMS_OUTPUT.PUT_LINE(LTRIM('***Oracle', '*')); -- Output: "Oracle"
END;
● RTRIM()
Removes trailing characters.
sql
BEGIN
DBMS_OUTPUT.PUT_LINE(RTRIM('Oracle***', '*')); -- Output: "Oracle"
END;
🔹 Number Data Types in PL/SQL
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
PL/SQL provides several numeric data types for storing and processing numbers, including integers,
decimals, and floating-point numbers. These are used in everything from simple arithmetic to complex
financial and scientific calculations.
✅ Main Number Data Types in PL/SQL
Data Type Description Example
NUMBER(p, s) General-purpose numeric type with precision and NUMBER(6,2) stores 9999.99
scale
INTEGER Whole numbers (alias for NUMBER(38, 0)) 123, -45
PLS_INTEGER Fastest integer type, used only in PL/SQL (not SQL) 100, -200
BINARY_INTEGE Obsolete synonym for PLS_INTEGER (still works) 42
R
FLOAT Floating-point numbers (approximate precision) FLOAT(126)
DECIMAL Synonym for NUMBER DECIMAL(5,2)
REAL Synonym for FLOAT REAL
SMALLINT Synonym for NUMBER(38, 0) 1000
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔹 Explanation of NUMBER(p, s)
● p: Precision – total number of digits (1 to 38)
● s: Scale – number of digits to the right of the decimal point
Definition Range of Values
NUMBER(5,2) -999.99 to 999.99
NUMBER(6,0) -999999 to 999999
NUMBER (no p/s) Max 38 digits (default)
🔹 Examples of Declarations
sql
DECLARE
v_salary NUMBER(8,2); -- Up to 6 digits before and 2 after decimal
v_age INTEGER := 30;
v_count PLS_INTEGER := 100;
v_amount FLOAT := 12345.678;
v_discount DECIMAL(5,2) := 10.25;
BEGIN
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DBMS_OUTPUT.PUT_LINE('Age: ' || v_age);
DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
DBMS_OUTPUT.PUT_LINE('Amount: ' || v_amount);
DBMS_OUTPUT.PUT_LINE('Discount: ' || v_discount);
END;
🔹 Why Use PLS_INTEGER?
● Faster performance than NUMBER or INTEGER
● Used in loops, counters, array indexes
● Stored in binary format (not decimal)
⚠️ PLS_INTEGER is limited to PL/SQL blocks — not usable directly in SQL statements or tables.
🔹 Summary
Type Used For PL/SQL Only?
NUMBER(p,s) General numeric values ❌
INTEGER Whole numbers ❌
PLS_INTEGER Loop counters, fast math ✅
FLOAT Approximate decimal values ❌
🔹 Number-Related Built-in Functions in PL/SQL
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
PL/SQL provides a wide range of numeric functions for mathematical calculations, rounding, comparisons,
and more. These work with NUMBER, INTEGER, FLOAT, PLS_INTEGER, etc.
✅ Common Number Functions with Examples
● ROUND(n [, d]) – Round to Nearest
What it does:
Rounds a number to the nearest whole number or d decimal places.
Syntax:
● ROUND(number) → rounds to 0 decimals
● ROUND(number, decimals) → rounds to decimals places
Example:
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('ROUND(3.456, 2) = ' || ROUND(3.456, 2)); -- Output: 3.46
END;
Explanation:
Used when reporting rounded values like currency or percentages.
● TRUNC(n [, d]) – Truncate Without Rounding
What it does:
Cuts off digits after the specified decimal place, without rounding.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Syntax: TRUNC(number, decimals)
Example:
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('TRUNC(3.456, 2) = ' || TRUNC(3.456, 2)); -- Output: 3.45
END;
Explanation:
Ideal for truncating sensitive financial calculations (e.g., banking).
● FLOOR(n) – Floor
What it does:
Returns the largest integer that is less than or equal to the number. It always rounds down.
Syntax: FLOOR(number)
Example:
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('FLOOR(4.9) = ' || FLOOR(4.9)); -- Output: 4
END;
Explanation:
Used when you want to estimate conservatively (e.g., maximum capacity without exceeding a limit).
● CEIL(n) – Ceiling
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
What it does:
Returns the smallest integer that is greater than or equal to the number. It always rounds up, even if the
number is already close to an integer.
Syntax: CEIL(number)
Example:
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('CEIL(4.2) = ' || CEIL(4.2)); -- Output: 5
END;
Explanation:
This is helpful in billing systems (e.g., always charge full units even for partial use).
● TO-CHAR Function (Number Conversion)
What it does:
Converts a number into a string with optional formatting (like commas, decimal points, currency symbols, etc.).
Syntax:
plsql
TO_CHAR(number, [format_model])
● number: The numeric value you want to convert.
● format_model (optional): A format string that specifies how the number should be displayed.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Example:
Basic Conversion
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('TO_CHAR(12345) = ' || TO_CHAR(12345));
END;
Output: '12345'
✅ Explanation: Basic conversion of a number to a string with no formatting.
● MOD(x, y) – Modulo / Remainder
What it does:
Returns the remainder of the division x ÷ y.
Syntax: MOD(dividend, divisor)
Example:
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('MOD(10, 3) = ' || MOD(10, 3)); -- Output: 1
END;
Explanation:
Useful in tasks like checking if a number is even/odd, cycle-based operations, or page calculations.
1. MOD(x, y) — Modulus Function
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Purpose:
Returns the remainder of division x ÷ y based on the sign of the divisor (y).
Syntax:
plsql
MOD(dividend, divisor)
2. REMAINDER(x, y) — IEEE Remainder Function
Purpose:
Returns the remainder from the division of x by y, but it's calculated using the IEEE standard:
REMAINDER=x−(y∗ROUND(x/y))REMAINDER = x - (y * ROUND(x/y))
REMAINDER=x−(y∗ROUND(x/y))
🔄 Key Difference Between MOD and REMAINDER
Feature MOD(x, y) REMAINDER(x, y)
Division logic x - y * FLOOR(x / y) x - y * ROUND(x / y)
Sign follows The divisor (y) The dividend (x)
Result range Always between 0 and y Between -y/2 and +y/2
Use case General arithmetic, cycle logic Scientific and statistical applications
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🧪 Examples
Example 1: Positive Numbers
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('MOD(10, 3) = ' || MOD(10, 3)); -- 1
DBMS_OUTPUT.PUT_LINE('REMAINDER(10, 3) = ' || REMAINDER(10, 3)); -- 1
END;
✅ Explanation:
● MOD(10, 3) = 10 - 3×FLOOR(10/3) = 10 - 3×3 = 1
● REMAINDER(10, 3) = 10 - 3×ROUND(10/3) = 10 - 3×3 = 1
→ Same result for positive numbers.
Example 2: Negative Dividend
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('MOD(-10, 3) = ' || MOD(-10, 3)); -- 2
DBMS_OUTPUT.PUT_LINE('REMAINDER(-10, 3) = ' || REMAINDER(-10, 3)); -- -1
END;
✅ Explanation:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● MOD(-10, 3) = -10 - 3×FLOOR(-10/3) = -10 - 3×(-4) = 2
● REMAINDER(-10, 3) = -10 - 3×ROUND(-10/3) = -10 - 3×(-3) = -1
🧠 Takeaway:
● MOD returns a positive result (follows divisor's sign).
● REMAINDER returns a negative result (follows dividend's sign).
Example 3: Negative Divisor
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('MOD(10, -3) = ' || MOD(10, -3)); -- -2
DBMS_OUTPUT.PUT_LINE('REMAINDER(10, -3) = ' || REMAINDER(10, -3)); -- 1
END;
✅ Explanation:
● MOD(10, -3) = 10 - (-3)×FLOOR(10 / -3) = 10 - (-3×(-4)) = 10 - 12 = -2
● REMAINDER(10, -3) = 10 - (-3)×ROUND(10 / -3) = 10 - (-3×(-3)) = 10 - 9 = 1
🧠 Notice:
● MOD result sign matches divisor
● REMAINDER result sign matches dividend
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Example 4: When Result is Zero
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('MOD(12, 4) = ' || MOD(12, 4)); -- 0
DBMS_OUTPUT.PUT_LINE('REMAINDER(12, 4) = ' || REMAINDER(12, 4)); -- 0
END;
✅ Both functions return 0 when the dividend is exactly divisible.
Example 5: Floating Point Numbers
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('MOD(10.75, 2.5) = ' || MOD(10.75, 2.5)); -- 0.25
DBMS_OUTPUT.PUT_LINE('REMAINDER(10.75, 2.5) = ' || REMAINDER(10.75, 2.5)); -- 0.25
END;
✅ Both support decimals and return correct fractional remainders.
Summary Table
Input (x, y) MOD(x, y) REMAINDER(x, y)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
(10, 3) 1 1
(-10, 3) 2 -1
(10, -3) -2 1
(-10, -3) -1 -1
(10.75, 2.5) 0.25 0.25
✅ When to Use
Scenario Use MOD Use REMAINDER
Simple arithmetic ✅ Yes ❌ Not preferred
Looping/cycles (e.g., weekdays) ✅ Yes ❌ No
Financial or general programming ✅ Yes ❌ Rarely
Scientific or statistical contexts ❌ Not preferred ✅ Yes (IEEE 754)
📌 Real-Life Use Case: Check for Even/Odd
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
plsql
DECLARE
num NUMBER := 27;
BEGIN
IF MOD(num, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE('Even Number');
ELSE
DBMS_OUTPUT.PUT_LINE('Odd Number');
END IF;
END;
✅ Explanation: MOD(n, 2) is a classic way to check even/odd values.
Date related data types in plsql:
In PL/SQL (Oracle), date and time values can be represented using several date-related data types.
There are three data types you can use to work with dates and times.
1.Date : This data type stores a date and time, resolved to the second. It does not include the time zone.
Date is the oldest and most commonly used data type for working with dates in oracle applications.
2.Timestamp : Time stamps are similar to dates but with these two key distinctions: (1) You can store
and manipulate times resolved to the nearest billionth of a second (nine decimal places of precision.),
and (2) You can associate a time zone with a time stamp, and oracle database will take that time zone
into account when manipulating the time stamp.
3.Interval : Whereas date and timestamp record a specific point in time, interval records and computes a
time duration. You can specify an interval in terms of years and months, or days and seconds.
1. DATE
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Stores: Date + Time (up to seconds)
● Includes: Century, Year, Month, Day, Hour, Minute, Second
● Default time: Midnight if time not specified
● Default format: 'DD-MON-YY' (unless overridden by NLS_DATE_FORMAT)
plsql
DECLARE
my_date DATE := TO_DATE('21-JUL-2025 15:45:00', 'DD-MON-YYYY HH24:MI:SS');
BEGIN
DBMS_OUTPUT.PUT_LINE('Date: ' || TO_CHAR(my_date, 'DD-MON-YYYY HH24:MI:SS'));
END;
2. TIMESTAMP
● Extends DATE to include fractional seconds
● Precision: up to 9 digits of fractional seconds
sql
TIMESTAMP [ (fractional_seconds_precision) ]
Example:
plsql
DECLARE
ts TIMESTAMP := SYSTIMESTAMP;
BEGIN
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DBMS_OUTPUT.PUT_LINE('Timestamp: ' || TO_CHAR(ts, 'DD-MON-YYYY HH24:MI:[Link]'));
END;
● TIMESTAMP WITH TIME ZONE
● Stores timestamp + time zone offset (e.g., +05:30)
● Useful in applications across different time zones
sql
TIMESTAMP WITH TIME ZONE
Example:
plsql
DECLARE
ts_tz TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
BEGIN
DBMS_OUTPUT.PUT_LINE('TS with TZ: ' || TO_CHAR(ts_tz, 'DD-MON-YYYY HH24:MI:[Link] TZR'));
END;
● TIMESTAMP WITH LOCAL TIME ZONE
● Stores timestamp in the database time zone
● Time zone is normalized to the session’s time zone at runtime
● Stores no time zone internally, but adjusts for session
sql
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
TIMESTAMP WITH LOCAL TIME ZONE
function timezone Datatype returned
CURRENT_DATE session DATE
CURRENT_TIMESTAMP session TIMESTAMP WITH TIMEZONE
LOCAL TIMESTAMP session TIMESTAMP
SYSDATE Database server DATE
SYSTIMESTAMP Database server TIMESTAMP WITH TIMEZONE
3. INTERVAL YEAR TO MONTH
● Represents a span of years and months
● Useful for calculations like age, project duration, etc.
sql
INTERVAL YEAR [ (precision) ] TO MONTH
Example:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
plsql
DECLARE
interval1 INTERVAL YEAR TO MONTH := INTERVAL '2-3' YEAR TO MONTH; -- 2 years, 3 months
BEGIN
DBMS_OUTPUT.PUT_LINE('Interval: ' || interval1);
END;
● INTERVAL DAY TO SECOND
● Represents a time interval in days, hours, minutes, seconds
● Supports fractional seconds
sql
INTERVAL DAY [ (day_precision) ] TO SECOND [ (fractional_seconds_precision) ]
Example:
plsql
DECLARE
interval2 INTERVAL DAY TO SECOND := INTERVAL '5 12:30:15.123' DAY TO SECOND;
BEGIN
DBMS_OUTPUT.PUT_LINE('Interval: ' || interval2);
END;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Summary Table
Data Type Stores Precision
DATE Date + Time (seconds) No fractional seconds
TIMESTAMP Date + Time + Fractional Seconds Up to 9 digits
TIMESTAMP WITH TIME ZONE Timestamp + Time Zone Precise to region/offset
TIMESTAMP WITH LOCAL TIME Timestamp normalized to session TZ Time zone at runtime
ZONE
INTERVAL YEAR TO MONTH Span of years and months Used in date arithmetic
INTERVAL DAY TO SECOND Span of days to fractional seconds Used in date arithmetic
Date related functions in plsql:
PL/SQL (and Oracle SQL) provides a rich set of date functions to handle, format, manipulate, and calculate
date and time values.
Date related functions:
● TO_CHAR
● EXTRACT
● TO_DATE
● TRUNC
Pre-defined oracle functions:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● ADD_MONTHS
● NEXT_DAY
● LAST_DAY
1. TO_CHAR()
Purpose:
Converts a DATE or TIMESTAMP to a string, formatted as specified.
Syntax:
sql
TO_CHAR(date_or_timestamp, format_model)
✅ Use Cases:
● Displaying dates in readable/custom formats
● Exporting to reports or logs
● Comparing only certain parts of a date (e.g., year or month)
Example:
plsql
DECLARE
v_today VARCHAR2(50);
BEGIN
v_today := TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS');
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DBMS_OUTPUT.PUT_LINE('Formatted Date: ' || v_today);
END;
Common Format Models:
Format Meaning
YYYY 4-digit year
MM Month (01–12)
MON Abbreviated month
DD Day of month
HH24 Hour (0–23)
MI Minute
SS Second
2. EXTRACT()
Purpose: The EXTRACT() function retrieves a specific part (like year, month, or hour) from a DATE,
TIMESTAMP, or INTERVAL value.
Syntax
sql
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
EXTRACT(<date_part> FROM <date_expression>)
● date_part: The component you want (e.g., YEAR, MONTH, DAY, HOUR, etc.)
● date_expression: A DATE, TIMESTAMP, or INTERVAL value
Supported date_part Units
Category Extractable Units Notes
Date Parts YEAR, MONTH, DAY Standard date fields
Time Parts HOUR, MINUTE, SECOND Works with TIMESTAMP
Time Zones TIMEZONE_HOUR, TIMEZONE_MINUTE Only for TIMESTAMP WITH TIME ZONE
Week/Quarter WEEK, QUARTER, DOY (Day of Year), Depends on DB version
DOW (Day of Week)
Example:
1. Extract Year, Month, Day from SYSDATE
plsql
DECLARE
v_year NUMBER;
v_month NUMBER;
v_day NUMBER;
BEGIN
v_year := EXTRACT(YEAR FROM SYSDATE);
v_month := EXTRACT(MONTH FROM SYSDATE);
v_day := EXTRACT(DAY FROM SYSDATE);
DBMS_OUTPUT.PUT_LINE('Year: ' || v_year);
DBMS_OUTPUT.PUT_LINE('Month: ' || v_month);
DBMS_OUTPUT.PUT_LINE('Day: ' || v_day);
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
END;
2. Extract Time Components from SYSTIMESTAMP
plsql
DECLARE
v_hour NUMBER;
v_minute NUMBER;
v_second NUMBER;
BEGIN
v_hour := EXTRACT(HOUR FROM SYSTIMESTAMP);
v_minute := EXTRACT(MINUTE FROM SYSTIMESTAMP);
v_second := EXTRACT(SECOND FROM SYSTIMESTAMP);
DBMS_OUTPUT.PUT_LINE('Hour: ' || v_hour);
DBMS_OUTPUT.PUT_LINE('Minute: ' || v_minute);
DBMS_OUTPUT.PUT_LINE('Second: ' || v_second);
END;
3. TO_DATE()
Purpose:
Converts a string to a DATE datatype using a format model.
Syntax:
sql
TO_DATE(string, format_model [, NLS_DATE_LANGUAGE])
Use Cases:
● Inserting string-based dates into DATE columns.
● Validating and parsing date input from users or files.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Example:
sql
DECLARE
v_date DATE;
BEGIN
v_date := TO_DATE('21-JUL-2025', 'DD-MON-YYYY');
DBMS_OUTPUT.PUT_LINE('Converted Date: ' || v_date);
END;
⚠️ Pitfall:
If the string and format don’t match, Oracle raises ORA-01843: not a valid month or similar errors.
Wrong:
sql
TO_DATE('2025/07/21', 'DD-MON-YYYY') -- Incorrect format
Right:
sql
TO_DATE('2025/07/21', 'YYYY/MM/DD')
4. TRUNC()
The TRUNC() function in PL/SQL truncates a DATE or TIMESTAMP to a specified unit of time—like day,
month, year, or hour—removing smaller time components.
It is commonly used to:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Remove the time portion from a date
● Normalize dates for comparison or grouping
● Truncate to a larger unit (like month, quarter, year)
Syntax
sql
TRUNC(date_value [, format])
● date_value: A DATE or TIMESTAMP
● format (optional): A string indicating how to truncate (e.g. 'MM', 'YYYY', etc.)
If no format is provided, it defaults to 'DD' (truncates the time part, keeps the date).
Example: Truncate to Month, Year, Quarter
plsql
DECLARE
v_date DATE := TO_DATE('21-JUL-2025 14:35:10', 'DD-MON-YYYY HH24:MI:SS');
BEGIN
DBMS_OUTPUT.PUT_LINE('Month Start: ' || TO_CHAR(TRUNC(v_date, 'MM'), 'DD-MON-YYYY'));
DBMS_OUTPUT.PUT_LINE('Year Start: ' || TO_CHAR(TRUNC(v_date, 'YYYY'), 'DD-MON-YYYY'));
DBMS_OUTPUT.PUT_LINE('Quarter Start: ' || TO_CHAR(TRUNC(v_date, 'Q'), 'DD-MON-YYYY'));
END;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Output:
sql
Month Start: 01-JUL-2025
Year Start: 01-JAN-2025
Quarter Start: 01-JUL-2025
Truncating SYSTIMESTAMP
If using SYSTIMESTAMP, convert it to DATE first or use CAST:
sql
SELECT TRUNC(CAST(SYSTIMESTAMP AS DATE)) AS today_start
FROM dual;
Common Format Options
Format Truncates to... Example Result (TRUNC('21-JUL-2025 14:45:00',
format))
'DD' Day (default) 21-JUL-2025 00:00:00
'D' Start of week (NLS dependent) 20-JUL-2025 (if NLS_TERRITORY = 'AMERICA')
'DAY' Start of week (long version) Same as 'D'
'MM' First day of the month 01-JUL-2025
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
'YYYY' First day of the year 01-JAN-2025
'Q' First day of the quarter 01-JUL-2025 (for Q3)
'HH' Truncates to the hour 21-JUL-2025 14:00:00
'MI' Truncates to the minute 21-JUL-2025 14:45:00
● ADD_MONTHS()
✅ Purpose:
Adds or subtracts a specified number of calendar months to/from a date.
✅ Syntax:
sql
ADD_MONTHS(date_value, number_of_months)
● number_of_months can be positive (future) or negative (past).
✅ Example:
sql
SELECT ADD_MONTHS(DATE '2025-07-21', 3) AS future_date FROM dual;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
-- Result: 21-OCT-2025
✅ Edge Case:
sql
SELECT ADD_MONTHS(DATE '31-JAN-2025', 1) FROM dual;
-- Result: 28-FEB-2025 (or 29-FEB in leap years)
● NEXT_DAY()
✅ Purpose:
Returns the next occurrence of the specified weekday after a given date.
✅ Syntax:
sql
NEXT_DAY(date_value, 'weekday')
● 'weekday' is a string: 'MONDAY', 'TUESDAY', etc.
● Not case-sensitive, NLS-sensitive if abbreviated ('MON', 'TUE', etc.)
✅ Example:
sql
SELECT NEXT_DAY(DATE '2025-07-21', 'FRIDAY') AS next_friday FROM dual;
-- Result: 25-JUL-2025 (since 21-JUL is Monday)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Notes:
● If the date is already the same weekday, NEXT_DAY returns the next one (not the same date).
● LAST_DAY()
✅ Purpose:
Returns the last day of the month for a given date.
✅ Syntax:
sql
LAST_DAY(date_value)
✅ Example:
sql
SELECT LAST_DAY(DATE '2025-02-15') AS month_end FROM dual;
-- Result: 28-FEB-2025 (or 29-FEB in leap years)
Practical PL/SQL Example Using All Three
plsql
DECLARE
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
v_today DATE := SYSDATE;
v_expiry DATE;
v_payday DATE;
v_month_end DATE;
BEGIN
v_expiry := ADD_MONTHS(v_today, 6); -- add 6 months
v_payday := NEXT_DAY(v_today, 'FRIDAY'); -- next Friday
v_month_end := LAST_DAY(v_today); -- last day of this month
DBMS_OUTPUT.PUT_LINE('Today : ' || TO_CHAR(v_today, 'DD-MON-YYYY'));
DBMS_OUTPUT.PUT_LINE('Expiry Date : ' || TO_CHAR(v_expiry, 'DD-MON-YYYY'));
DBMS_OUTPUT.PUT_LINE('Next Payday : ' || TO_CHAR(v_payday, 'DD-MON-YYYY'));
DBMS_OUTPUT.PUT_LINE('Month End Date: ' || TO_CHAR(v_month_end, 'DD-MON-YYYY'));
END;
🔶 Summary Table
Function Purpose Example Output
ADD_MONTH Add/subtract calendar ADD_MONTHS(SYSDATE, 1) → next month
S months
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
NEXT_DAY Find next specified NEXT_DAY(SYSDATE, 'MONDAY')
weekday
LAST_DAY Get last day of current LAST_DAY(SYSDATE) → 31-JUL-2025
month
How to read input from the user:
● The & symbol is used in SQL*Plus, SQL Developer, and similar Oracle tools to prompt the user for
input at runtime.
● It tells Oracle to replace the &variable with a value you type when running the script or query.
How does it work?
When Oracle encounters &variable_name, it:
1. Pauses execution.
2. Prompts you to enter a value for variable_name.
3. Substitutes that value into the SQL or PL/SQL code.
4. Executes the code with your input.
Example:
DECLARE
Num1 NUMBER(2);
Num2 NUMBER(2);
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Res NUMBER(3);
BEGIN
num1:=&NUMBER1;
num2:=&NUMBER2;
res:=num1+num2;
DBMS_OUTPUT.PUT_LINE(‘Result is :’||res;
END;
/
OUT PUT:
Enter value for number1: 78
Enter value for number2: 96
Result is 174
Example: Using & to Read Input in an INSERT Statement
sql
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (&emp_id, '&first_name', '&last_name');
● When this runs, Oracle will prompt:
rust
Enter value for emp_id:
Enter value for first_name:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Enter value for last_name:
● You enter values like 101, John, Doe.
● Oracle substitutes these values and runs:
sql
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (101, 'John', 'Doe');
Example: Using & in PL/SQL Block
plsql
DECLARE
v_name VARCHAR2(50);
BEGIN
v_name := '&username';
DBMS_OUTPUT.PUT_LINE('Hello, ' || v_name || '!');
END;
● You will see:
rust
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Enter value for username:
● You type a name, say Alice.
● The output will be:
Hello, Alice!
Notes:
● Use single quotes ' ' around &variable in string contexts to make sure the input is treated as a string.
● You can use &&variable to reuse the same input multiple times without prompting again.
● This works only in tools like SQL*Plus, SQLcl, Oracle SQL Developer, not inside application code or
pure PL/SQL blocks run programmatically.
%type and %ROWtype datatype:
In PL/SQL, %TYPE and %ROWTYPE are attribute datatypes used to declare variables dynamically based
on the structure of existing database objects like columns or entire rows.
They inherit the datatype and size automatically, making your code:
● More reliable
● Easier to maintain
● Less prone to errors when the table structure changes
🔷 %TYPE – Column-Level Data Type
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ What It Does:
● Declares a variable with the same data type as a table column or another variable.
● Useful when you only want to store a single column's value.
✅ Syntax:
plsql
variable_name table_name.column_name%TYPE;
✅ Example:
plsql
DECLARE
v_salary [Link]%TYPE;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
🔍 What Happens:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● v_salary will always match the datatype of [Link].
● If the column is changed in the future (e.g., from NUMBER(8,2) to NUMBER(10,2)), no code changes
are needed.
🔷 %ROWTYPE – Row-Level Data Type
✅ What It Does:
● Declares a record variable that has one field for each column in a table or cursor.
● Useful when working with an entire row of data.
✅ Syntax:
plsql
record_variable table_name%ROWTYPE;
✅ Example:
plsql
DECLARE
v_emp employees%ROWTYPE;
BEGIN
SELECT * INTO v_emp
FROM employees
WHERE employee_id = 101;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DBMS_OUTPUT.PUT_LINE('Name: ' || v_emp.first_name || ' ' || v_emp.last_name);
DBMS_OUTPUT.PUT_LINE('Department: ' || v_emp.department_id);
END;
🔍 What Happens:
● v_emp has fields like v_emp.employee_id, v_emp.salary, v_emp.job_id, etc.
● It reflects the entire row structure of the employees table.
🔄 Comparison: %TYPE vs %ROWTYPE
Feature %TYPE %ROWTYPE
Refers to A single column or variable An entire row of a table
Used for Single values (e.g., salary) Full row records (e.g., employee)
Code Size Small Larger
Syntax var [Link]%TYPE record table%ROWTYPE
Example v_name employees.first_name%TYPE v_emp employees%ROWTYPE
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Benefits
● ✔ Reduces hardcoding of data types
● ✔ Prevents type mismatch errors
● ✔ Automatically reflects schema changes
● ✔ Makes code cleaner and more maintainable
Example: Using Both
plsql
DECLARE
v_emp employees%ROWTYPE;
v_deptid departments.department_id%TYPE;
BEGIN
SELECT * INTO v_emp FROM employees WHERE employee_id = 101;
v_deptid := v_emp.department_id;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp.first_name || ', Dept ID: ' || v_deptid);
END;
✅ Control Statements in PL/SQL
Control statements are PL/SQL statements that control the execution path of your code. They let your
program:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Make decisions
● Repeat actions
● Skip or branch code
Without control statements, your PL/SQL code would run line-by-line in the order it appears — no conditions,
no loops, no decision-making.
🔶 Types of Control Statements in PL/SQL
PL/SQL has three main categories of control statements:
Type Description
Conditional Decision making (IF, CASE)
(selection)
Iterative (Loops) Repeating tasks (LOOP, WHILE, FOR)
Conditional Control Statements
Used to execute specific blocks of code based on conditions.
If statements in plsql:
In PL/SQL, the IF statement is used to make decisions in your program. It allows you to execute certain code only when a
condition is true.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔹 Purpose:
To control the flow of execution based on logical conditions.
🔹 IF Statements
Syntax:
pl
IF condition THEN
-- statements
END IF;
Variants:
● IF ... THEN ... END IF;
● IF ... THEN ... ELSE ... END IF;
● IF ... THEN ... ELSIF ... ELSE ... END IF;
Example:
plsql
IF salary > 5000 THEN
DBMS_OUTPUT.PUT_LINE('High salary');
ELSIF salary > 3000 THEN
DBMS_OUTPUT.PUT_LINE('Medium salary');
ELSE
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DBMS_OUTPUT.PUT_LINE('Low salary');
END IF;
1. IF...THEN
Executes code only if the condition is true.
plsql
IF condition THEN
-- statements
END IF;
🔸 Example:
plsql
IF salary > 5000 THEN
DBMS_OUTPUT.PUT_LINE('High salary');
END IF;
2. IF...THEN...ELSE
Adds an alternative if the condition is false.
plsql
IF condition THEN
-- true block
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
ELSE
-- false block
END IF;
🔸 Example:
plsql
IF salary > 5000 THEN
DBMS_OUTPUT.PUT_LINE('High salary');
ELSE
DBMS_OUTPUT.PUT_LINE('Normal salary');
END IF;
3. IF...THEN...ELSIF...ELSE
Checks multiple conditions in sequence.
plsql
IF condition1 THEN
-- block1
ELSIF condition2 THEN
-- block2
ELSE
-- block3
END IF;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔸 Example:
plsql
IF marks >= 90 THEN
DBMS_OUTPUT.PUT_LINE('Grade: A');
ELSIF marks >= 75 THEN
DBMS_OUTPUT.PUT_LINE('Grade: B');
ELSE
DBMS_OUTPUT.PUT_LINE('Grade: C');
END IF;
[Link] IF Statement
A nested IF means placing one IF statement inside another.
✅ Syntax:
pl
IF outer_condition THEN
IF inner_condition THEN
-- Code if both outer and inner conditions are true
ELSE
-- Code if outer true but inner false
END IF;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
ELSE
-- Code if outer condition is false
END IF;
✅ Example:
pl
DECLARE
salary NUMBER := 6000;
dept_id NUMBER := 10;
BEGIN
IF salary > 5000 THEN
IF dept_id = 10 THEN
DBMS_OUTPUT.PUT_LINE('High salary in HR department');
ELSE
DBMS_OUTPUT.PUT_LINE('High salary in other department');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Normal salary');
END IF;
END;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Summary
Statement Form Description
IF...THEN Executes block if condition is true
IF...ELSE Adds alternative block if false
IF...ELSIF...ELSE Multiple conditions in sequence
Nested IF IF inside another IF for complex logic
Case statements in plsql:
In PL/SQL, the CASE statement is used to evaluate conditions or expressions and execute one block of code
based on the result — similar to switch-case in other programming languages like C or Java.
🔷 Why Use CASE?
● Simplifies complex IF...ELSIF...ELSE chains
● Improves readability
● Makes code cleaner when checking multiple values
🔶 Types of CASE in PL/SQL
PL/SQL supports two types of CASE statements:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Type When to Use
Simple CASE When comparing one expression to multiple values
Searched CASE When using different logical conditions
1. Simple CASE Statement
✅ Syntax:
plsql
CASE expression
WHEN value1 THEN
-- code block
WHEN value2 THEN
-- code block
ELSE
-- default block
END CASE;
✅ Example:
plsql
DECLARE
job_code VARCHAR2(10) := 'HR_REP';
BEGIN
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
CASE job_code
WHEN 'IT_PROG' THEN
DBMS_OUTPUT.PUT_LINE('Programmer');
WHEN 'HR_REP' THEN
DBMS_OUTPUT.PUT_LINE('HR Representative');
WHEN 'SA_REP' THEN
DBMS_OUTPUT.PUT_LINE('Sales Representative');
ELSE
DBMS_OUTPUT.PUT_LINE('Other Role');
END CASE;
END;
2. Searched CASE Statement
Used when each WHEN clause contains a full condition (not just a value).
✅ Syntax:
plsql
CASE
WHEN condition1 THEN
-- code block
WHEN condition2 THEN
-- code block
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
ELSE
-- default block
END CASE;
✅ Example:
plsql
DECLARE
marks NUMBER := 82;
BEGIN
CASE
WHEN marks >= 90 THEN
DBMS_OUTPUT.PUT_LINE('Grade: A');
WHEN marks >= 75 THEN
DBMS_OUTPUT.PUT_LINE('Grade: B');
WHEN marks >= 60 THEN
DBMS_OUTPUT.PUT_LINE('Grade: C');
ELSE
DBMS_OUTPUT.PUT_LINE('Grade: F');
END CASE;
END;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Notes
● You must end the CASE with END CASE;
● CASE statements must be complete — include ELSE to handle all cases (optional, but recommended)
● CASE is faster and cleaner than multiple IF...ELSIF
✅ Use Case Summary
Use this... When...
Simple CASE You are comparing one variable to many values
Searched CASE You are using logical conditions
FOR Loop in PL/SQL:
The FOR loop in PL/SQL is a control structure used to repeat a block of code a specific number of times. It
automatically initializes, increments, and terminates the loop control variable.
PL/SQL supports two types of FOR loops:
Type Description
Numeric FOR Repeats a block for a fixed number of times
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Cursor FOR Loops through each row returned by a SQL query
1. Numeric FOR Loop (Also called Index or Counted FOR loop)
Purpose:
Used when you know how many times to iterate (e.g., 1 to 10).
Syntax:
plsql
FOR i IN [REVERSE] lower_bound .. upper_bound LOOP
-- Code block
END LOOP;
● i is the loop counter (auto-declared, read-only).
● Use REVERSE to count downward.
Example 1: Count from 1 to 5
plsql
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('i = ' || i);
END LOOP;
END;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
📝 Output:
ini
i=1
i=2
i=3
i=4
i=5
Example 2: Reverse count
plsql
BEGIN
FOR i IN REVERSE 5..1 LOOP
DBMS_OUTPUT.PUT_LINE('i = ' || i);
END LOOP;
END;
📝 Output:
Ini
i=5
i=4
i=3
i=2
i=1
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Features of Numeric FOR Loop
Feature Details
Loop counter Declared implicitly, local to loop
Read-only counter Cannot be changed manually
Automatic control PL/SQL handles start, stop, increment
Step Always increments/decrements by 1
2. Cursor FOR Loop
Purpose:
Used to fetch and process rows returned by a SELECT query automatically.
PL/SQL opens the cursor, fetches each row, and closes the cursor — all automatically.
Syntax 1: With Explicit Cursor
plsql
DECLARE
CURSOR emp_cur IS
SELECT empno, ename FROM emp;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
BEGIN
FOR rec IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE('Emp No: ' || [Link] || ', Name: ' || [Link]);
END LOOP;
END;
Syntax 2: With Implicit Cursor (SELECT directly in loop)
plsql
BEGIN
FOR rec IN (SELECT empno, ename FROM emp WHERE deptno = 10) LOOP
DBMS_OUTPUT.PUT_LINE('Emp No: ' || [Link] || ', Name: ' || [Link]);
END LOOP;
END;
In both cases, rec is a record variable that holds each row returned by the query.
Features of Cursor FOR Loop
Feature Details
Automatic open/close No need to manually open/fetch/close the cursor
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Record variable Each row is fetched into a record (rec)
Cleaner code No loop condition, exit logic, or fetch needed
Supports SELECT Works with both static and inline queries
🔶 Numeric vs Cursor FOR Loop — Comparison
Feature Numeric FOR Loop Cursor FOR Loop
Use Case Counted iterations Processing query results
Counter Type Integer counter Record from query result
Needs Cursor? ❌ No ✅ Yes
Auto Open/Fetch/Close ❌ No (Not applicable) ✅ Yes
Used For Repetitive tasks Row-by-row data operations
Real-Life Example: Cursor FOR Loop on EMP Table
plsql
DECLARE
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
CURSOR emp_cur IS
SELECT empno, sal FROM emp WHERE deptno = 20;
BEGIN
FOR emp_rec IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE('Emp No: ' || emp_rec.empno || ', Salary: ' || emp_rec.sal);
END LOOP;
END;
🔚 Summary
Loop Type Description Best Used For
Numeric FOR Loops between two numbers Counters, sums, fixed iterations
Cursor FOR Loops through result of a SQL query Row-by-row processing from tables
While and simple loops in plsql:
Simple loop:
A simple loop in PL/SQL is a basic loop structure that repeatedly executes a block of code until it is explicitly
exited using the EXIT statement. It does not have a built-in condition to stop automatically, so you must provide
one inside the loop.
🔁 Syntax of a Simple Loop:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
plsql
LOOP
-- Statements to execute
EXIT WHEN condition;
END LOOP;
✅ Example:
plsql
DECLARE
i NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Value of i: ' || i);
i := i + 1;
EXIT WHEN i > 5;
END LOOP;
END;
🔍 Explanation:
● i := 1; initializes the counter.
● The loop starts with LOOP.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Inside the loop:
○ It prints the current value of i.
○ Increments i by 1.
○ EXIT WHEN i > 5; — once i becomes 6, the loop exits.
● The loop runs 5 times (from 1 to 5).
📝 Key Points:
● Always use an EXIT or EXIT WHEN inside a simple loop; otherwise, it becomes an infinite loop.
● Good for when you don’t know exactly how many times you need to loop ahead of time.
While loop:
A WHILE loop in PL/SQL repeatedly executes a block of code as long as a condition is TRUE. Unlike a
simple loop, the condition is checked before each iteration.
🔁 Syntax of a WHILE Loop:
plsql
WHILE condition LOOP
-- Statements to execute
END LOOP;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Example:
plsql
DECLARE
i NUMBER := 1;
BEGIN
WHILE i <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('Value of i: ' || i);
i := i + 1;
END LOOP;
END;
🔍 Explanation:
● i := 1; initializes the counter.
● The WHILE i <= 5 condition is checked before each iteration.
● If the condition is TRUE, the loop executes:
○ Prints the current value of i.
○ Increments i.
● When i becomes 6, the condition i <= 5 becomes FALSE, and the loop exits.
📝 Key Points:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● A WHILE loop might not run at all if the condition is false at the start.
● Use it when you want to continue looping based on a condition.
● Always make sure the condition will eventually become false — or you’ll get an infinite loop.
🔄 Simple Loop vs While Loop
Feature Simple Loop While Loop
Condition check Inside the loop using EXIT WHEN At the start of the loop
Must run at least once? ✅ Yes ❌ No (depends on condition)
Risk of infinite loop ✅ Yes (if no EXIT WHEN is used) ✅ Yes (if condition never becomes false)
Use case When exit depends on logic inside When you want to loop while a condition is true
loop
📌 Introduction to Cursors in PL/SQL
In PL/SQL, a cursor is a pointer to a context area — a memory region where Oracle processes SQL
statements. Cursors let you fetch query results row-by-row, especially when dealing with SELECT
statements that return multiple rows.
🔄 Why Use a Cursor?
By default, PL/SQL can only handle one row at a time from a SELECT statement. A cursor allows you to:
● Process multiple rows from a query
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Control how and when data is fetched
● Perform logic on each row returned by a query
✅ Implementing a Cursor in PL/SQL – Step-by-Step Explanation
When you need to process multiple rows returned by a SELECT statement in PL/SQL, you implement a
cursor. Here's a clear explanation of how to do it.
🧱 Steps to Implement a Cursor
1. Declare the cursor
Define the SQL query that will return multiple rows.
2. Open the cursor
This executes the query and prepares the result set.
3. Fetch rows from the cursor
One row at a time is retrieved into variables.
4. Loop through the rows
Use a loop to process each row.
5. Close the cursor
Releases memory used by the cursor.
🧑💻 Example: Fetching Employee Details
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
plsql
DECLARE
-- Step 1: Declare the cursor
CURSOR emp_cursor IS
SELECT empno, ename FROM emp;
-- Step 2: Declare variables to hold data
v_empno [Link]%TYPE;
v_ename [Link]%TYPE;
BEGIN
-- Step 3: Open the cursor
OPEN emp_cursor;
-- Step 4: Loop to fetch and process rows
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
-- Process each row
DBMS_OUTPUT.PUT_LINE('Emp No: ' || v_empno || ', Name: ' || v_ename);
END LOOP;
-- Step 5: Close the cursor
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
CLOSE emp_cursor;
END;
🔍 Explanation of Each Part
Step Description
CURSOR emp_cursor IS... Declares a cursor named emp_cursor for a SELECT query
OPEN emp_cursor; Executes the query and makes the result set available
FETCH emp_cursor INTO... Retrieves one row at a time into declared variables
EXIT WHEN Exits the loop when no more rows are left to fetch
emp_cursor%NOTFOUND;
CLOSE emp_cursor; Closes the cursor and frees system resources
✅ Benefits of Using Cursors
● Handles multi-row queries in a structured way
● Gives full control over how each row is processed
● Works well with complex logic and conditional processing per row
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Cursor Attributes
Attribute Description
%FOUND Returns TRUE if a fetch found a row
%NOTFOUND Returns TRUE if no row was found
%ROWCOUNT Number of rows processed so far
%ISOPEN Checks if the cursor is open
%FOUND:
Check if a FETCH from a cursor was successful using %FOUND.
Simple Example Using %FOUND
plsql
DECLARE
-- Declare a cursor to fetch employees from department 10
CURSOR emp_cursor IS
SELECT empno, ename FROM emp WHERE deptno = 10;
-- Variables to hold the fetched data
v_empno [Link]%TYPE;
v_ename [Link]%TYPE;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
BEGIN
-- Open the cursor
OPEN emp_cursor;
-- Fetch the first row
FETCH emp_cursor INTO v_empno, v_ename;
-- Check if a row was found
IF emp_cursor%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Record Found:');
DBMS_OUTPUT.PUT_LINE('Emp No: ' || v_empno || ', Name: ' || v_ename);
ELSE
DBMS_OUTPUT.PUT_LINE('No employee found in department 10.');
END IF;
-- Close the cursor
CLOSE emp_cursor;
END;
🧾 Output (example):
If data exists:
yaml
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Record Found:
Emp No: 7369, Name: SMITH
If no data:
pgsql
No employee found in department 10.
🔍 Key Points:
● %FOUND returns TRUE if the last FETCH returned a row.
● It's used immediately after the FETCH statement.
● Useful to decide whether to enter a loop or skip processing
%NOTFOUND:
● %NOTFOUND returns TRUE if the last FETCH did not return any row.
● Used with explicit cursors to know when to exit a loop or stop fetching data.
✅ Simple Example Using %NOTFOUND
plsql
DECLARE
-- Step 1: Declare a cursor to get employee data
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
CURSOR emp_cursor IS
SELECT empno, ename FROM emp WHERE deptno = 10;
-- Step 2: Variables to hold fetched data
v_empno [Link]%TYPE;
v_ename [Link]%TYPE;
BEGIN
-- Step 3: Open the cursor
OPEN emp_cursor;
-- Step 4: Loop through the result set
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
-- Step 5: Exit the loop if no row is returned
EXIT WHEN emp_cursor%NOTFOUND;
-- Step 6: Process the row
DBMS_OUTPUT.PUT_LINE('Emp No: ' || v_empno || ', Name: ' || v_ename);
END LOOP;
-- Step 7: Close the cursor
CLOSE emp_cursor;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
END;
💡 Explanation:
● The loop keeps fetching rows.
● EXIT WHEN emp_cursor%NOTFOUND; stops the loop when there are no more rows.
● Safe and common way to iterate through cursor results.
📌 Output (Example):
If 3 employees are in dept 10:
yaml
Emp No: 7369, Name: SMITH
Emp No: 7499, Name: ALLEN
Emp No: 7521, Name: WARD
If no data is in dept 10, the loop is skipped — no output.
%ROWCOUNT:
● %ROWCOUNT returns the number of rows fetched so far (for explicit cursors) or affected (for
implicit cursors).
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● It's useful for keeping track of progress during data processing.
✅ Example: Using %ROWCOUNT with an Explicit Cursor
plsql
DECLARE
-- Step 1: Declare a cursor to fetch employee records
CURSOR emp_cursor IS
SELECT empno, ename FROM emp WHERE deptno = 10;
-- Step 2: Declare variables to store fetched data
v_empno [Link]%TYPE;
v_ename [Link]%TYPE;
BEGIN
-- Step 3: Open the cursor
OPEN emp_cursor;
-- Step 4: Loop to fetch and process each row
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
-- Step 5: Display fetched data and row count
DBMS_OUTPUT.PUT_LINE('Emp No: ' || v_empno || ', Name: ' || v_ename);
DBMS_OUTPUT.PUT_LINE('Rows Fetched So Far: ' || emp_cursor%ROWCOUNT);
END LOOP;
-- Step 6: Close the cursor
CLOSE emp_cursor;
END;
🔍 Explanation:
● emp_cursor%ROWCOUNT tells you how many rows have been fetched up to that point.
● Increases by 1 with each successful FETCH.
✅ Example: Using SQL%ROWCOUNT with an Implicit Cursor
plsql
BEGIN
DELETE FROM emp WHERE deptno = 99;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' row(s) deleted.');
END;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● SQL%ROWCOUNT gives the number of rows affected by the last DML operation (e.g., INSERT,
UPDATE, DELETE).
📝 Summary Table
Attribute Works With What It Does
%ROWCOUNT Explicit Cursor Returns number of rows fetched so far
SQL%ROWCOUNT Implicit Cursor Returns number of rows affected
%ISOPEN:
● %ISOPEN checks whether a cursor is currently open.
● Returns:
○ TRUE if the cursor is open
○ FALSE if the cursor is closed
🟡 Useful to prevent errors by avoiding attempts to fetch from or close an unopened or already closed cursor.
✅ Basic Example: Using %ISOPEN with an Explicit Cursor
plsql
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DECLARE
-- Step 1: Declare a cursor
CURSOR emp_cursor IS
SELECT empno, ename FROM emp WHERE deptno = 10;
-- Variables to hold data
v_empno [Link]%TYPE;
v_ename [Link]%TYPE;
BEGIN
-- Check if cursor is open before opening (should be FALSE)
IF emp_cursor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is already open.');
ELSE
DBMS_OUTPUT.PUT_LINE('Cursor is not open. Opening now...');
END IF;
-- Step 2: Open the cursor
OPEN emp_cursor;
-- Check again
IF emp_cursor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is now open.');
END IF;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
-- Fetch first row (optional)
FETCH emp_cursor INTO v_empno, v_ename;
-- Display one row
DBMS_OUTPUT.PUT_LINE('Emp No: ' || v_empno || ', Name: ' || v_ename);
-- Step 3: Close the cursor
CLOSE emp_cursor;
-- Final check
IF NOT emp_cursor%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is now closed.');
END IF;
END;
📝 Output (Example):
kotlin
Cursor is not open. Opening now...
Cursor is now open.
Emp No: 7369, Name: SMITH
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Cursor is now closed.
🧠 Summary
Attribute Purpose Returns
%ISOPEN Checks if the cursor is open TRUE/FALSE
🔁 Ref Cursors in PL/SQL
A Ref Cursor (short for "reference cursor") is a pointer to a query result set in PL/SQL. Unlike regular
(static) cursors, ref cursors are dynamic — the query they point to can be determined at runtime.
Why Use Ref Cursors?
● When you want to return a result set from a procedure or function.
● To write generic, reusable code.
● To process dynamic queries.
● Especially useful when calling PL/SQL code from applications (like Java, Python, or .NET) that
expect a result set.
✅ Syntax
1. Define the ref cursor type
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
plsql
TYPE ref_cursor_type IS REF CURSOR;
2. Open the ref cursor for a query
plsql
OPEN your_cursor FOR SELECT ...;
SYS_REFCURSOR:
● It’s an Oracle-provided predefined REF CURSOR type.
● Used when you want to return or work with query results dynamically.
● Works like a pointer to rows from a query.
Example Using SYS_REFCURSOR
pl
DECLARE
-- Declare a cursor variable of type SYS_REFCURSOR
emp_cur SYS_REFCURSOR;
-- Variables to hold data
v_empno [Link]%TYPE;
v_ename [Link]%TYPE;
BEGIN
-- Open the cursor for a SELECT query
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
OPEN emp_cur FOR
SELECT empno, ename FROM emp WHERE deptno = 10;
-- Fetch rows one by one
LOOP
FETCH emp_cur INTO v_empno, v_ename;
EXIT WHEN emp_cur%NOTFOUND;
-- Print the data
DBMS_OUTPUT.PUT_LINE('Emp No: ' || v_empno || ', Name: ' || v_ename);
END LOOP;
-- Close the cursor
CLOSE emp_cur;
END;
🔍 How It Works
● SYS_REFCURSOR is used instead of declaring your own REF CURSOR type.
● The cursor is opened with a query.
● You fetch and process each row one by one.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Then close the cursor.
✅ Benefits of SYS_REFCURSOR
Feature Advantage
Predefined No need to define custom cursor types
Flexible Can be used with any SELECT query
Reusable Ideal for returning data from procedures or functions
📌 Use Case
You’ll commonly use SYS_REFCURSOR when:
● You want to return query results from a stored procedure.
● You’re building dynamic or reusable code.
● You need to return data to external applications.
Run-Time Error Handling in PL/SQL
In PL/SQL, run-time errors occur while the code is executing, not during compilation. PL/SQL lets you
handle these errors gracefully using the EXCEPTION block, so your program doesn't crash unexpectedly.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔴 Types of Errors in PL/SQL
1. Compilation Errors
● When: During the compilation of PL/SQL code (before running).
● Why: Syntax mistakes, missing keywords, wrong declarations, etc.
● Example: Misspelling BEGIN as BEGINS, missing semicolon.
Note: These errors prevent your PL/SQL block from compiling.
2. Run-Time Errors (Exceptions)
● When: While the code is executing.
● Why: Logical errors, invalid operations, data errors.
● Handled by: Exception handling (EXCEPTION block).
Common run-time errors:
● NO_DATA_FOUND — No rows returned by a query.
● TOO_MANY_ROWS — Query returns more than one row for SELECT INTO.
● ZERO_DIVIDE — Division by zero.
● VALUE_ERROR — Data type conversion errors.
● ACCESS_INTO_NULL — Dereferencing a NULL pointer.
● CURSOR_ALREADY_OPEN — Trying to open an already open cursor.
3. Logical Errors
● When: Code runs without syntax errors, but logic is wrong.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Example: Calculating wrong formula, incorrect loop condition.
● Effect: Wrong output or infinite loops.
● Note: These errors don’t raise exceptions automatically — you have to debug them.
📋 Summary Table
Error Type When Detected Cause Handling
Compilation During compilation Syntax or declaration Fix syntax before running
Error errors
Run-Time Error During execution Invalid operations, data Exception handling
issues (EXCEPTION block)
Logical Error During/after Flawed program logic Code review and debugging
execution
Exception Handling
● Exceptions are errors or unexpected events that occur during the execution of a PL/SQL program
(run-time errors).
● Exception handling lets you catch these errors and respond to them gracefully instead of letting the
program crash.
● You use the EXCEPTION block to define how to handle different errors.
Structure of Exception Handling in PL/SQL
plsql
BEGIN
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
-- Code that might cause an error
EXCEPTION
WHEN exception_name1 THEN
-- Code to handle exception 1
WHEN exception_name2 THEN
-- Code to handle exception 2
WHEN OTHERS THEN
-- Code to handle all other exceptions
END;
🧑💻 Simple Example: Division with Exception Handling
plsql
DECLARE
numerator NUMBER := 10;
denominator NUMBER := 0;
result NUMBER;
BEGIN
result := numerator / denominator;
DBMS_OUTPUT.PUT_LINE('Result: ' || result);
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Cannot divide by zero.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Some other error occurred: ' || SQLERRM);
END;
Explanation:
● The code tries to divide 10 by 0, which causes a ZERO_DIVIDE exception.
● The WHEN ZERO_DIVIDE block catches this error and displays a friendly message.
● WHEN OTHERS catches any other unexpected exceptions.
🔍 How Exception Handling Works
1.Normal Execution: PL/SQL runs the code inside the BEGIN...END block.
2.Error Occurs: When an error happens, PL/SQL immediately jumps to the EXCEPTION block.
3.Matching Exception: It looks for a matching WHEN clause for that error.
4.Handling the Error: Executes the matching WHEN block.
5.Program Continues or Ends: After handling, the program either continues or ends gracefully.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔍 Common Built-In Exceptions
Exception Name Triggered When...
NO_DATA_FOUND A SELECT INTO statement returns no rows
TOO_MANY_ROWS SELECT INTO returns more than one row
ZERO_DIVIDE You divide a number by zero
INVALID_NUMBER Conversion of a string to a number fails
OTHERS Any other unhandled exception
VALUE_ERROR :
VALUE_ERROR is a built-in exception in PL/SQL that occurs when:
● There's a data type mismatch.
● A numeric variable overflows.
● A string is too long for the variable.
Example: Assigning a Long String to a Short Variable
pl
DECLARE
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
-- Define a variable with fixed length
v_name VARCHAR2(5);
BEGIN
-- Try to assign a longer string than allowed
v_name := 'Jonathan';
-- This line won't be reached if error occurs
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error: Assigned value is too long for the variable.');
END;
🔍 Explanation
● v_name can only hold 5 characters.
● 'Jonathan' has 8 characters.
● This causes a VALUE_ERROR because PL/SQL cannot truncate the string automatically.
● The EXCEPTION block catches the error and prints a friendly message.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Another Example: Number Too Large
plsql
DECLARE
v_num NUMBER(3); -- Max 3 digits
BEGIN
v_num := 12345; -- Too large, causes VALUE_ERROR
DBMS_OUTPUT.PUT_LINE('Value: ' || v_num);
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error: Number is too large for the variable.');
END;
🧠 When VALUE_ERROR Happens
Cause Example
String too long Assigning 'hello world' to VARCHAR2(5)
Number overflow Assigning 999999 to NUMBER(3)
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Invalid type conversion Converting 'abc' to NUMBER
Mismatched data type in assignment Using TO_NUMBER('abc')
❌ Invalid Rows in PL/SQL
In PL/SQL, an “invalid row” error often refers to errors like:
● TOO_MANY_ROWS: A SELECT INTO returns more than one row, which is not allowed.
● NO_DATA_FOUND: A SELECT INTO returns no rows.
Example: TOO_MANY_ROWS – Invalid Row Situation
⚠️ Problem:
You try to assign multiple rows from a query into a single variable using SELECT INTO.
plsql
DECLARE
v_ename [Link]%TYPE;
BEGIN
-- This will fail if more than one employee is in dept 10
SELECT ename INTO v_ename FROM emp WHERE deptno = 10;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_ename);
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Error: Query returned more than one row.');
END;
🧾 Explanation:
● SELECT INTO expects only one row.
● If the query returns multiple rows, PL/SQL throws a TOO_MANY_ROWS exception.
● The EXCEPTION block catches and handles it.
Example: NO_DATA_FOUND – Another Invalid Row Case
⚠️ Problem:
You try to fetch a row, but no data is returned.
plsql
DECLARE
v_ename [Link]%TYPE;
BEGIN
-- This will fail if no employee has empno 9999
SELECT ename INTO v_ename FROM emp WHERE empno = 9999;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: No employee found with that ID.');
END;
Summary: Invalid Row Errors
Exception Cause
TOO_MANY_ROWS Query returns more than one row into one variable
NO_DATA_FOUND Query returns zero rows into a variable
✅ Best Practices
● Use EXCEPTION blocks to catch these errors.
● Use CURSOR or BULK COLLECT when expecting multiple rows.
● Use IF EXISTS checks before SELECT INTO when unsure about data.
📦 STORED PROCEDURES in PL/SQL
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
A stored procedure in PL/SQL is a named block of code that performs a specific task and is stored in the
database. You can call it again and again, just like a function in any programming language.
✅ Basic Syntax
pl
CREATE OR REPLACE PROCEDURE procedure_name (
parameter1 [IN | OUT | IN OUT] datatype,
...
IS
-- Declarations
BEGIN
-- Executable statements
EXCEPTION
-- Optional error handling
END procedure_name;
🧪 Example 1: Procedure with No Parameters
plsql
CREATE OR REPLACE PROCEDURE welcome_message IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Welcome to PL/SQL Procedures!');
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
END;
🔸 To Execute:
plsql
BEGIN
welcome_message;
END;
📌 Output:
pgsql
Welcome to PL/SQL Procedures!
🧪 Example 2: Procedure with IN Parameter
pl
CREATE OR REPLACE PROCEDURE greet_user(p_name IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END;
🔸 To Execute:
plsql
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
BEGIN
greet_user('Alice');
END;
📌 Output:
Hello, Alice!
🧪 Example 3: Procedure with OUT Parameter
plsql
CREATE OR REPLACE PROCEDURE get_salary(
p_empno IN [Link]%TYPE,
p_sal OUT [Link]%TYPE
IS
BEGIN
SELECT sal INTO p_sal FROM emp WHERE empno = p_empno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_sal := 0;
END;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔸 To Call This Procedure:
pl
DECLARE
v_salary [Link]%TYPE;
BEGIN
get_salary(7369, v_salary);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
🧪 Example 4: Procedure with IN OUT Parameter
plsql
CREATE OR REPLACE PROCEDURE double_number(p_num IN OUT NUMBER) IS
BEGIN
p_num := p_num * 2;
END;
🔸 To Use It:
pl
DECLARE
v_number NUMBER := 5;
BEGIN
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
double_number(v_number);
DBMS_OUTPUT.PUT_LINE('Doubled Number: ' || v_number);
END;
📌 Output:
javascript
Doubled Number: 10
🧠 Summary
Mode Use
IN Pass input to procedure
OUT Return output from procedure
IN OUT Input and then return value
📘 Functions in plsql
A PL/SQL function without parameters:
● Takes no input
● Returns a single value
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Is useful for returning fixed values, system info, or results based on internal logic only
✅ Example 1: Return a Welcome Message
plsql
CREATE OR REPLACE FUNCTION get_welcome_message
RETURN VARCHAR2
IS
BEGIN
RETURN 'Welcome to PL/SQL!';
END;
🔸 Call the Function:
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE(get_welcome_message);
END;
📌 Output:
pgsql
Welcome to PL/SQL!
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
✅ Example 2: Return the Current System Date
plsql
CREATE OR REPLACE FUNCTION get_today_date
RETURN DATE
IS
BEGIN
RETURN SYSDATE;
END;
🔸 Call the Function:
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('Today is: ' || get_today_date);
END;
✅ Example 3: Return a Fixed Number
plsql
CREATE OR REPLACE FUNCTION get_constant_value
RETURN NUMBER
IS
BEGIN
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
RETURN 100;
END;
🔸 Call the Function:
plsql
BEGIN
DBMS_OUTPUT.PUT_LINE('Constant Value: ' || get_constant_value);
END;
🧠 Key Notes
● No parameters means no input is needed when calling.
● You still must return a value.
● Functions can return VARCHAR2, NUMBER, DATE, or even complex types.
📦 Packages in PL/SQL :
A package in PL/SQL is a collection of related procedures, functions, variables, constants, cursors, and
exceptions that are stored together under a single name.
Think of it like a toolbox: all the tools (procedures/functions) you need for a task are grouped and organized in
one place.
✅ Why Use Packages?
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Modularity: Group related code together.
● Reusability: Use the same package across multiple programs.
● Encapsulation: Hide private logic, expose only what’s needed.
● Improved performance: Only loads once into memory.
📦 A Package Has Two Parts:
Part Purpose
Specification Declares what’s available (like a header file)
Body Defines how it works (the actual logic)
1. Package Specification
This is the interface — it tells users what the package offers (but not how it works).
pl
CREATE OR REPLACE PACKAGE emp_utils IS
PROCEDURE show_message;
FUNCTION get_default_salary RETURN NUMBER;
END emp_utils;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
2. Package Body
This contains the code for the procedures and functions declared in the spec.
plsql
CREATE OR REPLACE PACKAGE BODY emp_utils IS
PROCEDURE show_message IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello from the EMP_UTILS package!');
END;
FUNCTION get_default_salary RETURN NUMBER IS
BEGIN
RETURN 30000;
END;
END emp_utils;
▶️ How to Use the Package
plsql
BEGIN
emp_utils.show_message;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
DBMS_OUTPUT.PUT_LINE('Default Salary: ' || emp_utils.get_default_salary);
END;
📌 Output:
sql
Hello from the EMP_UTILS package!
Default Salary: 30000
🧠 Benefits of Using Packages
Benefit Description
Encapsulation Hide private code (not in spec)
Reusability Use procedures/functions in many places
Performance Loaded once into memory
Modularity Group related logic together
🔐 Private Code in Package
Anything declared only in the body is private — it can’t be accessed from outside.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔁 Triggers in PL/SQL:
A trigger in PL/SQL is a named block of code that automatically executes in response to a specific event on
a table or view — such as an INSERT, UPDATE, or DELETE.
✅ What Is a Trigger?
● A trigger is fired automatically by the database when certain actions happen.
● Used for tasks like:
○ Auditing changes
○ Enforcing business rules
○ Preventing invalid transactions
📘 Syntax: Basic Trigger Structure
plsql
CREATE OR REPLACE TRIGGER trigger_name
BEFORE | AFTER INSERT | UPDATE | DELETE
ON table_name
[FOR EACH ROW] -- Optional: only for row-level triggers
BEGIN
-- Trigger logic
END;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🧪 Example 1: AFTER INSERT Trigger (Row-Level)
We want to log when a new employee is added.
Step 1: Create Log Table
sql
CREATE TABLE emp_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY,
emp_id NUMBER,
action VARCHAR2(10),
log_time TIMESTAMP
);
Step 2: Create the Trigger
plsql
CREATE OR REPLACE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO emp_log (emp_id, action, log_time)
VALUES (:[Link], 'INSERT', SYSTIMESTAMP);
END;
Explanation:
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Fires after a new row is inserted into employees.
● :[Link] refers to the new employee number.
● It logs the action in emp_log.
🧪 Example 2: BEFORE UPDATE Trigger – Prevent Salary Decrease
plsql
CREATE OR REPLACE TRIGGER trg_prevent_salary_cut
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :[Link] < :[Link] THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary decrease not allowed!');
END IF;
END;
Explanation:
● Fires before an update on employees.
● Compares old salary (:[Link]) with new salary (:[Link]).
● If the new salary is lower, it raises an error and stops the update.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🧪 Example 3: AFTER DELETE Trigger – Archive Deleted Rows
Suppose you want to keep deleted data in an archive table.
Step 1: Create Archive Table
sql
CREATE TABLE emp_archive AS SELECT * FROM employees WHERE 1=0;
Step 2: Create Trigger
pl
CREATE OR REPLACE TRIGGER trg_archive_on_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO emp_archive VALUES :OLD;
END;
Explanation:
● Fires after a row is deleted from employees.
● Inserts the deleted row into emp_archive using :OLD pseudorecord.
🧠 Pseudorecords: :NEW vs :OLD
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
Keyword Used In Represents
:NEW INSERT, UPDATE The new data
:OLD UPDATE, DELETE The existing (old) data
🔍 Trigger Types Summary
Type Description
BEFORE Trigger Executes before the event
AFTER Trigger Executes after the event
INSTEAD OF Used for views (replaces the action)
Row-Level Executes for each row affected
Statement-Level Executes once per SQL statement
✅ Real Use Cases
● Auditing: Track changes to sensitive tables.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
● Validation: Prevent invalid updates/deletes.
● Automatic actions: Archive, log, sync data, etc.
INSTEAD OF Trigger
An INSTEAD OF trigger is a special type of row-level trigger defined on a view, not on a table. When
someone runs an INSERT, UPDATE, or DELETE on that view, Oracle fires the trigger instead of performing
the standard DML.
Why Use INSTEAD OF Triggers?
● Views based on joins, aggregates, or other complex SQL constructs are generally non-updatable.
● These triggers enable INSERT, UPDATE, or DELETE operations on such views by implementing
the logic to modify underlying tables manually.
● They allow better data validation, integrity checks, and business logic encapsulation at the database
layer.
🧪 Simple Syntax
sql
CREATE OR REPLACE TRIGGER trigger_name
INSTEAD OF {INSERT | UPDATE | DELETE}
ON view_name
FOR EACH ROW
BEGIN
-- Your PL/SQL logic to handle operation on base tables
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
END;
● Only supported on views (not tables).
● Always defined as FOR EACH ROW (row-level trigger).
Example: Make a Join View Insertable
Step 1: Create Sample Tables
sql
CREATE TABLE departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(50)
);
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
dept_id NUMBER REFERENCES departments(dept_id)
);
Step 2: Create a Join View
sql
CREATE VIEW emp_details AS
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
The view emp_details is non-updatable because of the join.
Step 3: Create an INSTEAD OF INSERT Trigger
sql
CREATE OR REPLACE TRIGGER trg_emp_details_insert
INSTEAD OF INSERT ON emp_details
FOR EACH ROW
BEGIN
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (
:NEW.emp_id,
:NEW.emp_name,
(SELECT dept_id FROM departments WHERE dept_name = :NEW.dept_name)
);
END;
What Happens:
● When you try to INSERT into emp_details view,
● Oracle invokes this trigger,
● It uses :NEW.emp_* values and maps the dept_name to its ID,
● Then inserts the row into employees.
[Link] @teluguwebguru
SQL Notes TeluguWebGuru
🔍 Key Properties
● Only for views: Cannot be defined on tables. They let you override default behavior for DML on
views.
● Always row-level: Each row modification triggers the code.
● :NEW and :OLD values are accessible, but you cannot modify them within the trigger.
Use Cases
Scenario Use of INSTEAD OF Trigger
Views based on J O I N S Make them support INSERT, UPDATE, or DELETE
Views with validation needs Validate or transform input data before modifying base tables
Applications working via views Allow business logic in database layer instead of in application code
📝 Summary
● Use INSTEAD OF triggers to handle DML on views that are otherwise non-updatable.
● Triggers fire instead of the attempted view DML, leveraging :NEW (and optionally :OLD) values.
[Link] @teluguwebguru
You might also like
- Understanding Structured and Unstructured DataNo ratings yetUnderstanding Structured and Unstructured Data121 pages
- Database Management System Practical GuideNo ratings yetDatabase Management System Practical Guide36 pages
- Module 1 Tables Complete Classroom NotesNo ratings yetModule 1 Tables Complete Classroom Notes220 pages
- Oracle Database Overview and ManagementNo ratings yetOracle Database Overview and Management180 pages
- What Is Database?: Before Deep Diving Into This, Let's Understand What A Database IsNo ratings yetWhat Is Database?: Before Deep Diving Into This, Let's Understand What A Database Is233 pages
- DBMS Advantages and Disadvantages ExplainedNo ratings yetDBMS Advantages and Disadvantages Explained24 pages
- Understanding Database Management SystemsNo ratings yetUnderstanding Database Management Systems34 pages
- Overview of Relational Database ManagementNo ratings yetOverview of Relational Database Management22 pages
- Database Design and SQL Operations GuideNo ratings yetDatabase Design and SQL Operations Guide29 pages
- Overview of Database Management SystemsNo ratings yetOverview of Database Management Systems25 pages
- Understanding Database Management SystemsNo ratings yetUnderstanding Database Management Systems2 pages
- SQL Indexing and String Aggregation GuideNo ratings yetSQL Indexing and String Aggregation Guide13 pages
- DBMS Practical File for Bikaner CollegeNo ratings yetDBMS Practical File for Bikaner College30 pages
- SQL Command Types: DDL, DQL, DML, DCL, TCLNo ratings yetSQL Command Types: DDL, DQL, DML, DCL, TCL7 pages
- Informatics Practices Class XI Sample PaperNo ratings yetInformatics Practices Class XI Sample Paper13 pages
- Introduction to Structured Query LanguageNo ratings yetIntroduction to Structured Query Language45 pages
- Sap Hana Sqlscript Reference (Sap Hana Platform 2.0 Sps 00)No ratings yetSap Hana Sqlscript Reference (Sap Hana Platform 2.0 Sps 00)172 pages
- Understanding Database Management SystemsNo ratings yetUnderstanding Database Management Systems45 pages
- CBSE Class 12 Informatics Practices Sample PaperNo ratings yetCBSE Class 12 Informatics Practices Sample Paper9 pages
- Ingilizce Almanca Ve Türkce SQL Interview Soru CevaplarNo ratings yetIngilizce Almanca Ve Türkce SQL Interview Soru Cevaplar93 pages