In this article
  1. Nulls and basic behavior
  2. Limiting rows and ranking
  3. Joins, conditions and queries
  4. Changing data and transactions
  5. Follow-up questions
  6. Sources

Nulls and basic behavior

What is the DUAL table in Oracle?

DUAL is a table with one row and one column that lets you run a SELECT for an expression that needs no real table. In Oracle Database 19c a query needs a FROM clause, so SELECT SYSDATE FROM DUAL returns the current date and time. DUAL exists so that functions, sequences and constants can be selected on their own. The SQL interview questions guide covers the platform-neutral basics of Structured Query Language (SQL).

Why it is asked: it is the first Oracle-only habit a newcomer meets. Read the full explanation: Oracle SQL Language Reference, selecting from the dual table.

How does Oracle treat an empty string?

In Oracle, an empty string stored in a VARCHAR2 column is treated as null. A null is a marker for a missing or unknown value, not a value, so a row inserted with '' is found by IS NULL and never by = ''. Most other databases keep the two apart. Length of an empty string is therefore null, not zero, and concatenating it with another string simply returns the other string.

Why it is asked: it is the best-known Oracle surprise, and it breaks code ported from other databases. Read the full explanation: Oracle SQL Language Reference, Nulls.

Why does WHERE column = NULL return no rows?

Comparing anything to null with = gives unknown, not true, and a WHERE clause keeps only rows where the condition is true. So WHERE bonus = NULL returns nothing, and so does <> NULL. Use IS NULL and IS NOT NULL instead. In a script modeling this rule, every comparison against a missing value came out unknown.1

Why it is asked: it shows whether you know three-valued logic, the source of many silent wrong counts. Read the full explanation: Oracle SQL Language Reference, Nulls.

What is the difference between NVL, NVL2 and COALESCE?

The undefined function returns its second argument when the first is null. The undefined function takes three: it returns the second if the first is not null and the third if it is. The undefined function accepts any number of arguments and returns the first that is not null; NVL and NVL2 are Oracle functions. Prefer COALESCE for portable code.

Why it is asked: null handling is daily work, and the answer shows how many options you know. Read the full explanation: Oracle SQL Language Reference, undefined.

Where do nulls sort when you use ORDER BY?

In Oracle, an ascending sort places nulls last and a descending sort places them first, unless you write NULLS FIRST or NULLS LAST after the column. Because the default flips with the direction, a report that sorts salary descending can show missing salaries at the top. State the null placement explicitly when order matters. The SQL order by guide covers ascending and descending sorts in general.

Why it is asked: it tests whether you check edge rows, not only the typical ones. Read the full explanation: Oracle SQL Language Reference, select statement.

Limiting rows and ranking

What is ROWNUM, and why does WHERE ROWNUM = 2 return nothing?

ROWNUM is a pseudocolumn, a value you can select but that is not stored in the table, and Oracle assigns it to each row as the row is fetched, starting from 1. A row is only given the value 2 after an earlier row has been given the value 1 and has passed the filter. With WHERE ROWNUM = 2 the first row fails the test, so the counter never advances and no row is ever returned.

Why it is asked: it separates people who memorized the syntax from people who know when it is evaluated. Read the full explanation: Oracle SQL Language Reference, row number pseudocolumn.

How do you get the top N rows after sorting?

ROWNUM is assigned to rows as they are fetched, before ORDER BY sorts them, so in one query level the limit is applied first and the sort second. Sort in a subquery, then number and filter the sorted rows in the outer query with ROWNUM <= :n. Current releases also support the row-limiting clause FETCH FIRST :n ROWS ONLY, which needs no subquery (Oracle SQL Language Reference, select statement).

SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= :n;

SELECT * FROM employees ORDER BY salary DESC FETCH FIRST :n ROWS ONLY;

In the subquery form, the inner query sorts and the outer query numbers the sorted rows with ROWNUM and filters them.

Sort in a subquery, then number in the outer queryTwo flows of a top-N query in Oracle: in one query level ROWNUM numbers and filters rows before the sort, which is wrong; the right form sorts in an inner query, then numbers and filters in the outer query.Fetch rowswrong: one levelNumberROWNUMFilterROWNUM <= :nSortORDER BY, toolateFetch rowsright: innerquerySortinner ORDER BYNumberouter ROWNUMFilterROWNUM <= :nDiagram: Interview Clozo, CC BY 4.0 ·interview.clozo.ai/blog/oracle-sql-interview-questions.html
Sort inside a subquery, then number and filter in the outer query, so the limit sees the sorted order.CC BY 4.0 — source: Oracle SQL interview questions on nulls, ROWNUM, ranking, DECODE and MERGE
Text description of the diagram
  • ROWNUM is assigned to rows as they are fetched, before ORDER BY sorts them.
  • In the subquery form, the inner query sorts and the outer query numbers the sorted rows with ROWNUM and filters them.

Why it is asked: top-N is the most common Oracle query task. Read the full explanation: Oracle SQL Language Reference, row number pseudocolumn.

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

A ranking function is an analytic function that numbers rows inside a window defined by OVER (ORDER BY ...). ROW_NUMBER, RANK and DENSE_RANK are the three common ones, and they differ on ties. ROW_NUMBER gives every row a unique number, breaking ties arbitrarily. RANK gives tied rows the same number and then skips ahead. DENSE_RANK gives tied rows the same number and skips nothing. On illustrative salaries 9000, 7000, 7000, 5000 and 4000, RANK gives 1, 2, 2, 4, 5 and DENSE_RANK gives 1, 2, 2, 3, 4.1

Why it is asked: tie handling decides whether a ranking query returns the right rows. Read the full explanation: window functions interview questions, visualised; the reference is Oracle SQL Language Reference, Analytic Functions.

How do you find the second-highest salary?

Use DENSE_RANK over salary descending and keep rank two, so tied top earners do not hide the second value. On the same illustrative salaries, the second-highest distinct value is 7000, held by two rows.1 ROW_NUMBER equal to two would return only one of the two tied rows, and RANK equal to three would return nothing, because rank three is skipped.

SELECT * FROM (
  SELECT e.*, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees e
) WHERE dr = 2;

Why it is asked: it is a classic that checks ties, subqueries and function choice together. Read the full explanation: window functions interview questions, visualised; the reference is Oracle SQL Language Reference, Analytic Functions.

Knowing it is half the job. Saying it is the other half.

Interview Clozo is an AI interview copilot for real interview rounds: it hears the question and puts a strong answer on your screen, and you say it in your own words. See the features or how it works.

Start Free Trial →

Joins, conditions and queries

What are the types of join, and what is the (+) operator?

An inner join keeps rows that match on both sides; an outer join also keeps unmatched rows from the left, the right or both sides, filling the gaps with nulls. The older Oracle form marks the optional side with (+) in the WHERE clause. It still works, but it cannot express a full outer join, a common follow-up, and Oracle documents the standard LEFT JOIN, RIGHT JOIN and FULL JOIN syntax as the preferred form (Oracle SQL Language Reference, Joins). See the SQL joins guide to inner and outer join types.

Why it is asked: you will read old Oracle code that uses (+). Read the full explanation: Oracle SQL Language Reference, Joins.

What is the difference between DECODE and CASE?

undefined is an Oracle function that compares one expression with a list of values and returns the matching result, treating two nulls as equal. undefined is the standard expression: it supports comparisons such as ranges and IS NULL in searched form, and it works in other databases. Choose CASE for new code and read DECODE in old code. The guide to case statements and the page on case statements with multiple conditions show the forms.

Why it is asked: it checks whether you can read legacy code and still write standard SQL. Read the full explanation: Oracle SQL Language Reference, undefined.

What is MINUS, and how does it relate to EXCEPT?

MINUS is Oracle's set operator that returns the distinct rows from the first query that do not appear in the second. Other databases call the same operation EXCEPT. Both queries must return the same number of columns with compatible types, and duplicates are removed. Use it to find, for example, customers who have no orders when a join with a null test would be clumsy.

Why it is asked: it tests vocabulary that differs between database products. Read the full explanation: Oracle SQL Language Reference, the undefined, undefined and undefined operators.

How do you write a hierarchical query with CONNECT BY?

A hierarchical query walks a parent-child table such as employees and managers. START WITH names the root rows, CONNECT BY PRIOR child = parent describes how a row links to the next level, and the LEVEL pseudocolumn reports the depth of each row. Add ORDER SIBLINGS BY to sort within each branch. The syntax is Oracle-specific in origin; other databases use recursive queries, which Oracle also supports through recursive WITH.

SELECT employee_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Why it is asked: org charts and bills of materials are common, and the syntax is Oracle-specific in origin. Read the full explanation: Oracle SQL Language Reference, Hierarchical Queries.

Changing data and transactions

What is the difference between DELETE, TRUNCATE and DROP?

DELETE is a data manipulation language (DML) statement that removes chosen rows, can use a WHERE clause and can be rolled back. TRUNCATE is a data definition language (DDL) statement that removes all rows quickly, commits implicitly and cannot be rolled back. DROP removes the table itself, structure included, though a dropped table can often be recovered from the recycle bin with FLASHBACK TABLE ... TO BEFORE DROP. Only TRUNCATE and DROP lack a WHERE clause.

Why it is asked: choosing the wrong one can destroy data that cannot be recovered by a rollback. Read the full explanation: Oracle SQL Language Reference, truncating a table.

What do COMMIT, ROLLBACK and SAVEPOINT do?

A transaction is a group of statements that succeed or fail together. COMMIT makes its changes permanent, ROLLBACK undoes everything since the last commit, and SAVEPOINT marks a point you can roll back to without losing earlier work. In Oracle, a DDL statement commits the open transaction automatically. The references are undefined, undefined and undefined.

Why it is asked: it checks that you can keep data consistent when something fails halfway. Read the full explanation: what is transaction control language (TCL) in SQL.

What does the MERGE statement do?

MERGE updates a target row when it matches a source row and inserts a new row when it does not, in one statement. You give the target, the source, a join condition in ON, then WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT. It suits loading changes into a table where some rows already exist, and avoids running an update, checking the count and then inserting.

Why it is asked: upserts are routine in data loads, and Oracle's syntax is compact. Read the full explanation: Oracle SQL Language Reference, merge statement.

What is the difference between a primary key and a unique key?

A primary key identifies each row: it must be unique, cannot be null, and a table has only one. A unique key also forbids duplicate values but allows several per table, and in Oracle a unique column accepts many rows with a null, because nulls are not considered equal to each other. Both are enforced with an index, and a foreign key can point to either.

Why it is asked: it tests whether constraints are understood as rules about nulls as well as duplicates. Read the full explanation: Oracle SQL Language Reference, Nulls.

Why use VARCHAR2 instead of VARCHAR or CHAR?

VARCHAR2 is Oracle's variable-length text type and the one Oracle recommends for character data. CHAR pads every value with spaces to a fixed length, which can make comparisons surprising. VARCHAR is currently a synonym for VARCHAR2, but Oracle reserves the right to give it different semantics in future, so Oracle recommends VARCHAR2 (Oracle SQL Language Reference, data types). Remember also that an empty VARCHAR2 value is stored as null.

Why it is asked: it checks whether you know Oracle's own data types, not only the standard ones. Read the full explanation: Oracle SQL Language Reference, data types.

Follow-up questions

What are the common Oracle SQL interview questions?

The eighteen above are the ones that recur: nulls and empty strings, ROWNUM and top-N, ranking functions, joins, DECODE against CASE, MERGE, TRUNCATE against DELETE, and transactions. For the general database questions that appear on any platform, start from the SQL interview questions guide.

What SQL interview questions are asked on Oracle?

Expect the topics on this page, and for experienced developers the same topics with more depth: why a ROWNUM filter returns the wrong rows, how ties change a ranking query, how you would rewrite a (+) join or a DECODE, and what happens to open work when a DDL statement runs. A strong answer explains the trade-off, not only the syntax. Reports of what a specific company asks vary, so treat any list as practice, not a prediction.

How do you practice Oracle SQL for an interview?

Run each query on an Oracle database you can reach and compare the output with your prediction before you run it, especially for nulls, ties and ROWNUM. Then explain the result aloud in two sentences. The SQL interview questions for testers give another set to rehearse.

Do Oracle interviews also ask general SQL?

Yes, in the sense that joins, grouping, filtering and set logic are the same core skills; the Oracle-specific questions sit on top of them. Revise the SQL joins guide first, then the Oracle differences on this page. If you are comparing platforms, the SQL Server interview questions show the other side.

Key takeaways
  • Oracle treats an empty VARCHAR2 string as null, so test with IS NULL, never = NULL.
  • ROWNUM is assigned before ORDER BY; sort in a subquery or use FETCH FIRST :n ROWS ONLY.
  • ROW_NUMBER, RANK and DENSE_RANK differ only on ties; use DENSE_RANK for the second-highest value.
  • TRUNCATE is DDL, commits implicitly and cannot be rolled back; DELETE can.
  • Prefer COALESCE, CASE and standard join syntax in new code, and know NVL, DECODE and (+) for old code.

Sources

  1. Oracle Database SQL Language Reference 19c: Nulls — Oracle, 26 Oct 2026
  2. Oracle Database SQL Language Reference 19c: ROWNUM Pseudocolumn — Oracle, 26 Oct 2026
  3. Oracle Database SQL Language Reference 19c: SELECT — Oracle, 26 Oct 2026
  4. Oracle Database SQL Language Reference 19c: TRUNCATE TABLE — Oracle, 26 Oct 2026
  5. Oracle Database SQL Language Reference 19c: Selecting from the DUAL Table — Oracle, 26 Oct 2026
  6. Oracle Database SQL Language Reference 19c: Analytic Functions — Oracle, 26 Oct 2026
  7. Oracle Database SQL Language Reference 19c: Hierarchical Queries — Oracle, 26 Oct 2026
  8. Oracle Database SQL Language Reference 19c: MERGE — Oracle, 26 Oct 2026
  9. Oracle Database SQL Language Reference 19c: Joins — Oracle, 26 Oct 2026
  10. Oracle Database SQL Language Reference 19c: Data Types — Oracle, 26 Oct 2026
  11. Oracle Database SQL Language Reference 19c: NVL — Oracle, 26 Oct 2026
  12. Oracle Database SQL Language Reference 19c: NVL2 — Oracle, 26 Oct 2026
  13. Oracle Database SQL Language Reference 19c: COALESCE — Oracle, 26 Oct 2026
  14. Oracle Database SQL Language Reference 19c: DECODE — Oracle, 26 Oct 2026
  15. Oracle Database SQL Language Reference 19c: CASE Expressions — Oracle, 26 Oct 2026
  16. Oracle Database SQL Language Reference 19c: The UNION [ALL], INTERSECT, MINUS Operators — Oracle, 26 Oct 2026
  17. Oracle Database SQL Language Reference 19c: COMMIT — Oracle, 26 Oct 2026
  18. Oracle Database SQL Language Reference 19c: ROLLBACK — Oracle, 26 Oct 2026
  19. Oracle Database SQL Language Reference 19c: SAVEPOINT — Oracle, 26 Oct 2026