PROGRAM 1
AIM:
create a table for employee details with employee number as primary key and following
fields; Name, Designation, Gender, Age, Date of Joining and Salary. Insert atleast 10
rows and perform various queries using any one comparison, logical, set, sorting and
grouping operators
ALGORITHM:
STEP1 : Start the program
Step2 : Create database and select it
Step3 : Create table employee details with fields id, name, designation, age, gender, date of joining,
and Salary
Step4 : Insert record into the table using insert command.
Step5 : Use Select queries having conditional, logical, set, sorting & grouping operators
Step6: stop the program
SOURCE CODE:
CREATE TABLE EmployeeDetails (
EmployeeNumber NUMBER PRIMARY KEY,
Name VARCHAR2(50),
Designation VARCHAR2(50),
Gender VARCHAR2(10),
Age NUMBER,
DateOfJoining DATE,
Salary NUMBER
);
INSERT INTO EmployeeDetails (EmployeeNumber, Name, Designation, Gender, Age,
DateOfJoining, Salary)
VALUES (1, 'John Doe', 'Manager', 'Male', 35, TO_DATE('2022-01-15', 'yyyy-mm-dd'),
75000);
INSERT INTO EmployeeDetails (EmployeeNumber, Name, Designation, Gender, Age,
DateOfJoining, Salary)
VALUES (2, 'Jane Smith', 'Engineer', 'Female', 28, TO_DATE('2022-03-10', 'yyyy-mm-dd'),
60000);
Select employees with a salary greater than 60000
SELECT * FROM EmployeeDetails WHERE Salary > 60000;
Select employees who joined before a specific date
SELECT * FROM EmployeeDetails WHERE DateOfJoining< TO_DATE('2022-05-01',
'yyyy-mm-dd');
Select all female employees or employees with age less than 30
SELECT * FROM EmployeeDetails WHERE Gender = 'Female' OR Age < 30;
Sort employees by salary in descending order
SELECT * FROM EmployeeDetails ORDER BY Salary DESC;
Group employees by designation and calculate the average salary for each group
SELECT Designation, AVG(Salary) AS AverageSalary FROM EmployeeDetails GROUP BY
Designation;
RESULT:
The given program is executed successfully
PROGRAM 2
AIM:
Create tables for library management system which demonstrate the use of primary key
and foreign key. Master table should have the following fields: Accno,Title, Author and
Rate. Transaction table should have the following fields: User id, Accno, Date of Issue
and Date of Return. Create a Report with fields Accno, Title, Date of issue for the given
Date of return with column formats.
ALGORITHM:
Step 1: Start the program
Step 2: Create database library management
Step 3: Select the database.
Step 4: Create 2 tables master table and transaction table
Step 5: Create master table with fields Accno,Title, Author and Rate. (Accno as Primary Key)
Step 6: Create transaction table with fields : User id, Accno, Date of Issue and Date of Return.
(user id as primary key, Accno as foreign key)
Step 7: Insert values into 2 tables
Step6: Join 2 tables using join keyword
Step7: stop the program
SOURCE CODE:
CREATE TABLE LibraryMaster (
Accno NUMBER PRIMARY KEY,
Title VARCHAR2(100),
Author VARCHAR2(50),
Rate NUMBER
);
CREATE TABLE LibraryTransactions (
UserId NUMBER,
Accno NUMBER,
DateOfIssue DATE,
DateOfReturn DATE,
PRIMARY KEY (UserId, Accno),
FOREIGN KEY (Accno) REFERENCES LibraryMaster(Accno)
);
INSERT INTO LibraryMaster (Accno, Title, Author, Rate)
VALUES (1, 'Book 1', 'Author 1', 15);
INSERT INTO LibraryMaster (Accno, Title, Author, Rate)
VALUES (2, 'Book 2', 'Author 2', 20);
INSERT INTO LibraryTransactions (UserId, Accno, DateOfIssue, DateOfReturn)
VALUES (101, 1, TO_DATE('2023-08-15', 'yyyy-mm-dd'), TO_DATE('2023-08-30', 'yyyy-
mm-dd'));
INSERT INTO LibraryTransactions (UserId, Accno, DateOfIssue, DateOfReturn)
VALUES (102, 2, TO_DATE('2023-09-01', 'yyyy-mm-dd'), TO_DATE('2023-09-15', 'yyyy-
mm-dd'));
SELECT M.Accno, M.Title, T.DateOfIssue
FROM LibraryMaster M
JOIN LibraryTransactions T ON M.Accno = T.Accno
WHERE T.DateOfReturn = TO_DATE('2023-08-30', 'yyyy-mm-dd');
RESULT:
The given program is executed successfully
PROGRAM 3:
AIM:
Write a PL/SQL Program to update the rate field by 20% more than the current rate in the
inventory table which has the following fields: Product number, Product name, and rate. After
updating the table a new field called for the number of items and place for value for the new field
without using PL/SQL block.
ALGPRITHM:
step1: start the program
step2: create table inventory
step3: create product number, product name and rate into the inventory table
step4: use ALTER table to add the "number_of_items" field to the table
step5: update inventory by SET block
step6: stop the program
SOURCE CODE:
CREATE TABLE inventory (
"Product number" NUMBER PRIMARY KEY,
"Product name" VARCHAR2(255),
rate NUMBER(10, 2)
);
-- Add the "number_of_items" field to the table
ALTER TABLE inventory ADD number_of_items NUMBER;
ALTER TABLE inventory ADD number_of_items NUMBER;
UPDATE inventory
SET rate = rate * 1.20; -- Increase the rate by 20%
UPDATE inventory
SET number_of_items = 0;
RESULT:
The given program is executed successfully
PROGRAM 4
AIM
Write a PL/SQL program to check whether given string is palindrome or not
ALGORITHM
Step1: start the program
Step2: Declare the variable string & counter
Step3: In begin block using loop find whether string is palindrome or not
Step4: print the result
Step5: stop the program
SOURCE CODE:
SET SERVEROUTPUT ON
DECLARE
str varchar2(50):='&string';
counter int:=length(str);
BEGIN
dbms_output.put_line(counter);
LOOP exit WHEN counter=0;
exit WHEN not(substr(str,counter,1)
=substr(str,((length(str)+1)-counter),1));
counter:=counter-1;
END LOOP;
IF counter=0 THEN dbms_output.put_line
(str|| 'is palindrom');
ELSE dbms_output.put_line(str|| 'is not palindrom');
END IF;
END;
RESULT:
The given program is executed successfully
PROGRAM NO 5
AIM:
Write a PL/SQL program to find factorial of number using function and procedure
ALGORITHM:
Step1: start the program
Step2: Declare variable n , fac<i
Step3: in begin block using loop find the factorial of the number
Step4: print the factorial
Step5: stop the program
SOURCE CODE:
Declare
n number;
fac number:=1;
I number
begin
n:=&n;
for i in 1..n
loop
fac:=fac*i;
end loop;
Dbms_output.put_line(‘factorial=’||fac);
end;
RESULT:
The given program is executed successfully
PROGRAM 6
AIM:
Create a PL/SQL program to perform updation using trigger
ALGORITHM:
step1: start the program
step2: Create table customers
step3: insert values into customers
step4: Create trigger display_salary to show salary change
step5: display the salary change
step6: stop the program
SOURCE CODE:
--------- Create a table "employee"
SET SERVEROUTPUT ON
CREATE TABLE emp1 (
emp1_id NUMBER PRIMARY KEY,
emp1_name VARCHAR2(50),
last_update_time TIMESTAMP
);
INSERT INTO empl1_details VALUES (101,'John Smith');
INSERT INTO empl1_details VALUES (101,'John Smith');
INSERT INTO empl1_details VALUES (101,'John Smith');
-- Create a trigger that updates the "last_update_time" column
CREATE OR REPLACE TRIGGER emp1_update_trigger
BEFORE UPDATE ON emp1
FOR EACH ROW
BEGIN
:NEW.last_update_time := SYSTIMESTAMP;
dbms_output.put_line('emp1_id'||emp1_id);
dbms_output.put_line('emp1_name'||emp1_name);
DBMS_OUTPUT.put_line (SYSTIMESTAMP);
END;
RESULT:
The given program is executed successfully
PROGRAM 7
AIM:
Create Database Trigger to implement on master and transaction table which are based
on inventory for checking the data validity .
ALGORITHM:
step1: start the program
step2: Create table master
step3: insert values into master table
step4: Create table transaction
step5: insert values into transaction table
step6: in begin block using loop check the values exists in the both tables
step7: stop the program
SOURCE CODE:
CREATE TABLE master (
product_number NUMBER PRIMARY KEY,
product_name VARCHAR2(255) NOT NULL,
rate NUMBER(10, 2) NOT NULL
);
CREATE TABLE transaction (
transaction_id NUMBER PRIMARY KEY,
product_number NUMBER NOT NULL,
date_of_issue DATE NOT NULL,
date_of_return DATE,
CONSTRAINT fk_product FOREIGN KEY (product_number) REFERENCES master
(product_number)
);
CREATE OR REPLACE TRIGGER master_data_validity_trigger
BEFORE INSERT OR UPDATE ON master
FOR EACH ROW
BEGIN
IF :NEW.product_name IS NULL OR :NEW.rate IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, 'Product name and rate must not be null.');
END IF;
END;
/
CREATE OR REPLACE TRIGGER transaction_data_validity_trigger
BEFORE INSERT ON transaction
FOR EACH ROW
BEGIN
IF NOT EXISTS (SELECT 1 FROM master WHERE product_number =
:NEW.product_number) THEN
RAISE_APPLICATION_ERROR(-20002, 'Invalid product number.');
END IF;
END;
PROGRAM 8
AIM:
Write a PL/SQL Program to split the Student table into two tables based on result (one table for
pass and another for fail ). use curser for handling records of student table.
ALGORITHM:
step1: start the program
step2: create table student
step3: with student values like name,ID,subjects and set Student ID as primary key
step4: insert values into student table
step5: declare cursor for student and select from it
step6: In begin block using loop find the average marks for the student table
step7: stop the program
SOURCE CODE:
CREATE TABLE Student (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(255),
Subject1 NUMBER,
Subject2 NUMBER,
Subject3 NUMBER,
Subject4 NUMBER,
Subject5 NUMBER
);
INSERT INTO Student (StudentID, StudentName, Subject1, Subject2, Subject3, Subject4,
Subject5)
VALUES
(1, 'John Doe', 35, 42, 38, 45, 41),
(2, 'Jane Smith', 50, 48, 49, 47, 50),
(3, 'Bob Johnson', 30, 32, 35, 28, 40),
(4, 'Mary Brown', 42, 45, 38, 47, 40);
DECLARE
CURSOR student_cursor IS
SELECT *
FROM Student;
v_avg_mark NUMBER;
BEGIN
FOR student_rec IN student_cursor LOOP
v_avg_mark := (student_rec.Subject1 + student_rec.Subject2 +
student_rec.Subject3 + student_rec.Subject4 +
student_rec.Subject5) / 5;
IF v_avg_mark >= 40 THEN
-- Insert into Above40 table
INSERT INTO Above40 (StudentID, StudentName, AvgMarks)
VALUES (student_rec.StudentID, student_rec.StudentName, v_avg_mark);
ELSE
-- Insert into Below40 table
INSERT INTO Below40 (StudentID, StudentName, AvgMarks)
VALUES (student_rec.StudentID, student_rec.StudentName, v_avg_mark);
END IF;
END LOOP;
END;
/
-- Create the Above40 table
CREATE TABLE Above40 (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(255),
AvgMarks NUMBER
);
-- Create the Below40 table
CREATE TABLE Below40 (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(255),
AvgMarks NUMBER
);
RESULT:
The given program is executed successfully
PROGRAM 9:
AIM:
Write a PL/SQL program to raise the exception in bank account management table.
ALGORITHM:
step1: start the program
step2: create table bank account
step3: with bank account values as Account number,account holders and balance
step4: set Account number as a primary key
step5: insert sample valuesto bank account table
step6: declare account balance and withdrawal amount to know whether it exceeds the balance
step7: in begin block using loop raise an exception and update the withdrawal limit
step8: commit the transaction
step9: stop the program
SOURCE CODE:
CREATE TABLE Bank_Account (
AccountNumber NUMBER PRIMARY KEY,
AccountHolder VARCHAR2(255),
Balance NUMBER(10, 2)
);
-- Insert sample data into the Bank Account table
INSERT INTO Bank_Account (AccountNumber, AccountHolder, Balance)
VALUES (1, 'John Doe', 1000.00);
-- Create a PL/SQL block to perform a withdrawal
DECLARE
v_account_balance NUMBER(10, 2);
v_withdrawal_amount NUMBER(10, 2) := 1500.00; -- Withdrawal amount that exceeds the
balance
BEGIN
-- Retrieve the account balance
SELECT Balance INTO v_account_balance
FROM Bank_Account
WHERE AccountNumber = 1; -- Assuming Account Number 1
-- Check if withdrawal amount exceeds the balance
IF v_withdrawal_amount > v_account_balance THEN
-- Raise a custom exception
RAISE_APPLICATION_ERROR(-20001, 'Withdrawal amount exceeds account balance.');
ELSE
-- Perform the withdrawal
UPDATE Bank_Account
SET Balance = Balance - v_withdrawal_amount
WHERE AccountNumber = 1;
-- Commit the transaction
COMMIT;
DBMS_OUTPUT.PUT_LINE('Withdrawal successful.');
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Handle the exception
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
RESULT:
The given program is executed successfully
PROGRAM 10
AIM:
Create Pl/SQL program to handle package.
ALGORITHM:
step1: start the program
step2: Declare input_number
step3: in begin block call the calculate_square function from the package
step4: disply the result
step5: stop the program
SOURCE CODE:
DECLARE
input_number NUMBER := 5;
result NUMBER;
BEGIN
-- Call the calculate_square function from the package
result := MathOperations.calculate_square(input_number);
-- Display the result
DBMS_OUTPUT.PUT_LINE('The square of ' || input_number || ' is ' || result);
END;
/
RESULT:
The given program is executed successfully
PROGRAM 11:
AIM:
Write PL/SQL cursor for referencing fields in a record.
ALGORITHM:
step1: start the program
step2: create table employee
step3: with employee values like employee ID,first name,last name salary
step4: set employee ID as primary key
step5: insert values to employee table
step6: declare a cursor for quereing data and select from employee
step7: in begin block use the cursor to fetch data into the record
step8: process the data in the record and close the cursor
step9: stop the program
SOURCE CODE:
CREATE TABLE Employee (
EmployeeID NUMBER PRIMARY KEY,
FirstName VARCHAR2(50),
LastName VARCHAR2(50),
Salary NUMBER(10, 2)
);
-- Insert sample data into the Employee table
INSERT INTO Employee (EmployeeID, FirstName, LastName, Salary)
VALUES (1, 'John', 'Doe', 50000.00);
INSERT INTO Employee (EmployeeID, FirstName, LastName, Salary)
VALUES (2, 'Jane', 'Smith', 60000.00);
INSERT INTO Employee (EmployeeID, FirstName, LastName, Salary)
VALUES (3, 'Bob', 'Johnson', 45000.00);
-- Declare a cursor for querying employee data
DECLARE
CURSOR emp_cursor IS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employee;
emp_record emp_cursor%ROWTYPE; -- Declare a record based on the cursor's structure
BEGIN
-- Open the cursor
OPEN emp_cursor;
-- Fetch data into the record
FETCH emp_cursor INTO emp_record;
-- Process the data in the record
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.EmployeeID);
DBMS_OUTPUT.PUT_LINE('First Name: ' || emp_record.FirstName);
DBMS_OUTPUT.PUT_LINE('Last Name: ' || emp_record.LastName);
DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_record.Salary);
-- Close the cursor
CLOSE emp_cursor;
END;
/
RESULT:
The given program is executed successfully
PROGRAM 12:
AIM:
Write a PL/SQL trigger for entering a mark in the student table.
ALGORITHM:
step1: start the program
step2: create table student
step3: with values for student table like Student ID,name,mark
step4: creatye a trigger named"SetMark"the fires before an insert or update operation on the
"Student" table for each row
step5: calculate or assign the mark value based on logic
step6: stop the program
SOURCE CODE:
CREATE TABLE Student (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(255),
Mark NUMBER
);
-- Create a trigger to set the Mark column
CREATE OR REPLACE TRIGGER SetMark
BEFORE INSERT OR UPDATE ON Student
FOR EACH ROW
BEGIN
IF :NEW.Mark IS NULL THEN
-- Calculate or assign the mark value based on some logic
-- For example, you can set it to 0 or calculate it based on other columns.
:NEW.Mark := 0; -- Setting it to 0 as an example
END IF;
END;
/
RESULT:
The given program is executed successfully
