PG Department of Computer Science

Database Management Systems- Lab – 20CS5CP06

1. PL/SQL Program to solve the series

X+2Y-3Z+2W=2

2X+5Y-8Z+6W=5

3X+4Y-5Z+2W=4

DECLARE

a1 NUMBER := 1; b1 NUMBER := 2; c1 NUMBER := -3; d1 NUMBER := 2; r1 NUMBER :=

2;

a2 NUMBER := 2; b2 NUMBER := 3; c2 NUMBER := -8; d2 NUMBER := 1; r2 NUMBER :=

5;

a3 NUMBER := 3; b3 NUMBER := 4; c3 NUMBER := -5; d3 NUMBER := 2; r3 NUMBER :=

4;

det NUMBER;

detX NUMBER;

detY NUMBER;

detZ NUMBER;

detW NUMBER;

x NUMBER;

y NUMBER;

z NUMBER;

w NUMBER;

BEGIN

det := a1 * (b2 * d3 - c2 * d3) -

 b1 * (a2 * d3 - c2 * d3) +

c1 * (a2 * b3 - b2 * d3);

detX := r1 * (b2 * d3 - b3 * d2) -

r2 * (b1 * d3 - b3 * d1) +

r3 * (b1 * d2 - b2 * d1);

detY := a1 * (r2 * d3 - r3 * d2) -

a2 * (r1 * d3 - r3 * d1) +

a3 * (r1 * d2 - r2 * d1);

detZ := a1 * (b2 * r3 - b3 * r2) -

b1 * (a2 * r3 - a3 * r2) +

c1 * (a2 * b3 - a3 * b2);

detW := a1 * (b2 * r3 - b3 * r2) -

c1 * (a2 * r3 - b3 * c3) +

d1 * (b4 * )

IF det != 0 THEN

x := detX / det;

y := detY / det;

z := detZ / det;

DBMS_OUTPUT.PUT_LINE('Solution:');

DBMS_OUTPUT.PUT_LINE('x = ' || x);

DBMS_OUTPUT.PUT_LINE('y = ' || y);

DBMS_OUTPUT.PUT_LINE('z = ' || z);

ELSE

DBMS_OUTPUT.PUT_LINE('The system has no unique solution (det = 0).');

 END IF;

END;

2. Write a code in PL/SQL TO create a trigger that automatically updates a

'last_modified' timestamp whenever a row in a specific table is updated.

CREATE TABLE employees (

employee_id NUMBER PRIMARY KEY,

employee_name VARCHAR2(100),

salary NUMBER,

last_modified DATE

);

CREATE OR REPLACE TRIGGER trg_update_last_modified

BEFORE UPDATE ON employees

FOR EACH ROW

BEGIN

NEW.last_modified := SYSDATE;

END;

INSERT INTO employees (employee_id, employee_name, salary, last_modified)

VALUES (1, 'John Doe', 50000, SYSDATE);

UPDATE employees

SET salary = 55000

WHERE employee_id = 1;

SELECT employee_id, employee_name, salary, last_modified

FROM employees
WHERE employee_id = 1;

3. Write a PL/SQL program to arrange the number of two variable in such a way that the
small number will store in num_small variable and large number will store in num_large
variable.

DECLARE

num1 NUMBER := 10;

num2 NUMBER := 20;

num_small NUMBER;

num_large NUMBER;

BEGIN

IF num1 < num2 THEN

num_small := num1;

num_large := num2;

ELSE

num_small := num2;

num_large := num1;

END IF;

DBMS_OUTPUT.PUT_LINE('Smaller number: ' || num_small);

DBMS_OUTPUT.PUT_LINE('Larger number: ' || num_large);

END;

4. PL/SQL Program to solve the series

3x-7y=4

-9x+21y=4

1. DECLARE
 x NUMBER;

y NUMBER;

BEGIN

x := 1; -- We assume x = 1, and we solve for y.

-- Solve 3x - 7y = 4

y := (3 * x - 4) / 7;

DBMS_OUTPUT.PUT_LINE('The value of x is: ' || x);

DBMS_OUTPUT.PUT_LINE('The value of y is: ' || y);

END;

5. Write a PL/SQL procedure to calculate the incentive on a target achieved and display
the message either the record updated or not.

CREATE TABLE employees (

employee_id NUMBER PRIMARY KEY,

employee_name VARCHAR2(100),

target NUMBER,

actual NUMBER,

incentive_amount NUMBER

);

INSERT INTO employees (employee_id, employee_name, target, actual,

incentive_amount)

VALUES (1, 'John Doe', 1000, 1200, 0);

COMMIT;

CREATE OR REPLACE PROCEDURE calculate_incentive(target IN NUMBER, actual IN

NUMBER, emp_id IN NUMBER)

IS
 incentive NUMBER;

rows_updated NUMBER;

BEGIN

IF actual >= target THEN

incentive := actual * 0.10;

ELSE

incentive := actual * 0.05;

END IF;

UPDATE employees

SET incentive_amount = incentive

WHERE employee_id = emp_id;

rows_updated := SQL%ROWCOUNT;

IF rows_updated > 0 THEN

DBMS_OUTPUT.PUT_LINE('Record updated successfully with incentive: ' ||

incentive);

ELSE

DBMS_OUTPUT.PUT_LINE('No record was updated.');

END IF;

END;

6. Write a code in PL/SQL TO create a trigger that automatically updates a

'last_modified' timestamp whenever a row in a specific table is updated.

CREATE TABLE employees (

emp_id NUMBER PRIMARY KEY,

emp_name VARCHAR2(50),
 emp_salary NUMBER,

last_modified DATE

);

INSERT INTO employees (emp_id, emp_name, emp_salary, last_modified)

VALUES (1, 'Joe', 50000, SYSDATE);

INSERT INTO employees (emp_id, emp_name, emp_salary, last_modified)

VALUES (2, 'John', 60000, SYSDATE);

CREATE OR REPLACE TRIGGER update_last_modified_trigger

BEFORE UPDATE ON employees

FOR EACH ROW

BEGIN

:NEW.last_modified := SYSDATE;

END;

UPDATE employees

SET emp_salary = 55000

WHERE emp_id = 1;

SELECT * FROM employees;

7. PL/SQL Program to solve the series

5x + 15y =14x - 7y

5x - 14x = -7y - 15y

-9x = -22y

22y = 9x

y = (9/22)x
DECLARE

x NUMBER;

y NUMBER;

BEGIN

x := 22;

y := (9/22) * x;

DBMS_OUTPUT.PUT_LINE('x = ' || x);

DBMS_OUTPUT.PUT_LINE('y = ' || y);

END;

8. Write a PL/SQL program to check whether a number is even or odd.

DECLARE

n NUMBER := 64;

r NUMBER;

BEGIN

r := MOD(n, 2);

IF r = 0 THEN

DBMS_OUTPUT.PUT_LINE('Given number is Even');

ELSE

DBMS_OUTPUT.PUT_LINE('Given number is Odd');

END IF;

END;

9. Write a code in PL/SQL to create a trigger that prevents updates on a certain column
during specific hours of the day.

CREATE TABLE orders (

order_id NUMBER PRIMARY KEY,

order_date TIMESTAMP,

order_amount NUMBER

);

Create a trigger to prevent updates during specific hours

CREATE OR REPLACE TRIGGER prevent_updates

BEFORE UPDATE OF order_amount ON orders

FOR EACH ROW

DECLARE

current_hour NUMBER;

BEGIN

Get the current hour

SELECT TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'HH24')) INTO current_hour FROM

DUAL;

Check if it's outside of business hours (9 AM to 5 PM)

IF current_hour< 9 OR current_hour>= 17 THEN

RAISE_APPLICATION_ERROR(-20001, 'Updates are not allowed during non-

business hours.');

END IF;

END;

10. PL/SQL Program to solve the series

x+2y-3z+2w=2,
2x+5y-8z+6z=5,

3x+4y-5z+2w=4.

DECLARE

x NUMBER;

y NUMBER;

z NUMBER;

w NUMBER;

temp1 NUMBER;

temp2 NUMBER;

temp3 NUMBER;

BEGIN

temp1 := 2*(2 - 2*y + 3*z - 2*w) + 5*y - 8*z + 6*w;

temp2 := 3*(2 - 2*y + 3*z - 2*w) + 4*y - 5*z + 2*w;

y := 1;

z := -1;

w := 1;

x := 2 - 2*y + 3*z - 2*w;

DBMS_OUTPUT.PUT_LINE('Solution:');

DBMS_OUTPUT.PUT_LINE('x = ' || x);

DBMS_OUTPUT.PUT_LINE('y = ' || y);

DBMS_OUTPUT.PUT_LINE('z = ' || z);

DBMS_OUTPUT.PUT_LINE('w = ' || w);

END;

/
11. Write a PL/SQL procedure to calculate the incentive on a specific target otherwise a
general incentive to be paid using IF-THEN-ELSE.

12. Write a code in PL/SQL to implement a trigger that maintains a transaction history
log whenever a row is deleted from a table.

14. Write a PL/SQL program to check whether a date falls on weekend i.e. SATURDAY or
SUNDAY.

DECLARE

input_date DATE := TO_DATE('2024-10-12', 'YYYY-MM-DD'); -- Change this date to test

day_of_week NUMBER;

BEGIN

-- Get the day of the week: 1=Sunday, 2=Monday, ..., 7=Saturday

day_of_week := TO_CHAR(input_date, 'D');

-- Check if it's Saturday (7) or Sunday (1)

IF day_of_week = '1' OR day_of_week = '7' THEN

DBMS_OUTPUT.PUT_LINE('The date ' || TO_CHAR(input_date, 'YYYY-MM-DD') || '

falls on a weekend.');

ELSE

DBMS_OUTPUT.PUT_LINE('The date ' || TO_CHAR(input_date, 'YYYY-MM-DD') || ' is

a weekday.');

END IF;

END;

15. Write a code in PL/SQL to develop a trigger that enforces referential integrity by
preventing the deletion of a parent record if child records exist.

16. PL/SQL Program for Prime Number

DECLARE
 num NUMBER := &num;

i NUMBER := 2;

is_prime BOOLEAN := TRUE;

BEGIN

IF num < 2 THEN

is_prime := FALSE;

ELSE

WHILE i <= SQRT(num) LOOP

IF MOD(num, i) = 0 THEN

is_prime := FALSE;

EXIT;

END IF;

i := i + 1;

END LOOP;

END IF;

IF is_prime THEN

DBMS_OUTPUT.PUT_LINE(num || ' is a prime number.');

ELSE

DBMS_OUTPUT.PUT_LINE(num || ' is not a prime number.');

END IF;

END;

17. Write a PL/SQL procedure to calculate incentive achieved according to the specific
sale limit.
 DECLARE

PROCEDURE test1 (sal_achieve NUMBER)

IS

incentive NUMBER := 0;

BEGIN

IF sal_achieve > 44000 THEN

incentive := 1800;

ELSIF sal_achieve > 32000 THEN

incentive := 800;

ELSE

incentive := 500;

END IF;

DBMS_OUTPUT.NEW_LINE;

DBMS_OUTPUT.PUT_LINE (

'Sale achieved : ' || sal_achieve || ', incentive : ' || incentive || '.'

);

END test1;

BEGIN

test1(45000);

test1(36000);

test1(28000);

END;

18. Write a code in PL/SQL to create a trigger that checks for duplicate values in a
specific column and raises an exception if found.
CREATE TABLE employees (

employee_id NUMBER(6) PRIMARY KEY,

employee_name VARCHAR2(100),

department_id NUMBER(3)

);

CREATE OR REPLACE TRIGGER check_duplicate_names

BEFORE INSERT OR UPDATE ON employees

FOR EACH ROW

DECLARE

duplicate_count NUMBER;

BEGIN

SELECT COUNT(*) INTO duplicate_count

FROM employees

WHERE employee_name = :new.employee_name;

IF duplicate_count > 0 THEN

RAISE_APPLICATION_ERROR(-20001, 'Duplicate employee name found: '

|| :new.employee_name);

END IF;

END;

19. PL/SQL Program to Find Factorial of a Number

DECLARE

num NUMBER := &input_number;

 factorial NUMBER := 1;

BEGIN

IF num < 0 THEN

DBMS_OUTPUT.PUT_LINE('Factorial is not defined for negative numbers');

ELSIF num = 0 THEN

DBMS_OUTPUT.PUT_LINE('The factorial of 0 is 1');

ELSE

FOR i IN 1..num LOOP

factorial := factorial * i;

END LOOP;

DBMS_OUTPUT.PUT_LINE('The factorial of ' || num || ' is ' || factorial);

END IF;

END;

20. Write a PL/SQL program to count number of employees in department 50 and check
whether this department have any vacancies or not. There are 45 vacancies in this
department.

CREATE TABLE employees (

employee_id NUMBER PRIMARY KEY,

employee_name VARCHAR2(50),

department_id NUMBER,

salary NUMBER

);
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
(1, 'John Doe', 50, 50000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(2, 'Jane Smith', 50, 55000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(3, 'Alice Johnson', 60, 60000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(4, 'Bob Brown', 50, 52000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(5, 'Charlie Davis', 70, 58000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(6, 'Eve Adams', 60, 62000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(7, 'Frank Green', 80, 70000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(8, 'Grace White', 90, 53000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(9, 'Hank Miller', 50, 50000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(10, 'Ivy Clark', 70, 55000);

DECLARE

dept_id NUMBER := 50;

emp_count NUMBER;

max_vacancies NUMBER := 45;

BEGIN

SELECT COUNT(*)

INTO emp_count
 FROM employees

WHERE department_id = dept_id;

DBMS_OUTPUT.PUT_LINE('Number of employees in department ' || dept_id || ' is: ' ||

emp_count);

IF emp_count < max_vacancies THEN

DBMS_OUTPUT.PUT_LINE('There are vacancies available in department ' || dept_id ||

'. Vacancies left: ' || (max_vacancies - emp_count));

ELSE

DBMS_OUTPUT.PUT_LINE('No vacancies available in department ' || dept_id || '.');

END IF;

END;

21. Write a code in PL/SQL to create a trigger that restricts the insertion of new rows if
the total of a column's values exceeds a certain threshold.

CREATE TABLE Sales_details (

sale_id NUMBER PRIMARY KEY,

sale_amount NUMBER(10, 2)

);

INSERT INTO Sales_details (sale_id, sale_amount) VALUES (1, 2000);

INSERT INTO Sales_details (sale_id, sale_amount) VALUES (2, 3000);

INSERT INTO Sales_details (sale_id, sale_amount) VALUES (3, 4000);

COMMIT;

CREATE OR REPLACE TRIGGER restrict_sales

BEFORE INSERT ON Sales_details

FOR EACH ROW

DECLARE

total_sales NUMBER(10, 2);

threshold CONSTANT NUMBER(10, 2) := 10000;

BEGIN

SELECT SUM(sale_amount) INTO total_sales FROM Sales_details;

total_sales := total_sales + :NEW.sale_amount;

IF total_sales > threshold THEN

RAISE_APPLICATION_ERROR(-20001, 'Total sales exceed the allowed threshold of

10000');

END IF;

END;

/
22. PL/SQL Program to Print Table of a Number

SET SERVEROUTPUT ON;

DECLARE

n NUMBER;

BEGIN

n := 5;

DBMS_OUTPUT.PUT_LINE('Multiplication Table for ' || n || ':');

FOR i IN 1..10 LOOP

DBMS_OUTPUT.PUT_LINE(n || ' x ' || i || ' = ' || (n * i));

END LOOP;

END;

23. Write a PL/SQL program to display the description against a grade.

DECLARE

Grade CHAR(1);

Des VARCHAR2(50);

BEGIN

Grade := 'A';

CASE Grade

WHEN 'A' THEN

Des := 'Excellent';

WHEN 'B' THEN

Des := 'Good';

WHEN 'C' THEN

 Des := 'Average';

WHEN 'D' THEN

Des := 'Below Average';

WHEN 'F' THEN

Des := 'Fail';

ELSE

Des := 'Invalid Grade';

END CASE;

DBMS_OUTPUT.PUT_LINE('Grade: ' || Grade || ' - Description: ' || Des);

END;

DECLARE

grade CHAR(1) := '&enter_grade';

description VARCHAR2(20)

BEGIN

CASE

WHEN grade = 'A' THEN description := 'Excellent';

WHEN grade = 'B' THEN description := 'Good';

WHEN grade = 'C' THEN description := 'Fair';

WHEN grade = 'D' THEN description := 'Poor';

WHEN grade = 'F' THEN description := 'Fail';

ELSE description := 'Invalid Grade';

END CASE;

DBMS_OUTPUT.PUT_LINE('Grade: ' || grade);

 DBMS_OUTPUT.PUT_LINE('Description: ' || description);

END;

24. Write a code in PL/SQL to design a trigger that captures changes made to specific
columns and logs them in an audit table.

CREATE TABLE employees (

employee_id NUMBER PRIMARY KEY,

name VARCHAR2(100),

salary NUMBER

);

CREATE TABLE audit_salary_changes (

audit_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

employee_id NUMBER,

old_salary NUMBER,

new_salary NUMBER,

change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

changed_by VARCHAR2(50)

);

CREATE OR REPLACE TRIGGER trg_audit_salary_changes

AFTER UPDATE OF salary ON employees

FOR EACH ROW

BEGIN

INSERT INTO audit_salary_changes (employee_id, old_salary, new_salary,

changed_by)
 VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, USER);

END;

INSERT INTO employees (employee_id, name, salary) VALUES (1, 'John Doe', 50000);

INSERT INTO employees (employee_id, name, salary) VALUES (2, 'Jane Smith', 60000);

COMMIT;

UPDATE employees SET salary = 55000 WHERE employee_id = 1;

COMMIT;

SET SERVEROUTPUT ON;

BEGIN

FOR rec IN (SELECT * FROM audit_salary_changes) LOOP

DBMS_OUTPUT.PUT_LINE('Audit ID: ' || rec.audit_id ||

', Employee ID: ' || rec.employee_id ||

', Old Salary: ' || rec.old_salary ||

', New Salary: ' || rec.new_salary ||

', Change Date: ' || rec.change_date ||

', Changed By: ' || rec.changed_by);

END LOOP;

END;

25. PL/SQL Program for Reverse of a Number

DECLARE

num NUMBER := 701906;

 reversed_num NUMBER := 0;

remainder NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('Original Number: ' || num);

WHILE num > 0 LOOP

remainder := MOD(num, 10);

reversed_num := reversed_num * 10 + remainder;

num := TRUNC(num / 10);

END LOOP;

DBMS_OUTPUT.PUT_LINE('Reversed Number: ' || reversed_num);

END;

26. Write a PL/SQL program to count number of employees in a specific department and
check whether this department have any vacancies or not. If any vacancies, how many
vacancies are in that department.

CREATE TABLE employees (

employee_id NUMBER PRIMARY KEY,

employee_name VARCHAR2(50),

department_id NUMBER,

salary NUMBER

);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(1, 'Manimekala', 10, 48000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(2, 'Prenci', 50, 55000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(3, 'Nisha', 20, 63000);
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
(4, 'Buelah', 50, 72000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(5, 'Chandru', 80, 58000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(6, 'Naveeen', 90, 69000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(7, 'Saranya', 80, 75000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(8, 'Maha', 90, 33000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(9, 'veera', 70, 50000);

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES

(10, 'Dharshini', 10, 60000);

DECLARE

dept_id NUMBER := 24;

emp_count NUMBER;

max_vacancies NUMBER := 15;

BEGIN

SELECT COUNT(*)

INTO emp_count

FROM employees

WHERE department_id = dept_id;

DBMS_OUTPUT.PUT_LINE('Number of employees in department ' || dept_id || ' is: ' ||

emp_count);

IF emp_count < max_vacancies THEN

DBMS_OUTPUT.PUT_LINE('There are vacancies available in department ' || dept_id ||

'. Vacancies left: ' || (max_vacancies - emp_count));
 ELSE

DBMS_OUTPUT.PUT_LINE('No vacancies available in department ' || dept_id || '.');

END IF;

END

27. Write a code in PL/SQL to implement a trigger that records user activity (inserts,
updates, deletes) in an audit log for a given set of tables.

CREATE TABLE audit_log (

log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

table_name VARCHAR2(100),

operation VARCHAR2(10),

user_name VARCHAR2(30),

timestamp TIMESTAMP,

old_data CLOB,

new_data CLOB,

primary_key_value VARCHAR2(100)

);

CREATE TABLE employees (

employee_id NUMBER PRIMARY KEY,

first_name VARCHAR2(50),

last_name VARCHAR2(50),

department VARCHAR2(50)

);

CREATE OR REPLACE TRIGGER audit_trigger

AFTER INSERT OR UPDATE OR DELETE

ON employees

FOR EACH ROW

DECLARE

v_operation VARCHAR2(10);

v_old_data CLOB;

v_new_data CLOB;

v_pk_value VARCHAR2(100);

BEGIN

IF INSERTING THEN

v_operation := 'INSERT';

v_new_data := 'NEW: employee_id=' || :NEW.employee_id || ', first_name='

|| :NEW.first_name || ', last_name=' || :NEW.last_name || ', department='
|| :NEW.department;

ELSIF UPDATING THEN

v_operation := 'UPDATE';

v_old_data := 'OLD: employee_id=' || :OLD.employee_id || ', first_name='

|| :OLD.first_name || ', last_name=' || :OLD.last_name || ', department='
|| :OLD.department;

v_new_data := 'NEW: employee_id=' || :NEW.employee_id || ', first_name='

|| :NEW.first_name || ', last_name=' || :NEW.last_name || ', department='
|| :NEW.department;

ELSIF DELETING THEN

v_operation := 'DELETE';

v_old_data := 'OLD: employee_id=' || :OLD.employee_id || ', first_name='

|| :OLD.first_name || ', last_name=' || :OLD.last_name || ', department='
|| :OLD.department;

END IF;
 v_pk_value := :OLD.employee_id; -- Adjust to match your primary key column

INSERT INTO audit_log (

table_name,

operation,

user_name,

timestamp,

old_data,

new_data,

primary_key_value

) VALUES (

'employees',

v_operation,

USER,

SYSTIMESTAMP,

v_old_data,

v_new_data,

v_pk_value

);

END;

INSERT INTO employees (employee_id, first_name, last_name, department)

VALUES (1, 'John', 'Doe', 'Sales');

INSERT INTO employees (employee_id, first_name, last_name, department)

VALUES (2, 'Jane', 'Smith', 'Marketing');

UPDATE employees

SET department = 'HR'

WHERE employee_id = 1;

DELETE FROM employees

WHERE employee_id = 2;

SELECT *

FROM audit_log

ORDER BY timestamp DESC;

28. PL/SQL Program for Reverse a word

DECLARE

word VARCHAR2(20) = 'Reverse';

reversed_word VARCHAR2(20) = '';

BEGIN

FOR i IN REVERSE 1..LENGTH(word) LOOP

reversed_word = reversed_word || SUBSTR(word, i, 1);

END LOOP;

DBMS_OUTPUT.PUT_LINE('Reversed Word: ' || reversed_word);

END;

/
29. Write a PL/SQL program to display the description against a grade using CASE
statement.

DECLARE

v_grade CHAR(1);

v_description VARCHAR2(100);

BEGIN

v_grade = 'B';

v_description = CASE v_grade

WHEN 'A' THEN 'Excellent'

WHEN 'B' THEN 'Good'

WHEN 'C' THEN 'Average'

WHEN 'D' THEN 'Below Average'

WHEN 'F' THEN 'Fail'

ELSE 'Invalid Grade'

END;

DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade || ' - Description: ' || v_description);

END;

30. Write a code in PL/SQL to implement a trigger that automatically calculates and
updates a running total column for a table whenever new rows are inserted.

CREATE TABLE sales (

sale_id NUMBER PRIMARY KEY,

sale_amount NUMBER,
 running_total NUMBER

);

INSERT INTO sales (sale_id, sale_amount) VALUES (1, 100);

INSERT INTO sales (sale_id, sale_amount) VALUES (2, 200);

INSERT INTO sales (sale_id, sale_amount) VALUES (3, 150);

SELECT * FROM sales;

CREATE OR REPLACE TRIGGER update_running_total

BEFORE INSERT ON sales

FOR EACH ROW

DECLARE

v_total NUMBER;

BEGIN

SELECT NVL(MAX(running_total), 0) + :NEW.sale_amount

INTO v_total

declare

str1 varchar2(50):='&str';

str2 varchar2(50);

len number;

i number;

begin

len:=length(str1);

for i in reverse 1..len

loop
 str2:=str2 || substr(str1,i,1);

end loop;

dbms_output.put_line('Reverse of String is:'||str2);

end;

31. PL/SQL Program for Reverse a sentence

DECLARE

str1 varchar2(50):='&str';

str2 varchar2(50);

len number;

i number;

BEGIN

len:=length(str1);

for i in reverse 1..len

loop

str2:=str2 || substr(str1,i,1);

END loop;

dbms_output.put_line('Reverse of String is:'||str2);

END;

/
32. Write a PL/SQL program to display the description against a grade using CASE
statement with EXCEPTION.

DECLARE

grd CHAR(1);

BEGIN

grd := '&new_grd';

CASE

WHEN grd = 'A' THEN dbms_output.Put_line('Your Grade is: Outstanding');

WHEN grd = 'B' THEN dbms_output.Put_line('Your Grade is: Excellent');

WHEN grd = 'C' THEN dbms_output.Put_line('Your Grade is: Very Good');

WHEN grd = 'D' THEN dbms_output.Put_line('Your Grade is: Average');

WHEN grd = 'F' THEN dbms_output.Put_line('Your Grade is: Poor');

END CASE;

EXCEPTION

WHEN CASE_NOT_FOUND THEN

dbms_output.Put_line('No such grade in the list.');

END;

33. Write a code in PL/SQL to create a trigger that validates the availability of items
before allowing an order to be placed, considering stock levels and pending orders.

DECLARE

v_available_stock NUMBER;

v_pending_orders NUMBER;

BEGIN

SELECT stock_quantity INTO v_available_stock

 FROM items

WHERE item_id = :NEW.item_id;

SELECT SUM(order_quantity) INTO v_pending_orders

FROM orders

WHERE item_id = :NEW.item_id

AND order_id != :NEW.order_id; -- Exclude current order if it's an update

IF (v_available_stock - v_pending_orders) < :NEW.order_quantity THEN

RAISE_APPLICATION_ERROR(-20001, 'Insufficient stock for item: ' || :NEW.item_id);

END IF;

END;

34. PL/SQL Program for Fibonacci Series

DECLARE

v_n NUMBER := 10;

v_fib1 NUMBER := 0;

v_fib2 NUMBER := 1;

v_fib_next NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('Fibonacci Series:');

DBMS_OUTPUT.PUT_LINE(v_fib1);

FOR i IN 2..v_n LOOP

 DBMS_OUTPUT.PUT_LINE(v_fib2);

v_fib_next := v_fib1 + v_fib2;

v_fib1 := v_fib2;

v_fib2 := v_fib_next;

END LOOP;

END;

35. Write a PL/SQL program to check whether a given number is positive, negative or
zero.

DECLARE

v_number NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('Enter a number:');

v_number := &user_input;

IF v_number > 0 THEN

DBMS_OUTPUT.PUT_LINE(v_number || ' is Positive');

ELSIF v_number < 0 THEN

DBMS_OUTPUT.PUT_LINE(v_number || ' is Negative');

ELSE

DBMS_OUTPUT.PUT_LINE(v_number || ' is Zero');

END IF;

END;

/
36. Design a trigger that sends an email notification to a predefined address whenever
an error occurs during a specific operation

37. PL/SQL Program to Check Number is Odd or Even

Set serveroutput on;

DECLARE

num NUMBER;

result VARCHAR2(10);

BEGIN

num := 10;

IF MOD(num, 2) = 0 THEN

result := 'Even';

ELSE

result := 'Odd';

END IF;

DBMS_OUTPUT.PUT_LINE('The number ' || num || ' is ' || result);

END;

38. Write a PL/SQL program to check whether a given character is letter or digit.

Set Serveroutput on;

DECLARE

input_char CHAR(1);

is_letter BOOLEAN;

is_digit BOOLEAN;

BEGIN
 input_char := 'A';

is_letter := (input_char BETWEEN 'A' AND 'Z') OR (input_char BETWEEN 'a' AND 'z');

is_digit := (input_char BETWEEN '0' AND '9');

IF is_letter THEN

DBMS_OUTPUT.PUT_LINE(input_char || ' is a letter.');

ELSIF is_digit THEN

DBMS_OUTPUT.PUT_LINE(input_char || ' is a digit.');

ELSE

DBMS_OUTPUT.PUT_LINE(input_char || ' is neither a letter nor a digit.');

END IF;

END;

39. Write a PL/SQL block to handle the exception when a division by zero occurs.

SET SERVEROUTPUT ON;

DECLARE

numerator NUMBER := 10;

denominator NUMBER := 2;

result NUMBER;

BEGIN

result := numerator / denominator;

DBMS_OUTPUT.PUT_LINE('Result: ' || result);

EXCEPTION
 WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);

END;

40. PL/SQL Program to Reverse a String

DECLARE

input_string VARCHAR2(100) := '&input_string';

reversed_string :=''

VARCHAR2(100);

i NUMBER;

BEGIN

reversed_string := ''

FOR i IN REVERSE 1..LENGTH(input_string) LOOP

reversed_string := reversed_string || SUBSTR(input_string, i, 1);

END LOOP;

DBMS_OUTPUT.PUT_LINE('Original String: ' || input_string);

DBMS_OUTPUT.PUT_LINE('Reversed String: ' || reversed_string);

END;

Output

Original String: Hello

Reversed String: olleH

DECLARE

Original _string VARCHAR 2(10):=’hello’;

reversed_string VARCHAR2(10):=’ ‘;

BEGIN

FOR i IN REVERSE 1.. LENGTH (original _string)

LOOP

reversed_string:=reversed_string ||

SUBSTR(original _string, i,1);

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘original_

string’ || original _string);

DBMS_OUTPUT.PUT_LINE(‘reversed_

String’ || reversed _string);

END;

41. Write a PL/SQL program to convert a temperature in scale Fahrenheit to Celsius and
vice versa.

DECLARE

v_fahrenheit NUMBER := 98.6;

v_celsius NUMBER;

BEGIN

v_celsius := (v_fahrenheit - 32) * 5 / 9;

DBMS_OUTPUT.PUT_LINE('Temperature in Fahrenheit: ' || v_fahrenheit);

DBMS_OUTPUT.PUT_LINE('Temperature in Celsius: ' || ROUND(v_celsius, 2));

END;

42. Handle the NO_DATA_FOUND exception when retrieving a row from a table and no
matching record is found.

DELIMITER $$

CREATE PROCEDURE GetEmployeeName(IN emp_id INT)

BEGIN

DECLARE v_employee_name VARCHAR(100);

-- Select the employee name based on ID

SELECT employee_name INTO v_employee_name

FROM employees

WHERE employee_id = emp_id;

-- Check if a row was returned

IF v_employee_name IS NOT NULL THEN

SELECT v_employee_name AS EmployeeName;

ELSE

SELECT 'No data found' AS Message;

END IF;

END$$
DELIMITER

43. Pl/SQL Program for Palindrome Number.

DECLARE

original_number NUMBER;

reversed_number NUMBER := 0;

remainder NUMBER;

temp_number NUMBER;

BEGIN

-- Input: You can replace this with any number you want to check

original_number := 12321; -- Example number

-- Store the original number to compare later

temp_number := original_number;

-- Reverse the number

WHILE temp_number > 0 LOOP

remainder := MOD(temp_number, 10);

reversed_number := reversed_number * 10 + remainder;

temp_number := FLOOR(temp_number / 10);

END LOOP;

-- Check if the original number is equal to the reversed number

IF original_number = reversed_number THEN

 DBMS_OUTPUT.PUT_LINE(original_number || ' is a palindrome number.');

ELSE

DBMS_OUTPUT.PUT_LINE(original_number || ' is not a palindrome number.');

END IF;

END;

44. Write a PL/SQL program to display which day is a specific date.

DECLARE

-- Declare a variable to hold the input date

input_date DATE;

-- Declare a variable to hold the name of the day

day_of_week VARCHAR2(20);

BEGIN

-- Input a specific date (change 'YYYY-MM-DD' to your desired date)

input_date := TO_DATE('2024-10-12', 'YYYY-MM-DD');

-- Determine the day of the week using the TO_CHAR function

day_of_week := TO_CHAR(input_date, 'Day');

-- Trim any extra spaces from the day_of_week

day_of_week := TRIM(day_of_week);

-- Display the result

 DBMS_OUTPUT.PUT_LINE('The day of the week for ' || TO_CHAR(input_date, 'YYYY-
MM-DD') || ' is: ' || day_of_week);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

END;

45. Handle the TOO_MANY_ROWS exception when retrieving multiple rows instead of a
single row from a table.

46. Pl/SQL Program for Palindrome Series

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE generate_palindrome_series (start_num IN

NUMBER, end_num IN NUMBER) IS

palindrome_found BOOLEAN := FALSE;

i NUMBER; reversed_num VARCHAR2(4000);

BEGIN

DBMS_OUTPUT.PUT_LINE('Palindrome numbers between ' || start_num || ' and ' ||

end_num || ':');

FOR i IN start_num .. end_num LOOP

reversed_num := TO_CHAR(i);

reversed_num := REVERSE(reversed_num);

IF TO_CHAR(i) = reversed_num THEN

DBMS_OUTPUT.PUT_LINE(i); -- Print the palindrome number

palindrome_found := TRUE;

END IF;
 END LOOP;

IF NOT palindrome_found THEN

DBMS_OUTPUT.PUT_LINE('No palindromic numbers found in the specified range.');

END IF;

END generate_palindrome_series

BEGIN

generate_palindrome_series(1, 100);

END;

47. Write a program in PL/SQL to print the value of a variable inside and outside a loop
using LOOP EXIT statement.

SET SERVEROUTPUT ON;

DECLARE

input_string VARCHAR2(100);

numeric_value NUMBER;

BEGIN

input_string := '1234.56';

numeric_value := TO_NUMBER(input_string)

DBMS_OUTPUT.PUT_LINE('Convertednumber: ' || numeric_value);

EXCEPTION

WHEN INVALID_NUMBER THEN

DBMS_OUTPUT.PUT_LINE('Error: Invalid number format for input "' || input_string ||

'"');

WHEN OTHERS THEN

 DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);

END;

48. Handle the INVALID_NUMBER exception when converting a non-numeric value to a

number.

SET SERVEROUTPUT ON;

DECLARE

counter NUMBER := 1;

BEGIN

LOOP

DBMS_OUTPUT.PUT_LINE('Inside the loop, counter = ' || counter);

counter := counter + 1;

IF counter > 5 THEN

EXIT;

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Outside the loop, counter = ' || counter);

END;

49. PL/SQL Program to Swap two Numbers

declare

num1 number;

num2 number;

temp number;
 begin

num1:=1000;

num2:=2000;

-- print result before swapping

dbms_output.put_line('before');

dbms_output.put_line('num1 = '|| num1 ||' num2 = '|| num2);

-- swapping of numbers num1 and num2

temp := num1;

num1 := num2;

num2 := temp;

-- print result after swapping

dbms_output.put_line('after');

dbms_output.put_line('num1 = '|| num1 ||' num2 = '|| num2);

end;

50. Write a program in PL/SQL to print the value of a variable inside and outside a loop
using LOOP WHEN EXIT statement.

DECLARE

n NUMBER := 0;

BEGIN

LOOP
 DBMS_OUTPUT.PUT_LINE ('The value of n inside the loop is: ' ||
TO_CHAR(n));

n := n + 1;

IF n > 5 THEN

EXIT;

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('The value of n outside the loop is: ' ||

TO_CHAR(n));

END;

51. Handle the DUP_VAL_ON_INDEX exception when inserting a duplicate value into a
table with a unique constraint

create table Employee(id int,

name varchar(20));

DECLARE

emp_id NUMBER := 1; -- The employee ID to insert

emp_name VARCHAR2(100) := 'John Doe'; -- The employee name to insert

BEGIN

-- Try to insert the employee into the 'employees' table

INSERT INTO employees (id,name)

VALUES (emp_id, emp_name);

-- If the insert succeeds, print a success message

 DBMS_OUTPUT.PUT_LINE('Employee inserted: ' || emp_id || ', ' || emp_name);

EXCEPTION

-- Handle the DUP_VAL_ON_INDEX exception when a duplicate ID is inserted

WHEN DUP_VAL_ON_INDEX THEN

DBMS_OUTPUT.PUT_LINE('Error: Duplicate value found for ID ' || emp_id);

-- Handle any other unexpected errors

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);

END;

52. PL/SQL Program to Swap three Numbers

DECLARE

num1 NUMBER := 10;

num2 NUMBER := 20;

num3 NUMBER := 30;

temp NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('Before Swapping:');

DBMS_OUTPUT.PUT_LINE('num1 = ' || num1 || ', num2 = ' || num2 || ', num3 = ' || num3);

-- Swapping logic
 temp := num1;

num1 := num2;

num2 := num3;

num3 := temp;

DBMS_OUTPUT.PUT_LINE('After Swapping:');

DBMS_OUTPUT.PUT_LINE('num1 = ' || num1 || ', num2 = ' || num2 || ', num3 = ' || num3);

END;

53. Write a program in PL/SQL to show the uses of nested loop.

DECLARE

outer_counter NUMBER := 1;

inner_counter NUMBER;

BEGIN

-- Outer loop runs 3 times

WHILE outer_counter <= 3 LOOP

DBMS_OUTPUT.PUT_LINE('Outer Loop Iteration: ' || outer_counter);

-- Reset inner_counter and start inner loop

inner_counter := 1;

-- Inner loop runs 2 times for each iteration of the outer loop
 WHILE inner_counter <= 2 LOOP

DBMS_OUTPUT.PUT_LINE(' Inner Loop Iteration: ' || inner_counter);

inner_counter := inner_counter + 1;

END LOOP;

-- Increment outer_counter for the next iteration

outer_counter := outer_counter + 1;

END LOOP;

END;

54. Handle the VALUE_ERROR exception when assigning an incompatible value to a

variable.

DECLARE

num NUMBER;

str VARCHAR2(10) := 'ABC'; -- Incompatible value for NUMBER variable

BEGIN

BEGIN

-- Trying to assign a string value to a NUMBER variable, which will raise

VALUE_ERROR

num := TO_NUMBER(str); -- This will raise VALUE_ERROR because 'ABC' is not a

valid number

EXCEPTION

WHEN VALUE_ERROR THEN

DBMS_OUTPUT.PUT_LINE('Error: Incompatible value assigned to a NUMBER

variable.');

END;
 -- Continue the rest of the program after handling the exception

DBMS_OUTPUT.PUT_LINE('Program continued after handling the VALUE_ERROR

exception.');

END;

55. PL/SQL Program for Armstrong NUMBER

DECLARE

n1 NUMBER(5);

s NUMBER(5) := 0;

m NUMBER(5);

r NUMBER(5);

BEGIN

n1 := &n;

m := n1;

WHILE n1 > 0 LOOP

r := MOD(n1, 10);

s := s + (r * r * r);

n1 := TRUNC(n1 / 10);

END LOOP;

IF s = m THEN

DBMS_OUTPUT.PUT_LINE('The Given Number ' || m || ' is an Armstrong Number');

ELSE
 DBMS_OUTPUT.PUT_LINE('The Given Number ' || m || ' is Not an Armstrong
Number');

END IF;

END;

56. Write a program in PL/SQL to update the salary of a specifc employee by 8% if the
salary exceeds the mid range of the salary against this job and update up to mid range if
the salary is less than 'The

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, salary NUMBER);

INSERT INTO employees (employee_id, salary) VALUES (101, 4500);

INSERT INTO employees (employee_id, salary) VALUES (102, 5500);

INSERT INTO employees (employee_id, salary) VALUES (103, 5000);

COMMIT;

SELECT * FROM employees;

SET SERVEROUTPUT ON

DECLARE

v_employee_id NUMBER := 101;

v_salary NUMBER;

v_mid_salary NUMBER := 5000;

v_new_salary NUMBER;

BEGIN
 SELECT salary INTO v_salary

FROM employees

WHERE employee_id = v_employee_id;

IF v_salary > v_mid_salary THEN

v_new_salary := v_salary * 1.08;

UPDATE employees

SET salary = v_new_salary

WHERE employee_id = v_employee_id;

DBMS_OUTPUT.PUT_LINE('Salary updated by 8% for employee ' || v_employee_id ||

'. New salary: ' || v_new_salary);

ELSIF v_salary < v_mid_salary THEN

v_new_salary := v_mid_salary;

UPDATE employees

SET salary = v_new_salary

WHERE employee_id = v_employee_id;

DBMS_OUTPUT.PUT_LINE('Salary updated to mid-range for employee ' ||

v_employee_id ||

'. New salary: ' || v_mid_salary);

ELSE

DBMS_OUTPUT.PUT_LINE('Salary is already at mid-range for employee ' ||

v_employee_id ||

'. No update needed.');

 END IF;

COMMIT;

END;

57. mid range of the salary, and display a suitable message.

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, salary NUMBER);

INSERT INTO employees (employee_id, salary) VALUES (101, 4500);

INSERT INTO employees (employee_id, salary) VALUES (102, 5500);

INSERT INTO employees (employee_id, salary) VALUES (103, 5000);

COMMIT;

SELECT * FROM employees;

SET SERVEROUTPUT ON

DECLARE

v_employee_id NUMBER := 101;

v_salary NUMBER;

v_mid_salary NUMBER := 5000;

v_new_salary NUMBER;

BEGIN

SELECT salary INTO v_salary

 FROM employees

WHERE employee_id = v_employee_id;

IF v_salary > v_mid_salary THEN

v_new_salary := v_salary * 1.08;

UPDATE employees

SET salary = v_new_salary

WHERE employee_id = v_employee_id;

DBMS_OUTPUT.PUT_LINE('Salary updated by 8% for employee ' || v_employee_id ||

'. New salary: ' || v_new_salary);

ELSIF v_salary < v_mid_salary THEN

v_new_salary := v_mid_salary;

UPDATE employees

SET salary = v_new_salary

WHERE employee_id = v_employee_id;

DBMS_OUTPUT.PUT_LINE('Salary updated to mid-range for employee ' ||

v_employee_id ||

'. New salary: ' || v_mid_salary);

ELSE

DBMS_OUTPUT.PUT_LINE('Salary is already at mid-range for employee ' ||

v_employee_id ||

'. No update needed.');

END IF;
 COMMIT;

END;

58. Handle the PROGRAM_ERROR exception when a PL/SQL program encounters an

internal error.

DECLARE

-- Declare a variable for testing

num NUMBER := 1;

BEGIN

-- Try a normal operation (no manual error raised)

DBMS_OUTPUT.PUT_LINE('This operation works fine.');

EXCEPTION

WHEN PROGRAM_ERROR THEN

DBMS_OUTPUT.PUT_LINE('A PROGRAM_ERROR has occurred. Internal error

handled!');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');

END;

59. PL/SQL Program to Find Greatest of Three Numbers

DECLARE

num1 NUMBER := 10;

num2 NUMBER := 20;

 num3 NUMBER := 15;

biggest NUMBER;

BEGIN

IF (num1 >= num2) AND (num1 >= num3) THEN

biggest := num1;

ELSIF (num2 >= num1) AND (num2 >= num3) THEN

biggest := num2;

ELSE

biggest := num3;

END IF;

DBMS_OUTPUT.PUT_LINE('The biggest number is: ' || biggest);

END;

60. Write a program in PL/SQL using nested loop with EXIT WHEN statement.

DECLARE

outer_counter NUMBER := 1;

inner_counter NUMBER;

BEGIN

LOOP

inner_counter := 1;

LOOP

DBMS_OUTPUT.PUT_LINE(outer_counter || ' * ' || inner_counter || ' = ' ||

(outer_counter * inner_counter));

EXIT WHEN inner_counter = 5;

inner_counter := inner_counter + 1;
 END LOOP;

EXIT WHEN outer_counter = 5;

outer_counter := outer_co ohhunter + 1;

END LOOP;

END;

61. Handle the CURSOR_ALREADY_OPEN exception when trying to open a cursor that is
already open.

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR2(50),

LastName VARCHAR2(50),

Department VARCHAR2(50),

JobTitle VARCHAR2(50),

HireDate DATE,

Salary NUMBER(10, 2)

);

SET SERVEROUTPUT ON;

DECLARE

CURSOR cur_emp IS

SELECT * FROM Employees;

emp_rec cur_emp%ROWTYPE;

cursor_open BOOLEAN := FALSE;

cursor_already_open EXCEPTION;

BEGIN
 INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, JobTitle,
HireDate, Salary)

VALUES (1, 'John', 'Doe', 'Sales', 'Sales Manager', DATE '2018-01-01', 80000.00);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, JobTitle,

HireDate, Salary)

VALUES (2, 'Jane', 'Smith', 'Marketing', 'Marketing Manager', DATE '2019-06-01',

70000.00);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, JobTitle,

HireDate, Salary)

VALUES (3, 'Bob', 'Johnson', 'IT', 'Network Administrator', DATE '2020-03-01', 60000.00);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, JobTitle,

HireDate, Salary)

VALUES (4, 'Alice', 'Williams', 'HR', 'HR Manager', DATE '2017-09-01', 75000.00);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, JobTitle,

HireDate, Salary)

VALUES (5, 'Mike', 'Davis', 'Finance', 'Financial Analyst', DATE '2020-01-01', 65000.00);

COMMIT;

DBMS_OUTPUT.PUT_LINE('Records inserted successfully!');

IF NOT cursor_open THEN

OPEN cur_emp;

cursor_open := TRUE;

ELSE

RAISE cursor_already_open;

END IF;

FETCH cur_emp INTO emp_rec;

EXIT WHEN cur_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.EmployeeID);

 DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.FirstName || ' ' || emp_rec.LastName);

DBMS_OUTPUT.PUT_LINE('Department: ' || emp_rec.Department);

DBMS_OUTPUT.PUT_LINE('Job Title: ' || emp_rec.JobTitle);

DBMS_OUTPUT.PUT_LINE('Hire Date: ' || emp_rec.HireDate);

DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_rec.Salary);

DBMS_OUTPUT.PUT_LINE('---------------------------');

END LOOP;

IF cursor_open THEN

CLOSE cur_emp;

cursor_open := FALSE;

END IF;

EXCEPTION

WHEN cursor_already_open THEN

DBMS_OUTPUT.PUT_LINE('Error: The cursor is already open!');

WHEN INVALID_CURSOR THEN

DBMS_OUTPUT.PUT_LINE('Error: Invalid cursor operation!');

DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);

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

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);

CLOSE cur_emp;

cursor_open := FALSE;

END IF;

END;
/

62. PL/SQL Program to Find Greatest of Five Numbers

SET SERVEROUTPUT ON;

DECLARE

num1 NUMBER := &num1;

num2 NUMBER := &num2;

num3 NUMBER := &num3;

num4 NUMBER := &num4;

num5 NUMBER := &num5;

BEGIN

IF num1 > num2 AND num1 > num3 AND num1 > num4 AND num1 > num5 THEN

DBMS_OUTPUT.PUT_LINE('num1 is the greatest number');

ELSIF num2 > num3 AND num2 > num4 AND num2 > num5 THEN

DBMS_OUTPUT.PUT_LINE('num2 is the greatest number');

ELSIF num3 > num4 AND num3 > num5 THEN

DBMS_OUTPUT.PUT_LINE('num3 is the greatest number');

ELSIF num4 > num5 THEN

DBMS_OUTPUT.PUT_LINE('num4 is the greatest number');

ELSE

DBMS_OUTPUT.PUT_LINE('num5 is the greatest number');

END IF;

END;

63. Write a program in PL/SQL using loop with CONTINUE statement.

SET SERVEROUTPUT ON;

DECLARE

i NUMBER := 1;

BEGIN

LOOP

IF i = 5 THEN

i := i + 1;

CONTINUE;

END IF;

DBMS_OUTPUT.PUT_LINE('Iteration ' || i);

i := i + 1;

IF i > 10 THEN

EXIT;

END IF;

END LOOP;

END;

64. Handle the INVALID_CURSOR exception when referencing an invalid or closed .

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

Department VARCHAR(50),

JobTitle VARCHAR(50),
HireDate DATE,

Salary DECIMAL(10, 2)

);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, JobTitle,

HireDate, Salary)

VALUES

(1, 'John', 'Doe', 'Sales', 'Sales Manager', date'2018-01-01', 80000.00),

(2, 'Jane', 'Smith', 'Marketing', 'Marketing Manager', date'2019-06-01', 70000.00),

(3, 'Bob', 'Johnson', 'IT', 'Network Administrator', date'2020-03-01', 60000.00),

(4, 'Alice', 'Williams', 'HR', 'HR Manager', date'2017-09-01', 75000.00),

(5, 'Mike', 'Davis', 'Finance', 'Financial Analyst', date'2020-01-01', 65000.00);

COMMIT;

SET SERVEROUTPUT ON;

DECLARE

CURSOR cur_emp IS

SELECT * FROM employees;

emp_rec cur_emp%ROWTYPE;

BEGIN

OPEN cur_emp;

LOOP

FETCH cur_emp INTO emp_rec;

EXIT WHEN cur_emp%NOTFOUND;

 DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.EmployeeID);

DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.FirstName || ' ' || emp_rec.LastName);

DBMS_OUTPUT.PUT_LINE('Department: ' || emp_rec.Department);

DBMS_OUTPUT.PUT_LINE('Job Title: ' || emp_rec.JobTitle);

DBMS_OUTPUT.PUT_LINE('Hire Date: ' || emp_rec.HireDate);

DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_rec.Salary);

DBMS_OUTPUT.PUT_LINE('---------------------------');

END LOOP;

EXCEPTION

WHEN INVALID_CURSOR THEN

DBMS_OUTPUT.PUT_LINE('Invalid cursor operation!');

DBMS_OUTPUT.

_LINE('Error Code: ' || SQLCODE);

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

END;

65. PL/SQL Program to Find Smallest of Three Numbers

66. Write a program in PL/SQL to print 1st n numbers with a difference of 3 and
starting from 1.

67. Handle the COLLECTION_IS_NULL exception when trying to access elements

from a NULL collection.

68. PL/SQL Program to Find smallest of Five Numbers

69. Write a program in PL/SQL to show the value of a same variable declared as
local and global.
70. Handle the CASE_NOT_FOUND exception when no branch matches the value
of a CASE statement.

71. PL/SQL Program to Print the following Pattern

**

***

****

*****

******

*******

********

declare

n number:=8;

i number;

j number;

begin

for i in 1..n

loop

for j in 1..i

loop

dbms_output.put('*');
 end loop;

dbms_output.new_line;

end loop;

end;
/

DECLARE

i NUMBER := 1;

j NUMBER;

BEGIN

-- Outer loop to handle the number of rows

FOR i IN 1..8 LOOP

-- Inner loop to print '*' in each row

FOR j IN 1..i LOOP

DBMS_OUTPUT.PUT('*');

END LOOP;

-- Move to the next line after each row

DBMS_OUTPUT.PUT_LINE('');

END LOOP;

END;
/

72. Write a program in PL/SQL to explain the uses of nested for loop with label.

DECLARE

i NUMBER;

j NUMBER;

BEGIN

<<outer_loop>> -- Label for the outer loop

FOR i IN 1..5 LOOP

DBMS_OUTPUT.PUT_LINE('Multiplication table for ' || i || ':');

<<inner_loop>> -- Label for the inner loop

FOR j IN 1..5 LOOP

DBMS_OUTPUT.PUT_LINE(i || ' * ' || j || ' = ' || (i * j));

END LOOP inner_loop; -- End of the inner loop

DBMS_OUTPUT.PUT_LINE('----------------------');

END LOOP outer_loop; -- End of the outer loop

END;

73. Handle the INVALID_TRANSACTION exception when a transaction is in an

invalid state.

DECLARE
 -- Declare variables

v_balance NUMBER := 1000; -- Example balance

v_withdraw_amount NUMBER := 1200; -- Example withdrawal amount

-- Declare a custom exception

ex_invalid_transaction EXCEPTION;

BEGIN

-- Check for invalid transaction state

IF v_withdraw_amount > v_balance THEN

-- Raise the custom exception if the transaction is invalid

RAISE ex_invalid_transaction;

ELSE

-- Perform the transaction if it's valid

v_balance := v_balance - v_withdraw_amount;

DBMS_OUTPUT.PUT_LINE('Transaction successful! New balance: ' ||

v_balance);

END IF;

EXCEPTION

-- Handle the custom exception

WHEN ex_invalid_transaction THEN

DBMS_OUTPUT.PUT_LINE('Error: Invalid transaction. Withdrawal amount

exceeds available balance.');
 -- Other potential exceptions can also be handled here if needed

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');

END;

74. PL/SQL Program to Print the following Pattern

*
**
***
****
*****
******
*******
********
declare

n number:=5;

i number;

j number;

begin
 for i in 1..n

dbms_output.put(' ');

loop

for j in 1..i

loop

dbms_output.put('*');

end loop;

dbms_output.new_line;

end loop;

end;
/

75. Write a program in PL/SQL to print the prime numbers between 1 to 50.

76. Handle the SUBSCRIPT_BEYOND_COUNT exception when accessing an

array element beyond its declared size.

77. PL/SQL Program to Print the following Pattern

*
**
***
****
*****
******
*******
********
declare
 n number:=5;
i number;
j number;
k number;
begin
for i in 1..n
loop
for j in 1..n-
i
loop

dbms_output.put('
');
end loop;

for k in 1..i
loop

dbms_output.put('*');
end loop;

dbms_output.new_line;
end loop;
end;
/

78. Write a program in PL/SQL to check whether a number is prime or not using
goto statement with for loop.

79. Handle the ROWTYPE_MISMATCH exception when assigning values to

variables of incompatible row types.

81. Write a program in PL/SQL to insert records from one table to another.
82. Handle the ACCESS_INTO_NULL exception when trying to access a NULL
record.

83. PL/SQL Program to Print the following Pattern

84. Write a program in PL/SQL to insert a row if the featched value for a
component is specified.

DROP TABLE TEST;

CREATE TABLE TEST(

c1 INTEGER,

c2 INTEGER);

DELETE FROM TEST;

INSERT INTO TEST VALUES(2, 4);

INSERT INTO TEST VALUES(1, 3);

DECLARE

n1 NUMBER;

n2 NUMBER;

BEGIN

SELECT c1,c2 INTO n1,n2 FROM TEST WHERE c1>1;

IF n2=4 THEN

INSERT INTO TEST VALUES(n2,n1);

ELSE
 INSERT INTO TEST VALUES(n2+15,n1+15);

END IF;

END;

85. Write a program in PL/SQL to handle the NO_DATA_NEEDED exception when

a query retrieves more data than required.
Table: employees

employee_id integer

first_name varchar(25)

last_name varchar(25)

email archar(25)

phone_number varchar(15)

hire_date date

job_id varchar(25)

salary integer

commission_pct decimal(5,2)

manager_id integer

department_id integer

PL/SQL Code:

Explain

DECLARE v_employee_idemployees.employee_id%TYPE;
v_first_nameemployees.first_name%TYPE;
v_last_nameemployees.last_name%TYPE; CURSOR employee_cursor IS
SELECT employee_id, first_name, last_name FROM employees; BEGIN
OPEN employee_cursor; LOOP FETCH employee_cursor INTO
v_employee_id, v_first_name, v_last_name;
DBMS_OUTPUT.PUT_LINE(v_employee_id || ' - ' || v_first_name || '
' || v_last_name); IF employee_cursor%NOTFOUND THEN RAISE
NO_DATA_NEEDED; END IF; END LOOP; EXCEPTION WHEN NO_DATA_NEEDED
THEN DBMS_OUTPUT.PUT_LINE('Caught NO_DATA_NEEDED exception: More
data retrieved than required.'); WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); END; /;

86. PL/SQL Program to Print the following Pattern

*
***
*****
*******
*********
*******
*****
***
*

declare
i number:=9;
j number;
k number;
begin
while i>=1
loop
j:=9;
while j>i
loop
dbms_output.put(' ');
j:=j-2;
end loop;
 for k in 1..i
loop
dbms_output.put('*');
end loop;

dbms_output.new_line;
i:=i-2;
end loop;
end;
/

87. Write a program in PL/SQL using loop with CONTINUE WHEN statement.

DECLARE

n NUMBER := 0;

BEGIN

LOOP -- After CONTINUE statement, control resumes here

DBMS_OUTPUT.PUT_LINE ('The value inside the loop: n = ' ||

TO_CHAR(n));

n := n + 1;

CONTINUE WHEN n < 4;

DBMS_OUTPUT.PUT_LINE

('The value inside loop, after CONTINUE: n = ' ||

TO_CHAR(n));

EXIT WHEN n = 6;

END LOOP;

DBMS_OUTPUT.PUT_LINE ('The value after exit from the loop: n =

' || TO_CHAR(n));

END;

/
88. Write a PL/SQL code to create a package that includes a procedure to
calculate the factorial of a number and a function to check if a number is prime.

CREATE OR REPLACE PACKAGE MathUtils IS

PROCEDURE CalculateFactorial(n IN NUMBER, result OUT NUMBER);
FUNCTION IsPrime(n IN NUMBER) RETURN BOOLEAN;
END MathUtils;
/
CREATE OR REPLACE PACKAGE BODY MathUtils IS
PROCEDURE CalculateFactorial(n IN NUMBER, result OUT NUMBER) IS
factorial NUMBER := 1;
BEGIN
IF n < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Factorial is not defined
for negative numbers.');
END IF;
IF n > 1 THEN
FOR i IN 2..n LOOP
factorial := factorial * i;
END LOOP;
END IF;
result := factorial;
END CalculateFactorial;
FUNCTION IsPrime(n IN NUMBER) RETURN BOOLEAN IS
divisor NUMBER := 2;
BEGIN
IF n < 2 THEN
RETURN FALSE;
END IF;
WHILE divisor <= SQRT(n) LOOP
IF n MOD divisor = 0 THEN
RETURN FALSE;
END IF;
divisor := divisor + 1;
END LOOP;
RETURN TRUE;
END IsPrime;
END MathUtils;
/
87.Pl/ sql program to find factorial of a number
DECLARE
num NUMBER;
fact NUMBER := 1;
BEGIN
num := 5;
FOR i IN REVERSE 1..num
LOOP
fact := fact * i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is: ' || fact);
END;
/
88.Write a pl/sql program to count number of employees in department 50 and check
whether this department any vacancies or not. There are 45 vacancies in this department
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
employee_name VARCHAR2(50),
department_id NUMBER
);
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (1,
'Abi', 50);
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (2,
'Ramya', 50);
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (3,
'sineka', 60);
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (4,
'latha', 50);
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (5,
'anu', 40);

COMMIT;
DECLARE
v_emp_count NUMBER;
v_vacancies NUMBER := 45; -- predefined vacancies in department 50
BEGIN
SELECT COUNT(*) INTO v_emp_count
FROM employees
WHERE department_id = 50; DBMS_OUTPUT.PUT_LINE('Number of employees in
department 50: ' || v_emp_count);
IF v_emp_count < v_vacancies THEN
DBMS_OUTPUT.PUT_LINE('There are vacancies in department 50.');
ELSE
DBMS_OUTPUT.PUT_LINE('No vacancies in department 50.');
END IF;
END;
89.Write a code in pl/sql to create a trigger that restrict the insertion of rows if the total of
a columns value exceeds a certain threshold
CREATE TABLE sales (
sale_id INSERT INTO sales (sale_id, product_name, sale_amount) VALUES (1,
'Product A', 30000);
INSERT INTO sales (sale_id, product_name, sale_amount) VALUES (2, 'Product B',
40000);
INSERT INTO sales (sale_id, product_name, sale_amount) VALUES (3, 'Product C',
25000);
COMMIT;
CREATE OR REPLACE TRIGGER check_sales_threshold
BEFORE INSERT ON sales
FOR EACH ROW
DECLARE
v_total_sales NUMBER;
v_threshold NUMBER := 100000;
BEGIN
 SELECT SUM(sale_amount) INTO v_total_sales
FROM sales;
IF (v_total_sales + :NEW.sale_amount) > v_threshold THEN
RAISE_APPLICATION_ERROR(-20001, 'Total sales amount exceeds the threshold
of 100,000.');
END IF;
END;