Oracle Database Foundations Intermediate Practice Exam: Medium Difficulty 2025

A database administrator needs to ensure that all employee records are properly organized and no duplicate employee IDs exist in the EMPLOYEES table. The table should also prevent NULL values in the EMPLOYEE_ID column. Which combination of constraints would best accomplish this requirement?

An application developer writes the following SQL query: SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 50000 GROUP BY department_id; The query fails to execute. What is the problem and how should it be corrected?

A company's database stores customer orders. The business analyst needs to retrieve all customers who have placed orders, along with their order details, but also wants to include customers who have not yet placed any orders (showing NULL for order details). Which type of join should be used?

In Oracle Database architecture, when a user executes a SELECT statement, the database first checks if the required data blocks are already in memory before reading from disk. Which component of the System Global Area (SGA) is primarily responsible for caching these data blocks?

A database contains a PRODUCTS table and a CATEGORIES table. The PRODUCTS table has a CATEGORY_ID column that references the CATEGORY_ID in the CATEGORIES table. If a user attempts to delete a category that is referenced by existing products, what will happen if the foreign key was created WITHOUT the ON DELETE CASCADE option?

A developer needs to create a view that displays employee names along with their department names by joining the EMPLOYEES and DEPARTMENTS tables. Later, the structure of the EMPLOYEES table is modified by adding a new column. What happens to the view?

An organization's database uses the concept of schemas to organize database objects. A user named SALES_USER wants to query a table named INVENTORY that belongs to the WAREHOUSE_USER schema. What must be true for SALES_USER to successfully execute: SELECT * FROM INVENTORY?

During a transaction, a user executes several INSERT statements into a SALES table. Before issuing a COMMIT, the user's session terminates unexpectedly due to a network failure. What happens to the uncommitted changes and what Oracle Database component is responsible for handling this situation?

A database table ORDERS contains columns ORDER_ID, CUSTOMER_ID, ORDER_DATE, and TOTAL_AMOUNT. A developer needs to retrieve the top 5 highest order amounts along with their order details. Which query approach would correctly accomplish this in Oracle Database?

A company maintains an EMPLOYEES table with sensitive salary information. The HR department wants to allow managers to view employee names and hire dates but not salary information. What database object should be created to accomplish this security requirement while maintaining data integrity?