Scribd
Upload
2 views

plsql_interview_questions

Uploaded by

sachinnayaka999
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views

plsql_interview_questions

Uploaded by

sachinnayaka999
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

PL/SQL Interview Questions and Answers (3+ Years Experience)

1. Write an anonymous block that prints 'Welcome to PL/SQL' using DBMS_OUTPUT.PUT_LINE.

DECLARE
BEGIN
DBMS_OUTPUT.PUT_LINE('Welcome to PL/SQL');
END;
/

2. Write a PL/SQL block that calculates the sum of numbers from 1 to 100.

DECLARE
v_sum NUMBER := 0;
BEGIN
FOR i IN 1..100 LOOP
v_sum := v_sum + i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Sum: ' || v_sum);
END;
/

3. Write a block to check if a given number is prime or not.

DECLARE
v_num NUMBER := 17;
v_flag BOOLEAN := TRUE;
BEGIN
IF v_num < 2 THEN
v_flag := FALSE;
ELSE
FOR i IN 2..SQRT(v_num) LOOP
IF MOD(v_num, i) = 0 THEN
v_flag := FALSE;
EXIT;
END IF;
END LOOP;
END IF;
IF v_flag THEN
DBMS_OUTPUT.PUT_LINE(v_num || ' is Prime');
ELSE
DBMS_OUTPUT.PUT_LINE(v_num || ' is Not Prime');
END IF;
END;
/

4. Write a block that swaps two numbers without using a third variable.

DECLARE
a NUMBER := 10;
b NUMBER := 20;
BEGIN
a := a + b;
b := a - b;
a := a - b;
DBMS_OUTPUT.PUT_LINE('After Swap: a=' || a || ', b=' || b);
END;
/

5. Write a block to calculate the factorial of a number using recursion.

CREATE OR REPLACE FUNCTION factorial(n NUMBER) RETURN NUMBER IS


BEGIN
IF n = 0 THEN
RETURN 1;
ELSE
RETURN n * factorial(n - 1);
END IF;
END;
/
DECLARE
result NUMBER;
BEGIN
result := factorial(5);
DBMS_OUTPUT.PUT_LINE('Factorial: ' || result);
END;
/

6. Write a block to print Fibonacci numbers up to a given limit.

DECLARE
a NUMBER := 0;
b NUMBER := 1;
temp NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
FOR i IN 3..10 LOOP
temp := a + b;
DBMS_OUTPUT.PUT_LINE(temp);
a := b;
b := temp;
END LOOP;
END;
/

7. Fetch and display employee details based on an employee_id from the employees table.

DECLARE
v_name VARCHAR2(50);
v_salary NUMBER;
v_employee_id NUMBER := 101;
BEGIN
SELECT first_name, salary INTO v_name, v_salary
FROM employees WHERE employee_id = v_employee_id;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name || ', Salary: ' || v_salary);
END;
/

8. Write a block to display the highest salary from the employees table.

DECLARE
v_max_salary NUMBER;
BEGIN
SELECT MAX(salary) INTO v_max_salary FROM employees;
DBMS_OUTPUT.PUT_LINE('Highest Salary: ' || v_max_salary);
END;
/

9. Implement a block that prints odd and even numbers separately from 1 to 50.

DECLARE
BEGIN
FOR i IN 1..50 LOOP
IF MOD(i, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE(i || ' is Even');
ELSE
DBMS_OUTPUT.PUT_LINE(i || ' is Odd');
END IF;
END LOOP;
END;
/

10. Write a block to find the second highest salary in a table without using MAX().

DECLARE
v_second_max_salary NUMBER;
BEGIN
SELECT DISTINCT salary INTO v_second_max_salary
FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)
ORDER BY salary DESC FETCH FIRST 1 ROW ONLY;
DBMS_OUTPUT.PUT_LINE('Second Highest Salary: ' || v_second_max_salary);
END;
/

You might also like