1

Database Management System

(AIDS254)

NAME: Tushar Kaushik

ROLL NO.: 00715611923
SECTION: S-12
BRANCH: Artificial Intelligence And Data
Science
SUBMITTED TO: Mrs. Yatu Rani

Tushar Kaushik 00715611923 AIDS(S-12)

 2

S.No Name of Experiment Implementation Evaluation Date Sign Remarks

Date
1. Study and practice various
database management systems
like
MySQL/Oracle/PostgreSQL/SQL
Server and others.
2. Implement simple queries of DDL
and DML.
3. Implement basic queries to
Create, Insert, Update, Delete
and Select Statements for two
different scenarios (For instance:
Bank, College etc.)
4. Implement queries including various
functions- mathematical, string, date
etc.
5. Implement queries including Sorting ,
Grouping & Subqueries- like any , all
, exists ,not exists
6. Implement queries including various
Set operations (Union , Intersection ,
Except etc.).
7. Implement various JOIN operations-
( Inner, Outer)
8. Write a PL/SQL program using FOR
loop to insert ten rows into a
database table.
9. Given the table EMPLOYEE ( Emp
No , Name , Salary , Designation ,
DeptID) write a cursor to select the
five highest-paid employees from the
table.
10. Illustrate how you can embed
PL/SQL in a high-level host
language such as C/Java and
demonstrates how a banking debit
transaction might be done.

Tushar Kaushik 00715611923 AIDS(S-12)

 3

EXPERIMENT 1
Aim: Study and practice various database management systems like MySQL/Oracle/PostgreSQL/SQL
Server and others.
1. MySQL
 Type: Open-source RDBMS
 Developer: Oracle Corporation
 Language: SQL
 Popular Use Cases: Web applications (e.g., WordPress, Joomla), LAMP stack

Key Features:

 Fast and reliable for read-heavy applications

 Cross-platform (Windows, Linux, macOS)
 Supports stored procedures, triggers, views
 ACID compliant (InnoDB engine)
 Scalable with replication and clustering

2. Oracle Database

 Type: Proprietary RDBMS

 Developer: Oracle Corporation
 Popular Use Cases: Enterprise applications, banking systems, data warehousing

Key Features:

 Highly secure, robust, and scalable

 Supports PL/SQL (Procedural Language extension to SQL)
 Multi-version concurrency control
 Advanced features like partitioning, materialized views, in-memory database
 Strong backup and recovery mechanisms (RMAN)

3. PostgreSQL

 Type: Open-source Object-Relational DBMS

 Developer: PostgreSQL Global Development Group
 Popular Use Cases: Complex analytical systems, geospatial databases (PostGIS)

Key Features:

 Full support for SQL standards

 Extensible with custom functions and data types
 MVCC for high concurrency
 Supports JSON, XML for semi-structured data
 Strong community and third-party tool ecosystem

4. Microsoft SQL Server

 Type: Proprietary RDBMS

Tushar Kaushik 00715611923 AIDS(S-12)

 4

 Developer: Microsoft
 Popular Use Cases: Enterprise applications, business intelligence, .NET apps

Key Features:

 T-SQL (Transact-SQL) for advanced querying

 Integration with Microsoft ecosystem (Excel, Azure, Power BI)
 Built-in tools like SQL Server Management Studio (SSMS)
 High availability via Always On and failover clustering
 Reporting and analytics through SSRS, SSIS, and SSAS

5. SQLite

 Type: Embedded RDBMS (serverless)

 Developer: D. Richard Hipp
 Popular Use Cases: Mobile apps (Android, iOS), IoT devices, browsers

Key Features:

 Lightweight and self-contained

 No need for separate server process
 Uses a single disk file for storage
 Ideal for local storage and development
 ACID compliant with minimal setup

6. MariaDB
 Type: Open-source RDBMS (MySQL fork)
 Developer: MariaDB Corporation
 Popular Use Cases: Replaces MySQL in many web applications

Key Features:
 Backward-compatible with MySQL
 Enhanced security, performance, and storage engines (Aria, ColumnStore)
 Open-source development community
 Active development with frequent updates

7. IBM Db2

 Type: Proprietary RDBMS

 Developer: IBM
 Popular Use Cases: Large-scale enterprise systems

Key Features:
 High performance for OLTP and OLAP
 Advanced data compression
 Integrated with AI and machine learning
 Support for JSON, XML, and spatial data
 High availability and disaster recovery option

Tushar Kaushik 00715611923 AIDS(S-12)

 5

EXPERIMENT 2
Aim: Implement simple queries of DDL and DML.
CODE:
DDL Queries (Data Definition Language)
DROP DATABASE IF EXISTS Schoolcd;
CREATE DATABASE Schoolcd;
USE Schoolcd;
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Course VARCHAR(50),
Email VARCHAR(100)
);

DML Queries (Data Manipulation Language)

INSERT INTO Students (StudentID, Name, Age, Course, Email)
VALUES (1, 'Rakesh Singh', 21, 'B.Tech AI', 'raka@email.com');
SELECT * FROM Students;

EXPLANATION:
1. DROP DATABASE IF EXISTS Schoolcd;
Removes the Schoolcd database if it already exists to avoid conflicts.

2. CREATE DATABASE Schoolcd;

Creates a new database named Schoolcd.
3. USE Schoolcd;
Switches the active database to Schoolcd for subsequent operations.
4. CREATE TABLE Students
Creates a Students table with columns:
a. StudentID (Primary Key)
b. Name (VARCHAR)
c. Age (INT)
d. Course (VARCHAR)
e. Email (VARCHAR)
5. INSERT INTO Students
Inserts a new student record with values for StudentID, Name, Age, Course, and Email.
6. SELECT * FROM Students;
Retrieves all records from the Students table to verify the inserted data.

OUTPUT:

Tushar Kaushik 00715611923 AIDS(S-12)

 6

EXPERIMENT 3
Aim: Implement basic queries to Create, Insert, Update, Delete and Select Statements for two different
scenarios (For instance: Bank, College etc.)
CODE: (A) BANKDB
DROP DATABASE IF EXISTS BankDB;
CREATE DATABASE BankDB;
USE BankDB;

CREATE TABLE Accounts (

AccountID INT PRIMARY KEY,
AccountHolder VARCHAR(100),
Balance DECIMAL(10, 2)
);

INSERT INTO Accounts (AccountID, AccountHolder, Balance)

VALUES
(101, 'Doll', 5000.00),
(102, 'RAKA', 50000.00);

UPDATE Accounts
SET Balance = 6000.00
WHERE AccountID = 101;

DELETE FROM Accounts

WHERE AccountID = 101;

SELECT * FROM Accounts;

Explanation:

 DROP DATABASE IF EXISTS BankDB;

Drops the BankDB database if it already exists to avoid conflicts.
 CREATE DATABASE BankDB;
Creates a new database named BankDB.
 USE BankDB;
Switches to the BankDB database for further operations.
 CREATE TABLE Accounts
Creates an Accounts table with three columns:
AccountID (Primary Key)
AccountHolder
Balance
 INSERT INTO Accounts
Inserts two records into the Accounts table:
Account 101 with balance 5000.00 and holder 'Doll'
Account 102 with balance 50000.00 and holder 'RAKA'
 UPDATE Accounts
Updates the balance of Account 101 to 6000.00.
 DELETE FROM Accounts
Deletes the record for Account 101.
 SELECT * FROM Accounts
Selects and displays all records from the Accounts table (which should show only Account 102 after
deletion).

Tushar Kaushik 00715611923 AIDS(S-12)

 7

OUTPUT:

Tushar Kaushik 00715611923 AIDS(S-12)

 8

EXPERIMENT 3
Aim: Implement basic queries to Create, Insert, Update, Delete and Select Statements for two different
scenarios (For instance: Bank, College etc.)

CODE: (B) COLLAGEDB

DROP DATABASE IF EXISTS CollegeDB;
CREATE DATABASE CollegeDB;
USE CollegeDB;

CREATE TABLE Students (

StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Major VARCHAR(50)
);

INSERT INTO Students (StudentID, Name, Age, Major)

VALUES
(1, 'RAKA', 20, 'Computer Science'),
(2, 'DOLL', 20, 'Computer Science');

UPDATE Students
SET Age = 21
WHERE StudentID = 1;

DELETE FROM Students

WHERE StudentID = 1;

SELECT * FROM Students;

EXPLANATION:

 DROP DATABASE: Deletes CollegeDB if it already exists.

 CREATE DATABASE: Creates a new database.
 USE CollegeDB: Switches to this database for executing queries.
 CREATE TABLE: Defines a Students table with 4 columns.
 INSERT INTO: Inserts two distinct student records (IDs must be unique).
 UPDATE: Changes the age of the student with StudentID = 1.
 DELETE: Deletes that same student.
 SELECT: Displays the remaining records (only StudentID = 2 will be shown).

OUTPUT:

Tushar Kaushik 00715611923 AIDS(S-12)

 9

EXPERIMENT 4

Aim: Implement queries including various functions- mathematical, string, date etc.
CODE:
DROP DATABASE IF EXISTS FunctionDB;
CREATE DATABASE FunctionDB;
USE FunctionDB;

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10, 2),
JoinDate DATE
);

INSERT INTO Employee (EmpID, Name, Salary, JoinDate)

VALUES
(1, 'Rakesh singh', 25000.75, '2023-04-10'),
(2, 'Sakshi jha', 30500.50, '2022-10-15'),
(3, 'Doll', 18000.00, '2021-06-05');

SELECT UPPER(Name) AS UpperName FROM Employee;

SELECT LENGTH(Name) AS NameLength FROM Employee;
SELECT ROUND(Salary) AS RoundedSalary FROM Employee;
SELECT SALARY * 0.10 AS Bonus FROM Employee;
SELECT DATEDIFF(CURDATE(), JoinDate) AS DaysWorked FROM Employee;
SELECT NOW() AS CurrentDateTime;
SELECT CONCAT(Name, ' - ID: ', EmpID) AS EmployeeDetails FROM Employee;
Explanation:

1. DROP DATABASE IF EXISTS FunctionDB;

Deletes the database if it exists.
2. CREATE DATABASE FunctionDB;
Creates a new database called FunctionDB.
3. USE FunctionDB;
Selects FunctionDB for further operations.
4. CREATE TABLE Employee
Defines a table with columns: EmpID, Name, Salary, and JoinDate.
5. INSERT INTO Employee
Adds 3 employee records with names, salaries, and joining dates.
6. SELECT UPPER(Name)
Converts employee names to uppercase.
7. SELECT LENGTH(Name)
Gets the number of characters in each name.
8. SELECT ROUND(Salary)
Rounds off salaries to the nearest whole number.
9. SELECT SALARY * 0.10
Calculates 10% bonus on each salary.
10. SELECT DATEDIFF(CURDATE(), JoinDate)
Calculates number of days each employee has worked.
11. SELECT NOW()
Displays current date and time.

Tushar Kaushik 00715611923 AIDS(S-12)

 10

12. SELECT CONCAT(Name, ' - ID: ', EmpID)

Combines name and ID into a single formatted string.

OUTPUT:

Tushar Kaushik 00715611923 AIDS(S-12)

 11

EXPERIMENT 5

Aim: Implement queries including Sorting, Grouping and Subqueries- like any, all, exists, not exists
CODE:
DROP DATABASE IF EXISTS SalesDB;
CREATE DATABASE SalesDB;
USE SalesDB;

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerName VARCHAR(100),
Amount DECIMAL(10, 2),
OrderDate DATE
);

INSERT INTO Orders (OrderID, CustomerName, Amount, OrderDate)

VALUES
(1, 'Sakshi Jha', 500.00, '2023-07-01'),
(2, 'Rakesh Singh', 750.00, '2023-07-02'),
(3, 'Sakshi Jha', 300.00, '2023-07-03'),
(4, 'Ankur Yadav', 1200.00, '2023-07-04');

SELECT * FROM Orders ORDER BY Amount DESC;

SELECT CustomerName, SUM(Amount) AS TotalAmount FROM Orders GROUP BY CustomerName;

SELECT * FROM Orders

WHERE Amount > ANY (SELECT Amount FROM Orders WHERE CustomerName = 'Rakesh Singh');

SELECT * FROM Orders

WHERE Amount < ALL (SELECT Amount FROM Orders WHERE CustomerName = 'Ankur Yadav');

SELECT * FROM Orders o

WHERE EXISTS (SELECT 1 FROM Orders i WHERE i.CustomerName = 'Sakshi Jha' AND i.OrderID <>
o.OrderID);

SELECT * FROM Orders o

WHERE NOT EXISTS (SELECT 1 FROM Orders i WHERE i.CustomerName = 'Chaitanya');

Explanation:

1. DROP DATABASE IF EXISTS SalesDB;

Deletes the database SalesDB if it already exists.
2. CREATE DATABASE SalesDB;
Creates a new database named SalesDB.
3. USE SalesDB;
Selects SalesDB for all following operations.
4. CREATE TABLE Orders
Creates a table named Orders with:
5. OrderID: unique order number.
6. CustomerName: name of the customer.
7. Amount: order amount.
8. OrderDate: date of the order.

Tushar Kaushik 00715611923 AIDS(S-12)

 12

9. INSERT INTO Orders VALUES

Adds 4 orders into the Orders table with:
10. Two orders by Sakshi Jha
11. One by Rakesh Singh
12. One by Ankur Yadav
13. SELECT * FROM Orders ORDER BY Amount DESC;
Retrieves all orders sorted by Amount in descending order.
14. SELECT CustomerName, SUM(Amount) GROUP BY CustomerName;
Groups records by customer and calculates total amount spent by each.
15. WHERE Amount > ANY
Selects orders where Amount is greater than any amount ordered by Rakesh Singh.
16. WHERE Amount < ALL
Selects orders where Amount is less than all amounts ordered by Ankur Yadav.
17. WHERE EXISTS
Selects orders only if Sakshi Jha has more than one order (at least one other exists).
18. WHERE NOT EXISTS
Selects orders only if no order exists for Chaitanya.

OUTPUT:

Tushar Kaushik 00715611923 AIDS(S-12)

 13

EXPERIMENT 6

Aim: Implement queries including various Set operations (Union, Intersection, Except etc.).
CODE:
DROP DATABASE IF EXISTS CollegeDB;
CREATE DATABASE CollegeDB;
USE CollegeDB;

CREATE TABLE CourseA (

StudentName VARCHAR(100)
);
CREATE TABLE CourseB (
StudentName VARCHAR(100)
);
INSERT INTO CourseA (StudentName)
VALUES
('Sakshi Jha'),
('Rakesh Singh'),
('Ankur Yadav'),
('Chaitanya');
INSERT INTO CourseB (StudentName)
VALUES
('Rakesh Singh'),
('Chaitanya'),
('Vishal Verma'),
('Aachal Rana');
SELECT StudentName FROM CourseA
UNION
SELECT StudentName FROM CourseB;
SELECT StudentName FROM CourseA
INTERSECT
SELECT StudentName FROM CourseB;
SELECT StudentName FROM CourseA
EXCEPT
SELECT StudentName FROM CourseB;
import java.util.Scanner;
public class StringOperations {
String str1, str2;
void getInput() {
Scanner sc = new Scanner(System.in);
System.out.print("Enter first string: ");
str1 = sc.nextLine();
System.out.print("Enter second string: ");
str2 = sc.nextLine();
}
void checkEqual() {
if(str1.equals(str2))
System.out.println("Strings are equal");
else
System.out.println("Strings are not equal");
}
void reverseString() {
String reversed = new StringBuilder(str1).reverse().toString();
Tushar Kaushik 00715611923 AIDS(S-12)
 14

System.out.println("Reversed string: " + reversed);

}
void changeCase() {
String upper = str1.toUpperCase();
String lower = str1.toLowerCase();
System.out.println("Uppercase: " + upper);
System.out.println("Lowercase: " + lower);
}
public static void main(String[] args) {
StringOperations obj = new StringOperations();
obj.getInput();
obj.checkEqual();
obj.reverseString();
obj.changeCase();
}
}

Explanation:

1. DROP DATABASE IF EXISTS CollegeDB;

Deletes existing CollegeDB database.
2. CREATE DATABASE CollegeDB;
Creates a new CollegeDB.
3. USE CollegeDB;
Selects the active database.
4. CREATE TABLE CourseA / CourseB
Creates two tables representing students in two different courses.
5. INSERT INTO CourseA / CourseB
Adds student names to both courses.
6. Common: Rakesh Singh, Chaitanya
7. Unique: Sakshi Jha, Ankur Yadav (in A), Neha Verma, Amit Rana (in B)
8. UNION
Combines all unique student names from both tables (removes duplicates).
9. INTERSECT
Shows only names that are common in both courses.
10. EXCEPT
Returns names in CourseA but not in CourseB.

OUTPUT:

Tushar Kaushik 00715611923 AIDS(S-12)

 15

EXPERIMENT 7

Aim: Implement various JOIN operations- (Inner, Outer).

CODE:
DROP DATABASE IF EXISTS UniversityDB;
CREATE DATABASE UniversityDB;
USE UniversityDB;

CREATE TABLE Departments (

DeptID INT PRIMARY KEY,
DeptName VARCHAR(100)
);

CREATE TABLE Students (

StudentID INT PRIMARY KEY,
StudentName VARCHAR(100),
DeptID INT
);

INSERT INTO Departments (DeptID, DeptName)

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

INSERT INTO Students (StudentID, StudentName, DeptID)

VALUES
(101, 'Sakshi Jha', 1),
(102, 'Rakesh Singh', 2),
(103, 'Ankur Yadav', 4),
(104, 'Chaitanya', NULL);

SELECT Students.StudentName, Departments.DeptName

FROM Students
INNER JOIN Departments ON Students.DeptID = Departments.DeptID;

SELECT Students.StudentName, Departments.DeptName

FROM Students
LEFT JOIN Departments ON Students.DeptID = Departments.DeptID;

SELECT Students.StudentName, Departments.DeptName

FROM Students
RIGHT JOIN Departments ON Students.DeptID = Departments.DeptID;

SELECT Students.StudentName, Departments.DeptName

FROM Students
LEFT JOIN Departments ON Students.DeptID = Departments.DeptID
UNION
SELECT Students.StudentName, Departments.DeptName
FROM Students
RIGHT JOIN Departments ON Students.DeptID = Departments.DeptID;

Tushar Kaushik 00715611923 AIDS(S-12)

 16

Explanation:

1. DROP DATABASE / CREATE DATABASE / USE

Sets up a new database UniversityDB.
2. CREATE TABLE
3. Departments: Stores department ID and name.
4. Students: Stores student details with a reference to DeptID.
5. INSERT INTO
6. 3 departments.
7. 4 students:
a. 2 linked to valid departments (ID 1 & 2).
b. 1 with non-existent DeptID (4).
c. 1 with NULL DeptID.
8. INNER JOIN
Displays students who belong to a valid department (matches in both tables).
9. LEFT JOIN
Shows all students and their department if it exists. Shows NULL if no match.
10. RIGHT JOIN
Shows all departments and their students if assigned. Shows NULL if no student.
11. FULL OUTER JOIN (using UNION)
Combines results from both LEFT JOIN and RIGHT JOIN to show all students and departments,
whether matched or not.

OUTPUT:

Tushar Kaushik 00715611923 AIDS(S-12)

 17

EXPERIMENT 8

Aim: Write a PL/SQL program using FOR loop to insert ten rows into a database table.
CODE:
DROP PROCEDURE IF EXISTS InsertEmployees;

DELIMITER $$
CREATE PROCEDURE InsertEmployees()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
INSERT INTO Employee (EmployeeID, EmployeeName)
VALUES (i, CONCAT('Employee ', i));
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;
CALL InsertEmployees();
SELECT * FROM Employee;
Explanation:

1. DROP PROCEDURE IF EXISTS InsertEmployees;

Ensures any existing InsertEmployees procedure is removed before creating a new one.
2. DELIMITER $$
Changes the delimiter to $$ so MySQL can properly identify the end of the procedure block.
3. CREATE PROCEDURE InsertEmployees()
Defines the stored procedure InsertEmployees which inserts 10 rows into the Employee table.
4. Procedure Logic (Inside the BEGIN...END block):
5. DECLARE i INT DEFAULT 1;: Declares a loop counter starting at 1.
6. WHILE i <= 10 DO: Loops 10 times, inserting 10 rows into the table.
7. INSERT INTO Employee (EmployeeID, EmployeeName): Inserts a row with EmployeeID as i and
EmployeeName as 'Employee ' || i.
8. SET i = i + 1;: Increments the loop counter by 1.
9. DELIMITER ;
Resets the delimiter back to the default semicolon (;).
10. CALL InsertEmployees();
Executes the InsertEmployees procedure to insert 10 rows into the table.
11. SELECT * FROM Employee;
Retrieves all rows from the Employee table to show the output of the insertions.

OUTPUT:

Tushar Kaushik 00715611923 AIDS(S-12)

 18

EXPERIMENT 9

Aim: Given the table EMPLOYEE (Emp No, Name, Salary, Designation, DeptID), write a cursor to select the
five highest-paid employees from the table.
CODE:
Step 1: Create the Database
CREATE DATABASE IF NOT EXISTS CompanyDB;

-- Use the created database

USE CompanyDB;

Step 2: Create the EMPLOYEE table

DROP TABLE IF EXISTS EMPLOYEE;

CREATE TABLE EMPLOYEE (

EmpNo INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10, 2),
Designation VARCHAR(100),
DeptID INT
);

Step 3: Insert Sample Data into EMPLOYEE Table

INSERT INTO EMPLOYEE (EmpNo, Name, Salary, Designation, DeptID)
VALUES
(1, 'Ankur yadav', 85000.00, 'Software Engineer', 101),
(2, 'Rakesh Singh', 105000.00, 'Director', 102),
(3, 'Rajesh Singh', 120000.00, 'Senior Engineer', 103),
(4, 'Kunal', 95000.00, 'Project Manager', 101),
(5, 'Chaitaniya', 110000.00, 'Software Engineer', 104),
(6, 'Manisha Jha', 130000.00, 'Lead Developer', 103),
(7, 'Doll', 80000.00, 'Intern', 102),
(8, 'Nisha Jha', 115000.00, 'Project Manager', 104),
(9, 'Cutiepie', 105000.00, 'Business Analyst', 101),
(10, 'Tushar', 97000.00, 'Software Developer', 102);

Step 4: Drop Procedure if it Exists and Create New One

DROP PROCEDURE IF EXISTS GetTop5Employees;

DELIMITER $$

CREATE PROCEDURE GetTop5Employees()

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE empno INT;
DECLARE empname VARCHAR(100);
DECLARE empsalary DECIMAL(10,2);
DECLARE empdesignation VARCHAR(100);
DECLARE empdeptid INT;

DECLARE emp_cursor CURSOR FOR

SELECT EmpNo, Name, Salary, Designation, DeptID
FROM EMPLOYEE
Tushar Kaushik 00715611923 AIDS(S-12)
 19

ORDER BY Salary DESC LIMIT 5;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN emp_cursor;

-- Creating a temporary table to store the results

CREATE TEMPORARY TABLE IF NOT EXISTS TempTopEmployees (
EmpNo INT,
Name VARCHAR(100),
Salary DECIMAL(10, 2),
Designation VARCHAR(100),
DeptID INT
);

-- Loop to fetch data from cursor and insert into temporary table
read_loop: LOOP
FETCH emp_cursor INTO empno, empname, empsalary, empdesignation, empdeptid;
IF done THEN
LEAVE read_loop;
END IF;

-- Insert fetched data into temporary table

INSERT INTO TempTopEmployees (EmpNo, Name, Salary, Designation, DeptID)
VALUES (empno, empname, empsalary, empdesignation, empdeptid);
END LOOP;

CLOSE emp_cursor;

-- Output the results from the temporary table

SELECT * FROM TempTopEmployees;

-- Drop the temporary table after use

DROP TEMPORARY TABLE IF EXISTS TempTopEmployees;
END$$

DELIMITER ;

Step 5: Call the procedure to display the top 5 highest-paid employees

CALL GetTop5Employees();

Explanation:

1. Database Creation:
The script begins by creating a database CompanyDB (if it doesn't exist) and selects it for use.
2. Table Creation:
The EMPLOYEE table is created with columns: EmpNo (Employee ID), Name, Salary, Designation,
and DeptID.
3. Inserting Sample Data:
10 sample employee records are inserted into the EMPLOYEE table, including their names, salaries,
designations, and department IDs.
4. Procedure Creation:
a. The existing GetTop5Employees procedure is dropped if it exists.

Tushar Kaushik 00715611923 AIDS(S-12)

 20

b. A new procedure GetTop5Employees is created that:

i. Declares variables for employee details.
ii. Uses a cursor to fetch the top 5 highest-paid employees, ordered by Salary DESC.
iii. Inserts the fetched data into a temporary table TempTopEmployees.
iv. Outputs the results by selecting all data from the temporary table.
5. Calling the Procedure:
The procedure GetTop5Employees is called to fetch and display the top 5 highest-paid employees.
6. Temporary Table Usage:
a. A temporary table is created to store the top 5 highest-paid employees' details before displaying
them.
b. The temporary table is dropped after its use to clean up.
7. Result:
The procedure outputs the top 5 highest-paid employees from the EMPLOYEE table based on salary.

OUTPUT:

Tushar Kaushik 00715611923 AIDS(S-12)

 21

EXPERIMENT 10

Aim: Illustrate how you can embed PL/SQL in a high-level host language such as C/Java And demonstrates
how a banking debit transaction might be done.

Embedding PL/SQL in High-Level Host Languages (C/Java)

PL/SQL can be embedded in high-level host languages like C or Java using an OCI (Oracle Call Interface) or
JDBC (Java Database Connectivity). Here, I'll demonstrate how you can embed PL/SQL in Java to simulate
a banking debit transaction.

1. PL/SQL Block for Debit Transaction

First, we'll create a PL/SQL block that handles a simple debit transaction. It will check the account balance,
subtract the debit amount, and update the balance.

DECLARE
v_account_balance NUMBER;
BEGIN
-- Fetch account balance based on account ID
SELECT balance INTO v_account_balance
FROM accounts
WHERE account_id = :account_id;

-- Check if sufficient balance is available

IF v_account_balance >= :debit_amount THEN
-- Subtract debit amount
UPDATE accounts
SET balance = balance - :debit_amount
WHERE account_id = :account_id;

COMMIT;
DBMS_OUTPUT.PUT_LINE('Transaction Successful');
ELSE
DBMS_OUTPUT.PUT_LINE('Insufficient Balance');
END IF;
END;

1. Explanation:
a. :account_id and :debit_amount are bind variables.
b. It fetches the balance for the account with the given account_id.
c. If the balance is sufficient, the debit is processed; otherwise, an error message is printed.

2. Embedding PL/SQL in Java using JDBC

In Java, PL/SQL can be executed using JDBC by calling the PL/SQL block. The following is a simple example:

Java Code:
import java.sql.*;

Tushar Kaushik 00715611923 AIDS(S-12)

 22

public class BankingTransaction {

public static void main(String[] args) {

// Database connection details
String jdbcURL = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "your_username";
String password = "your_password";

// Debit transaction details

int accountId = 101;
double debitAmount = 1000.00;

// SQL PL/SQL block

String plsql = "DECLARE "
+ "v_account_balance NUMBER; "
+ "BEGIN "
+ "SELECT balance INTO v_account_balance FROM accounts WHERE account_id = ?; "
+ "IF v_account_balance >= ? THEN "
+ "UPDATE accounts SET balance = balance - ? WHERE account_id = ?; "
+ "COMMIT; "
+ "DBMS_OUTPUT.PUT_LINE('Transaction Successful'); "
+ "ELSE "
+ "DBMS_OUTPUT.PUT_LINE('Insufficient Balance'); "
+ "END IF; "
+ "END;";

try {
// Step 1: Establish connection to Oracle DB
Connection conn = DriverManager.getConnection(jdbcURL, username, password);

// Step 2: Prepare statement for PL/SQL

CallableStatement stmt = conn.prepareCall(plsql);

// Step 3: Bind input parameters (account_id and debit_amount)

stmt.setInt(1, accountId);
stmt.setDouble(2, debitAmount);
stmt.setDouble(3, debitAmount);
stmt.setInt(4, accountId);

// Step 4: Execute PL/SQL block

stmt.execute();

// Step 5: Close the resources

stmt.close();
conn.close();

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

Explanation of the Java Code:

1. JDBC Connection:
o Establish a connection to the Oracle database using JDBC (DriverManager.getConnection).
2. PL/SQL Block:

Tushar Kaushik 00715611923 AIDS(S-12)

 23

o The PL/SQL block (as a string) is embedded inside the Java program. It is designed to check
the account balance, perform the debit if sufficient funds are available, and print a success or
failure message.
3. Binding Parameters:
o The values for account_id and debit_amount are passed as input parameters using stmt.setInt()
and stmt.setDouble().
4. CallableStatement:
o CallableStatement is used to execute the PL/SQL block in the database. The execute() method
runs the PL/SQL block.
5. Database Output:
o The PL/SQL DBMS_OUTPUT.PUT_LINE function prints the transaction result. In a real-world
application, you might capture these outputs in Java and display them to the user.
6. Transaction Management:
o The PL/SQL block performs the debit by updating the balance. If the balance is sufficient, it is
debited and committed. If not, a message indicating insufficient balance is displayed.

3. JDBC Setup and Execution

Before running the Java code, ensure the following:

1. JDBC Driver:
o Add the Oracle JDBC driver (ojdbc8.jar) to your classpath.
2. Database Configuration:
o Ensure the Oracle database is running, and the accounts table is created with appropriate data:

CREATE TABLE accounts (

account_id INT PRIMARY KEY,
balance DECIMAL(10, 2)
);

INSERT INTO accounts (account_id, balance)

VALUES (101, 5000.00); -- Sample account data

Summary of Banking Debit Transaction in Java:

 PL/SQL Block: Handles the debit transaction by checking the account balance and updating it if
sufficient funds are available.
 Java Code: Uses JDBC to embed the PL/SQL block, pass parameters (account ID and debit amount),
and execute it on the Oracle database.
 Database Operations: The PL/SQL block performs a SELECT to fetch the balance, checks if the debit
can be processed, and uses UPDATE to subtract the debit amount from the balance.

Tushar Kaushik 00715611923 AIDS(S-12)