oracle sql developer practice online free Advanced Practice Exam: Hard Questions 2025

You are reviewing a schema design where table ORDERS has a column CUSTOMER_ID that references CUSTOMERS.CUSTOMER_ID. The requirement is: "An order must always belong to exactly one customer, and the database must prevent orphan orders." Which design choice best enforces the requirement with the least reliance on application logic?

Given table T with column VALS containing comma-separated tokens like 'A,B,B,C' (duplicates possible, order significant). You must return one row per token, preserving the original token order within each row, using SQL only. Which query best meets the requirement?

You need the top 3 earners per DEPT_ID from EMP(EMP_ID, DEPT_ID, SALARY). If two employees tie at the boundary, they must all be included (so a department can return more than 3 rows). Which query satisfies this requirement?

Table SALES has columns (SALE_ID, AMOUNT NUMBER, SALE_DATE DATE). A report must return all rows for calendar year 2025. A developer writes: WHERE TO_CHAR(sale_date,'YYYY') = '2025'. Which rewrite is best practice and avoids common performance pitfalls?

You must sort customer names in a way that: (1) ignores leading and trailing spaces, (2) sorts case-insensitively, and (3) treats NULL names as last. Which ORDER BY clause best meets all requirements in Oracle SQL?

A column COMMENTS contains free-form text. You must derive a new column CLEAN_COMMENTS by removing only trailing punctuation characters '.' '!' '?' and leaving internal punctuation intact. Which expression best satisfies this requirement?

You need to extract the portion between the first pair of parentheses from a string like 'ABC(123)DEF(999)'. If no parentheses exist, return NULL. Which expression correctly returns '123' for the example and NULL when missing?

A staging table STG has column AMT_TXT VARCHAR2 containing values like '1,234.50' (US format) and sometimes 'N/A'. You must produce a numeric AMT where invalid values become NULL without raising an error, using SQL only. Which approach is correct?

Table TXN has columns (ACCT_ID, TXN_DATE, AMOUNT). You need one row per ACCT_ID showing: total amount, count of transactions, and average amount, but the average must ignore NULL amounts while the count must include rows even when AMOUNT is NULL. Which SELECT list correctly meets the requirement?

You are troubleshooting a query intended to list all departments and their employees, including departments with no employees. The developer wrote: SELECT d.dept_id, d.dept_name, e.emp_id FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id WHERE e.status = 'ACTIVE'; It unexpectedly excludes departments with no employees. What change preserves departments with no employees while still returning only ACTIVE employees where present?