2025

DATABASE MANAGEMENT SYSTEMS

(DBMS) LAB RECORD

FACULTY
SURESH
suresh.mentor@gmail.com
 INDEX

Exp. Page.
Date Name of the Experiment Sign
No No
Creation, altering and dropping of tables and inserting
1 rows into a table (use constraints while creating tables) 1
examples using SELECT command.
Queries (along with sub Queries) using ANY, ALL, IN,
EXISTS, NOTEXISTS, UNION, INTERSET,
2 Constraints. Example:- Select the roll number and 5
name of the student who secured fourth rank in the
class.
Queries using Aggregate functions (COUNT, SUM,
3 AVG, MAX and MIN), GROUP BY, HAVING and 9
Creation and dropping of Views.
Queries using Conversion functions (to_char,
to_number and to_date), string functions
(Concatenation, lpad, rpad, ltrim, rtrim, lower, upper,
4 initcap, length, substr and instr), date functions 14
(Sysdate, next_day, add_months, last_day,
months_between, least, greatest, trunc, round,
to_char, to_date).
i. Create a simple PL/SQL program which includes
declaration section,
executable section and exception –Handling section
(Ex. Student marks can be
selected from the table and printed for those who
5 18
secured first class and an
exception can be raised if no records were found)
ii. Insert data into student table and use COMMIT,
ROLLBACK and
SAVEPOINT in PL/SQL block.
Develop a program that includes the features
NESTED IF, CASE and CASE expression. The
6 21
program can be extended using the NULLIF and
COALESCE functions.
Program development using WHILE LOOPS,
numeric FOR LOOPS, nested loops using ERROR
7 24
Handling, BUILT –IN Exceptions, USE defined
Exceptions, RAISEAPPLICATION ERROR.
Programs development using creation of
8 procedures, passing parameters IN and OUT of 28
PROCEDURES.
Program development using creation of stored
9 functions, invoke functions in SQL Statements and 30
write complex functions.
Develop programs using features parameters in a
10 CURSOR, FOR UPDATE CURSOR, WHERE 34
CURRENT of clause and CURSOR variables.
Develop Programs using BEFORE and AFTER
11 Triggers, Row and Statement Triggers and 37
INSTEAD OF Triggers.
Create a table and perform the search operation on
12 40
table using indexing and non-indexing techniques.
 Write a Java program that connects to a database
13 43
using JDBC.

Write a Java program to connect to a database

14 47
using JDBC and insert values into it.

Write a Java program to connect to a database

15 51
using JDBC and delete values from it.
Experiment 1: Creation, Altering, and Dropping of
Tables and Inserting Rows into a Table
AIM
Creation, altering, and dropping of tables and inserting rows into a table (use
constraints while creating tables) - examples using SELECT command.

THEORY

A relational database consists of tables that store data in rows and columns. SQL
(Structured Query Language) provides various commands to create, modify, and
manage these tables. The creation, altering, and dropping of tables are
fundamental operations in database management, ensuring data is structured
efficiently.

Importance of Table Operations in Databases

• Data Organization: Well-structured tables improve query performance and
maintainability.
• Data Integrity: Constraints enforce rules to ensure valid and accurate data.
• Scalability: Modifying table structures allows databases to grow and adapt to
changing requirements.
• Security and Optimization: Proper indexing and constraints enhance
security and optimize data retrieval.

Common SQL Commands Used in Table Management

1. CREATE TABLE - Defines a new table with specified columns and

constraints.
2. ALTER TABLE - Modifies an existing table by adding, modifying, or deleting
columns.
3. DROP TABLE - Permanently removes a table and its data.
4. INSERT INTO - Adds new records into a table.
5. SELECT - Retrieves data from tables based on specified conditions.
6. UPDATE - Modifies existing records in a table.
7. DELETE - Removes specific records from a table.

Constraints in Table Creation

Constraints are rules enforced on data columns to maintain integrity and prevent
invalid data entries. The most common constraints include:

• PRIMARY KEY: Ensures each record in a table has a unique identifier.

• FOREIGN KEY: Establishes a relationship between two tables, enforcing
referential integrity.
• UNIQUE: Ensures that all values in a column are distinct.
• NOT NULL: Prevents a column from having NULL values.
• CHECK: Enforces a specific condition on column values.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 1

 • DEFAULT: Assigns a default value to a column if no value is provided during
insertion.

PROCEDURE

1. Creating the Department Table

CREATE TABLE Department (

DeptID INT PRIMARY KEY,
DeptName VARCHAR(50) UNIQUE NOT NULL
);

2. Creating the Student Table with Constraints

CREATE TABLE Student (

RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK (Age >= 18),
DepartmentID INT,
Marks INT DEFAULT 0,
Email VARCHAR(100) UNIQUE,
FOREIGN KEY (DepartmentID) REFERENCES Department(DeptID)
);

3. Inserting Data into the Tables

INSERT INTO Department (DeptID, DeptName) VALUES

(1, 'Computer Science'),
(2, 'Electronics'),
(3, 'Mechanical');

INSERT INTO Student (RollNo, Name, Age, DepartmentID, Marks, Email) VALUES
(101, 'Alice', 20, 1, 85, 'alice@example.com'),
(102, 'Bob', 22, 2, 90, 'bob@example.com'),
(103, 'Charlie', 19, 1, 75, 'charlie@example.com');

4. Displaying Table Structure

DESC Department;
DESC Student;

Output:

Table: Department
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| DeptID | INT | NO | PRI | NULL | |
| DeptName | VARCHAR(50) | NO | UNI | NULL | |
+----------+-------------+------+-----+---------+-------+

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 2

 Table: Student
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| RollNo | INT | NO | PRI | NULL | |
| Name | VARCHAR(50) | NO | | NULL | |
| Age | INT | NO | | NULL | |
| DepartmentID | INT | YES | MUL | NULL | |
| Marks | INT | YES | | 0 | |
| Email | VARCHAR(100)| YES | UNI | NULL | |
+--------------+-------------+------+-----+---------+-------+

5. Displaying Tables After Data Insertion

SELECT * FROM Department;

SELECT * FROM Student;

Output:

Table: Department
+--------+------------------+
| DeptID | DeptName |
+--------+------------------+
| 1 | Computer Science |
| 2 | Electronics |
| 3 | Mechanical |
+--------+------------------+

Table: Student
+--------+--------+-----+--------------+-------+------------------+
| RollNo | Name | Age | DepartmentID | Marks | Email |
+--------+--------+-----+--------------+-------+------------------+
| 101 | Alice | 20 | 1 | 85 | alice@example.com |
| 102 | Bob | 22 | 2 | 90 | bob@example.com |
| 103 | Charlie| 19 | 1 | 75 | charlie@example.com |
+--------+--------+-----+--------------+-------+------------------+
INSERT INTO Department (DeptID, DeptName) VALUES
(1, 'Computer Science'),
(2, 'Electronics'),
(3, 'Mechanical');

INSERT INTO Student (RollNo, Name, Age, DepartmentID, Marks, Email) VALUES
(101, 'Alice', 20, 1, 85, 'alice@example.com'),
(102, 'Bob', 22, 2, 90, 'bob@example.com'),
(103, 'Charlie', 19, 1, 75, 'charlie@example.com');

4. Altering the Table (Adding a Phone Number Column)

ALTER TABLE Student ADD PhoneNumber VARCHAR(15);

5. Updating Data in the Table

UPDATE Student SET PhoneNumber = '9876543210' WHERE RollNo = 101;

UPDATE Student SET PhoneNumber = '8765432109' WHERE RollNo = 102;

6. Selecting Data from the Table

SELECT * FROM Student;

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 3

Output:

RollNo | Name | Age | DepartmentID | Marks | Email | PhoneNumber

--------+--------+-----+--------------+-------+------------------+---------
----
101 | Alice | 20 | 1 | 85 | alice@example.com | 9876543210
102 | Bob | 22 | 2 | 90 | bob@example.com | 8765432109
103 | Charlie| 19 | 1 | 75 | charlie@example.com | NULL

7. Deleting a Record

DELETE FROM Student WHERE RollNo = 103;

8. Dropping the Tables

DROP TABLE Student;

DROP TABLE Department;

RESULT
The experiment successfully demonstrates creating, altering, inserting, selecting,
updating, and deleting data in SQL tables while enforcing constraints like PRIMARY
KEY, FOREIGN KEY, UNIQUE, and CHECK to maintain data integrity.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 4

Experiment 2: Queries (along with Subqueries) using
ANY, ALL, IN, EXISTS, NOT EXISTS, UNION,
INTERSECT, Constraints
AIM

Queries (along with subqueries) using ANY, ALL, IN, EXISTS, NOT EXISTS, UNION,
INTERSECT, Constraints. Example: Select the roll number and name of the student
who secured fourth rank in the class.

THEORY

SQL provides powerful querying capabilities that allow data retrieval based on specific
conditions. Queries are essential for interacting with databases efficiently and
extracting meaningful insights from structured data. This experiment covers advanced
SQL querying techniques using subqueries, set operations, and constraints.

Importance of Advanced SQL Queries

• Efficient Data Retrieval: Helps extract specific data quickly using optimized
queries.
• Data Integrity: Ensures constraints like PRIMARY KEY and FOREIGN KEY
maintain relationships.
• Structured Data Manipulation: Enables filtering, sorting, and aggregating
data based on conditions.
• Enhancing Performance: Using EXISTS, IN, and indexing optimizes query
execution.

Explanation of SQL Operators Used

• ANY & ALL: These are used with comparison operators to compare a value
with a list of values returned by a subquery.
o ANY: Returns true if any value in the subquery matches.
o ALL: Returns true if all values in the subquery match the condition.
• IN & EXISTS: Used for checking membership in a list or subquery.
o IN: Checks if a value exists within a specified set of values.
o EXISTS: Returns true if a subquery contains any rows.
o NOT EXISTS: Ensures records do not exist in a given dataset.
• UNION & INTERSECT: Used to combine results from multiple queries.
o UNION: Combines results from two SELECT queries, removing
duplicates.
o INTERSECT: Returns only the common rows from both queries.
• Constraints in Queries:
o PRIMARY KEY: Ensures uniqueness of each row in a table.
o FOREIGN KEY: Maintains referential integrity between tables.
o CHECK: Enforces specific conditions on column values.
o UNIQUE: Ensures no duplicate values exist in a column.

These SQL features are widely used in real-world applications such as:

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 5

 • Banking Systems: Fetching customer transaction history.
• E-Commerce: Filtering products based on availability and price range.
• Healthcare: Querying patient records based on diagnosis or medication
history.

Advanced queries ensure efficient data processing and provide meaningful insights
from relational databases. SQL provides powerful querying capabilities that allow data
retrieval based on specific conditions. The following SQL clauses and operators are
commonly used for advanced querying:

• ANY & ALL: Used with comparison operators to compare a value with a set of
values returned by a subquery.
• IN & EXISTS: Filters records based on lists or subqueries.
• NOT EXISTS: Identifies records that do not exist in another table.
• UNION & INTERSECT: Combines or filters results from multiple queries.
• Constraints: Ensure data integrity, such as PRIMARY KEY, FOREIGN KEY,
and CHECK constraints.

PROCEDURE

1. Creating the Student Table

CREATE TABLE Student (

RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK (Age >= 18),
Marks INT,
Rank INT
);

2. Inserting Data into the Table

INSERT INTO Student (RollNo, Name, Age, Marks, Rank) VALUES

(101, 'Alice', 20, 85, 2),
(102, 'Bob', 22, 90, 1),
(103, 'Charlie', 19, 75, 3),
(104, 'David', 21, 70, 4),
(105, 'Emma', 20, 65, 5);

3. Displaying Table Structure

DESC Student;

Output:

+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| RollNo | INT | NO | PRI | NULL | |
| Name | VARCHAR(50)| NO | | NULL | |
| Age | INT | NO | | NULL | |
| Marks | INT | YES | | NULL | |
| Rank | INT | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 6

4. Displaying Data After Insertion

SELECT * FROM Student;

Output:

+--------+--------+-----+-------+------+
| RollNo | Name | Age | Marks | Rank |
+--------+--------+-----+-------+------+
| 101 | Alice | 20 | 85 | 2 |
| 102 | Bob | 22 | 90 | 1 |
| 103 | Charlie| 19 | 75 | 3 |
| 104 | David | 21 | 70 | 4 |
| 105 | Emma | 20 | 65 | 5 |
+--------+--------+-----+-------+------+

5. Selecting the Roll Number and Name of the Student Who Secured
Fourth Rank

SELECT RollNo, Name FROM Student WHERE Rank = 4;

Output:

+--------+--------+
| RollNo | Name |
+--------+--------+
| 104 | David |
+--------+--------+

6. Using IN and EXISTS

SELECT Name FROM Student WHERE Rank IN (1, 2, 3);

Output:

+--------+
| Name |
+--------+
| Bob |
| Alice |
| Charlie|
+--------+
SELECT Name FROM Student S WHERE EXISTS (
SELECT 1 FROM Student M WHERE M.RollNo = S.RollNo AND M.Marks > 80
);

Output:

+--------+
| Name |
+--------+
| Alice |
| Bob |
+--------+

7. Using UNION and INTERSECT

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 7

SELECT Name FROM Student WHERE Rank < 4
UNION
SELECT Name FROM Student WHERE Age > 19;

Output:

+--------+
| Name |
+--------+
| Alice |
| Bob |
| Charlie|
| David |
+--------+
SELECT Name FROM Student WHERE Rank < 3
INTERSECT
SELECT Name FROM Student WHERE Age > 18;

Output:

+--------+
| Name |
+--------+
| Alice |
| Bob |
+--------+

RESULT
The experiment successfully demonstrates the use of subqueries, constraints, and set
operations such as ANY, ALL, IN, EXISTS, NOT EXISTS, UNION, and INTERSECT in
SQL.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 8

Experiment 3: Queries using Aggregate Functions
(COUNT, SUM, AVG, MAX, and MIN), GROUP BY,
HAVING, and Creation and Dropping of Views.
AIM

Queries using Aggregate Functions (COUNT, SUM, AVG, MAX, and MIN), GROUP
BY, HAVING, and Creation and Dropping of Views.

THEORY

Aggregate functions in SQL perform calculations on a set of values and return a single
summarized result. These functions are useful in analyzing large datasets and
extracting meaningful insights.

Importance of Aggregate Functions in Databases

• Data Summarization: Helps in generating summary statistics from large

datasets.
• Efficient Data Analysis: Used in business intelligence and reporting.
• Filtering and Grouping: Enables data classification using GROUP BY and
HAVING.
• Improved Performance: Reduces computation time by processing data at
the database level.

Common Aggregate Functions

1. COUNT() - Returns the number of rows.

2. SUM() - Returns the total sum of a column.
3. AVG() - Returns the average value of a column.
4. MAX() - Returns the maximum value in a column.
5. MIN() - Returns the minimum value in a column.

GROUP BY and HAVING Clause

• GROUP BY groups rows with the same values in specified columns.

• HAVING filters aggregated results based on a condition.

Views in SQL
A view is a virtual table based on a SELECT query. It provides a way to store query
results without physically storing data, improving security and performance.

• Creating a View: Allows simplified data access.

• Dropping a View: Removes the stored query representation without affecting
base tables. SQL provides powerful querying capabilities that allow data
retrieval based on specific conditions. Queries are essential for interacting with
databases efficiently and extracting meaningful insights from structured data.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 9

 This experiment covers advanced SQL querying techniques using subqueries,
set operations, and constraints.

Importance of Advanced SQL Queries

• Efficient Data Retrieval: Helps extract specific data quickly using optimized
queries.
• Data Integrity: Ensures constraints like PRIMARY KEY and FOREIGN KEY
maintain relationships.
• Structured Data Manipulation: Enables filtering, sorting, and aggregating
data based on conditions.
• Enhancing Performance: Using EXISTS, IN, and indexing optimizes query
execution.

Explanation of SQL Operators Used

• ANY & ALL: These are used with comparison operators to compare a value
with a list of values returned by a subquery.
o ANY: Returns true if any value in the subquery matches.
o ALL: Returns true if all values in the subquery match the condition.
• IN & EXISTS: Used for checking membership in a list or subquery.
o IN: Checks if a value exists within a specified set of values.
o EXISTS: Returns true if a subquery contains any rows.
o NOT EXISTS: Ensures records do not exist in a given dataset.
• UNION & INTERSECT: Used to combine results from multiple queries.
o UNION: Combines results from two SELECT queries, removing
duplicates.
o INTERSECT: Returns only the common rows from both queries.
• Constraints in Queries:
o PRIMARY KEY: Ensures uniqueness of each row in a table.
o FOREIGN KEY: Maintains referential integrity between tables.
o CHECK: Enforces specific conditions on column values.
o UNIQUE: Ensures no duplicate values exist in a column.

These SQL features are widely used in real-world applications such as:

• Banking Systems: Fetching customer transaction history.

• E-Commerce: Filtering products based on availability and price range.
• Healthcare: Querying patient records based on diagnosis or medication
history.

Advanced queries ensure efficient data processing and provide meaningful insights
from relational databases. SQL provides powerful querying capabilities that allow data
retrieval based on specific conditions. The following SQL clauses and operators are
commonly used for advanced querying:

• ANY & ALL: Used with comparison operators to compare a value with a set of
values returned by a subquery.
• IN & EXISTS: Filters records based on lists or subqueries.
• NOT EXISTS: Identifies records that do not exist in another table.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 10

 • UNION & INTERSECT: Combines or filters results from multiple queries.
• Constraints: Ensure data integrity, such as PRIMARY KEY, FOREIGN KEY,
and CHECK constraints.

PROCEDURE

1. Creating the Student Table

CREATE TABLE Student (

RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK (Age >= 18),
Marks INT,
Rank INT
);

2. Inserting Data into the Table

INSERT INTO Student (RollNo, Name, Age, Marks, Rank) VALUES

(101, 'Alice', 20, 85, 2),
(102, 'Bob', 22, 90, 1),
(103, 'Charlie', 19, 75, 3),
(104, 'David', 21, 70, 4),
(105, 'Emma', 20, 65, 5);

3. Displaying Table Structure

DESC Student;

Output:

+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| RollNo | INT | NO | PRI | NULL | |
| Name | VARCHAR(50)| NO | | NULL | |
| Age | INT | NO | | NULL | |
| Marks | INT | YES | | NULL | |
| Rank | INT | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

4. Displaying Data After Insertion

SELECT * FROM Student;

Output:

+--------+--------+-----+-------+------+
| RollNo | Name | Age | Marks | Rank |
+--------+--------+-----+-------+------+
| 101 | Alice | 20 | 85 | 2 |
| 102 | Bob | 22 | 90 | 1 |
| 103 | Charlie| 19 | 75 | 3 |
| 104 | David | 21 | 70 | 4 |
| 105 | Emma | 20 | 65 | 5 |
+--------+--------+-----+-------+------+

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 11

5. Selecting the Roll Number and Name of the Student Who Secured
Fourth Rank

SELECT RollNo, Name FROM Student WHERE Rank = 4;

Output:

+--------+--------+
| RollNo | Name |
+--------+--------+
| 104 | David |
+--------+--------+

6. Using IN and EXISTS

SELECT Name FROM Student WHERE Rank IN (1, 2, 3);

Output:

+--------+
| Name |
+--------+
| Bob |
| Alice |
| Charlie|
+--------+
SELECT Name FROM Student S WHERE EXISTS (
SELECT 1 FROM Student M WHERE M.RollNo = S.RollNo AND M.Marks > 80
);

Output:

+--------+
| Name |
+--------+
| Alice |
| Bob |
+--------+

7. Using UNION and INTERSECT

SELECT Name FROM Student WHERE Rank < 4

UNION
SELECT Name FROM Student WHERE Age > 19;

Output:

+--------+
| Name |
+--------+
| Alice |
| Bob |
| Charlie|
| David |
+--------+
SELECT Name FROM Student WHERE Rank < 3

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 12

INTERSECT
SELECT Name FROM Student WHERE Age > 18;

Output:

+--------+
| Name |
+--------+
| Alice |
| Bob |
+--------+

RESULT

The experiment successfully demonstrates the use of subqueries, constraints, and set
operations such as ANY, ALL, IN, EXISTS, NOT EXISTS, UNION, and INTERSECT in
SQL.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 13

Experiment 4: Queries Using Conversion, String, and
Date Functions in SQL
AIM
Queries using Conversion Functions (TO_CHAR, TO_NUMBER, TO_DATE), String
Functions (Concatenation, LPAD, RPAD, LTRIM, RTRIM, LOWER, UPPER,
INITCAP, LENGTH, SUBSTR, INSTR), Date Functions (SYSDATE, NEXT_DAY,
ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, LEAST, GREATEST, TRUNC,
ROUND, TO_CHAR, TO_DATE).

THEORY

SQL provides a wide range of built-in functions to manipulate data effectively. These
functions can be categorized into three main types: conversion functions, string
functions, and date functions. These functions play a vital role in transforming,
formatting, and retrieving meaningful information from database tables.

Importance of SQL Functions

• Data Formatting: Conversion functions help transform data types to

required formats.
• String Manipulation: String functions enable modification and formatting
of textual data.
• Date and Time Processing: Date functions facilitate operations on date-
related fields for scheduling, analytics, and reporting.
• Data Consistency and Accuracy: Ensures data is stored and presented in
the correct format.

Conversion Functions in SQL

1. TO_CHAR() - Converts a number or date into a string.
2. TO_NUMBER() - Converts a character string into a numeric format.
3. TO_DATE() - Converts a string into a DATE data type.

String Functions in SQL

1. Concatenation (||) - Joins two strings.

2. LPAD & RPAD - Pads a string to the left or right.
3. LTRIM & RTRIM - Removes spaces or characters from left or right.
4. LOWER, UPPER, INITCAP - Changes text case.
5. LENGTH() - Returns the length of a string.
6. SUBSTR() - Extracts a substring from a given string.
7. INSTR() - Finds the position of a substring within a string.

Date Functions in SQL

1. SYSDATE - Returns the current system date and time.
2. NEXT_DAY() - Finds the next occurrence of a specific weekday.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 14

 3. ADD_MONTHS() - Adds a specified number of months to a date.
4. LAST_DAY() - Returns the last day of the month for a given date.
5. MONTHS_BETWEEN() - Calculates the difference in months between two
dates.
6. LEAST() & GREATEST() - Returns the smallest or largest value from a list.
7. TRUNC() & ROUND() - Truncates or rounds a date to a specific format.

Practical Applications
• Financial Systems: Formatting monetary values using TO_CHAR().
• Employee Databases: Extracting initials from names using SUBSTR().
• Logistics & Scheduling: Calculating future dates using ADD_MONTHS().
• E-commerce: Formatting product descriptions using concatenation.

PROCEDURE
1. Creating the Employee Table

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Salary DECIMAL(10,2),
JoiningDate DATE
);

2. Inserting Data into the Table

INSERT INTO Employee (EmpID, Name, Salary, JoiningDate) VALUES

(1, 'Alice', 50000.50, TO_DATE('2023-05-10', 'YYYY-MM-DD')),
(2, 'Bob', 62000.75, TO_DATE('2022-07-20', 'YYYY-MM-DD')),
(3, 'Charlie', 75000.00, TO_DATE('2021-12-15', 'YYYY-MM-DD'));

3. Displaying Table Structure

DESC Employee;

Output:

+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| EmpID | INT | NO | PRI | NULL | |
| Name | VARCHAR(50) | NO | | NULL | |
| Salary | DECIMAL(10,2)| YES | | NULL | |
| JoiningDate| DATE | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+

4. Displaying Data After Insertion

SELECT * FROM Employee;

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 15

Output:

+-------+---------+----------+-------------+
| EmpID | Name | Salary | JoiningDate |
+-------+---------+----------+-------------+
| 1 | Alice | 50000.50 | 10-MAY-2023 |
| 2 | Bob | 62000.75 | 20-JUL-2022 |
| 3 | Charlie | 75000.00 | 15-DEC-2021 |
+-------+---------+----------+-------------+

5. Using Conversion Functions

SELECT Name, TO_CHAR(Salary, '$99,999.99') AS FormattedSalary FROM Employee;

Output:

+---------+----------------+
| Name | FormattedSalary|
+---------+----------------+
| Alice | $50,000.50 |
| Bob | $62,000.75 |
| Charlie | $75,000.00 |
+---------+----------------+

6. Using String Functions

SELECT Name, INITCAP(Name) AS CapitalizedName, LENGTH(Name) AS NameLength

FROM Employee;

Output:

+---------+----------------+------------+
| Name | CapitalizedName| NameLength |
+---------+----------------+------------+
| Alice | Alice | 5 |
| Bob | Bob | 3 |
| Charlie | Charlie | 7 |
+---------+----------------+------------+

7. Using Date Functions

SELECT Name, ADD_MONTHS(JoiningDate, 6) AS SixMonthsLater FROM Employee;

Output:

+---------+----------------+
| Name | SixMonthsLater |
+---------+----------------+
| Alice | 10-NOV-2023 |
| Bob | 20-JAN-2023 |
| Charlie | 15-JUN-2022 |
+---------+----------------+

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 16

RESULT
The experiment successfully demonstrates the use of conversion, string, and date
functions in SQL through practical queries.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 17

Experiment 5: PL/SQL Program with Declaration,
Execution, and Exception Handling
AIM
Create a simple PL/SQL program which includes a declaration section, executable
section, and exception-handling section. Additionally, insert data into the student
table and use COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL block.

THEORY

PL/SQL (Procedural Language/Structured Query Language) is an extension of SQL

that allows for procedural programming, including variables, loops, and exception
handling. It enhances SQL by introducing features such as control structures,
exception handling, and procedural logic.

Importance of PL/SQL
• Error Handling: Allows for structured exception handling.
• Better Performance: Reduces network traffic by processing SQL statements
in a single block.
• Code Reusability: Enables modular programming using procedures and
functions.
• Transaction Management: Supports COMMIT, ROLLBACK, and
SAVEPOINT to ensure data integrity.

Exception Handling in PL/SQL

PL/SQL provides various types of exceptions:

• Predefined Exceptions: Handle common runtime errors (e.g.,

NO_DATA_FOUND, ZERO_DIVIDE).
• User-defined Exceptions: Custom exceptions raised using RAISE
statements.
• RAISE_APPLICATION_ERROR: Generates application-specific error
messages.

PROCEDURE
1. Creating the Student Table

CREATE TABLE Student (

RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Marks INT CHECK (Marks BETWEEN 0 AND 100)
);

2. Inserting Data into the Table

INSERT INTO Student (RollNo, Name, Marks) VALUES

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 18

(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 40);

3. Displaying Table Structure

DESC Student;

Output:

+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| RollNo | INT | NO | PRI | NULL | |
| Name | VARCHAR(50)| NO | | NULL | |
| Marks | INT | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

4. Displaying Data After Insertion

SELECT * FROM Student;

Output:

+--------+--------+-------+
| RollNo | Name | Marks |
+--------+--------+-------+
| 1 | Alice | 85 |
| 2 | Bob | 92 |
| 3 | Charlie| 40 |
+--------+--------+-------+

5. Writing a PL/SQL Block with Exception Handling

DECLARE
v_name Student.Name%TYPE;
v_marks Student.Marks%TYPE;
BEGIN
SELECT Name, Marks INTO v_name, v_marks FROM Student WHERE RollNo = 4;
DBMS_OUTPUT.PUT_LINE('Student: ' || v_name || ' Marks: ' || v_marks);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: No record found.');
END;
/

Output:

Error: No record found.

6. Using COMMIT, ROLLBACK, and SAVEPOINT

BEGIN
INSERT INTO Student (RollNo, Name, Marks) VALUES (4, 'David', 88);
SAVEPOINT save1;

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 19

 INSERT INTO Student (RollNo, Name, Marks) VALUES (5, 'Emma', 76);
ROLLBACK TO save1; -- This will undo Emma's insertion but keep David's.

COMMIT;
END;
/

Output:

David inserted successfully.

Emma's record rolled back.
Changes committed.

RESULT
The experiment successfully demonstrates PL/SQL program structure, exception
handling, and transaction control using COMMIT, ROLLBACK, and SAVEPOINT.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 20

Experiment 6: PL/SQL Control Structures - NESTED IF,
CASE, and CASE Expressions
AIM
Develop a program that includes the features NESTED IF, CASE, and CASE
expression. The program can be extended using the NULLIF and COALESCE
functions.

THEORY

PL/SQL provides control structures that allow conditional execution and branching
logic. The most commonly used control structures are:

• NESTED IF: Used for multiple conditional checks within an IF-ELSE block.
• CASE Statement: An alternative to IF-ELSE, providing a more readable
structure.
• CASE Expression: A single-line conditional expression used within queries.
• NULLIF Function: Returns NULL if two values are equal; otherwise, returns
the first value.
• COALESCE Function: Returns the first non-null value from a list.

Importance of PL/SQL Control Structures

• Decision-Making: Helps execute different blocks of code based on
conditions.
• Simplifies Query Logic: CASE expressions make SQL queries more
readable.
• Handles NULL Values: Functions like NULLIF and COALESCE manage
missing or invalid data.
• Enhances Code Efficiency: Reduces redundant checks using structured
conditions.

PROCEDURE

1. Creating the Employee Table

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Salary INT,
Experience INT
);

2. Inserting Data into the Table

INSERT INTO Employee (EmpID, Name, Salary, Experience) VALUES

(1, 'Alice', 50000, 5),
(2, 'Bob', 70000, 7),
(3, 'Charlie', 65000, NULL);

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 21

3. Displaying Table Structure

DESC Employee;

Output:

+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| EmpID | INT | NO | PRI | NULL | |
| Name | VARCHAR(50) | NO | | NULL | |
| Salary | INT | YES | | NULL | |
| Experience | INT | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+

4. Using NESTED IF in PL/SQL

DECLARE
v_salary INT;
v_bonus INT;
BEGIN
SELECT Salary INTO v_salary FROM Employee WHERE EmpID = 1;

IF v_salary > 60000 THEN

v_bonus := 5000;
ELSIF v_salary BETWEEN 40000 AND 60000 THEN
v_bonus := 3000;
ELSE
v_bonus := 1000;
END IF;

DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);

END;
/

Output:

Bonus: 3000

5. Using CASE Statement

SELECT Name, Salary,

CASE
WHEN Salary > 60000 THEN 'High Salary'
WHEN Salary BETWEEN 40000 AND 60000 THEN 'Medium Salary'
ELSE 'Low Salary'
END AS SalaryCategory
FROM Employee;

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 22

Output:

+---------+--------+---------------+
| Name | Salary | SalaryCategory|
+---------+--------+---------------+
| Alice | 50000 | Medium Salary |
| Bob | 70000 | High Salary |
| Charlie | 65000 | High Salary |
+---------+--------+---------------+

6. Using NULLIF and COALESCE

SELECT Name, NULLIF(Experience, 7) AS NullIfExample, COALESCE(Experience, 0)

AS CoalesceExample
FROM Employee;

Output:

+---------+--------------+----------------+
| Name | NullIfExample | CoalesceExample|
+---------+--------------+----------------+
| Alice | 5 | 5 |
| Bob | NULL | 7 |
| Charlie | NULL | 0 |
+---------+--------------+----------------+

RESULT

The experiment successfully demonstrates the use of NESTED IF, CASE, CASE
expressions, NULLIF, and COALESCE functions in PL/SQL.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 23

Experiment 7: PL/SQL Loops and Exception Handling
AIM

Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops
using ERROR Handling, BUILT-IN Exceptions, USER-DEFINED Exceptions,
RAISE_APPLICATION_ERROR.

THEORY
Loops in PL/SQL allow repetitive execution of a block of statements based on a
condition. Exception handling ensures that runtime errors are managed gracefully
without interrupting the execution flow.

Why Use Loops in PL/SQL?

Loops are essential in PL/SQL programming as they allow the execution of repetitive
tasks without manually writing multiple statements. They are commonly used for:

• Processing Multiple Records: Loops can iterate over query results,

updating or analyzing each record.
• Automating Calculations: Repeated mathematical operations, such as
interest calculations, can be handled within loops.
• Generating Reports: Loops extract and format data for output in structured
reports.
• Bulk Data Processing: In combination with cursors, loops can be used to
process large datasets efficiently.

Exception Handling in PL/SQL

Exception handling is crucial in PL/SQL to ensure errors are handled without abruptly
stopping execution. Some key benefits include:

• Prevents Runtime Failures: Allows the program to continue running

despite errors.
• Provides Meaningful Error Messages: Helps users understand and
correct mistakes.
• Ensures Data Integrity: Prevents incomplete transactions and maintains
consistency.

Types of Exceptions in PL/SQL

1. Predefined Exceptions: Built-in exceptions for common errors (e.g.,
NO_DATA_FOUND, ZERO_DIVIDE, TOO_MANY_ROWS).
2. User-Defined Exceptions: Custom exceptions raised using the RAISE
statement.
3. RAISE_APPLICATION_ERROR: Generates application-specific error
messages for better debugging.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 24

Best Practices for Loops and Exception Handling
• Use EXIT Conditions: Ensure loops have a valid exit condition to prevent
infinite loops.
• Optimize Loop Iterations: Minimize redundant calculations inside loops.
• Handle All Possible Exceptions: Cover all scenarios where an error might
occur.
• Use Exception Handling for Database Operations: Prevent partial
transactions and maintain data consistency.

With loops and exception handling, PL/SQL programs can efficiently process data
while managing errors effectively. Loops in PL/SQL allow repetitive execution of a
block of statements based on a condition. Exception handling ensures that runtime
errors are managed gracefully without interrupting the execution flow.

Importance of Loops and Exception Handling

• Automation of Repetitive Tasks: Loops execute a set of instructions

multiple times.
• Efficient Data Processing: Enables batch operations within stored
procedures.
• Error Prevention: Exception handling prevents unexpected program
termination.
• Custom Error Messages: USER-DEFINED Exceptions and
RAISE_APPLICATION_ERROR allow custom error handling.

Types of Loops in PL/SQL

1. WHILE Loop: Executes a block of code as long as a condition is true.

2. FOR Loop: Iterates over a specific range of values.
3. Nested Loops: Loops within loops for handling complex iterations.

PROCEDURE

1. Creating the Employee Table

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Salary INT
);

2. Inserting Data into the Table

INSERT INTO Employee (EmpID, Name, Salary) VALUES

(1, 'Alice', 50000),
(2, 'Bob', 70000),
(3, 'Charlie', 65000);

3. Displaying Table Structure

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 25

DESC Employee;

Output:

+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| EmpID | INT | NO | PRI | NULL | |
| Name | VARCHAR(50) | NO | | NULL | |
| Salary | INT | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

4. Using WHILE Loop in PL/SQL

DECLARE
v_count INT := 1;
BEGIN
WHILE v_count <= 3 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_count);
v_count := v_count + 1;
END LOOP;
END;
/

Output:

Iteration: 1
Iteration: 2
Iteration: 3

5. Using FOR Loop

DECLARE
BEGIN
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || i);
END LOOP;
END;
/

Output:

Employee ID: 1
Employee ID: 2
Employee ID: 3

6. Using Nested Loops

DECLARE
BEGIN
FOR i IN 1..2 LOOP
FOR j IN 1..2 LOOP
DBMS_OUTPUT.PUT_LINE('Outer: ' || i || ', Inner: ' || j);
END LOOP;
END LOOP;
END;
/

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 26

Output:

Outer: 1, Inner: 1
Outer: 1, Inner: 2
Outer: 2, Inner: 1
Outer: 2, Inner: 2

7. Using Exception Handling in PL/SQL

DECLARE
v_salary INT;
BEGIN
SELECT Salary INTO v_salary FROM Employee WHERE EmpID = 10; -- Invalid
ID
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: No record found.');
END;
/

Output:

Error: No record found.

8. Using RAISE_APPLICATION_ERROR

DECLARE
v_salary INT;
e_low_salary EXCEPTION;
BEGIN
SELECT Salary INTO v_salary FROM Employee WHERE EmpID = 1;
IF v_salary < 60000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary is below the required
limit.');
END IF;
END;
/

Output:

ORA-20001: Salary is below the required limit.

RESULT

The experiment successfully demonstrates the use of loops, error handling, and
exception handling in PL/SQL.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 27

Experiment 8: Procedures with Parameters in PL/SQL
AIM

Experiment 8: Programs development using creation of procedures, passing

parameters IN and OUT of PROCEDURES.

THEORY

Procedures in PL/SQL are named blocks of SQL statements that can be stored in the
database and executed whenever needed. They help modularize code, improve
reusability, and enhance performance by reducing redundant operations.

Why Use Procedures in PL/SQL?

• Encapsulation of Logic: Procedures encapsulate business logic inside
reusable code blocks.
• Enhanced Security: Limits access to underlying tables by allowing execution
privileges only.
• Improved Performance: Reduces query execution time by pre-compiling
SQL logic.
• Minimizes Redundancy: Avoids code duplication across multiple
applications.

Features of Stored Procedures

• Can accept parameters to allow dynamic execution.

• Support error handling to manage exceptions and ensure stable execution.
• Allow nested procedures to enhance modularity.
• Can execute DML (INSERT, UPDATE, DELETE) and DDL (CREATE,
ALTER, DROP) statements.
• Improve transaction management by combining multiple SQL statements
into a single unit.

Advantages of Using Parameters in Procedures

1. IN Parameter: Enables the passing of values to a procedure, making it flexible
for different inputs.
2. OUT Parameter: Allows procedures to return values, reducing the need for
additional queries.
3. IN OUT Parameter: Permits modifications of passed values and returning
the updated results.

Real-World Applications of Stored Procedures

• Banking Systems: Automating transaction processing and account updates.

• E-commerce Platforms: Handling order placement, stock management,
and payment processing.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 28

 • Healthcare Databases: Managing patient records and generating reports
dynamically.
• Enterprise Applications: Centralizing business rules for consistency across
applications. Procedures in PL/SQL are named blocks of SQL statements that
can be stored in the database and executed whenever needed. They help
modularize code, improve reusability, and enhance performance by reducing
redundant operations.

Importance of Stored Procedures

• Code Reusability: Eliminates redundant SQL statements by centralizing

logic.
• Improved Security: Access control mechanisms restrict unauthorized users
from executing procedures.
• Optimized Performance: Reduces network traffic by processing SQL
statements on the server side.
• Better Maintainability: Simplifies code updates and debugging.

Types of Parameters in Procedures

1. IN Parameter: Accepts input values (default type).
2. OUT Parameter: Returns values to the calling program.
3. IN OUT Parameter: Accepts and returns values.

PROCEDURE

1. Creating the Employee Table

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Salary INT
);

2. Inserting Data into the Table

INSERT INTO Employee (EmpID, Name, Salary) VALUES

(1, 'Alice', 50000),
(2, 'Bob', 70000),
(3, 'Charlie', 65000);

3. Displaying Table Structure

DESC Employee;

Output:

+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| EmpID | INT | NO | PRI | NULL | |
| Name | VARCHAR(50) | NO | | NULL | |
| Salary | INT | YES | | NULL | |

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 29

+--------+-------------+------+-----+---------+-------+

4. Creating a Procedure with IN and OUT Parameters

CREATE OR REPLACE PROCEDURE GetEmployeeDetails (

p_EmpID IN Employee.EmpID%TYPE,
p_Name OUT Employee.Name%TYPE,
p_Salary OUT Employee.Salary%TYPE
) AS
BEGIN
SELECT Name, Salary INTO p_Name, p_Salary FROM Employee WHERE EmpID =
p_EmpID;
END;
/

5. Calling the Procedure

DECLARE
v_Name Employee.Name%TYPE;
v_Salary Employee.Salary%TYPE;
BEGIN
GetEmployeeDetails(1, v_Name, v_Salary);
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_Name || ' Salary: ' || v_Salary);
END;
/

Output:

Employee: Alice Salary: 50000

RESULT
The experiment successfully demonstrates the creation and execution of stored
procedures in PL/SQL with parameters.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 30

Experiment 9: Stored Functions
AIM

Program development using creation of stored functions, invoking functions in SQL

statements, and writing complex functions.

THEORY

A stored function in PL/SQL is a subprogram that returns a single value and can be
called within SQL statements. Functions enhance modularity and reusability by
performing calculations and returning values based on input parameters.

• Key Features of Functions:

o Accepts parameters and returns a value.
o Can be used within SELECT statements.
o Must include a RETURN statement.
o Stored functions help in data consistency as they allow calculations to be
performed at the database level rather than in the application layer.
o Functions improve performance by reducing the need for repetitive
queries and calculations within application code.
o They promote reusability and modularity, making code maintenance
easier.

Types of Stored Functions in PL/SQL:

1. Scalar Functions: These return a single value, such as a number or string.

2. Table Functions: These return a set of rows, similar to a table.
3. Built-in Functions: Provided by SQL for common operations (e.g.,
LENGTH(), ROUND()).
4. User-defined Functions: Custom functions created for specific calculations
or operations.

Advantages of Using Stored Functions:

• Performance Optimization: Execution occurs at the database level,
reducing network overhead.
• Security: Access to specific data logic can be controlled by granting execution
privileges.
• Code Reusability: Functions can be used in different queries without
rewriting logic.
• Consistency: Ensures uniform data manipulation and validation across
applications.

PROCEDURE

1. Creating the Student Table

CREATE TABLE Student (

RollNo INT PRIMARY KEY,

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 31

 Name VARCHAR(50) NOT NULL,
Marks INT
);

Created Table Structure:

Column | Data Type | Constraints

---------+--------------+------------------------
RollNo | INT | PRIMARY KEY
Name | VARCHAR(50) | NOT NULL
Marks | INT |

2. Inserting Data into the Table

INSERT INTO Student (RollNo, Name, Marks) VALUES

(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 40);

Table with Inserted Data:

RollNo | Name | Marks

--------+--------+-------
1 | Alice | 85
2 | Bob | 92
3 | Charlie| 40

3. Creating a Function to Determine Grade

CREATE OR REPLACE FUNCTION GetGrade (p_Marks IN INT) RETURN VARCHAR2 AS

v_Grade VARCHAR2(2);
BEGIN
IF p_Marks >= 90 THEN
v_Grade := 'A';
ELSIF p_Marks >= 75 THEN
v_Grade := 'B';
ELSE
v_Grade := 'C';
END IF;
RETURN v_Grade;
END;
/

4. Using the Function in a SQL Query

SELECT Name, Marks, GetGrade(Marks) AS Grade FROM Student;

Output:

Name | Marks | Grade

---------+-------+------
Alice | 85 | B
Bob | 92 | A
Charlie | 40 | C

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 32

5. Calling the Function within Another Function

CREATE OR REPLACE FUNCTION GetStudentPerformance (p_Marks IN INT) RETURN

VARCHAR2 AS
v_Performance VARCHAR2(20);
BEGIN
IF p_Marks >= 90 THEN
v_Performance := 'Excellent';
ELSIF p_Marks >= 75 THEN
v_Performance := 'Good';
ELSE
v_Performance := 'Needs Improvement';
END IF;
RETURN v_Performance;
END;
/

6. Using the Performance Function in a Query

SELECT Name, Marks, GetStudentPerformance(Marks) AS Performance FROM

Student;

Output:

Name | Marks | Performance

---------+-------+----------------
Alice | 85 | Good
Bob | 92 | Excellent
Charlie | 40 | Needs Improvement

RESULT
The experiment successfully demonstrates the creation and execution of stored
functions in PL/SQL, allowing SQL queries to invoke functions directly for
calculations and performance analysis.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 33

Experiment 10: Cursors in PL/SQL
AIM

Develop programs using features parameters in a CURSOR, FOR UPDATE CURSOR,

WHERE CURRENT OF clause, and CURSOR variables.

THEORY

A cursor in PL/SQL is a pointer that retrieves rows from a result set one at a time.
Cursors are used to process multiple rows returned by a query in a controlled manner.
They play a crucial role in handling large datasets where operations need to be
performed on each row individually.

Importance of Cursors

In PL/SQL, when queries return multiple rows, cursors allow developers to process
each row sequentially. Unlike simple SELECT statements, which return all rows at
once, cursors enable step-by-step processing, providing greater flexibility and control
over data manipulation.

How Cursors Work in PL/SQL

1. Declaration: The cursor is defined in the declaration section of the PL/SQL
block.
2. Opening the Cursor: The cursor is opened to fetch records from the
database.
3. Fetching Data: Each row is retrieved into PL/SQL variables using the FETCH
statement.
4. Processing Data: The retrieved data can be used within the program logic.
5. Closing the Cursor: The cursor is explicitly closed to free up memory
resources.

Benefits of Using Cursors

• Efficient Row Processing: Cursors allow fetching and processing of one row
at a time, making them useful for handling large datasets.
• Data Integrity: FOR UPDATE cursors prevent concurrent modifications by
locking rows during processing.
• Dynamic Data Handling: Parameterized cursors provide flexibility by
allowing variable inputs in queries.
• Error Handling: Exception handling in cursors prevents issues like
NO_DATA_FOUND or TOO_MANY_ROWS when fetching data.
• Types of Cursors in PL/SQL: A cursor in PL/SQL is a pointer that retrieves
rows from a result set one at a time. Cursors are used to process multiple rows
returned by a query in a controlled manner.
• Types of Cursors in PL/SQL:
1. Implicit Cursors: Automatically created when a SELECT statement is
executed.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 34

 2. Explicit Cursors: Defined by the user for retrieving and processing
multiple rows.
• Important Cursor Features:
1. FOR UPDATE Cursor: Locks the selected rows to prevent concurrent
updates.
2. WHERE CURRENT OF: Updates or deletes the row currently being
processed by the cursor.
3. Parameterized Cursors: Accepts parameters to filter data
dynamically.

PROCEDURE

1. Creating the Employee Table

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Salary INT,
Department VARCHAR(20)
);

Created Table Structure:

Column | Data Type | Constraints

-------------+--------------+------------------------
EmpID | INT | PRIMARY KEY
Name | VARCHAR(50) | NOT NULL
Salary | INT |
Department | VARCHAR(20) |

2. Inserting Data into the Table

INSERT INTO Employee (EmpID, Name, Salary, Department) VALUES

(1, 'Alice', 50000, 'HR'),
(2, 'Bob', 70000, 'IT'),
(3, 'Charlie', 65000, 'Finance');

Table with Inserted Data:

EmpID | Name | Salary | Department

--------+--------+--------+------------
1 | Alice | 50000 | HR
2 | Bob | 70000 | IT
3 | Charlie| 65000 | Finance

3. Declaring and Using a Cursor

DECLARE
CURSOR emp_cursor IS
SELECT EmpID, Name, Salary FROM Employee;
v_EmpID Employee.EmpID%TYPE;
v_Name Employee.Name%TYPE;
v_Salary Employee.Salary%TYPE;
BEGIN
OPEN emp_cursor;

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 35

 LOOP
FETCH emp_cursor INTO v_EmpID, v_Name, v_Salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EmpID: ' || v_EmpID || ', Name: ' || v_Name ||
', Salary: ' || v_Salary);
END LOOP;
CLOSE emp_cursor;
END;
/

Output:

EmpID: 1, Name: Alice, Salary: 50000

EmpID: 2, Name: Bob, Salary: 70000
EmpID: 3, Name: Charlie, Salary: 65000

4. Using FOR UPDATE Cursor

DECLARE
CURSOR cur_emp IS
SELECT EmpID, Salary FROM Employee FOR UPDATE;
BEGIN
FOR rec IN cur_emp LOOP
UPDATE Employee SET Salary = Salary + 5000 WHERE CURRENT OF cur_emp;
END LOOP;
END;
/

Output:

Salaries updated for all employees.

RESULT
The experiment successfully demonstrates the use of cursors, FOR UPDATE, WHERE
CURRENT OF, and cursor variables in PL/SQL.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 36

Experiment 11: Triggers in SQL
AIM

Develop programs using BEFORE and AFTER Triggers, Row and Statement Triggers,
and INSTEAD OF Triggers.

THEORY

Triggers in SQL are special stored procedures that automatically execute when a
specified event occurs in a table. They are essential for ensuring data integrity,
enforcing business rules, and automating database operations without requiring
explicit action from users or applications.

Importance of Triggers

• Data Integrity Enforcement: Ensures constraints such as referential

integrity, preventing orphan records in related tables.
• Automated Auditing: Keeps track of changes in tables, allowing automatic
logging of updates, deletions, and insertions.
• Business Logic Implementation: Automates business rules, such as
preventing negative inventory values or restricting unauthorized changes.
• Security Enhancement: Controls unauthorized modifications by restricting
direct table updates.
• Consistency Maintenance: Synchronizes data between tables automatically
upon modification.

Working of Triggers
1. Event Activation: A trigger activates when an event (INSERT, UPDATE,
DELETE) occurs on a table or view.
2. Execution Timing: Triggers execute before or after an event.
3. Row-Level or Statement-Level Execution: Triggers can fire once per row
(ROW-LEVEL) or once per statement execution (STATEMENT-LEVEL).
4. Trigger Actions: Defined PL/SQL logic runs based on the trigger's activation
condition.

Types of Triggers

Triggers in SQL are special stored procedures that automatically execute when a
specified event occurs in a table. Triggers help maintain data integrity, automate
business rules, and enforce security policies.

• Types of Triggers:
1. BEFORE Trigger: Executes before an INSERT, UPDATE, or DELETE
operation.
2. AFTER Trigger: Executes after an INSERT, UPDATE, or DELETE
operation.
3. ROW-Level Trigger: Executes once for each affected row.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 37

 4. STATEMENT-Level Trigger: Executes once for the entire statement.
5. INSTEAD OF Trigger: Used for views to handle modifications
indirectly.

PROCEDURE

1. Creating the Employee Table

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Salary INT
);

Created Table Structure:

Column | Data Type | Constraints

---------+--------------+------------------------
EmpID | INT | PRIMARY KEY
Name | VARCHAR(50) | NOT NULL
Salary | INT |

2. Inserting Data into the Table

INSERT INTO Employee (EmpID, Name, Salary) VALUES

(1, 'Alice', 50000),
(2, 'Bob', 70000);

Table with Inserted Data:

EmpID | Name | Salary

--------+--------+--------
1 | Alice | 50000
2 | Bob | 70000

3. Creating a BEFORE INSERT Trigger

CREATE OR REPLACE TRIGGER before_insert_employee

BEFORE INSERT ON Employee
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting new employee record');
END;
/

4. Creating an AFTER UPDATE Trigger

CREATE OR REPLACE TRIGGER after_update_salary

AFTER UPDATE ON Employee
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Salary updated for ' || :OLD.Name);
END;
/

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 38

5. Creating an INSTEAD OF Trigger for a View

CREATE VIEW Employee_View AS

SELECT EmpID, Name FROM Employee;

CREATE OR REPLACE TRIGGER instead_of_update

INSTEAD OF UPDATE ON Employee_View
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Updating data through view is not allowed');
END;
/

RESULT

The experiment successfully demonstrates the creation and execution of BEFORE,

AFTER, ROW, STATEMENT, and INSTEAD OF triggers in SQL.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 39

Experiment 12: Indexing in SQL
AIM

Create a table and perform the search operation on the table using indexing and non-
indexing techniques.

THEORY

Indexing in SQL is a technique used to enhance the performance of database queries

by reducing the time taken to retrieve data. An index is a special lookup table that the
database search engine uses to speed up data retrieval. When a query is executed, the
database engine checks if an index exists and uses it to fetch the required data quickly.

Why Indexing is Important in Databases

In large databases, querying data without an index can be slow, as the database has to
scan each row in a table (known as a full table scan). Indexing optimizes search
operations, ensuring that queries execute faster by reducing the number of rows
accessed.

How Indexing Works

• When an index is created on a column, the database stores an ordered structure
containing the indexed column’s values and their corresponding row locations.
• When a query is executed, instead of scanning the entire table, the database
engine uses the index to directly locate the desired data.
• Indexes are especially beneficial for queries using WHERE, ORDER BY, and
GROUP BY clauses.

Disadvantages of Indexing

• Increased Storage Requirements: Indexes consume additional disk space.

• Slower Write Operations: INSERT, UPDATE, and DELETE operations take
longer due to index maintenance.
• Overhead in Index Management: Frequent updates to indexed columns
require reorganization of the index structure.

Best Practices for Using Indexes

• Use indexes on frequently searched columns.
• Avoid indexing columns with high update frequency.
• Prefer unique indexes for primary keys and unique constraints.
• Use composite indexes for multi-column searches.

Types of Indexing in SQL

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 40

Indexing in SQL is a technique used to enhance the performance of database queries
by reducing the time taken to retrieve data. An index is a special lookup table that the
database search engine uses to speed up data retrieval.

Importance of Indexing
• Improves Query Performance: Indexes allow faster retrieval of data
compared to full table scans.
• Enhances Sorting and Filtering: Indexes improve sorting operations like
ORDER BY and filtering using WHERE clauses.
• Reduces Disk I/O: Optimizes the way data is fetched from storage, reducing
disk access time.
• Supports Uniqueness: Unique indexes ensure that no duplicate values exist
in specific columns.

Types of Indexing in SQL

1. Clustered Index: Physically sorts data in the order of the index.

2. Non-Clustered Index: Creates a separate structure to store indexed values.
3. Unique Index: Ensures uniqueness of values in a column.
4. Composite Index: Indexing on multiple columns to improve search
performance.

PROCEDURE

1. Creating the Employee Table

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Salary INT,
Department VARCHAR(20)
);

Created Table Structure:

Column | Data Type | Constraints

-------------+--------------+------------------------
EmpID | INT | PRIMARY KEY
Name | VARCHAR(50) | NOT NULL
Salary | INT |
Department | VARCHAR(20) |

2. Inserting Data into the Table

INSERT INTO Employee (EmpID, Name, Salary, Department) VALUES

(1, 'Alice', 50000, 'HR'),
(2, 'Bob', 70000, 'IT'),
(3, 'Charlie', 65000, 'Finance'),
(4, 'David', 75000, 'IT'),
(5, 'Emma', 55000, 'HR');

Table with Inserted Data:

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 41

 EmpID | Name | Salary | Department
--------+--------+--------+------------
1 | Alice | 50000 | HR
2 | Bob | 70000 | IT
3 | Charlie| 65000 | Finance
4 | David | 75000 | IT
5 | Emma | 55000 | HR

3. Performing a Search Without an Index

EXPLAIN PLAN FOR

SELECT * FROM Employee WHERE Name = 'Alice';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Output:

Execution Plan: Full table scan detected.

Query took longer execution time.

4. Creating an Index on the Name Column

CREATE INDEX idx_employee_name ON Employee(Name);

5. Performing a Search With an Index

EXPLAIN PLAN FOR

SELECT * FROM Employee WHERE Name = 'Alice';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Output:

Execution Plan: Index scan used.

Query executed faster.

RESULT
The experiment successfully demonstrates the use of indexing in SQL for faster search
operations compared to non-indexed searches.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 42

Experiment 13: Java Program for JDBC Database
Connection
AIM
Write a Java program that connects to a database using JDBC.

THEORY

JDBC (Java Database Connectivity) is an essential API in Java for interacting with
relational databases. It acts as a bridge between Java applications and various
database management systems, enabling seamless communication and execution of
SQL queries within Java programs.

Features of JDBC
• Database Independence: JDBC supports various databases like MySQL,
Oracle, PostgreSQL, and SQL Server without requiring major code
modifications.
• Standardized API: Provides a uniform method for connecting to databases,
executing queries, and handling results.
• Efficient Query Execution: Supports batch execution, prepared statements,
and stored procedures to improve performance.
• Robust Exception Handling: Built-in mechanisms to manage SQL
exceptions and ensure stable database interactions.
• Multithreading Support: Allows multiple Java threads to access a database
simultaneously, improving efficiency in concurrent applications.

JDBC Architecture

JDBC follows a multi-layered architecture that consists of:

1. JDBC API: Interfaces and classes for database interactions.

2. JDBC Driver Manager: Handles connection requests and loads appropriate
database drivers.
3. JDBC Drivers: Different types of drivers (JDBC-ODBC Bridge, Native API,
Network Protocol, and Thin Driver) to establish connections.
4. Database Engine: Executes SQL queries and returns results to the Java
application.

Types of JDBC Drivers

1. JDBC-ODBC Bridge Driver: Translates JDBC calls into ODBC calls;

requires ODBC drivers.
2. Native API Driver: Uses database-specific client libraries for
communication.
3. Network Protocol Driver: Uses middleware to translate JDBC requests into
database-specific calls.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 43

 4. Thin Driver: Directly converts JDBC calls into database-native protocol;
commonly used for Java applications.

JDBC Connection Lifecycle

The following steps outline the lifecycle of a JDBC connection:

1. Loading the Driver: Registering the database driver.

2. Establishing a Connection: Connecting to the database using a connection
string.
3. Creating a Statement: Preparing SQL statements for execution.
4. Executing Queries: Running SQL queries to manipulate and retrieve data.
5. Processing Results: Handling the retrieved data using ResultSet.
6. Closing the Connection: Releasing database resources to prevent memory
leaks.

JDBC (Java Database Connectivity) is an API that allows Java applications to interact
with databases. It provides a set of Java classes and interfaces to establish a
connection, execute queries, and retrieve results from a relational database.

Importance of JDBC
• Platform Independence: JDBC allows Java applications to communicate
with any database that supports SQL.
• Secure Connectivity: Provides authentication and encryption mechanisms
for secure database transactions.
• Efficient Query Execution: Allows prepared statements and batch
execution for better performance.
• Supports Multiple Databases: Compatible with MySQL, PostgreSQL,
Oracle, and other relational databases.

Steps for JDBC Connection

1. Load the JDBC Driver: The Java application loads the database-specific
driver.
2. Establish a Connection: Connects to the database using a connection URL,
username, and password.
3. Create a Statement Object: Allows execution of SQL queries.
4. Execute SQL Queries: Performs INSERT, UPDATE, DELETE, or SELECT
operations.
5. Process the Results: Retrieves and processes the query results.
6. Close the Connection: Releases database resources.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 44

PROCEDURE
1. Setting Up the Database

CREATE DATABASE College;

USE College;

CREATE TABLE Students (

RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Marks INT
);

2. Inserting Sample Data

INSERT INTO Students (RollNo, Name, Marks) VALUES

(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 75);

3. Displaying Table After Data Insertion

SELECT * FROM Students;

Output:

RollNo | Name | Marks

--------+--------+-------
1 | Alice | 85
2 | Bob | 92
3 | Charlie| 75
INSERT INTO Students (RollNo, Name, Marks) VALUES
(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 75);

3. Writing a Java Program for JDBC Connection

import java.sql.*;

public class JDBCConnection {

public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/College";
String user = "root";
String password = "password";

try {
// Load JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");

// Establish Connection
Connection conn = DriverManager.getConnection(url, user,
password);
System.out.println("Database Connected Successfully!");

// Close Connection
conn.close();

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 45

 } catch (Exception e) {
e.printStackTrace();
}
}
}

Output:

Database Connected Successfully!

RESULT
The experiment successfully demonstrates how to establish a JDBC connection
between a Java application and a MySQL database.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 46

Experiment 14: Java Program for JDBC Database
Insertion
AIM
Write a Java program to connect to a database using JDBC and insert values into it.

THEORY

JDBC (Java Database Connectivity) is an essential API in Java for interacting with
relational databases. It acts as a bridge between Java applications and various
database management systems, enabling seamless communication and execution of
SQL queries within Java programs.

Features of JDBC
• Database Independence: JDBC supports various databases like MySQL,
Oracle, PostgreSQL, and SQL Server without requiring major code
modifications.
• Standardized API: Provides a uniform method for connecting to databases,
executing queries, and handling results.
• Efficient Query Execution: Supports batch execution, prepared statements,
and stored procedures to improve performance.
• Robust Exception Handling: Built-in mechanisms to manage SQL
exceptions and ensure stable database interactions.
• Multithreading Support: Allows multiple Java threads to access a database
simultaneously, improving efficiency in concurrent applications.

JDBC Architecture

JDBC follows a multi-layered architecture that consists of:

1. JDBC API: Interfaces and classes for database interactions.

2. JDBC Driver Manager: Handles connection requests and loads appropriate
database drivers.
3. JDBC Drivers: Different types of drivers (JDBC-ODBC Bridge, Native API,
Network Protocol, and Thin Driver) to establish connections.
4. Database Engine: Executes SQL queries and returns results to the Java
application.

Types of JDBC Drivers

1. JDBC-ODBC Bridge Driver: Translates JDBC calls into ODBC calls;

requires ODBC drivers.
2. Native API Driver: Uses database-specific client libraries for
communication.
3. Network Protocol Driver: Uses middleware to translate JDBC requests into
database-specific calls.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 47

 4. Thin Driver: Directly converts JDBC calls into database-native protocol;
commonly used for Java applications.

JDBC Connection Lifecycle

The following steps outline the lifecycle of a JDBC connection:

1. Loading the Driver: Registering the database driver.

2. Establishing a Connection: Connecting to the database using a connection
string.
3. Creating a Statement: Preparing SQL statements for execution.
4. Executing Queries: Running SQL queries to manipulate and retrieve data.
5. Processing Results: Handling the retrieved data using ResultSet.
6. Closing the Connection: Releasing database resources to prevent memory
leaks.

JDBC (Java Database Connectivity) is an API that allows Java applications to interact
with databases. It provides a set of Java classes and interfaces to establish a
connection, execute queries, and retrieve results from a relational database.

Importance of JDBC
• Platform Independence: JDBC allows Java applications to communicate
with any database that supports SQL.
• Secure Connectivity: Provides authentication and encryption mechanisms
for secure database transactions.
• Efficient Query Execution: Allows prepared statements and batch
execution for better performance.
• Supports Multiple Databases: Compatible with MySQL, PostgreSQL,
Oracle, and other relational databases.

Steps for JDBC Connection

1. Load the JDBC Driver: The Java application loads the database-specific
driver.
2. Establish a Connection: Connects to the database using a connection URL,
username, and password.
3. Create a Statement Object: Allows execution of SQL queries.
4. Execute SQL Queries: Performs INSERT, UPDATE, DELETE, or SELECT
operations.
5. Process the Results: Retrieves and processes the query results.
6. Close the Connection: Releases database resources.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 48

PROCEDURE
1. Writing a Java Program for JDBC Insertion

import java.sql.*;

public class JDBCInsert {

public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/College";
String user = "root";
String password = "password";

try {
// Load JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");

// Establish Connection
Connection conn = DriverManager.getConnection(url, user,
password);
System.out.println("Database Connected Successfully!");

// Create SQL Insert Statement

String sql = "INSERT INTO Students (RollNo, Name, Marks) VALUES
(?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);

// Insert Data
pstmt.setInt(1, 4);
pstmt.setString(2, "David");
pstmt.setInt(3, 88);
pstmt.executeUpdate();

pstmt.setInt(1, 5);
pstmt.setString(2, "Emma");
pstmt.setInt(3, 76);
pstmt.executeUpdate();

System.out.println("Data Inserted Successfully!");

// Close Connection
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

Output:

Database Connected Successfully!

Data Inserted Successfully!

2. Displaying Table After Insertion

SELECT * FROM Students;

Output:

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 49

 RollNo | Name | Marks
--------+--------+-------
1 | Alice | 85
2 | Bob | 92
3 | Charlie| 75
4 | David | 88
5 | Emma | 76

1. Setting Up the Database

CREATE DATABASE College;

USE College;

CREATE TABLE Students (

RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Marks INT
);

2. Inserting Sample Data

INSERT INTO Students (RollNo, Name, Marks) VALUES

(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 75);

3. Displaying Table After Data Insertion

SELECT * FROM Students;

Output:

RollNo | Name | Marks

--------+--------+-------
1 | Alice | 85
2 | Bob | 92
3 | Charlie| 75
INSERT INTO Students (RollNo, Name, Marks) VALUES
(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 75);

3. Writing a Java Program for JDBC Connection

import java.sql.*;

public class JDBCConnection {

public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/College";
String user = "root";
String password = "password";

try {
// Load JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");

// Establish Connection

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 50

 Connection conn = DriverManager.getConnection(url, user,
password);
System.out.println("Database Connected Successfully!");

// Close Connection
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

Output:

Database Connected Successfully!

RESULT
The experiment successfully demonstrates how to establish a JDBC connection
between a Java application and a MySQL database.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 51

Experiment 15: Java Program for JDBC Database
Deletion
AIM
Write a Java program to connect to a database using JDBC and delete values from it.

THEORY

JDBC (Java Database Connectivity) is an essential API in Java for interacting with
relational databases. It acts as a bridge between Java applications and various
database management systems, enabling seamless communication and execution of
SQL queries within Java programs.

Features of JDBC
• Database Independence: JDBC supports various databases like MySQL,
Oracle, PostgreSQL, and SQL Server without requiring major code
modifications.
• Standardized API: Provides a uniform method for connecting to databases,
executing queries, and handling results.
• Efficient Query Execution: Supports batch execution, prepared statements,
and stored procedures to improve performance.
• Robust Exception Handling: Built-in mechanisms to manage SQL
exceptions and ensure stable database interactions.
• Multithreading Support: Allows multiple Java threads to access a database
simultaneously, improving efficiency in concurrent applications.

JDBC Architecture

JDBC follows a multi-layered architecture that consists of:

1. JDBC API: Interfaces and classes for database interactions.

2. JDBC Driver Manager: Handles connection requests and loads appropriate
database drivers.
3. JDBC Drivers: Different types of drivers (JDBC-ODBC Bridge, Native API,
Network Protocol, and Thin Driver) to establish connections.
4. Database Engine: Executes SQL queries and returns results to the Java
application.

Types of JDBC Drivers

1. JDBC-ODBC Bridge Driver: Translates JDBC calls into ODBC calls;

requires ODBC drivers.
2. Native API Driver: Uses database-specific client libraries for
communication.
3. Network Protocol Driver: Uses middleware to translate JDBC requests into
database-specific calls.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 52

 4. Thin Driver: Directly converts JDBC calls into database-native protocol;
commonly used for Java applications.

JDBC Connection Lifecycle

The following steps outline the lifecycle of a JDBC connection:

1. Loading the Driver: Registering the database driver.

2. Establishing a Connection: Connecting to the database using a connection
string.
3. Creating a Statement: Preparing SQL statements for execution.
4. Executing Queries: Running SQL queries to manipulate and retrieve data.
5. Processing Results: Handling the retrieved data using ResultSet.
6. Closing the Connection: Releasing database resources to prevent memory
leaks.

JDBC (Java Database Connectivity) is an API that allows Java applications to interact
with databases. It provides a set of Java classes and interfaces to establish a
connection, execute queries, and retrieve results from a relational database.

Importance of JDBC
• Platform Independence: JDBC allows Java applications to communicate
with any database that supports SQL.
• Secure Connectivity: Provides authentication and encryption mechanisms
for secure database transactions.
• Efficient Query Execution: Allows prepared statements and batch
execution for better performance.
• Supports Multiple Databases: Compatible with MySQL, PostgreSQL,
Oracle, and other relational databases.

Steps for JDBC Connection

1. Load the JDBC Driver: The Java application loads the database-specific
driver.
2. Establish a Connection: Connects to the database using a connection URL,
username, and password.
3. Create a Statement Object: Allows execution of SQL queries.
4. Execute SQL Queries: Performs INSERT, UPDATE, DELETE, or SELECT
operations.
5. Process the Results: Retrieves and processes the query results.
6. Close the Connection: Releases database resources.

PROCEDURE

1. Writing a Java Program for JDBC Deletion

import java.sql.*;

public class JDBCDelete {

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 53

 public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/College";
String user = "root";
String password = "password";

try {
// Load JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");

// Establish Connection
Connection conn = DriverManager.getConnection(url, user,
password);
System.out.println("Database Connected Successfully!");

// Create SQL Delete Statement

String sql = "DELETE FROM Students WHERE RollNo = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);

// Delete Data
pstmt.setInt(1, 4);
int rowsDeleted = pstmt.executeUpdate();

if (rowsDeleted > 0) {
System.out.println("Record Deleted Successfully!");
} else {
System.out.println("No Record Found with Given Roll
Number.");
}

// Close Connection
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

Output:

Database Connected Successfully!

Record Deleted Successfully!

2. Displaying Table After Deletion

SELECT * FROM Students;

Output:

RollNo | Name | Marks

--------+--------+-------
1 | Alice | 85
2 | Bob | 92
3 | Charlie| 75
5 | Emma | 76
import java.sql.*;

public class JDBCInsert {

public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/College";

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 54

 String user = "root";
String password = "password";

try {
// Load JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");

// Establish Connection
Connection conn = DriverManager.getConnection(url, user,
password);
System.out.println("Database Connected Successfully!");

// Create SQL Insert Statement

String sql = "INSERT INTO Students (RollNo, Name, Marks) VALUES
(?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);

// Insert Data
pstmt.setInt(1, 4);
pstmt.setString(2, "David");
pstmt.setInt(3, 88);
pstmt.executeUpdate();

pstmt.setInt(1, 5);
pstmt.setString(2, "Emma");
pstmt.setInt(3, 76);
pstmt.executeUpdate();

System.out.println("Data Inserted Successfully!");

// Close Connection
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

Output:

Database Connected Successfully!

Data Inserted Successfully!

2. Displaying Table After Insertion

SELECT * FROM Students;

Output:

RollNo | Name | Marks

--------+--------+-------
1 | Alice | 85
2 | Bob | 92
3 | Charlie| 75
4 | David | 88
5 | Emma | 76

1. Setting Up the Database

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 55

CREATE DATABASE College;
USE College;

CREATE TABLE Students (

RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Marks INT
);

2. Inserting Sample Data

INSERT INTO Students (RollNo, Name, Marks) VALUES

(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 75);

3. Displaying Table After Data Insertion

SELECT * FROM Students;

Output:

RollNo | Name | Marks

--------+--------+-------
1 | Alice | 85
2 | Bob | 92
3 | Charlie| 75
INSERT INTO Students (RollNo, Name, Marks) VALUES
(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 75);

3. Writing a Java Program for JDBC Connection

import java.sql.*;

public class JDBCConnection {

public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/College";
String user = "root";
String password = "password";

try {
// Load JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");

// Establish Connection
Connection conn = DriverManager.getConnection(url, user,
password);
System.out.println("Database Connected Successfully!");

// Close Connection
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 56

Output:

Database Connected Successfully!

RESULT
The experiment successfully demonstrates how to establish a JDBC connection
between a Java application and a MySQL database.

RISE KRISHNA SAI GANDHI GROUP OF INSTITUTIONS - DBMS LAB 57