Nested Query :
A nested query (subquery) is a query written inside another SQL query. The inner query
executes first, and its result is used by the outer query.
Nested queries are used to perform complex operations such as filtering data based on results
from another query.
🔹 Example:
Find employees whose salary is greater than the average salary:
SELECT name, salary
FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);
🔹 Explanation:
The inner query SELECT AVG(salary) calculates the average salary.
The outer query selects employees whose salary is greater than this
value.
++++++++++++++++++++++++++++++++++++++++++
✍️Matching a Pattern from a Table in DBMS
🔷 Introduction
In a Database Management System (DBMS), retrieving specific data based on certain conditions
is very important. Sometimes, instead of exact matches, we need to find data that follows a
particular pattern. This is known as pattern matching.
Pattern matching is commonly used in SQL using the LIKE operator along with special wildcard
characters. It allows users to search for partial matches in a table.
🔷 What is Pattern Matching?
Pattern matching refers to the process of searching for data in a table where the values match a
specific format or structure rather than an exact value.
For example:
Finding names that start with "A"
Searching emails that contain "@[Link]"
Retrieving phone numbers with a specific pattern
🔷 LIKE Operator
The LIKE operator is used in SQL to perform pattern matching.
🔹 Syntax:
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;
🔷 Wildcard Characters
Wildcard characters are special symbols used with the LIKE operator to
define patterns.
1. % (Percent Sign)
Represents zero, one, or multiple characters
2. _ (Underscore)
Represents exactly one character
🔷 Examples of Pattern Matching
✅ Example 1: Names Starting with 'A'
SELECT name
FROM Employee
WHERE name LIKE 'A%';
📌 Explanation:
A% means the name starts with 'A' and can have any number of
characters after it
Matches: Aman, Ankit, Ajay
Does not match: Ravi
✅ Example 2: Names Ending with 'n'
SELECT name
FROM Employee
WHERE name LIKE '%n';
📌 Explanation:
%n means any name that ends with 'n'
Matches: Mohan, Karan
✅ Example 3:
SELECT name
FROM Employee
WHERE name LIKE '%it%';
📌 Explanation:
%it% finds names containing "it" anywhere
Matches: Ritesh, Amit
✅ Example 4: Fixed Length Pattern
SELECT name
FROM Employee
WHERE name LIKE 'A_ _ _';
📌 Explanation:
_ represents one character
A___ means names starting with 'A' and having exactly 4 letters
Matches: Aman, Ajay
✅ Example 5: Email Pattern Matching
SELECT email
FROM Users
WHERE email LIKE '%@[Link]';
📌 Explanation:
Finds all Gmail users
✅ Example 6: Phone Number Pattern
SELECT phone
FROM Customers
WHERE phone LIKE '98%';
📌 Explanation:
Finds phone numbers starting with 98
🔷 Combining Wildcards
Wildcards can be combined for complex patterns.
SELECT name
FROM Employee
WHERE name LIKE '_a%';
📌 Explanation:
Second letter must be 'a'
Matches: Raj, Karan
🔷 NOT LIKE Operator
To exclude patterns, we use NOT LIKE.
SELECT name
FROM Employee
WHERE name NOT LIKE 'A%';
📌 Explanation:
Retrieves names that do not start with 'A'
🔷 Case Sensitivity
Pattern matching may be case-sensitive or case-insensitive, depending
on the DBMS:
In MySQL, LIKE is usually case-insensitive
In Oracle, it is case-sensitive
To handle case sensitivity, functions like UPPER() or LOWER() are used:
SELECT name
FROM Employee
WHERE LOWER(name) LIKE 'a%';
🔷 Pattern Matching with ESCAPE Character
Sometimes wildcard characters (%, _) appear as actual data. In such
cases, we use the ESCAPE keyword.
SELECT name
FROM Products
WHERE name LIKE '50\%%' ESCAPE '\';
📌 Explanation:
Searches for values starting with "50%"
🔷 Use of Pattern Matching in Real Life
Pattern matching is widely used in real-world applications:
1. Search Systems
o Searching names or keywords
2. Email Filtering
o Finding emails from specific domains
3. Data Validation
o Checking formats of phone numbers or IDs
4. E-commerce
o Searching products by partial names
5. Login Systems
o Validating usernames or patterns
🔷 Advantages of Pattern Matching
Helps in flexible data retrieval
Easy to use with simple syntax
Supports partial matching
Useful in large databases
🔷 Limitations
Slower than exact matches
Cannot use indexes efficiently (in some cases)
Complex patterns may reduce performance
🔷 Difference Between LIKE and = Operator
Feature LIKE =
Matching Type Pattern-based Exact match
Wildcards Supported Not supported
Flexibility High Low
🔷 Advanced Pattern Matching (Brief)
Some DBMS support advanced pattern matching using:
REGEXP (Regular Expressions)
Example:
SELECT name
FROM Employee
WHERE name REGEXP '^A';
📌 Matches names starting with 'A'
+++++++++++++++++++++++++++++++++++++++++++++++++
✍️%TYPE and %ROWTYPE in PL/SQL
🔷 Introduction
In PL/SQL, %TYPE and %ROWTYPE are attribute-based declarations used to define variables
based on the structure of database tables. Instead of manually specifying data types, these
attributes allow variables to inherit the data type of a column or an entire row.
They help in improving data consistency, maintainability, and flexibility of database
programs.
🔷 %TYPE Attribute
🔹 Definition
%TYPE is used to declare a variable with the same data type as a column in a table or another
variable.
🔹 Syntax
variable_name table_name.column_name%TYPE;
🔹 Example
DECLARE
v_name [Link]%TYPE;
v_salary [Link]%TYPE;
BEGIN
SELECT name, salary
INTO v_name, v_salary
FROM Employee
WHERE emp_id = 101;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
🔹 Explanation
v_name gets the same data type as [Link]
v_salary gets the same data type as [Link]
Values are fetched using SELECT INTO
Output is displayed using DBMS_OUTPUT.PUT_LINE
🔹 Advantages of %TYPE
Prevents data type mismatch errors
Automatically updates if column type changes
Reduces maintenance effort
Improves code readability
🔷 %ROWTYPE Attribute
🔹 Definition
%ROWTYPE is used to declare a variable that can store a complete row of a table or cursor.
🔹 Syntax
variable_name table_name%ROWTYPE;
🔹 Example
DECLARE
v_emp Employee%ROWTYPE;
BEGIN
SELECT *
INTO v_emp
FROM Employee
WHERE emp_id = 101;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp.emp_id);
DBMS_OUTPUT.PUT_LINE('Name: ' || v_emp.name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_emp.salary);
DBMS_OUTPUT.PUT_LINE('Dept: ' || v_emp.dept_id);
END;
🔹 Explanation
v_emp stores the entire row
All columns are accessed using dot notation (v_emp.column_name)
Simplifies handling of multiple fields
🔹 Advantages of %ROWTYPE
Stores complete row in one variable
Reduces number of variable declarations
Automatically reflects table structure changes
Useful in loops and cursors
🔷 Difference Between %TYPE and %ROWTYPE
Feature %TYPE %ROWTYPE
Purpose Single column Entire row
Data Stored One value Multiple values
Memory Usage Less More
Usage Specific fields Full records
Practical Comparison
Without Using Attributes:
DECLARE
v_id NUMBER;
v_name VARCHAR2(20);
v_salary NUMBER;
BEGIN
SELECT emp_id, name, salary
INTO v_id, v_name, v_salary
FROM Employee
WHERE emp_id = 101;
END;
Using %ROWTYPE:
DECLARE
v_emp Employee%ROWTYPE;
BEGIN
SELECT *
INTO v_emp
FROM Employee
WHERE emp_id = 101;
END;
🔷 When to Use
Use %TYPE when working with individual column values
Use %ROWTYPE when working with complete rows or records
🔷 Key Points
Both are PL/SQL attributes, not data types
Ensure consistency with database schema
Reduce chances of runtime errors
Improve maintainability of code
🔷 Conclusion
%TYPE and %ROWTYPE are powerful features of PL/SQL that simplify variable declaration and
ensure consistency with database structures. %TYPE is ideal for handling single column values,
while %ROWTYPE is useful for managing complete rows efficiently. Their usage enhances code
reliability and reduces maintenance effort in database applications.