50 Oracle Database SQL Practice Questions: Question Bank 2025

Which SQL statement will retrieve all columns from the EMPLOYEES table where the SALARY is greater than 5000 and display the results sorted by LAST_NAME in ascending order?

Examine the following table structure: EMPLOYEES (employee_id, first_name, last_name, department_id, salary) Which query will display the average salary for each department, showing only departments with an average salary greater than 6000?

Which function would you use to replace all NULL values in the COMMISSION_PCT column with 0 when displaying employee data?

You need to retrieve employee names and their department names. The EMPLOYEES table has a DEPARTMENT_ID column, and the DEPARTMENTS table has DEPARTMENT_ID and DEPARTMENT_NAME columns. Some employees do not have an assigned department. Which join should you use to display all employees including those without a department?

Examine this query: SELECT employee_id, last_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700); What type of subquery is being used and when is it executed?

A developer creates a table with the following statement: CREATE TABLE products (product_id NUMBER(6), product_name VARCHAR2(50), price NUMBER(8,2)); Which statement about this table is correct?

You execute the following statements in sequence: INSERT INTO departments VALUES (300, 'Marketing', 100, 1700); SAVEPOINT sp1; UPDATE departments SET department_name = 'Sales' WHERE department_id = 300; SAVEPOINT sp2; DELETE FROM departments WHERE department_id = 300; ROLLBACK TO sp1; What is the state of the DEPARTMENTS table after these statements?

Given the EMPLOYEES table with columns: EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY Which query will display employees hired in the year 2020, showing their full name as a single column labeled 'Full Name' and their annual compensation (salary * 12), sorted by annual compensation in descending order?

A database administrator needs to create a table where the EMPLOYEE_ID column must be unique, cannot be NULL, and should be the primary identifier for each row. The EMAIL column must also be unique across all rows but can be updated. Which combination of constraints should be applied?

Examine this query: SELECT e.last_name, e.salary, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); What will happen when this query is executed?

You need to retrieve all employees whose last name starts with 'S' and ends with 'n'. Which WHERE clause should you use?

Which SQL statement will successfully create a table with a primary key constraint?

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

A developer needs to display employee names and their manager names. The EMPLOYEES table has columns EMPLOYEE_ID, FIRST_NAME, LAST_NAME, and MANAGER_ID where MANAGER_ID references EMPLOYEE_ID. Which type of join is most appropriate?

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

You execute this UPDATE statement: UPDATE employees SET salary = salary * 1.10 WHERE department_id = 50; What happens if you execute ROLLBACK immediately after?

Which query will return employees who work in departments located in the same city as department 20, using a subquery?

Examine this query: SELECT employee_id, last_name FROM employees ORDER BY 3; What will happen when this query is executed?

Which statement about the MERGE statement is true?

You need to find the employee with the third highest salary in the company. Which query will correctly return this result?

Which SQL statement correctly creates a table with a composite primary key consisting of two columns?

Examine the following query: SELECT department_id, COUNT(*) AS emp_count FROM employees WHERE COUNT(*) > 5 GROUP BY department_id; What will happen when this query is executed?

A developer needs to retrieve all employees whose last names start with 'S' and contain exactly 5 characters. Which WHERE clause condition achieves this requirement?

Given the tables EMPLOYEES (employee_id, manager_id, first_name, last_name) and DEPARTMENTS (department_id, manager_id, department_name), you need to find all employees who are also department managers. Which query is correct?

Which statement about the MERGE statement in Oracle SQL is correct?

Examine this query: SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank FROM employees WHERE department_id = 50; If three employees have the same highest salary, what will be the next rank and dense_rank values for the employee with the next lower salary?

A table PRODUCTS has columns product_id, product_name, and price. You need to update the price of all products in the 'Electronics' category by increasing them by 10%, but the category information is stored in a separate CATEGORIES table. Which statement is correct?

What is the result of the following query if the EMPLOYEES table contains a row where both commission_pct and manager_id are NULL? SELECT employee_id FROM employees WHERE commission_pct = manager_id;

Examine the following query structure: SELECT department_id, AVG(salary) as avg_sal FROM employees GROUP BY department_id HAVING AVG(salary) > (SELECT AVG(salary) FROM employees) ORDER BY avg_sal DESC; What does this query accomplish?

A database contains tables ORDERS (order_id, customer_id, order_date) and ORDER_ITEMS (order_id, product_id, quantity, price). You need to find customers who have never ordered product_id 100. Which query is most efficient and correct?

Which SQL statement will successfully create a table with a composite primary key consisting of two columns?

Examine this 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 need to retrieve all employees whose last name starts with 'S' and ends with 'n', with exactly 5 characters total. Which WHERE clause should you use?

Which statement about the TRUNCATE TABLE command is correct?

Examine these tables: EMPLOYEES (employee_id, first_name, last_name, department_id) DEPARTMENTS (department_id, department_name) You need to display all departments and their employees, including departments with no employees. Which query should you use?

What is the result of this query? SELECT ROUND(156.768, -2) FROM DUAL;

A developer creates this view: CREATE VIEW high_salary_emp AS SELECT employee_id, first_name, salary FROM employees WHERE salary > 10000; They then execute: INSERT INTO high_salary_emp VALUES (999, 'John', 8000); What is the result?

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

A database contains tables ORDERS and CUSTOMERS with a foreign key relationship. You need to delete a customer who has existing orders. The foreign key was created WITHOUT the ON DELETE CASCADE option. What will happen if you execute DELETE FROM customers WHERE customer_id = 100?

Examine this complex query: SELECT department_id, AVG(salary) as avg_sal FROM employees GROUP BY department_id HAVING AVG(salary) > (SELECT AVG(salary) * 1.1 FROM employees) ORDER BY avg_sal DESC; What does this query return?

You need to create a view that shows only employees earning more than $50,000. Which CREATE VIEW statement is correct?

Which SQL function should you use to return the current system date and time from the Oracle database server?

You execute the following query: SELECT employee_id, salary FROM employees WHERE department_id = 50 ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY; What does this query return?

A developer needs to update the salary of all employees in department 30 by increasing it by 10%. Which UPDATE statement is correct?

You need to retrieve all employees and their department names. Some employees are not assigned to any department and should still appear in the result with NULL for department name. Which join should you use?

What is the result of the following query? SELECT NVL2(commission_pct, salary * commission_pct, 0) FROM employees WHERE employee_id = 100; Assume the commission_pct for employee 100 is NULL.

You execute: SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5 ORDER BY department_id; What does the HAVING clause do in this query?

A table has a CHECK constraint defined as: CHECK (salary BETWEEN 1000 AND 50000). You attempt to insert a row with salary = NULL. What happens?

You need to write a query that returns employees who work in the same city as any employee from department 50. Which subquery approach is most appropriate?

You execute: SELECT department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees; What does this query return?