Oracle SQL Interview Questions with Answers and Examples

By AI Leaf Global Learn · 7 October 2026

Practise 15 Oracle SQL interview questions with clear answers, sample data, expected results and examples covering joins, NULLs, grouping, ranking and date ranges.

What should you practise for an Oracle SQL interview?

Practise filtering, joins, grouping, NULLs, ranking and date ranges. Below are 15 questions with original examples for Oracle Database 19c. They are learning exercises, not a claim about any employer's interview questions. Use an Oracle practice database; a generic SQL compiler may use another dialect.

Reusable sample data

Paste this WITH clause before an example's SELECT. Replace its final SELECT with the query you want to practise. Where an example starts with a comma and ranked AS, append that CTE after departments before the final SELECT. No real tables are changed.

WITH employees (employee_id, employee_name, department_id, salary, hire_date) AS (
 SELECT 1, 'Anu', 10, 60000, DATE '2025-01-10' FROM dual UNION ALL
 SELECT 2, 'Bala', 10, 50000, DATE '2025-02-15' FROM dual UNION ALL
 SELECT 3, 'Chitra', 20, 50000, DATE '2025-02-20' FROM dual UNION ALL
 SELECT 4, 'Dev', CAST(NULL AS NUMBER), 40000, DATE '2025-03-05' FROM dual UNION ALL
 SELECT 5, 'Esha', 20, CAST(NULL AS NUMBER), DATE '2025-03-10' FROM dual
), departments (department_id, department_name) AS (
 SELECT 10, 'Engineering' FROM dual UNION ALL
 SELECT 20, 'Analytics' FROM dual UNION ALL
 SELECT 30, 'Support' FROM dual
)
SELECT * FROM employees ORDER BY employee_id;

1. How do you filter and sort rows?

WHERE selects matching rows. ORDER BY specifies result order; use a unique tie-breaker for predictable ordering.

SELECT employee_name, salary FROM employees
WHERE salary >= 50000
ORDER BY salary DESC, employee_id;

Result: Anu 60000, Bala 50000, Chitra 50000. Without ORDER BY, row order is not guaranteed.

2. How do you find a missing salary?

Use IS NULL, not = NULL. Missing data is not zero. Ordinary comparisons with NULL evaluate to UNKNOWN and do not pass a WHERE filter.

SELECT employee_name FROM employees WHERE salary IS NULL;

Result: Esha. Oracle 19c treats an empty character string as NULL, but NULL and numeric zero are different.
Oracle NULL handling.

3. How do COUNT(*) and COUNT(column) differ?

COUNT(*) counts all rows. COUNT(salary) counts non-NULL salaries. DISTINCT counts each known salary once.

SELECT COUNT(*) AS total_rows, COUNT(salary) AS known_salaries,
 COUNT(DISTINCT salary) AS distinct_salaries FROM employees;

Result: 5, 4, 3. The two 50000 values are two rows but one distinct salary.
Oracle COUNT.

4. What does NVL do?

NVL returns its second argument when its first is NULL. Choose compatible types and distinguish display replacement from stored data.

SELECT employee_name, NVL(salary, 0) AS displayed_salary
FROM employees ORDER BY employee_id;

Esha displays 0; her sample salary remains NULL. Treating an unknown salary as zero in an average changes the business meaning.
Oracle NVL.

5. How do you label rows with CASE?

CASE returns a value based on conditions. Give missing values their own label if needed.

SELECT employee_name,
 CASE WHEN salary IS NULL THEN 'Unknown'
 WHEN salary >= 55000 THEN 'Higher band'
 ELSE 'Standard band' END AS salary_band
FROM employees ORDER BY employee_id;

Anu is Higher band, Esha is Unknown; the others are Standard band. These are illustrative labels.

6. How do INNER JOIN and LEFT JOIN differ?

INNER JOIN keeps matching pairs. LEFT JOIN also keeps unmatched left-side rows with NULLs for right-side columns.

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

All five employees appear. Dev has no department name. INNER JOIN would return four employees.

7. Why can WHERE change a LEFT JOIN result?

A WHERE filter that requires a right-side value removes unmatched rows. Put a matching condition in ON to retain every left-side row.

SELECT e.employee_name, d.department_name
FROM employees e LEFT JOIN departments d
 ON d.department_id = e.department_id
 AND d.department_name = 'Engineering'
ORDER BY e.employee_id;

All five employees remain; only Anu and Bala have a department name. Moving the name condition to WHERE returns only Anu and Bala.

8. How do you count employees in empty departments?

Start from departments and count a non-NULL employee key. COUNT(*) would count the placeholder row of an empty department.

SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS total
FROM departments d LEFT JOIN employees e
 ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id;

Result: Engineering 2, Analytics 2, Support 0. Unassigned Dev is outside these department counts.

9. When do you use WHERE and HAVING?

WHERE filters rows before aggregation; HAVING filters groups afterwards.

SELECT department_id, COUNT(*) AS total FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id HAVING COUNT(*) >= 2
ORDER BY department_id;

Departments 10 and 20 each return 2. COUNT(*) >= 2 cannot be a WHERE condition here.

10. How do you report an average with missing salaries?

AVG ignores NULLs. Include the number of known values to make the result clear.

SELECT department_id, COUNT(*) AS total, COUNT(salary) AS known,
 AVG(salary) AS average_known_salary
FROM employees WHERE department_id IS NOT NULL
GROUP BY department_id ORDER BY department_id;

Department 10: 2 total, 2 known, average 55000. Department 20: 2 total, 1 known, average 50000. AVG(NVL(salary, 0)) instead gives 25000 for department 20.

11. How do you find the second-highest distinct salary?

DENSE_RANK assigns equal ranks to ties without skipping ranks. Exclude missing salaries. Append this CTE to the sample WITH clause.

, ranked AS (
 SELECT employee_name, salary,
 DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
 FROM employees WHERE salary IS NOT NULL
)
SELECT employee_name, salary FROM ranked
WHERE salary_rank = 2 ORDER BY employee_name;

Result: Bala and Chitra, both 50000. This means second distinct salary, not second row. Fewer than two distinct salaries gives no rows.
Oracle DENSE_RANK.

12. How do you pick one highest-paid employee per department?

ROW_NUMBER with PARTITION BY picks one row per group. A unique tie-breaker makes the choice deterministic. Append this CTE to the sample WITH clause.

, ranked AS (
 SELECT employee_name, department_id, salary,
 ROW_NUMBER() OVER (PARTITION BY department_id
 ORDER BY salary DESC, employee_id) AS rn
 FROM employees
 WHERE department_id IS NOT NULL AND salary IS NOT NULL
)
SELECT department_id, employee_name, salary FROM ranked
WHERE rn = 1 ORDER BY department_id;

Result: department 10 Anu 60000; department 20 Chitra 50000. For all tied highest salaries, use DENSE_RANK = 1 with only salary in the ranking order.
Oracle ROW_NUMBER.

13. How do you find departments with no employees?

NOT EXISTS checks for no matching row and avoids a NULL-containing NOT IN list.

SELECT d.department_name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e
 WHERE e.department_id = d.department_id);

Result: Support. department_id NOT IN (SELECT department_id FROM employees) returns no departments in this sample because that list includes NULL.

14. How do you include every hire in a month?

Use a half-open range: include the month's start and exclude the next month's start. Oracle DATE includes a time component.

SELECT employee_name, hire_date FROM employees
WHERE hire_date >= DATE '2025-02-01'
 AND hire_date < DATE '2025-03-01'
ORDER BY hire_date, employee_id;

Result: Bala and Chitra. This also includes a February 28 hire at 23:59:59. BETWEEN with February 28 midnight as its end excludes most times that day. For timestamp columns, match boundary types and define the relevant time zone.

15. How do you return the top three rows in Oracle 19c?

Use ORDER BY and FETCH FIRST. Decide whether the requirement means three rows or all ties at the salary cutoff.

SELECT employee_name, salary FROM employees WHERE salary IS NOT NULL
ORDER BY salary DESC, employee_id FETCH FIRST 3 ROWS ONLY;

Result order: Anu, Bala, Chitra. This returns at most three rows. For cutoff ties, use ORDER BY salary DESC FETCH FIRST 3 ROWS WITH TIES. Including a unique employee ID in the ordering eliminates ties in the combined ordering criteria.

How should you practise?

Write a query before reading the answer.
Predict the result and explain each clause aloud.
Add a duplicate salary, missing value and empty department, then recheck.
Distinguish one row, all tied rows and one distinct salary level.
Practise with sample data rather than production records.

Where can Chennai and Tambaram learners study Oracle SQL?

AI Leaf Global Learn offers classroom learning in Mudichur, in the Tambaram area of Chennai, and online learning across India. Confirm current batches, mode, syllabus and fees before enrolling. Tamil Nadu tags describe this guide's audience and do not imply additional branches.
View the Oracle SQL and PL/SQL course.
Contact the learning team.

Author and official references

Prepared by AI Leaf Global Learn. Examples target Oracle Database 19c and have been reviewed against documentation; they are not represented as executed on a live Oracle instance. Versions and business rules can affect the final solution.
Oracle SELECT: joins, grouping, sorting and row limiting.
Oracle NULL handling.
Oracle COUNT.
Oracle NVL.
Oracle DENSE_RANK.
Oracle ROW_NUMBER.

Oracle joins.
Oracle AVG.
Oracle CASE expressions.
Oracle EXISTS.

Tamil Nadu Chennai Tambaram