Oracle Database SQL Practice Exam 2025: Latest Questions

Which SQL statement retrieves all columns from the EMPLOYEES table where the salary is greater than 5000?

You need to sort employee records by LAST_NAME in ascending order and then by HIRE_DATE in descending order. Which ORDER BY clause is correct?

What will the following query return if the COMMISSION_PCT column contains NULL values for some employees? SELECT employee_id, salary * commission_pct AS commission FROM employees;

Which function would you use to replace NULL values in the COMMISSION_PCT column with 0?

A developer writes the following query to find employees in departments 10, 20, and 30: SELECT employee_id, last_name FROM employees WHERE department_id = 10 OR 20 OR 30; What is wrong with this query?

Which query correctly performs an inner join between EMPLOYEES and DEPARTMENTS tables on the DEPARTMENT_ID column?

You need to retrieve all employees and their department names, including employees who are not assigned to any department. Which join type should you use?

What is the result of the following query? SELECT COUNT(*), COUNT(commission_pct) FROM employees;

A developer executes the following INSERT statement: INSERT INTO departments (department_id, department_name) VALUES (100, 'Marketing'); What must be done to make this change permanent in the database?

You need to create a query that finds employees whose salary is greater than the average salary of all employees in their department. Which approach is correct?

You need to retrieve all employees whose last name starts with 'S' and contains exactly 5 characters. Which WHERE clause should you use?

Examine this query: SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 5000 GROUP BY department_id; Why will this query fail?

You execute the following statement: DELETE FROM departments; What happens if the EMPLOYEES table has a foreign key constraint referencing DEPARTMENTS with no cascading options?

Which query will return employees who work in departments located in 'Seattle' or have a salary greater than 10000, but NOT both conditions?

What is the result of this expression: SELECT TRUNC(SYSDATE, 'MONTH') FROM DUAL;

You need to create a view that joins three tables and includes a WHERE clause. Users should only be able to insert or update rows through the view if they satisfy the WHERE condition. Which option must be included in the CREATE VIEW statement?

Examine this query: SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) as rank_sal, DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank_sal FROM employees; If three employees have the same highest salary of 10000 and the next salary is 9000, what will be the rank_sal and dense_rank_sal for the employee earning 9000?

Which statement about correlated subqueries is TRUE?

You execute this statement: CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE DEFAULT SYSDATE, total_amount NUMBER(10,2) CHECK (total_amount > 0), status VARCHAR2(20) DEFAULT 'PENDING' ); Then you execute: INSERT INTO orders (order_id, total_amount) VALUES (101, 500); What values will be stored for order_date and status?

You need to write a query that returns the employee_id, employee_name, department_name, and for employees without a department assignment, display 'Unassigned' as the department_name. The query must return ALL employees including those not assigned to any department. Which query is correct?

You need to create a table named PRODUCTS with columns PRODUCT_ID, PRODUCT_NAME, and PRICE. The PRODUCT_ID should automatically generate sequential numbers starting from 1000. Which CREATE TABLE statement accomplishes this in Oracle 12c and later?

Examine the following query: SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 5000 GROUP BY department_id; What is the result of executing this query?

You execute the following transaction: UPDATE employees SET salary = salary * 1.1 WHERE department_id = 50; SAVEPOINT sp1; DELETE FROM employees WHERE employee_id = 101; SAVEPOINT sp2; UPDATE employees SET commission_pct = 0.15 WHERE employee_id = 102; ROLLBACK TO sp1; What is the state of the database after this transaction sequence?

Which query will display employee names and their manager names, including employees who do not have a manager?

Examine the following correlated subquery: SELECT e.employee_id, e.last_name, e.salary FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); Which statement accurately describes this query's behavior and performance characteristics?