50+ Real-World Questions on Procedures, Functions, Cursors & Exception Handling

By /

If you’re learning Oracle PL/SQL and want hands-on confidence for interviews, projects, or production work, practicing with the HR schema is one of the smartest moves you can make.
This post is designed to give you realistic, scenario-based practice questions, carefully arranged from basic → intermediate → slightly advanced, so you can grow step by step.

📘 What is the HR Schema?

The HR schema is a sample schema provided by Oracle that simulates a Human Resources system. It contains commonly used tables such as:

  • EMPLOYEES
  • DEPARTMENTS
  • JOBS
  • JOB_HISTORY
  • LOCATIONS
  • COUNTRIES
  • REGIONS

These tables are perfect for learning PL/SQL, because they reflect real business data like employees, salaries, departments, managers, and job changes.

⚙️ How to Enable & Access HR Schema in Oracle 19c

✅ Step 1: Check if HR Schema Exists

Note: connect to sqlplus as sys user

SELECT username FROM dba_users WHERE username = 'HR';

✅ Step 2: Unlock HR Schema

ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK;

✅ Step 3: Grant Required Privileges

GRANT CONNECT, RESOURCE TO hr;

✅ Step 4: Login as HR

Use SQL Developer or SQL*Plus:

Username: HR
Password: hr

🔽 HR Schema Not Available? Download It Here

If your Oracle 19c installation doesn’t include HR schema, you can download it from Oracle’s official GitHub repository:

👉 HR Schema Download (Official Oracle)
https://github.com/oracle/db-sample-schemas

Follow the README instructions to install it in your database.

🧠 Practice Assignment Instructions

  • Use PL/SQL blocks only
  • Use HR schema tables
  • Do not hard-code values unless mentioned
  • Use meaningful variable names
  • Add comments where necessary
  • Do NOT look at solutions first 😉

🔹 Topic 1: PL/SQL Procedures (10 Questions)

Basic → Intermediate

  1. Create a procedure to display employee details (name, job, salary) for a given employee ID.
    Sample Input: EMPLOYEE_ID = 101
    Expected Output: Employee name, job title, salary
  2. Write a procedure to update an employee’s salary by a given percentage.
  3. Create a procedure to insert a new department into the DEPARTMENTS table.
  4. Write a procedure that displays all employees working in a given department name.
  5. Create a procedure that increases salary for all employees under a specific manager.
  6. Write a procedure to delete employees who joined before a given year.
  7. Create a procedure to transfer an employee to a new department and job.
  8. Write a procedure that prints total number of employees per department.
  9. Create a procedure to validate whether an employee exists before performing any update.
  10. Write a procedure to archive employees earning below a specified salary into a backup table.

🔹 Topic 2: PL/SQL Functions (10 Questions)

Basic → Intermediate

  1. Create a function that returns an employee’s annual salary.
  2. Write a function to return department name for a given employee ID.
  3. Create a function to calculate total employees in a department.
  4. Write a function that checks whether an employee is a manager.
  5. Create a function that returns job title based on job ID.
  6. Write a function to calculate years of experience for an employee.
  7. Create a function that returns average salary of a department.
  8. Write a function that categorizes employees as LOW, MEDIUM, HIGH salary.
  9. Create a function to return employee email based on employee ID.
  10. Write a function to check whether salary is within job salary range.

🔹 Topic 3: Cursors (10 Questions)

Basic → Intermediate

  1. Write a PL/SQL block using an implicit cursor to update salaries.
  2. Use an explicit cursor to display all employees from a given department.
  3. Write a cursor to display employees earning above department average.
  4. Use cursor FOR loop to print employee names and hire dates.
  5. Write a cursor to calculate total salary per department.
  6. Use cursor with parameters to fetch employees by job ID.
  7. Write a cursor to update commission for eligible employees.
  8. Use cursor to copy employees from one department to another table.
  9. Write a cursor to identify employees without managers.
  10. Use cursor to generate a formatted employee salary report.

🔹 Topic 4: Exception Handling (10 Questions)

Basic → Intermediate

  1. Write a PL/SQL block to handle NO_DATA_FOUND when searching employee.
  2. Handle TOO_MANY_ROWS exception while fetching employee data.
  3. Write a procedure that handles DUP_VAL_ON_INDEX during insert.
  4. Handle invalid department ID using user-defined exception.
  5. Write a block that handles salary update failure gracefully.
  6. Create a procedure with exception logging into an error table.
  7. Handle divide-by-zero exception while calculating salary hike.
  8. Write a function that raises an exception if salary is below minimum.
  9. Handle cursor fetch exception when no rows are returned.
  10. Create a procedure that uses RAISE_APPLICATION_ERROR for business rule violations.

💡 Motivation Corner 💪

Learning PL/SQL is not about memorizing syntax — it’s about thinking like a database developer.

Every procedure you write,
every cursor you loop,
every exception you handle
👉 brings you one step closer to mastery.

💥 Don’t skip the hard questions.
💥 Break them, debug them, own them.

That’s how real PL/SQL developers are made.

⚠️ Disclaimer

This content is intended only for educational and practice purposes.
The HR schema and table structures belong to Oracle Corporation.
All scenarios are hypothetical and should not be directly used in production without proper validation and testing.

Scroll to Top