Oracle Database SQL Intermediate Practice Exam: Medium Difficulty 2025

You need to retrieve all employees from the EMPLOYEES table who work in departments 10, 20, or 30, and whose salary is greater than the average salary of their respective department. Which query would accomplish this requirement?

A database developer needs to create a query that displays employee names and their manager names. Some employees do not have managers assigned. The result should include all employees, showing NULL for the manager name when no manager exists. Which type of join should be used?

You execute the following query: SELECT department_id, COUNT(*) FROM employees WHERE salary > 5000 HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC; What will be the result?

Given a table ORDERS with columns ORDER_DATE (DATE type) and TOTAL_AMOUNT (NUMBER type), you need to find orders placed in the last 6 months with amounts between 1000 and 5000, sorted by the most recent orders first. Which query is correct?

You need to update the SALARY column in the EMPLOYEES table by increasing it by 10% for all employees in department 20, but only if their current salary is less than 8000. Which DML statement is correct?

A query returns employee data with NULL values in the COMMISSION_PCT column. You need to display 'No Commission' as text for NULL values and calculate total compensation as salary plus commission. Which expression correctly handles this requirement?

You need to create a table PROJECTS with a primary key on PROJECT_ID, a foreign key referencing DEPARTMENTS(DEPT_ID), and a CHECK constraint ensuring BUDGET is positive. Which CREATE TABLE statement is correct?

Given two tables: CUSTOMERS (customer_id, customer_name) and ORDERS (order_id, customer_id, order_date), you need to find customers who have never placed an order. Which query would produce the correct result?

You need to display the department name, the number of employees in each department, and the average salary per department, but only for departments that have more than 5 employees. Departments with no employees should not be shown. Which query is correct?

You need to retrieve employee records where the LAST_NAME starts with 'S', contains at least one 'a' or 'A' anywhere in the name, and is exactly 5 characters long. Which WHERE clause condition is correct?