Oracle AI Database SQL Associate Intermediate Practice Exam: Medium Difficulty 2025

A database administrator needs to identify all employees who earn more than the average salary in their respective departments. Which SQL statement accomplishes this requirement?

A developer executes the following sequence of commands: INSERT INTO orders VALUES (101, 5000, SYSDATE); SAVEPOINT sp1; UPDATE orders SET amount = 6000 WHERE order_id = 101; SAVEPOINT sp2; DELETE FROM orders WHERE order_id = 101; ROLLBACK TO sp1; What is the final state of the order with order_id 101?

An analyst needs to display employee names with their hire dates formatted as 'Day, DD Month YYYY' (e.g., 'Monday, 15 January 2023'). The HIRE_DATE column is of DATE datatype. Which function combination achieves this?

A company maintains CUSTOMERS and ORDERS tables. Management wants a report showing all customers and their total order amounts, including customers who have never placed an order (showing NULL or 0 for their total). Which query structure is most appropriate?

A developer needs to update employee salaries in the EMPLOYEES table, increasing them by 10% for employees in department 50, but only if the resulting salary does not exceed 15000. Which statement correctly implements this logic?

Given a PRODUCTS table with columns PRODUCT_ID, PRODUCT_NAME, CATEGORY, and PRICE, which query returns the three most expensive products in each category?

An application requires a view that automatically restricts users from inserting or updating employee records with salaries exceeding 20000. Which view creation option enforces this constraint?

A report requires displaying employee information with their manager's name. The EMPLOYEES table has columns EMPLOYEE_ID, EMPLOYEE_NAME, and MANAGER_ID (which references EMPLOYEE_ID of the manager). Which query correctly retrieves this information?

A database contains a SALES table with columns SALE_ID, PRODUCT_ID, SALE_DATE, and AMOUNT. A developer needs to calculate a running total of sales amounts ordered by sale date. Which query accomplishes this?

A developer needs to handle potential NULL values in the COMMISSION_PCT column when calculating total compensation (salary + commission). If commission is NULL, it should be treated as 0. Which expression correctly implements this?