oracle sql developer practice online free Intermediate Practice Exam: Medium Difficulty 2025

A reporting application queries an ORDERS table frequently. The table has a primary key (ORDER_ID) and a foreign key (CUSTOMER_ID) referencing CUSTOMERS(CUSTOMER_ID). The DBA asks you to explain what the foreign key constraint primarily guarantees. Which statement is correct?

You need to return the top 5 highest-paid employees from EMPLOYEES, showing EMPLOYEE_ID, SALARY, and ordering by SALARY descending. Which query correctly returns exactly 5 rows in Oracle Database?

A customer service query must list each employee and a derived column LEVEL_NAME based on JOB_ID: if JOB_ID starts with 'SA_' then 'SALES', if it starts with 'IT_' then 'TECH', otherwise 'OTHER'. Which SQL expression should you use?

You need to list employees whose last name contains the literal character '_' (underscore), and sort results by LAST_NAME ascending then EMPLOYEE_ID descending. Which WHERE and ORDER BY clause combination is correct?

A data quality report needs to display a masked version of PHONE_NUMBER. If PHONE_NUMBER is in the form '555.123.4567', display '(***) ***-4567' (keeping only the last 4 digits). Which single-row function approach best fits?

You are asked to display employees with their hire month in the format 'Month YYYY' (example: 'January 2024') and ensure it is not padded with trailing spaces. Which expression is correct?

A table SALES has a column AMOUNT stored as VARCHAR2, containing values like '1200.50' and sometimes 'N/A'. You must calculate total sales amount, treating non-numeric values as 0, and return a numeric sum. Which query is the best solution?

You need a report by department showing department_id, the number of employees, and the average salary. Only include departments with at least 5 employees, and sort by average salary descending. Which query is correct?

A manager wants to see each employee’s salary, the department average salary, and the difference between the employee salary and the department average. You must return one row per employee. Which approach is correct?

You must list all departments, including those with no employees, and show the number of employees in each department. Which query meets the requirement?