Oracle SQL Query-Based Interview Questions (0–5 Years)

By /

Query-based questions are the most important part of Oracle SQL technical rounds.
Interviewers use these questions to test:

  • Logical thinking
  • SQL fundamentals
  • Real-world problem-solving

Below are commonly asked SQL queries with explanations.

1. Find Second Highest Salary

This is one of the most frequently asked interview questions.

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

This approach works without analytic functions and is easy to explain.

2. Find Nth Highest Salary

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

Using DENSE_RANK ensures duplicate salaries are handled correctly.

3. Find Employees Without Department

SELECT *
FROM employees
WHERE dept_id IS NULL;

Very common in HR and banking applications.

4. Find Duplicate Records

SELECT emp_id, COUNT(*)
FROM employees
GROUP BY emp_id
HAVING COUNT(*) > 1;

Used during data validation and cleanup tasks.

5. Delete Duplicate Records

DELETE FROM employees
WHERE ROWID NOT IN (
  SELECT MIN(ROWID)
  FROM employees
  GROUP BY emp_id
);

ROWID is Oracle-specific and often discussed in interviews.

6. Count Employees Department-Wise

SELECT dept_id, COUNT(*)
FROM employees
GROUP BY dept_id;

Common reporting requirement.

7. Departments Without Employees

SELECT d.dept_id
FROM departments d
LEFT JOIN employees e
ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

Tests JOIN understanding.

8. Top 3 Salaries Per Department

Uses analytic functions:

SELECT *
FROM (
  SELECT emp_name, dept_id, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn
  FROM employees
)
WHERE rn <= 3;

9. Employees Earning Above Average Salary

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

10. Employees Joined in Last 30 Days

SELECT *
FROM employees
WHERE hire_date >= SYSDATE - 30;

Why These Queries Matter

These queries simulate real project scenarios:

  • Reports
  • Data validation
  • Banking transactions
  • HR analytics

Mastering them significantly improves technical round performance.

Final Interview Tip

“Always explain your logic before writing the query.”

Interviewers value clarity of thinking more than syntax.

Scroll to Top