PL/SQL Scenarios Interview Questions
1. Which block correctly updates salaries by 10% for all employees in 'SALES' department?
• A)
BEGIN
UPDATE employees
SET salary = salary * 1.1
WHERE department = 'SALES';
END;
• B)
BEGIN
IF department = 'SALES' THEN
salary := salary * 1.1;
END IF;
END;
• C)
UPDATE employees
SET salary = salary + 0.10;
WHERE dept = 'SALES';
• D)
BEGIN
UPDATE employees
SET salary = salary + 10%;
WHERE department = 'SALES';
END;
Answer: A
2. Which snippet correctly uses %ROWTYPE to fetch data into a record?
• A)
DECLARE
emp_rec employees%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM employees WHERE emp_id = 101;
END;
• B)
DECLARE
emp_rec employees.ROWTYPE;
BEGIN
FETCH emp_rec FROM employees WHERE emp_id = 101;
END;
• C)
DECLARE
emp_rec employees%TYPE;
BEGIN
emp_rec := SELECT * FROM employees WHERE emp_id = 101;
END;
• D)
DECLARE
emp_rec employees;
BEGIN
FETCH INTO emp_rec FROM employees;
END;
Answer: A
3. Which one correctly raises a user-defined exception?
• A)
DECLARE
e_salary EXCEPTION;
BEGIN
RAISE e_salary;
EXCEPTION
WHEN e_salary THEN
DBMS_OUTPUT.PUT_LINE('Custom Error Raised');
END;
• B)
BEGIN
THROW 'Custom Error';
END;
• C)
DECLARE
e_salary EXCEPTION := 'Low Salary';
BEGIN
RAISE e_salary;
END;
• D)
BEGIN
RAISE_APPLICATION_ERROR('Low Salary');
END;
Answer: A
4. Which code correctly declares and uses a parameterized cursor?
• A)
CURSOR c_emp(deptno NUMBER) IS
SELECT * FROM employees WHERE department_id = deptno;
BEGIN
FOR emp IN c_emp(10) LOOP
DBMS_OUTPUT.PUT_LINE(emp.first_name);
END LOOP;
END;
• B)
CURSOR c_emp IS
SELECT * FROM employees;
BEGIN
OPEN c_emp(10);
END;
• C)
DECLARE
deptno NUMBER := 10;
c_emp employees%ROWTYPE;
BEGIN
SELECT * INTO c_emp FROM employees WHERE department_id = deptno;
END;
• D)
OPEN employees(10);
FETCH employees INTO name;
CLOSE employees;
Answer: A
5. Which one is the correct syntax for creating a stored procedure with an IN parameter?
• A)
CREATE PROCEDURE increase_salary(salary IN NUMBER)
IS
BEGIN
UPDATE employees SET salary = salary + salary;
END;
• B)
PROCEDURE increase_salary(salary IN NUMBER)
BEGIN
salary := salary + 1000;
END;
• C)
CREATE OR REPLACE PROCEDURE increase_salary(sal IN NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(sal + 1000);
END;
• D)
CREATE PROCEDURE increase_salary(sal IN NUMBER)
BEGIN
salary = salary + sal;
END;
Answer: C
6. Which block correctly handles a divide-by-zero error using EXCEPTION block?
• A)
BEGIN
x := 10 / 0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero occurred');
END;
• B)
BEGIN
x := divide(10, 0);
CATCH ZERO_DIVIDE
DBMS_OUTPUT.PUT_LINE('Error');
END;
• C)
BEGIN
x := 10 / 0;
EXCEPTION
ZERO_DIVIDE => DBMS_OUTPUT.PUT_LINE('Error');
END;
• D)
BEGIN
x := 10 / 0;
ON ERROR
DBMS_OUTPUT.PUT_LINE('Divide by 0');
END;
Answer: A
7. Which code correctly creates a trigger on employee insert?
• A)
CREATE OR REPLACE TRIGGER trg_emp_insert
AFTER INSERT ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserted');
END;
• B)
TRIGGER emp_insert
AFTER INSERT
BEGIN
PRINT 'Inserted';
END;
• C)
CREATE TRIGGER trg_emp
ON employees
WHEN INSERTED
BEGIN
DBMS_OUTPUT('Insert');
END;
• D)
CREATE TRIGGER trg_emp
INSERT ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Insert');
END;
Answer: A
8. Which PL/SQL construct supports bulk binding for better performance?
• A)
FORALL i IN indices.FIRST .. indices.LAST
INSERT INTO emp VALUES (emp_data(i));
• B)
FOR i IN emp_data LOOP
INSERT INTO emp VALUES (i);
END LOOP;
• C)
LOOP
INSERT INTO emp VALUES (:NEW);
END LOOP;
• D)
BEGIN
BULK INSERT emp_data INTO emp;
END;
Answer: A
9. Which one correctly creates a function returning employee count?
• A)
CREATE OR REPLACE FUNCTION emp_count
RETURN NUMBER
IS
total NUMBER;
BEGIN
SELECT COUNT(*) INTO total FROM employees;
RETURN total;
END;
• B)
FUNCTION emp_count RETURN NUMBER
IS
RETURN SELECT COUNT(*) FROM employees;
END;
• C)
CREATE FUNCTION emp_count()
RETURN INT
AS
BEGIN
RETURN SELECT COUNT(*) FROM employees;
END;
• D)
FUNCTION emp_count():
RETURN COUNT FROM employees;
Answer: A
10. Which code correctly uses a loop to fetch data from an open cursor?
• A)
OPEN c1;
LOOP
FETCH c1 INTO v_id;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id);
END LOOP;
CLOSE c1;
• B)
LOOP
FETCH INTO c1(v_id);
EXIT WHEN NOTFOUND;
END LOOP;
• C)
OPEN c1;
FETCH c1;
WHILE c1%FOUND LOOP
PRINT c1.id;
END LOOP;
• D)
OPEN c1;
FETCH c1 INTO v_id;
DBMS_OUTPUT.PUT_LINE(v_id);
CLOSE c1;
Answer: A
Follow- Utkarsh Ranjan for more Interview Questions
Follow- Utkarsh Ranjan for more Interview Questions
