Tricky Oracle SQL Queries for Interview: Top Questions and Answers

Oracle SQL interviews do not just test whether you know the syntax. They test whether you can think through a problem and construct the right query under pressure. Interviewers love tricky scenarios because they reveal how deeply you actually understand the database.

This guide walks you through 25 of the most commonly asked tricky Oracle SQL query questions, complete with a working SQL example after each answer. Study these carefully, run them in your own environment, and you will walk into your next interview ready for whatever they throw at you.

Finding and Removing Duplicate Rows

Duplicate data is one of the most frequent real-world problems, and interviewers almost always ask at least one question about it.

1. How do you find duplicate rows in a table?

You group by the columns that define what makes a row unique and use HAVING COUNT(*) > 1 to surface any group that has more than one row. This tells you exactly which combinations of values appear more than once.

SELECT
    employee_name,
    department_id,
    COUNT(*) AS duplicate_count
FROM employees
GROUP BY employee_name, department_id
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

2. How do you delete duplicate rows while keeping one copy?

You use ROW_NUMBER() partitioned by the columns that define duplicates. Every duplicate set gets numbered starting at 1. You then delete all rows where the row number is greater than 1, keeping exactly one row per group.

DELETE FROM employees
WHERE ROWID IN (
    SELECT ROWID
    FROM (
        SELECT
            ROWID,
            ROW_NUMBER() OVER (
                PARTITION BY employee_name, department_id
                ORDER BY ROWID
            ) AS rn
        FROM employees
    )
    WHERE rn > 1
);

Ranking and Top-N Queries

Ranking questions are a staple in Oracle interviews. You need to know how each approach behaves differently.

3. How do you find the second-highest salary in a table?

The cleanest method is to use DENSE_RANK() so that ties at the top do not accidentally skip the real second value. You wrap the analytic function in an inline view and filter on rank = 2 in the outer query.

SELECT salary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
WHERE rnk = 2;

4. How do you find the top 3 earners in each department?

You use ROW_NUMBER() or RANK() with PARTITION BY department_id. Partitioning restarts the numbering for each department, so filtering on the rank gives you the top N per group instead of globally.

SELECT
    employee_name,
    department_id,
    salary,
    rnk
FROM (
    SELECT
        employee_name,
        department_id,
        salary,
        RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
)
WHERE rnk <= 3
ORDER BY department_id, rnk;

5. How do you fetch the Nth row from a query result in Oracle?

You use ROW_NUMBER() to assign a sequence to each row and then filter for exactly the row number you want. This works reliably regardless of the Oracle version you are using.

SELECT *
FROM (
    SELECT
        e.*,
        ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
    FROM employees e
)
WHERE rn = 5;

Date and Time Tricky Queries

Date handling catches many candidates off guard. These are reliable patterns that come up often in Oracle interview scenarios.

6. How do you write a query to find all employees hired in the last 90 days?

You subtract 90 from SYSDATE and compare directly against the hire date column. This is simple but a very common pattern in production queries and interview scenarios alike.

SELECT
    employee_name,
    hire_date
FROM employees
WHERE hire_date >= SYSDATE - 90
ORDER BY hire_date DESC;

7. How do you find the first day and last day of the current month in Oracle?

TRUNC(SYSDATE, 'MM') gives you the first day of the current month by truncating the date to the month level. LAST_DAY(SYSDATE) gives you the last calendar day of the current month directly.

SELECT
    TRUNC(SYSDATE, 'MM')    AS first_day_of_month,
    LAST_DAY(SYSDATE)       AS last_day_of_month
FROM DUAL;

8. How do you calculate the number of working days between two dates, excluding weekends?

One approach is to generate all dates between the two values using a sequence or a connect-by trick, then count only those where the day of the week is not Saturday (7) or Sunday (1) based on Oracle's TO_CHAR day format.

SELECT
    COUNT(*) AS working_days
FROM (
    SELECT
        TO_DATE('2026-01-01', 'YYYY-MM-DD') + LEVEL - 1 AS dt
    FROM DUAL
    CONNECT BY LEVEL <= TO_DATE('2026-01-31', 'YYYY-MM-DD')
                          - TO_DATE('2026-01-01', 'YYYY-MM-DD') + 1
)
WHERE TO_CHAR(dt, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN')
    NOT IN ('SAT', 'SUN');

Hierarchical and Recursive Queries

Hierarchical queries are uniquely powerful in Oracle and almost always come up when interviewing for senior roles. Know how CONNECT BY works inside out.

9. How do you display an employee hierarchy starting from the CEO?

CONNECT BY PRIOR links each row to its parent. The START WITH clause identifies the root of the tree. LEVEL gives you the depth of each node so you can indent output for readability using LPAD.

SELECT
    LPAD(' ', (LEVEL - 1) * 4) || employee_name AS org_chart,
    employee_id,
    manager_id,
    LEVEL AS depth
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_name;

10. How do you find all direct and indirect reports under a specific manager?

You use CONNECT BY PRIOR but set the START WITH to the specific manager's ID instead of the top of the hierarchy. This subtree traversal is one of the most practical uses of the CONNECT BY clause.

SELECT
    employee_id,
    employee_name,
    manager_id,
    LEVEL AS level_in_hierarchy
FROM employees
START WITH manager_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER BY LEVEL, employee_name;

Aggregation and Grouping Tricks

Aggregation questions test whether you know when to use GROUP BY, HAVING, and analytic alternatives. These are common traps in Oracle interviews.

11. How do you find departments where the average salary is higher than the company average?

You group by department to get each department's average, then compare it against the overall company average using a subquery in the HAVING clause. This is a classic two-level aggregation pattern.

SELECT
    department_id,
    ROUND(AVG(salary), 2) AS dept_avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary) FROM employees
)
ORDER BY dept_avg_salary DESC;

12. How do you pivot rows into columns without using Oracle's PIVOT keyword?

You use conditional aggregation. Wrap each value category in a CASE expression inside a SUM or MAX and group by the remaining columns. This older technique still appears in interviews and works in all Oracle versions.

SELECT
    department_id,
    SUM(CASE WHEN job_title = 'Manager'  THEN salary ELSE 0 END) AS manager_salary,
    SUM(CASE WHEN job_title = 'Analyst'  THEN salary ELSE 0 END) AS analyst_salary,
    SUM(CASE WHEN job_title = 'Clerk'    THEN salary ELSE 0 END) AS clerk_salary
FROM employees
GROUP BY department_id
ORDER BY department_id;

13. How do you use the PIVOT operator in Oracle to rotate rows into columns?

Oracle's built-in PIVOT keyword makes conditional aggregation cleaner. You specify the aggregation function, the column whose values become new column headers, and the values to pivot on. The result is a crosstab-style output.

SELECT *
FROM (
    SELECT department_id, job_title, salary
    FROM employees
)
PIVOT (
    SUM(salary)
    FOR job_title IN (
        'Manager'  AS manager_salary,
        'Analyst'  AS analyst_salary,
        'Clerk'    AS clerk_salary
    )
)
ORDER BY department_id;

Analytic Window Function Challenges

Window functions are tested constantly in Oracle interviews. These tricky scenarios go beyond the basics and reflect real problem-solving on the job.

14. How do you calculate a running total using analytic functions?

SUM() with an OVER clause that includes ORDER BY computes a cumulative sum. The default window in Oracle when you specify ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which gives you exactly a running total.

SELECT
    order_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
ORDER BY order_date;

15. How do you calculate a 3-row moving average in Oracle?

You use SUM() or AVG() with a ROWS window frame that limits the calculation to the current row and the two preceding rows. Specifying ROWS instead of RANGE ensures the window is based on physical row position rather than value ranges.

SELECT
    order_id,
    order_date,
    order_amount,
    ROUND(
        AVG(order_amount) OVER (
            ORDER BY order_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2
    ) AS moving_avg_3
FROM orders
ORDER BY order_date;

16. How do you find the difference between a current row's value and the previous row's value?

LAG() retrieves the value from the previous row without a self-join. You subtract the LAG value from the current row's value to get the period-over-period difference. This is a very common pattern for financial and reporting queries.

SELECT
    order_date,
    order_amount,
    LAG(order_amount) OVER (ORDER BY order_date) AS prev_amount,
    order_amount - LAG(order_amount) OVER (ORDER BY order_date) AS change
FROM orders
ORDER BY order_date;

NULL Handling Tricky Queries

NULL behavior in Oracle surprises even experienced developers. Interviewers test this specifically because mistakes with NULLs cause silent, hard-to-debug data issues.

17. What happens when you use NOT IN with a subquery that contains NULL values?

This is one of the most dangerous Oracle SQL traps. If the subquery returns even one NULL, the entire NOT IN condition evaluates to NULL for every row, and your query returns zero rows. The fix is to always filter NULLs out of the subquery explicitly.

-- By explicitly filtering out NULLs in the subquery, this prevents the NOT IN trap from returning zero rows.

SELECT employee_name
FROM employees
WHERE department_id NOT IN (
    SELECT department_id FROM departments WHERE department_id IS NOT NULL
);

-- Safer alternative using NOT EXISTS
SELECT e.employee_name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
);

18. How do you sort NULL values to appear last in an ORDER BY clause?

Oracle sorts NULLs last for ASC order and first for DESC order by default. You can override this behavior using NULLS FIRST or NULLS LAST in the ORDER BY clause to explicitly control where NULLs appear.

SELECT
    employee_name,
    commission_pct
FROM employees
ORDER BY commission_pct DESC NULLS LAST;

Joins and Self-Join Scenarios

Join-based tricky questions test your ability to think about table relationships clearly under interview conditions.

19. How do you write a self-join to find each employee along with their manager's name?

A self-join joins the employees table to itself using different aliases. The LEFT JOIN ensures that even the top-level employee with no manager still appears in the result, with their manager name as NULL.

SELECT
    e.employee_id,
    e.employee_name,
    m.employee_name AS manager_name
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

20. How do you find employees who do not belong to any department?

You use a LEFT JOIN between employees and departments and then filter for rows where the department table returns no match, indicated by the department key being NULL. This is the standard outer join filtering pattern.

SELECT
    e.employee_id,
    e.employee_name,
    e.department_id
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_id IS NULL
ORDER BY e.employee_id;

21. How do you find departments that have no employees?

This is the mirror of the previous question. You do a LEFT JOIN from departments to employees and filter for rows where no matching employee is found. Use NOT EXISTS as an alternative when performance matters more on large tables.

SELECT
    d.department_id,
    d.department_name
FROM departments d
LEFT JOIN employees e
    ON d.department_id = e.department_id
WHERE e.employee_id IS NULL
ORDER BY d.department_id;

String and Pattern Matching Queries

String manipulation questions test your familiarity with Oracle's rich set of character functions and regular expression support.

22. How do you extract the domain name from a list of email addresses?

You use SUBSTR combined with INSTR to find the position of the @ character and then extract everything after it. This is a common real-world string parsing problem that interviewers use to test your knowledge of character functions.

SELECT
    email,
    SUBSTR(email, INSTR(email, '@') + 1) AS domain_name
FROM employees
WHERE email IS NOT NULL
ORDER BY domain_name;

23. How do you use REGEXP_REPLACE to remove all non-numeric characters from a string?

REGEXP_REPLACE with a character class pattern lets you replace entire categories of characters at once. The pattern [^0-9] matches any character that is not a digit and replaces it with an empty string, leaving only the numeric characters behind.

SELECT
    phone_number,
    REGEXP_REPLACE(phone_number, '[^0-9]', '') AS digits_only
FROM employees
WHERE phone_number IS NOT NULL;

MERGE and Upsert Patterns

MERGE questions are popular in senior interviews because they test a concept that beginners rarely use but experienced developers rely on heavily.

24. How do you write a MERGE statement to insert a row if it does not exist, or update it if it does?

The MERGE statement compares a source data set against a target table using an ON condition. When a match is found, the WHEN MATCHED branch updates the row. When no match exists, the WHEN NOT MATCHED branch inserts a new row. Both operations happen in a single atomic statement.

MERGE INTO employees tgt
USING (
    SELECT
        101        AS employee_id,
        'Jane Doe' AS employee_name,
        75000      AS salary,
        10         AS department_id
    FROM DUAL
) src
ON (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN
    UPDATE SET
        tgt.employee_name = src.employee_name,
        tgt.salary        = src.salary,
        tgt.department_id = src.department_id
WHEN NOT MATCHED THEN
    INSERT (employee_id, employee_name, salary, department_id)
    VALUES (src.employee_id, src.employee_name, src.salary, src.department_id);

CTE and Subquery Tricks

Common Table Expressions make complex queries readable. Interviewers check whether you know how to use them effectively versus when a simpler approach is better.

25. How do you use a CTE to find employees who earn more than their department average?

You define a CTE to calculate the average salary per department first, then join it back to the employees table in the main query to filter those who earn above their own department's average. This is much more readable than nesting the same logic as an inline subquery.

WITH dept_avg AS (
    SELECT
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    e.employee_id,
    e.employee_name,
    e.department_id,
    e.salary,
    ROUND(d.avg_salary, 2) AS dept_avg_salary
FROM employees e
JOIN dept_avg d
    ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary
ORDER BY e.department_id, e.salary DESC;

Quick Reference: Query Patterns and Best Practices

Tips for Answering Tricky SQL Questions in an Interview

When an interviewer asks you a tricky query question, slow down before you start typing or writing. Understand the input, the expected output, and any edge cases before committing to an approach.

Here are a few habits that will make you stand out:

• Always ask about NULL handling before you write the query since NULLs change the logic in almost every scenario
• Mention performance considerations when you have a choice of approaches, like why NOT EXISTS is often safer than NOT IN
• Use CTEs to break complex problems into readable steps rather than deeply nesting subqueries
• If you are using analytic functions, say out loud what the PARTITION BY and ORDER BY are doing so the interviewer knows you understand the window
• Test your mental model by tracing through a small example with two or three rows before committing to your answer

As one senior Oracle database architect put it, "A great SQL answer is not just correct. It shows that you thought about what could go wrong and why your approach handles it cleanly."

Conclusion

Tricky Oracle SQL questions are tricky for a reason. They expose gaps in understanding that only show up when the problem has a catch. NULLs that break NOT IN, ROWNUM that gets applied before ORDER BY, RANK gaps that hide the real second value, and invisible circular loops in CONNECT BY are all examples of real mistakes that even experienced developers make.

Going through each of these 25 questions with a live Oracle environment will help more than reading alone. Run every query, tweak the data, break things on purpose, and see what changes. That hands-on experience is what turns a memorized answer into real knowledge you can defend in any interview.

You now have a strong set of query patterns that cover duplicates, ranking, dates, hierarchies, NULLs, aggregation tricks, window functions, and MERGE. Practice them until writing each one feels natural, and you will be in excellent shape for your next Oracle SQL interview.