Oracle AI Database SQL Associate Advanced Practice Exam: Hard Questions 2025

A database administrator discovers that a query using a correlated subquery in the WHERE clause is performing poorly against a table with 5 million rows. The subquery references the outer query's column and performs an aggregate function. Which optimization strategy would provide the MOST significant performance improvement while maintaining equivalent results?

An application performs concurrent INSERT operations on a table with a sequence-generated primary key. After a transaction rollback, users notice gaps in the sequence values. The development team is concerned about these gaps affecting application logic. What is the BEST explanation and recommended action?

Given a hierarchical EMPLOYEES table with EMPLOYEE_ID and MANAGER_ID columns, you need to identify all employees in a management chain where any manager in the chain has a salary greater than $200,000, including the high-earning manager and all their subordinates at any level. Which query structure would accomplish this most efficiently?

A transaction performs the following sequence: INSERT into table A, UPDATE table B, SAVEPOINT sp1, DELETE from table C, UPDATE table A, SAVEPOINT sp2, INSERT into table B. A constraint violation occurs during the final INSERT. The application executes ROLLBACK TO SAVEPOINT sp1. What is the state of the database after this rollback operation?

You are analyzing a complex query that joins five tables and uses GROUP BY with HAVING clause. The execution plan shows that Oracle is performing a HASH JOIN followed by SORT GROUP BY operation. The HAVING clause filters on an aggregate function result. To optimize this query, where should filtering conditions be placed for maximum performance?

A partitioned table uses RANGE partitioning on ORDER_DATE with monthly partitions. A query with WHERE ORDER_DATE BETWEEN TO_DATE('2024-01-15','YYYY-MM-DD') AND TO_DATE('2024-03-15','YYYY-MM-DD') shows partition pruning is not occurring in the execution plan. What is the MOST likely cause?

An application uses the following query repeatedly: SELECT * FROM orders WHERE UPPER(customer_name) = UPPER(:cust_name). Performance is poor despite an index on customer_name. What is the MOST effective solution to improve performance while maintaining case-insensitive searching?

You execute a complex analytical query using RANK() and DENSE_RANK() window functions partitioned by department and ordered by salary DESC. An employee with salary $85,000 is ranked 3 by RANK() and 2 by DENSE_RANK() within their department. What can you definitively conclude about the salary distribution?

A developer creates a view that joins three tables and includes aggregate functions with GROUP BY. Users report that INSERT operations fail against this view. After adding INSTEAD OF INSERT trigger, INSERTs work but data appears inconsistent. What is the fundamental issue?

A query uses CASE expressions within an aggregate function: SELECT department_id, SUM(CASE WHEN salary > 100000 THEN salary ELSE 0 END) as high_salaries, SUM(CASE WHEN salary <= 100000 THEN 1 ELSE 0 END) as low_salary_count FROM employees GROUP BY department_id. The results show NULL for high_salaries in departments where no employee earns over $100,000. What is the explanation?