Data Base Management Systems Lab
Assignment 1 [Each Q- 20 Marks]
1. Write a PL/SQL program to display the following
format. SET SERVEROUTPUT ON;
DECLARE
BEGIN
-- Upper part: 1, 1 2, 1 2 3
FOR i IN 1..3 LOOP
FOR s IN 1..(3 - i) LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
FOR n IN 1..i LOOP
DBMS_OUTPUT.PUT(n || ' ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
-- Lower part: 1 2, 1
FOR i IN REVERSE 1..2 LOOP
FOR s IN 1..(3 - i) LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
FOR n IN 1..i LOOP
DBMS_OUTPUT.PUT(n || ' ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
OUTPUT:
2. Create a PL/SQL program that generates the
Fibonacci sequence.
CREATE OR REPLACE PROCEDURE
generate_fibonacci (n_terms IN NUMBER)
IS
-- Variables to hold the current and previous
numbers in the sequence
v_a NUMBER := 0;
v_b NUMBER := 1;
v_temp NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('Fibonacci Sequence
up to ' || n_terms || ' terms:');
IF n_terms <= 0 THEN
DBMS_OUTPUT.PUT_LINE('Number of terms
must be a positive integer.');
RETURN;
ELSIF n_terms = 1 THEN
DBMS_OUTPUT.PUT_LINE(v_a);
ELSE
-- Print the first two terms manually
DBMS_OUTPUT.PUT_LINE(v_a);
DBMS_OUTPUT.PUT_LINE(v_b);
-- Loop from the third term up to the desired
number of terms
FOR i IN 3..n_terms LOOP
-- Calculate the next term
v_temp := v_a + v_b;
-- Print the calculated term
DBMS_OUTPUT.PUT_LINE(v_temp);
-- Update v_a and v_b for the next iteration
v_a := v_b;
v_b := v_temp;
END LOOP;
END IF;
END generate_fibonacci;
/
OUTPUT:
3. Write a PL/SQL program to display the details of
top 8 employee in the company.
DECLARE
-- Define a record type to hold the employee
details
TYPE emp_record_type IS RECORD (
employee_id NUMBER(6),
first_name VARCHAR2(20),
salary NUMBER(8, 2),
job_id VARCHAR2(10)
);
-- Declare a cursor that selects the top 8
employees
CURSOR c_top_employees IS
SELECT
employee_id,
first_name,
salary,
job_id
FROM
(SELECT -- Inner query orders the
employees by salary in descending order
employee_id,
first_name,
salary,
job_id
FROM
employees
ORDER BY
salary DESC)
WHERE ROWNUM <= 8; -- Outer query limits
the result to the first 8 rows
-- Declare a variable of the record type
v_emp_rec emp_record_type;
BEGIN
-- Enable DBMS_OUTPUT for displaying results
DBMS_OUTPUT.PUT_LINE('Details of the Top 8
Employees (by Salary):');
DBMS_OUTPUT.PUT_LINE('-------------------------------
-----------------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('ID', 5) ||
RPAD('Name', 20) || RPAD('Salary', 15) ||
RPAD('Job ID', 10));
DBMS_OUTPUT.PUT_LINE('-------------------------------
-----------------------------------');
-- Open the cursor and loop through the results
OPEN c_top_employees;
LOOP
FETCH c_top_employees INTO v_emp_rec;
EXIT WHEN c_top_employees%NOTFOUND;
-- Display employee details
DBMS_OUTPUT.PUT_LINE(
RPAD(v_emp_rec.employee_id, 5) ||
RPAD(v_emp_rec.first_name, 20) ||
RPAD(v_emp_rec.salary, 15) ||
RPAD(v_emp_rec.job_id, 10)
);
END LOOP;
-- Close the cursor
CLOSE c_top_employees;
END;
/
OUTPUT:
4. Write a PL/SQL program to calculate the factorial
of a number.
DECLARE
num NUMBER := 5; -- The number for which
to calculate the factorial
factorial NUMBER := 1; -- Initialize factorial to 1
i NUMBER; -- Loop counter
BEGIN
-- Check if the number is negative
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
-- Calculate factorial using a FOR loop
FOR i IN 1..num LOOP
factorial := factorial * i;
END LOOP;
-- Output the result
DBMS_OUTPUT.PUT_LINE('The factorial of ' ||
num || ' is ' || factorial || '.');
END IF;
END;
/
Output:
5. Write a PL/SQL program to find all prime
numbers up to a given number.
declare
-- declare variable n, i
-- and temp of datatype number
n number;
i number;
temp number;
begin
-- Here we Assigning 13 into n
n := 13;
-- Assigning 2 to i
i := 2;
-- Assigning 1 to temp
temp := 1;
-- loop from i = 2 to n/2
for i in 2..n/2
loop
if mod(n, i) = 0
then
temp := 0;
exit;
end if;
end loop;
if temp = 1
then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if;
end;
-- Program End
OUTPUT: