Top 50 Oracle SQL Interview Questions (2026 Edition)

Getting ready for an Oracle SQL interview can feel overwhelming, especially when the role covers everything from basic queries to advanced performance tuning. The good news is that most interviewers pull from the same core topics again and again. If you know these well, you will walk in confidently.

This guide covers the top 50 Oracle SQL interview questions you are likely to face in 2026. With the industry shifting towards Oracle Database 26ai, familiarity with both core fundamentals and modern features is a major advantage. Each answer is kept clear and to the point so you can study fast and retain what matters. Whether you are a fresher or an experienced developer brushing up, this list has you covered.

Basic Oracle SQL Concepts

Interviewers always start with the fundamentals. Make sure you can explain these without hesitation.

1. What is Oracle SQL, and how is it different from standard SQL?

Oracle SQL is the version of SQL used in Oracle Database. It follows the ANSI SQL standard but also includes Oracle-specific extensions like CONNECT BY for hierarchical queries, ROWNUM, advanced AI Vector Search capabilities (a major feature in the recent 'ai' database generations like 26ai), and many built-in functions not found in other databases. These additions give you more power when working with Oracle environments.

2. What is the difference between DELETE, TRUNCATE, and DROP?

3. What is ROWNUM in Oracle?

ROWNUM is a pseudocolumn that assigns a sequential number to each row returned by a query, starting at 1. It is commonly used to limit the number of rows returned. However, you need to be careful because ROWNUM is assigned before ORDER BY is applied when used directly in a WHERE clause.

4. What is the difference between ROWNUM and ROW_NUMBER()?

ROWNUM is a pseudocolumn assigned during row retrieval, before sorting. ROW_NUMBER() is an analytic function that assigns a rank to each row after the result set is sorted. ROW_NUMBER() gives you much more control, especially when combined with OVER(ORDER BY ...).

5. What is a NULL value in Oracle SQL?

NULL represents the absence of a value. It is not zero and not an empty string. Any comparison with NULL using = or != returns NULL, not TRUE or FALSE. You must always use IS NULL or IS NOT NULL to check for null values correctly.

Joins and Set Operations

Joins are one of the most tested areas in any SQL interview. You should know each type cold.

6. What are the different types of joins in Oracle SQL?

• INNER JOIN: Returns only rows where there is a match in both tables
• LEFT OUTER JOIN: Returns all rows from the left table and matched rows from the right
• RIGHT OUTER JOIN: Returns all rows from the right table and matched rows from the left
• FULL OUTER JOIN: Returns all rows from both tables, with NULLs where there is no match
• CROSS JOIN: Returns the Cartesian product of both tables
• SELF JOIN: Joins a table to itself using an alias

7. What is the Oracle-specific outer join syntax using (+)?

Before ANSI join syntax was adopted, Oracle used the (+) operator to indicate the outer side of a join. For example, WHERE a.id = b.id(+) is equivalent to a LEFT OUTER JOIN. You may still see this in legacy code, but ANSI syntax is preferred for all new work.

8. What is the difference between UNION and UNION ALL?

UNION combines the result sets of two queries and removes duplicate rows. UNION ALL combines them without removing duplicates, which makes it faster. Use UNION ALL when you know there are no duplicates or when duplicates are acceptable.

9. What is the difference between INTERSECT and MINUS?

INTERSECT returns only the rows that appear in both result sets. MINUS returns the rows from the first result set that do not appear in the second. Both operators remove duplicates from the final output.

10. What is a Cartesian join, and when does it happen?

A Cartesian join occurs when two tables are joined without any join condition. Every row in the first table is combined with every row in the second table. If table A has 100 rows and table B has 200 rows, the result has 20,000 rows. This is usually a mistake, but CROSS JOIN intentionally produces this result.

Oracle Analytic and Aggregate Functions

Analytic functions are almost always tested in Oracle-specific interviews. They are powerful tools worth mastering.

11. What is the difference between aggregate and analytic functions?

Aggregate functions like SUM, COUNT, and AVG collapse multiple rows into a single result. Analytic functions perform calculations across a window of rows without collapsing them. The OVER() clause is what makes a function analytic. You get one output row for every input row.

12. What are ROW_NUMBER, RANK, and DENSE_RANK, and how do they differ?

13. What is the LAG and LEAD function used for?

LAG lets you access data from a previous row in the same result set without using a self-join. LEAD does the same for the next row. They are extremely useful for comparing current and previous period values, like month-over-month sales comparisons.

14. What does the PARTITION BY clause do in analytic functions?

PARTITION BY divides the result set into groups, similar to GROUP BY, but without collapsing rows. The analytic function is applied independently within each partition. For example, ranking employees by salary within each department uses PARTITION BY department_id.

15. How does LISTAGG work in Oracle?

LISTAGG is an aggregate function that concatenates values from multiple rows into a single delimited string. It is perfect for turning a column of values into a comma-separated list. Starting with Oracle 19c, you can also use the ON OVERFLOW TRUNCATE option to handle cases where the output exceeds 4,000 characters.

Subqueries and CTEs

Knowing how to structure complex queries using subqueries and CTEs is a skill every serious Oracle developer needs.

16. What is a subquery in Oracle SQL?

A subquery is a query nested inside another query. It can appear in the SELECT, FROM, or WHERE clause. Subqueries in the WHERE clause are used to filter rows based on values returned by a separate query. They can be correlated or non-correlated.

17. What is a correlated subquery?

A correlated subquery references a column from the outer query. It is evaluated once for every row processed by the outer query. This can make it slower than a regular subquery, but it is sometimes the only way to express certain logic. An example is finding every employee who earns more than the average salary in their own department.

18. What is a Common Table Expression (CTE) or WITH clause?

A CTE defines a named temporary result set using the WITH keyword before the main SELECT. You can reference the CTE like a table within the same query. CTEs make complex queries more readable and can be defined once and referenced multiple times within the same statement.

19. What is a recursive CTE, and when would you use one?

A recursive CTE references itself to process hierarchical or tree-structured data. In Oracle, you can also use the CONNECT BY clause for this purpose, which is the traditional Oracle-specific approach. Recursive CTEs are useful for organizational charts, bill-of-materials structures, or any parent-child hierarchy.

20. What is an inline view in Oracle?

An inline view is a subquery placed in the FROM clause of a SELECT statement. Oracle treats it like a virtual table. Inline views are necessary when you want to apply window functions and then filter on their results, since you cannot use analytic functions directly in a WHERE clause.

Indexes and Performance

Performance questions are very common in mid-level and senior Oracle interviews. Interviewers want to know if you understand what happens under the hood.

21. What is an index in Oracle, and why do you use one?

An index is a database object that speeds up data retrieval by creating a sorted structure on one or more columns. Instead of scanning every row in a table (full table scan), Oracle uses the index to jump directly to the relevant rows. The trade-off is additional storage and slower write operations.

22. What are the common types of indexes in Oracle?

• B-Tree Index: The default index type, ideal for high-cardinality columns
• Bitmap Index: Efficient for low-cardinality columns like status or gender, commonly used in data warehouses
• Function-Based Index: Built on the result of a function or expression
• Composite Index: Built on two or more columns
• Unique Index: Enforces uniqueness on a column or combination of columns
• Reverse Key Index: Reverses the bytes of the indexed value to avoid index hot spots in sequence-generated keys

23. What is a full table scan, and when is it acceptable?

A full table scan reads every block in the table to find matching rows. It is often a sign that no appropriate index exists. However, it can actually be faster than an index scan when a query returns a large percentage of rows, because Oracle can read blocks in bulk rather than jumping around the disk.

24. What is the EXPLAIN PLAN statement used for?

EXPLAIN PLAN shows you the execution plan Oracle intends to use for a query without actually running it. You can see whether Oracle is using an index, performing a full table scan, or doing a hash join. After running EXPLAIN PLAN FOR your_query, you view the results by querying PLAN_TABLE or using DBMS_XPLAN.DISPLAY.

25. What does the HINT syntax do in Oracle SQL?

Hints are special comments you embed in a SQL statement to instruct the Oracle optimizer to use a specific execution strategy. Common hints include /*+ INDEX(table index_name) */ to force an index, or /*+ FULL(table) */ to force a full table scan. Hints override the optimizer's own decisions and should be used sparingly.

Constraints and Data Integrity

Data integrity questions test whether you understand how Oracle enforces rules at the database level, not just in application code.

26. What are the types of constraints in Oracle?

• PRIMARY KEY: Uniquely identifies each row and does not allow NULLs
• UNIQUE: Ensures all values in a column are distinct, but allows one NULL
• NOT NULL: Prevents NULL values in a column
• FOREIGN KEY: Enforces a referential relationship between two tables
• CHECK: Validates that column values satisfy a logical condition

27. What is a deferred constraint in Oracle?

A deferred constraint is not checked until the end of a transaction rather than at the end of each individual statement. You define it with INITIALLY DEFERRED or enable deferral with INITIALLY IMMEDIATE DEFERRABLE. This is useful when you need to insert rows in a specific order that temporarily violates a constraint during the transaction.

28. What happens when a foreign key constraint violation occurs?

Oracle raises an ORA-02292 error: integrity constraint violated, child record found. This happens when you try to delete or update a parent row that still has matching child rows. You can avoid this by deleting the child rows first, or by defining the foreign key with ON DELETE CASCADE.

Transactions and Locking

Transaction control is a topic that separates developers from database engineers. Make sure you understand how Oracle handles concurrency.

29. What is the difference between COMMIT and ROLLBACK?

COMMIT permanently saves all changes made during the current transaction. ROLLBACK undoes all changes back to the last COMMIT or SAVEPOINT. In Oracle, unlike some other databases, DDL statements like CREATE TABLE automatically trigger an implicit COMMIT.

30. What is a SAVEPOINT in Oracle?

A SAVEPOINT marks a point within a transaction that you can roll back to without undoing the entire transaction. This is useful in long transactions where you want to undo only part of the work. ROLLBACK TO savepoint_name takes you back to that specific marker while keeping earlier changes intact.

31. How does Oracle handle read consistency?

Oracle uses multi-version read consistency, which means readers never block writers and writers never block readers. When you start a query, Oracle takes a consistent snapshot of data as of that moment. If another session modifies data after your query began, you see the original version from the undo tablespace. This is one of Oracle's most important architectural advantages.

32. What is a deadlock in Oracle, and how does it get resolved?

A deadlock occurs when two sessions each hold a lock the other needs, and both are waiting indefinitely. Oracle automatically detects deadlocks and resolves them by rolling back one of the statements involved, then raising an ORA-00060 error. The application must handle this error and retry the operation if needed.

PL/SQL and Stored Objects

Many Oracle SQL interviews also touch on PL/SQL, since the two work hand in hand in production environments.

33. What is the difference between a function and a procedure in Oracle?

A function must return a single value and can be used inside a SQL expression. A procedure does not return a value directly through its name but can return values through OUT parameters. Both are stored in the database and compiled for repeated use.

34. What is a trigger in Oracle?

A trigger is a stored PL/SQL block that automatically executes in response to a specific event on a table, view, or database. You can define triggers to fire BEFORE or AFTER an INSERT, UPDATE, or DELETE. Row-level triggers fire once per affected row, while statement-level triggers fire once per DML statement.

35. What is a sequence in Oracle, and how is it different from IDENTITY columns?

A sequence is a database object that generates a unique series of numbers, commonly used for primary keys. You call it with sequence_name.NEXTVAL. Starting with Oracle 12c, IDENTITY columns automate this process directly on the column definition, similar to AUTO_INCREMENT in MySQL. Both achieve the same result, but IDENTITY columns require less code.

36. What is a synonym in Oracle?

A synonym is an alias for a database object like a table, view, sequence, or procedure. Private synonyms are accessible only to the owner, while public synonyms are visible to all users. Synonyms simplify SQL by hiding schema names and make it easier to migrate objects between environments without changing application code.

Advanced SQL Techniques

Senior-level interviews often go deep into advanced Oracle SQL features. These are the questions that separate candidates who truly know the database.

37. What is the MERGE statement in Oracle?

MERGE, also called an upsert, lets you insert or update rows in a target table based on data from a source table in a single statement. If a matching row exists in the target, it is updated. If no match exists, a new row is inserted. This is far more efficient than running separate INSERT and UPDATE statements.

38. What is a materialized view?

A materialized view stores the result of a query physically on disk, unlike a regular view that re-executes its query every time. You can configure materialized views to refresh on a schedule or on commit. They are widely used in data warehousing and reporting to dramatically speed up complex aggregation queries.

39. What is partitioning in Oracle, and what are the main types?

Partitioning divides a large table into smaller, more manageable pieces called partitions. Oracle supports several partition types:

• Range Partitioning: Divides data based on a range of values, commonly used for dates
• List Partitioning: Divides data based on a specific list of discrete values
• Hash Partitioning: Distributes data evenly across partitions using a hash function
• Composite Partitioning: Combines two partitioning strategies, such as range-hash or range-list
• Interval Partitioning: An extension of range partitioning where Oracle creates new partitions automatically as data arrives

40. What is partition pruning?

Partition pruning is the optimizer's ability to skip partitions that cannot possibly contain matching rows. It dramatically reduces I/O for queries that include the partition key in the WHERE clause. For a table partitioned by month, a query like WHERE order_date BETWEEN '01-JAN-2025' AND '31-JAN-2025' only reads the January partition instead of the entire table.

41. What are global and local indexes on partitioned tables?

A local index is partitioned in the same way as its underlying table, creating one index partition per table partition. A global index spans all partitions of the table. Local indexes generally perform better for partition-pruning queries, while global indexes are better for queries that need to search across partition boundaries.

42. What is the CONNECT BY clause used for?

CONNECT BY is Oracle's way of writing hierarchical queries. You define a parent-child relationship using CONNECT BY PRIOR, and the query traverses the tree starting from the row or rows defined by the START WITH clause. It is used for organizational hierarchies, menu structures, part explosions, and similar tree-shaped data.

43. How do you find the top N rows in Oracle without using FETCH FIRST?

The classic Oracle technique is to wrap the query in an inline view sorted by your criteria, then filter with WHERE ROWNUM <= N in the outer query. Applying ROWNUM filtering on an already-sorted result set ensures you get the actual top rows. Here is the general pattern:

-- Inner query:
SELECT columns FROM table ORDER BY sort_column DESC
-- Outer query:
SELECT * FROM (inner_query) WHERE ROWNUM <= 10

(Note: Starting with Oracle 12c, you can simply use FETCH FIRST 10 ROWS ONLY, which is much cleaner).

44. What is the difference between WHERE and HAVING?

WHERE filters rows before any grouping or aggregation takes place. HAVING filters groups after GROUP BY aggregation is applied. You cannot use aggregate functions like SUM or COUNT in a WHERE clause, but you can use them in HAVING. Always use WHERE when possible since it filters earlier and reduces the number of rows processed.

45. What is the NVL and NVL2 function?

NVL(expr1, expr2) returns expr2 if expr1 is NULL, otherwise returns expr1. NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL and expr3 if expr1 is NULL. COALESCE is the ANSI-standard equivalent of NVL and accepts more than two arguments, returning the first non-null value in the list.

Oracle SQL Date and String Functions

Date and string manipulation questions come up frequently in both junior and senior-level interviews.

46. What is the difference between SYSDATE and SYSTIMESTAMP?

SYSDATE returns the current date and time from the database server with second-level precision. SYSTIMESTAMP returns the same but with fractional seconds and time zone information. Use SYSTIMESTAMP when you need sub-second precision or when auditing across different time zones.

47. How do you convert between dates and strings in Oracle?

Use TO_DATE to convert a string to a date, and TO_CHAR to convert a date to a string. Both functions take a format mask as the second argument, like TO_DATE('2026-04-18', 'YYYY-MM-DD'). Relying on implicit conversion is a bad practice because it depends on the session's NLS_DATE_FORMAT setting, which can vary by environment.

48. What is the SUBSTR and INSTR function used for in Oracle?

SUBSTR extracts a portion of a string. SUBSTR('Oracle Database', 1, 6) returns 'Oracle'. INSTR finds the position of a substring within a string. INSTR('Oracle Database', 'Data') returns 8. You often combine both to extract a dynamically located substring.

49. What is REGEXP_LIKE and when would you use it?

REGEXP_LIKE is a condition that tests whether a string matches a regular expression pattern. It is useful for validating formats like email addresses, phone numbers, or zip codes directly in SQL. For example, you can find all rows where a column contains only numeric digits using REGEXP_LIKE(column_name, '^[0-9]+$').

Common Interview Scenario Questions

Interviewers often follow up with scenario-based questions that test how well you apply what you know.

50. How would you find duplicate rows in a table?

The most common approach is to group by the columns that define uniqueness and use HAVING COUNT(*) > 1 to find groups with more than one row. If you need to identify and delete specific duplicate rows while keeping one, you combine this with ROW_NUMBER() partitioned by the duplicate key columns and delete all rows where ROW_NUMBER() > 1.

As one Oracle performance tutor puts it, "The interview is not just testing whether you know the answer. It is testing whether you understand why the answer works."

Quick Reference: Key Oracle SQL Topics by Difficulty

How to Prepare for Your Oracle SQL Interview

Reading these questions alone is not enough. You need to actually write and run the queries yourself. Set up a free Oracle Database environment using Oracle Live SQL at livesql.oracle.com, where you can run queries directly in a browser with no installation needed.

Practice writing each type of query from memory, especially analytic functions and hierarchical queries since those trip up the most candidates. Time yourself answering questions out loud, because interviewers notice when you hesitate on basics.

Here are a few high-value study tips:

1. Know the difference between RANK, DENSE_RANK, and ROW_NUMBER by heart since this is one of the most asked analytic questions.
2. Understand EXPLAIN PLAN at a conceptual level so you can speak to query performance confidently.
3. Practice writing MERGE statements since they appear in senior-level interviews more often than you might expect.
4. Be ready to explain multi-version read consistency since it is a frequent differentiator question for Oracle-specific roles.

Conclusion

Oracle SQL is a deep skill, and interviewers in 2026—especially as companies adopt Oracle Database 26ai—are looking for candidates who go beyond memorized answers. They want to see that you understand the why behind each feature, not just the what. Working through all 50 of these questions prepares you for both the straightforward questions and the follow-ups that come from a genuinely curious interviewer.

Use this guide as a starting point, not an ending point. Run the queries, read the Oracle documentation for the features that interest you most, and build real examples in Oracle Live SQL. The more hands-on time you spend with the database, the more naturally your answers will flow in the interview room.

You are more ready than you think. All it takes is focused, deliberate preparation and the confidence to explain what you know clearly and honestly.

See also: Oracle APEX Interview Questions