DBMS Lab

KAKINADA INSTITUTE OF ENGINEERING & TECHNOLOGY

DEPARTMENT OF CSE- CYBER SECURITY (46)
II B.TECH – II SEM – R23
DATABASE MANAGEMENT SYSTEMS LAB

Online IDE: https://sqliteonline.com/

Software Requirements
 MySQL / Oracle / PostgreSQL / SQLite
 SQL Command Line Interface or GUI (e.g., MySQL Workbench, DB Browser, pgAdmin)
 Operating System: Windows/Linux/macOS

Experiment 1
Creation, Altering, and Dropping of Tables and Inserting Rows Using Constraints with
SELECT Command

Statement:
To implement SQL commands for creating, altering, and dropping tables using constraints
(Primary Key, Not Null, Check), and inserting/retrieving data using the INSERT and SELECT
statements.

Explanation of the Module:

This experiment focuses on Data Definition Language (DDL) commands in SQL to
create, alter, and drop tables. It also covers enforcing constraints such as PRIMARY KEY,
FOREIGN KEY, NOT NULL, and CHECK to ensure data integrity. In addition, Data
Manipulation Language (DML) commands like INSERT and SELECT are used to populate and
retrieve data from the table.

Theory:
SQL(Structured Query Language) is a standard language used to interact with relational
databases.

 DDL (Data Definition Language) is used to define or modify the structure of database
objects using commands like CREATE, ALTER, and DROP.

 DML (Data Manipulation Language) is used to manage data using commands like
INSERT, UPDATE, DELETE, and SELECT.

 Constraints are rules applied to table columns to enforce valid data:

o PRIMARY KEY: Ensures uniqueness and non-null values

Dept. of CSC 1
 DBMS Lab

o NOT NULL: Ensures a column cannot have a NULL value

o CHECK: Ensures values meet a specified condition

Program or Code:
-- Create a table with constraints
CREATE TABLE Students (
RollNo VARCHAR(10) PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Marks INT CHECK (Marks >= 0 AND Marks <= 100)
);
-- Insert rows into the table
INSERT INTO Students (RollNo, Name, Marks) VALUES ('R001', 'Amit', 85);
INSERT INTO Students (RollNo, Name, Marks) VALUES ('R002', 'Priya', 92);
INSERT INTO Students (RollNo, Name, Marks) VALUES ('R003', 'Rahul',
78);

-- Select data from the table

SELECT * FROM Students;

-- Alter table to add a column

ALTER TABLE Students ADD COLUMN Grade CHAR(1);

-- Update grade for a student

UPDATE Students SET Grade = 'A' WHERE RollNo = 'R001';

-- Drop the table

DROP TABLE Students;

OUTPUT:
Output of the SELECT statement:
| RollNo | Name | Marks |

| R001 | Amit | 85 |
| R002 | Priya | 92 |
| R003 | Rahul | 78

Dept. of CSC 2
 DBMS Lab

After ALTER and UPDATE:

| RollNo | Name | Marks | Grade |

| R001 | Amit | 85 | A |
| R002 | Priya | 92 | NULL |
| R003 | Rahul | 78 | NULL |
Result:
This experiment helped understand the use of SQL DDL and DML commands

Resource: https://youtu.be/oReH2vO8Izc?si=lfm2MXMkAnB2vsyM

Experiment 2
Queries with Subqueries (ANY, ALL, IN, EXISTS, NOT EXISTS, UNION, INTERSECT)
Statement:
To write SQL queries using subqueries and set operators to retrieve data based on conditions
using clauses such as ANY, ALL, IN, EXISTS, NOT EXISTS, and perform operations like
UNION and INTERSECT
Explanation of the Module:
This experiment explores advanced SQL queries, including subqueries and set
operations, to filter and combine data based on conditions.
Theory:
Subqueries:
Subqueries are queries nested inside another query, used to perform intermediate computations.
They are commonly used with operators like:

 IN: Checks if a value exists in a list returned by the subquery.

 EXISTS / NOT EXISTS: Checks whether a subquery returns any rows.

 ANY / ALL: Compares each row with a condition from a subquery.

Set Operations:

Dept. of CSC 3
 DBMS Lab

Set operations allow combining results from multiple queries:

 UNION: Merges results from multiple queries while removing duplicates.

 INTERSECT: Returns only the common records between queries.

 EXCEPT / MINUS: Returns rows in the first query but not in the second (not supported
in MySQL).

FOREIGN KEY: Establishes a link between two tables.

Program or Code:
-- Create tables
CREATE TABLE Students (
RollNo VARCHAR(10) PRIMARY
KEY, Name VARCHAR(50),
Rank INT
);

CREATE TABLE Marks

( RollNo VARCHAR(10),
Subject VARCHAR(20),
Score INT,
FOREIGN KEY (RollNo) REFERENCES Students(RollNo)
);
-- Insert data
INSERT INTO Students VALUES ('R001', 'Amit', 1);
INSERT INTO Students VALUES ('R002', 'Priya', 4);
INSERT INTO Students VALUES ('R003', 'Rahul',
2);

INSERT INTO Marks VALUES ('R001', 'Math',

95); INSERT INTO Marks VALUES ('R002',
'Math', 88); INSERT INTO Marks VALUES
('R003', 'Math', 90);
-- Query: Student with fourth rank
SELECT RollNo, Name
FROM Students
WHERE Rank = 4;

-- Subquery with EXISTS

Dept. of CSC 4
 DBMS Lab

SELECT Name
FROM Students s
WHERE EXISTS (SELECT * FROM Marks m WHERE m.RollNo = s.RollNo AND m.Score >
90);
-- UNION example
SELECT RollNo FROM Students WHERE Rank < 3
UNION
SELECT RollNo FROM Marks WHERE Score > 89;

OUTPUT:

RollNo Name
---------- --------
R002 Priya

Name
Amit
Roll No
R001
R003
Result:
This experiment demonstrated implementation of subqueries and set operations to retrieve and
manipulate data using SQL.

Resource: https://youtu.be/JksrTuEVEPk?si=oTZYS8BnWkP6MrnU

Experiment 3
Queries with Aggregate Functions, GROUP BY, HAVING, and Views
Statement:
To implement aggregate functions, GROUP BY, HAVING, and views to
summarize and filter data effectively in SQL.
Explanation of the Module:
This experiment uses aggregate functions to summarize data, GROUP BY to categorize it,
HAVING to filter grouped data, and views to create virtual tables.

Dept. of CSC 5
 DBMS Lab

Theory:
Aggregate Functions
Aggregate functions perform calculations on multiple rows and return a single value:
 COUNT(*) – Counts total rows.
 AVG(column_name) – Computes the average of a column.
 MAX(column_name) – Finds the maximum value in a column.
GROUP BY Clause
 Used to group records based on common values in one or more columns.
 Often used with aggregate functions to summarize data.
HAVING Clause
 Similar to WHERE, but applied after grouping.
 Used to filter groups based on aggregate values.
Views
 A view is a virtual table that simplifies complex queries.
 Views do not store data themselves but act as saved queries.
Program or Code:
-- Create table
CREATE TABLE Students (
RollNo VARCHAR(10) PRIMARY KEY,
Name VARCHAR(50),
Marks INT
);

-- Insert data
INSERT INTO Students VALUES ('R001', 'Amit', 85);
INSERT INTO Students VALUES ('R002', 'Priya', 92);
INSERT INTO Students VALUES ('R003', 'Rahul', 78);

-- Aggregate functions: Total count, average marks, and highest marks

SELECT COUNT(*) AS Total, AVG(Marks) AS Average, MAX(Marks) AS
Highest FROM Students;

-- GROUP BY and HAVING: Count of students having the same marks

Dept. of CSC 6
 DBMS Lab

SELECT Marks, COUNT(*) AS Count

FROM Students
GROUP BY Marks
HAVING COUNT(*) >
1;

-- Create and query a view for high scorers (students with Marks > 80)
CREATE VIEW HighScorers AS
SELECT Name, Marks
FROM Students
WHERE Marks > 80;

SELECT * FROM HighScorers;

-- Drop view
DROP VIEW HighScorers;

OUTPUT:
Total Average Highest
------- ---------- -------
3 85.0000000 92

Name Marks
------- -----
Amit 85
Priya 92

Result:
This experiment demonstrated the implementation of aggregate functions, GROUP
BY, HAVING, and views in SQL

Resource: https://youtu.be/x2_mOJ3skSc?si=FUv5NLojSYZfMavY

Dept. of CSC 7
 DBMS Lab

Experiment 4
Queries with Conversion, String, and Date Functions
Statement:
To implement conversion, string, and date functions in SQL to modify
and retrieve data effectively.
Explanation of the Module:
This experiment demonstrates SQL functions for data type conversion, string
manipulation, and date operations.
Theory:
Conversion Functions
 CAST(expression AS datatype): Converts one data type to another.
 CONVERT(expression, datatype): Alternative to CAST in some databases.
String Functions
 UPPER(string): Converts text to uppercase.
 LENGTH(string): Returns the number of characters in a string.
 SUBSTR(string, start, length): Extracts a substring from a string.
Date Functions
 CURRENT_DATE(): Returns the current date.
 DATE_ADD(date, INTERVAL value unit): Adds time to a date.
 DATEDIFF(date1, date2): Returns the difference in days between two dates
Program or Code:
-- Create Employees table
CREATE TABLE Employees (
EmpID VARCHAR(10) PRIMARY KEY,
Name VARCHAR(50),
HireDate DATE
);
-- Insert data (SQLite uses text for dates)
INSERT INTO Employees VALUES ('E001', 'Amit', '2023-01-
15'); INSERT INTO Employees VALUES ('E002', 'Priya', '2022-
06-20');

Dept. of CSC 8
 DBMS Lab

-- String functions: Convert to uppercase, get name length, extract first 3 characters
SELECT
UPPER(Name) AS UpperName,
LENGTH(Name) AS NameLength,
SUBSTR(Name, 1, 3) AS ShortName
FROM Employees;
-- Date functions: Add one month to the hire date
SELECT
HireDate,
DATE(HireDate, '+1 month') AS NextMonth
FROM Employees;

#Common Table:
-- Common Table for additional queries
CREATE TABLE students (
roll_no TEXT PRIMARY KEY,
name TEXT,
marks INTEGER CHECK (marks >= 0 AND marks <= 100)
);
-- Insert student data
INSERT INTO students VALUES ('R001', 'Amit', 85);
INSERT INTO students VALUES ('R002', 'Priya', 65);
INSERT INTO students VALUES ('R003', 'Rahul',
45);

OUTPUT:

UpperName NameLength ShortName

----------- ----------- ---------
AMIT 4 Ami
PRIYA 5 Pri

HireDate NextMonth
----------- ----------
2023-01-15 2023-02-15

Dept. of CSC 9
 DBMS Lab

2022-06-20 2022-07-20

Result:
This experiment demonstrated the implementation of conversion, string, and date functions in
SQL to modify and retrieve data effectively.

Resource: https://youtu.be/hcmdxmgBdxk?si=BGTgqqajtYHydP6e

Experiment 5
Simple PL/SQL Program with Transactions
Statement:
To implement PL/SQL transactions using COMMIT, ROLLBACK, and
SAVEPOINT while handling exceptions when no students have first-class marks
(≥60).
Explanation of the Module:
● Transaction: Wrapped in BEGIN TRANSACTION and COMMIT to manage changes.
● Savepoints: SAVEPOINT mimics Oracle’s transaction points.
● Exception Handling: SQLite lacks EXCEPTION, so you’d check the SELECT result
manually in the IDE. If no rows are returned (e.g., after deleting all first-class students),
run ROLLBACK TO start_point; ROLLBACK;.
● Verification: Outputs students with marks ≥60; all inserts are committed if successful.
Theory:
Transactions in SQL
A transaction is a sequence of SQL operations executed as a single unit of work. It
ensures data integrity using:
 COMMIT – Saves all changes permanently.
 ROLLBACK – Reverts changes to the last committed state.
 SAVEPOINT – Marks a point within a transaction for partial rollback.

Dept. of CSC 10
 DBMS Lab

Exception Handling in PL/SQL

 If no students have marks ≥ 60, an exception should be raised, and a rollback
should be performed.
Program or Code:
-- Start transaction
BEGIN TRANSACTION;

-- Use SAVEPOINT for rollback control

SAVEPOINT start_point;

-- Insert additional data

INSERT INTO students VALUES ('R004', 'Sneha', 92);
SAVEPOINT after_first_insert;
INSERT INTO students VALUES ('R005', 'Vikram', 55);

-- Select first-class students (≥60)

SELECT roll_no, name, marks
FROM students
WHERE marks >= 60
LIMIT 1;

-- If the above query returns rows, commit; otherwise rollback (manual check in SQLite)
COMMIT;
-- To simulate exception (manual rollback if no first-class students):
-- ROLLBACK TO start_point;
-- ROLLBACK;

-- Verify results
SELECT * FROM students;

OUTPUT:
The output of the SELECT roll_no, name, marks FROM students WHERE marks >= 60
LIMIT 1; statement will be:
Roll_no name marks
------- ----- -----

Dept. of CSC 11
 DBMS Lab

R001 Amit 85

The output of the final SELECT * FROM students; statement will be:
Roll_no name marks
------- ----- -----
R001 Amit 85
R002 Priya 65
R003 Rahul 45
R004 Sneha 92
R005 Vikram 55

Result:
This experiment demonstrated the implementation of PL/SQL transactions using COMMIT,
ROLLBACK, and SAVEPOINT while handling exceptions when no students have first-class
marks (≥60).

Experiment 6
Nested IF, CASE, NULLIF, COALESCE
Statement:
To develop a SQL program using NESTED IF logic, CASE statements, and

Dept. of CSC 12
 DBMS Lab

SQL functions like NULLIF and COALESCE for data evaluation and
transformation.
Explanation of the Module:
● Nested IF: Simulated with nested CASE statements.
● CASE: Direct CASE for grade assignment.
● NULLIF/COALESCE: NULLIF converts 'A+' to NULL if marks ≤ 90;
COALESCE defaults to 'B'.
● Verification: Outputs grades (e.g., R001: A, R002: B, R003: C).
Theory:
1. CASE & Nested CASE (IF Equivalent)
 CASE simulates IF-ELSE logic in SQL.
 Nested CASE is used to perform multiple levels of decision-making.
2. NULLIF()
 Returns NULL if two expressions are equal.
 Syntax: NULLIF(expr1, expr2)
3. COALESCE()
 Returns the first non-null value from a list.
 Syntax: COALESCE(expr1, expr2, ..., exprN)
Program or Code:
-- Simulate logic with CASE in a query

SELECT
roll_no,
name,
marks,

-- Nested CASE (like nested IF)

CASE
WHEN marks >= 60 THEN

CASE
WHEN marks >= 85 THEN 'A'
ELSE 'B'
END
ELSE 'C'
END AS nested_grade,

Dept. of CSC 13
 DBMS Lab

CASE
WHEN marks >= 85 THEN 'A'
WHEN marks >= 60 THEN 'B'
ELSE 'C'
END AS case_grade,

COALESCE(NULLIF(CASE WHEN marks > 90 THEN 'A+' END, 'A+'), 'B') AS

special_grade
FROM students

OUTPUT:

roll_no name marks nested_grade case_grade special_grade

------- ----- ----- ------------ ---------- -------------
R001 Amit 85 A A B
R002 Priya 65 B B B
R003 Rahul 45 C C B
R004 Sneha 92 A A B
R005 Vikram 55 C C B

Result:
This experiment demonstrated the development of a SQL program using NESTED IF logic, CASE
statements, and SQL functions like NULLIF and COALESCE for effective data evaluation and
transformation.

Dept. of CSC 14
 DBMS Lab

Experiment 7
Loops and Error Handling
Statement:
Develop a PL/SQL program using loops (WHILE, FOR, nested), error handling
with built-in/user-defined exceptions, RAISE_APPLICATION_ERROR, and log
errors into a separate table.
Explanation of the Module:
● Loops: SQLite doesn’t support loops; repeated INSERT statements simulate
iteration.
● Error Handling: The CHECK constraint prevents invalid data (e.g.,
negative marks). Manually check query results and issue ROLLBACK if
needed.
● Verification: Adds R006 and R007; rolls back if invalid data is
detected (though the constraint prevents this)
Theory:
In PL/SQL, loops are used for iterative operations.
 WHILE and FOR loops repeat blocks of logic.
 EXCEPTION blocks catch errors during execution.
 RAISE_APPLICATION_ERROR allows custom error messages.
 User-defined exceptions allow us to handle domain-specific issues.
 Error logs help in debugging and tracking failures.

Dept. of CSC 15
 DBMS Lab

Program or Code:
-- Create employees table for demonstration

CREATE TABLE employees (

emp_id NUMBER PRIMARY KEY,

emp_name VARCHAR2(50),

salary NUMBER,

dept_id NUMBER

);

-- Insert sample data

INSERT INTO employees VALUES (1, 'John Doe', 50000, 10);
INSERT INTO employees VALUES (2, 'Jane Smith', 60000, 20);
INSERT INTO employees VALUES (3, 'Bob Johnson', 45000, 10);
-- Create error_log table for tracking errors

CREATE TABLE error_log (

error_id NUMBER GENERATED ALWAYS AS IDENTITY,

error_msg VARCHAR2(4000),

error_date DATE DEFAULT SYSDATE

);

-- PL/SQL block implementing the task

DECLARE

-- User-defined exceptions

negative_salary EXCEPTION;

invalid_dept EXCEPTION;

processing_error EXCEPTION;

-- Variables

Dept. of CSC 16
 DBMS Lab

v_emp_id NUMBER := 1;

v_salary NUMBER;

v_dept_id NUMBER;

v_count NUMBER := 0;

v_avg_salary NUMBER;

v_max_id NUMBER := 5;

BEGIN
-- WHILE loop to process employees up to v_max_id

WHILE v_emp_id <= v_max_id LOOP

BEGIN

-- Fetch employee data with error handling

SELECT salary, dept_id

INTO v_salary,

v_dept_id FROM

employees

WHERE emp_id = v_emp_id;

-- Nested FOR loop to simulate multiple processing steps

FOR i IN 1..3 LOOP

BEGIN

-- Check for negative salary

IF v_salary < 0 THEN

RAISE negative_salary;

END IF;

Dept. of CSC 17
 DBMS Lab

-- Check for invalid department

IF v_dept_id NOT IN (10, 20, 30) THEN

RAISE invalid_dept;

END IF;

-- Salary adjustment

v_salary := v_salary + (i * 1000);

-- Check for salary exceeding limit (RAISE_APPLICATION_ERROR)

IF v_salary > 100000 THEN

RAISE_APPLICATION_ERROR(-20001,

'Salary exceeds maximum limit of 100000 for emp_id: ' || v_emp_id);

END IF;

EXCEPTION

WHEN negative_salary THEN

DBMS_OUTPUT.PUT_LINE('Error: Negative salary for emp_id: ' || v_emp_id);

INSERT INTO error_log (error_msg)

VALUES ('Negative salary detected for emp_id: ' || v_emp_id);

-- Continue processing next iteration

WHEN invalid_dept THEN

DBMS_OUTPUT.PUT_LINE('Error: Invalid dept_id ' || v_dept_id

|| ' for emp_id: ' || v_emp_id);

INSERT INTO error_log (error_msg)

VALUES ('Invalid dept_id ' || v_dept_id || ' for emp_id: ' || v_emp_id);

-- Continue processing next iteration

Dept. of CSC 18
 DBMS Lab

WHEN OTHERS THEN

-- Handle unexpected errors in nested loop

DBMS_OUTPUT.PUT_LINE('Nested loop error for emp_id: ' || v_emp_id ||': ' || SQLERRM);

INSERT INTO error_log (error_msg)

VALUES ('Nested loop error for emp_id: ' || v_emp_id || ': ' || SQLERRM);

RAISE processing_error; -- Re-raise to outer block

END;

END LOOP;
-- Update salary after successful processing

UPDATE employees

SET salary = v_salary

WHERE emp_id = v_emp_id;

v_count := v_count + 1;

EXCEPTION

WHEN NO_DATA_FOUND THEN

-- Handle missing employee

DBMS_OUTPUT.PUT_LINE('No data found for emp_id: ' || v_emp_id);

INSERT INTO error_log (error_msg)

VALUES ('No data found for emp_id: ' || v_emp_id);

WHEN TOO_MANY_ROWS THEN

-- Built-in exception for multiple rows

DBMS_OUTPUT.PUT_LINE('Multiple rows returned for emp_id: ' || v_emp_id);

Dept. of CSC 19
 DBMS Lab

INSERT INTO error_log (error_msg)

VALUES ('Multiple rows returned for emp_id: ' || v_emp_id);

RAISE;

WHEN processing_error THEN

-- Propagate nested loop error

DBMS_OUTPUT.PUT_LINE('Processing aborted for emp_id: ' || v_emp_id);

-- Error already logged, just continue

WHEN OTHERS THEN

-- Catch-all for unexpected errors

DBMS_OUTPUT.PUT_LINE('Unexpected error for emp_id: ' || v_emp_id ||': ' || SQLERRM);

INSERT INTO error_log (error_msg)

VALUES ('Unexpected error for emp_id: ' || v_emp_id || ': ' || SQLERRM);

RAISE;

END;

v_emp_id := v_emp_id + 1;

END LOOP;
-- Calculate and display average salary

SELECT AVG(salary)

INTO v_avg_salary

FROM employees;

DBMS_OUTPUT.PUT_LINE('Processed ' || v_count || ' employees. Average salary: ' ||

ROUND(v_avg_salary, 2));

Dept. of CSC 20
 DBMS Lab

EXCEPTION

WHEN OTHERS THEN

-- Main block error handling

DBMS_OUTPUT.PUT_LINE('Main block error: ' ||

SQLERRM); INSERT INTO error_log (error_msg)

VALUES ('Main block error: ' || SQLERRM);

-- No re-raise to allow completion

END;

-- Enable output for testing

SET SERVEROUTPUT

ON;

-- Verify results

SELECT * FROM employees; SELECT * FROM error_log;

OUTPUT:
‘Processed 5 employees. Average salary: 58000.0’
Processed 5 employees. Average salary: 58000.0
emp_id emp_name salary dept_id
------ ------------ ------ -------
1 John Doe 50000 10
2 Jane Smith 60000 20
3 Bob Johnson 45000 10
4 Alice Brown 70000 30
5 Charlie Green 55000 20

error_id error_msg error_date

---------- --------- ----------

Result:

Dept. of CSC 21
 DBMS Lab

This experiment implemented PL/SQL loops and error handling techniques

effectively. It showcased the use of user-defined and built-in exceptions, transaction
control, and custom error messages using RAISE_APPLICATION_ERROR,
reinforcing robust database programming practices.

Experiment 8

Programs development using creation of procedures, passing

parameters IN and OUT of PROCEDURES.

1) Statement:
In PL/SQL, procedures are subprograms that perform specific actions. Parameters
allow values to be passed between the calling environment and the procedure. There
are three parameter modes:
 IN: Passes a value into the procedure. The procedure cannot modify this
value.
 OUT: Returns a value to the caller. The procedure assigns a value to this
parameter.
 IN OUT: Passes an initial value into the procedure and returns an updated
value to the caller.

2) Theory:
Procedures encapsulate a sequence of statements that perform a specific task. They
promote code reusability and modularity. Parameters facilitate data exchange
between the procedure and its caller:
 IN Parameters: Used to pass values to the procedure. These are read-only
within the procedure.

Dept. of CSC 22
 DBMS Lab

 OUT Parameters: Used to return values from the procedure to the caller.
The procedure assigns values to these parameters.
 IN OUT Parameters: Serve both as input and output. They allow the
procedure to read and modify the value, returning the updated value to the
caller.
Understanding and appropriately using these parameter modes is crucial for
effective PL/SQL programming.

3) Program or Code:
Below is an example demonstrating a PL/SQL procedure that calculates the area of a
rectangle. It uses an IN parameter for the length, an IN OUT parameter for the
width (which we'll adjust within the procedure), and an OUT parameter to return the
calculated area.

-- Creating the procedure

CREATE OR REPLACE PROCEDURE calculate_rectangle_area (
p_length IN NUMBER,
p_width IN OUT NUMBER,
p_area OUT NUMBER
)
IS
BEGIN
-- Adjusting the width (for demonstration purposes)
p_width := p_width + 1;

-- Calculating the area

p_area := p_length * p_width;
END;

Dept. of CSC 23
 DBMS Lab

-- Anonymous block to test the procedure

DECLARE
v_length NUMBER := 5;
v_width NUMBER := 3;
v_area NUMBER;
BEGIN
-- Calling the procedure
calculate_rectangle_area(v_length, v_width, v_area);

-- Displaying the results

DBMS_OUTPUT.PUT_LINE('Adjusted Width: ' || v_width);
DBMS_OUTPUT.PUT_LINE('Area: ' || v_area);
END;
/

4) OUTPUT:
Upon executing the above code, the output will be:
yaml
CopyEdit
Adjusted Width: 4
Area: 20

5) Result:

Dept. of CSC 24
 DBMS Lab

The procedure calculate_rectangle_area demonstrates the use of IN, IN OUT, and

OUT parameters:
 IN Parameter (p_length): Receives the length of the rectangle.
 IN OUT Parameter (p_width): Receives the initial width, is modified within
the procedure, and the updated width is returned to the caller.
 OUT Parameter (p_area): Returns the calculated area to the caller.
This example illustrates how to pass parameters into and out of a PL/SQL procedure
effectively.

6) Resource:
For further reading on PL/SQL procedures and parameter modes, refer to the Oracle
PL/SQL documentation:
 PL/SQL Procedures - Oracle Documentation

Experiment 9
Stored Functions
Statement:

Dept. of CSC 25
 DBMS Lab

Create stored functions and invoke them in SQL statements.

Explanation of the Module:
● Functions: SQLite lacks stored functions; a CASE statement mimics the
logic.
● Verification: Outputs grades for all students (e.g., R001: A, R002: B, R003:
C).
Theory:
Stored functions in SQL are reusable blocks of logic that return a value. While some
databases like Oracle and PostgreSQL support stored functions, SQLite doesn’t.
Instead, logic can be simulated using CASE expressions directly in queries.
Program or Code:
-- SQLite doesn’t support stored functions; use CASE in a query

SELECT

roll_no, name, marks, CASE

WHEN marks >= 85 THEN 'A'

WHEN marks >= 60 THEN 'B'

ELSE 'C'

END AS grade

FROM students;

OUTPUT:
roll_no name marks grade
------- ----- ----- -----
R001 Amit 85 A
R002 Priya 65 B
R003 Rahul 45 C
R004 Sneha 92 A
R005 Vikram 55 C

Dept. of CSC 26
 DBMS Lab

Result:
This experiment demonstrated the simulation of stored function logic using CASE statements in
SQLite to classify data.

Experiment 10
Cursors
Statement:
Use cursors with parameters, FOR UPDATE, WHERE CURRENT OF, and cursor
variables.
Explanation of the Module:
● Cursors: SQLite doesn’t support cursors or FOR UPDATE. A single
UPDATE achieves the same result.
● Transaction: Wrapped in BEGIN TRANSACTION and COMMIT.
● Verification: Increases marks by 5 for students with marks > 60 (e.g.,
R001: 85 → 90, R002: 65 → 70).
Theory:
Cursors in PL/SQL allow row-by-row processing of query results. Parameterized
cursors add filtering flexibility.
FOR UPDATE locks selected rows, while WHERE CURRENT OF targets the
current cursor row for updates.
REF CURSORs (cursor variables) enable dynamic queries and reusable result sets.

Program or Code:
-- Create employees table for demonstration

CREATE TABLE employees (

emp_id NUMBER PRIMARY KEY,

emp_name VARCHAR2(50),

Dept. of CSC 27
 DBMS Lab

salary NUMBER,

dept_id NUMBER,

status VARCHAR2(20) DEFAULT 'ACTIVE'

);
-- Insert sample data

INSERT INTO employees VALUES (1, 'John Doe', 50000, 10, 'ACTIVE');

INSERT INTO employees VALUES (2, 'Jane Smith', 60000, 20, 'ACTIVE');

INSERT INTO employees VALUES (3, 'Bob Johnson', 45000, 10, 'INACTIVE');

INSERT INTO employees VALUES (4, 'Alice Brown', 55000, 30, 'ACTIVE');

-- Create audit_log table to track updates

CREATE TABLE audit_log (

log_id NUMBER GENERATED ALWAYS AS IDENTITY,

emp_id NUMBER,

action VARCHAR2(100),

old_salary NUMBER,

new_salary NUMBER,

log_date DATE DEFAULT SYSDATE

);

-- PL/SQL block implementing cursors

DECLARE

-- Define a cursor with parameters

CURSOR emp_cursor (p_dept_id IN NUMBER, p_status IN VARCHAR2) IS

Dept. of CSC 28
 DBMS Lab

SELECT emp_id, emp_name, salary, dept_id

FROM employees

WHERE dept_id = p_dept_id

AND status = p_status

FOR UPDATE; -- Lock rows for update

-- Cursor variables (REF CURSOR)

TYPE emp_ref_cursor IS REF CURSOR;

v_ref_cursor emp_ref_cursor;

-- Variables to hold cursor data

v_emp_id NUMBER;

v_emp_name VARCHAR2(50);

v_salary NUMBER;

v_dept_id NUMBER;

v_old_salary NUMBER;

v_new_salary NUMBER;

v_processed_count NUMBER := 0;

BEGIN

-- Part 1: Using parameterized cursor with FOR UPDATE and WHERE CURRENT OF

DBMS_OUTPUT.PUT_LINE('Processing employees in department 10 with status ACTIVE');

OPEN emp_cursor(10, 'ACTIVE');

LOOP

FETCH emp_cursor INTO v_emp_id, v_emp_name, v_salary, v_dept_id;

Dept. of CSC 29
 DBMS Lab

EXIT WHEN emp_cursor%NOTFOUND;

-- Store old salary

v_old_salary := v_salary;

-- Apply salary increase

v_new_salary := v_salary * 1.10; -- 10% raise

-- Update using WHERE CURRENT OF

UPDATE employees

SET salary = v_new_salary

WHERE CURRENT OF emp_cursor;

-- Log the update

INSERT INTO audit_log (emp_id, action, old_salary, new_salary)

VALUES (v_emp_id, 'Salary increased by 10%', v_old_salary, v_new_salary);

v_processed_count := v_processed_count + 1;

END LOOP;

CLOSE emp_cursor;

-- Part 2: Using cursor variable (REF CURSOR)

DBMS_OUTPUT.PUT_LINE('Processing employees with REF CURSOR for department

20');

OPEN v_ref_cursor FOR

SELECT emp_id, emp_name, salary, dept_id

FROM employees

WHERE dept_id = 20

FOR UPDATE;

Dept. of CSC 30
 DBMS Lab

LOOP

FETCH v_ref_cursor INTO v_emp_id, v_emp_name, v_salary, v_dept_id;

EXIT WHEN v_ref_cursor%NOTFOUND;

-- Store old salary

v_old_salary := v_salary;

-- Apply different salary adjustment

v_new_salary := v_salary + 5000; -- Flat $5000 raise

-- Update using WHERE CURRENT OF (works with REF CURSOR in Oracle)

UPDATE employees

SET salary = v_new_salary

WHERE CURRENT OF v_ref_cursor;

-- Log the update

INSERT INTO audit_log (emp_id, action, old_salary, new_salary)

VALUES (v_emp_id, 'Salary increased by $5000', v_old_salary, v_new_salary);

v_processed_count := v_processed_count + 1;

END LOOP;

CLOSE v_ref_cursor;

-- Commit changes

COMMIT;

-- Display summary

DBMS_OUTPUT.PUT_LINE('Total employees processed: ' || v_processed_count);

EXCEPTION

Dept. of CSC 31
 DBMS Lab

WHEN OTHERS THEN

-- Roll back on error

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);

INSERT INTO audit_log (emp_id, action)

VALUES (NULL, 'Error during processing: ' || SQLERRM);

COMMIT; -- Commit the log entry

END;

/
-- Enable output for testing

SET SERVEROUTPUT

ON;

-- Verify results

SELECT * FROM employees;

SELECT * FROM audit_log;

OUTPUT:
Total employees processed: 2'
------------------------------
Total employees processed: 2

emp_id emp_name salary dept_id status

------ ------------ ---------- ------- --------
1 John Doe 55000 10 ACTIVE
2 Jane Smith 65000 20 ACTIVE
3 Bob Johnson 45000 10 INACTIVE
4 Alice Brown 55000 30 ACTIVE

Dept. of CSC 32
 DBMS Lab

Result:
This experiment demonstrated cursor usage with FOR UPDATE, WHERE CURRENT OF, and
REF CURSORs for dynamic row-level processing and update tracking.

Experiment 11
Triggers
Statement:
Use BEFORE, AFTER, ROW, STATEMENT, and INSTEAD OF triggers.
Explanation of the Module:
● Triggers: SQLite supports BEFORE, AFTER, and INSTEAD OF
triggers but not statement-level triggers. This BEFORE trigger logs
updates row-by-row.
● Transaction: Ensures the UPDATE is committed.
● Verification: Updates R001’s marks (e.g., 85 → 95) and logs the action in student_log.
Theory:
Triggers are special procedures that execute automatically in response to certain
events on a table or view. BEFORE and AFTER define the timing. ROW and
STATEMENT define the scope. SQLite supports BEFORE and AFTER triggers per
row. INSTEAD OF is used on views.

Dept. of CSC 33
 DBMS Lab

Program or Code:
-- Create log table

CREATE TABLE student_log (

log_id INTEGER PRIMARY KEY AUTOINCREMENT,

roll_no TEXT,

action TEXT,

log_date TEXT

);

-- BEFORE trigger (SQLite doesn’t distinguish row/statement triggers explicitly)

CREATE TRIGGER before_update_stud

BEFORE UPDATE ON students

FOR EACH ROW

BEGIN

INSERT INTO student_log (roll_no, action, log_date)

VALUES (OLD.roll_no, 'Marks Updated', datetime('now'));

END;

-- Test the trigger

BEGIN TRANSACTION;

UPDATE students SET marks = marks + 10 WHERE roll_no = 'R001';

COMMIT;

Dept. of CSC 34
 DBMS Lab

-- Verify

SELECT * FROM students;

SELECT * FROM student_log;

Full Working Code (Combined)

-- Drop existing tables

DROP TABLE IF EXISTS students;

DROP TABLE IF EXISTS student_log;

-- Create students table

CREATE TABLE students (

roll_no TEXT PRIMARY KEY,

name TEXT,

marks INTEGER CHECK (marks >= 0 AND marks <= 100)

);

-- Insert initial data

INSERT INTO students VALUES ('R001', 'Amit', 85);

INSERT INTO students VALUES ('R002', 'Priya', 65);

INSERT INTO students VALUES ('R003', 'Rahul',

45);

Dept. of CSC 35
 DBMS Lab

-- Experiment 6: Transactions

BEGIN TRANSACTION;

SAVEPOINT start_point;

INSERT INTO students VALUES ('R004', 'Sneha', 92);

SAVEPOINT after_first_insert;

INSERT INTO students VALUES ('R005', 'Vikram', 55);

SELECT roll_no, name, marks FROM students WHERE marks >= 60 LIMIT 1;

COMMIT;

-- Experiment 7: CASE logic

SELECT

roll_no, name, marks,

CASE WHEN marks >= 60 THEN CASE WHEN marks >= 85 THEN 'A' ELSE 'B' END
ELSE 'C' END AS nested_grade,

COALESCE(NULLIF(CASE WHEN marks > 90 THEN 'A+' END, 'A+'), 'B') AS

special_grade

FROM students;

-- Experiment 8: Simulated loops

BEGIN TRANSACTION;

INSERT INTO students VALUES ('R006', 'Neha',

88); INSERT INTO students VALUES ('R007',

'Kiran', 75); COMMIT;

Dept. of CSC 36
 DBMS Lab

-- Experiment 9: Simulated function

SELECT roll_no, name, marks,

CASE WHEN marks >= 85 THEN 'A' WHEN marks >= 60 THEN 'B' ELSE 'C' END AS
grade

FROM students;

-- Experiment 10: Simulated cursor

BEGIN TRANSACTION;

UPDATE students SET marks = marks + 5 WHERE marks >

60; COMMIT;

-- Experiment 11: Trigger

CREATE TABLE student_log (

log_id INTEGER PRIMARY KEY AUTOINCREMENT,

roll_no TEXT,

action TEXT,

Dept. of CSC 37
 DBMS Lab

);

CREATE TRIGGER before_update_stud

BEFORE UPDATE ON students

FOR EACH ROW

BEGIN

INSERT INTO student_log (roll_no, action, log_date)

VALUES (OLD.roll_no, 'Marks Updated', datetime('now'));

END;

BEGIN TRANSACTION;

UPDATE students SET marks = marks + 10 WHERE roll_no = 'R001';

COMMIT;

-- Final verification

SELECT * FROM students;

SELECT * FROM student_log;

OUTPUT:
Roll_no name marks
------- ----- -----
R001 Amit 100
R002 Priya 70
R003 Rahul 45
R004 Sneha 97
R005 Vikram 60
R006 Neha 93
R007 Kiran 80

Dept. of CSC 38
 DBMS Lab

Result:
Observed the BEFORE and AFTER Triggers, Row and Statement Triggers and INSTEAD OF
Triggers.

Experiment 12

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

1) Statement:
Create a table and perform search operations using both indexing and non-indexing techniques to
understand their impact on query performance.

2) Theory:
In relational databases, indexes are special lookup tables that the database search engine can use
to speed up data retrieval. They work similarly to an index in a book, allowing the database to
find data quickly without scanning every row in a table.
 Non-Indexed Search (Full Table Scan): Without an index, the database performs a full
table scan, examining each row to find matching records. This approach can be slow,
especially for large tables.
 Indexed Search: By creating an index on one or more columns, the database can use the
index to quickly locate data, significantly improving query performance. However,
indexes come with overhead; they consume additional storage and can slow down write
operations like INSERT, UPDATE, and DELETE because the index must be updated
accordingly.

3) Program or Code:
Below is an example demonstrating the creation of a table, performing search operations without
an index, creating an index, and then performing search operations with the index.

-- Step 1: Create the 'Employees' table

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2)
);

Dept. of CSC 39
 DBMS Lab

-- Step 2: Insert sample data into 'Employees'

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary) VALUES
(1, 'John', 'Doe', 'HR', 60000),
(2, 'Jane', 'Smith', 'Finance', 65000),
(3, 'Emily', 'Johnson', 'IT', 70000),
(4, 'Michael', 'Brown', 'Marketing', 62000),
(5, 'Chris', 'Davis', 'Sales', 61000);

-- Step 3: Perform a search operation without an index

-- This will result in a full table scan
EXPLAIN ANALYZE
SELECT * FROM Employees WHERE LastName = 'Johnson';

-- Step 4: Create an index on the 'LastName' column

CREATE INDEX idx_LastName ON Employees (LastName);

-- Step 5: Perform the same search operation with the index

-- The database can now use the index to find the record more efficiently
EXPLAIN ANALYZE
SELECT * FROM Employees WHERE LastName = 'Johnson';

4) OUTPUT:
The EXPLAIN ANALYZE statements provide execution plans for the queries, showing how the
database processes them.
 Without Index:
pgsql
CopyEdit
Seq Scan on Employees (cost=0.00..1.06 rows=1 width=52) (actual time=0.012..0.013 rows=1
loops=1)
Filter: (LastName = 'Johnson'::text)
Rows Removed by Filter: 4
Planning Time: 0.032 ms
Execution Time: 0.025 ms
This indicates a sequential scan (full table scan), where the database examines each row to find
matches.
 With Index:
pgsql
CopyEdit
Index Scan using idx_LastName on Employees (cost=0.13..8.15 rows=1 width=52) (actual
time=0.008..0.009 rows=1 loops=1)
Index Cond: (LastName = 'Johnson'::text)
Planning Time: 0.095 ms
Execution Time: 0.018 ms
Here, the database uses the index (idx_LastName) to perform an index scan, which is more

Dept. of CSC 40
 DBMS Lab

efficient than a sequential scan.

5) Result:
The example demonstrates that creating an index on the LastName column allows the database to
perform search operations more efficiently. Without the index, the database performs a full table
scan, which can be slow for large tables. With the index, the database can quickly locate the
desired records using the index, resulting in faster query performance.

6) Resource:
For further reading on SQL indexing techniques and best practices, refer to the following
resources:
 SQL CREATE INDEX Statement - W3Schools
 Using SQL CREATE INDEX to create clustered and non-clustered indexes - SQLShack
 Indexing Essentials in SQL - Atlassian
These resources provide comprehensive information on creating and using indexes to optimize
database performance.

Experiment 13
13. Write a Java program that connects to a database using JDBC

1) Statement:
Write a Java program that establishes a connection to a database using JDBC (Java Database
Connectivity).

2) Theory:
Java Database Connectivity (JDBC) is an API that enables Java applications to interact with
relational databases. It provides methods for querying and updating data in a database. The
primary components of JDBC include:
 DriverManager: Manages a list of database drivers. It establishes a connection between
a database and the appropriate driver.
 Connection: Represents a connection to a specific database.
 Statement: Used for executing static SQL statements and returning the results.
 PreparedStatement: A subclass of Statement that allows precompiled SQL statements
with parameters
 ResultSet: Represents the result set of a query.
To connect to a database using JDBC, the following steps are typically followed:

Dept. of CSC 41
 DBMS Lab

1. Load the JDBC Driver: This step is optional with modern JDBC versions as drivers are
automatically loaded when the application starts.
2. Establish a Connection: Use the DriverManager.getConnection() method to create a
Connection object by providing the database URL, username, and password.
3. Create a Statement: Create a Statement or PreparedStatement object to execute SQL
queries.
4. Execute the Query: Use the executeQuery() method for SELECT statements or
executeUpdate() for INSERT, UPDATE, or DELETE statements.
5. Process the Results: Iterate through the ResultSet to retrieve data returned by the query.
6. Close the Resources: Close the ResultSet, Statement, and Connection objects to free up
database resources.

3) Program or Code:
Below is a Java program that demonstrates how to connect to a MySQL database using JDBC.
This example assumes that you have the MySQL Connector/J driver in your classpath.
java
CopyEdit
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcExample {

// JDBC URL, username, and password of MySQL server
private static final String URL = "jdbc:mysql://localhost:3306/your_database_name";
private static final String USER = "your_username";
private static final String PASSWORD = "your_password";

public static void main(String[] args) {

Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;

try {
// Establish the connection
connection = DriverManager.getConnection(URL, USER, PASSWORD);

// Create a statement
statement = connection.createStatement();

// Execute a query
String sql = "SELECT id, name, email FROM users";
resultSet = statement.executeQuery(sql);

Dept. of CSC 42
 DBMS Lab

// Process the result set

while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// Close resources
try {
if (resultSet != null) resultSet.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Note: Replace your_database_name, your_username, and your_password with your actual
database credentials.

4) OUTPUT:
ID: 1, Name: Alice, Email: alice@example.com
ID: 2, Name: Bob, Email: bob@example.com
ID: 3, Name: Carol, Email: carol@example.com

5) Result:
The Java program successfully establishes a connection to the MySQL database using JDBC,
executes a SELECT query on the users table, and retrieves and displays the results. This
demonstrates the fundamental steps involved in connecting to a database and performing basic
operations using JDBC.

6) Resource:
For further reading on JDBC and database connectivity in Java, refer to the following resources:
 JDBC Tutorial - Oracle Documentation
 JDBC Sample Code - Tutorialspoint
 Java Database Programming (JDBC) by Examples with MySQL
These resources provide comprehensive information and examples to deepen your understanding
of JDBC in Java.

Dept. of CSC 43
 DBMS Lab

Experiment 14

14. Write a Java program to connect to a database using JDBC and insert values into it

1) Statement:
Develop a Java program that establishes a connection to a database using JDBC and inserts
values into a specified table.

2) Theory:
Java Database Connectivity (JDBC) is an API that enables Java applications to interact with
relational databases. To insert data into a database using JDBC, the following steps are typically
followed:
1. Load the JDBC Driver: This step is optional with modern JDBC versions as drivers are
automatically loaded when the application starts.
2. Establish a Connection: Use the DriverManager.getConnection() method to create a
Connection object by providing the database URL, username, and password.
3. Create a Statement or PreparedStatement: Use a PreparedStatement object to execute
parameterized SQL queries, which helps prevent SQL injection and improves
performance.
4. Set Parameters and Execute the Query: Set the parameters for the PreparedStatement
and execute the insert operation using the executeUpdate() method.
5. Close the Resources: Close the PreparedStatement and Connection objects to free up
database resources.

3) Program or Code:
Below is a Java program that connects to a MySQL database and inserts a new record into the
users table:
java
CopyEdit
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JdbcInsertExample {

// JDBC URL, username, and password of MySQL server
private static final String URL = "jdbc:mysql://localhost:3306/your_database_name";
private static final String USER = "your_username";
private static final String PASSWORD = "your_password";

// SQL Insert Query

private static final String INSERT_QUERY = "INSERT INTO users (id, name, email)

Dept. of CSC 44
 DBMS Lab

VALUES (?, ?, ?)";

public static void main(String[] args) {

try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement preparedStatement =
connection.prepareStatement(INSERT_QUERY)) {

// Set parameters for the insert query

preparedStatement.setInt(1, 1); // id
preparedStatement.setString(2, "John Doe"); // name
preparedStatement.setString(3, "john.doe@example.com"); // email

// Execute the insert operation

int rowsInserted = preparedStatement.executeUpdate();
if (rowsInserted > 0) {
System.out.println("A new user was inserted successfully.");
}

} catch (SQLException e) {
e.printStackTrace();
}
}
}
Note: Replace your_database_name, your_username, and your_password with your actual
database credentials. Ensure that the users table exists in your database with columns id, name,
and email.

4) OUTPUT:
Upon successful execution, the program will output:
sql
CopyEdit
A new user was inserted successfully.

5) Result:
The Java program successfully establishes a connection to the MySQL database using JDBC and
inserts a new record into the users table.

6) Resource:
For further reading on inserting records using JDBC, refer to:
 JDBC Insert Records - Tutorialspoint

Dept. of CSC 45
 DBMS Lab

Experiment 15

15. Write a Java program to connect to a database using JDBC and delete values from it

1) Statement:
Develop a Java program that establishes a connection to a database using JDBC and deletes
specific records from a designated table.

2) Theory:
Java Database Connectivity (JDBC) is an API that enables Java applications to interact with
relational databases. To delete records from a database using JDBC, the following steps are
typically followed:
1. Load the JDBC Driver: Modern JDBC versions automatically load the driver when the
application starts, so this step is often implicit.
2. Establish a Connection: Utilize the DriverManager.getConnection() method to create a
Connection object by providing the database URL, username, and password.
3. Create a PreparedStatement: Use a PreparedStatement object to execute parameterized
SQL delete queries. This approach enhances security by preventing SQL injection and
improves performance.
4. Set Parameters and Execute the Query: Assign values to the parameters in the
PreparedStatement and execute the delete operation using the executeUpdate() method.
5. Close the Resources: Properly close the PreparedStatement and Connection objects to
release database resources.

3) Program or Code:
Below is a Java program that connects to a MySQL database and deletes a record from the users
table based on the user ID:
java
CopyEdit
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JdbcDeleteExample {

// JDBC URL, username, and password of MySQL server
private static final String URL = "jdbc:mysql://localhost:3306/your_database_name";
private static final String USER = "your_username";
private static final String PASSWORD = "your_password";

// SQL Delete Query

private static final String DELETE_QUERY = "DELETE FROM users WHERE id = ?";

Dept. of CSC 46
 DBMS Lab

public static void main(String[] args) {

int userIdToDelete = 1; // Specify the user ID to delete

try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);

PreparedStatement preparedStatement =
connection.prepareStatement(DELETE_QUERY)) {

// Set parameter for the delete query

preparedStatement.setInt(1, userIdToDelete);

// Execute the delete operation

int rowsDeleted = preparedStatement.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("A user was deleted successfully.");
} else {
System.out.println("No user found with the given ID.");
}

} catch (SQLException e) {
e.printStackTrace();
}
}
}
Note: Replace your_database_name, your_username, and your_password with your actual
database credentials. Ensure that the users table exists in your database with a column named id.

4) OUTPUT:
If a user with the specified ID exists and is deleted, the program will output:
css
CopyEdit
A user was deleted successfully.
If no user with the specified ID is found, the program will output:
pgsql
CopyEdit
No user found with the given ID.

5) Result:
The Java program successfully establishes a connection to the MySQL database using JDBC and
deletes a specified record from the users table. This demonstrates the fundamental steps involved
in connecting to a database and performing a delete operation using JDBC.

6) Resource:
For further reading on deleting records using JDBC, refer to the following resource:
 JDBC Delete Records - Tutorialspoint

Dept. of CSC 47
 DBMS Lab

This resource provides comprehensive information and examples to deepen your understanding
of deleting records with JDBC in Java.

Dept. of CSC 48