1) In a database system, you are tasked with creating a monthly report that aggregates sales data. The system should process large amounts of data quickly. What is the best approach for efficient report generation?
2) You need to ensure that all data inserted into the customer_orders table has a valid customer ID. If the customer ID is invalid, the insertion should fail. How can you enforce this constraint?
3) You are tasked with creating a banking system where each customer’s balance must be updated when a withdrawal is made. If the withdrawal amount exceeds the balance, the transaction should be canceled. Which PL/SQL feature would you use to implement this functionality?
4) In an e-commerce application, you need to generate a report of products sold within a specific date range. The report should display the total sales and the number of items sold. What would be the most efficient way to write the report?
5) You are developing a scheduling system where tasks need to be scheduled automatically for execution. Which PL/SQL feature would allow you to schedule the execution of a procedure without manual intervention?
6) You are implementing a log management system where every update to the logs table should be tracked. What is the most efficient way to automatically record every update to this table?
7) In an online booking system, you need to ensure that double bookings are prevented for the same time slot. Which of the following methods will ensure that double bookings are avoided?
8) In a financial application, you need to track the amount of money being transferred between accounts. If the transfer fails, you need to ensure that no money is deducted or added to any account. Which approach would best prevent inconsistent data?
9) In a customer feedback system, you need to track which feedback is high priority. If a feedback is marked as high priority, it should be escalated automatically. What PL/SQL feature would you use to implement this?
10) In a payment processing system, you need to ensure that the system can handle transactions even during high traffic periods. What feature would you use to improve the performance of frequent transactions?
11) In a medical records system, a patient’s data must be updated only if the provided record is newer than the current one. How would you ensure that the database updates the record only when the new data is more recent?
12) You are working on a payment gateway system that needs to securely store transaction details. What is the best way to ensure the security and integrity of sensitive transaction data?
13) You are creating a customer loyalty program where customers earn loyalty points based on their purchase amounts. Which PL/SQL feature would you use to track and update customer points automatically when a new purchase is made?
14) You are working with a large dataset and need to optimize a query that frequently retrieves data from multiple tables. How would you improve the performance of this query?
15) You are implementing a data migration from an old database system to a new one. During the migration, some records contain duplicate data. How would you handle the detection and removal of duplicates?
16) You are working on an order processing system where each order can have multiple items. When processing an order, you need to calculate the total cost of the items. What is the most efficient way to calculate the total cost for each order?
17) You are tasked with creating an alert system that notifies administrators when certain events occur. Which PL/SQL feature would you use to send notifications automatically when these events are detected?
18) You are designing a warehouse management system where product stock levels must be updated as items are sold. If the stock is insufficient, the system should not process the sale. How would you enforce this rule?
19) You are working on a library management system and need to generate a report of overdue books. The report must show the due date and the number of days overdue for each book. How would you efficiently generate this report?
20) You are working on a data integrity project and need to ensure that there are no duplicate customer entries in the database. Which approach would best guarantee data integrity?
