PL/SQL in Action: Banking and E-commerce Scenarios Quiz Quiz

This quiz explores practical PL/SQL use cases found in banking and e-commerce systems, focusing on concepts like transactions, triggers, stored procedures, exception handling, and data validation. Sharpen your understanding of real-world PL/SQL applications relevant to financial and online retail workflows.

1. Transaction Management

   In a banking application, which PL/SQL statement ensures that a funds transfer between two accounts is treated as a single, atomic operation?

   1. WHEN OTHERS
   2. DECLARE
   3. COMMIT
   4. CURSOR

   Explanation: COMMIT makes a transaction permanent, ensuring that both debit and credit operations are saved together, which is essential for atomicity in banking scenarios. WHEN OTHERS is used for exception handling, not transaction control. DECLARE is for defining variables and cursors, not for managing transactions. CURSOR is used for retrieving data row by row, unrelated to transaction atomicity.

2. Trigger for Audit Trail

   Which PL/SQL feature would you use to automatically record every deletion of a customer order in an e-commerce system for auditing purposes?

   1. ROLLBACK
   2. SAVEPOINT
   3. AFTER DELETE trigger
   4. FOR UPDATE

   Explanation: An AFTER DELETE trigger automatically executes after a row is deleted and is ideal for logging deletions in audit tables. SAVEPOINT marks a point within a transaction for partial rollbacks, not for automatic actions. ROLLBACK undoes changes without logging. FOR UPDATE is used for locking records during queries and does not handle auditing.

3. Stored Procedure for Payment Processing

   A PL/SQL stored procedure is most suitable for which banking scenario?

   1. Batch processing multiple bill payments for several accounts
   2. Defining a simple variable
   3. Displaying a single customer address
   4. Formatting a report header

   Explanation: Stored procedures are ideal for encapsulating complex logic like batch processing multiple payments, ensuring code reusability and transaction integrity. Displaying a single address is a simple query, not requiring a procedure. Formatting a report header is best done in reporting software. Defining a variable is done within blocks, not as a stored procedure.

4. Exception Handling

   In an e-commerce PL/SQL block, which construct gracefully handles an invalid product ID error during checkout?

   1. EXCEPTION
   2. RENAME
   3. OPEN
   4. SAVE

   Explanation: The EXCEPTION block in PL/SQL is used to handle errors such as invalid product IDs, allowing alternative actions or error messages without crashing the process. OPEN is for cursors, not for error handling. RENAME is used to rename objects like tables, which is unrelated to error handling. SAVE is not a PL/SQL statement.

5. Data Validation with PL/SQL

   Which PL/SQL technique would you use to ensure that a customer's withdrawal does not exceed their bank account balance before updating the record?

   1. ALTER TABLE statement
   2. CURSOR FOR LOOP only
   3. IF-THEN-ELSE logic in a PL/SQL block
   4. EXISTS constraint

   Explanation: IF-THEN-ELSE logic lets you compare the withdrawal amount with the account balance and prevent invalid transactions. EXISTS is a SQL operator, not a PL/SQL constraint. CURSOR FOR LOOP can retrieve data but does not enforce validation by itself. ALTER TABLE is for changing table structures, not runtime validation.

6. Trigger for Inventory Update

   How can you automatically update inventory counts after every successful purchase transaction in an e-commerce database?

   1. Using an AFTER INSERT trigger on the purchase table
   2. By creating an INDEX
   3. By running SELECT statements
   4. Via a SESSION

   Explanation: An AFTER INSERT trigger on the purchases table allows automatic updates to the inventory whenever a new purchase is made. SELECT statements only retrieve data and cannot make automatic changes. INDEX is used for faster data retrieval, not for automatic updates. SESSION relates to user connections, not inventory management.

7. Using Cursors for Statements

   In banking systems, which PL/SQL element allows you to retrieve and process multiple unpaid invoices for a customer one by one?

   1. CONSTRAINT
   2. ANALYZE
   3. Explicit cursor
   4. PRIMARY KEY

   Explanation: Explicit cursors are defined by PL/SQL programmers to fetch and process multiple rows, such as unpaid invoices, record by record. CONSTRAINT is for enforcing rules like uniqueness. PRIMARY KEY ensures unique values but does not process rows. ANALYZE is a command to gather statistics, not for processing result sets.

8. Bulk Processing in PL/SQL

   Which PL/SQL feature efficiently updates the shipping status for hundreds of e-commerce orders in a single execution?

   1. ROWNUM
   2. ORDER BY clause
   3. FORALL statement
   4. FETCH FIRST

   Explanation: The FORALL statement lets you perform bulk DML operations, like updating many shipping statuses at once, improving performance. ROWNUM assigns row numbers, not for bulk operations. ORDER BY is for sorting results, and FETCH FIRST limits rowsets but doesn't update multiple records efficiently.

9. Preventing Duplicate Transactions

   What method can a PL/SQL developer use to avoid double-charging a customer during online payment processing?

   1. Increase the payment amount each time
   2. Add more NULL values to the table
   3. Remove COMMIT statements
   4. Check for existing payment records before inserting a new one

   Explanation: Verifying for existing payments before inserting is a reliable way to prevent duplicate transactions and ensure data integrity. Adding NULL values does not help avoid duplicates. Increasing payment amounts is illogical and could harm customers. Removing COMMIT leads to uncommitted transactions and doesn't prevent duplicates.

10. Using Functions in E-commerce Reporting

    How can PL/SQL functions help generate a customer's total monthly spend in an e-commerce portal?

    1. By rolling back all monthly transactions
    2. By returning a calculated value based on orders placed within the month
    3. By truncating the database after each report
    4. By modifying table structures directly

    Explanation: A PL/SQL function can compute and return the total spend by aggregating order amounts, aiding monthly reporting. Modifying table structures is for schema management, not calculations. Rolling back transactions would undo valid purchases. Truncating the database erases data and is never suitable for reporting.