Oracle SQL & PL/SQL Interview Questions (0–5 Years Experience)

By /

With Real-Time Examples Asked in Top IT Companies & Banks

If you are preparing for an Oracle SQL / PL-SQL Developer interview and have 0–5 years of experience, this guide is for you.

These interview questions are frequently asked in technical rounds.

Who Should Read This?

  • Freshers preparing for Oracle SQL interviews
  • 1–5 years experienced Oracle SQL / PL-SQL Developers
  • Candidates appearing for service-based & product-based company interviews
  • Banking & financial domain aspirants

1. Difference Between SQL and PL/SQL

Interview Question:
What is the difference between SQL and PL/SQL?

Explanation:

  • SQL (Structured Query Language) is used to retrieve, insert, update, and delete data.
  • PL/SQL (Procedural Language SQL) is Oracle’s extension of SQL that allows procedural programming.

Key Differences:

SQLPL/SQL
Executes one statement at a timeExecutes a block of statements
No loops or conditionsSupports IF, LOOP, CASE
Used for data operationsUsed for business logic

Real-Time Example:

SELECT * FROM employees;

IF salary > 50000 THEN
   bonus := 1000;
END IF;

👉 Interview Tip:
Say this line confidently:

“SQL works with data, PL/SQL works with logic.”

2. INNER JOIN vs LEFT JOIN

Explanation:

  • INNER JOIN returns only matching rows from both tables.
  • LEFT JOIN returns all rows from the left table, even if there is no match.

Real-Time Example:

In HR applications, employees may exist without department mapping.

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id;

👉 Used in: Reporting, dashboards, banking MIS reports.

3. UNION vs UNION ALL

Explanation:

  • UNION removes duplicate records.
  • UNION ALL keeps duplicates and performs faster.

Real-Time Example:

Combining monthly sales data from two systems.

👉 Performance Tip:
Always use UNION ALL unless duplicate removal is mandatory.

4. DELETE vs TRUNCATE

Explanation:

DELETETRUNCATE
Row-by-row deletionRemoves all rows
Can rollbackCannot rollback
SlowerFaster

Real-Time Example:

  • DELETE → Remove last month’s records
  • TRUNCATE → Clean staging table before ETL load

5. WHERE vs HAVING

Explanation:

  • WHERE filters rows before aggregation.
  • HAVING filters results after aggregation.

Real-Time Example:

SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 50000;

6. Subquery vs Correlated Subquery

Explanation:

  • Subquery runs once.
  • Correlated subquery runs for each row of the outer query.

Real-Time Example:

Employees earning more than department average (very common bank interview question).

7. IN vs EXISTS

Explanation:

  • IN compares values.
  • EXISTS checks existence of rows.

Real-Time Insight:

For large datasets, EXISTS performs better and is preferred in enterprise systems.

8. ROWNUM vs ROW_NUMBER()

Explanation:

  • ROWNUM is Oracle-specific and assigned before ORDER BY.
  • ROW_NUMBER() is an analytic function.

Real-Time Example:

Top 3 salaries per department → ROW_NUMBER() is correct.

9. What Is an Index?

Explanation:

An index improves data retrieval speed by reducing full table scans.

Real-Time Example:

Banking systems index:

  • account_number
  • customer_id

👉 Interview Tip:
Indexes speed SELECT but slow INSERT/UPDATE.

10. Types of Indexes

Common Types:

  • B-Tree Index → High cardinality (Employee_ID)
  • Bitmap Index → Low cardinality (Gender, Status)

11. What Is a View?

Explanation:

A view is a virtual table created using a SELECT query.

Real-Time Use:

  • Hide sensitive columns like salary
  • Simplify complex joins

12. NVL vs COALESCE

Explanation:

  • NVL works with two values.
  • COALESCE works with multiple values.

Example:

SELECT COALESCE(commission, bonus, 0) FROM employees;

13. What Is a Cursor?

Explanation:

A cursor processes rows one at a time in PL/SQL.

Real-Time Use:

  • Bonus calculation
  • Record-by-record validation

14. Implicit vs Explicit Cursor

  • Implicit → Oracle handles it automatically.
  • Explicit → Developer controls fetch and loop.

15. %TYPE vs %ROWTYPE

Explanation:

  • %TYPE → single column datatype
  • %ROWTYPE → entire row structure

Benefit:

Avoids datatype mismatch during schema changes.

16. Exception Handling in PL/SQL

Explanation:

Exceptions handle runtime errors.

Common Exceptions:

  • NO_DATA_FOUND
  • TOO_MANY_ROWS

👉 Mandatory in production code.

17. Procedure vs Function

ProcedureFunction
May not return valueMust return value
Cannot be used in SQLCan be used in SQL

Real-Time Use:

  • Function → tax calculation
  • Procedure → data load

18. What Is a Package?

Explanation:

A package groups related procedures, functions, and variables.

Real-Time Use:

Payroll, Banking transactions, Utility packages.

19. What Is a Trigger?

Explanation:

Triggers execute automatically on INSERT, UPDATE, DELETE.

Real-Time Example:

Audit logging in banking systems.

20. COMMIT vs ROLLBACK

Explanation:

  • COMMIT saves changes permanently.
  • ROLLBACK undoes changes.

Banking Relevance:

Transaction consistency is critical.

21. SAVEPOINT

Explanation:

SAVEPOINT allows partial rollback.

Real-Time Use:

Rollback last step without losing entire transaction.

22. MERGE Statement

Explanation:

MERGE performs INSERT or UPDATE in a single statement.

Real-Time Example:

Daily employee or customer data feed.

23. Handling NULL Values

Explanation:

NULL represents unknown value.

Common Mistake:

Arithmetic with NULL returns NULL.

24. Analytic Functions

Explanation:

Analytic functions operate on result sets without collapsing rows.

Example:

Ranking, running totals, moving averages.

25. BULK COLLECT

Explanation:

Fetches multiple rows at once into a collection.

Benefit:

Improves PL/SQL performance drastically.

26. FORALL

Explanation:

Performs bulk DML operations efficiently.

Used With:

BULK COLLECT

27. Dynamic SQL

Explanation:

Executes SQL at runtime using EXECUTE IMMEDIATE.

Real-Time Use:

Dynamic reports, admin utilities.

28. Explain Plan

Explanation:

Shows how Oracle executes a SQL query.

Interview Expectation:

Ability to identify performance bottlenecks.

29. Common SQL Performance Mistakes

  • Using SELECT *
  • Missing indexes
  • Functions on indexed columns

30. How Do You Optimize SQL in Projects?

Ideal Interview Answer:

  • Proper indexing
  • Use execution plans
  • Avoid unnecessary joins
  • Use bind variables
Scroll to Top