PLSQL Practice Set 1

By /

Refer the Sample Schema for this practice- https://blog.otlms.in/oracle-pl-sql-practice-reference-table-schemas-for-real-world-use-cases/

This practice set is designed to push your thinking beyond syntax and into real-time database scenarios used in banking systems, fintech platforms, analytics engines, and stock market applications.

👉 Rules:

  • Only questions — no hints, no solutions.
  • Expect challenges that mirror actual enterprise projects.
  • If a question feels tough… good. That’s where real learning begins 💪

📌 1. Use of Scalar Data Types in PL/SQL

🏦 Banking & Financial Scenarios

  1. Create a PL/SQL block to calculate monthly interest for a savings account using appropriate scalar data types.
  2. Write a PL/SQL program that stores customer age, PAN number, account balance, and account status using suitable scalar data types.
  3. Design a PL/SQL block to validate a transaction amount against a daily withdrawal limit.
  4. Develop a PL/SQL routine that handles currency conversion using NUMBER and ROUND functions.
  5. Write a PL/SQL block that stores and displays stock prices with precision suitable for share market trading.

🗄️ Database Integration

  1. Fetch account balance from a BANK_ACCOUNTS table and store it in a scalar variable.
  2. Calculate total transaction value from a TRANSACTIONS table using scalar variables.

📌 2. Variable Declaration and Initialization

💳 FinTech & Payments Use Cases

  1. Declare and initialize variables to store UPI transaction details such as transaction_id, amount, and status.
  2. Write a PL/SQL block that initializes a variable with the current exchange rate and calculates converted amount.
  3. Declare variables to store loan principal, rate of interest, and tenure, then compute EMI.
  4. Initialize variables using SELECT INTO from a CUSTOMER table.

🗄️ Database Integration

  1. Retrieve employee salary from an EMPLOYEES table and initialize a variable with it.
  2. Initialize a variable with the highest stock price from a STOCK_PRICES table.

📌 3. Global and Local Variables

🏢 Enterprise Application Scenarios

  1. Create a PL/SQL block with a global variable representing bank branch code and local variables for customer transactions.
  2. Demonstrate how a global variable can store application-level tax rate while local variables calculate invoice totals.
  3. Write a nested block where a local variable overrides a global variable for transaction fee calculation.
  4. Use global variables to track total trades processed in a stock trading session.

🗄️ Database Integration

  1. Use a global variable to store total number of customers fetched from a CUSTOMERS table.
  2. Use local variables to process each row retrieved from a TRANSACTIONS table.

📌 4. Bind and Substitution Variables

🔄 Real-Time User Interaction Scenarios

  1. Use substitution variables to accept customer ID and fetch account details.
  2. Write a PL/SQL block using bind variables to insert transaction data into a PAYMENTS table.
  3. Accept stock symbol as a substitution variable and display its latest price.
  4. Use bind variables in a PL/SQL block executed from SQL*Plus for loan approval logic.

🗄️ Database Integration

  1. Insert values into a BANK_LOG table using bind variables.
  2. Update account balance using bind variables based on user input.

📌 5. Control Structures in PL/SQL

📊 Decision Making & Analysis

  1. Use IF-ELSE to determine loan eligibility based on credit score.
  2. Write a PL/SQL block using CASE statement to classify transactions as LOW, MEDIUM, or HIGH value.
  3. Use a FOR loop to calculate yearly interest for multiple years.
  4. Use a WHILE loop to simulate monthly SIP investments.
  5. Implement nested IF conditions for fraud detection logic.

🗄️ Database Integration

  1. Loop through records of a TRANSACTIONS table and calculate total debit and credit.
  2. Use control structures to apply penalties on overdue loans stored in a LOANS table.

📌 6. Anonymous Block and Nested Anonymous Block

🧠 Complex Business Logic Scenarios

  1. Write an anonymous PL/SQL block to generate a daily transaction summary.
  2. Create a nested anonymous block to calculate brokerage charges inside a stock trading transaction.
  3. Use nested blocks to handle exception scenarios during fund transfer.
  4. Write an anonymous block that processes salary credits for multiple employees.

🗄️ Database Integration

  1. Fetch customer details from a CUSTOMERS table using an anonymous block.
  2. Use a nested anonymous block to update balances in ACCOUNTS and log entries in TRANSACTION_LOG.

🔥 Final Challenge for You

💡 If you can confidently solve all these exercises, you’re no longer just “learning PL/SQL” — you’re thinking like a database developer in a real enterprise system.

Take your time. Break things. Debug harder.
That’s how production-ready PL/SQL developers are made.

⚠️ Disclaimer

This post is created solely for educational and practice purposes.
All scenarios, table names, and business use cases are fictional and do not represent real systems or organizations.
Use these exercises responsibly while learning Oracle PL/SQL in a controlled environment.

✨ Happy Coding & Keep Practicing PL/SQL Like a Pro!

Check out: next level (procedures, functions, cursors, exceptions, packages)

Scroll to Top