50 Oracle AI Database SQL Associate Practice Questions: Question Bank 2025

Which statement best describes a primary key constraint in an Oracle relational table?

You need to list employees whose commission is unknown. Which WHERE clause correctly returns only rows where COMMISSION_PCT has no value?

Given a table ORDERS(order_id, status), which SQL statement correctly sets STATUS to 'CLOSED' only for order 1001?

Which statement about COMMIT and ROLLBACK is true in Oracle?

A reporting query must show only unique department IDs from the EMPLOYEES table. Which query is best practice for returning unique values from one column?

You are troubleshooting a query that returns fewer rows than expected after adding a filter on a nullable column in the WHERE clause. The query is: SELECT e.employee_id, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.location_id = 1700; What is the issue?

You need to store a new row in table PRODUCTS(product_id, name) but must ensure the insert is not permanent unless later steps succeed. Which approach is recommended?

A table SALES contains a NUMBER column AMOUNT that may be NULL. You need a query that returns AMOUNT but shows 0 when AMOUNT is NULL. Which expression is correct?

You want to return one row per department showing the highest salary and the employee(s) who earn it. Which query correctly returns all employees tied for the maximum salary within their department?

A developer creates a view to simplify reporting: CREATE VIEW v_emp AS SELECT employee_id, salary FROM employees; Later, they run: INSERT INTO v_emp(employee_id, salary) VALUES (999, 5000); The insert fails. Which is the most likely cause based on common Oracle behavior and best practices?

You run the query: SELECT last_name FROM employees WHERE salary BETWEEN 5000 AND 8000; Which statement is true about the rows returned?

You need to display only unique department IDs from the EMPLOYEES table. Which query is the best choice?

A developer executes an UPDATE statement but does not issue COMMIT. The session then disconnects unexpectedly. What happens to the uncommitted changes?

You need to return all departments, including those with no matching employees. Which query correctly does this?

You need to categorize employees based on commission_pct, treating NULL as 0. Which expression correctly returns 'NO COMM' when commission_pct is NULL or 0, otherwise 'HAS COMM'?

A report must show the top 3 highest-paid employees in each department. Which analytic function approach is most appropriate?

Your query is returning too many rows because of an unintended Cartesian product. Which mistake most commonly causes this in a multi-table query?

You need to insert rows into SALES_SUMMARY from SALES, but only for sales that do not already exist in SALES_SUMMARY based on (sale_id). Which approach is a best practice in a single SQL statement?

You need to store valid status codes in an ORDERS table such that status must be one of ('NEW','SHIPPED','CANCELLED') and the rule must be enforced for all DML. Which schema object should you use?

You want to delete from PARENT where no matching rows exist in CHILD. The CHILD.parent_id column is nullable. Which WHERE condition is safest to avoid unexpected results caused by NULLs?

You need to ensure that each row in the EMPLOYEES table can be uniquely identified and referenced by other tables. Which schema object best supports this requirement?

A developer runs the following statement and receives ORA-00979: not a GROUP BY expression: SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY department_id; What is the correct fix?

A user updates rows in a session and wants to permanently save the changes so they are visible to other sessions. Which statement should the user execute?

You need to return all employees, including those who are not assigned to a department. Which query should you use?

A report must categorize each order as 'SMALL' when TOTAL_AMOUNT < 1000, 'MEDIUM' when TOTAL_AMOUNT is between 1000 and 4999, and 'LARGE' otherwise. Which SQL approach is recommended?

You want to display employee last names in the format 'Smith' (first letter uppercase, remaining letters lowercase), regardless of how the data is stored. Which expression accomplishes this?

You execute: INSERT INTO employees(emp_id, last_name) VALUES (1001, 'Ng'); A few seconds later, you run ROLLBACK; in the same session. What is the result?

A query must list each employee with their department’s average salary, and it must not reduce the result to one row per department. Which SQL technique best satisfies this requirement?

You want to find customers who have placed at least one order, and also customers who have placed no orders. You need a single query that labels them as 'ORDERED' or 'NO_ORDERS'. Which approach is best?

A team created a view to simplify reporting: CREATE VIEW v_emp AS SELECT emp_id, salary FROM employees; They later run: INSERT INTO v_emp(emp_id, salary) VALUES (2001, 5000); Assuming EMPLOYEES.EMP_ID is a primary key and there are no other constraints, which statement is true?

You need to list all employees, including those who are not assigned to any department. Which query should you use?

You attempt to insert a row into CHILD_T with a foreign key referencing PARENT_T. The insert fails with an integrity constraint violation. Which is the most likely cause?

You need to make a change permanent after running several UPDATE statements. Which statement should you execute?

Which SQL clause is used to restrict the groups returned by a query that uses GROUP BY?

A report requires each department's total salary and only those departments whose total salary exceeds 100000. Which query meets the requirement?

You want to update SALARY for employees in department 50 by increasing it 10%, but only for those whose current salary is below the department's average salary. Which statement correctly performs this update?

You have a column PHONE that may contain leading and trailing spaces. You must compare it to the value '8005551212' ignoring those spaces. Which expression is the best choice?

A developer wants to prevent users from inserting rows into ORDERS with a NULL CUSTOMER_ID. Which schema object should be used to enforce this rule most directly?

You must return the top 3 highest-paid employees per department. Which approach is appropriate?

You run this query and get ORA-01427: single-row subquery returns more than one row: SELECT employee_id, last_name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE location_id = 1700); Which change fixes the error while keeping the intent of returning employees in any department located at location 1700?

You need to display each employee's full name as a single column in the format "LAST, First" where LAST is uppercase and First is capitalized (first letter uppercase, rest lowercase). Which expression meets the requirement?

A developer runs DML statements in a session but forgets to commit. The session then disconnects unexpectedly. What happens to the uncommitted changes?

Which statement correctly describes a primary key constraint in Oracle?

You want to list all departments, including those with no employees, along with the number of employees in each department. Which query is correct?

A report must show products whose sales are above the average sales of all products. Which clause is most appropriate to implement this filter?

You need to increase the salary by 10% only for employees in department 50 and ensure that either all qualifying rows are updated or none are if an error occurs. Which approach is recommended?

A table has a column COMMENTS of type VARCHAR2(2000). You need to display 'N/A' when COMMENTS is NULL, otherwise display COMMENTS. Which expression is correct?

You must return exactly one row per customer, showing the most recent order date for each customer. Which SQL approach is most appropriate?

You need to generate a unique numeric identifier for each inserted row in a table and guarantee uniqueness without querying existing rows. Which schema object is the best fit?

You are troubleshooting a query that returns more rows than expected after joining ORDERS to ORDER_ITEMS. Each order can have multiple items, but the report should show one row per order with total amount. Which solution is the best practice?