1) In a project, you need to implement error logging for a complex ETL process where data is loaded into a staging table. If any error occurs during the insertion, you need to capture the error details, including the error code and message. Which PL/SQL approach is most suitable?
2) You are developing a stored procedure to import data from a flat file into a table. The data includes customer information with possible duplicate records. What would be the best strategy to handle duplicates during this process?
3) During a data migration project, you need to transform employee salary data by applying a 10% increase for employees in the "IT" department. Which is the best method to perform this update in PL/SQL?
4) A financial application requires you to create a trigger that checks for any withdrawal transactions exceeding $10,000 and logs them for review. Which trigger type would be most appropriate for this scenario?
5) In a data warehouse project, you need to implement a daily ETL job that loads only new records from a source table into a target table based on the last_modified_date field. Which method would you use?
6) You are developing a reporting tool that queries large datasets. The tool allows users to filter by various columns. To optimize the queries, which PL/SQL feature should you consider implementing?
7) In a scenario where you need to process a batch of records from a table and apply a transformation to each record, which PL/SQL feature is best suited for this task?
8) A logistics company needs a stored procedure to update the shipment_status in the orders table based on a specific set of conditions. What is the best practice to handle multiple conditions in this procedure?
9) In an ETL project, you need to calculate the cumulative sum of sales for each product category in a PL/SQL block. Which approach is the most efficient?
10) A client requests an enhancement where new customer records are assigned a unique customer ID using a sequence. How would you implement this in PL/SQL?
11) In a banking application, you need to create a procedure that transfers funds from one account to another. The procedure should first check if the sender's account has sufficient funds. If the funds are insufficient, it should raise an error and not proceed with the transfer. Which approach would best handle this scenario?
12) In a project, you are tasked with implementing audit logging for changes made to a critical table. The client requires that any UPDATE or DELETE operation on the table be logged with details such as the user performing the change, timestamp, and old data values. What is the best way to implement this?
13) You have a stored procedure that processes employee bonuses based on sales performance. The bonus is calculated based on various sales criteria, and the client wants a report generated after the procedure runs, listing employees who received bonuses. What is the most efficient way to implement this?
14) In an inventory management system, you need to ensure that product stock levels do not fall below a minimum threshold when processing orders. If an order reduces the stock below the threshold, it should be rejected. What is the best way to enforce this rule?
15) Your client requires a monthly sales report that aggregates data from multiple tables, including orders, customers, and products. The report must be generated quickly at the beginning of each month. How can you optimize this process in PL/SQL?
16) During a data migration project, you need to transfer data from an old system to a new one. The source system uses different date formats, and some date fields contain invalid or null values. How would you handle this during the migration process?
17) In a healthcare application, you need to implement a feature that checks if a patient's lab test results are within the normal range. If not, an alert should be raised. The normal ranges vary based on the type of test. What would be the most efficient way to implement this check?
18) A client requests a new feature where customer orders need to be flagged if they are not delivered within 5 days of the order date. You need to automate this check and update the status in the orders table. What approach would you take?
19) In a retail analytics project, you are required to create a PL/SQL function that calculates the average sales per customer for a given month. The function should be efficient and handle cases where no sales data is available. What is the best implementation strategy?
20) You have a batch process that inserts large volumes of data into a table. Occasionally, due to network issues, the process fails midway, leaving incomplete data. How would you ensure that only complete batches are inserted into the table, avoiding partial inserts?
