Oracle AI Database SQL Associate Practice Exam 2025: Latest Questions

You need to list each employee's last name and salary from the EMPLOYEES table. Which SELECT statement is correct?

A user runs an UPDATE statement to change rows in a table, then realizes it was incorrect and issues ROLLBACK. Which statement best describes what happens?

You want to return all rows from ORDERS where the SHIP_DATE is unknown. Which predicate should you use in the WHERE clause?

You need to return the current system date and time from Oracle in a query. Which expression is correct?

A report must show each employee's annual salary, defined as monthly SALARY multiplied by 12, and label the column as ANNUAL_SALARY. Which query is correct?

You want to show total salary by department, including only departments whose total salary exceeds 50000. Which clause should be used to filter by the aggregate result?

A developer needs to insert a row into DEPARTMENTS with DEPARTMENT_ID, DEPARTMENT_NAME, and MANAGER_ID. Which statement is best practice to avoid errors if the table structure changes?

A query joins CUSTOMERS and ORDERS. Some customers have never placed an order, but the business still wants those customers listed with NULLs for order columns. Which join should be used?

You run the statement: SELECT department_id, COUNT(*) FROM employees; and Oracle returns ORA-00937: not a single-group group function. What is the correct fix?

You need to remove all rows from a large staging table quickly, and you do not need the operation to be reversible with ROLLBACK. Which approach is most appropriate?

You need to return each employee's salary along with the department average salary, without reducing the number of rows returned (one row per employee). Which approach is best?

A table PRODUCTS has a column PRICE defined as NUMBER(8,2). Users sometimes enter data with more than two decimal places. What happens when inserting PRICE=10.999?

You run the following statements in your session: 1) UPDATE accounts SET balance = balance - 100 WHERE account_id = 10; 2) SAVEPOINT s1; 3) UPDATE accounts SET balance = balance + 100 WHERE account_id = 20; 4) ROLLBACK TO s1; 5) COMMIT; Which result is correct?

A report must list all customers, including those with no orders. For customers with no orders, the total order amount should display as 0. Which SQL pattern is most appropriate?

You need to assign a sequence-generated primary key for each inserted row into table SALES. Which statement is the recommended way to supply the key during INSERT?

You want to display employees sorted by department, and within each department show the highest salary first. Which ORDER BY clause satisfies this requirement?

A developer runs: SELECT TO_CHAR(TO_DATE('2026-01-15','YYYY-MM-DD'),'DD-MON-YYYY') FROM dual; In one environment it returns '15-JAN-2026', but in another it returns a different language abbreviation for the month. What is the best way to ensure the month is always in English?

You need a query that returns one row per department showing the department name and the number of employees, including departments with zero employees. Which is the most appropriate solution?

A table ORDERS has millions of rows. You frequently run queries like: SELECT * FROM orders WHERE TRUNC(order_date) = DATE '2026-01-15'; Performance is poor even though there is an index on ORDER_DATE. What is the best solution to allow index usage while keeping the same predicate behavior?

You run this statement: UPDATE employees e SET e.salary = (SELECT AVG(salary) FROM employees) WHERE e.department_id IN (SELECT department_id FROM departments WHERE location_id = 1700); It fails with an error indicating a restriction on modifying a table referenced in a subquery. Which rewrite avoids the error while preserving the intent?

You are investigating why an application query returns fewer rows than expected. The query is: SELECT * FROM employees WHERE commission_pct = NULL; What change correctly returns employees whose COMMISSION_PCT is NULL?

A table ORDERS has one row per order. You need a report showing each CUSTOMER_ID with the total number of orders and the total ORDER_TOTAL, but only for customers having at least 5 orders. Which query meets the requirement?

You need to display the time difference between two TIMESTAMP columns START_TS and END_TS in a table as the number of minutes (including fractional minutes). Which approach is recommended in Oracle SQL?

A developer runs these statements in one session: UPDATE accounts SET balance = balance - 100 WHERE account_id = 10; SAVEPOINT s1; UPDATE accounts SET balance = balance + 100 WHERE account_id = 20; ROLLBACK TO s1; COMMIT; Which outcome is correct?

You need a list of departments that currently have no employees. Tables: DEPARTMENTS(dept_id, dept_name) and EMPLOYEES(emp_id, dept_id). Which query returns all departments with zero employees?