SQL & PL/SQL Comprehensive Notes

Table of Contents

1. SQL Clauses
2. SQL Operators
3. SQL Joins
4. Aggregate Functions
5. NULL Handling
6. PL/SQL Fundamentals
7. Stored Procedures
8. SQL Views
9. SQL Constraints
10. SQL Commands (DDL, DML, DCL, TCL)
11. PL/SQL Collections
12. PL/SQL Control Structures
13. PL/SQL Packages
14. DBMS_OUTPUT Package
15. SQL Injection
16. String Functions
17. Database Snapshots

1. SQL Clauses

WHERE Clause

Purpose: Filters rows based on specified conditions

Used with: SELECT, UPDATE, DELETE statements
Position: Comes after FROM clause, before GROUP BY
Key Points :
WHERE filters individual rows BEFORE grouping
WHERE cannot use aggregate functions
WHERE works with comparison operators, logical operators, pattern matching

Example:

SELECT * FROM employees

WHERE salary > 50000;

GROUP BY Clause

Purpose: Groups rows that have the same values in specified columns
Used with: Aggregate functions (COUNT, SUM, AVG, MAX, MIN)
Key Points :
Every non-aggregated column in SELECT must be in GROUP BY
Filters data AFTER grouping using HAVING clause
FALSE: WHERE clause is NOT used to restrict groups (it's HAVING)

Example:

SELECT department, COUNT(*) as emp_count

FROM employees
GROUP BY department;

HAVING Clause

Purpose: Filters groups created by GROUP BY

Difference from WHERE:
WHERE filters rows before grouping
HAVING filters groups after grouping
HAVING can use aggregate functions

Example:

SELECT department, COUNT(*) as emp_count

FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Comparison Table: WHERE vs HAVING

 Feature WHERE HAVING

Filters Individual rows Groups

When applied Before GROUP BY After GROUP BY

Can use aggregate

No Yes
functions

Position After FROM After GROUP BY

WHERE salary > HAVING COUNT(*) >

Example
50000 5

2. SQL Operators

BETWEEN Operator

Purpose: Selects values within a given range

Syntax: column_name BETWEEN value1 AND value2
Key Points :
Range is INCLUSIVE (includes both boundary values)
Works with numbers, text, and dates
TRUE: DATE values CAN be used with BETWEEN

Example:

-- Numbers
SELECT * FROM products
WHERE price BETWEEN 10 AND 20; -- Includes 10 and 20

-- Dates
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Text (alphabetical order)

SELECT * FROM employees
WHERE name BETWEEN 'A' AND 'M';

Range Behavior:

BETWEEN 10 AND 20 returns values: 10, 11, 12, ..., 19, 20

Both 10 and 20 are included

LIKE Operator

Purpose: Pattern matching in strings

Used in : WHERE clause
Wildcards:
% : Represents zero, one, or multiple characters
_ : Represents exactly one single character

Examples:

-- % wildcard
SELECT * FROM customers
WHERE name LIKE 'A%'; -- Starts with A
WHERE name LIKE '%son'; -- Ends with son
WHERE name LIKE '%an%'; -- Contains 'an' anywhere

-- _ wildcard
SELECT * FROM employees
WHERE phone LIKE '98_______'; -- Starts with 98, followed by 7 characters
WHERE code LIKE '_BC%'; -- Second and third characters are BC

AND Operator
 Purpose: Combines multiple conditions (ALL must be true)
Correct Syntax: condition1 AND condition2
Wrong Syntax: condition1 && condition2 , condition1 & condition2

Example:

SELECT * FROM employees

WHERE salary > 50000 AND department = 'IT';

OR Operator

Purpose: Combines conditions (ANY condition can be true)

Key Point : Displays record if ANY condition separated by OR is true

Example:

SELECT * FROM employees

WHERE department = 'IT' OR department = 'Sales';

Comparison Operators

Valid in
Operator Description
WHERE?

= Equal to Yes

!= or <> Not equal to Yes

> Greater than Yes

< Less than Yes

Greater than or
>= Yes
equal

<= Less than or equal Yes

== Double equals NOT VALID

Note : == is NOT a valid SQL operator. Use single = for comparison.

3. SQL Joins

Join Types Overview

Result Set
Join Type Description Venn Diagram
Size

Returns matching rows from both

INNER JOIN Smallest A∩B
tables

A (includes A ∩
LEFT JOIN All from left + matching from right Medium
B)

B (includes A ∩
RIGHT JOIN All from right + matching from left Medium
B)

FULL OUTER
All rows from both tables Largest A∪B
JOIN

CROSS JOIN Cartesian product of both tables Very Large A×B

SELF JOIN Table joined with itself Varies -

INNER JOIN

Returns only matching rows from both tables

Most commonly used join

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON [Link] = [Link];

LEFT JOIN (LEFT OUTER JOIN)

Returns ALL rows from left table

Matching rows from right table
NULL for non-matching right table rows
Set Representation: Table1 (includes all of Table1)

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON [Link] = [Link];

RIGHT JOIN (RIGHT OUTER JOIN)

Returns ALL rows from right table

Matching rows from left table
NULL for non-matching left table rows

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2
ON [Link] = [Link];

FULL OUTER JOIN

Returns ALL rows from both tables

Largest result set among standard joins
NULL where no match exists
Set Representation: Table1 ∪ Table2

Syntax:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON [Link] = [Link];

CROSS JOIN

Cartesian product of both tables

Every row from table1 with every row from table2
If table1 has 100 rows and table2 has 50 rows → Result: 5000 rows
Returns very large result sets

Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

-- Or without CROSS JOIN keyword

SELECT columns
FROM table1, table2;
SELF JOIN

Table joined with itself

Purpose: Compare values in a column with other values in the same column of the same table
Uses table aliases to treat same table as two different tables

Use Cases:

Finding hierarchical relationships (employee-manager)

Comparing rows within same table
Finding duplicates

Example:

-- Finding employees and their managers

SELECT [Link] AS Employee, [Link] AS Manager
FROM employees e1
INNER JOIN employees e2
ON e1.manager_id = e2.employee_id;

-- Finding pairs of employees in same city

SELECT [Link], [Link]
FROM employees e1
INNER JOIN employees e2
ON [Link] = [Link]
WHERE e1.employee_id < e2.employee_id;

4. Aggregate Functions

Overview

Perform calculations on a set of values

Return a single value
Can be used together in a single SELECT statement

Common Aggregate Functions

Function Description Example

Counts number of
COUNT() COUNT(*) , COUNT(column)
rows

SUM() Adds up values SUM(salary)

AVG() Calculates average AVG(price)

MAX() Finds maximum value MAX(age)

MIN() Finds minimum value MIN(salary)

COUNT Function

COUNT(*): Counts all rows (including NULL)

COUNT(column) : Counts non-NULL values
COUNT(DISTINCT column) : Counts unique non-NULL values

Important Rule:

If non-aggregated columns are used with aggregate functions in SELECT

Those columns MUST be in GROUP BY clause

Example:
 -- Wrong - department not in GROUP BY
SELECT department, COUNT(*)
FROM employees;

-- Correct
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

MIN and MAX Functions

Can be used together in a single SELECT statement

Work with numbers, dates, and strings

Syntax:

-- Minimum value
SELECT MIN(column_name)
FROM table_name
WHERE condition;

-- Using both together

SELECT MIN(salary), MAX(salary), AVG(salary)
FROM employees;

Multiple Aggregate Functions

TRUE: You can use multiple aggregate functions together

Example:

SELECT
COUNT(*) as total_employees,
MIN(salary) as min_salary,
MAX(salary) as max_salary,
AVG(salary) as avg_salary,
SUM(salary) as total_payroll
FROM employees;

5. NULL Handling

Understanding NULL

NULL represents missing or unknown data

NULL is NOT zero, empty string, or false
NULL is a special marker for absence of value

NULL in Calculations

Key Rule: Any arithmetic operation with NULL results in NULL

-- Examples
NULL + 5 = NULL
NULL * 10 = NULL
50 - NULL = NULL
100 / NULL = NULL

-- When known value is added to NULL

Known_Value + NULL = NULL Correct Answer

COALESCE Function

Purpose: Returns the first NON-NULL expression among its arguments

 Syntax: COALESCE(expression1, expression2, ..., expressionN)
Goes through arguments left to right
Returns first non-NULL value

Example:

-- Returns first non-NULL value

SELECT COALESCE(NULL, NULL, 'Third', 'Fourth');
-- Result: 'Third'

SELECT COALESCE(phone1, phone2, phone3, 'No phone');

-- Returns first available phone number, or 'No phone' if all NULL

-- Practical use
SELECT
name,
COALESCE(mobile, office_phone, 'No contact') as contact
FROM employees;

COALESCE vs ISNULL/IFNULL

Function Arguments Description

Returns first non-NULL from many

COALESCE() Multiple
arguments

Two (SQL
ISNULL() Returns second if first is NULL
Server)

IFNULL() Two (MySQL) Returns second if first is NULL

NULL in Comparisons

-- NULL comparisons
NULL = NULL -- Result: NULL (not TRUE!)
NULL <> NULL -- Result: NULL

-- Correct way to check NULL

column IS NULL -- Correct
column IS NOT NULL -- Correct

column = NULL -- Wrong - always returns NULL

column != NULL -- Wrong - always returns NULL

6. PL/SQL Fundamentals

What is PL/SQL?

PL/SQL = Procedural Language extension to SQL

Combines SQL with procedural programming features
Used in Oracle databases
Block-structured language

PL/SQL Block Structure

 DECLARE
-- Declaration section (optional)
-- Variables, constants, cursors
BEGIN
-- Execution section (mandatory)
-- SQL statements and procedural logic
EXCEPTION
-- Exception handling section (optional)
-- Error handling
END;
/

Variable Declaration

Correct Syntax:

variable_name datatype [NOT NULL] [:= value];

Examples:

DECLARE
-- Basic declaration
emp_name VARCHAR2(50);

-- With initialization
salary NUMBER := 50000;

-- With NOT NULL constraint

dept_id NUMBER NOT NULL := 10;

-- With %TYPE (inherits column datatype)

emp_salary [Link]%TYPE;

-- With %ROWTYPE (entire row structure)

emp_rec employees%ROWTYPE;
END;
/

Wrong Syntax:

-- These are INCORRECT

datatype variable_name [NOT NULL := value]; -- Wrong order
variable_name datatype (NOT NULL := value); -- Wrong brackets

Constants in PL/SQL

Declared using CONSTANT keyword

Must be initialized at declaration time
Cannot be changed after initialization

Syntax:

DECLARE
PI CONSTANT NUMBER := 3.14159;
TAX_RATE CONSTANT NUMBER := 0.18;
COMPANY_NAME CONSTANT VARCHAR2(50) := 'TechCorp';
BEGIN
-- PI := 3.14; -- ERROR: Cannot modify constant
dbms_output.put_line('PI value: ' || PI);
END;
/

Key Point : FALSE - We CANNOT change the value of a constant later in the program
String Literals in PL/SQL

Always enclosed in single quotes ' '

Not double quotes " "

Examples:

DECLARE
name VARCHAR2(50) := 'John Doe'; -- Correct
city VARCHAR2(50) := 'New York'; -- Correct
-- message VARCHAR2(50) := "Hello"; -- Wrong
BEGIN
dbms_output.put_line('Hello World'); -- Correct
END;
/

Basic PL/SQL Example

DECLARE
a NUMBER(2) := 100;
BEGIN
IF (a < 50) THEN
dbms_output.put_line('Value of a is less than 50');
ELSIF (a < 75) THEN -- Note: ELSIF not ELSEIF
dbms_output.put_line('Value of a is less than 75');
ELSE
dbms_output.put_line('Value of a is greater than 75');
END IF;
dbms_output.put_line('Value of a is: ' || a);
END;
/

Output:

Value of a is greater than 75

Value of a is: 100
PL/SQL procedure successfully completed.

PL/SQL String Functions

UPPER() Function

Purpose: Converts string to uppercase

Returns: Uppercase string
Syntax: UPPER(string)

Example:

DECLARE
name VARCHAR2(50) := 'sanfoundry';
BEGIN
dbms_output.put_line(UPPER(name)); -- Output: SANFOUNDRY
END;
/

Other String Functions

Function Description Example

LOWER(x) Converts to lowercase LOWER('HELLO') → 'hello'

UPPER(x) Converts to uppercase UPPER('hello') → 'HELLO'

INITCAP(x) Capitalizes first letter INITCAP('hello world') → 'Hello World'

 LENGTH(x) Returns string length LENGTH('Hello') → 5
Function Description Example
SUBSTR(x,start,len) Extracts substring SUBSTR('Hello',1,3) → 'Hel'

CONCAT(x,y) Concatenates strings CONCAT('Hello',' World')

TRIM(x) Removes leading/trailing spaces TRIM(' Hello ') → 'Hello'

7. Stored Procedures

What are Stored Procedures?

Pre-compiled SQL code stored in the database

Can accept parameters and return values
Reusable and improve performance
Stored at the database level

Where are Stored Procedures Stored?

Stored in the database system catalog/data dictionary

In SQL Server: [Link] system view
In Oracle: USER_PROCEDURES , ALL_PROCEDURES , DBA_PROCEDURES
Compiled and stored in optimized form

Creating Stored Procedures

Basic Syntax:

CREATE PROCEDURE procedure_name

@param1 datatype,
@param2 datatype
AS
BEGIN
-- SQL statements
END;

Example:

-- SQL Server
CREATE PROCEDURE GetEmployeeDetails
@emp_id INT,
@dept_name VARCHAR(50) OUTPUT
AS
BEGIN
SELECT @dept_name = department
FROM employees
WHERE employee_id = @emp_id;
END;

-- Oracle PL/SQL
CREATE OR REPLACE PROCEDURE GetEmployeeDetails
(p_emp_id IN NUMBER,
p_dept_name OUT VARCHAR2)
AS
BEGIN
SELECT department INTO p_dept_name
FROM employees
WHERE employee_id = p_emp_id;
END;
/

Parameters in Stored Procedures

Can pass ANY number of parameters (no fixed limit in most RDBMS)
 Three types of parameters:
IN: Input parameter (default)
OUT: Output parameter
IN OUT: Both input and output

Example:

CREATE PROCEDURE CalculateSalary

@emp_id INT, -- IN parameter
@bonus DECIMAL, -- IN parameter
@new_salary DECIMAL OUTPUT -- OUT parameter
AS
BEGIN
SELECT @new_salary = salary + bonus
FROM employees
WHERE employee_id = @emp_id;
END;

Creating Sub-programs at Schema Level

Syntax Options:

-- Standalone procedure
CREATE [OR REPLACE] PROCEDURE procedure_name
AS
BEGIN
-- code
END;

-- Standalone function
CREATE [OR REPLACE] FUNCTION function_name
RETURN return_type
AS
BEGIN
-- code
RETURN value;
END;

-- Package specification (declaration)

CREATE [OR REPLACE] PACKAGE package_name
AS
-- declarations
END;

-- Package body (implementation)

CREATE [OR REPLACE] PACKAGE BODY package_name
AS
-- implementations
END;

Advantages of Stored Procedures

Performance: Pre-compiled and optimized

Security: Encapsulate logic, grant execute permissions
Reusability: Write once, use many times
Reduced network traffic: Multiple statements in one call
Maintainability: Centralized business logic

8. SQL Views

What are Views?

Virtual tables based on SQL queries

Don't store data physically (except materialized views)
Also called : Virtual Tables
 Dynamic - reflect current data from base tables

Creating Views

Basic Syntax:

CREATE VIEW view_name AS

SELECT columns
FROM tables
WHERE conditions;

Example:

-- Simple view
CREATE VIEW active_employees AS
SELECT employee_id, name, salary, department
FROM employees
WHERE status = 'Active';

-- View with joins

CREATE VIEW employee_dept_view AS
SELECT [Link], [Link], d.dept_name, [Link]
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

Using Views

-- Query a view like a table

SELECT * FROM active_employees
WHERE salary > 50000;

-- Views in joins
SELECT * FROM active_employees ae
JOIN projects p ON ae.employee_id = p.employee_id;

WITH CHECK OPTION

Ensures that INSERT and UPDATE through the view satisfy the view's WHERE condition
Prevents modifications that would make rows invisible to the view

Syntax:

CREATE VIEW high_salary_employees AS

SELECT * FROM employees
WHERE salary > 50000
WITH CHECK OPTION;

Example:

-- This will succeed

INSERT INTO high_salary_employees
VALUES (101, 'John', 60000, 'IT'); -- salary > 50000

-- This will FAIL

INSERT INTO high_salary_employees
VALUES (102, 'Jane', 40000, 'HR'); -- violates check option

Types of Views

Type Description Use Case

Based on single table, no

Simple View Direct DML operations
functions
 Complex View Multiple tables, joins, functions Read-only queries
Type Description Use Case
Materialized Performance for complex
Physically stores data
View queries

Updatable View Allows INSERT, UPDATE, DELETE Limited scenarios

View vs Table

Feature Table View

Data storage Stores data physically Stores only query definition

Space Takes storage space Minimal space

May be slower for complex

Performance Faster for simple queries
queries

Update Direct data modification Limited update capability

Called as Base table/Actual table Virtual table

9. SQL Constraints

Overview of Constraints

Rules enforced on table columns

Ensure data integrity and accuracy
Can be column-level or table-level

Types of Constraints

Constraint Description Level

PRIMARY KEY Uniquely identifies each row Column/Table

FOREIGN KEY Links tables together Column/Table

UNIQUE Ensures all values are different Column/Table

NOT NULL Column cannot have NULL Column only

CHECK Validates data based on condition Column/Table

DEFAULT Sets default value Column only

DEFAULT Constraint

Provides default value when no value is specified

CANNOT be applied as table-level constraint
Only column-level constraint

Syntax:
 -- At creation
CREATE TABLE employees (
employee_id INT,
name VARCHAR(50),
hire_date DATE DEFAULT CURRENT_DATE, -- DEFAULT constraint
status VARCHAR(20) DEFAULT 'Active', -- DEFAULT constraint
salary DECIMAL DEFAULT 0
);

-- Cannot do this (table level)

-- DEFAULT constraint is only column-level

Adding DEFAULT with ALTER:

ALTER TABLE employees

ADD CONSTRAINT df_status DEFAULT 'Active' FOR status;

CHECK Constraint

Validates data based on condition

Can be column-level or table-level

Syntax:

-- Column level
CREATE TABLE employees (
employee_id INT,
age INT CHECK (age >= 18),
salary DECIMAL CHECK (salary > 0)
);

-- Table level
CREATE TABLE employees (
employee_id INT,
age INT,
salary DECIMAL,
CONSTRAINT chk_employee CHECK (age >= 18 AND salary > 0)
);

Adding Constraints with ALTER TABLE

Syntax:

-- Add PRIMARY KEY

ALTER TABLE table_name
ADD CONSTRAINT pk_name PRIMARY KEY (column_name);

-- Add FOREIGN KEY

ALTER TABLE table_name
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES other_table(column_name);

-- Add UNIQUE
ALTER TABLE table_name
ADD CONSTRAINT uq_name UNIQUE (column_name);

-- Add CHECK
ALTER TABLE table_name
ADD CONSTRAINT chk_name CHECK (condition);

-- Add DEFAULT
ALTER TABLE table_name
ADD CONSTRAINT df_name DEFAULT value FOR column_name;
Examples:

-- Add CHECK constraint

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 10000);

-- Add DEFAULT constraint

ALTER TABLE employees
ALTER COLUMN status SET DEFAULT 'Active';

-- Add FOREIGN KEY

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

Constraint Levels

Level Description Applicable Constraints

Column-level Applied to single column ALL constraints

Applied after column PRIMARY KEY, FOREIGN KEY, UNIQUE,

Table-level
definitions CHECK

Column-only MUST be column-level NOT NULL, DEFAULT

10. SQL Commands

Command Categories

Category Full Form Purpose Commands

CREATE, ALTER, DROP,

DDL Data Definition Language Define database structure
TRUNCATE

DML Data Manipulation Language Manipulate data SELECT, INSERT, UPDATE, DELETE

DCL Data Control Language Control access GRANT, REVOKE

Transaction Control
TCL Manage transactions COMMIT, ROLLBACK, SAVEPOINT
Language

DDL - Data Definition Language

DROP Command

Category: DDL (Data Definition Language)

Purpose: Delete database objects permanently
Cannot be rolled back (in most RDBMS)

Dropping Tables:

-- Drop table in same database

DROP TABLE table_name;

-- Drop table in different database (SQL Server)

DROP TABLE database_name.dbo.table_name; -- Correct

-- Wrong syntaxes
-- DROP TABLE database_name.table_name
-- DROP TABLE table_name.database_name

Other DROP Commands:

 -- Drop database
DROP DATABASE database_name;

-- Drop view
DROP VIEW view_name;

-- Drop index
DROP INDEX index_name ON table_name;

-- Drop procedure
DROP PROCEDURE procedure_name;

ALTER Command

Purpose: Modify existing database objects

Syntax:

-- ALTER TABLE - Add column

ALTER TABLE table_name
ADD column_name datatype;

-- ALTER TABLE - Modify column

ALTER TABLE table_name
ALTER COLUMN column_name new_datatype;

-- Modify is not a standard SQL command

-- Use ALTER to modify tables

Examples:

-- Add column
ALTER TABLE employees
ADD email VARCHAR(100);

-- Modify column
ALTER TABLE employees
ALTER COLUMN salary DECIMAL(12,2);

-- Drop column
ALTER TABLE employees
DROP COLUMN temp_address;

-- Rename column (varies by RDBMS)

ALTER TABLE employees
RENAME COLUMN old_name TO new_name;

TRUNCATE vs DROP vs DELETE

Feature TRUNCATE DROP DELETE

Category DDL DDL DML

Specific
Removes All rows Entire table
rows

Can be rolled
No (usually) No Yes
back

WHERE clause No No Yes

Not Not
Triggers Activated
activated activated

Reset identity Yes Yes No

 Feature
Speed TRUNCATE
Fastest DROP
Fast DELETE
Slowest

DML - Data Manipulation Language

INSERT INTO with SELECT

Copies data from one table to another

When SELECT is used, VALUES clause is omitted

Syntax:

-- Standard INSERT
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

-- INSERT with SELECT (no VALUES clause)

INSERT INTO table_name (column1, column2)
SELECT column1, column2
FROM other_table
WHERE condition;

Examples:

-- Copy all data

INSERT INTO employees_backup
SELECT * FROM employees;

-- Copy specific rows

INSERT INTO active_employees (id, name, salary)
SELECT employee_id, name, salary
FROM employees
WHERE status = 'Active';

-- Insert with calculation

INSERT INTO employee_stats (dept, avg_salary)
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

Key Points:

Table name : Always required

VALUES clause : Omitted when using SELECT
Column names: Optional if inserting all columns in order

SQL Command Keywords

Alias

Purpose: Temporarily rename table or column in a specific SQL statement

Keywords: AS (optional)
Not: Rename, Change, Modify (these are ALTER operations)

Syntax:

-- Column alias
SELECT column_name AS alias_name
FROM table_name;

-- Table alias
SELECT t1.column1, t2.column2
FROM table1 AS t1
JOIN table2 AS t2 ON [Link] = [Link];
Examples:

-- Column aliases
SELECT
first_name AS fname,
last_name AS lname,
salary * 12 AS annual_salary
FROM employees;

-- Without AS keyword (also valid)

SELECT
first_name fname,
last_name lname
FROM employees;

-- Table aliases in joins

SELECT [Link], d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

11. PL/SQL Collections

What are Collections?

Ordered groups of elements of same datatype

Similar to arrays in other languages
Three types: Associative Arrays, Nested Tables, VARRAYs

Collection Methods

Method Description Returns

EXISTS(n) Checks if element exists at index n Boolean

COUNT Returns number of elements Number

Returns maximum size (VARRAY

LIMIT Number
only)

FIRST Returns first index number Number

LAST Returns last index number Number

NEXT(n) Returns next index after n Number

PRIOR(n) Returns previous index before n Number

EXTEND Adds elements to collection -

TRIM Removes elements from end -

DELETE Removes elements -

EXISTS Method

Purpose: Checks if specified element is present in collection

Returns: TRUE if exists, FALSE otherwise
Prevents SUBSCRIPT_BEYOND_COUNT exception

Example:
 DECLARE
TYPE num_table IS TABLE OF NUMBER;
numbers num_table := num_table(10, 20, 30, 40, 50);
BEGIN
IF [Link](3) THEN -- Check if 3rd element exists
dbms_output.put_line('Element 3: ' || numbers(3));
END IF;

IF NOT [Link](10) THEN

dbms_output.put_line('Element 10 does not exist');
END IF;
END;
/

COUNT Method

Returns total number of elements

For associative arrays, counts populated elements

Example:

DECLARE
TYPE num_table IS TABLE OF NUMBER;
numbers num_table := num_table(10, 20, 30);
BEGIN
dbms_output.put_line('Count: ' || [Link]); -- Output: 3
END;
/

EXTEND Method

Adds elements to collection

Not for associative arrays
Syntax:
EXTEND - adds one NULL element
EXTEND(n) - adds n NULL elements
EXTEND(n, i) - adds n copies of element i

TRIM Method

Removes n elements from end of collection

Syntax: TRIM(n)

Example:

DECLARE
TYPE num_table IS TABLE OF NUMBER;
numbers num_table := num_table(10, 20, 30, 40, 50);
BEGIN
[Link](2); -- Remove last 2 elements
dbms_output.put_line('Count: ' || [Link]); -- Output: 3
END;
/

DELETE Method

Removes specific elements

Syntax:
DELETE - removes all elements
DELETE(n) - removes element at index n
DELETE(m, n) - removes elements from m to n

12. PL/SQL Control Structures

IF-THEN-ELSIF-ELSE Statement
Correct Syntax:

IF condition1 THEN
statements;
ELSIF condition2 THEN -- Note: ELSIF not ELSEIF
statements;
ELSIF condition3 THEN
statements;
ELSE
statements;
END IF;

Important: Use ELSIF , not ELSEIF

Example:

DECLARE
score NUMBER := 85;
grade CHAR(1);
BEGIN
IF score >= 90 THEN
grade := 'A';
ELSIF score >= 80 THEN
grade := 'B';
ELSIF score >= 70 THEN
grade := 'C';
ELSIF score >= 60 THEN
grade := 'D';
ELSE
grade := 'F';
END IF;
dbms_output.put_line('Grade: ' || grade);
END;
/

Loop Control Statements

Statement Purpose Exits Loop? Continues Loop?

EXIT Exit loop completely Yes No

EXIT WHEN Exit when condition true Yes No

Skip rest, go to next

CONTINUE No Yes
iteration

CONTINUE WHEN Skip when condition true No Yes

GOTO Jump to labeled statement Depends Depends

CONTINUE Statement

Purpose: Skip remaining part of current iteration

Forces next iteration to take place
Available from Oracle 11g onwards

Example:
 DECLARE
counter NUMBER := 0;
BEGIN
LOOP
counter := counter + 1;

IF counter = 5 THEN
CONTINUE; -- Skip when counter is 5
END IF;

dbms_output.put_line('Counter: ' || counter);

EXIT WHEN counter = 10;

END LOOP;
END;
/
-- Output: 1, 2, 3, 4, 6, 7, 8, 9, 10 (5 is skipped)

EXIT Statement

Exits loop completely

Can use with condition: EXIT WHEN condition

Example:

DECLARE
counter NUMBER := 0;
BEGIN
LOOP
counter := counter + 1;
dbms_output.put_line('Counter: ' || counter);

IF counter = 5 THEN
EXIT; -- Exit loop
END IF;
END LOOP;
dbms_output.put_line('Loop ended');
END;
/

GOTO Statement

Jumps to labeled statement

Use sparingly (can make code hard to follow)

Example:

BEGIN
dbms_output.put_line('Before GOTO');
GOTO skip_section;

dbms_output.put_line('This will be skipped');

<<skip_section>>
dbms_output.put_line('After GOTO');
END;
/

Loop Types

Basic LOOP
 LOOP
statements;
EXIT WHEN condition;
END LOOP;

WHILE LOOP

WHILE condition LOOP

statements;
END LOOP;

FOR LOOP

FOR counter IN lower_bound..upper_bound LOOP

statements;
END LOOP;

-- Reverse
FOR counter IN REVERSE lower_bound..upper_bound LOOP
statements;
END LOOP;

13. PL/SQL Packages

What are Packages?

Group related procedures, functions, variables, and cursors

Encapsulation unit in PL/SQL
Divided into 2 parts:
1. Package Specification (Interface/Header)
2. Package Body (Implementation)

Package Structure
 -- 1. Package Specification (Declaration)
CREATE OR REPLACE PACKAGE package_name AS
-- Public declarations
-- Procedures, functions, variables, constants, cursors

PROCEDURE procedure_name(params);
FUNCTION function_name(params) RETURN return_type;
variable_name datatype;
END package_name;
/

-- 2. Package Body (Implementation)

CREATE OR REPLACE PACKAGE BODY package_name AS
-- Private declarations (optional)

-- Implementations of procedures/functions
PROCEDURE procedure_name(params) IS
BEGIN
-- code
END;

FUNCTION function_name(params) RETURN return_type IS

BEGIN
-- code
RETURN value;
END;
END package_name;
/

Example Package
 -- Specification
CREATE OR REPLACE PACKAGE employee_pkg AS
-- Public procedures
PROCEDURE hire_employee(p_name VARCHAR2, p_salary NUMBER);
PROCEDURE fire_employee(p_emp_id NUMBER);

-- Public function
FUNCTION get_employee_count RETURN NUMBER;

-- Public constant
MIN_SALARY CONSTANT NUMBER := 10000;
END employee_pkg;
/

-- Body
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
-- Private variable
v_last_update DATE;

PROCEDURE hire_employee(p_name VARCHAR2, p_salary NUMBER) IS

BEGIN
INSERT INTO employees (name, salary, hire_date)
VALUES (p_name, p_salary, SYSDATE);
v_last_update := SYSDATE;
END;

PROCEDURE fire_employee(p_emp_id NUMBER) IS

BEGIN
DELETE FROM employees WHERE employee_id = p_emp_id;
v_last_update := SYSDATE;
END;

FUNCTION get_employee_count RETURN NUMBER IS

v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
RETURN v_count;
END;
END employee_pkg;
/

Using Packages
 -- Call package procedure
BEGIN
employee_pkg.hire_employee('John Doe', 50000);
employee_pkg.fire_employee(101);
END;
/

-- Call package function

DECLARE
emp_count NUMBER;
BEGIN
emp_count := employee_pkg.get_employee_count;
dbms_output.put_line('Total employees: ' || emp_count);
END;
/

-- Access package constant

BEGIN
IF salary < employee_pkg.MIN_SALARY THEN
-- do something
END IF;
END;
/

Package Advantages

Modularity : Related objects grouped together

Encapsulation: Hide implementation details
Performance: Loaded into memory once
Easier maintenance: Change body without changing specification
Overloading: Multiple subprograms with same name

14. DBMS_OUTPUT Package

What is DBMS_OUTPUT?

Oracle-supplied package for displaying output

Used for debugging and testing
Output displayed in SQL*Plus, SQL Developer, etc.
Must enable with: SET SERVEROUTPUT ON

DBMS_OUTPUT Procedures

Procedure Purpose Syntax

PUT Write to buffer (no newline) DBMS_OUTPUT.PUT(item)

PUT_LINE Write to buffer with newline DBMS_OUTPUT.PUT_LINE(item)

NEW_LINE Put end-of-line marker DBMS_OUTPUT.NEW_LINE

GET_LINE Get single line from buffer DBMS_OUTPUT.GET_LINE(line, status)

Get multiple lines from

GET_LINES DBMS_OUTPUT.GET_LINES(lines, numlines)
buffer

ENABLE Enable output DBMS_OUTPUT.ENABLE(buffer_size)

DISABLE Disable output DBMS_OUTPUT.DISABLE

PUT Procedure

Writes data to buffer WITHOUT newline

Multiple PUT calls on same line
Syntax:

DBMS_OUTPUT.PUT(item IN VARCHAR2);
DBMS_OUTPUT.PUT(item IN NUMBER);
DBMS_OUTPUT.PUT(item IN DATE);

Example:

BEGIN
DBMS_OUTPUT.PUT('Hello ');
DBMS_OUTPUT.PUT('World');
DBMS_OUTPUT.NEW_LINE;
-- Output: Hello World
END;
/

PUT_LINE Procedure

Writes data to buffer WITH newline

Most commonly used

Syntax:

DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2);

Example:

BEGIN
DBMS_OUTPUT.PUT_LINE('First line');
DBMS_OUTPUT.PUT_LINE('Second line');
END;
/
-- Output:
-- First line
-- Second line

NEW_LINE Procedure

Puts an end-of-line marker

Used after PUT to complete line

Example:

BEGIN
DBMS_OUTPUT.PUT('Name: ');
DBMS_OUTPUT.PUT('John');
DBMS_OUTPUT.NEW_LINE; -- End of line marker
DBMS_OUTPUT.PUT_LINE('Done');
END;
/

String Concatenation

Use || operator to concatenate

Example:
 DECLARE
name VARCHAR2(50) := 'John';
age NUMBER := 30;
BEGIN
-- Concatenation with ||
DBMS_OUTPUT.PUT_LINE('Name is: ' || name);
DBMS_OUTPUT.PUT_LINE('Age is: ' || age);
DBMS_OUTPUT.PUT_LINE('Info: ' || name || ' is ' || age || ' years old');
END;
/

Complete Example

SET SERVEROUTPUT ON;

DECLARE
a VARCHAR2(20) := 'Sanfoundry';
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
END;
/

-- Output:
-- Sanfoundry
-- PL/SQL procedure successfully completed.

15. SQL Injection

What is SQL Injection?

Security vulnerability in database-driven applications

Attacker inserts malicious SQL code into input fields
Can bypass authentication, access unauthorized data, modify/delete data

Categories of SQL Injection

SQL injection attacks can be classified into 3 main categories :

1. In-band SQL Injection (Classic)

Attacker uses same channel for attack and results

Most common and easy to exploit
Sub-types:
Error-based SQL Injection
Union-based SQL Injection

2. Blind SQL Injection (Inferential)

No direct error messages or data transfer

Attacker asks true/false questions
Sub-types:
Boolean-based Blind SQL Injection
Time-based Blind SQL Injection

3. Out-of-band SQL Injection

Uses different channel for attack and results

Requires certain features enabled on database server
Uses DNS or HTTP requests

SQL Injection Example

 -- Vulnerable code
SELECT * FROM users
WHERE username = 'user_input' AND password = 'pass_input';

-- Malicious input: ' OR '1'='1

SELECT * FROM users
WHERE username = '' OR '1'='1' AND password = '' OR '1'='1';
-- This returns all users!

Protection from SQL Injection

Parameterized Queries (Best Practice)

Parameters preceded by @ symbol

Treats input as data, not executable code

Example:

-- C# with SQL Server

string query = "SELECT * FROM users WHERE username = @username AND password = @password";
SqlCommand cmd = new SqlCommand(query, connection);
[Link]("@username", userInput);
[Link]("@password", passInput);

Other Parameter Symbols:

@ - SQL Server, MySQL (stored procedures)

: - Oracle, PostgreSQL
? - JDBC, PDO (positional parameters)

Prevention Techniques

1. Parameterized Queries / Prepared Statements

-- Prepared statement
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
EXECUTE stmt USING @userid;

2. Stored Procedures

CREATE PROCEDURE GetUser(@userid INT)

AS
BEGIN
SELECT * FROM users WHERE id = @userid;
END;

3. Input Validation

Whitelist allowed characters

Validate data type, length, format
Escape special characters

4. Least Privilege Principle

Database user should have minimum necessary permissions

Don't use admin account for application

5. Web Application Firewall (WAF)

Filters malicious requests

Provides additional layer of security

Vulnerable vs Safe Code

Vulnerable (String Concatenation) Safe (Parameterized)

"SELECT * FROM users WHERE id = " + userId "SELECT * FROM users WHERE id = @userId"

Direct SQL concatenation Parameters bound separately

 Input executed as code Input treated as data
Vulnerable (String Concatenation) Safe (Parameterized)

16. String Functions

Common SQL String Functions

Function Description Example Result

UPPER(str) Convert to uppercase UPPER('hello') 'HELLO'

LOWER(str) Convert to lowercase LOWER('HELLO') 'hello'

LENGTH(str) String length LENGTH('Hello') 5

SUBSTRING(str,start,len) Extract substring SUBSTRING('Hello',1,3) 'Hel'

CONCAT(str1,str2,...) Concatenate strings CONCAT('Hello',' ','World') 'Hello World'

TRIM(str) Remove leading/trailing spaces TRIM(' Hello ') 'Hello'

LTRIM(str) Remove leading spaces LTRIM(' Hello') 'Hello'

RTRIM(str) Remove trailing spaces RTRIM('Hello ') 'Hello'

REPLACE(str,find,replace) Replace substring REPLACE('Hello','ll','yy') 'Heyyo'

REVERSE(str) Reverse string REVERSE('Hello') 'olleH'

CHARINDEX(find,str) Find position of substring CHARINDEX('ll','Hello') 3

LEFT(str,n) Get n leftmost characters LEFT('Hello',3) 'Hel'

RIGHT(str,n) Get n rightmost characters RIGHT('Hello',3) 'llo'

UPPER Function in PL/SQL

TRUE: UPPER(x) function converts string x to uppercase

Returns uppercase string as output

Examples:

-- In SQL
SELECT UPPER('sanfoundry') FROM dual;
-- Output: SANFOUNDRY

SELECT UPPER(name) FROM employees;

-- Converts all names to uppercase

-- In PL/SQL
DECLARE
text VARCHAR2(50) := 'hello world';
BEGIN
DBMS_OUTPUT.PUT_LINE(UPPER(text));
-- Output: HELLO WORLD
END;
/

String Concatenation

SQL: Use || operator or CONCAT() function

Different in other languages (+ or &)

Examples:
 -- Using || operator
SELECT 'Hello' || ' ' || 'World' FROM dual;
-- Output: Hello World

SELECT first_name || ' ' || last_name AS full_name

FROM employees;

-- Using CONCAT function

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- In PL/SQL
DECLARE
fname VARCHAR2(20) := 'John';
lname VARCHAR2(20) := 'Doe';
BEGIN
DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname);
-- Output: John Doe
END;
/

17. Database Snapshots

What are Database Snapshots?

Read-only, static view of a database (SQL Server feature)

Point-in-time copy of database
Uses sparse files (stores only changes)
Fast to create

Key Points

Read-only: Cannot modify data

Dependent on source: If source database is dropped, snapshot is dropped
Space-efficient: Only stores pages that differ from source
After dropping snapshot, CANNOT be re-created by restoring backup
Must re-create from source database

Creating Database Snapshot

CREATE DATABASE snapshot_name

ON
(
NAME = logical_file_name,
FILENAME = 'path_to_snapshot_file'
)
AS SNAPSHOT OF source_database;

Example:

CREATE DATABASE MyDB_Snapshot_2024

ON
(
NAME = MyDB_Data,
FILENAME = 'C:\Snapshots\MyDB_Snapshot_2024.ss'
)
AS SNAPSHOT OF MyDatabase;

Using Snapshots
 -- Query snapshot
SELECT * FROM snapshot_name.dbo.table_name;

-- Compare with source

SELECT * FROM source_db.dbo.table_name
EXCEPT
SELECT * FROM snapshot_name.dbo.table_name;

Restoring from Snapshot

-- Restore source database from snapshot

RESTORE DATABASE source_database
FROM DATABASE_SNAPSHOT = 'snapshot_name';

Dropping Snapshot

DROP DATABASE snapshot_name;

Important: Once dropped, it's gone forever. You must create a new snapshot from the source database.

Quick Reference Summary

SQL Command Classification

DDL DML DCL TCL

CREATE SELECT GRANT COMMIT

ALTER INSERT REVOKE ROLLBACK

DROP UPDATE SAVEPOINT

TRUNCATE DELETE

Operator Precedence (High to Low)

1. Parentheses ()
2. Multiplication * , Division /
3. Addition + , Subtraction -
4. Comparison = , <> , < , > , <= , >=
5. NOT
6. AND
7. OR

Join Result Set Sizes (Relative)

1. INNER JOIN - Smallest

2. LEFT/RIGHT JOIN - Medium
3. FULL OUTER JOIN - Large
4. CROSS JOIN - Largest (Cartesian product)

Common Mistakes to Avoid

Using == instead of = for comparison

Using ELSEIF instead of ELSIF in PL/SQL
Trying to modify constants
Using double quotes for strings (use single quotes)
Forgetting GROUP BY for non-aggregated columns with aggregate functions
Trying to add DEFAULT constraint at table level
Using WHERE with aggregate functions (use HAVING)
String concatenation with && or & (use AND )
Thinking NULL equals NULL (it doesn't!)

Best Practices
 Use parameterized queries to prevent SQL injection
Always use SET SERVEROUTPUT ON for DBMS_OUTPUT
Use EXISTS for collection methods before accessing elements
Use ELSIF (not ELSEIF) in PL/SQL
Use meaningful aliases for tables and columns
Always specify column names in INSERT statements
Use CONTINUE to skip iterations, EXIT to leave loops
Declare constants with CONSTANT keyword and initialize them
Use single quotes for string literals in PL/SQL
Use COALESCE to handle NULL values

Practice Questions Summary

True/False Quick Reference

1. WHERE restricts groups? FALSE (HAVING does)

2. MAX and MIN together? TRUE
3. GROUP BY with aggregates? TRUE
4. Change constant value? FALSE
5. DEFAULT table-level? FALSE (column-level only)
6. UPPER converts to uppercase? TRUE
7. DATE with BETWEEN? TRUE
8. Snapshots restored from backup? FALSE

Multiple Choice Answers

LEFT JOIN representation: Table1

COALESCE returns: First non-NULL expression
Alias is used for: Renaming in statement
DROP category: DDL
Stored procedure parameters: Any number
SQL injection categories: 3
Self join purpose: Compare same column in same table
Collection method for checking existence: EXISTS
OR operator: Any condition true
Percent sign represents: Zero, one, or multiple characters
CONTINUE statement: Skips rest of loop iteration
View also called: Virtual table
Packages divided into: 2 parts
== operator: NOT valid
Full join result size: Largest (among standard joins)

End of Notes
These comprehensive notes cover all SQL and PL/SQL topics from your practice questions, including related concepts and best practices. Use this as your study
guide and quick reference material.