IT405 Unit 3 DBMS Notes | SQL, Query Processing, Query Optimization

Q: What is Selection Operation?

Selection Operation retrieves rows satisfying a specified condition and is represented by Sigma (σ).

What You Will Learn in DBMS Unit 3?

DBMS Unit 3 database query execution ka practical part explain karta hai. Is unit me hum samajhte hain ki SQL query user se DBMS tak kaise jaati hai, DBMS us query ko process kaise karta hai, aur same query ko fast execute karne ke liye optimization kaise hoti hai.

RGPV previous year papers me Unit 3 se mostly Query Processing, Query Optimization, Cost Based Optimization, Join Evaluation, Relational Algebra conversion aur Evaluation Plan jaise topics repeat hue hain.

UNIT 3 ROADMAP ↓ SQL Statements ↓ Query Processing ↓ Query Optimization ↓ Selection Operation ↓ Sorting ↓ Join Evaluation ↓ Expression Transformation ↓ Cost Estimation ↓ Evaluation Plan ↓ Oracle and DB2 Case Study

SQL Data Definition Statements

SQL Data Definition Statements database structure ko create, modify aur manage karne ke liye use kiye jaate hain.

Ye statements database schema define karte hain aur tables, views aur indexes jaise objects ko create ya modify karte hain.

Definition

Data Definition Statements are SQL commands used to define and manage database structures.

Purpose of Data Definition Statements

Create database objects.
Modify existing database objects.
Delete database objects.
Define schema structure.
Maintain database organization.

Major DDL Commands

SQL Data Definition │ ├── CREATE ├── ALTER ├── DROP ├── TRUNCATE └── RENAME

1. CREATE Statement

CREATE statement ka use nayi table, database ya view create karne ke liye hota hai.

Syntax

CREATE TABLE Student ( Roll_No INT, Name VARCHAR(50), Branch VARCHAR(20) );

Example

CREATE TABLE Student ( Roll_No INT PRIMARY KEY, Name VARCHAR(50), Branch VARCHAR(20), Marks INT );

Output Structure

Roll_No	Name	Branch	Marks

2. ALTER Statement

ALTER statement existing table structure ko modify karne ke liye use hoti hai.

Add New Column

ALTER TABLE Student ADD Email VARCHAR(100);

Modify Column

ALTER TABLE Student MODIFY Name VARCHAR(100);

Drop Column

ALTER TABLE Student DROP COLUMN Email;

3. DROP Statement

DROP statement database object ko permanently delete kar deta hai.

Syntax

DROP TABLE Student;

Effect

Table Structure ❌ Deleted ---------------- Table Data ❌ Deleted

4. TRUNCATE Statement

TRUNCATE statement table ke saare records delete karta hai lekin table structure ko preserve rakhta hai.

Syntax

TRUNCATE TABLE Student;

Before TRUNCATE

Roll_No	Name
101	Shivam
102	Rahul

After TRUNCATE

Roll_No	Name

5. RENAME Statement

RENAME statement existing table ka naam change karne ke liye use hoti hai.

Syntax

RENAME TABLE Student TO Student_Record;

DDL Execution Flow

Create Table ↓ Modify Table ↓ Insert Data ↓ Rename Table ↓ Delete Table

DDL vs DML

DDL	DML
Structure Related	Data Related
CREATE	INSERT
ALTER	UPDATE
DROP	DELETE
Schema Changes	Record Changes

Advantages of DDL

Easy database design.
Simple schema creation.
Supports database maintenance.
Flexible structure modification.
Fast object management.

Disadvantages of DDL

Wrong DROP can delete entire data.
Schema changes may affect applications.
Requires careful execution.

Real Life Example

Suppose ek college database create karna hai.

CREATE Student ↓ ALTER Student ↓ Add New Columns ↓ TRUNCATE Records ↓ DROP Table

Memory Trick

CREATE ↓ New Object ------------------- ALTER ↓ Modify Object ------------------- DROP ↓ Delete Object ------------------- TRUNCATE ↓ Delete Records ------------------- RENAME ↓ Change Name

Exam Shortcut

Command	Purpose
CREATE	Create Table
ALTER	Modify Structure
DROP	Delete Object
TRUNCATE	Delete Records
RENAME	Change Name

RGPV Exam Keywords

SQL Data Definition
DDL Commands
CREATE
ALTER
DROP
TRUNCATE
RENAME
Schema Definition
Database Structure

Most Expected Questions

2 Marks

What is DDL?
Define CREATE statement.
Define ALTER statement.
What is TRUNCATE?

5 Marks

Explain CREATE statement with example.
Explain ALTER statement.
Differentiate DROP and TRUNCATE.

7 Marks

Explain SQL Data Definition Statements.
Discuss DDL commands with examples.
Explain CREATE, ALTER and DROP statements.

14 Marks

Explain SQL Data Definition Statements in detail with syntax, examples, advantages and applications.

SQL Data Manipulation Statements

SQL Data Manipulation Statements database ke andar stored data ko insert, retrieve, modify aur delete karne ke liye use kiye jaate hain.

DDL database structure ko manage karti hai, jabki DML actual records par operation perform karti hai.

Definition

Data Manipulation Statements are SQL commands used to access and manipulate data stored in database tables.

Purpose of DML Statements

New records insert karna.
Existing records retrieve karna.
Data update karna.
Records delete karna.
Database information maintain karna.

Major DML Statements

SQL Data Manipulation │ ├── INSERT ├── SELECT ├── UPDATE ├── DELETE ├── WHERE ├── ORDER BY └── GROUP BY

Student Table Example

Roll_No	Name	Branch	Marks

1. INSERT Statement

INSERT statement new records add karne ke liye use hoti hai.

Syntax

INSERT INTO Student VALUES (101,'Shivam','CSE',85);

Example

INSERT INTO Student VALUES (102,'Rahul','IT',78);

Output

Roll_No	Name	Branch	Marks
101	Shivam	CSE	85
102	Rahul	IT	78

2. SELECT Statement

SELECT statement database se data retrieve karne ke liye use hoti hai.

Syntax

SELECT * FROM Student;

Meaning

Student table ke sabhi records display honge.

Specific Columns

SELECT Name, Marks FROM Student;

3. WHERE Clause

WHERE clause specific condition apply karne ke liye use hota hai.

Example

SELECT * FROM Student WHERE Branch='CSE';

Result

Sirf CSE branch ke students display honge.

Comparison Operators

Operator	Meaning
=	Equal To
>	Greater Than
<	Less Than
>=	Greater Than Equal
<=	Less Than Equal
!=	Not Equal

4. UPDATE Statement

UPDATE statement existing records modify karne ke liye use hoti hai.

Syntax

UPDATE Student SET Marks = 90 WHERE Roll_No = 101;

Before Update

Roll_No	Name	Marks
101	Shivam	85

After Update

Roll_No	Name	Marks
101	Shivam	90

5. DELETE Statement

DELETE statement records remove karne ke liye use hoti hai.

Syntax

DELETE FROM Student WHERE Roll_No = 102;

Result

Specified record table se remove ho jayega.

6. ORDER BY Clause

ORDER BY records ko ascending ya descending order me arrange karta hai.

Ascending Order

SELECT * FROM Student ORDER BY Marks ASC;

Descending Order

SELECT * FROM Student ORDER BY Marks DESC;

7. GROUP BY Clause

GROUP BY same values wale records ko groups me organize karta hai.

Example

SELECT Branch, COUNT(*) FROM Student GROUP BY Branch;

Output

Branch	Students
CSE	25
IT	20

SQL Execution Flow

Table ↓ SELECT ↓ WHERE ↓ GROUP BY ↓ ORDER BY ↓ Result

INSERT vs UPDATE vs DELETE

Command	Purpose
INSERT	Add Record
UPDATE	Modify Record
DELETE	Remove Record

Advantages of DML

Easy data management.
Fast record retrieval.
Supports modifications.
Flexible querying.
Efficient database operations.

Memory Trick

INSERT ↓ Add ---------------- SELECT ↓ View ---------------- UPDATE ↓ Modify ---------------- DELETE ↓ Remove ---------------- ORDER BY ↓ Sort ---------------- GROUP BY ↓ Group

RGPV Exam Keywords

SQL Data Manipulation
DML
INSERT
SELECT
UPDATE
DELETE
WHERE Clause
ORDER BY
GROUP BY
Data Retrieval

Most Expected Questions

2 Marks

What is DML?
Define INSERT statement.
Define SELECT statement.
What is GROUP BY?

5 Marks

Explain SELECT statement with example.
Explain UPDATE statement.
Explain ORDER BY clause.

7 Marks

Explain SQL Data Manipulation Statements.
Discuss INSERT, SELECT, UPDATE and DELETE.
Explain WHERE, ORDER BY and GROUP BY.

14 Marks

Explain SQL Data Manipulation Statements in detail with syntax, examples, applications and advantages.

Query Optimization

Query Optimization DBMS ka process hai jisme multiple possible execution plans me se sabse efficient plan choose kiya jata hai.

Same SQL query ko execute karne ke kai methods ho sakte hain. Query Optimizer un sab plans ka cost evaluate karta hai aur sabse fast aur low-cost plan select karta hai.

RGPV examinations me Query Optimization Unit 3 ka sabse important topic hai aur lagbhag har 2-3 saal me 7 marks ya 14 marks me poocha jata hai.

Definition

Query Optimization is the process of selecting the most efficient execution strategy for executing a database query.

Need of Query Optimization

Execution time reduce karna.
Disk I/O minimize karna.
CPU utilization reduce karna.
Memory usage optimize karna.
Overall database performance improve karna.

Basic Idea

Same Query ↓ Multiple Plans ↓ Cost Comparison ↓ Best Plan Selected

Example

Suppose Student table me 10 lakh records hain.

SELECT * FROM Student WHERE Roll_No = 101;

DBMS query ko full table scan ya index search dono se execute kar sakta hai.

Optimizer index search choose karega kyunki wo faster hai.

Query Optimization Process

SQL Query ↓ Relational Algebra ↓ Alternative Plans ↓ Cost Estimation ↓ Best Plan ↓ Execution

Objectives of Query Optimization

Minimum execution cost.
Minimum response time.
Maximum throughput.
Efficient resource utilization.
Fast query execution.

Types of Query Optimization

Query Optimization │ ├── Heuristic Optimization └── Cost-Based Optimization

1. Heuristic Optimization

Heuristic Optimization predefined rules ke basis par query ko optimize karta hai.

Isme exact cost calculate nahi ki jaati.

Common Heuristic Rules

Selection operation ko early perform karo.
Projection operation ko early perform karo.
Cartesian Product avoid karo.
Join order optimize karo.

Example

Original ↓ Join ↓ Selection ------------------- Optimized ↓ Selection ↓ Join

2. Cost-Based Optimization

Cost-Based Optimization execution plans ka estimated cost calculate karta hai.

Jiska cost sabse kam hota hai us plan ko choose kiya jata hai.

Factors Considered

Disk Access Cost
CPU Cost
Memory Cost
Network Cost
Execution Time

Working

Plan A Cost = 500 ------------------ Plan B Cost = 200 ------------------ Optimizer ↓ Choose Plan B

Query Tree Optimization

Query Tree optimization me relational algebra tree ko transform karke better execution plan banaya jata hai.

Example

π Name | σ Marks > 80 | Student

Selection operation ko niche push karne se fewer records process honge aur execution fast hogi.

Optimization Techniques

Optimization Techniques │ ├── Selection Push Down ├── Projection Push Down ├── Join Reordering ├── Index Usage └── Elimination of Redundant Operations

Selection Push Down

Selection ko data source ke paas execute kiya jata hai taaki unnecessary tuples remove ho jaye.

Projection Push Down

Required columns hi process ki jaati hain.

Memory aur processing cost reduce hoti hai.

Join Reordering

Joins ka order change karke execution cost kam ki jaati hai.

Index Based Optimization

Index ka use karke full table scan avoid kiya jata hai.

Without Index

Scan 10,00,000 Rows

With Index

Direct Record Access

Cost Estimation Parameters

Parameter	Description
Disk I/O	Number of Disk Accesses
CPU Cost	Processor Usage
Memory Cost	RAM Consumption
Response Time	Total Execution Time

Advantages of Query Optimization

Improves query performance.
Reduces execution cost.
Efficient resource utilization.
Faster response time.
Better scalability.

Disadvantages

Optimization overhead.
Complex implementation.
Cost estimation may be inaccurate.

Real Life Example

Google, Amazon aur Banking Systems me lakhon records hote hain.

Query Optimization ke bina search aur reporting systems bahut slow ho jaate.

Large Database ↓ Optimizer ↓ Best Plan ↓ Fast Result

Memory Trick

Query ↓ Alternative Plans ↓ Cost Compare ↓ Best Plan ↓ Execute

Exam Shortcut

Optimization Type	Method
Heuristic	Rules Based
Cost Based	Cost Calculation

RGPV Exam Keywords

Query Optimization
Cost-Based Optimization
Heuristic Optimization
Query Tree
Selection Push Down
Projection Push Down
Join Reordering
Index Optimization
Cost Estimation
Execution Plan

Most Expected Questions

2 Marks

Define Query Optimization.
What is Cost-Based Optimization?
What is Heuristic Optimization?
What is Query Tree?

5 Marks

Explain Heuristic Optimization.
Explain Cost-Based Optimization.
Explain Query Tree Optimization.

7 Marks

Discuss Query Optimization techniques.
Differentiate Heuristic and Cost-Based Optimization.
Explain Query Optimization with diagram.

14 Marks

Explain Query Optimization in detail. Discuss Heuristic Optimization, Cost-Based Optimization, Query Tree Optimization and Optimization Techniques with suitable examples.

Query Evaluation Measures

Query Evaluation Measures wo parameters hote hain jinke basis par DBMS kisi query execution plan ki efficiency evaluate karta hai.

Jab optimizer multiple execution plans generate karta hai, tab Query Evaluation Measures ki help se best plan select kiya jata hai.

Unit 3 me Query Evaluation Measures Query Processing aur Query Optimization ka foundation topic hai.

Definition

Query Evaluation Measures are performance parameters used to estimate and compare the cost of different query execution plans.

Need of Query Evaluation

Best execution plan select karna.
Query cost estimate karna.
Performance improve karna.
Execution time reduce karna.
Resource utilization optimize karna.

Major Evaluation Measures

Query Evaluation Measures │ ├── Disk Access Cost ├── CPU Cost ├── Memory Cost ├── Communication Cost └── Response Time

1. Disk Access Cost

Disk Access Cost query execution ka sabse important factor hai.

Disk se data read ya write karna CPU operations se bahut slow hota hai.

Example

Read 1000 Blocks ↓ High Disk Cost ------------------ Read 100 Blocks ↓ Low Disk Cost

Goal

Disk I/O operations ko minimum rakhna.

2. CPU Cost

CPU Cost query execute karne me processor dwara perform kiye gaye operations ko represent karta hai.

Includes

Comparisons
Sorting
Searching
Join Processing

Example

1 Million Comparisons ↓ Higher CPU Cost

3. Memory Cost

Memory Cost query execution ke dauran required RAM amount ko represent karta hai.

Example

Large Join Operation ↓ More Memory Required

Goal

Minimum memory consumption maintain karna.

4. Communication Cost

Distributed databases me network communication cost bhi important hoti hai.

Example

Server A ↓ Network ↓ Server B

Network data transfer cost increase karta hai.

5. Response Time

Response Time query submit karne aur result receive karne ke beech ka total time hota hai.

Formula

Response Time = Query Finish Time - Query Start Time

Query Cost

Query Cost execution ke liye required total resources ka estimation hota hai.

Approximation

Query Cost = Disk Cost + CPU Cost + Memory Cost

Factors Affecting Query Cost

Table Size
Number of Records
Indexes
Join Operations
Selection Conditions
Available Memory

Performance Metrics

Metric	Purpose
Disk I/O	Measure Storage Access
CPU Time	Measure Processing Cost
Memory Usage	Measure RAM Consumption
Response Time	Measure User Wait Time
Throughput	Queries Processed Per Unit Time

Throughput

Throughput ek specific time period me process hui queries ki number ko represent karta hai.

Formula

Throughput = Number of Queries / Time

Example of Plan Comparison

Plan	Disk Cost	CPU Cost	Total Cost
Plan A	500	200	700
Plan B	300	150	450

Optimizer Plan B select karega kyunki uska total cost kam hai.

Role in Query Optimization

Alternative Plans ↓ Cost Estimation ↓ Compare Costs ↓ Select Lowest Cost Plan

Advantages of Query Evaluation Measures

Efficient query execution.
Performance improvement.
Resource optimization.
Better response time.
Improved throughput.

Real Life Example

Suppose Banking Database me 10 million records hain.

Customer account search query execute karte waqt optimizer lowest disk access wale plan ko choose karega.

Multiple Plans ↓ Evaluate Cost ↓ Choose Cheapest Plan ↓ Fast Result

Memory Trick

Disk ↓ Storage Cost ------------------ CPU ↓ Processing Cost ------------------ Memory ↓ RAM Cost ------------------ Response Time ↓ User Waiting Time

Exam Shortcut

Measure	Remember
Disk Cost	Most Important
CPU Cost	Processing Cost
Memory Cost	RAM Usage
Response Time	User Delay
Throughput	Queries per Time

RGPV Exam Keywords

Query Evaluation Measures
Disk Access Cost
CPU Cost
Memory Cost
Communication Cost
Response Time
Throughput
Query Cost
Performance Metrics
Cost Estimation

Most Expected Questions

2 Marks

Define Query Evaluation.
What is Disk Access Cost?
What is Response Time?
What is Throughput?

5 Marks

Explain Query Cost.
Explain Disk Access Cost.
Explain Throughput and Response Time.

7 Marks

Discuss Query Evaluation Measures.
Explain Performance Metrics in DBMS.
Explain Cost Estimation Parameters.

14 Marks

Explain Query Evaluation Measures in detail. Discuss Disk Access Cost, CPU Cost, Memory Cost, Response Time, Throughput and Query Cost with examples.

Selection Operation

Selection Operation query processing ka ek important operation hai jo relation (table) se specific condition satisfy karne wale tuples (rows) ko retrieve karta hai.

Selection operation Relational Algebra me σ (Sigma) symbol se represent kiya jata hai.

Query Optimization me selection operation ko jaldi execute karna performance improve karne ka important technique mana jata hai.

Definition

Selection Operation is a unary relational algebra operation that selects tuples satisfying a given condition.

Symbol

σ Condition (Relation)

Example

Student table me sirf CSE students select karna:

σ Branch='CSE' (Student)

Student Relation

Roll_No	Name	Branch	Marks
101	Shivam	CSE	85
102	Rahul	IT	78
103	Aman	CSE	90

Selection Result

Roll_No	Name	Branch	Marks
101	Shivam	CSE	85
103	Aman	CSE	90

Selection Operation Working

Input Relation ↓ Check Condition ↓ Matching Tuples ↓ Output Relation

Selection Algorithms

Selection Algorithms │ ├── Linear Search ├── Binary Search ├── Primary Index ├── Secondary Index └── Hash Based Search

1. Linear Search

Linear Search me table ke har record ko sequentially check kiya jata hai.

Working

Record 1 ↓ Record 2 ↓ Record 3 ↓ ... ↓ Required Record

Advantages

Simple implementation.
No index required.

Disadvantages

Large tables me slow.
High disk access cost.

2. Binary Search

Binary Search sorted files par use hoti hai.

Har step me search space aadha ho jata hai.

Working

100 Records ↓ 50 ↓ 25 ↓ 12 ↓ 6 ↓ Record Found

Advantages

Fast searching.
Less comparisons.

3. Selection Using Primary Index

Primary Index search directly indexed record tak pahunch jata hai.

Process

Primary Index ↓ Block Address ↓ Required Record

Benefit

Disk accesses significantly reduce ho jaate hain.

4. Selection Using Secondary Index

Secondary Index non-primary attributes par search support karta hai.

Example

Search ↓ Branch = CSE ↓ Secondary Index ↓ Matching Records

5. Hash Based Selection

Hashing technique search key ko hash function ke through specific bucket me map karti hai.

Working

Key ↓ Hash Function ↓ Bucket ↓ Record

Selection Cost Factors

Factor	Effect
File Size	Higher Records → Higher Cost
Index Availability	Lower Cost
Sorting	Supports Binary Search
Memory	Improves Speed

Selection Push Down Optimization

Query Optimization me selection operation ko earliest possible stage par execute kiya jata hai.

Example

Original ↓ Join ↓ Selection ------------------ Optimized ↓ Selection ↓ Join

Isse processing cost reduce ho jaati hai.

Selection vs Projection

Selection	Projection
Select Rows	Select Columns
σ Symbol	π Symbol
Condition Based	Attribute Based

Advantages of Selection Operation

Required data retrieve karta hai.
Processing reduce karta hai.
Optimization improve karta hai.
Query performance enhance karta hai.
Disk accesses reduce karta hai.

Real Life Example

College database me 50,000 students hain.

Sirf CSE students retrieve karne ke liye Selection Operation use kiya jayega.

50,000 Students ↓ Branch = CSE ↓ 8,000 Students

Memory Trick

Selection ↓ Rows ------------------ Projection ↓ Columns ------------------ Index ↓ Fast Search

Exam Shortcut

Method	Speed
Linear Search	Slow
Binary Search	Fast
Primary Index	Very Fast
Hash Search	Fastest

RGPV Exam Keywords

Selection Operation
Sigma (σ)
Linear Search
Binary Search
Primary Index
Secondary Index
Hash Based Search
Selection Algorithm
Query Processing
Selection Push Down

Most Expected Questions

2 Marks

Define Selection Operation.
What is Sigma Operator?
What is Binary Search?
What is Primary Index?

5 Marks

Explain Selection Operation with example.
Explain Linear Search and Binary Search.
Explain Selection using Index.

7 Marks

Discuss Selection Algorithms.
Explain Selection Operation in Query Processing.
Compare different Selection methods.

14 Marks

Explain Selection Operation in detail. Discuss Linear Search, Binary Search, Primary Index, Secondary Index and Hash Based Selection with suitable examples.

Sorting Operation

Sorting Operation DBMS me records ko ascending ya descending order me arrange karne ke liye use ki jati hai.

Query Processing aur Query Optimization me sorting ka bahut important role hota hai kyunki Join, Group By, Order By aur Duplicate Elimination jaise operations sorting par depend karte hain.

RGPV examinations me Sorting techniques aur External Sorting frequently pooche jaate hain.

Definition

Sorting is the process of arranging records in a particular order based on one or more attributes.

Need of Sorting

Data ko organized form me display karna.
Binary Search support karna.
Join operations fast banana.
GROUP BY execution improve karna.
ORDER BY queries execute karna.

Example

Before Sorting

Roll_No	Name	Marks
103	Aman	90
101	Shivam	85
102	Rahul	78

After Sorting (Ascending)

Roll_No	Name	Marks
101	Shivam	85
102	Rahul	78
103	Aman	90

Types of Sorting

Sorting │ ├── Internal Sorting └── External Sorting

1. Internal Sorting

Internal Sorting tab use hoti hai jab poora data main memory (RAM) me fit ho jata hai.

Examples

Bubble Sort
Insertion Sort
Selection Sort
Quick Sort
Merge Sort

Working

Data ↓ RAM ↓ Sort ↓ Output

Advantages

Fast execution.
Low disk access.
Simple implementation.

2. External Sorting

External Sorting tab use hoti hai jab data size RAM se bada ho.

Large databases me External Sorting sabse commonly use hoti hai.

Working

Large File ↓ Divide into Chunks ↓ Sort Each Chunk ↓ Store on Disk ↓ Merge ↓ Final Sorted File

External Merge Sort

External Merge Sort DBMS me sabse popular external sorting technique hai.

Phase 1: Run Generation

Large File ↓ Small Blocks ↓ Sort Each Block ↓ Sorted Runs

Phase 2: Merge Phase

Run 1 Run 2 Run 3 ↓ Merge ↓ Sorted Output

Two-Way Merge Sort

Is technique me do sorted runs ko merge kiya jata hai.

Example

Run A 10 20 30 ---------------- Run B 15 25 35 ---------------- Merged 10 15 20 25 30 35

Multi-Way Merge Sort

Multi-Way Merge me ek hi time par multiple runs merge kiye jaate hain.

Benefit

Less passes required.
Faster execution.
Efficient for huge databases.

Sorting Cost Factors

Factor	Impact
File Size	Higher Size → Higher Cost
Available Memory	More RAM → Faster Sorting
Disk Access	Major Cost Factor
Number of Merge Passes	Affects Performance

Sorting in Query Processing

SQL Query ↓ ORDER BY ↓ Sorting ↓ Result

Sorting for Join Processing

Sort-Merge Join me pehle tables sort ki jaati hain aur phir join perform hota hai.

Process

Table A ↓ Sort ---------------- Table B ↓ Sort ---------------- Merge ↓ Join Result

Internal vs External Sorting

Internal Sorting	External Sorting
Uses RAM	Uses Disk
Small Data	Large Data
Fast	Comparatively Slow
Low I/O Cost	High I/O Cost

Advantages of Sorting

Faster searching.
Efficient joins.
Improved query performance.
Supports grouping operations.
Better data organization.

Real Life Example

Suppose university database me 5 lakh students hain.

Marks ke according merit list generate karne ke liye sorting use ki jaati hai.

5,00,000 Records ↓ Sort by Marks ↓ Merit List

Memory Trick

Internal ↓ RAM ------------------ External ↓ Disk ------------------ Merge Sort ↓ Large Files

Exam Shortcut

Technique	Used For
Internal Sort	Small Data
External Sort	Large Data
Two-Way Merge	Two Runs
Multi-Way Merge	Multiple Runs

RGPV Exam Keywords

Sorting Operation
Internal Sorting
External Sorting
Merge Sort
External Merge Sort
Two-Way Merge
Multi-Way Merge
ORDER BY
Sort-Merge Join
Disk I/O Cost

Most Expected Questions

2 Marks

Define Sorting.
What is Internal Sorting?
What is External Sorting?
What is Merge Sort?

5 Marks

Explain Internal Sorting.
Explain External Sorting.
Explain Merge Sort.

7 Marks

Differentiate Internal and External Sorting.
Explain External Merge Sort.
Discuss Sorting in Query Processing.

14 Marks

Explain Sorting Operation in DBMS. Discuss Internal Sorting, External Sorting, External Merge Sort, Two-Way Merge and Multi-Way Merge with suitable examples.

Join Evaluation

Join Evaluation DBMS me Join operations ko efficiently execute karne ki process hai. Jab do ya adhik relations ko combine karna hota hai, tab DBMS different join algorithms use karta hai.

Query Processing aur Query Optimization me Join Evaluation sabse important topics me se ek hai kyunki join operations generally sabse expensive database operations hote hain.

RGPV examinations me Join Evaluation, Join Algorithms aur Join Optimization frequently pooche jaate hain.

Definition

Join Evaluation is the process of selecting and executing an efficient algorithm to perform join operations between database relations.

Need of Join Evaluation

Execution cost reduce karna.
Disk I/O minimize karna.
Response time improve karna.
Large database joins efficiently perform karna.
Query optimization support karna.

Example Relations

Student Table

Roll_No	Name	Dept_ID
101	Shivam	D1
102	Rahul	D2

Department Table

Dept_ID	Department
D1	CSE
D2	IT

Join Result

Roll_No	Name	Department
101	Shivam	CSE
102	Rahul	IT

Types of Join Evaluation Algorithms

Join Evaluation │ ├── Nested Loop Join ├── Block Nested Loop Join ├── Indexed Join ├── Sort-Merge Join └── Hash Join

1. Nested Loop Join

Nested Loop Join sabse simple join algorithm hai.

Outer relation ke har tuple ko inner relation ke har tuple ke saath compare kiya jata hai.

Working

For Each Tuple In Table A ↓ Compare With Every Tuple In Table B

Algorithm

FOR each row in A FOR each row in B Compare Join Condition END END

Advantages

Simple implementation.
No sorting required.

Disadvantages

High execution cost.
Slow for large tables.

2. Block Nested Loop Join

Nested Loop Join ka improved version hai.

Single tuples ki jagah blocks process kiye jaate hain.

Working

Block A ↓ Block B ↓ Join

Benefit

Disk accesses significantly reduce ho jaate hain.

3. Indexed Join

Indexed Join me join attribute par index available hota hai.

Process

Tuple ↓ Index Search ↓ Matching Record

Advantages

Fast search.
Low disk access cost.
Efficient for large tables.

4. Sort-Merge Join

Sort-Merge Join me dono relations ko pehle sort kiya jata hai aur phir merge karke join perform kiya jata hai.

Working

Relation A ↓ Sort ------------------ Relation B ↓ Sort ------------------ Merge ↓ Join Result

Advantages

Efficient for large datasets.
Supports ordered data.

Disadvantages

Sorting overhead.
Extra memory required.

Example

A 10 20 30 ------------------ B 20 30 40 ------------------ Merge ↓ 20 30

5. Hash Join

Hash Join me join attribute par hash function apply kiya jata hai.

Working

Relation A ↓ Hash Function ↓ Hash Table ------------------ Relation B ↓ Hash Lookup ↓ Join

Advantages

Very fast execution.
Efficient for equality joins.
Low response time.

Disadvantages

Extra memory required.
Hash collisions possible.

Join Cost Comparison

Join Method	Speed	Cost
Nested Loop Join	Slow	High
Block Nested Loop	Medium	Medium
Indexed Join	Fast	Low
Sort-Merge Join	Fast	Medium
Hash Join	Very Fast	Low

Join Evaluation Process

Join Query ↓ Possible Algorithms ↓ Cost Estimation ↓ Best Algorithm ↓ Execution

Role in Query Optimization

Optimizer different join algorithms evaluate karta hai aur lowest cost wala algorithm select karta hai.

Real Life Example

E-commerce websites me Orders table aur Customers table ko join karna padta hai.

Orders + Customers ↓ Join ↓ Customer Order Report

Large databases me Hash Join aur Indexed Join commonly use kiye jaate hain.

Memory Trick

Nested Loop ↓ Simple ------------------- Indexed Join ↓ Index Based ------------------- Sort-Merge ↓ Sort Then Merge ------------------- Hash Join ↓ Hash Table

Exam Shortcut

Join	Remember
Nested Loop	Simple but Slow
Indexed Join	Uses Index
Sort-Merge	Sort + Merge
Hash Join	Fastest Equality Join

RGPV Exam Keywords

Join Evaluation
Nested Loop Join
Block Nested Loop Join
Indexed Join
Sort-Merge Join
Hash Join
Join Cost
Query Optimization
Join Algorithm
Execution Plan

Most Expected Questions

2 Marks

Define Join Evaluation.
What is Nested Loop Join?
What is Hash Join?
What is Sort-Merge Join?

5 Marks

Explain Indexed Join.
Explain Sort-Merge Join.
Explain Hash Join.

7 Marks

Discuss Join Evaluation Algorithms.
Compare Nested Loop Join and Hash Join.
Explain Join Evaluation with diagram.

14 Marks

Explain Join Evaluation in DBMS. Discuss Nested Loop Join, Indexed Join, Sort-Merge Join and Hash Join with suitable examples.

Expression Transformation

Expression Transformation Query Optimization ka ek important process hai jisme Relational Algebra expressions ko equivalent but more efficient expressions me convert kiya jata hai.

Transformation ka main objective execution cost reduce karna aur query performance improve karna hota hai.

Definition

Expression Transformation is the process of converting a relational algebra expression into an equivalent expression that can be executed more efficiently.

Need of Expression Transformation

Reduce execution cost.
Improve query performance.
Reduce disk access.
Generate efficient execution plans.
Support query optimization.

Basic Concept

Original Query ↓ Relational Algebra ↓ Transformation ↓ Optimized Expression ↓ Execution

Example

Original σ Branch='CSE' (Student ⨝ Department)

Transformed Expression

(σ Branch='CSE' Student) ⨝ Department

Selection operation pehle execute hogi, jisse fewer tuples join honge aur cost reduce hogi.

Transformation Rules

Expression Transformation │ ├── Selection Push Down ├── Projection Push Down ├── Join Reordering ├── Cascade of Selections └── Combination of Operations

1. Selection Push Down

Selection operation ko data source ke nearest execute kiya jata hai.

Rule

σ Condition (R ⨝ S) ↓ (σ Condition R) ⨝ S

2. Projection Push Down

Only required attributes ko process kiya jata hai.

Rule

π Name (R ⨝ S) ↓ π Name (π Name R ⨝ S)

3. Join Reordering

Join sequence ko change karke cost reduce ki ja sakti hai.

Rule

(R ⨝ S) ⨝ T ↓ R ⨝ (S ⨝ T)

4. Cascade of Selections

Multiple conditions ko separate selections me break kiya ja sakta hai.

Example

σ A AND B (R) ↓ σ A (σ B(R))

Benefits of Transformation

Lower query cost.
Reduced I/O operations.
Faster execution.
Efficient joins.
Better optimization.

Estimation of Expression Results

Expression Result Estimation me DBMS estimate karta hai ki query execute hone ke baad approximately kitne tuples output me aayenge.

Ye information optimizer ko best execution plan choose karne me help karti hai.

Definition

Estimation of Expression Results is the process of predicting the size of intermediate and final query results.

Need of Estimation

Cost calculation.
Join optimization.
Memory allocation.
Execution planning.
Performance improvement.

Parameters Used

Parameter	Meaning
n(R)	Number of Tuples
b(R)	Number of Blocks
V(A,R)	Distinct Values of Attribute A
Size(R)	Relation Size

Selection Estimation

Suppose Student table me 10,000 tuples hain.

σ Branch='CSE' (Student)

Agar 5 branches hain to approximately:

10000 / 5 = 2000 Tuples

Projection Estimation

Projection operation tuples ki count change nahi karta.

Sirf columns reduce hote hain.

Example

π Name (Student)

Tuple count same rahegi.

Join Result Estimation

Join result size estimate karna Query Optimization ka important part hai.

Formula

n(R ⨝ S) = n(R) × n(S) -------------------- Max(V(A,R),V(A,S))

Example

Student 1000 Tuples ------------------- Department 100 Tuples ------------------- Join Result Estimated

Role in Query Optimization

Expression ↓ Estimate Result Size ↓ Estimate Cost ↓ Choose Best Plan

Advantages

Efficient execution plans.
Accurate cost estimation.
Improved optimization.
Reduced resource usage.
Better query performance.

Real Life Example

Suppose University Database me 1 million student records hain.

Optimizer pehle estimate karega ki query kitne records return karegi aur phir best plan select karega.

1 Million Records ↓ Estimate Output ↓ Cost Calculation ↓ Best Execution Plan

Memory Trick

Transform ↓ Optimize Query ------------------- Estimate ↓ Predict Output Size ------------------- Optimize ↓ Select Best Plan

Exam Shortcut

Concept	Remember
Transformation	Rewrite Query
Selection Push Down	Filter Early
Projection Push Down	Reduce Columns
Estimation	Predict Output Size
Optimization	Choose Best Plan

RGPV Exam Keywords

Expression Transformation
Selection Push Down
Projection Push Down
Join Reordering
Cascade of Selections
Expression Result Estimation
Query Optimization
Cost Estimation
Intermediate Results
Execution Plan

Most Expected Questions

2 Marks

Define Expression Transformation.
What is Selection Push Down?
What is Join Reordering?
What is Result Estimation?

5 Marks

Explain Expression Transformation.
Explain Selection Push Down.
Explain Result Estimation.

7 Marks

Discuss Transformation Rules in Query Optimization.
Explain Estimation of Expression Results.
Explain Query Optimization using Expression Transformation.

14 Marks

Explain Expression Transformation and Estimation of Expression Results in DBMS. Discuss transformation rules, result estimation techniques and their role in Query Optimization.

Evaluation Plans

Evaluation Plan ek detailed strategy hoti hai jo DBMS ko batati hai ki query ko kaise execute karna hai.

Query Optimization ke baad optimizer sabse efficient Evaluation Plan select karta hai.

Definition

An Evaluation Plan is a sequence of operations used by the DBMS to execute a query efficiently.

Need of Evaluation Plans

Fast query execution.
Efficient resource utilization.
Low execution cost.
Better query performance.
Optimization support.

Evaluation Plan Generation

SQL Query ↓ Parser ↓ Relational Algebra ↓ Optimizer ↓ Evaluation Plan ↓ Execution

Example

SELECT * FROM Student WHERE Branch='CSE' AND Marks > 80;

Possible Plan 1

Full Table Scan ↓ Filter Branch ↓ Filter Marks

Possible Plan 2

Use Index ↓ Direct Search ↓ Result

Optimizer Plan 2 select karega kyunki uski cost kam hai.

Components of Evaluation Plan

Evaluation Plan │ ├── Access Path ├── Selection Method ├── Join Algorithm ├── Sorting Method └── Output Generation

Access Path

Access Path determine karta hai ki records database se kaise retrieve honge.

Sequential Scan
Index Scan
Hash Access

Selection Method

Selection operation execute karne ke liye suitable algorithm choose kiya jata hai.

Join Method

Join evaluation ke liye optimizer join algorithm choose karta hai.

Nested Loop Join
Sort Merge Join
Hash Join
Indexed Join

Sorting Method

ORDER BY aur GROUP BY operations ke liye sorting techniques choose ki jaati hain.

Characteristics of Good Evaluation Plan

Minimum execution time.
Minimum disk access.
Low CPU usage.
Low memory consumption.
High throughput.

Evaluation Plan Selection

Generate Plans ↓ Estimate Cost ↓ Compare Plans ↓ Choose Best Plan ↓ Execute

Case Study of Oracle DBMS

Oracle duniya ka sabse popular commercial Relational Database Management System (RDBMS) hai.

Oracle Corporation dwara develop kiya gaya Oracle Database enterprise applications, banking systems, ERP software aur cloud platforms me extensively use hota hai.

Features of Oracle

High Security.
Transaction Management.
Concurrency Control.
Backup and Recovery.
Distributed Database Support.
Scalability.
High Availability.

Oracle Architecture

User ↓ Oracle Instance ↓ Memory (SGA) ↓ Background Processes ↓ Database Files

Main Components

Component	Function
SGA	Shared Memory Area
PGA	Program Memory
Data Files	Store Data
Redo Log Files	Recovery Support
Control Files	Database Information

Applications of Oracle

Banking Systems
ERP Software
E-Commerce Platforms
Government Databases
Telecommunication Systems

Advantages of Oracle

Highly Secure.
Reliable.
Supports Large Databases.
Excellent Recovery Mechanism.
Enterprise Level Performance.

Case Study of DB2

DB2 IBM dwara develop kiya gaya Relational Database Management System hai.

DB2 large enterprise applications aur mainframe systems me extensively use hota hai.

Features of DB2

High Performance.
Advanced Security.
Parallel Processing.
Scalability.
Data Warehousing Support.
Cloud Integration.

DB2 Architecture

Users ↓ DB2 Engine ↓ Buffer Pool ↓ Database Storage ↓ Tables & Indexes

Applications of DB2

Financial Institutions
Insurance Companies
Government Organizations
Large Enterprises
Business Analytics Systems

Advantages of DB2

High Speed.
Excellent Data Management.
Advanced Security.
Strong Scalability.
Reliable Transaction Processing.

Oracle vs DB2

Oracle	DB2
Developed by Oracle Corporation	Developed by IBM
Very Popular Enterprise DBMS	Mainframe Oriented DBMS
Strong ERP Support	Strong Analytics Support
Excellent Recovery Features	High Performance Processing
Widely Used Globally	Popular in Large Enterprises

Memory Trick

Evaluation Plan ↓ Best Execution Strategy ------------------- Oracle ↓ Oracle Corporation ------------------- DB2 ↓ IBM ------------------- Optimizer ↓ Choose Lowest Cost Plan

Exam Shortcut

Topic	Remember
Evaluation Plan	Execution Strategy
Oracle	Enterprise RDBMS
DB2	IBM Database
Optimizer	Select Best Plan

RGPV Exam Keywords

Evaluation Plan
Execution Strategy
Access Path
Oracle Database
Oracle Architecture
DB2 Database
IBM DB2
Cost Estimation
Query Optimizer
Enterprise DBMS

Most Expected Questions

2 Marks

Define Evaluation Plan.
What is Oracle DBMS?
What is DB2?
What is Access Path?

5 Marks

Explain Evaluation Plan.
Explain Oracle Architecture.
Explain DB2 Features.

7 Marks

Discuss Evaluation Plans in DBMS.
Explain Oracle DBMS with architecture.
Explain DB2 with features and applications.

14 Marks

Explain Evaluation Plans in DBMS and discuss Oracle and DB2 case studies with architecture, features, advantages and applications.

DBMS Unit 3 Important Questions

The following questions are selected from RGPV previous year examination papers, repeated university questions and expected topics for upcoming examinations.

🔥 Top Important 2 Marks Questions

Define Query Processing.

What is Query Optimization?

What is Query Evaluation Plan?

Define Selection Operation.

What is Sort-Merge Join?

What is Hash Join?

Define Cost Estimation.

What is Selection Push Down?

What is Oracle DBMS?

What is IBM DB2?

⭐ Top Important 5 Marks Questions

Explain Query Processing Architecture.

Explain Query Optimization.

Discuss Query Evaluation Measures.

Explain Selection Operation.

Explain External Sorting.

Explain Sort-Merge Join.

Explain Hash Join.

Explain Expression Transformation.

Explain Oracle Architecture.

Explain DB2 Features.

🏆 Top Important 7 Marks Questions

Explain Query Processing with diagram.

Discuss Query Optimization techniques.

Differentiate Heuristic and Cost-Based Optimization.

Explain Selection Algorithms.

Discuss Sorting techniques in DBMS.

Explain Join Evaluation Algorithms.

Explain Estimation of Expression Results.

Explain Evaluation Plans.

Oracle DBMS Architecture and Features.

DB2 Architecture and Applications.

🚀 Most Important 14 Marks Questions

Explain Query Processing in detail with suitable diagram.

Explain Query Optimization techniques and Cost-Based Optimization.

Discuss Selection Operation and Selection Algorithms.

Explain External Sorting and Merge Sort.

Explain Join Evaluation with suitable examples.

Explain Expression Transformation and Result Estimation.

Discuss Evaluation Plans in DBMS.

Explain Oracle and DB2 case studies.

DBMS Unit 3 PYQ Analysis

The following analysis is based on RGPV Previous Year Question Papers (2020, 2022, 2023 and 2025) shared by students. These topics have appeared repeatedly and have a very high probability of appearing again.

Topics Covered

SQL Data Definition

SQL Data Manipulation

Query Processing

Query Optimization

Selection Operation

Sorting

Join Evaluation

Expression Transformation

Cost Estimation

Evaluation Plans

Oracle DBMS

DB2

PYQ Frequency Analysis

Topic	2020	2022	2023	2025	Frequency
Query Processing	✅	✅	✅	✅	★★★★★
Query Optimization	✅	✅	✅	✅	★★★★★
Cost Estimation	❌	✅	✅	✅	★★★★☆
Selection Operation	❌	✅	✅	❌	★★★★☆
Join Evaluation	❌	✅	✅	✅	★★★★★
Expression Transformation	❌	❌	✅	✅	★★★★☆
Oracle / DB2	✅	❌	✅	❌	★★★☆☆

Most Repeated Unit 3 Questions

🔥 Q1. Explain Query Processing with diagram.

Appeared In:

2020
2022
2023
2025

Prediction: ⭐⭐⭐⭐⭐

🔥 Q2. Explain Query Optimization.

Appeared In:

2020
2022
2023
2025

Prediction: ⭐⭐⭐⭐⭐

🔥 Q3. Explain Join Evaluation Algorithms.

Appeared In:

2022
2023
2025

Prediction: ⭐⭐⭐⭐⭐

🔥 Q4. Explain Cost Estimation.

Appeared In:

2022
2023
2025

Prediction: ⭐⭐⭐⭐⭐

🔥 Q5. Explain Expression Transformation.

Appeared In:

2023
2025

Prediction: ⭐⭐⭐⭐☆

2026 Expected Questions

VERY HIGH PROBABILITY 🔥 Query Processing 🔥 Query Optimization 🔥 Join Evaluation 🔥 Cost Estimation 🔥 Evaluation Plans -------------------------------- HIGH PROBABILITY ⭐ Selection Operation ⭐ Expression Transformation ⭐ External Sorting ⭐ Oracle DBMS ⭐ DB2

Actual RGPV Trend

According to recent RGPV examination patterns, Unit 3 is highly focused on Query Processing and Optimization.

TOP REPEATED TOPICS 1. Query Processing 2. Query Optimization 3. Join Evaluation 4. Cost Estimation 5. Evaluation Plans

These topics have appeared repeatedly either as short notes, 7 marks questions or 14 marks long answer questions.

Unit 3 Weightage Analysis

Topic	Importance
Query Processing	★★★★★
Query Optimization	★★★★★
Join Evaluation	★★★★★
Cost Estimation	★★★★★
Evaluation Plans	★★★★★
Selection Operation	★★★★☆
Expression Transformation	★★★★☆
Sorting	★★★★☆
Oracle DBMS	★★★☆☆
DB2	★★★☆☆

2026 Score Booster Topics

✓ Query Processing ✓ Query Optimization ✓ Join Evaluation ✓ Cost Estimation ✓ Evaluation Plans ✓ Selection Operation ✓ Expression Transformation

If you prepare these topics thoroughly, you can cover approximately 75-85% of the expected Unit 3 examination pattern.

Frequently Asked Questions (FAQs)

What is Query Processing?

Query Processing is the process of converting an SQL query into an efficient execution strategy and producing the required result.

What is Query Optimization?

Query Optimization is the process of selecting the most efficient execution plan among multiple alternatives.

What is Query Evaluation?

Query Evaluation is the process of executing a query using a selected evaluation plan.

What is Selection Operation?

Selection Operation retrieves tuples that satisfy a specified condition and is represented by Sigma (σ).

What is Join Evaluation?

Join Evaluation is the process of selecting an efficient algorithm to perform join operations between relations.

What is Cost Estimation?

Cost Estimation predicts the resources required to execute a query and helps the optimizer choose the best plan.

What is Expression Transformation?

Expression Transformation converts a relational algebra expression into an equivalent but more efficient expression.

What is Oracle DBMS?

Oracle is a commercial enterprise-level Relational Database Management System developed by Oracle Corporation.

What is IBM DB2?

DB2 is a Relational Database Management System developed by IBM for enterprise and mainframe environments.

What is an Evaluation Plan?

An Evaluation Plan is a sequence of operations chosen by the optimizer to execute a query efficiently.

DBMS Unit 3 Quick Revision Sheet

QUERY PROCESSING ↓ Parsing ↓ Translation ↓ Optimization ↓ Execution -------------------------------- QUERY OPTIMIZATION ↓ Heuristic Optimization ↓ Cost Based Optimization -------------------------------- QUERY EVALUATION ↓ Disk Cost ↓ CPU Cost ↓ Memory Cost ↓ Response Time -------------------------------- SELECTION OPERATION ↓ σ (Sigma) ↓ Select Rows -------------------------------- SORTING ↓ Internal Sorting ↓ External Sorting ↓ Merge Sort -------------------------------- JOIN EVALUATION ↓ Nested Loop Join ↓ Indexed Join ↓ Sort Merge Join ↓ Hash Join -------------------------------- EXPRESSION TRANSFORMATION ↓ Selection Push Down ↓ Projection Push Down ↓ Join Reordering -------------------------------- RESULT ESTIMATION ↓ Estimate Output Size ↓ Estimate Query Cost -------------------------------- EVALUATION PLAN ↓ Best Execution Strategy -------------------------------- ORACLE ↓ Oracle Corporation ↓ Enterprise RDBMS -------------------------------- DB2 ↓ IBM ↓ Enterprise Database

Last Minute Exam Revision

Topic	Priority
Query Processing	★★★★★
Query Optimization	★★★★★
Join Evaluation	★★★★★
Cost Estimation	★★★★★
Evaluation Plans	★★★★★
Selection Operation	★★★★☆
Expression Transformation	★★★★☆
Sorting	★★★★☆
Oracle DBMS	★★★☆☆
DB2	★★★☆☆

Most Important Definitions for Exam

Query Processing ↓ Convert Query Into Result -------------------------------- Query Optimization ↓ Choose Best Plan -------------------------------- Selection Operation ↓ Select Rows -------------------------------- Sorting ↓ Arrange Records -------------------------------- Join Evaluation ↓ Execute Join Efficiently -------------------------------- Expression Transformation ↓ Rewrite Query -------------------------------- Cost Estimation ↓ Predict Query Cost -------------------------------- Evaluation Plan ↓ Execution Strategy -------------------------------- Oracle ↓ Enterprise Database -------------------------------- DB2 ↓ IBM Database

Related DBMS Units

DBMS Unit 1 DBMS Unit 2 DBMS Unit 4 DBMS Unit 5

Conclusion

DBMS Unit 3 focuses on how database queries are processed, optimized and executed efficiently. This unit introduces students to Query Processing, Query Optimization, Selection Operations, Sorting, Join Evaluation, Expression Transformation, Result Estimation and Evaluation Plans.

These concepts are used internally by modern database systems to provide fast query execution and efficient resource utilization. Understanding Unit 3 is essential for database developers, software engineers and backend developers.

🏆 UNIT 3 SCORE BOOSTER Must Prepare: ✓ Query Processing ✓ Query Optimization ✓ Join Evaluation ✓ Cost Estimation ✓ Evaluation Plans ✓ Selection Operation ✓ Expression Transformation These topics cover most of the repeatedly asked RGPV examination questions.

DBMS Unit 3 Notes

Query Processing, Query Optimization, Evaluation Plans, Oracle and DB2

Detailed Notes

Important Questions

PYQ Analysis

DBMS Unit 3 Syllabus Topics

What You Will Learn in DBMS Unit 3?

SQL Data Definition Statements

Definition

Purpose of Data Definition Statements

Major DDL Commands

1. CREATE Statement

Syntax

Example

Output Structure

2. ALTER Statement

Add New Column

Modify Column

Drop Column

3. DROP Statement

Syntax

Effect

4. TRUNCATE Statement

Syntax

Before TRUNCATE

After TRUNCATE

5. RENAME Statement

Syntax

DDL Execution Flow

DDL vs DML

Advantages of DDL

Disadvantages of DDL

Real Life Example

Memory Trick

Exam Shortcut

RGPV Exam Keywords

Most Expected Questions

2 Marks

5 Marks

7 Marks

14 Marks

SQL Data Manipulation Statements

Definition

Purpose of DML Statements

Major DML Statements

Student Table Example

1. INSERT Statement

Syntax

Example

Output

2. SELECT Statement

Syntax

Meaning

Specific Columns

3. WHERE Clause

Example

Result

Comparison Operators

4. UPDATE Statement

Syntax

Before Update

After Update

5. DELETE Statement

Syntax

Result

6. ORDER BY Clause

Ascending Order

Descending Order

7. GROUP BY Clause

Example

Output

SQL Execution Flow

INSERT vs UPDATE vs DELETE

Advantages of DML

Memory Trick

RGPV Exam Keywords

Most Expected Questions

2 Marks

5 Marks

7 Marks